the relational model and normalization (2) is 240 – database management lecture #8 2004-02-17...

The Relational Model and

Normalization (2)

IS 240 – Database ManagementLecture #8 2004-02-17

Prof. M. E. Kabay, PhD, CISSP

Norwich University

[email protected]

mailto:[email protected]

2 Copyright © 2004 M. E. Kabay. All rights reserved.

Topics

Review of Modification AnomaliesEssence of NormalizationClasses of Relations1st – 5th Normal Forms2nd Normal Form3rd Normal FormBoyce-Codd Normal Form (BCNF)4th Normal FormHomework


Review of Modification Anomalies

Member Book1:N

Member_ID

Member_Name

Member_Address

Member_Etc

Book_ID

Book_Name

Book_ISBN

Date_Borrowed

Member_ID

Date_Returned

Book_Location

What is the Member_ID if the book isn’t

out?

How can we delete a

member if there are still records

showing the books (s)he

borrowed and returned?


Essence of NormalizationWe run into problems when an entity tries to

include attributes that belong to different relations.

*“Problems occur when a relation contains facts about two different themes.”

E.g.,Storing information describing a book

along with information about when it was borrowed

Grouping attributes about a member along with information about what (s)he borrowed

*Kroenke p. 127


Normalization (cont’d)

When we try to group information about disparate entities into a single relation, we generate modification anomalies

Solution is to break up a relation into separate relations that don’t mix themes

E.g., store information about the member, information about the book, and information about each borrowing

in separate relations


Classes of Relations

1970s: Theorists analyzed anomaliesDeveloped names for different types of

problemsNamed relations as normal forms

1NF = first normal form2NF = second normal form3NF = third normal formBCNF = Boyce-Codd normal form4NF = fourth normal form5NF = fifth normal form (not used)


1NF – First Normal Form All the relations that we have defined are 1NF: Each cell contains a single value (no lists, tables,

arrays) All instances of an attribute (field, column) must be

instances of the same quality Every attribute (field, column) is uniquely identified

(same name in all tuples (records, rows) Every tuple (record, row) is unique Order of attributes and tuples is arbitrary – many

designs are functionally equivalent A group of one or more attributes (fields, columns) that

uniquely identifies a tuple (records, row) is called a key Every relation has at least one key No record (tuple, row) may duplicate another


1st – 5th Normal Forms

All relations as we have defined them are always in first normal form (1NF)

Other normal forms are subsets of 1NFWhen we split a relation that is in 1NF we

generate some other NF


Classes of Relations (cont’d)

1NF

2NF Anomalies

3NF Anomalies

BCNF Anomalies

4NF Anomalies

5NF Anomalies

DK/NF Anomalies

That’s all, folks!

Boyce-Codd

Domain/Key


1st Normal Form Anomalies

LIBRARYBook_IDBook_NameMember_IDMember_NameDate_BorrowedDate_ReturnedBook_Location

Book_ID (Book_Name, Book_Location)Member_ID Member_Name(Book_ID, Member_ID) Date_Borrowed, Date_Returned

ANOMALIES:Member has no books out?No one has borrowed a book?


2NF

BOOKBook_IDBook_NameBook_Location

MEMBERMember_IDMember_Name

BORROWINGBook_IDMember_IDDate_BorrowedDate_Returned

A relation is in 2NF if all its nonkey attributes are dependent on the entire key.

Keys in

RED


2nd Normal Form (cont’d)MAJOR_INFO

Student_IDMajor_NameDirector_ID

ANOMALIES:How will we know who is running a major

before a student registers for that major?How do we keep information about who’s running a major

if the last student registered withdraws from that major?How many records will we have to update

if we change the director of a major?

Major_Name Director_IDStudent_ID Major_Name Director_ID

Transitive dependency

Only one major per student

Only one director

per major


3NF

“A relation is in 3NF if it is in 2NF and has no transitive dependencies.”

STUDENT_MAJORStudent_IDMajor_Name

MAJOR_DIRECTORMajor_NameDirector_ID


3NF Anomalies

ElkinsCardiac110

DouglasICU90

CharliePulmonary70

BakerICU55

AbleCardiac50

NurseWardPatient

PATIENT

AbleCardiac110

Key: (Patient, Nurse)FD*: Nurse Ward

Nurse is a determinant yet it is not a possible (candidate) key

Anomaly: removing patient 110 will removefact that Elkins works in the Cardiac ward

3NF

*FD = Functional Dependency


Boyce-Codd Normal Form (BCNF)

A relation is in BCNF if every determinant is a candidate key.

Patient Nurse

50 Able

55 Baker

70 Charlie

90 Douglas

110 Elkins

110 Able

Ward Nurse

Cardiac Able

ICU Baker

Pulmonary Charlie

ICU Douglas

Cardiac Elkins

Patient

Nurse

Ward


Multi-Value Dependencies

Suppose a patient can have several diseasesPatient Disease

The patient can also have several insurersPatient Insurer

Storing complete information about patients would require all combinations of patient, disease and insurer; e.g.,

Patient_ID Disease InsurerA Liver PrudentialA Liver LibertyA Heart PrudentialA Heart Liberty


4th Normal Form

A relation is in 4NF if it is in BCNF and has no multi-value dependencies.

Patient Disease Patient Insurer


Homework

Review Chapter 5 material thoroughlyREQUIRED: by Tuesday 24 Feb 2004

For 7 points, complete Group 1 questions 5.11 through 5.17

Study to the end of Chapter 5 and then review the entire chapter carefully

By next Tuesday the 24th, Work on Ferrett et al. to complete up to

and including Project #6 in time for your mid-term exam

OPTIONAL: by THURSDAY THE 26TH FEB, complete Ch 5 FiredUp Project questions A through H for an extra 8 extra points.


DISCUSSION

(Reminder:Mid-term Exam

Thu 26th)

the relational model and normalization (2) is 240 – database management lecture #8 2004-02-17...

Documents