Normalization-Introduction to Database System-Lecture Slides, Slides of Introduction to Database Management Systems

Download Normalization-Introduction to Database System-Lecture Slides and more Slides Introduction to Database Management Systems in PDF only on Docsity!

In the name of

ALLAH

Introduction to

Database Systems

Lecture 15-05- Spring 2012 docsity.com

Department of Computer Science

Anomalies

 A state of DBD that can lead the DB to inconsistency or incorrect state of database

 Four types of anomalies are of concern here; Redundancy, insertion, deletion and updation

Department of Computer Science

Normalization

 A strongly recommended step

 Normalized design makes the

maintenance of database easier

 Normalization applied on each

table of a DB design

Department of Computer Science

Normalization Process

 Different forms or levels of normalization

 Called first, second, third and so on forms

 Each form has got certain conditions

 If a table fulfils the condition(s) for a normal form then the table is in that normal form

Department of Computer Science

Normalized DB Design

 Process is applied on each table

 The minimum form in which all tables are in is called the normal form of the entire database

 Objective is to place the DB in highest form of normalization

Department of Computer Science

Functional Dependency

Definition: If A and B are attributes

of a relation R, then B is

functionally dependent on A if each

value of A in R is associated with

exactly one value of B; written as

A B

Department of Computer Science

Functionally Dependency

 It does not mean that A derives B, although it may be the case sometime  Means that if we know value of A then we can precisely determine a unique value of B

Department of Computer Science

FD Example

STD(stId, stName, stAdr, prName, credits)

stId stName, stAdr, prName, credits prName credits

Where they come from?

 Common sense with the

meaning of attributes

 Determined by organization

 Judged from sample data

Department of Computer Sciencedocsity.com

Department of Computer Science

FDs and Keys

 A minimal super key is the

candidate key, so if a

determinant of an FD determines

all attributes of that relation then

it is definitely a super key, and

Department of Computer Science

FDs and Keys

 If there is no other FD where a subset of

this determinant/SK is a super key, then

it is a candidate key

 So FDs help to identify keys, how

Department of Computer Science

FDs and Keys

EMP(eId, eName, eAdr, eDept, prId, prSal) eId  eName, eDept, eAdr

eId, prId  prSal STD(stId, stName, prName, adr, nic, cgpa) stId  stName, prName, adr, nic, cgpa nic  stName, prName, adr, stId, cgpa

Department of Computer Science

Inference Rules

 Called inference axioms or armstrong axioms  These are rules that establish certain FDs from a given set of FDs  These rules are sound