Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2012
    Posts
    1

    Help with Boyce Codd and Data Model

    Hi,
    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
    ID
    Name
    Phone #

    The table PLANE should include
    ID
    Date of Manufacture
    Plane_type_ID
    FOREIGN KEY (Plane_type_ID) references table Plane Type

    The table PLANE TYPE should include
    ID
    Fuel Capacity
    Maximum Range

    The table PILOT should include
    ID
    Name
    Date of Birth
    Address
    License #
    Pilot_plane_typeID
    FOREIGN KEY (Pilot_plane_typeID) references table Pilot_plane_type
    Airline ID
    FOREIGN KEY (Airline ID) references table AIRLINES

    The table Pilot_plane_type should include:
    Pilot ID
    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?

    Many thanks.
    Last edited by depecid; 08-21-12 at 23:32. Reason: Working on a correction

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •