normalization & er model

8/3/2019 Normalization & ER Model

1/145

KEYS


2/145

Chapter ObjectivesThe purpose of normailization

Data redundancy and Update AnomaliesFunctional Dependencies

The Process of Normalization

First Normal Form (1NF)Second Normal Form (2NF)

Third Normal Form (3NF)


3/145

Chapter Objectives (2)General Definition ofSecond and Third

Normal Form

Boyce-Codd Normal Form (BCNF)

Fourth Normal Form (4NF)

Fifth Normal Form (5NF)


4/145

What is Normalization?Database designed based on the E-R model may have some amount of

Inconsistency

Uncertainty

Redundancy

To eliminate these draw backs some refinement has to be done on thedatabase.

This Refinement process is called Normalization

Defined as a step-by-step process of decomposing a complex relationinto a simple and stable data structure.

The formal process that can be followed to achieve a good databasedesign

Also used to check that an existing design is of good quality

The different stages of normalization are known as normal forms

To accomplish normalization we need to understand the concept of

Functional Dependencies.


5/145

The Purpose of Normalization

Normalization is a technique for producing a set of

relations with desirable properties, given the data

requirements of an enterprise.

The process of normalization is a formal method that

identifies relations based on their primary or candidatekeys and the functional dependencies among their

attributes.


6/145

Update AnomaliesRelations that have redundant data may have

problems called update anomalies, which are

classified as ,Insertion anomalies

Deletion anomalies

Modification anomalies


7/145

Example of Update Anomalies

To insert a new staff with branchNo B007 into the StaffBranch

relation;

To delete a tuple that represents the last member of staff located

at a branch B007;

To change the address of branch B003.

staffNo sName position salary branchNo bAddress

SL21 JohnWhite Manager 30000 B005 22 Deer Rd, London

SG37 Ann Beech Assistant 12000 B003 163 Main St,Glasgow

SG14 David Ford Supervisor 18000 B003 163 Main St,Glasgow

SA9 Mary Howe Assistant 9000 B007 16 Argyll St, Aberdeen

SG5 Susan Brand Manager 24000 B003 163 Main St,Glasgow

SL41 Julie Lee Assistant 9000 B005 22 Deer Rd, London

StaffBranch

Figure 1 StraffBranch relation


8/145

Example of Update Anomalies (2)

staffNo sName position salary branceNo

SL21 JohnWhite Manager 30000 B005

SG37 Ann Beech Assistant 12000 B003

SG14 David Ford Supervisor 18000 B003

SA9 Mary Howe Assistant 9000 B007

SG5 Susan Brand Manager 24000 B003

SL41 Julie Lee Assistant 9000 B005

branceNo bAddress

B005 22 Deer Rd, London

B007 16 Argyll St, Aberdeen

B003 163 Main St,Glasgow

Figure 2 Straff and Branch relations

Staff

Branch


9/145

Functional DependenciesFunctional dependency describes the relationship betweenattributes in a relation.

For example, if A and B are attributes of relation R, and B is

functionally dependent on A ( denoted A B), if each value ofA is associated with exactly one value of B. ( A and B may each

consist of one or more attributes.)

A B

B is functionally

dependent on A

DeterminantRefers to the attribute or group of attributes on the

left-hand side of the arrow of a functional dependency


10/145

Trival functional dependency means that the right-handside is a subset ( not necessarily a proper subset) of the left-

hand side.

Functional Dependencies (2)

For example: (See Figure 1)

staffNo, sName sNamestaffNo, sName staffNo

They do not provide any additional information about possible integrity

constraints on the values held by these attributes.

We are normally more interested in nontrivial dependenciesbecause theyrepresent integrity constraints for the relation.


11/145


Main characteristics of functional dependencies in normalization

Have a one-to-one relationship between attribute(s) on the

left- and right- hand side of a dependency;

hold forall time;

are nontrivial.


12/145

Identifying the primary key

Functional dependency is a property of the meaning or

semantics of the attributes in a relation. When a functional

dependency is present, the dependency is specified as a

constraintbetween the attributes.

An important integrity constraint to consider first is the

identification of candidate keys, one of which is selected to

be the primary key for the relation using functional dependency.



13/145


Inference RulesA set of all functional dependencies that are implied by a given

set of functional dependencies X is called closure of X, written

X+. A set of inference rule is needed to compute X+ from X.

Armstrongs axioms

1. Relfexivity: If B is a subset of A, them A B

2. Augmentation: If A B, then A, C B

3. Transitivity: If A B and B C, then A C4. Self-determination: A A

5. Decomposition: If A B,C then A B and A C

6. Union: If A B and A C, then A B,C

7. Composition: If A

B and C

D, then A,C

B,


14/145


Minial Sets ofFunctional Dependencies

A set of functional dependencies X is minimal if it satisfies

the following condition:

Every dependency in X has a single attribute on itsright-hand side

We cannot replace any dependency A B in X with

dependency C B, where C is a proper subset of A, and

still have a set of dependencies that is equivalent to X.

We cannot remove any dependency from X and still

have a set of dependencies that is equivalent to X.


15/145


Example of A Minial Sets ofFunctionalDependencies

A set of functional dependencies for the StaffBranch relation

satisfies the three conditions for producing a minimal set.

staffNo sName

staffNo position

staffNo salary

staffNo branchNostaffNo bAddress

branchNo bAddress

branchNo, position salary

bAddress, position salary


16/145

Functional dependencyIn a given relation R, X and Y are attributes. Attribute Y isfunctionally dependent on attribute X if each value of X determinesEXACTLY ONE value ofY, which is represented as X -> Y (X can becomposite in nature).

We say here x determines y or y is functionally dependent on x

XpY does not imply YpX

If the value of an attribute Marks is known then the value of anattribute Grade is determined since MarkspGrade

Types of functional dependencies:

Full Functional dependency

Partial Functional dependency

Transitive dependency


17/145

Functional Dependencies

Consider the following Relation

REPORT (STUDENT#,COURSE#, CourseName, IName, Room#,

Marks, Grade)

