Functional Dependencies in Database Management Systems, Slides of Introduction to Database Management Systems

Download Functional Dependencies in Database Management Systems and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database

Management

System

Lecture - 19

Functional Dependency

Normalization is based on functional dependencies (FDs) A type of relationship between attributes of a relation

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

Functional Dependency

Attribute of set of attributes on the left side are called determinant and on the right are called dependents Like R (a, b, c, d, e) a b, c, d d d, e

FD Example from Table

stId stName stAdr prName prCrdts S1020 Sohail Dar I-8 Islamabad MCS 64 S1038 Shoaib Ali G-6 Islamabad BCS 132 S1015 Tahira Ejaz L Rukh Wah MCS 64 S1015 Tahira Ejaz L Rukh Wah MCS 132 S1018 Arif Zia E-8, Islamabad. BIT 134

FDs and Keys

We can determine the keys of a table seeing its FDs

The determinant of an FD that determines all attributes of that table is the super key

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

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

Reflexivity

If B is a subset of A then A B, it also implies that A A always hold, that is stName, stAdr stName

Or stName stName

Augmentation

If we have A B then

AC BC that is if stId stName then stId, stAdr stName, stAdr

Additivity or Union

If A B and A C then A BC

if empId eName and empId qual

Then we can write it as

empId eName, qual

Projectivity or Decomposition

If A BC then A B and A C if empId eName, qual Then we can write it as empId eName and empId qual

Normal Forms

First Normal Form

A relation is in first normal form iff every attribute in every tuple contains an atomic value

There is no multivaued (repeating group) in the relation