Week 12 November 14

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
Kamaaina 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 939A 1109A 12A
Los Angeles-Honolulu AA297 F 200P 556P 25H Ho
nolulu-Kahalui, Maui HA116 Y 910A 945A 9A Kaha
lui, Maui-Kona HA138 Y 910A 938A 17C Kona-Hono
lulu HA162 Y 1035A 1143A 17A Honolulu-San
Francisco HA12 F 130P 825P 22G San
Francisco-Sacramento UA5363 Y 1000P 1045P 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 AAd
vantage_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 AAd
vantage_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 AAd
vantage_number (fk)
?Have
0..
1..1
1..1
0..
Partial relationship
?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 AAd
vantage_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 AAd
vantage_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 AAd
vantage_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 AAd
vantage_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 index index-name on table-name (column-name
1 ascdesc, , column-namen ascdesc)
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
Physical Database Design Denormalization
Customers AAdvantage_number Customer_name Address
Zip_code (fk)
Itineraries Confirmation_number Itinerary_date AAd
vantage_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
17
Denormalization

• Duplicating attributes or combining relations
• Combining 11 relationships
• Duplicating nonkey attributes in 1M
  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 1M
  relationships to reduce joins
• Duplicating attributes in MN 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 AAd
vantage_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
20
(No Transcript)