STUDENT# - Student Number

COURSE# - Course Number

CourseName - Course Name

IName - Name of the Instructor who delivered the course

Room# - Room number which is assigned to respectiveInstructor

Marks - Scored in Course COURSE# by Student STUDENT#

Grade - obtained by Student STUDENT# in Course COURSE#


18/145

Functional Dependencies- From

the previous example

STUDENT# COURSE# Marks

COURSE# CourseName,

COURSE# IName (Assuming one course is

taught by one and only one Instructor)

IName Room# (Assuming each Instructor hashis/her own and non-shared room)

Marks Grade


19/145

Dependency diagram

Assumptions: Each course has only one lecturer and each lecturer has a

room.

Grade is determined from Marks.

Report( S#,C#,SName,CTitle,LName,Room#,Marks,Grade)

S# SName

C# CTitle,

C# LName

LName Room#C# Room#

S# C# Marks

Marks Grade

S# C# GradeMarks Grade

CTitleSName

LName

Room#

S# C#


20/145

Full dependencies

X and Y are attributes.X Functionally determines Y

Note: Subset of X should not functionally determine Y


21/145

Partial dependencies

X and Y are attributes.Attribute Y is partially dependent on the attribute X only if it is dependent on

a sub-set of attribute X.


22/145

Transitive dependencies

X Y and Z are three attributes.X -> Y

Y-> Z

= > X - > Z


23/145

Need for NormalizationStudent_Course_Result Table

Student_Details Course_Details Result_Details

101 Davis 11/4/1986 M4

Applied

Mathematics

Basic

Mathematics 7

11/11/200

4 82 A

102 Daniel 11/6/1987 M4

Applied

Mathematics

Basic

Mathematics 7

11/11/200

4 62 C

101 Davis 11/4/1986 H6 American History 4

11/22/20

04 79 B

103 Sandra 10/2/1988 C3 Bio Chemistry

BasicChemistr

y 11

11/16/20

04 65 B

104 Evelyn 2/22/1986 B3 Botany 8

11/26/20

04 77 B

102 Daniel 11/6/1987 P3 Nuclear Physics Basic Physics 13

11/12/20

04 68 B

105 Susan 8/31/1985 P3 Nuclear Physics Basic Physics 13

11/12/20

04 89 A

103 Sandra 10/2 / 1988 B4 Zoology 5

11/27/20

04 54 D

105 Susan 8/31/1985 H6 American History 4

11/22/20

04 87 A

104 Evelyn 2/22/1986 M4

Applied

Mathematics

Basic

Mathematics 7

11/11/200

4 65 B

Insert , Delete, Update Anomaly and Data Duplication


24/145

The Process of Normalization Normalization is often executed as a series of steps. Each step

corresponds to a specific normal form that has known properties.

As normalization proceeds, the relations become progressivelymore restricted in format, and also less vulnerable to update

anomalies.

For the relational data model, it is important to recognize thatit is only first normal form (1NF) that is critical in creating

relations. All the subsequent normal forms are optional.


25/145

First Normal Form (1NF)Unnormalized form (UNF)A table that contains one or more repeating groups.

ClientNo cName propertyNo pAddress rentStart rentFinish rent ownerNo oName

CR76John

kay

PG4

PG16

6 lawrence

St,Glasgow

5 Novar Dr,

Glasgow

1-Jul-00

1-Sep-02

31-Aug-01

1-Sep-02

350

450

CO40

CO93

TinaMurphy

TonyShaw

CR56Aline

Stewart

PG4

PG36

PG16

6 lawrence

St,Glasgow

2 Manor Rd,

Glasgow

5 Novar Dr,

Glasgow

1-Sep-99

10-Oct-00

1-Nov-02

10-Jun-00

1-Dec-01

1-Aug-03

350

370

450

CO40

CO93

CO93

TinaMurphy

TonyShaw

TonyShaw

Figure 3 ClientRental unnormalized table

Repeating group = (propertyNo, pAddress,rentStart, rentFinish, rent, ownerNo, oName)


26/145

Definition of 1NFFirst Normal Form is a relation in which the intersection of each

row and column contains one and only one value.

There are two approaches to removing repeating groups fromunnormalized tables:

1. Removes the repeating groups by entering appropriate data

in the empty columns of rows containing the repeating data.

2. Removes the repeating group by placing the repeating data,

along with a copy of the original key attribute(s), in a separate

relation. A primary key is identified for the new relation.


27/145

First Normal Form: 1NFA relation schema is in 1NF:

if and only if all the attributes of the relationR are atomic in nature.

Atomic:the smallest level to which datamay be broken down and remain

meaningful


28/145

1NF ClientRental relation with the first approach

ClientNo propertyNo cName pAddress rentStart rentFinish rent ownerNo oName

CR76 PG4John

Kay

6 lawrence

St,Glasgow1-Jul-00 31-Aug-01 350 CO40

TinaMurphy

CR76 PG16John

Kay

5 Novar Dr,

Glasgow1-Sep-02 1-Sep-02 450 CO93

TonyShaw

CR56 PG4Aline

Stewart

6 lawrence

St,Glasgow1-Sep-99 10-Jun-00 350 CO40

TinaMurphy

CR56 PG36Aline

Stewart

2 Manor Rd,

Glasgow10-Oct-00 1-Dec-01 370 CO93

TonyShaw

CR56 PG16Aline

Stewart

5 Novar Dr,

Glasgow1-Nov-02 1-Aug-03 450 CO93

TonyShaw

Figure 4 1NF ClientRental relation with the first approach

The ClientRental relation is defined as follows,ClientRental ( clientNo, propertyNo, cName, pAddress, rentStart, rentFinish, rent,

ownerNo, oName)

With the first approach, we remove the repeating group

(property rented details) by entering the appropriate clientdata into each row.


29/145

1NF ClientRental relation with the second approach

With the second approach, we remove the repeating group

(property rented details) by placing the repeating data along with

a copy of the original key attribute (clientNo) in a separte relation.

Client (clientNo, cName)PropertyRentalOwner (clientNo, propertyNo, pAddress, rentStart,

rentFinish, rent, ownerNo, oName)

ClientNo cName

CR76 John Kay

CR56 Aline Stewart

ClientNo propertyNo pAddress rentStart rentFinish rent ownerNo oName

CR76 PG46 lawrence

St,Glasgow1-Jul-00 31-Aug-01 350 CO40

TinaMurphy

CR76 PG16

5 Novar Dr,

Glasgow 1-S

ep-02 1-S

ep-02 450 CO93

Tony

Shaw

CR56 PG46 lawrence

St,Glasgow1-Sep-99 10-Jun-00 350 CO40

TinaMurphy

CR56 PG362 Manor Rd,

Glasgow10-Oct-00 1-Dec-01 370 CO93

TonyShaw

CR56 PG165 Novar Dr,

Glasgow1-Nov-02 1-Aug-03 450 CO93

TonyShaw

Figure 5 1NF ClientRental relation with the second approach


30/145

Full functional dependency

Full functional dependency indicates that if A andB are

attributes of a relation, B is fully functionally dependent on

A ifB is functionally dependent on A, but not on any proper

subset of A.

A functional dependency AB is partially dependent if there is

some attributes that can be removed from A and the dependency

still holds.


31/145

Student_Course_Result TableStudent_Details Course_Details Results

101 Davis 11/4/1986 M4

Applied

Mathematics

Basic

Mathematics 7 11/11/2004 82 A

102 Daniel 11/6/1987 M4

Applied

Mathematics

Basic

Mathematics 7 11/11/2004 62 C

101 Davis 11/4/1986 H6 American History 4 11/22/2004 79 B

103 Sandra 10/2/1988 C3 Bio Chemistry Basic Chemistry 11 11/16/2004 65 B

104 Evelyn 2/22/1986 B3 Botany 8 11/26/2004 77 B

102 Daniel 11/6/1987 P3 Nuclear Physics Basic Physics 13 11/12/2004 68 B

105 Susan 8/31/1985 P3 Nuclear Physics Basic Physics 13 11/12/2004 89 A

103 Sandra 10/2 /1988 B4 Zoology 5 11/27/2004 54 D

105 Susan 8/31/1985 H6 American History 4 11/22/2004 87 A

104 Evelyn 2/22/1986 M4

Applied

Mathematics

Basic

Mathematics 7 11/11/2004 65 B


32/145

Table in 1NF

Student_Course_Result Table in First

Normal Form


33/145

Second Normal Form (2NF)

Second normal form (2NF) is a relation that is in first

normal form and every non-primary-key attribute is fully

functionally dependent on the primary key.

The normalization of 1NF relations to 2NF involves the

removal ofpartial dependencies. If a partial dependency

exists, we remove the function dependent attributes from

the relation by placing them in a new relation along witha copy of their determinant.


34/145

2NF ClientRental relation

The ClientRental relation has the following functional

dependencies:

fd1 clientNo, propertyNo rentStart, rentFinish (Primary Key)

fd2 clientNo cName (Partial dependency)fd3 propertyNo pAddress, rent, ownerNo, oName (Partial dependency)

fd4 ownerNo oName (Transitive Dependency)

fd5 clientNo, rentStart propertyNo, pAddress,

rentFinish, rent, ownerNo, oName (Candidate key)

fd6 propertyNo, rentStart clientNo, cName, rentFinish (Candidate key)


35/145


After removing the partial dependencies, the creation of the three

new relations called Client, Rental, and PropertyOwner

ClientNo cName

CR76 John Kay

CR56 Aline Stewart

Client

ClientNo propertyNo rentStart rentFinish

CR76 PG4 1-Jul-00 31-Aug-01

CR76 PG16 1-Sep-02 1-Sep-02

CR56 PG4 1-Sep-99 10-Jun-00

CR56 PG36 10-Oct-00 1-Dec-01

CR56 PG16 1-Nov-02 1-Aug-03

Rental

propertyNo pAddress rent ownerNo oName

PG4 6 lawrence St,Glasgow 350 CO40 Tina Murphy

PG16 5 Novar Dr, Glasgow 450 CO93 Tony Shaw

PG36 2 Manor Rd, Glasgow 370 CO93 Tony Shaw

PropertyOwner

Client (clientNo, cName)

Rental (clientNo, propertyNo, rentStart, rentFinish)

PropertyOwner (propertyNo, pAddress, rent, ownerNo, oName)

Figure 6 2NF ClientRental relation


36/145

Second Normal Form: 2NF

A Relation is said to be in Second Normal Form if andonly if :

It is in the First normal form, and

No partial dependency exists between non-key

attributes and key attributes. An attribute of a relation R that belongs to the candidate key of R is said to bea key attribute and that which doesnt is a non-key attribute

To make a table 2NF compliant, we have to remove all the partial dependencies

Note : - All partial dependencies are eliminated


37/145

Key and Non-Key AttributesStudent_Course_Result(Student#, Course#, StudentName,

DateOfBirth, CourseName, PreRequisite, DurationInDays,

DateOfExam, Marks, Grade)

Student#

Course#Is a KEY Attribute

Is NON-KEY Attribute

Marks

Grade

DateOfBirth

StudentName

CourseName

PreRequisite

DateOfExam

DurationInDays


38/145

Second Normal FormSTUDENT# is key attribute forStudent

COURSE# is key attribute for CourseSTUDENT# COURSE# together form the composite key

attributes forStudent_Course_Result relation.

Other attributes like StudentName (Student Name), DateOfBirth,

CourseName, PreRequisite, DurationInDays, DateOfExam,

Marks and Grade are non-key attributes.

To make this table 2NF compliant, we have to remove all the

partial

Dependencies.

Student #, Course# -> Marks, Grade

Student# -> StudentName, DateOfBirth,

Course# -> CourseName, PreRequisite, DurationInDays,

DateOfExam


39/145

Second Normal Form

S#,C# Marks

S#,C# Grade

S# StudentName

S# DOB

Fully Functionallydependent on composite

Primary key

Partial Dependency with

respect to the Primary Key

Partial Dependency withrespect to the Primary Key

C# DateOfExam

C# CourseName

C# Prerequisite

C# Duration


40/145

Second Normal Form - Tables in 2 NF

STUDENT TABLEStudent# StudentName Dateof Birth

101 Davis 04-Nov-1986

102 Daniel 06-Nov-1987

103 Sandra 02-Oct-1988

104 Evelyn 22-Feb-1986

105 Susan 31-Aug-1985

106 Mike 04-Feb-1987

107 Juliet 09-Nov-1986

108 Tom 07-Oct-1986

109 Catherine 06-Jun-1984

Course

#

Course

Name

Pre

Req

uisite

Durat

ion

Date Of

Exam

M1

Basic

Mathematics 11 11-Nov-04

M4

Applied

Mathematics M1 7 11-Nov-04

H6

American

History 4 22-Nov-

04

C1

Basic

Chemistry 5 16-Nov-04

C3 Bio Chemistry C1 11 16-Nov-04

B3 Botany 8 26-Nov-04

P1 Basic Physics 8 12-Nov-04

P3

Nuclear

Physics P1 13 12-Nov-04

B4 Zoology 5 27-Nov-04

COURSE TABLE


41/145

Second Normal form Tables in 2 NF

Student# Course# Marks Grade

101 M4 82 A

102 M4 62 C101 H6 79 B

103 C3 65 B

104 B3 77 B

102 P3 68 B

105 P3 89 A

103 B4 54 D

105 H6 87 A

104 M4 65 B

Report


42/145

Second Normal Form - Tables in 2 NF

STUDENT TABLEStudent# StudentN

ame

DateofBirth

101 Davis 04-Nov-1986

102 Daniel 06-Nov-1987

103 Sandra 02-Oct-1988

104 Evelyn 22-Feb-1986

105 Susan 31-Aug-1985

106 Mike 04-Feb-1987

107 Juliet 09-Nov-1986

108 Tom 07-Oct-1986

109 Catherine 06-Jun-1984

COURSE TABLECours

e#CourseName

PreRequisit

e

DurationInDays

M1 Basic Mathematics 11

M4

Applied

Mathematics

M1

7

H6 American History 4

C1 Basic Chemistry 5

C3 Bio Chemistry C1 11

B3 Botany 8

P1 Basic Physics 8

P3 Nuclear Physics P1 13

B4 Zoology 5


43/145

Second Normal form Tables

in 2 NFStudent# Course# Marks Grade

101 M4 82 A

102 M4 62 C

101 H6 79 B

103 C3 65 B

104 B3 77 B

102 P3 68 B

105 P3 89 A

103 B4 54 D

105 H6 87 A

104 M4 65 B

Report


44/145

2 NF

Course# DateOfExam

M4 11-Nov-04

H6 22-Nov-04

C316-Nov-

04

B3 26-Nov-04

P3 12-Nov-04

B4 27-Nov-04

Exam_Date Table


45/145

Third Normal Form (3NF)Transitive dependency

A condition where A, B, and C are attributes of a relation such that

if A B and B C, then C is transitively dependent on A via B

(provided that A is not functionally dependent on B or C).

Third normal form (3NF)

A relation that is in first and second normal form, and in which

no non-primary-key attribute is transitively dependent on the

primary key.

The normalization of 2NF relations to 3NF involves the removal

of transitive dependencies by placing the attribute(s) in a new

relation along with a copy of the determinant.


46/145


The functional dependencies for the Client, Rental and

PropertyOwnerrelations are as follows:

Client

fd2 clientNo cName (Primary Key)

Rentalfd1 clientNo, propertyNo rentStart, rentFinish (Primary Key)

fd5 clientNo, rentStart propertyNo, rentFinish (Candidate key)

fd6 propertyNo, rentStart clientNo, rentFinish (Candidate key)

PropertyOwnerfd3 propertyNo pAddress, rent, ownerNo, oName (Primary Key)

fd4 ownerNo oName (Transitive Dependency)


47/145


The resulting 3NF relations have the forms:

Client (clientNo, cName)

Rental (clientNo, propertyNo, rentStart, rentFinish)

PropertyOwner (propertyNo, pAddress, rent, ownerNo)Owner (ownerNo, oName)


48/145

ClientNo cName

CR76 John Kay

CR56 Aline Stewart

Client

ClientNo propertyNo rentStart rentFinish

CR76 PG4 1-Jul-00 31-Aug-01

CR76 PG16 1-Sep-02 1-Sep-02

CR56 PG4 1-S

ep-99 10-Jun-00CR56 PG36 10-Oct-00 1-Dec-01

CR56 PG16 1-Nov-02 1-Aug-03

Rental

propertyNo pAddress rent ownerNo

PG4 6 lawrence St,Glasgow 350 CO40

PG16 5 Novar Dr, Glasgow 450 CO93

PG36 2 Manor Rd, Glasgow 370 CO93

PropertyOwner


ownerNo oName

CO40 Tina Murphy

CO93 Tony Shaw

Owner

Figure 7 2NF ClientRental relation


49/145

Third Normal Form: 3 NFA relation R is said to be in the Third Normal Form (3NF) if and only if

It is in 2NF and

No transitive dependency exists between non-key attributes and

key attributes.

In Report TableS

TUDE

NT#

and COURSE#

are the key attributes.

All other attributes, except grade are non-

partially, non-transitively dependent on key

attributes.

Student#, Course# - > Marks

Marks -> Grade

S#,C#

S#,C#

Marks

Grade

S#,C# Marks Grade

Note : - All transitive dependencies are eliminated


50/145

3NF TablesStude

nt#

Course

#

Marks

101 M4 82

102 M4 62

101 H6 79

103 C3 65

104 B3 77

102 P3 68

105 P3 89

103 B4 54

Marks


51/145

Marks Grade

82 A

62 C

79 B

65 B

77 B

68 B

89 A

54 D

87 A

Third Normal Form Tables in 3 NF

MarksGrade


52/145

Third Normal Form Tables in 3 NF

GRADE TAB

LEUpperB

ound

LowerB

ound

Grade

100 95 A+

94 85 A

84 70 B

69

65B

-64 55 C

54 45 D

440

E


53/145

Boyce-Codd Normal Form (BCNF)

Boyce-Codd normal form (BCNF)

A relation is in BCNF, if and only if, every determinant is a

candidate key.

The difference between 3NF andBCNF is that for a functional

dependency A B, 3NF allows this dependency in a relation

ifB is a primary-key attribute andA is not a candidate key,

whereas BCNF insists that for this dependency to remain in a

relation, A must be a candidate key.


54/145

Example ofBCNF

fd1 clientNo, interviewDate interviewTime, staffNo, roomNo (Primary Key)

fd2 staffNo, interviewDate, interviewTime clientNo (Candidate key)

fd3 roomNo, interviewDate, interviewTime clientNo, staffNo (Candidate key)

fd4 staffNo, interviewDate roomNo (not a candidate key)

As a consequece the ClientInterview relation may suffer from update anmalies.

For example, two tuples have to be updated if the roomNo need be changed for

staffNo SG5 on the 13-May-02.

ClientNo interviewDate interviewTime staffNo roomNo

CR76 13-May-02 10.30 SG5 G101CR76 13-May-02 12.00 SG5 G101

CR74 13-May-02 12.00 SG37 G102

CR56 1-Jul-02 10.30 SG5 G102

Figure 8 ClientInterview relation

ClientInterview


55/145

Example ofBCNF(2)

To transform the ClientInterview relation to BCNF, we must removethe violating functional dependency by creating two new relations

called Interview and SatffRoom as shown below,

Interview (clientNo, interviewDate, interviewTime, staffNo)

StaffRoom(staffNo, interviewDate, roomNo)

ClientNo interviewDate interviewTime staffNo

CR76 13-May-02 10.30 SG5

CR76 13-May-02 12.00 SG5

CR74 13-May-02 12.00 SG37

CR56 1-Jul-02 10.30 SG5

staffNo interviewDate roomNo

SG5 13-May-02 G101

SG37 13-May-02 G102

SG5 1-Jul-02 G102

Interview

StaffRoom

Figure 9 BCNF Interview and StaffRoom relations


56/145

Fourth Normal Form (4NF)Multi-valueddependency (MVD)

represents a dependency between attributes (for example, A,

B and C) in a relation, such that for each value of A there is a

set of values for B and a set of value for C. However, the set of

values for B and C are independent of each other.

A multi-valueddependency can be further defined as being

trivial or nontrivial. A MVD A > B in relation R is defined

as being trivial if

B is a subset of Aor

A U B = R

A MVD is defined as being nontrivial if neither of the above two

conditions is satisfied.


57/145

Fourth Normal Form (4NF)

Fourth normal form (4NF)

A relation that is in Boyce-Codd normal form and contains

no nontrivial multi-valueddependencies.


58/145

Fifth Normal Form (5NF)

Lossless-join dependency

A property ofdecomposition, which ensures that no spurious

tuples are generated when relations are reunited through a

natural join operation.

Join dependency

Describes a type ofdependency. For example, for a relation

R with subsets of the attributes of Rdenoted as A, B, , Z, a

relation R satisfies a join dependency if, and only if, everylegal value of R is equal to the join of its projections on A, B,

, Z.

Fifth normal form (5NF)

A relation that has no join dependency.


59/145

Merits of Normalization

Normalization is based on a

mathematical foundation.

Removes the redundancy to a greater

extent.

After 3NF, data redundancy is


60/145

Demerits of Normalization

Data retrieval orSELECT operation

performance will be severely affected.

Normalization might not always

represent real world scenarios.


61/145

Summary of Normal Forms

Input Operation Output

Un-

normalized

Table

Create separate rows

or columns for everycombination of

multivalued columns

Table in1 NF

Table in1 NF

Eliminate Partialdependencies Tablesin 2NF

Tables in

2 NF

Eliminate Transitive

dependencies

Tables

in 3 NF


62/145

Keys

Candidate key

Primary Key

Alternate Key

SuperKey


63/145

KeysCandidate key

A Candidate key is a set ofone or moreattributes that can uniquely identify a row in agiven table.


64/145

Keys

Candidate key


65/145

Keys

Primary key

During the creation of the table, the

Database Designer chooses one of the

Candidate Key from amongst the severalavailable, to uniquely identify row in the

given table.


66/145

Alternate Key

The candidate key that is chosen to

perform the identification task is called

the primary keyand the remainingcandidate keys are known as alternate

keys.

No of Alternate Keys = No of Candidate

Keys - 1


67/145

KeysSuper key

Any superset of a candidate Key is a super key.

Example:

Custid,CName can uniquely distinguish each tuple of the relation

from the other ones. Thus it satisfies the property of uniqueness.

Also Custid can alone distinguish each tuple of the relation from

the others. Thus it too, satisfies the property of uniqueness.

Therefore, Custid is the Candidate Key and

Custid,CName(superset of candidate) is the superKey.


68/145

KeysForeign key

A Foreign Key is a set of attribute (s) whose values are required to

match values of a Candidate key in the same or another table.

DEPT EMP(Parent /Master/Referenced Table) (Child /Referencing Table)

Point to remember

Foreign key values do not (usually) have to be unique.

Foreign keys can also be null.

DeptNo DName

D1 IVS

D2 ENR

EmpNo EName EDeptNo

1001 Elsa D1

1002 John D2

1003 Maria Null

1004 Maida D1


69/145

KeysForeign key

Points to remember

A ForeignK

ey is a set of attributes of a table,whose values are required to match values of

some Candidate Key in the same or another table

The constraint that values of a given Foreign Key

must match the values of the corresponding

Candidate Key is known as Referential constraint

A table which has a Foreign Key referring to its

own Candidate Key is known as Self-Referencing

table

Demos


70/145

Keys

Non-Key Attributes

The attributes other than the Candidate

Key attributes in a table/relation are calledNon-Key attributes.

OR

The attributes which do not participate in

the Candidate key.


71/145

Exercise on Key attributes

Given a relation R1(X,Y,Z,L) and thefollowing attribute(s) can uniquely

identify the records of relation R1.

1)X

