• Join over 1.2 million students every month
  • Accelerate your learning by 29%
  • Unlimited access from just £6.99 per month
Page
  1. 1
    1
  2. 2
    2
  3. 3
    3
  4. 4
    4
  5. 5
    5
  6. 6
    6
  7. 7
    7
  8. 8
    8
  9. 9
    9
  10. 10
    10
  11. 11
    11
  12. 12
    12
  13. 13
    13
  14. 14
    14
  15. 15
    15
  16. 16
    16
  17. 17
    17
  18. 18
    18
  19. 19
    19
  20. 20
    20
  21. 21
    21
  22. 22
    22
  23. 23
    23
  24. 24
    24
  25. 25
    25
  26. 26
    26
  27. 27
    27
  28. 28
    28
  29. 29
    29
  30. 30
    30
  31. 31
    31
  32. 32
    32
  33. 33
    33
  34. 34
    34
  35. 35
    35
  36. 36
    36
  37. 37
    37
  38. 38
    38
  39. 39
    39
  40. 40
    40

Database Systems modules and course work

Extracts from this document...

Introduction

image22.pngimage23.png

3ISY402: Database Systems

COURSEWORK

image00.png


Question 1

ER Diagram

image53.pngimage02.pngimage08.pngimage02.pngimage03.pngimage13.pngimage20.pngimage04.pngimage12.pngimage21.pngimage11.pngimage06.pngimage19.pngimage10.pngimage02.pngimage17.pngimage07.pngimage18.pngimage02.pngimage05.pngimage09.pngimage16.pngimage14.pngimage06.pngimage01.png


The statements below describes the relationships in the ER Diagram,

Customer and Holiday Booking

A Customer may make zero or more Holiday Bookings.

A Holiday Booking must have one and only one Customer.

Payment and Holiday Booking

A payment must be made for one and only one Holiday Booking.

A Holiday Booking may contain zero or more Payments.

Holiday Booking and Hotel  

A Hotel Booking may contain zero or more hotels.

A Hotel may contain zero or more Holiday Bookings

Airline and Scheduled Flight

An Airline may schedule zero or more Scheduled Flights.

A Scheduled Flight must be scheduled by one and only one Airline.

Scheduled Flight and Airport

A scheduled Flight must fly to one and only one Airport.

An Airport must have one or more Scheduled Flights.

Scheduled Flight and Airport

A scheduled Flight must fly from one and only one Airport.

An Airport must have one or more Scheduled Flights.

Holiday Booking and Scheduled Flight

A Holiday Booking may consist of zero or one Scheduled Flight.

A Scheduled Flight may be a part of zero or more Holiday Bookings.

Assumptions

Holiday Booking and Scheduled Flight

A Holiday Booking can exist without a scheduled Flight or may have just one Flight Scheduled; we have also assumed that a scheduled Flight may have zero or more Holiday Bookings.

Scheduled Flight and Airport

The Departure time of a Flight is not considered as the take off time of the Flights and we have two relationships between scheduled flight and Airport considering the two airports such as the destination and starting airports we are ignoring the transit airports we assuming the relationship is for the whole journey.

Question 2

Relational Schema of the Entity Relationship,

  1. Customer  (CustomerNo, Address, Title, Surname, Forename, TelNo)

...read more.

Middle

telNo CHAR(10) NOT NULL,

faxNo CHAR(10) NOT NULL,

PRIMARY KEY(airline));

INSERT INTO GROUPA5Airline

VALUES ('ULL86','Sri Lankan Air Ways', 0112446688, 0112446689);

INSERT INTO GROUPA5Airline

VALUES ('SQL44','Quatar Airways', 5565896472, 4565896472);

INSERT INTO GROUPA5Airline

VALUES ('NSL86','Ethiad Airways', 5255846923, 1122564236);

INSERT INTO GROUPA5Airline

VALUES ('SQL58','Quatar', 5565896472, 4565896472);

INSERT INTO GROUPA5Airline

VALUES ('ULL54','Sri Lankan Air Ways', 0112446688, 0112446689);

COMMIT;

HOTEL

