Dagmar Kusiak

1
Chapter 5

• Dagmar Kusiak

2
Chapter Objectives

• Database Design
• Detailed Conceptual Design
• Normalization
• Entities
• Attributes
• Relationships

3
Ended with this relationship
4
Database Design
5
Normalization

• First Normal Form
• No repeated rows
• Is this in 1NF?

6
Normalization Continued

• FIX IT

7
Normalization Continued
8
Normalization continued

• 2NF
• In 1NF and all primary keys determine all
  non-primary keys
• Example SSNO-gt lastname, firstname, so SSNO
  determines lastname, firstname or lastname,
  firstname is dependant on SSNO.
• Example lastname, firstname, phoneno -gt address,
  city, zipcode

9
Normalization continued

• In 2NF?

10
Normalization continued
11
Normalization continued

• 3NF
• In 2NF and no transitive dependencies (A-gtB,
  B-gtC) and has no non primary keys that follow
  this rule (A, B-gtC)
• A-gtB, B-gtC (A determines B, B determines C.
  Dont want this among any primary or non primary
  keys.
• Example Stockcode -gt Nation -gt Exch_rate
• C -gt A, B
• Example Zipcode -gt City, State

12
Normalization continued

• 3NF?
• Stock code -gt Nation -gt Exchange rate

13
Normalization continued
14
Normalization continued

• 3NF?
• Zipcode -gt City, State

15
Normalization continued