2)X,L3)Z,L

Identify the following in relation R1?


72/145

Entity Relationship

modeling


73/145

Database Design Techniques

Top down Approach

E R Modeling

Bottom Up approach

Normalization

ER d li


74/145

ER modelingER modeling:A graphical technique forunderstandingand

organizing the data independent of the actual databaseimplementation.

Entity:Any thing that may have an independent existence and

about which we intend to collect data.

Also known as Entity type. E.g.: Trainee

Entity instance: a particular member of the entity type e.g. a

particular trainee

Attributes: Properties/characteristics that describe entities.eg:

Trainee name, Batchname, DOB, Address, etc.

Relationships:Associations between entities.E.g.: Trainee

belongs to a Batch

Att ib t


75/145

AttributesThe set of possible values for an attribute is called the domain of

the attributeExample:

The domain of attribute marital status is having four values:

single, married, divorced or widowed.

The domain of the attribute month is having twelve valuesranging from January to December.

Key attribute:The attribute (or combination of attributes) that is

unique for every entity instance

E.g.: the account number of an account, the employee id of

an employee etc.

If the key consists of two or more attributes in combination, it is

called a composite key


76/145

Simple Vs composite attribute

Simple attribute: cannot be divided intosimpler components

E.g.: age of an employee

Composite attribute: can be split into

componentsE.g.: Date of joining of the employee.

Can be split into day, month and year


77/145

Single Vs Multi-valued

AttributesSingle valued : can take on only a singlevalue for each entity instance

E.g.: age of employee. There can be onlyone value for this.

Mu

lti-valu

ed: can take up many valuesE.g.: skill setof employee


78/145

Stored Vs Derived attribute

Stored Attribute: Attribute that need to be stored

permanently.

E.g.: name of an employee

Derived Attribute: Attribute that can be calculated

based on other attributes.

E.g. : years of service of employee can be

calculated from date of joining and current date

R l V W k tit t


79/145

Regular Vs.Weak entity type

Regular Entity: Entity that has its own key attribute(s).

E.g.: Employee, student ,customer, policy holder etc.

Weak entity: Entity that depends on other entity for itsexistence and doesnt have key attribute (s) of its own

E.g. : spouse of employee


80/145

RelationshipsA relationship type between two entity types defines

the set of all associations between these entity types

Each instance of the relationship between members of

these entity types is called a relationship instance

E.g ifWorks-foris the relationship between the

Employee entity and the department entity, then

Rohan works-for IVS department,

Riya works forENR department ..etc are

relationship instances of the relationship, works-

for


81/145


82/145

Cardinality

Relationships can have different connectivity

