Logical Database Design A Normalization Process

1
Logical Database DesignA Normalization Process

• Shaun Simpson
• MIS 372
• Database Management

2
Outline

• What is Logical Database Design?
• A Design Overview
• Relational Model and Basic Concepts
• Why Normalization?
• Basic Terminology for Normalization Theory

3
What is Logical Database Design?
? Transform

• Logical database design - A formal process to
  transform a conceptual model (e.g., an E-R model)
  into a logical (normalized) database model
• Foundation of Logical Database Design
  Normalization Theory - a theoretical methodology
  to remove potential design errors (anomalies)!!

4
Logical Database DesignAn Overview
Conceptual Data Model An E-R Diagram
First Normal forms - ??th Normal Form
Normalization
Relation Integration
Combine end-user group views into a global view
(i.e.,a relation)
Logical Data Model (Flat Tables)
5
Relational Data ModelBasic Concepts

• A relation -- a 2-D table with rows and columns

6
Relational Data Model Basic Concepts (contd)

• Basic Approach -- Using the Embedded Key Scheme

Embedded Keys
7
Relational Data Model Basic Properties of a
Relation

• Relation - A flat two-dimensional table
• Each row is uniquely identified (No duplicates!)
• the order of rows is insignificant!!
• the order of columns is insignificant!!
• All columns are atomic (single values)
• All values in each column drawn from the same
  value domain (meaning is same).

8
Why Normalization?To avoid Anomalies and Enhance
...

• To avoid operational anomalies
• Addition anomaly - there is a difficulty in
  adding data when facts already exist
• Update anomaly - change in data must be recorded
  in more than one place in database
• Deletion anomaly - information is unexpectedly
  lost due to some deletion of data
• To enhance data integrity
• values must be Consistent and Accurate!!
• To reduce data redundancy
• No duplications except the connection keys!

9
Data AnomaliesExamples
Addition
Deletion
Update
10
Normalization TheoryBasic Terminology - A Review

• Key Any attribute or a set of attributes that
  can serve as a unique identifier to a row in a
  relation.
• Candidate Key - Any possible unique identifiers
  found in a relation
• Primary Key - the identifier that is chosen to
  serve as the key for each row in a table.
• Foreign Key - Any attribute or a set of
  attributes that serves as a primary key in other
  relations

11
Normalization TheoryTerminology Review - An
Example

• STUDENT (SID, Name, SSN, Phone, Address, ZIP)
• Candidate Key(s)
• Primary Key
• Foreign Key
• ZIPTABLE (ZIP, City, State)
• Candidate Key(s)
• Primary Key
• Foreign Key

12
Normalization TheoryTerminology Review - An
Example

• STUDENT (SID, Name, SSN, Phone, Address, ZIP)
• Candidate Key(s) SID, SSN
• Primary Key can be either SID or SSN
• Foreign Key ZIP
• ZIPTABLE (ZIP, City, State)
• Candidate Key(s) ZIP
• Primary Key ZIP
• Foreign Key none

13
Normalization TheoryMore on Basic Terminology

• In a relation, there are two kinds of attributes
• key attribute - an attribute that is used as a
  key or part of the key
• non-key attribute - otherwise.
• Ex EMPLOYEE (EID, Name, B_date,Phone,SSN,Addr)
• TRANSCRIPT (SID, CourseID,
  Semester,Year,Grade)
• Simple Key A key that contains only one
  attribute
• Composite (Compound) Key A key that includes
  more than one attribute