Databases
StackOverflow2013
Postsdbo
PONormalize to Boyce Codd Normal Form in relational data model
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONormalize to Boyce Codd Normal Form in relational data model
    primarykey
    data
    text
    <p>OK this is probably very basic for the majority of you, but I'm struggling to do this assignment (Yep, it's homework). I've attempted this twice now and failed both times so far.</p> <p>I need to create a relational data model in Boyce Codd Normal Form. Here are the details:</p> <blockquote> <p>The Airport Authority in a small nation requires a database to store information about airlines, pilots and planes.</p> <p>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.</p> <p>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.</p> </blockquote> <p>This is what I now have (After re-designing):</p> <p>The table AIRLINES should include:</p> <pre><code>ID, Name, Phone # </code></pre> <p>The table PLANES should include:</p> <pre><code>ID, Date of Manufacture Airline ID FOREIGN KEY (Airline ID) references table AIRLINES Plane Type ID FOREIGN KEY (Plane Type ID) references table PLANE TYPE </code></pre> <p>The table PLANE TYPE should include:</p> <pre><code>ID, Maximum range, Fuel capacity, </code></pre> <p>The table PILOTS should include:</p> <pre><code>ID, Name, Date of Birth, Address, License #, Airline ID, FOREIGN KEY (Airline ID) references table AIRLINES PilotPlaneID FOREIGN KEY (PilotPlaneID) references table PILOTPLANETYPE </code></pre> <p>The table PILOTPLANETYPE should include:</p> <pre><code> PilotID PlaneTypeID FOREIGN KEY (PilotID) references table PILOT FOREIGN KEY (PlaneTypeID) references table PLANE TYPE </code></pre> <p>According to my marker, I had have left a many to many relationship between Pilot and Plane Type. Can someone with some database design knowledge please give me helping hand?</p> <p>(EDIT: Tables now updated with PILOTPLANETYPE, which I think should satisfy the requirements)?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMat
    1. USScott Wilson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COA type of plane is manufactured over a vast date range (20 years, maybe); the date of manufacture for a single plane is probably not associated with the type of plane but with the specific plane. Similarly, there are many airlines that fly a given type of plane, but a given plane is only flown by a single airline at a time. You have some attributes misplaced, therefore.
      singulars
      1. PONormalize to Boyce Codd Normal Form in relational data model
      1. USJonathan Leffler
    2. COOk, i've changed the tables and attributes. Do you think this should work now?
      singulars
      1. PONormalize to Boyce Codd Normal Form in relational data model
      1. USScott Wilson
    3. COPretty close; you might want to document that the combination of PilotID and PlaneTypeID is the primary key of the PilotPlaneType table. You've got the minimum necessary information in the PlaneType table; I suspect you might want a Manufacturer ID (and table of Manufacturers to identify the different manufacturers, with a PK-FK relationship to PlaneType) and a Model Name in the PlaneType table. Simply printing the plane type number might be sufficient, but probably isn't quite.
      singulars
      1. PONormalize to Boyce Codd Normal Form in relational data model
      1. USJonathan Leffler
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload