1 / 21

Week 12 November 14

Week 12 November 14. Homework 3, Part 1. Confirmation number: MCWY123 Date: November 11, 2001. AAdvantage Number: ABC123456 Mr. Moki Mao 1234 Kama`aina Place, Honolulu, HI 96821. Date. Airline. Flight/Class. Depart. Arrive. Seat. Service. Jan 17, 01 Jan 17, 01 Jan 20, 01

warren-johnson
Télécharger la présentation

Week 12 November 14

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Week 12November 14 Homework 3, Part 1

  2. Confirmation number: MCWY123 Date: November 11, 2001 AAdvantage Number: ABC123456 Mr. Moki Mao 1234 Kama`aina Place, Honolulu, HI 96821 Date Airline Flight/Class Depart Arrive Seat Service Jan 17, 01 Jan 17, 01 Jan 20, 01 Jan 23, 01 Jan 26, 01 Jan 26, 01 Jan 25, 01 United American Hawaiian Hawaiian Hawaiian Hawaiian United Sacarmento-Los Angeles UA2503 Y 9:39A 11:09A 12A Los Angeles-Honolulu AA297 F 2:00P 5:56P 25H Honolulu-Kahalui, Maui HA116 Y 9:10A 9:45A 9A Kahalui, Maui-Kona HA138 Y 9:10A 9:38A 17C Kona-Honolulu HA162 Y 10:35A 11:43A 17A Honolulu-San Francisco HA12 F 1:30P 8:25P 22G San Francisco-Sacramento UA5363 Y 10:00P 10:45P 5A Origin and destination cities Flight number Class

  3. Unique Identifier Confirmation number: MCWY123 Date: November 11, 2001 AAdvantage Number: ABC123456 Mr. Moki Mao 1234 Kama`aina Place, Honolulu, HI 96821 Date Airline Flight/Class Depart Arrive Seat Service Jan 17, 01 Jan 17, 01 Jan 20, 01 Jan 23, 01 Jan 26, 01 Jan 26, 01 Jan 25, 01 United American Hawaiian Hawaiian Hawaiian Hawaiian United Sacarmento-Los Angeles UA2503 Y 9:39A 11:09A 12A Los Angeles-Honolulu AA297 F 2:00P 5:56P 25H Honolulu-Kahalui, Maui HA116 Y 9:10A 9:45A 9A Kahalui, Maui-Kona HA138 Y 9:10A 9:38A 17C Kona-Honolulu HA162 Y 10:35A 11:43A 17A Honolulu-San Francisco HA12 F 1:30P 8:25P 22G San Francisco-Sacramento UA5363 Y 10:00P 10:45P 5A

  4. Confirmation Number Attribute Confirmation number: MCWY123 Date: November 11, 2001 AAdvantage Number Attribute Itinerary Date Attribute AAdvantage Number: ABC123456 Mr. Moki Mao 1234 Kama`aina Place, Honolulu, HI 96821 Zip Code Attribute Customer Name Attribute Date Airline Flight/Class Depart Arrive Seat Service Address Attribute City Attribute State Attribute Jan 17, 01 Jan 17, 01 Jan 20, 01 Jan 23, 01 Jan 26, 01 Jan 26, 01 Jan 25, 01 United American Hawaiian Hawaiian Hawaiian Hawaiian United Sacarmento-Los Angeles UA2503 Y 9:39A 11:09A 12A Los Angeles-Honolulu AA297 F 2:00P 5:56P 25H Honolulu-Kahalui, Maui HA116 Y 9:10A 9:45A 9A Kahalui, Maui-Kona HA138 Y 9:10A 9:38A 17C Kona-Honolulu HA162 Y 10:35A 11:43A 17A Honolulu-San Francisco HA12 F 1:30P 8:25P 22G San Francisco-Sacramento UA5363 Y 10:00P 10:45P 5A

  5. Confirmation number: MCWY123 Date: November 11, 2001 AAdvantage Number: ABC123456 Mr. Moki Mao 1234 Kama`aina Place, Honolulu, HI 96821 Date Airline Flight/Class Depart Arrive Seat Service Jan 17, 01 Jan 17, 01 Jan 20, 01 Jan 23, 01 Jan 26, 01 Jan 26, 01 Jan 25, 01 United American Hawaiian Hawaiian Hawaiian Hawaiian United Sacarmento-Los Angeles UA2503 Y 9:39A 11:09A 12A Los Angeles-Honolulu AA297 F 2:00P 5:56P 25H Honolulu-Kahalui, Maui HA116 Y 9:10A 9:45A 9A Kahalui, Maui-Kona HA138 Y 9:10A 9:38A 17C Kona-Honolulu HA162 Y 10:35A 11:43A 17A Honolulu-San Francisco HA12 F 1:30P 8:25P 22G San Francisco-Sacramento UA5363 Y 10:00P 10:45P 5A Repeating groups (multivalue attributes)

  6. Confirmation number: MCWY123 Date: November 11, 2001 AAdvantage Number: ABC123456 Mr. Moki Mao 1234 Kama`aina Place, Honolulu, HI 96821 Date Airline Flight/Class Depart Arrive Seat Service Jan 17, 01 Jan 17, 01 Jan 20, 01 Jan 23, 01 Jan 26, 01 Jan 26, 01 Jan 25, 01 United American Hawaiian Hawaiian Hawaiian Hawaiian United Sacarmento-Los Angeles UA2503 Y 9:39A 11:09A 12A Los Angeles-Honolulu AA297 F 2:00P 5:56P 25H Honolulu-Kahalui, Maui HA116 Y 9:10A 9:45A 9A Kahalui, Maui-Kona HA138 Y 9:10A 9:38A 17C Kona-Honolulu HA162 Y 10:35A 11:43A 17A Honolulu-San Francisco HA12 F 1:30P 8:25P 22G San Francisco-Sacramento UA5363 Y 10:00P 10:45P 5A Food Service Attribute Date Attribute Airline Attribute Origin Attribute Destination Attribute Seat Attribute Movie Attribute Flight number Attribute Class Attribute Departure Time Attribute Arrival Time Attribute

  7. First Normal Form (1NF) • A relation is in first normal form if and only if every attribute is single-valued for each tuple. • Remove all repeating groups • Create a flat file

  8. Unnormalized Data Model (Confirmation_number, itinerary_date, AAdvantage_number, customer_name, address, city, state, zip_code, flight_date1, flight_number1, airline1, departure_time1, arrival_time1, origin1, destination1, seat1, class1, food_service1, movie1, ... flight_daten, flight_numbern, airlinen, departure_timen, arrival_timen, originn, destinationn, seatn, classn, food_servicen, movien)

  9. 1NF • A relation is in first normal form if and only if every attribute is single-valued for each tuple. • Remove all repeating groups • Create a flat file Key? (Confirmation_number, itinerary_date, AAdvantage_number, customer_name, address, city, state, zip_code, flight_date, flight_number, airline, departure_time, arrival_time, origin, destination, seat, class, food_service, movie)

  10. 1NF • A relation is in first normal form if and only if every attribute is single-valued for each tuple. • Remove all repeating groups • Create a flat file Itineraries (Confirmation_number, itinerary_date, AAdvantage_number, customer_name, address, city, state, zip_code, flight_date, flight_number, airline, departure_time, arrival_time, origin, destination, seat, class, food_service, movie)

  11. 1NFHow Would You Uniquely Identify Each Tuple? (MCWY123, Nov 11, 2001, ABC123456, Moki Mao, 1234 Kama`aina Place, Honolulu, HI, 96821, Jan 17, 2006, UA2503, United, 9:39 am, 11:08 am, Sacramento, Los Angeles, 12A, Y, B, 0) (MCWY123, Nov 11, 2001, ABC123456, Moki Mao, 1234 Kama`aina Place, Honolulu, HI, 96821, Jan 17, 2006, AA297, American, 2:00 pm, 5:56 pm, Los Angeles, Honolulu, 25H, F, M, 1) (MCWY123, Nov 11, 2001, ABC123456, Moki Mao, 1234 Kama`aina Place, Honolulu, HI, 96821, Jan 20, 2006, HA116, Hawaiian, 9:10 am, 9:45 am, Honolulu, Kahului, 9A, Y, B, 0)

  12. 2NF • A relation is in second normal form if and only if it is in first normal form and the nonkey attributes are fully functionally dependent on the key. Do we have partial dependencies? If so, where? 1NF: Itinearies (Confirmation_number, itinerary_date, AAdvantage_number, customer_name, address, city, state, zip_code, flight_date, flight_number, airline, departure_time, arrival_time, origin, destination, seat, class, food_service, movie)

  13. 2NF Itineraries (Confirmation_number, itinerary_date, AAdvantage_number, customer_name, address, city, state, zip_code) Flights (Flight_number, flight_date, airline, departure_time, arrival_time, origin, destination, seat, class, food_service, movie) Is this key adequate?

  14. 2NF Itineraries (Confirmation_number, itinerary_date, AAdvantage_number, customer_name, address, city, state, zip_code) Flights (Flight_number, flight_date, airline, departure_time, arrival_time, origin, destination, seat, class, food_service, movie) Itineraries (Confirmation_number, itinerary_date, AAdvantage_number, customer_name, address, city, state, zip_code) Reservations (Confirmation_number, flight_date, flight_number, airline, departure_time, arrival_time, origin, destination, seat, class, food_service, movie) Are there any partial dependencies?

  15. 2NF Confirmation_number, flight_number, flight_date, seat, class Flight_number, airline, departure_time, arrival_time, origin, destination, food_service, movie Itineraries (Confirmation_number, itinerary_date, AAdvantage_number, customer_name, address, city, state, zip_code) (Flight_number, flight_date, airline, departure_time, arrival_time, origin, destination, seat, class, food_service, movie) These attributes depend on confirmation _number and flight_number for their values Itineraries (Confirmation_number, itinerary_date, AAdvantage_number, customer_name, address, city, state, zip_code) Reservations (Confirmation_number, flight_date, flight_number, airline, departure_time, arrival_time, origin, destination, seat, class, food_service, movie) These attributes depend on flight_number for their values Are there any partial dependencies?

  16. 2NF Itineraries (Confirmation_number, itinerary_date, AAdvantage_number, customer_name, address, city, state, zip_code) Flights (Flight_number, flight_date, airline, departure_time, arrival_time, origin, destination, seat, class, food_service, movie) Therefore Itineraries (Confirmation_number, itinerary_date, AAdvantage_number, customer_name, address, city, state, zip_code) Reservations (Confirmation_number, flight_date, flight_number, seat, class) Flights (Flight_number, airline, departure_time, arrival_time, origin, destination, food_service, movie)

  17. 3NF • A relation is in third normal form if it is in second normal form and no nonkey attribute is transitively dependent on the key. • Remove transitive dependencies Itineraries (Confirmation_number, itinerary_date, AAdvantage_number, customer_name, address, city, state, zip_code) Reservations (Confirmation_number, flight_date, flight_number, seat, class) Flights (Flight_number, airline, departure_time, arrival_time, origin, destination, food_service, movie)

  18. 3NF • A relation is in third normal form if it is in second normal form and no nonkey attribute is transitively dependent on the key. • Remove transitive dependencies Itineraries (Confirmation_number, itinerary_date, AAdvantage_number, customer_name, address, city, state, zip_code) Reservations (Confirmation_number, flight_date, flight_number, seat, class) Flights (Flight_number, airline, departure_time, arrival_time, origin, destination, food_service, movie) Transitive dependencies

  19. 3NF • A relation is in third normal form if it is in second normal form and no nonkey attribute is transitively dependent on the key. • Remove transitive dependencies AAdvantage_number, customer_name, address zip_code city, state Itineraries (Confirmation_number, itinerary_date, AAdvantage_number, customer_name, address, city, state, zip_code) Reservations (Confirmation_number, flight_date, flight_number, seat, class) Flights (Flight_number, airline, departure_time, arrival_time, origin, destination, food_service, movie) Transitive dependencies

  20. 3NF Itineraries (Confirmation_number, itinerary_date, AAdvantage_number) Customers (AAdvantage_number, customer_name, address, zip_code) Zip_codes (Zip_code, city, state) Reservations (Confirmation_number, flight_number, flight_date, seat, class) Flights (Flight_number, airline, departure_time, arrival_time, origin, destination, food_service, movie)

More Related