Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2012
    Posts
    3

    Normalisation to 1NF and 2NF query.

    Hi, (you will see the example in the attached Word document)

    I have been been looking at a normalisation question and solution (attached) and realise that i am coming up with a different solution to the one given to me! I have provided my own solution, which is different. Could you please look at my solution and see if you agree?

    The second thing is to do with the 1NF split. Does it matter how I link the two tables...again, you will see what i mean in the attachment.

    Please let me know if you can advise as I want to get this stuff clear in my head!

    Many thanks

    T
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i doh wanna read word docs

    can't you think of a more internet-friendly way of presenting your problem?

    plain text and/or gifs will do nicely
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2012
    Posts
    3

    1NF - 3NF Normalisation query. I get a different answer!

    Here’s the normalisation question. The table below needs to be normalised to 1NF, 2NF and then 3NF.

    Normalisation Q.bmp


    Now, the solution provided to me is as follows:
    1NF
    FLIGHT (FlightID, AirportCode, Airport, Date, Departure, Arrival)

    FLIGHT-PASSENGER (FlightID, PassengerID, Name, Contact)

    2NF
    FLIGHT (FlightID, AirportCode, Airport, Date, Departure, Arrival)

    FLIGHT-PASSENGER (FlightID, PassengerID)

    PASSENGER (PassengerID, Name, Contact)

    3NF
    FLIGHT (FlightID, AirportCode, Date, Departure, Arrival)

    AIRPORT (AirportCode, Airport)

    FLIGHT-PASSENGER (FlightID, PassengerID)

    PASSENGER (PassengerID, Name, Contact)

    My main issue here is with the 1NF arrangement. I would have kept the Date field with the FlightID and the PassengerID fields. My reasoning here is that the date is not just for one flight. The flightID can surely have many dates! Surely not just the 01/11. Therefore, I would understand it to be the date that the Passenger travelled on a particular Flight. My own 1NF to 3NF arrangement would be as follows:

    1NF
    FLIGHT (FlightID, AirportCode, Airport, Departure, Arrival)
    FLIGHT-PASSENGER (FlightID, PassengerID, Date, Name, Contact)

    2NF
    FLIGHT (FlightID, AirportCode, Airport, Departure, Arrival)
    FLIGHT-PASSENGER (FlightID, PassengerID, Date)
    PASSENGER (PassengerID, Name, Contact)

    3NF
    FLIGHT (FlightID, AirportCode, Airport, Departure, Arrival)
    AIRPORT (AirportCode, Airport)
    FLIGHT-PASSENGER (FlightID, PassengerID, Date)
    PASSENGER (PassengerID, Name, Contact)

    Would I be right? Or wrong here? Please help.
    (Please read on to see my second query)

    One other thing….which has always bugged me!

    Does it matter which field is used to link the two tables in 1NF? For example, the table in the question could have been presented two different ways:

    FLIGHT (FlightID, AirportCode, Airport, Departure, Arrival)
    FLIGHT-PASSENGER (FlightID, PassengerID, Date, Name, Contact)

    OR

    PASSENGER (PassengerID, Name, Contact)
    FLIGHT-PASSENGER (FlightID, PassengerID, Date, AirportCode, Airport, Departure, Arrival)

    Are both perceived to be correct and acceptable?

    Many thanks,,,I look forward to any advice given!

  4. #4
    Join Date
    Sep 2012
    Posts
    3

    Sorry...here are the key fields for the previous solutions:

    Now, the solution provided to me is as follows:
    1NF
    FLIGHT (FlightID, AirportCode, Airport, Date, Departure, Arrival)

    FLIGHT-PASSENGER (FlightID, PassengerID, Name, Contact)

    2NF
    FLIGHT (FlightID, AirportCode, Airport, Date, Departure, Arrival)

    FLIGHT-PASSENGER (FlightID, PassengerID)

    PASSENGER (PassengerID, Name, Contact)

    3NF
    FLIGHT (FlightID, AirportCode, Date, Departure, Arrival)

    AIRPORT (AirportCode, Airport)

    FLIGHT-PASSENGER (FlightID, PassengerID)

    PASSENGER (PassengerID, Name, Contact)

    My main issue here is with the 1NF arrangement. I would have kept the Date field with the FlightID and the PassengerID fields. My reasoning here is that the date is not just for one flight. The flightID can surely have many dates! Surely not just the 01/11. Therefore, I would understand it to be the date that the Passenger travelled on a particular Flight. My own 1NF to 3NF arrangement would be as follows:

    1NF
    FLIGHT (FlightID, AirportCode, Airport, Departure, Arrival)
    FLIGHT-PASSENGER (FlightID, PassengerID, Date, Name, Contact)

    2NF
    FLIGHT (FlightID, AirportCode, Airport, Departure, Arrival)
    FLIGHT-PASSENGER (FlightID, PassengerID, Date)
    PASSENGER (PassengerID, Name, Contact)

    3NF
    FLIGHT (FlightID, AirportCode, Airport, Departure, Arrival)
    AIRPORT (AirportCode, Airport)
    FLIGHT-PASSENGER (FlightID, PassengerID, Date)
    PASSENGER (PassengerID, Name, Contact)

    Would I be right? Or wrong here? Please help.
    (Please read on to see my second query)
    One other thing….which has always bugged me!

    Does it matter which field is used to link the two tables in 1NF? For example, the table in the question could have been presented two different ways:

    FLIGHT (FlightID, AirportCode, Airport, Departure, Arrival)
    FLIGHT-PASSENGER (FlightID, PassengerID, Date, Name, Contact)

    OR

    PASSENGER (PassengerID, Name, Contact)
    FLIGHT-PASSENGER (FlightID, PassengerID, Date, AirportCode, Airport, Departure, Arrival)

    Are both perceived to be correct and acceptable?

    Many thanks,,,I look forward to any advice given!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i agree with you about the date being an attribute of a passenger-flight occurrence, rather than belonging to the flight (which would indicate that each flight occurs only once)

    regarding your 1nf question, all that is needed for 1nf is to have a primary key and atomic column values, so either of those approaches works, and i believe even the original flat file would be 1nf
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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