Database Design and Normalization, Lecture notes of Mathematics

Download Database Design and Normalization and more Lecture notes Mathematics in PDF only on Docsity!

Database Design and Development

Unit 4

Normalization (1 of 2)

Lecture 7

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 (1 st^ NF)

– Remove all partial dependencies (2 nd^ NF)

– Remove all transitive dependencies (3 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 (dư thừa) from

DB

• Followings are anomalies in DB design

– Insertion anomalies

– Update anomalies

– Deletion anomalies

Activity: Creating Employee Table

Activity: Insertion Anomaly

Activity: Update Anomaly

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- 2 Remesh Shah 1998-02- 3 Susan Black 1999-08- 4 John Smith 1999-01- 5 John Doe 1998-02-

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

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

Vocabularies