Database Systems Individual Assignment - HKU SPACE Community College

This document presents a complete solution to a database assignment from the HKU SPACE Community College. The assignment focuses on the design and implementation of a database system for a private charter flight company. The solution begins by illustrating data anomalies (insertion, deletion, and modification) within the provided CHARTER_FLIGHT table. It then constructs a dependency diagram, identifying all dependencies, including transitive dependencies. The core of the solution involves normalizing the CHARTER_FLIGHT table to meet 3NF requirements, with detailed steps provided. An Entity-Relationship Diagram (ERD) in Crow’s Foot notation is then drawn, highlighting primary keys, foreign keys, attributes, and relationships. Based on the ERD, the solution provides SQL queries for specific tasks, such as generating flight listings and calculating total kilometers flown and total charges. Finally, the solution suggests improvements to the database design, incorporating concepts from the lecture on 'Improving the Database Design & De-normalization'.

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