Transcript DAT702 Normal Forms Normalization Rules

DAT702
Normal Forms
Normalization Rules
Normal Forms
Normal Forms, also called Normalization rules, are basically processes or
steps taken to allow for the efficient creation of Relational databases.
Following these rules lead an administrator towards a set of tables that:
• allow data to be organized in an efficient manner
• reduce disk space requirements for data storage
• minimize or eliminate redundant data
• greatly reduce the chance of data entry errors
• provide programmers with a stable data platform from
which to create efficient applications
The Rules
We will concentrate on the first three rules as they should be enough to
create proper data tables for most circumstances.
Rule 1: If a table contains repeating groups (fields) of data then these
groups (fields) should be removed and placed into another table
(child/foreign) as a single field.
Rule 2: If a table contains records that have repeating data (redundant)
then create a new table.
Rule 3: All fields within a table must relate directly to and describe the
Primary Key.
Form 0
(Flat File)
Table: Students
Student# Name
Course1 CourseDesc1
Course2 CourseDesc2
12345
Fred
DAT702 Database Admin
CDD301 DataComm
12346
Jane
OPS400 AS/400
OPS440
Unix Admin
Notice the repeating groups of data (Course1, Course2).
Adding course data limited to number of current fields
Violates all 3 Normal Form rules
Correcting with Rule #1
Table: Students
StudentNo Name
Course1
CourseDesc1
Course2
CourseDesc2
12345
Fred
DAT702 Database Admin
DCN386 DataComm
12346
Jane
OPS400 AS/400
OPS435
Unix Admin
Converted to 1st Normal Form
Table: Students
StudentNo
Name
CourseCode
CourseDesc
12345
Fred
DAT702
Database Admin
12345
Fred
DCN386
DataComm
12456
Jane
OPS400
AS/400
12456
Jane
OPS435
Unix Admin
Better: But still violates Rules 2 & 3
Correcting with Rule #2
StudentNo
Name
CourseCode
CourseDesc
12345
Fred
DAT702
Database Admin
12345
Fred
DCN386
DataComm
12456
Jane
OPS400
AS/400
12456
Jane
OPS435
Unix Admin
Converted to 2rd Normal Form
Table: Students
StudentNo(P) Name
Table: Courses
CourseCode(P)
StudentNo(F) CourseDesc
12345
Fred
DAT702
12345
Database Admin
12456
Jane
DCN386
12345
DataComm
OPS400
12456
AS/400
OPS435
12456
Unix Admin
Rule 3 violation
Primary – Foreign Key Relationship
Correcting with Rule #3
Table: Students
Table: Courses
StudentNo(P) Name
12345
Fred
12456
Jane
CourseCode(P)
StudentNo(F)
CourseDesc
DAT702
12345
Database Admin
DCN386
12345
DataComm
OPS400
12456
OPS435
12456
AS/400
Unix Admin
Converted to 3rd Normal Form
Table: Students
StudentNo(P) Name
12345
Fred
12456
Jane
Table: Stud_Courses
CourseCode(F)
Table: Courses
StudentNo(F)
CourseCode(P) Description
DAT702
12345
DAT702
Database Admin
DCN386
12345
DCN386
DataComm
OPS400
12456
OPS400
AS/400
OPS435
12456
Primary – Foreign Key Relationship