Database Management System Normalization 12302021 Functional Dependency Normalization

Database Management System Normalization 12/30/2021

Functional Dependency ØNormalization is based on functional dependencies (FDs) ØA type of relationship between attributes of a relation 12/30/2021

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 12/30/2021 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) 12/30/2021 a b, c, d d d, e

FD Example STD(st. Id, st. Name, st. Adr, pr. Name, credits) st. Id pr. Name 12/30/2021 st. Name, st. Adr, pr. Name, credits

FD Example from Table st. Id st. Name st. Adr pr. Name pr. Crdts S 1020 Sohail Dar I-8 Islamabad MCS 64 S 1038 Shoaib Ali G-6 Islamabad BCS 132 S 1015 Tahira Ejaz L Rukh Wah MCS 64 S 1015 Tahira Ejaz L Rukh Wah MCS 132 S 1018 Arif Zia E-8, Islamabad. BIT 12/30/2021 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 12/30/2021

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 12/30/2021

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 12/30/2021

FDs and Keys EMP(e. Id, e. Name, e. Adr, e. Dept, pr. Id, pr. Sal) e. Id e. Name, e. Dept, e. Adr e. Id, pr. Id pr. Sal STD(st. Id, st. Name, pr. Name, adr, nic, cgpa) st. Id st. Name, pr. Name, adr, nic, cgpa nic st. Name, pr. Name, adr, st. Id, cgpa 12/30/2021

Normal Forms 12/30/2021

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 12/30/2021

First Normal Form STD(st. Id, st. Name, st. Adr, pr. Name, bk. Id) st. Id st. Name st. Adr pr. Name bk. Id S 1020 Sohail Dar I-8 Islamabad MCS B 00129 S 1038 Shoaib Ali G-6 Islamabad BCS B 00327 S 1015 Tahira Ejaz L Rukh Wah MCS B 08945, B 06352 S 1018 12/30/2021 Arif Zia E-8, Islamabad. BIT B 08474

First Normal Form st. Id st. Name st. Adr pr. Name bk. Id S 1020 Sohail Dar I-8 Islamabad MCS B 00129 S 1038 Shoaib Ali G-6 Islamabad BCS B 00327 S 1015 Tahira Ejaz L Rukh Wah MCS B 08945 S 1015 Tahira Ejaz L Rukh Wah MCS B 06352 S 1018 Arif Zia E-8, Islamabad. BIT 12/30/2021 Mind the key please B 08474

Second Normal Form Full functional dependency: an attribute B is fully functionally dependent on A if the B can be determined by whole of A not by any proper subset of A 12/30/2021

Full Functional Dependence Consider the relation CLASS(cr. Id, st. Name, f. Id, room, grade) cr. Id, st. Id st. Name, f. Id, room, grade st. Id st. Name cr. Id f. Id, room 12/30/2021

Second Normal Form ØA relation is in nd 2 normal form iff it is in the first normal form and all non key attributes are fully functionally dependent on key, that is, there is no partial dependency © Virtual University of Pakistan

Second Normal Form CLASS(cr. Id, st. Name, f. Id, room, grade) cr. Id, st. Id st. Name, f. Id, room, grade st. Id st. Name cr. Id f. Id, room © Virtual University of Pakistan

Anomalies ØRedundancy ØInsertion Anomaly ØDeletion Anomaly ØUpdation Aomaly © Virtual University of Pakistan

Anomalies cr. Id st. Name f. Id room C 3456 S 1020 Suhail Dar F 2345 104 grade B C 5678 S 1020 Suhail Dar F 4567 106 C 3456 S 1038 Shoaib Ali F 2345 104 A C 5678 S 1015 Tahira Ejaz F 4567 106 B © Virtual University of Pakistan

Second Normal Form Relation is decomposed based on the FDs CLASS(cr. Id, st. Name, f. Id, room, grade) cr. Id, st. Id st. Name, f. Id, room, grade st. Id st. Name cr. Id f. Id, room STD(st. Id, st. Name) COURSE(cr. Id, f. Id, room) CLASS(cr. Id, st. Id, grade) © Virtual University of Pakistan

Second Normal Form ØEach of these tables is in second normal form ØFree of anomalies due to partial dependency © Virtual University of Pakistan

Third Normal Form A table is in third normal form (3 NF) iff it is in 2 NF and there is no transitive dependency, that is, no non-key attribute is dependent on another non-key attribute © Virtual University of Pakistan

Transitive Dependency STD(st. Id, st. Name, st. Adr, pr. Name, pr. Crdts) st. Id st. Name, st. Adr, pr. Name, pr. Crdts pr. Name pr. Crdts © Virtual University of Pakistan

Anomalies st. Id st. Name st. Adr pr. Name pr. Crdts S 1020 Sohail Dar I-8 Islamabad MCS 64 S 1038 Shoaib Ali G-6 Islamabad BCS 132 S 1015 Tahira Ejaz L Rukh Wah MCS 64 S 1018 Arif Zia E-8, Islamabad. BIT © Virtual University of Pakistan 134

Third Normal Form ØSTD(st. Id, st. Name, st. Adr, pr. Name, pr. Crdts) Øst. Id st. Name, st. Adr, pr. Name, pr. Crdts Øpr. Name pr. Crdts ØSTD (st. Id, st. Name, st. Adr, pr. Name) ØPROGRAM (pr. Name, pr. Crdts) © Virtual University of Pakistan

3 NF Relations ØEach of the table is in 3 NF ØFree of all anomalies © Virtual University of Pakistan

Boyce-Codd Normal Form ØA general form of 3 NF ØEvery relation in BCNF is in 3 NF vice-versa is not always true Ø 3 NF is checked in steps, BCNF checked directly © Virtual University of Pakistan

BCNF ØA table is in BCNF if every determinant is a candidate key ØSituation when table in 3 NF is not in BCNF ØA non-key determines a part of the composite primary key © Virtual University of Pakistan

BCNF FACULTY(f. Id, dept, office, rank, date. Hired) f. Id, dept office, rank, date. Hired office dept Ø Table is in 3 NF, not in BCNF since the office is not a candidate key Ø Generates multiple overlapping candidate keys so we have f. Id, office dept, rank, date. Hired © Virtual University of Pakistan

BCNF ØWe decompose the table again to bring it into BCNF FACULTY (f. Id, dept, office, rank, date. Hired) FACULTY(f. Id, office, rank, date. Hred) OFFICE(office, dept) © Virtual University of Pakistan

Higher Normal Forms ØAfter BCNF fourth, fifth and domain-key normal forms exist Ø 4 NF deals with multivalued dependency, fifth deals with possible lossless decompositions, DKNF reduces further chances of any possible inconsistency © Virtual University of Pakistan

Database Management System Lecture - 20 © Virtual University of Pakistan