1

I am designing a SQL database that needs to be a five-table database to meet Air-Crewe’s requirements. I have the following so far:

I have this for UNF:

CrewID, Crew Type, Title, Forename, Surname, Gender, CAALicenceNum, FlightID, FlightNum, IATADep, IARAArr, Date, SchDep,SchArr, Comments, A/CType, A/CReg, A/CManuf

I have this for 1NF:

TBLCrew(CrewID[PrimaryKey], CrewType, CrewTitle, Forename, Surname, gender, CAALicenceNum, FlightID*)

TBLFlight(FlightID[PrimaryKey], FlightNumber, IATADep, IATAArr, Date, SchArr, comments, A/CType, A/CReg, A/CManuf)

I have this for 2NF:

TBLCrew(CrewID[PrimaryKey], CrewType, CreweTitle, Forename, Surname, gender, CAALicenceNum)

TBLFlight(FlightID[PrimaryKey], FlightNum, IATADep, IATAArr, Date, SchArr, comments, A/CType, A/CReg, A/CManuf)

TBLCrewFlight(CreweID[composite/compoundKey], FlightID[composite/compoundKey])

The 3NF needs to be be separated into five tables but I don't know how to achive this - can anyone please help me out? Or correct me if I have made a mistake in the normalisation above (I am new to normalisation as you probably can tell)

enter image description here

R. Jones
  • 255
  • 2
  • 13
  • You can have 2 another tables for **A/CType** and **CrewType** entities. – Mike Anderson Oct 04 '15 at 14:16
  • I might put Crew Type and Title in TBLCrewFlight for 2NF since a pilot might be a pilot or copilot, and if there's a head flight assistant it might vary from flight to flight, too. It depends somewhat on how the business defines things. – Bacon Bits Oct 04 '15 at 14:24
  • thanks for the quick response guys, I have included a UNF table above in order to illustrate the different type of cell values, thanks for taking the time to help me out! – R. Jones Oct 04 '15 at 14:27
  • I'm slightly unclear from this whether rows `TBLCrew` describe a *crew* (group of people), or a *crew member* (single person). – Ian McLaird Oct 04 '15 at 14:29
  • sorry my bad, TBL crew is for a single crewe member – R. Jones Oct 04 '15 at 14:30
  • in other words it is a table that is built up of single crew members – R. Jones Oct 04 '15 at 14:30

3 Answers3

1

Accepted answer can have more Aircrafts for a flight, I do not think that is correct.

TBLCrew(CrewID[PrimaryKey], CrewType, CreweTitle, Forename, Surname, gender, CAALicenceNum)

TBLFlight(FlightNum[PrimaryKey], IATADep, IATAArr, Date, SchArr, A/CReg[composite/compoundKey])

TBLCrewFlight(CreweID[composite/compoundKey], FlightNum[composite/compoundKey], Comments)

Aircraft(A/CReg[PrimaryKey], A/CType[composite/compoundKey])

TBL_A/CType(A/CType[PrimaryKey], A/C Manuf)
Antonín Lejsek
  • 6,003
  • 2
  • 16
  • 18
  • This is actually correct. There should never be more than one aircraft on a flight at a time. Depending on what's meant by a "flight", this answer is better than mine. – Ian McLaird Oct 05 '15 at 23:58
0

First of all - I am unsure even of the 1st form. Comments implies multiple instances of a comment, therefore it probably isn't atomic and I would make a table for them too. It would have three attributes - comment_ID, comment, FlightID.

In the 3rd form every non-prime attribute of the table is non-transitively dependent on every superkey of the table. So in layman's terms if you logically identify attributes which are dependent on another non-key attribute, you need to transform them into another table.

If gender is dependent on the forename is arguable. Other decompositions are somewhat difficult, since I don't have the descriptions of the columns (not entirely sure what they represent).

However here I present some of my speculations:

  • CrewTitle probably depends on the gender - bam new table
  • Departures and arrivals depend on the Flight number - bam new table
  • A/C types and manufacturers probably depend on the A/C Reg - bam new table

However, you should have better understanding of the individual columns, and therefore you should make these decisions by yourself. These examples should help you to understand the 3rd form concept.

Leprechaun
  • 852
  • 6
  • 25
0

I think you're actually almost there. I'd split the aircraft information into its own table

Aircraft(CraftId[PimaryKey], A/CType, A/C Rep, A/C Manuf)

And then assign aircraft to the flight

AircraftFlight(CraftId, FlightId) [Composite Key]
Ian McLaird
  • 5,507
  • 2
  • 22
  • 31