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

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

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

• 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

• 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)