1

I have to create an entity relationship diagram for an SQL database. This question relates to a previous question I posted: Normalisation - SQL - 3NF

I have done the following entity relationship diagram and was wondering if it is correct (such as the relationships between tables), as follows:

enter image description here

The above entity relationship diagram is based on the following data in 3NF:

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

TBLFlight(FlightID[PrimaryKey], FlightNum, IATADep, IATAArr, Date, SchArr, comments)

TBLAircraft(AircraftId[PimaryKey], A/CType, A/C Rep, A/C Manuf)

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

AircraftFlight(CraftId, FlightId) [Composite Keys]

And here is the original data in UNF:

enter image description here

Is the entity relationship diagram correct in terms of relationships within the 3NF?

Community
  • 1
  • 1
R. Jones
  • 255
  • 2
  • 13
  • 1
    Yes, it looks correct. The best way to validate it is to write some code to test it. Basically, if you have the data create the DDL for the schema and the DML for the inserts. Then, insert the data and see if you have reduced the possibility of duplicate data, but still allowed for the creation of the business entities as given in the requirement. – jsh Oct 04 '15 at 18:11
  • ahh, okay like in Microsoft Access for example? – R. Jones Oct 04 '15 at 18:13
  • Are you using a modeling tool for your diagram? Many of these tools will generate the DDL for you. Here are some basic pointers that may be helpful if you have the corresponding toolset. http://stackoverflow.com/questions/6775810/exporting-entity-relationship-diagram-from-sql-with-visio-2010-professional-plu?rq=1. – jsh Oct 04 '15 at 18:15
  • I am not too advanced in relation to entity-relationship diagrams (as you can probably tell). I just sketched it up in paint for the time being. I wasn't too sure if the relations ships were correct. So is the TBLCrew-to-TBLFlights an example of a many-to-many relationship, and is the relationship between TBLAircrafts-to-TBLFlights correct? Thanks for the help by the way – R. Jones Oct 04 '15 at 18:18
  • No problem. Yes, they look correct. – jsh Oct 04 '15 at 18:28
  • I'll take your word for it that your relations are in 3NF since you didn't specify functional dependencies. It looks like multiple aircraft can fly the same flight on the same date - is that right? – reaanb Oct 05 '15 at 14:55

0 Answers0