I'm hoping someone can take the time out to help a frustrated student.
I have been trying to do an assignment for a while now and i've been pulling my hair out with this one. I know it's probably so simple, but anyway.
I need to create a relational data motel in Boyce Codd Normal Form. Here are the details:
The Airport Authority in a small nation requires a database to store information about airlines, pilots and planes.
There is only one airport but several airlines use it.
For each airline its identification code and full name is required together with the phone number of its local office.
Several types of plane use the airport and for each type, the fuel capacity and maximum range is to be recorded.
Each pilot works for only one airline. For each pilot, their name, date of birth, address, the types of plane they can fly and their pilot‟s licence number is to be recorded.
Further information is to be recorded to enable the production of a report listing the following details for each plane that uses the airport: Plane identification code Plane type Date of manufacture Owning airline.
(EDIT i have changed this to what i have since come up with, i think it is now correct?) This is what i have:
The table AIRLINES should include
The table PLANE should include
Date of Manufacture
FOREIGN KEY (Plane_type_ID) references table Plane Type
The table PLANE TYPE should include
The table PILOT should include
Date of Birth
FOREIGN KEY (Pilot_plane_typeID) references table Pilot_plane_type
FOREIGN KEY (Airline ID) references table AIRLINES
The table Pilot_plane_type should include:
FOREIGN KEY (Pilot ID) references table Pilot
Plane Type ID
FOREIGN KEY (Plane Type ID) references table Plane Type
According to my marker, i had left a many to many relationship between Pilot and Plane Type. I think i have removed the many to many by placing the pilot_plane_type table. Can someone with some database design knowledge please give me helping hand?
Last edited by depecid; 08-21-12 at 22:32.
Reason: Working on a correction