one-to-one (1:1)

one-to-many (1:N)

many-to- One (M:1) many-to-many (M:N)

E.g.:

Employee head-ofdepartment (1:1)

Lectureroffers course (1:N) assuming a course is taught by a

single lecturer

Student enrolls course (M:N)


83/145

Cardinality One - To - One

P1

P2

P3

P4

C1

C2

C3

C4

P1

P2

P3

P4

C1

C2

C3

C4

Person Chair

One instance of entity type Person

is related

to one instance of the entity type Chair.

Demos


84/145

Cardinality One -to- Many

One instance of entity type Organization

is related

to multiple instances of entity type Employee

O1

O2

O3

E1

E2

E3

E4

E5

O1

O2

O3

E1

E2

E3

E4

E5

Organization Employee

Demos


85/145

Cardinality Many-to-One

D1

D2

D3

E1

E2

E3

E4

E5

D1

D2

D3

E1

E2

E3

E4

E5

Reverse of the One to Many relationship.

Employee Department

Demos


86/145

Cardinality Many-to-Many

S1

S2

S3

S4

C1

C2

C3

C4

S1

S2

S3

S4

C1

C2

C3

C4

Multiple instances of one Entity are related to multiple instances of

another Entity.

Student Course Demos


87/145

Relationship Participation

