Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jan 2005
    Posts
    4

    Airport Normalization

    I have to design a conceptual database model up to 3rd normal form based on this problem, i think i have identified the main entities in 1nf as listed below however i am a little confused as to where to go next. Should national and international flights have their own table?? If so in what form?? Should Completed and future flights have their own table and if so in what form? I have seen many examples and all make sense even though they are generally different, so i am very confused and any help identifying the entities and attributes in 3nf and below would be very helpful because then i can get on with the SQL coding! I just cant get my head around this bit.

    Here is a summary of the problem....


    The requirements collection and analysis phase of the database design process has provided the following data requirements for a company called Crazy Airline. The company offers flights, each of which has a number that identifies the flight (for example Manchester-Paris, MP777), a date (1st March 2003), the day (Monday), a departure time (3:00) and an arrival time (12:00), a departure airport and a destination airport. Information about the airport includes the city, country and the number of the runways in that airport. There are national and international aircrafts, with different types and description (number of seats, size engine, flight make, and so on). The international flights can have one or more stopovers.
    For completed flights, information to be recorded is the actual time of the departure and arrival. For future flights, the number of seats available must be known, with the description of the aircraft.
    What i have so far:

    Flight (Flight Number, Date, Day, Departure Time, Arrival Time, Departure Airport, Destination Airport)

    Airport (Airport ID, City, Country, Number of runways)

    Aircraft (Aircraft ID, National/International, Type, Number of Seats, Engine Size, Flight Make)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    march 1st 2003 was a saturday

    so even before the flight gets out of the gate, you have a data integrity problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    4
    haha obviously the things in brackets are examples i didnt go and look at the date to find out

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'd suggest that you either attend one of the class discussion sessions, or that you stop and talk with the teacher or TA. Those are really your best choices for this kind of problem.

    We generally can help with technical questions (I want to do X, and here's what I've tried, but I keep getting error message Y), but it is practically impossible to help with design issues like you are facing via postings. You really need to get some hands-on, one-on-one help to work through these issues, and I don't know how to give that in the posting environment.

    Aside from the practical issues of my not knowing how to really help you with this problem here, it may also help the TA or teacher if they work through things with you. Lots of times they see things from a different perspective while helping people, and may realize that they've missed some important topic in class.

    -PatP

  5. #5
    Join Date
    Jan 2005
    Posts
    30
    That sounds like your off to a good start, are you after it in 3NF?? I think you have the entities right

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, the entities are right, but having both date and day violates one of the normal forms (can't remember which) because day depends on date and not on flight number

    and according to theory, if city is unique (an iffy proposition unless city is a foreign key) then country depends on city, and not on airport id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2005
    Posts
    4
    so do you think i should create a separate table with date and day in as date would be unique and therefore a primary key??

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    absolutely not

    just use the appropriate date function

    you wouldn't create this table, would you? --

    create table sums
    ( one integer
    , two integer
    , oneplustwo integer
    )
    insert into sums values ( 1, 1, 2 )
    insert into sums values ( 2, 2, 4 )
    insert into sums values ( 4, 2, 6 )
    insert into sums values ( 6, 3, 9 )
    ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2004
    Posts
    330
    Don't forget the foreign-key referencing the NUMBERS table this will insure that all of the entries are valid numbers.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    omg wtf LOL

    another brilliant gem!

    urquel++
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by urquel
    Don't forget the foreign-key referencing the NUMBERS table this will insure that all of the entries are valid numbers.
    [thud]
    ooouch...It's not often I get knocked off my BarStoo....ummm Office chair

    [/thud]

    While you're at it. Make sure you have a valid alpha numeric table....make dure to include ASCII Values (ie SELECT ASCII('a')) Don't forget to include case sensitivity...then make sure you check your strings byte by byte. A user defined function would help here.

    Or go google

    http://databases.about.com/od/specif...malization.htm
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Aug 2004
    Posts
    330
    To take it a step further. The BYTE table would have a foreign key referencing the BIT table. This would keep those pesky 2's out of the system.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And make sure every table has a clustered unique primary key IDENTITY Column.

    Make sure they are all called [id]..saves on typing
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jan 2005
    Posts
    4
    helpful....

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    And make sure every table has a clustered unique primary key IDENTITY Column.
    especially the state codes

    you may think you can get away with TX as the natural primary key for Texas, but don't make that mistake

    actually, you should be using a GUID (globally unique identifier)

    thus, an address row would look like

    123 Cowpatty Lane, Austin, {3F2504E0-4F89-11D3-9A0C-0305E82C3301}

    and then {3F2504E0-4F89-11D3-9A0C-0305E82C3301} would be the foreign key link to TX in the state table

    you know, in case Texas ever changes its state code TX



    the part that's always confused me about GUIDs, however, is this: how does sql server know that it's TX i'm generating the GUID key for?

    i mean, if it's supposed to be, you know, a globally unique identifier, then for crying out loud, TX had better be the same in everybody's database on the globe, otherwise how could you ever communicate an address from one computer to another?????????????????
    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
  •