Scribd
Upload
6 views

Course1Module11Lesson3

Uploaded by

Yanet Cesaire
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
6 views

Course1Module11Lesson3

Uploaded by

Yanet Cesaire
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 12

Information Systems Program

Module 11
Normalization Concepts and Practice

Lesson 3: Normal Forms


Lesson Objectives
• Understand the nature of normal forms
• Define Boyce-Codd Normal Form (BCNF)
• Apply BCNF to a list of functional dependencies

Information Systems Program


Normalization

Table Apply Normal Revised


Design Form Table
FDs (FD Pattern) Design

• Detect violations
• Split table

Information Systems Program


Normalization Simplification
• Determination of a complete and minimal list of
FDs
• Determination of unique columns from FDs
• Details too complex and specialized for this
course

Information Systems Program


Relationships of Normal Forms
1NF

2NF

3NF/BCNF

4NF

5NF

DKNF

Information Systems Program


Boyce-Codd Normal
Form (BCNF)
Every
Apply with
Simple determinant
BCNF
definition must be
procedure
unique.
6

Information Systems Program


Big University Database Table

StdNo StdEmail StdClass OfferNo OffYear EnrGrade CourseNo CrsDesc

S1 joe@bigu.edu JUN O1 2017 3.5 C1 DB

S1 sue@bigu.edu JUN O2 2017 3.3 C2 VB

S2 mj@bigu.edu JUN O3 2018 3.1 C3 OO

S2 tom@bigu.edu JUN O2 2017 3.4 C2 VB

Information Systems Program


BCNF Example
 Unique columns in the big university table
– <StdNo, OfferNo>
– <StdEmail, OfferNo>
 Many BCNF violations
 StdNo  StdCity, StdClass, StdEmail
 StdEmail  StdNo
 OfferNo  OffTerm, OffYear, CourseNo
 CourseNo  CrsDesc
– StdNo, OfferNo  EnrGrade

Information Systems Program


BCNF Procedure

Group FDs Define tables Merge tables

• Same • Each FD • Containment


determinant group • Prevents
• Determinant excessive
as PK splitting
• FKs

Information Systems Program


BCNF Procedure Example

Group FDs Define tables Merge tables

• StdNo • Student • Student


• StdEmail • StudentEmail • StudentEmail
• OfferNo • Offering
• CourseNo • Course
• OfferNo, • Enrollment
StdNo

FKs in step 2
• Student.StdEmail,
• StudentEmail.StdNo
• Offering.CourseNo
• Enrollment.StdNo, Enrollment.OfferNo
10

Information Systems Program


Merging Tables
• Step 2 defines too many tables when two columns
determine each other.
– StdNo  StdEmail
– StdEmail  StdNo
• Merge tables with a containment relationship
– Student(StdNo, StdEmail, StdCity, StdClass)
– StudentEmail(StdEmail, StdNo)
– Merge tables because Student contains columns of StdEmail
• Multiple unique columns do not violate BCNF

11

Information Systems Program


Summary
• Covered general idea of normal forms and details of
BCNF
• Know BCNF definition and simplified procedure
• Study examples for work on practice and graded
problems

12

Information Systems Program

You might also like