CREATE TABLE GROUPA5Hotel(

hotelId  CHAR(5) NOT NULL,

hotelName  VARCHAR(20) NOT NULL,

street  VARCHAR(20) NOT NULL,

town  VARCHAR(15) NOT NULL,

postCode CHAR(9) NOT NULL,

PRIMARY KEY (hotelId),

INSERT INTO GROUPA5Hotel

VALUES ('HIL01','Hotel Hilton','7 Brverly Street','U.S.A','081263356');

INSERT INTO GROUPA5Hotel

VALUES ('TAJ02','Taj','21 Tower Brigde,' London','546667699');

INSERT INTO GROUPA5Hotel

VALUES ('HOL01','Holiday Inn','04 Ql Rashif Park','Dubia','964466562');

INSERT INTO GROUPA5Hotel

VALUES ('HIL02','Hotel Hilton','22 Lakw House','Sri Lanka','888999898');

INSERT INTO GROUPA5Hotel

VALUES ('TAJ01','Taj','03 Bristol','West Germany',' 595456632');

COMMIT;

HOLIDAY BOOKING

CREATE TABLE GROUPA5HolidayBooking(

bookingId CHAR(5) NOT NULL,

bookingDate DATE NOT NULL

totalCost NUMBER(6,2) NOT NULL,

customerNo CHAR(5) NOT NULL,

PRIMARY KEY(BookingId),

FOREIGN KEY(customerNo) REFERENCES GROUPA5Customer(customerNo));

INSERT INTO GROUPA5HolidayBooking

VALUES ('LK112','07-Jan-2007',32.49 ,'CU111');

INSERT INTO GROUPA5HolidayBooking

VALUES ('LK113','31-Jan-2008',92.10, 'CU555');

INSERT INTO GROUPA5HolidayBooking

VALUES ('LK114','04-Sep-2006',62.35, 'CU222');

INSERT INTO GROUPA5HolidayBooking

VALUES ('LK115','08-Apr-2006',112.50, 'CU777');

INSERT INTO GROUPA5HolidayBooking

VALUES ('LK116','03-Mar-1999',79.50, 'CU111');

COMMIT;

PAYMENT

CREATE TABLE GROUPA5Payment(

paymentId CHAR(5) NOT NULL,

paymentDate DATE NOT NULL,

modeOfPayment VARCHAR(20) NOT NULL,

bookingId CHAR(5) NOT NULL,

PRIMARY KEY(paymentId),

FOREIGN KEY(bookingId) REFERENCES GROUPA5HolidayBooking(bookingId));

INSERT INTO GROUPA5Payment

VALUES ('DP666','30-Jan-07','Cash','LK112');

INSERT INTO GROUPA5Payment

...read more.

Conclusion

Find out the number of courses that have been taken by each student. For each student, give their name and number of courses enrolled on.list the students in alphabetical order.

image34.png

Question 4

Calculate the average tuition fee of all courses held in ‘Cavendish’.

image35.png

Question 5

List the details of all courses related to Design, which cost less than 600.make sure that the word “Design” is in the course name.

image36.png

Question 6

Find out how many tutors teach courses that are less than 10 hours long.

image37.png

Question 7

Give the following details of  the “data base management” course :

Course code, tuition fee, room number, room location and tutor name.

image38.png

Question 8

For each tutor who teaches more than one course,find out the number of courses taught and the total duration/lenghth of the courses. the following columns are required in the output:

Tutor forename and surname,number of courses and total duration hours.

image39.png

Question 9

List the detail of all tutors who have taught “Glen Boss’, and also show the course name in the output

image40.png

Question 10

List the details of courses allocated the classroom with the highest seating capacity

image41.png

Question 11

Creative a view called GoodStudents, providing details of all students who have achieved a grade of either ‘A’or ‘B’ include the following columns: student number,student name,course code,course name and grade

image42.png

Question 12

Add an attribute called ‘dateOfBirth’ to the student table.

Enter a date of birth for each student

Altering the table GROUPA5Student

image43.png

Inserting data from date of birth for each student in GROUPA5Student

image44.png

image45.png

image46.png

Table GROUPA5Student

image47.png

Question 13

Write an update query to increase the tuition fee of all courses taught by ‘Marie Powell’

By 25% Assume that you do not know Marie Powell’s tutor id.

image48.png

image46.png

Table GROUPA5Course

image49.png

Question 14

Write an update query to delete any record in the Enrolment table where a grade has not been entered.

image50.png

image51.png

Table GROUPA5Enrolment

image52.png


Question 15

Draw an ER diagram representing the entities and relationships given the relational schema above.

image15.png


Page  of

...read more.

This student written piece of work is one of many that can be found in our University Degree Information Systems section.

Found what you're looking for?

  • Start learning 29% faster today
  • 150,000+ documents available
  • Just £6.99 a month

Not the one? Search for your essay title...
  • Join over 1.2 million students every month
  • Accelerate your learning by 29%
  • Unlimited access from just £6.99 per month

See related essaysSee related essays

Related University Degree Information Systems essays

  1. This research is about Management of network operating system at Natural Forest Resources management ...

    Windows can be a great tool for the lazy and incompetent, but it takes a true scholar and one who wants to learn to run a robust operating system like Linux. 2.2 Empirical Literature Review Saini Pradeep Kumar(2003),defined operating system is like a interpreter between the user and computer ,it

  2. The purpose of this paper is to provide an analysis of Cisco Systems primary ...

    Threat of Substitute Products and Services A substitute is a replacement or alternative to doing business with the strategic business unit. The largest threat Cisco faces from substitute products is actually from Cisco products, used that is. Used Cisco equipment is auctioned off by online companies like E-bay.

  1. Network Proposal. Androsia Superior Fabrics has solicited the services of ML-DD IT Consultants ...

    the network Network administrators will be able to do the following: * Access the firewalls * Have access to all software suites * Have administrator rights to all end-user machines *

  2. Happy Holidays Hotel. Information Systems Analysis and Design. When we design the new ...

    When we design the new system, it will be providing the owners to receive and store reservations from customers through the internet, or by other booking methods and then record into the system. Because they also have a website, so we will to synchronize the website process such as cancel

  1. Creating a shopping website with Project management

    Customers will need to login to their personal account to purchase items. This page will be simple and easy to use. The group also plan to have an administration page in our website that only admin will be able to access.

  2. BIM and Facility Management

    status in the CAFM which will send an email to the costumer to confirm that the WICI is solved. The following chapter will explain the technique behind a CAFM system and its implementation into a company. Chapter 2 CAFM 2.1 Introduction In this chapter we will explain what a CAFM is.

  1. Systems Analysis Assignment 2

    Include in all BSOs NRF2 Integration with telephone system Sales Team/ Manager M System should be able to integrate with telephone system to report information such as how many calls each sales team member has taken and call timings. Include in all BSOs NRF3 Set sales targets Manager M Manager

  2. Organisational Information Systems. In this assignment I have chosen the accounting department, I ...

    profit and loss accounts, auditing, payroll etc. laptops are very practical as they allow the individual to work from home in severe weather conditions, and by the help of Tele-communications they can interact with colleagues and other staff 2. USB devices- these allow an individual to save all the documents they may need to use outside their office.

  • Over 160,000 pieces
    of student written work
  • Annotated by
    experienced teachers
  • Ideas and feedback to
    improve your own work