chapter 10 functional dependencies and normalization for rdbs
DESCRIPTION
Fundamentals of Database Systems. Chapter 10 Functional Dependencies and Normalization for RDBs. Informal Guidelines for Relation Schema Design. Four Informal Measures Semantics of the attributes Reducing the redundant values in tuples Reducing the null values in tuples - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/1.jpg)
IM ISU 1Database
Chapter 10
Functional Dependencies and
Normalization for RDBs
Fundamentals of Database Systems
![Page 2: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/2.jpg)
IM ISU 2Database
Informal Guidelines for Relation Schema Design
Four Informal Measures Semantics of the attributes Reducing the redundant values in tuples Reducing the null values in tuples Disallowing the possibility of generating spur
ious tuples
![Page 3: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/3.jpg)
IM ISU 3Database
Informal Guidelines for Relation Schema Design (cont.)
Semantics of the Relation Attributes Meaning of semantics: specifies how to inter
pret the attribute values stored in a tuple of the relation
In general, the easier it is to explain the semantics of the relation, the better the relation schema design will be
![Page 4: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/4.jpg)
IM ISU 4Database
ER-to-Relational Mapping (cont.)
![Page 5: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/5.jpg)
IM ISU 5Database
Informal Guidelines for Relation Schema Design (cont.)
GUIDELINE 1» Design a relation schema so that it is easy to
explain its meaning» Do not combine attributes from multiple entity
types and relationship types into a single relation» Intuitively, if a relation schema corresponds to
one entity type or one relationship type, the meaning tends to be clear
![Page 6: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/6.jpg)
IM ISU 6Database
Informal Guidelines for Relation Schema Design (cont.)
Poor design example:
EMP_DEPT mixes attributes of employees and departments
EMP_PROJ mixes attributes of employees and projects
![Page 7: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/7.jpg)
IM ISU 7Database
Informal Guidelines for Relation Schema Design (cont.)
Redundant Information in Tuples and Update Anomalies One goal of schema design is to minimize th
e storage space of the base relations Example: compare Fig. 14.2 and 14.4
» Redundancy problem» Update anomalies problem
![Page 8: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/8.jpg)
IM ISU 8Database
Informal Guidelines for Relation Schema Design (cont.)
Fig 14.2
![Page 9: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/9.jpg)
IM ISU 9Database
Informal Guidelines for Relation Schema Design (cont.)
![Page 10: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/10.jpg)
IM ISU 10Database
Informal Guidelines for Relation Schema Design (cont.)
Fig 14.4
![Page 11: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/11.jpg)
IM ISU 11Database
Informal Guidelines for Relation Schema Design (cont.)
![Page 12: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/12.jpg)
IM ISU 12Database
Informal Guidelines for Relation Schema Design (cont.)
Update Anomalies Insertion anomalies: two situations
» To insert a new employee tuple into EMP_DEPT, we must include either the attribute values for the department that the employee works for, or nulls
» It is difficult to insert a new department that has no employees as yet in the EMP_DEPT relation
![Page 13: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/13.jpg)
IM ISU 13Database
Informal Guidelines for Relation Schema Design (cont.)
Deletion anomalies» If we delete from EMP_DEPT an employee tuple
that happens to represent the last employee working for a particular department, the information concerning that department is lost
Modification anomalies» If we change the value of one of the attributes of
a particular department, we must update the tuples of all employees in that department to avoid inconsistency
![Page 14: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/14.jpg)
IM ISU 14Database
Informal Guidelines for Relation Schema Design (cont.)
GUIDELINE 2» Design the base relation schemas so that no
insertion, deletion, or modification anomalies are present in the relations
» If any anomalies are present, note them clearly and make sure that the programs that update the database will operate correctly
![Page 15: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/15.jpg)
IM ISU 15Database
Informal Guidelines for Relation Schema Design (cont.)
Note» To improve the performance of certain queries, th
ese guidelines may sometimes have to be violated
» In general, it is advisable to use anomaly-free base relations and to specify views that include the JOINs for placing together the attributes frequently referenced in important queries
» Example: Specify EMP_DEPT as a view to speedup query
![Page 16: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/16.jpg)
IM ISU 16Database
Informal Guidelines for Relation Schema Design (cont.)
Null Values in Tuples Problems with null values
» Waste space at the storage level » May lead to problems with understanding the me
aning of the attributes– The attribute does not apply to this tuple
– The attribute value for this tuple is unknown
– The value is known but absent
» How to account for them when aggregate operations such as COUNT or SUM are applied
![Page 17: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/17.jpg)
IM ISU 17Database
Informal Guidelines for Relation Schema Design (cont.)
![Page 18: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/18.jpg)
IM ISU 18Database
Informal Guidelines for Relation Schema Design (cont.)
![Page 19: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/19.jpg)
IM ISU 19Database
Informal Guidelines for Relation Schema Design (cont.)
![Page 20: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/20.jpg)
IM ISU 20Database
A NATURAL JOIN on EMP_PROJ1 and EMP_LOCS produces more tuples than those in EMP_PROJ
![Page 21: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/21.jpg)
IM ISU 21Database
Informal Guidelines for Relation Schema Design (cont.)
GUIDELINE 4» Design relation schemas so that they can be JOIN
ed with equality conditions on attributes that are either primary keys or foreign keys in a way that guarantees that no spurious tuples are generated
» Do not have relations that contain matching attributes other than foreign key-primary key combinations
» If such relations are unavoidable, do not join them on such attributes
![Page 22: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/22.jpg)
IM ISU 22Database
Functional Dependencies
Definition Consider a relation schema R = {A1, A2, …, An}.
A functional dependency, denoted by X Y, for X, Y R, specifies a constraint on a relation state r of R such that for any two tuples t1 and t
2 in r,
if t1[X] = t2[X], we must have t1[Y] = t2[Y].
Note: if X is a candidate key of R, this implies that X Y for any subset of attributes Y of R
![Page 23: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/23.jpg)
IM ISU 23Database
Functional Dependencies (cont.)
Meaning The Y component of a tuple in r depend on,
or are determined by, the values of the X component
The values of the X component of a tuple uniquely (or functionally) determine the values of the Y component
We also say that there is a functional (FD) dependency from X to Y or that Y is functionally dependent on X
![Page 24: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/24.jpg)
IM ISU 24Database
Functional Dependencies (cont.)
Example: Relation schema EMP_PROJ 1. SSN ENAME
2. PNUMBER {PNAME, PLOCATION}
3. {SSN, PNUMBER} HOURS
![Page 25: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/25.jpg)
IM ISU 25Database
Functional Dependencies (cont.)
Notice A functional dependency is a property of the
relation schema (intension) R, not of a particular legal relation state (extension) r of R.
An FD cannot be inferred automatically from a given relation extension r but must be defined explicitly by someone who knows the semantics of the attributes of R.
![Page 26: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/26.jpg)
IM ISU 26Database
Functional Dependencies (cont.)
Inference Rules for FDs F: the set of functional dependencies specifie
d on a relation schema R Other dependencies can be inferred or deduce
d from the FDs in F The set of all such dependencies is called the
closure of F and is denoted by F+
![Page 27: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/27.jpg)
IM ISU 27Database
Functional Dependencies (cont.) Example
» Let F = {SSN {ENAME, BDATE, ADDRESS, DNUMBER}, DNUMBER {DNAME, DMGRSSN}}
» We can infer the following additional FDsSSN {DNAME, DMGRSSN}, SSN SSN, DNUMBER DNAME
![Page 28: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/28.jpg)
IM ISU 28Database
Functional Dependencies (cont.) Inference rules
» The rules that can be used to infer new dependencies from a given F
» Notation F X Y: X Y is inferred from the set F
» For simplicity, – {X, Y} Z is abbreviated to XY Z – {X, Y, Z} {U, V} is abbreviated to XYZ
UV
»There are six well defined rules
![Page 29: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/29.jpg)
IM ISU 29Database
Functional Dependencies (cont.)
» IR1 (reflexive rule): If X Y, then X Y » IR2 (augmentation rule): {X Y } XZ YZ » IR3 (transitive rule): {X Y, Y Z} X Z » IR4 (decomposition, or projective, rule):
{X YZ} X Y. » IR5 (union, or additive, rule):
{X Y, X Z} X YZ » IR6 (pseudotransitive rule):
{X Y, WY Z } WX Z
![Page 30: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/30.jpg)
IM ISU 30Database
Functional Dependencies (cont.)
IR1 through IR3 are known as Armstrong’s inference rules » It has been shown by Armstrong (1974) that
inference rules IR1 through IR3 are sound and complete
» In other words, the set of dependencies, which we called the closure of F, can be determined from F by using only inference rules IR1 through IR3
![Page 31: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/31.jpg)
IM ISU 31Database
Normalization
Introduction Normalization of data
» A process of analyzing the given relation schemas based on their FDs and primary keys to achieve the desirable properties of (1) minimizing redundancy(2) minimizing the insertion, deletion, and update
anomalies » Unsatisfactory relation schemas that do not meet t
he normal form tests are decomposed into smaller relation schemas
![Page 32: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/32.jpg)
IM ISU 32Database
Normalization (cont.) History
» Initially, Codd (1972a) proposed three normal forms based on FD, which he called first, second, and third normal form
» A stronger definition of 3NF—called Boyce-Codd normal form (BCNF)—was proposed later by Boyce and Codd
» Later, a fourth normal form (4NF) and a fifth normal form (5NF) were proposed, based on the concepts of multivalued dependencies and join dependencies
![Page 33: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/33.jpg)
IM ISU 33Database
Normalization (cont.)
Notice» Normal forms, when considered in isolation from
other factors, do not guarantee a good database design
– The lossless join or nonadditive join property, which guarantees that the spurious tuple generation problem
– The dependency preservation property, which ensures that each functional dependency is represented in some individual relations resulting after decomposition
![Page 34: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/34.jpg)
IM ISU 34Database
Normalization (cont.)» The database designers need not normalize to the
highest possible normal form. – Relations may be left in a lower normalization
status for performance reasons– The process of storing the join of higher norm
al form relations as a base relation—which is in a lower normal form—is known as denormalization
![Page 35: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/35.jpg)
IM ISU 35Database
Normalization (cont.) Related terminology
» Prime attribute – An attribute of relation schema R is called a
prime attribute of R if it is a member of some candidate key of R
» Nonprime attribute – An attribute is called nonprime if it is not a
prime attribute
»Example -- WORKS_ON relation– prime: both SSN and PNUMBER– nonprime: others
![Page 36: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/36.jpg)
IM ISU 36Database
Normal Forms
First Normal Form (1NF) Historically, it was defined to disallow multival
ued attributes, composite attributes, and their combinations
The domain of an attribute must include only atomic (simple, indivisible) values and that the value of any attribute in a tuple must be a single value from the domain of that attribute
![Page 37: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/37.jpg)
IM ISU 37Database
Normal Forms (cont.)
Example» Not in 1NF because DLOCATIONS is not an
atomic attribute
![Page 38: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/38.jpg)
IM ISU 38Database
Normal Forms (cont.) Three main techniques to achieve 1NF
1. Decomposes the non-1NF relation into two 1NF relations
![Page 39: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/39.jpg)
IM ISU 39Database
Normal Forms (cont.)2. Expand the key to distinguish each tuple
– Has the disadvantage of introducing redundancy
3. Divide the attribute into several atomic attributes– DLOCATIONS => DLOCATION1, DLOCATION2, an
d DLOCATION3 – The maximum number of values needs to be known – Has the disadvantage of introducing null values
![Page 40: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/40.jpg)
IM ISU 40Database
Normal Forms (cont.)» The first is superior because it does not suffer fro
m redundancy and it is completely general
The first normal form also disallows multivalued, composite attributes» These are called nested relations» For example:
EMP_PROJ(SSN, ENAME, {PROJS(PNUMBER, HOURS)})
– PROJS is a multivalued, composite attribute
![Page 41: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/41.jpg)
IM ISU 41Database
Normal Forms (cont.)
![Page 42: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/42.jpg)
IM ISU 42Database
Normal Forms (cont.) Technique to normalize multivalued, compos
ite attributes into 1NF » Remove the nested relation attributes into a new r
elation » Propagate the primary key into new relation
![Page 43: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/43.jpg)
IM ISU 43Database
Normal Forms (cont.) Second Normal Form (2NF)
2NF is based on the concept of full functional dependency
X Y is a full functional dependency if removal of any attribute A from X invalidates the dependency
X Y is a partial dependency if some attribute A X can be removed and the dependency still holds
![Page 44: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/44.jpg)
IM ISU 44Database
Normal Forms (cont.) Example
» {SSN, PNUMBER} HOURS is a full dependency
» {SSN, PNUMBER} ENAME is partial
![Page 45: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/45.jpg)
IM ISU 45Database
Normal Forms (cont.) Testing for 2NF
» The test for 2NF involves testing for functional dependencies whose left-hand side attributes are part of the primary key
» If the primary key contains a single attribute, the test need not be applied at all
» A relation schema R is in 2NF if every nonprime attribute A in R is fully functionally dependent on the primary key of R
![Page 46: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/46.jpg)
IM ISU 46Database
Normal Forms (cont.)» Example
– EMP_PROJ is in 1NF but not in 2NF– The nonprime attribute ENAME violates 2NF
because FD2 is partial– The nonprime attributes PNAME and
PLOCATION also violates 2NF because FD3 is partial
Method for normalizing a non-2NF relation» Divide the relation into several relations in which
nonprime attributes are associated only with the part of the primary key on which they are fully functionally dependent
![Page 47: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/47.jpg)
IM ISU 47Database
Normal Forms (cont.)
![Page 48: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/48.jpg)
IM ISU 48Database
Normal Forms (cont.)
Third Normal Form (3NF) 3NF is based on the concept of transitive
dependency X Y in a relation schema R is a transitive
dependency if there is a set of attributes Z that is neither a candidate key nor a subset of any key of R, and both X Z and Z Y hold
![Page 49: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/49.jpg)
IM ISU 49Database
Normal Forms (cont.) Example
» Both SSN DNUMBER and DNUMBER DMGRSSN hold
» DNUMBER is neither a key nor a subset of the key of EMP_DEPT
» SSN DMGRSSN is a transitive dependency
![Page 50: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/50.jpg)
IM ISU 50Database
Normal Forms (cont.) Testing for 3NF
» A relation schema R is in 3NF if it satisfies 2NF and no nonprime attribute of R is transitively dependent on the primary key
» Example: the EMP_DEPT relation
Method for normalizing a non-3NF relation» Decompose and set up a relation that includes the
nonkey attribute(s) that functionally determine(s) other nonkey attribute(s)
![Page 51: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/51.jpg)
IM ISU 51Database
Normal Forms (cont.)» Example
![Page 52: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/52.jpg)
IM ISU 52Database
General Normal Form Definitions
Preliminary The above definitions consider the primary
key only We have to consider more general definitions
that take into account relations with multiple candidate keys
General definition of prime attribute» An attribute that is part of any candidate key will
be considered as prime
![Page 53: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/53.jpg)
IM ISU 53Database
General Normal Form Definitions (cont.)
General Definition of 2NF A relation schema R is in second normal
form (2NF) if every non-prime attribute A in R is fully functionally dependent on every key of R
Example: relation schema LOTS» Two candidate keys: PROPERTY_ID# and
{COUNTY_NAME, LOT#}» FD1 and FD2 hold» Assume FD3 and FD4 also hold
![Page 54: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/54.jpg)
IM ISU 54Database
General Normal Form Definitions (cont.)
» TAX_RATE is partially dependent on the candidate key {COUNTY_NAME, LOT#}, due to FD3
» LOTS not in general 2NF
![Page 55: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/55.jpg)
IM ISU 55Database
General Normal Form Definitions (cont.)
» Normalization to general 2NF– Decompose it into the two relations LOTS1
and LOTS2
![Page 56: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/56.jpg)
IM ISU 56Database
General Normal Form Definitions (cont.)
General Definition of 3NF A relation schema R is in third normal form
(3NF) if whenever a FD X A holds in R, then either:
(a) X is a superkey of R, or
(b) A is a prime attribute of R Superkey of relation schema R
» A set of attributes S of R that contains a key of R
![Page 57: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/57.jpg)
IM ISU 57Database
General Normal Form Definitions (cont.)
Example» LOTS2 is in general 3NF» FD4 in LOTS1 violates 3NF
– AREA is not a superkey– PRICE is not a prime attribute in LOTS1
![Page 58: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/58.jpg)
IM ISU 58Database
General Normal Form Definitions (cont.)
» Normalization LOTS1 to general 3NF– Decompose it into the relation schemas
LOTS1A and LOTS1B
![Page 59: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/59.jpg)
IM ISU 59Database
General Normal Form Definitions (cont.)
Boyce-Codd Normal Form Definition
» A relation schema R is in Boyce-Codd Normal Form (BCNF) if whenever a nontrivial FD X A holds in R, then X is a superkey of R
» BCNF is stronger than 3NF– Every relation in BCNF is also in 3NF; however,
a relation in 3NF is not necessarily in BCNF
– The only difference is that condition (b) of 3NF
![Page 60: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/60.jpg)
IM ISU 60Database
General Normal Form Definitions (cont.) Example -- LOTS1A
» Suppose that – There are only two counties: Dekalb and Fulton – Lot sizes in Dekalb: restricted to 0.5, 0.6, ...,1.0 acres – Lot sizes in Fulton: restricted to 1.1, 1.2, ..., 2.0 acres
» There is an additional FD in relation LOTS1AFD5: AREA COUNTY_NAME
![Page 61: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/61.jpg)
IM ISU 61Database
General Normal Form Definitions (cont.)
» It is still is in 3NF because COUNTY_NAME is a prime attribute
» FD5 violates BCNF in LOTS1A because AREA is not a superkey of LOTS1A
» We can decompose LOTS1A into two BCNF relations LOTS1AX and LOTS1AY
– In LOTS1AY, there are only 16 possible AREA values
– This reduces the redundancy in LOTS1A tuples– But it loses the functional dependency FD2
![Page 62: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/62.jpg)
IM ISU 62Database
General Normal Form Definitions (cont.)
![Page 63: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/63.jpg)
IM ISU 63Database
General Normal Form Definitions (cont.)
Summary» Most relation 3NF schemas are also in BCNF » Only if FD X A holds in R with X not being a s
uperkey and A being a prime attribute will R be in 3NF but not in BCNF
» General form
![Page 64: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/64.jpg)
IM ISU 64Database
General Normal Form Definitions (cont.) » Example -- TEACH
– FD1: {STUDENT, COURSE} INSTRUCTOR
– FD2: INSTRUCTOR COURSE
![Page 65: Chapter 10 Functional Dependencies and Normalization for RDBs](https://reader037.vdocuments.us/reader037/viewer/2022110103/568145b7550346895db2be62/html5/thumbnails/65.jpg)
IM ISU 65Database
General Normal Form Definitions (cont.)
Each normal form is strictly stronger than the previous one:» Every 2NF relation is in 1NF» Every 3NF relation is in 2NF» Every BCNF relation is in 3NF