Databases : Design of Relational Database Schemas

1
Databases Design of Relational Database
Schemas

• 2007, Fall
• Pusan National University
• Ki-Joune Li

2
Bad Design Anomalies

• Bad Design Example
• Redundancy
• Update Anomaly
• Deletion Anomaly

Title Year Length Film Type StudioName StarName
Star Wars 1977 124 Color Fox Carrie Fisher
Star Wars 1977 124 Color Fox Mark Hamill
Star Wars 1977 124 Color Fox Harrison Ford
Mighty Ducks 1991 104 Color Disney Emilio Estevez
Waynes World 1992 95 Color Paramount Dana Carvey
Waynes World 1992 95 Color Paramount Mike Meyers
3
Decomposing Relations

• Decomposition of Bad Relation
• A good way to remove the problem of bad relations
• Decomposition Lossless Decomposition
• A1 A2 An ? B1 B2 Bm , C1 C2 Ck
  such that B1 B2 Bm ? C1 C2 Ck A1
  A2 An and B1 B2 Bm ? C1 C2 Ck ?

4
Decomposing Relations Example

• Rtitle, year, length, filmType, studioName,
  starName ? title, year, length, filmType,
  studioName (R1), title, year, starName (R2)
• Redundancy
• Update Anomaly
• Deletion Anomaly

Title Year Length Film Type StudioName
Star Wars 1977 124 Color Fox
Mighty Ducks 1991 104 Color Disney
Waynes World 1992 95 Color Paramount
Title Year StarName
Star Wars 1977 Carrie Fisher
Star Wars 1977 Mark Hamill
Star Wars 1977 Harrison Ford
Mighty Ducks 1991 Emilio Estevez
Waynes World 1992 Dana Carvey
Waynes World 1992 Mike Meyers
5
Normal Form Conditions for Good Relation

• 1st Normal Form (1NF)
• 2nd Normal Form (2NF)
• 3rd Normal Form (3NF)
• Boyce-Codd Normal Form (BCNF)

6
1st Normal Form

• 1NF Every component of relation should be ATOMIC
• No Table in component
• No Set
• No List etc..

7
2nd Normal Form

• 2NF
• 1NF and
• None of the non-prime attributes of the relation
  is FD on a part of a candidate key
• Partial Dependency on non-prime attribute
• Example
• Player (Team, Number, TeamAddress, Name,
  Position)
• 1NF but not 2NF

C
A
B
8
Example
Employee Skill Current Work Location
Jones Typing 114 Main Street
Jones Shorthand 114 Main Street
Jones Whittling 114 Main Street
Roberts Light Cleaning 73 Industrial Way
Ellis Alchemy 73 Industrial Way
Ellis Juggling 73 Industrial Way
Harrison Light Cleaning 73 Industrial Way

• Candidate Key Employee, Skill
• Not 2ND
• Partial FD Employee ? Current Work Location
• Should be decomposed
• (Employee, Skill), (Employee, Current Work
  Location)

9
3rd Normal Form

• 2NF Every non-prime attributes of the relation
  must be non-transitively dependent on every
  candidate key
• Example
• Team (TeamName, Address, Manager SS ID,
  ManagerHireDate)
• 2NF but Not 3NF
• TeamName ? Manager SS ID ? ManagerHireDate
• To be decomposed
• (TeamName, Address, ManagerID), (Manager SS ID,
  ManagerHireDate)

B
C
A
10
Example 2NF but NOT 3NF
Tournament Year Winner Winner Date of Birth
Indiana Invitational 1998 Al Fredrickson 21 July 1975
Cleveland Open 1999 Bob Albertson 28 September 1968
Des Moines Masters 1999 Al Fredrickson 21 July 1975
Indiana Invitational 1999 Chip Masterson 14 March 1977

• Candidate Key Tournament, Year
• 2NF No Partial Dependency
• Not 3ND
• Transitive Functional Dependency
• Tournament, Year ? Winner ? Winner Date of
  Birth
• Should be decomposed
• (Tournament, Year, Winner), (Player, Birth date

11
Boyce-Codd Normal Form (BCNF)

• BCNF For every one of its non-trivial functional
  dependencies X ? Y, X is a super key
• Remember nontrivial means A is not a member of
  set X.
• Remember, a superkey is any superset of a key
  (not necessarily a proper superset)
• BCNF is slightly stronger than 3NF

12
Relationship between 1NF, 2NF, 3NF and BCNF
1NF
2NF
3NF
BCNF
13
Example 3NF but NOT BCNF

• A table to show the assignment of students
• Candidate Keys
• Prof. ID, Student ID
• Prof. SS ID, Student ID
• 1NF
• 2NF no partial FD on non-prime attributes on
  candidate key
• 3NF No transitive FD
• NOT BCNF
• Prof. ID ? Prof. SS ID Functional Dependency
  but not candidate key
• Should be decomposed (Prof. ID, Student ID),
  (Prof. ID, Prof. SS ID)

Prof. ID Prof. SS ID Student ID
1078 088-51-0074 31850
1078 088-51-0074 37921
1293 096-77-4146 46224
1480 072-21-2223 31850