• 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 ...

    Consumers can purchase functioning network equipment for fractions of the original cost. Thousands of Cisco products are sold on E-bay with price tags ranging from a few dollars to just under one hundred thousand. Customers also have the option to access the Internet or a network without building their own.

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

    be deployed for office LAN communication and one Cisco 3560 for the data centre. A Cisco 3825 with built-in firewall will be deployed at the Internet PoP (Point of Presence) at the main Nassau branch that will integrate the incoming WAN connections from the remote offices.

  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. Information Systems

    Substitutes The determinants of the substitution threat are the relative price performance, switching costs, and the inclination of buyers to use substitutes. When suppliers have alienated their customers they increase the readiness of those customers to use an alternative product whenever it becomes available.

  2. Creating a shopping website with Project management

    be needed * Links need to be more clear - linking in with colour scheme OLD WEBSITE NEW WEBSITE The screen design below is the design the group will use for our website. The group have changed our screen designs so that is user friendly with the goals of the HCI which stands for Human-computer interaction.

  1. Systems Analysis Assignment 2

    Include in all BSOs FR7 Sales and Profit Information Manager H Manager to request from the system daily/weekly/monthly reports as required. Include in all BSOs Non Functional Requirements ID Description User or Source Priority Comment Action NFR1 Integration with accounts system Payments Team M System should automatically update the Sage accounts system to show payments received.

  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