All parts of this question refer to an airline database, with the following func
ID: 3890561 • Letter: A
Question
All parts of this question refer to an airline database, with the following functional dependencies. In the functional dependencies, two or more attributes, separated by commas, on the left side of the arrow, indicate that those attributes, in combination, determine the attribute on the right side of the arrow.
Assume that Pilot Number, Passenger Number, and Aircraft Serial Number are all unique.
FD: Flight Number, Date (arrow) Aircraft Serial Number
FD: Flight Number, Date (arrow) Pilot Number
FD: Flight Number, Date (arrow) Pilot Name
FD: Pilot Number (arrow) Pilot Name
FD: Flight Number, Date, Passenger Number (arrow) Passenger Name
FD: Passenger Number (arrow) Passenger Name
For each of the following relations, first indicate the normal form for that relation. Then, if the relation is not in third normal form, decompose it into 3NF relations. Primary key fields are underlined.
a. Flight Number, Date, Pilot Number, Pilot Name
b. Flight Number, Date, Aircraft Serial Number
c. Flight Number, Date
d. Flight Number, Date, Pilot Number, Pilot Name, Aircraft Serial Number
e. Flight Number, Date, Passenger Number, Passenger Name
Explanation / Answer
Normalization :
It is a process of organizing the data in schema in such a way to reduce the redundancy of data and to improve the integreity of data.
While looking throug hte
An database is said to be in Third normal when it satisfies the below condition
1. Should satisfy 1NF(Each of the attribute in the table should have atomic values)
2. Should satisfy 2NF(All the non key attribute need to be dependant on Key attribute)
3. No Transitive functional dependancy.
a. Flight Number, Date, Pilot Number, Pilot Name
The above table is not in 3NF since Pilot name transitively dependant on Pilot number
So to make it as 3NF, it will be further sub divided into
(Flight Number , Flight Name, Pilot Number) (Pilot Number , Pilot Name)
b. Flight Number, Date, Aircraft Serial Number
The above table is in 3NF it satisfies the above three condition
c. Flight Number, Date
The above table is in 3NF it satisfies the above three condition
d.Flight Number, Date, Pilot Number, Pilot Name, Aircraft Serial Number
The above table was not in 3NF because, there is transitive relation of attributes exits, to set the table as 3NF, it is further subdivided into
(Flight Number, Date, Aircraft Serial Number) (Pilot Number, Pilot Name)
e. Flight Number, Date, Passenger Number, Passenger Name
The above table was not in 3NF as it doesn't satisfy one of the conditions above, to make it as 3NF it is further sub divided into
(Flight Number, Date, Passenger Number) (Passenger Number, Passenger Name)
Now all the above table are in 3NF.