1 / 20

Week 12 November 14

Week 12 November 14. Homework 3, Part 1 Physical Database Design and Denormalization. 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.

scantu
Download Presentation

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 Physical Database Design and Denormalization

  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

  3. Normalized Relations 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)

  4. Normalized Relations Itineraries (Confirmation_number, itinerary_date, AAdvantage_number) Customers (AAdvantage_number, customer_name, address, zip_code) Zip_codes (Zip_code, city, state) Reservations (Confirmation_number, Airline_code, flight_number, flight_date, seat, class) Flights (Airline_code, Flight_number, airline, departure_time, arrival_time, origin, destination, food_service, movie) Airlines (Airline_code, Airline_company) If Flight_number is a composite key of Airline_code and Flight_number

  5. Customers AAdvantage_number Customer_name Address Zip_code (fk) Itineraries Confirmation_number Itinerary_date AAdvantage_number (fk) Have 0..* 1..1 1..1 0..* Consist of Locate 1..* 1..1 1..1 Reservations Confirmation_number (fk) Airline_code (fk) Flight_number (fk) Flight_date Seat Class Zip_codes Zip_code City State 0..* Reserve 1..1 Flights Airline_code (fk) Flight_number Departure_time Arrival_time Origin Destination Food_service Movie Airlines Airline_code Airline Offer 0..* 1..1

  6. Customers AAdvantage_number Customer_name Address Zip_code (fk) Itineraries Confirmation_number Itinerary_date AAdvantage_number (fk) Have 0..* 1..1 1..1 0..* Consist of Partial relationship  Locate 1..* 1..1 1..1 Reservations Confirmation_number (fk) Airline_code (fk) Flight_number (fk) Flight_date Seat Class Zip_codes Zip_code City State 0..* Reserve 1..1 Flights Airline_code (fk) Flight_number Departure_time Arrival_time Origin Destination Food_service Movie Airlines Airline_code Airline Offer 0..* 1..1

  7. Customers AAdvantage_number Customer_name Address Zip_code (fk) Itineraries Confirmation_number Itinerary_date AAdvantage_number (fk) Have 0..* 1..1 1..1 Partial relationship 0..* Consist of  Locate 1..* 1..1 1..1 1..1 Reservations Confirmation_number (fk) Airline_code (fk) Flight_number (fk) Flight_date Seat Class Zip_codes Zip_code City State 0..* Reserve 1..1 Flights Airline_code (fk) Flight_number Departure_time Arrival_time Origin Destination Food_service Movie Airlines Airline_code Airline Offer 0..* 1..1

  8. Customers AAdvantage_number Customer_name Address Zip_code (fk) Itineraries Confirmation_number Itinerary_date AAdvantage_number (fk) Have Strong Entity 0..* 1..1 1..1 0..* Mandatory relationship Consist of  Locate 1..* 1..1 Reservations Confirmation_number (fk) Airline_code (fk) Flight_number (fk) Flight_date Seat Class Zip_codes Zip_code City State Weak Entity Key of Itineraries is part of the key for Reservations 0..* Reserve 1..1 Flights Airline_code (fk) Flight_number Departure_time Arrival_time Origin Destination Food_service Movie Airlines Airline_code Airline Offer 0..* 1..1

  9. Customers AAdvantage_number Customer_name Address Zip_code (fk) Itineraries Confirmation_number Itinerary_date AAdvantage_number (fk) Have 0..* 1..1 1..1 0..* Consist of  Locate 1..* 1..1 Reservations Confirmation_number (fk) Airline_code (fk) Flight_number (fk) Flight_date Seat Class Zip_codes Zip_code City State Strong Entity 0..* Partial relationship Reserve 1..1 Flights Airline_code (fk) Flight_number Departure_time Arrival_time Origin Destination Food_service Movie Airlines Airline_code Airline Offer Weak Entity 0..* 1..1

  10. Customers AAdvantage_number Customer_name Address Zip_code (fk) Itineraries Confirmation_number Itinerary_date AAdvantage_number (fk) Have 0..* 1..1 1..1 0..* Consist of  Locate 1..* 1..1 Reservations Confirmation_number (fk) Airline_code (fk) Flight_number (fk) Flight_date Seat Class Zip_codes Zip_code City State Weak Entity 0..* Reserve 1..1 Partial relationship Flights Airline_code (fk) Flight_number Departure_time Arrival_time Origin Destination Food_service Movie Strong Entity Airlines Airline_code Airline Offer 0..* 1..1

  11. Customers AAdvantage_number Customer_name Address Zip_code (fk) Itineraries Confirmation_number Itinerary_date AAdvantage_number (fk) Have 0..* 1..1 1..1 0..* Consist of  Locate 1..* 1..1 Reservations Confirmation_number (fk) Airline_code (fk) Flight_number (fk) Flight_date Seat Class Zip_codes Zip_code City State 0..* Logical Design Reserve 1..1 Flights Airline_code (fk) Flight_number Departure_time Arrival_time Origin Destination Food_service Movie Airlines Airline_code Airline Offer 0..* 1..1

  12. Physical Database Design • Implement constraints • Required data (not null) • Domain constraints (check, references) • Multiplicity • Entity integrity (primary key) • Referential integrity (foreign key) • General constraints (business rules) • How can the logical database design be “enhanced” for the physical design?

  13. Physical Database Design: Indexing • Index the primary key (if it is not the key of the file organization) • Do not index small relations • Add a secondary index to a heavily used secondary key • Add a secondary index to a frequently used foreign key AVOID INDEXING AN ATTRIBUTE OR RELATION THAT IS FREQUENTLY UPDATED • Avoid indexing an attribute if the query will retrieve a large portion of the tuples in a relation • Avoid indexing attributes that consist of long character strings

  14. Physical Database Design • In SQL… • For example… Create indexindex-nameontable-name (column-name1 {asc|desc}, …, column-namen {asc|desc}); Create index video_sales on video_sales (rating, distributor_code, category_code);

  15. Physical Database Design • Domain constraint • External reference (dynamic values) • Internal reference (static values) Index name create table… video_rating varchar2(5) constraint dc_product_code references products(product_code)); SQL Table name and column name (provided the column has been defined as unique when the table was created) create table… seat_capacity smallint constraint ck_seat_capacity check (seat_capacity between 1 and 12)); SQL Index name Static values

  16. Customers AAdvantage_number Customer_name Address Zip_code (fk) Itineraries Confirmation_number Itinerary_date AAdvantage_number (fk) Physical Database Design: Denormalization Have 0..* 1..1 1..1 0..* Consist of  Locate 1..* 1..1 Reservations Confirmation_number (fk) Airline_code (fk) Flight_number (fk) Flight_date Seat Class Zip_codes Zip_code City State 0..* Reserve 1..1 Flights Airline_code (fk) Flight_number Departure_time Arrival_time Origin Destination Food_service Movie Airlines Airline_code Airline Offer 0..* 1..1

  17. Denormalization • Duplicating attributes or combining relations • Combining 1:1 relationships • Duplicating nonkey attributes in 1:M relationships to reduce joins (creating partial or transitive dependencies) • Reference tables (introducing transitive dependencies by reducing the number of joins) • Duplicating foreign key attributes in 1:M relationships to reduce joins • Duplicating attributes in M:N relationships to reduce joins

  18. Denormalization • Duplicating attributes or combining relations (cont.) • Introducing repeating groups (if the number of occurrences is known and/or constant) • Creating extract tables (in an extreme case, an unnormalized relation) - frees computing resources • Introduction of “codes” to • Simplify the composite key • Retain the original sequence

  19. Customers AAdvantage_number Customer_name Address Zip_code (fk) Itineraries Confirmation_number Itinerary_date AAdvantage_number (fk) Have 0..* 1..1 1..1 0..* Consist of  Locate 1..* 1..1 Reservations Confirmation_number (fk) Airline_code (fk) Flight_number (fk) Flight_date Seat Class Zip_codes Zip_code City State 0..* Reserve 1..1 Flights Airline_code (fk) Flight_number Departure_time Arrival_time Origin Destination Food_service Movie Airlines Airline_code Airline Offer 0..* 1..1

More Related