1. Convert the Big Patient Table into 3rd normal form.
The functional dependencies are shown in
Table 2 for your reference. Show the result of each step in the normalization process.
Normalization In-Class
Exercise
1. Convert the Big Patient
rd
Table into 3 normal form.
The functional dependencies
are shown
in Table 2 for your reference.
Draw the functional
dependency diagram, and
show the result
of each step in the
normalization process.
Table 1 Sample Data for the
Big Patient Table
VisitNo VisitDate PatNo PatAge
PatCity ProvNo ProvSpecialty
Diagnosis
V10020 1/13/2007 P1 35 DENVER
D1 INTERNIST EAR INFECTION
V10020 1/13/2007 P1 35 DENVER
D2 NURSE PRACTIONER
INFLUENZA
V93030 1/20/2007 P3 17
ENGLEWOOD D2 NURSE
PRACTIONER PREGNANCY
V82110 1/18/2007 P2 60
BOULDER D3 CARDIOLOGIST
MURMUR
Table 1 Sample Data for the Big Patient Table
VisitNo VisitDate PatNo PatAge PatCity ProvNo ProvSpecialty Diagnosis
V10020 1/13/2007 P1 35 DENVER D1 INTERNIST EAR
INFECTION
V10020 1/13/2007 P1 35 DENVER D2 NURSE PRACTIONER
INFLUENZA
V93030 1/20/2007 P3 17 ENGLEWOOD D2 NURSE PRACTIONER
PREGNANCY
V82110 1/18/2007 P2 60 BOULDER D3 CARDIOLOGIST MURMUR
Table 2 Functional Dependency in the Big Patient Table
PatNo PatAge, PatCity
ProvNo ProvSpecialty
VisitNo PatNo, VisitDate, PatAge, PatCity
VisitNo, ProvNo Diagnosis
2. A travel agency manages flight bookings for its clients. For each booking, the agency creates a
booking itinerary. The following rules are applied to the itineraries:
● A booking is made for a single person. Each booking is identified by a unique booking number. A
person may have one or more bookings with the travel agency.
● A booking may involve one or many flights. Each flight is identified by the flight number.
A flight number is unique for a given flight route and time of the day. The flight number will be re-
used across different days to represent the same scheduled trip. However, airlines use a different flight
number to represent each scheduled trip on the same day. For example, flight number QF47 represents
a flight which departs from Sydney and flies to Melbourne at 7:30 PM. Flight QF47 may, for
example, be scheduled to fly on each day of the week.
● The duration of the flight does not need to be kept in the database, it is only printed on the itinerary
based on the calculation of the difference between the departure date/time and the arrival date/time.
SAMPLE ITINERARIES
Three sample itineraries are supplied below:
Booking Number: QFTR23
Client no: 123
Client Name: W.H Red
Flight Number : QF9
Depart date : 12-July-2018
Depart time : 10 AM
Depart Airport code : MEL
Depart Airport name : Melbourne
Arrival date : 12-July-2018
Arrival time : 7:50 AM
Arrival Airport code : LAX
Arrival Airport name : Los Angeles
Duration : 14 hr 50 mins
Flight Number : AA1
Depart date : 12-July-2018
Depart time : 10 AM
Depart Airport code : LAX
Depart Airport name : Los Angeles
Arrival date : 12-July-2018
Arrival time : 11:05 AM
Arrival Airport code : SAN
Arrival Airport name : San Diego
Duration : 1 hr 5 mins
Booking Number: AXYT12
Client no: 345
Client Name: B.B Brown
Flight Number : QF47
Depart date : 21-Aug-2018
Depart time : 7:30 PM
Depart Airport code : SYD
Depart Airport name : Sydney
Arrival date : 21-Aug-2018
Arrival time : 8:50 PM
Arrival Airport code : MEL
Arrival Airport name : Melbourne
Duration : 1 hr 20 mins
Booking Number: QFAB12
Client no: 123
Client Name: W.H Red
Flight Number: : QF47
Depart date : 22-Aug-2018
Depart time : 7:30 PM
Depart Airport code : SYD
Depart Airport name : Sydney
Arrival date : 22-Aug-2018
Arrival time : 8:50 PM
Arrival Airport code : MEL
Arrival Airport name : Melbourne
Duration : 1 hr 20 mins
1. Represent this booking data in UNF. Note that the three itineraries shown are three examples of the
same form, only one UNF is required that would cover all three examples.
2. Convert this UNF to first normal form (1NF) and show all dependencies via dependency diagrams.
Continue the normalisation through to third normal form (3NF). Clearly write the relations in each
step from the unnormalised form (UNF) to the third normal form (3NF). You may consolidate the
relations after arriving at 3NF (if necessary).