Total : Every entity instance must beconnected through the relationship to anotherinstance of the other participating entity types

Partial:All instances need not participate

E.g.: Employee Head-ofDepartmentEmployee: partial

Department: total


88/145

ER Modeling - Notations

ER Modeling Notations


89/145

ER Modeling -NotationsAn Entity is an object or concept about

which business user wants to store

information.

A weak Entity is dependent on another

Entity to exist. Example Order Item depends

upon Order Number for its existence.

Without Order Number it is impossible to

identify Order Item uniquely.

Attributes are the properties or

characteristics of an Entity

A key attribute is the unique, distinguishing

characteristic of the Entity

A multi-valued attribute can have more

than one value. For example, an employee

Entity can have multiple skill values.


90/145

ER Modeling -NotationsA derived attribute is based on another attribute.

For example, an employee's monthly salary is based

on the employee's basic salary and House rent

allowance.

Relationships illustrate how two entities share

information in the database structure.

To connect a weak Entity with others, you

should use a weak relationship notation.

ER Modeling Notations


91/145

ER Modeling -Notations

Cardinality specifies how many instances of anEntity relate to one instance of another Entity.

M,N both represent MANY and 1 represents

ONE Cardinality

In some cases, entities can be self-linked. For example, employees can

supervise other employees


92/145

Attributes

EmployeeE#

Name

DOB

Address

Designatio

n


93/145

Key attribute

EmployeeE#

Name

DOB

Address

Designatio

n

The key attribute

is underlined


94/145

Multivalued Attribute

EmployeeE#

Name

DOBAddress

Designatio

n

skill set

C it tt ib t


95/145

Composite attribute

EmployeeE#

Name

DOB

Address

Designation

floor building


96/145

Relationship

studentenrols

incourse


97/145

Unary Relationship

EmployeeManages


98/145


99/145

Binary Relationship

EmployeeWorks

forDepartment

T R l ti hi


100/145

Ternary Relationship

Doctor

Medicine

PatientPrescription

Relationship participation


101/145

