Normalization and Database Design: Anomalies, Functional Dependencies, and Normal Forms, Lecture notes of Computer Science

Download Normalization and Database Design: Anomalies, Functional Dependencies, and Normal Forms and more Lecture notes Computer Science in PDF only on Docsity!

Lecture 5.

Normalization and Design Review

Chapter outcomes

• By the end of this chapter you will be able to:

– Reasons for normalization: Anomalies

– Understand about functional dependencies

– Evaluate an entity against the first 3 normal forms

– Remove all repeating lists or arrays (

st

NF)

– Remove all partial dependencies (

nd

NF)

– Remove all transitive dependencies (

rd

NF)

– Understand the importance of design review

Motivation

• One of the most difficult tasks in developing a

DB is about designing it

• One simplest design approach is to use a big

table and store everything

– So no designing difficulty

• But what’s the problem with this?

– Anomalies

– Redundancies

Normalization

• Normalization is the process of removing

anomalies and redundancies from DB

• Followings are anomalies in DB design

– Insertion anomalies

– Update anomalies

– Deletion anomalies

Activity: Creating Employee Table

create database NormalizationTest; go use NormalizationTest; go create table Employee( eKey int primary key, eLastName varchar( 50 ), eFirstName varchar( 50 ), pName varchar( 50 ), pDescription varchar( 50 ) ); go insert into Employee values ( 1 , 'Smith', 'John', 'P1', 'DB Prj'), ( 2 , 'Doe', 'John', 'P2', 'Java Prj'), ( 3 , 'Smith', 'Carol', 'P1', 'DB Prj');

Activity: Insertion Anomaly

insert into Employee values (null, null, null, 'P3', 'Good project'); Error: Cannot insert the value NULL into column 'eKey'

Activity: Update Anomaly

update Employee set pDescription = 'Database Project' where pName = 'P1'; Result 2 row(s) affected.

Deletion Anomalies

• Where deleting one piece of data

inadvertently causes other data to be lost

E.g., If we delete employee Sanderson Lisa (e.g., she quit), then we will lose information about Project DB

Functional dependencies

• Functional dependencies

– Describe relationship among attributes in a

relation

• We write A - > B

– Read as: A determines B or B is functionally

dependent on A

– Means given a value of A, we can find one and

exactly one value of B

Functional dependencies examples

sId sName sDoB 1 John Smith 1999 - 01 - 12 2 Remesh Shah 1998 - 02 - 28 3 Susan Black 1999 - 08 - 10 4 John Smith 1999 - 01 - 12 5 John Doe 1998 - 02 - 28 Student Functional dependency sId - > {sName, sDob} sName - > sDoB Functional Dependency Diagram sId sName sDoB sName sDoB Note: Keys (super key, primary key, candidate key) determine all the attributes in a relation ERD RDM(relational data model)

Transitive dependency

• If A->B and B->C

– Attribute A must be the determinant of C.

– Attribute A transitively determines attribute C or

– C is transitively dependent on A

A B C

Normal Forms

• Each form was designed to eliminate one or more

of these anomalies

– First Normal Form

– Second Normal Form

– Third Normal Form

• The above 3 are most critical and the followings

are refinements

– Boyce Codd Normal Form

– Fourth Normal Form

– Fifth Normal Form

– Domain Key Normal Form

First Normal Form (1NF)

• A cell in a relation contains one and only one value.

• Disallows composite attributes, multivalued attributes or nested relations

• UNF to 1NF

• Nominate an attribute or group of attributes to act as the key for the unnormalized table.
• Identify repeating group(s) in unnormalized table which repeats for the key attribute(s).

UNF to 1NF/

• Remove repeating group by:

– Entering data into the empty columns of rows

containing repeating data (‘flattening’ the table)