Database Systems Individual Assignment

The assignment is about analyzing and designing a database for a private charter flight company. It involves identifying data anomalies (insertion, deletion, and modification), creating a dependency diagram, normalizing the database to meet 3NF requirements, drawing an ERD in Crow's Foot notation, and writing SQL queries to retrieve specific information.

THE UNIVERSITY OF HONG KONG
HKU SPACE Community College
Introduction to Database Systems
Individual Assignment
Due Date : 6/Dec/16
Total Marks : 100
Weight : 15% of the total assessment of this course
Submission : Softcopy (through SOUL) – MS Word or pdf formats
Requirements:
 This is an individual assignment. You may discuss with each other, but you
must write your own answers.
 Hand-drawn diagrams are acceptable. Make sure they are clearly readable.
A private charter flight company uses the following table to keep track of its flight
records. Four sample data are displayed below for reference.
CHARTER_FLIGHT Table:
Attribute Name Sample Value Sample Value Sample Value Sample Value
Date 15-Jan-2014 15-Jan-2014 16-Jan-2014 18-Jan-2014
Origin Hong Kong Shanghai Hong Kong Taipei
Destination Shanghai Beijing Singapore Hong Kong
Distance (km) 1,200 1,080 2,570 810
Customer name Lee C W Wong S K Chu T Y Lee C W
Customer HKID A277377(3) B135790(4) C987652(A) A277377(3)
Customer Phone [phone] 28883888 13579024 99998888
No. of
passengers 2 18 5 7
Cargo weight
(kg) 235 18,940 348 1,550
Pilot Melton Chen Henderson Chen
Aircraft No. 1234Q 6780Y 1234Q 2557W
No. of seats 10 38 10 18
Charges per km
(US$) $5.78 $40.82 $5.78 $12.50
Q1. Using the CHARTER_FLIGHT table above, give examples to illustrate the
three data anomalies, namely insertion, deletion and modification anomalies.
(10%)
1

Q2. Draw the dependency diagram for the CHARTER_FLIGHT table above.
Identify all dependencies including transitive dependencies. Make assumptions
as necessary. (10%)
Q3. Normalize the CHARTER_FLIGHT table to meet 3NF requirements, showing
all intermediate steps. (20%)
Q4. Based on the results from Q3, draw the ERD in Crow’s Foot notation,
highlighting all PKs, FKs, attributes and relationships. (20%)
Q5. Based on the ERD from Q4, write SQLs for the queries.
a. Generate a listing of all the flights originated from Hong Kong in
January 2014, showing the aircraft no., customer name, no. of
passengers on board and the pilot name, in the ascending order of
customer name. (10%)
b. Display the total kilometers flown and the total charges for customer
LEE C W in January 2014. The answer should be as follows: (20%)
Q6. Make improvements to the tables from Q3, using suggestions from the lecture
‘Improving the Database Design & De-normalization’. (10%)
2
Customer_Nam
e Total_Distance Total_Charges
Lee C W 2010 17,061.00