Relationship participation

departmentEmployee head

of

1 1

Attributes of a Relationship


102/145

Attributes of a Relationship

Doctor

Medicine

PatientPrescription

dosage

Number of days

Weak entity


103/145

Weak entity

Employee

E#

has dependant

id

1 N

name

The dependant entity is represented by a double lined rectangle and

the identifying relationship by a double lined diamond

C St d ER M d l F


104/145

Assumptions:A college contains many departments

Each department can offer any number of courses

Many instructors can work in a department

An instructor can work only in one department

For each department there is a Head

An instructor can be head of only one department

Each instructor can take any number of coursesA course can be taken by only one instructor

A student can enroll for any number of courses

Each course can have any number of students

Case Study ER Model For a

Uinversity DB


105/145

Steps in ER Modeling

Identify the Entities

Find relationships

Identify the key attributes for every Entity

Identify other relevant attributes

Draw complete E-R diagram with all attributes including Primary

Key

Review your results with your Business users



106/145

Step 1:Identify the Entities

DE

PARTME

NT

STUDENT

COURSE

INSTRUCTOR




107/145


Step 2: Find the relationships

One course is enrolled by multiple students and one studentenrolls for multiple courses, hence the cardinality betweencourse and student is Many to Many.

The department offers many courses and each course belongsto only one department, hence the cardinality betweendepartment and course is One to Many.

One department has multiple instructors and one instructor

belongs to one and only one department , hence the cardinalitybetween department and instructor is one to Many.

Each department there is a Head of department and oneinstructor is Head of department ,hence the cardinality is one

to one .



108/145

Step 3:Identify the key attributes

Deptname is the key attribute for the Entity Department, as itidentifies the

Department uniquely.

Course#

(CourseId) is the key attribute for CourseE

ntity.Student# (Student Number) is the key attribute for Student Entity.

Instructor Name is the key attribute for Instructor Entity.

Step 4:Identify other relevant attributes

For the department entity, the relevant attribute is locationFor course entity, course name,duration,prerequisite

For instructor entity, room#, telephone#

For student entity, student name, date of birth


Step 5:Steps in ER Modeling


109/145

Step 5:

Draw complete E-R diagram with all

attributes including Primary Key

ER diagram for thUniversity


C St d B ki


110/145

Case Study Banking

Business ScenarioAssumptions:There are multiple banks and

each bank has many

branches. Each branch has

multiple customers

Customers have various

types of accounts

Some Customers also had

taken different types of loans

from these bank branches

One customer can have

multiple accounts and Loans


111/145


Identify the Entities

Find relationships

Identify the key attributes for every Entity

Identify other relevant attributes

Draw complete E-R diagram with all attributes including Primary

Key

Review your results with your Business users


112/145

Step 1:Identify the Entities

BANK

BRANCH

LOAN

ACCOUNT

CUSTOMER.




113/145

Step 2: Find the relationshipsOne Bank has many branches and each branch belongs to only one bank,hence the cardinality between Bank and Branch is One to Many.

One Branch offers many loans and each loan is associated with one branch,hence the cardinality between Branch and Loan is One to Many.

One Branch maintains multiple accounts and each account is associated to oneand

only one Branch, hence the cardinality between Branch and Account is One toMany

One Loan can be availed by multiple customers, and each Customer can availmultiple

loans, hence the cardinality betweenLoan and Customer is Many to Many.

One Customer can hold multiple accounts, and each Account can be held bymultiple

Customers, hence the cardinality between Customer and Account is Many toMany




114/145

Step 3:Identify the key attributes

BankCode (Bank Code) is the key attribute for the EntityBank, as it identifies the bank uniquely

Branch#

(Branch Number) is the key attribute for BranchEntity

Customer# (Customer Number) is the key attribute forCustomer Entity

Loan# (Loan Number) is the key attribute for Loan Entity

Account No (Account Number) is the key attribute for AccountEntity



115/145

Steps in ER ModelingStep 4:Identify other relevant attributes

For the Bank Entity, the relevant attributes other than BankCode would be

Name and Address

For the Branch Entity, the relevant attributes other than Branch# would be

Name and Address

For the Loan Entity, the relevant attribute other than Loan# would be Loan

Type

For the Account Entity, the relevant attribute other than Account No would be

Account Type

For the Customer Entity, the relevant attributes other than Customer# would

be Name, Telephone# and Address



116/145

Step 5:

Draw complete E-R diagram with all attributes including Primary Key

ER Diagram for theBank

g

Merits and Demerits of ER


117/145

Merits and Demerits ofER

ModelingMeritsEasy to understand. Represented in Business Users Language.

Can be understood by non-technical specialist.

Intuitive and helps in Physical Database creation.

Can be generalized and specialized based on needs.Can help in database design.

Gives a higher level description of the system.

Demerits

Physical design derived from E-R Model may have some amountof ambiguities or inconsistency.

Sometime diagrams may lead to misinterpretations


118/145

Logical Database Design


119/145

Converting Strong entity types

Each entity type becomes a table

Each single-valued attribute becomes a column

Derived attributes are ignored

Composite attributes are represented by its equivalent parts

Multi-valued attributes are represented by a separate table

The key attribute of the entity type becomes the primary key of

the table


120/145

Entity example

Here address is a composite

attribute

Years of service is a derivedattribute (can be calculated

from date of joining and

current date)

Skill set is a multi-valued

attribute

And

Employee (E#, Name, Door_No, Street, City, Pincode, Date_Of_Joining)

Emp_Skillset( E#, Skillset)

Entity example (Contd.)


121/145

y p ( )

SkillSet Table

E# FK/PK

Skillset PK

Employee Table

E# PK

Name

Door_No

Street

City

Pincode

Date_Of_Joining

Converting weak entity types


122/145

g y yp

Weak entity types areconverted into a table of their own, with the primarykey of the strong entityacting as a foreign key inthe table.

This foreign key along withthe key of the weak entityforms the compositeprimary key of this weaktable

The Relational Schema

Employee (E# ,EmpName,DateOfJoining,SkillSet)

Dependant (Employee, Dependant_ID, Name, Address)



123/145


(Contd)

Dependent Table

Employee PK/FK

Dependent_ID PK

Name

Address

Employee TableE# PK

EmpName

DateofJoining

SkillSet


124/145

Converting relationships

The way relationships are represented depends onthe cardinality and the degree of the relationship

The possible cardinalities are:1:1, 1:M, N:M

The degrees are:

Unary Binary

Ternary


125/145

Binary 1:1

Case 1: Combination of participation types

The primary key of the partial participant will become the foreignkey in the total participant.

Employee( E#, EName,DateOfJoining,SkillSet)

Department (Dept#, DName,Location,Head)

DepartmentEmployee head

of1 1

Demos


126/145

Binary 1 : 1

Department

Dept#

PK

DName

Location

Head FK

Employee

Table

E# PK

EName

DateofJoiningSkillSet

Binary 1:1


127/145

Binary 1:1

Case 2: Uniform participation types

The primary key of either of the participants can become a foreignkey in the other

Employee (EmpCode,EmpName,DateOfJoining)

Chair( Item#, Model, Location, Used_by)

(OR)

Employee (EmpCode,EmpName,DateOfJoining,Sits_on)

Chair(Item#, Model, Location)

Employee CHAIR Sits_on


128/145

Binary 1 : 1Chair TableItem# PK

Model

Location

Used_By FK

Employee Table

EmpCode PK

EmpName

DateofJoining

Chair Table

Item# PK

Model

Location

Employee Table

EmpCode PK

EmpNameDateofJoining

Sits_On FK

OR


129/145

Binary 1:N

The primary key of the relation on the 1 side of the relationship

becomes a foreign key in the relation on the N side.

Teacher (TeacherID, Name, Telephone, Cabin)

Subject (SubCode, SubName, Duration, TeacherID)

Teacher Teaches Subject1 N

Demos


130/145

Binary 1 : N

Subject

SubCode PK

SubName

Duration

TeacherID FK

Teacher

TeacherID PK

Name

Telephone

Cabin

Binary M:N


131/145

y

A new table is created to represent the relationship

which contains two foreign keys - one from each of theparticipants in the relationship.

The primary key of the new table is the combination ofthe two foreign keys.

Student (StudentID,SName,DOB,Address)Course(CourseID,CName)

Enrolls (SID, CID)

Student Enrolls CourseM N

Demos


132/145

Binary M : N

Course

CourseID PK

Coursename

Student

StudentID PK

SName

DOB

Address

Enrolls

SID PK / FK

CID PK / FK

DOIssue

Status

Unary 1:1


133/145

Employee( E#,

EName,Spouse)

Consider employees who are also

a couple

The primary key field itself will

become foreign key in the same

table

Demos


134/145

Unary 1 : 1

Employee Table

E# PKEName

DateofJoining

SkillSet

Spouse FK

Unary 1:N


135/145

Employee( E#, EName, DateOfJoining,

SkillSet, Manager)

The primary key field itselfwill become foreign key in

the same table.

Same as unary 1:1

Demos

Unary 1 : N


136/145

Employee TableE# PK

EName

DateofJoining

SkillSet

Manager FK

Unary M:N


137/145

There will be two resulting tables. One to represent the

entity and another to represent the M:N relationship as

follows

Employee( E#, EName, DateOfJoining, SkillSet)

Gu

aranty( Guarantor, Beneficiary)

Employee

Guarantor_ofM

N

Demos

U M N


138/145

Unary M : N

Guaranty

Guarantor PK/FK

Beneficiary PK/FK

Employee Table

EmpCode PK

EName

DateofJoining

SkillSet

T l ti hi


139/145

Ternary relationshipRepresented by a new table.

The new table contains three

foreign keys - one from each of

the participating Entities.

The primary key of the new

table is the combination of all

three foreign keys.

Prescription (DocID, PatCode,

MedName)

T


140/145

Ternary

Doctor

DocID PK

Title

Prescription

DocID PK / FK

PatCode PK / FK

MedName PK/ FK

NextVisit

Patient

PatCode PK

PatName

DOB

Address

Medicine

MedName PK

ExpDate

Deriving Logical Schema for


141/145

Deriving Logical Schema for

Banking ApplicationEach Entity represented in the E-R

model can be defined as a table in the

relational scheme. All the attributes ofthe Entity will become columns of the

table.

Example: Letus consider the CUSTOMER Entity of

the bankingdatabase scenario. We can translate this

Entity to a CUSTOMER table with the following

columns.

Deriving Logical Schema forBanking Application


142/145

Weak Entity types are converted into a table of their own,

with the primary key of the strong Entity acting as a foreign

key in the table. This foreign key along with the partial key

of theWeak Entity forms the composite primary key of this

table.

Example: s per this guideline, a Branch table can be

createdwith the followingstructure.

BRANCH

(BankCode, Branch#, Name, Address)



143/145

Each relationship can be defined as separate table in relational

schema. Key attributes ofparticipatingentities will become key

attribute of the Relationship.

Example: We candefine Loan_Detailtable with Loan# andCustomer# together as primary key with other relevant

attributes like DateOfSanction, InterestRate, LoanAmount,

Duration etc.

LOAN_DETAILS (Loan#, Customer#, DateofSanction, InterestRate,

LoanAmount, Duration)

Participating entities:The entities which are joined by the



144/145

In a Many to Many relationship, it is necessaryto create separate tables

for the participating entities and the

relationship. In the banking application we

have Customer and Loan Entities have a

Many to Many relationship. Hence one should

create separate tables for CUSTOMER,

LOANS and LOAN_DETAILS. Here

LOAN_DETAILS refers to relationship table.

S


145/145

SummaryMost of the application errors are because ofmiscommunication between the application user and

the designer and between the designer and the

developer.

It is always better to represent business findings in

terms of picture to avoid miscommunication

It is practically impossible to review the complete

requirement document by business users.

An E-R diagram is one of the many ways to represent

business findings in pictorial format.

E-R Modeling will also help the database design

normalization & er model

Documents