fundamentals of database systems - · pdf filechapter outline 1 informal design guidelines for...
TRANSCRIPT
![Page 1: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/1.jpg)
Chapter 9
Functional Dependencies and Normalization for Relational
Databases (from E&N and my editing)
![Page 2: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/2.jpg)
2DBMS odd 2010 D.W.W
Chapter Outline
1 Informal Design Guidelines for Relational Databases1.1Semantics of the Relation Attributes1.2 Redundant Information in Tuples and Update Anomalies1.3 Null Values in Tuples1.4 Spurious Tuples
2 Functional Dependencies (FDs)2.1 Definition of FD2.2 Inference Rules for FDs2.3 Equivalence of Sets of FDs2.4 Minimal Sets of FDs
![Page 3: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/3.jpg)
3DBMS odd 2010 D.W.W
Design ProcessConceptual
Design
ConceptualSchema
(ER Model)
LogicalDesign
Logical Schema(Relational Model)
Analysisof Schema
Step 2: NormalizationAnalyzing the Schema fromPerformance/Efficiency Perspectivesto arrive at “Optimal” Schema
Normalized Schema
![Page 4: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/4.jpg)
4DBMS odd 2010 D.W.W
1 Informal Design Guidelines for
Relational Databases (1)
What is relational database design?
The grouping of attributes to form "good" relation schemas
Two levels of relation schemas– The logical "user view" level– The storage "base relation" level
Design is concerned mainly with base relations What are the criteria for "good" base relations?
![Page 5: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/5.jpg)
5DBMS odd 2010 D.W.W
Informal Design Guidelines for Relational Databases (2)
We first discuss informal guidelines for good relational design
Then we discuss formal concepts of functional dependencies and normal forms- 1NF (First Normal Form)- 2NF (Second Normal Form)- 3NF (Third Normal Form)- BCNF (Boyce-Codd Normal Form)
Additional types of dependencies, further normal forms, relational design algorithms by synthesis are discussed in Chapter 11
![Page 6: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/6.jpg)
6DBMS odd 2010 D.W.W
1.1 Semantics of the Relation
Attributes GUIDELINE 1: Informally, each tuple in a relation
should represent one entity or relationship instance. (Applies to individual relations and their attributes).
Attributes of different entities (EMPLOYEEs, DEPARTMENTs, PROJECTs) should not be mixed in the same relation
Only foreign keys should be used to refer to other entities Entity and relationship attributes should be kept apart as much as possible.
Bottom Line: Design a schema that can be explained easily relation by relation. The semantics of attributes should be easy to interpret.
![Page 7: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/7.jpg)
7DBMS odd 2010 D.W.W
A simplified COMPANY relational database schema
![Page 8: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/8.jpg)
8DBMS odd 2010 D.W.W
1.2 Redundant Information in
Tuples and Update Anomalies Mixing attributes of multiple entities may cause
problemsInformation is stored redundantly wasting storageProblems with update anomalies
– Insertion anomalies– Deletion anomalies– Modification anomalies
![Page 9: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/9.jpg)
9DBMS odd 2010 D.W.W
EXAMPLE OF AN UPDATE
ANOMALY (1) Consider the relation:EMP_PROJ ( Emp#, Proj#, Ename, Pname, No_hours)
Update Anomaly: Changing the name of project
number P1 from “Billing” to “Customer-Accounting” may cause this update to be made for all 100 employees working on project P1.
![Page 10: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/10.jpg)
10DBMS odd 2010 D.W.W
EXAMPLE OF AN UPDATE ANOMALY (2)
Insert Anomaly: Cannot insert a project unless an employee is assigned to .
Inversely - Cannot insert an employee unless an he/she is assigned to a project.
Delete Anomaly: When a project is deleted, it will result in deleting all the employees who work on that project. Alternately, if an employee is the sole employee on a project, deleting that employee would result in deleting the corresponding project.
![Page 11: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/11.jpg)
11DBMS odd 2010 D.W.W
Two relation schemas suffering from update anomalies
![Page 12: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/12.jpg)
12DBMS odd 2010 D.W.W
Example States for EMP_DEPT and EMP_PROJ
![Page 13: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/13.jpg)
13DBMS odd 2010 D.W.W
Guideline to Redundant Information in Tuples and Update Anomalies
GUIDELINE 2: Design a schema that does not suffer from the insertion, deletion and update anomalies. If there are any present, then note them so that applications can be made to take them into account
![Page 14: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/14.jpg)
14DBMS odd 2010 D.W.W
1.3 Null Values in Tuples
GUIDELINE 3: Relations should be designed such that their tuples will have as few NULL values as possible
Attributes that are NULL frequently could be placed in separate relations (with the primary key)
Reasons for nulls:– attribute not applicable or invalid– attribute value unknown (may exist)– value known to exist, but unavailable
![Page 15: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/15.jpg)
15DBMS odd 2010 D.W.W
1.4 Spurious Tuples
Bad designs for a relational database may result in erroneous results for certain JOIN operations
The "lossless join" property is used to guarantee meaningful results for join operations
There are two important properties of decompositions:
–non-additive or losslessness of the corresponding join
–preservation of the functional dependencies.
Note that property (a) is extremely important and cannot be sacrificed. Property (b) is less stringent and may be sacrificed.
![Page 16: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/16.jpg)
16DBMS odd 2010 D.W.W
We’ve had Guidelines for: One Concept/Relation, Avoiding Update Anomalies, Null Values
Two Other “Related” Concerns Can Arise – First, in Decomposing (Splitting) a Relation Apart, we May
“Lose” Information
– Second, in Attempting to Reassemble Two or More Relations into One (via a Join), Spurious Tuples may Result
A Spurious Tuple “Wasn’t” Present Originally and Makes No Sense - Didn’t Exist and its Existence is Inconsistency
![Page 17: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/17.jpg)
17DBMS odd 2010 D.W.W
Suppose Split EMP_PROJ
![Page 18: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/18.jpg)
18DBMS odd 2010 D.W.W
Semantics of Split?
EMP_LOCS Means the Employee ENAME Works on Some Project at PLOCATION
EMP_PROJ1 Means the Employee Identified by SSN Works HOURS per Week on Project Identified by PNAME, PNUMBER, PLOCATION
![Page 19: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/19.jpg)
19DBMS odd 2010 D.W.W
Recall EMP_PROJ
![Page 20: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/20.jpg)
20DBMS odd 2010 D.W.W
Tuple after Split
![Page 21: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/21.jpg)
21DBMS odd 2010 D.W.W
The Issues?
Suppose EMP_PROJ1 and EMP_LOCS used in Place of EMP_PROJ
The Split is Legitimate if we Can Recover the Information Originally in EMP_PROJ
How could you Recover the Information?– Natural Join on EMP_PROJ1 and EMP_LOCS
– What would be the Result?
Note: “*’ed” Entries are Spurious Tuples
We do not Obtain the “Correct” Information
We have Conducted a “Lossy” Decomposition
![Page 22: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/22.jpg)
22DBMS odd 2010 D.W.W
When we do Join?
![Page 23: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/23.jpg)
23DBMS odd 2010 D.W.W
Guideline 4
GUIDELINE 4: – The relations should be designed to satisfy the lossless join
condition.
– No spurious tuples should be generated by doing a natural-join of any relations.
There are 2 important properties of decompositions – non-additive or losslessness of the corresponding join
– preservation of the functional dependencies.
– Property (a) is extremely important and cannot be sacrificed.
– Property (b) is less stringent and may be sacrificed
![Page 24: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/24.jpg)
24DBMS odd 2010 D.W.W
Lost information
R = (A, B, C) S = (D, C)
b2b2b4
c1c1c2
A B C
c1c2c2c3
d1d2d4d5
D C
a1a2a3a3
a1a2a3
b2b2b4b4
c1c1c2c2
d1d1d2d4
A B C D
RS(A, B, C, D)
lost info of (d5, c3) after join R & S
A First Example of Lost Information– What is Lost in the Join of R and S?
![Page 25: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/25.jpg)
25DBMS odd 2010 D.W.W
Spurious Tuple
A Second Example of Spurious Tuples– What are Spurious in the Join of R1and R2?
a1a2a3a4
b1b2b1b2
c1c2c1c2
d1d1d2d3
A B C D
R(A, B, C, D) R1(B, D)
B C
b1b2b1b2
d1d1d2d3
D
d1d1d2d3
A
a1a2a3a4
R2(A, D)
a1a2a1a2a3a4
b1b1b2b2b1b2
c1c2c2c2c1c2
d1d1d1d1d2d3
A B C D
R1 and R2 Join
![Page 26: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/26.jpg)
26DBMS odd 2010 D.W.W
2.1 Functional Dependencies (1)
Functional dependencies (FDs) are used to specify formal measures of the "goodness" of relational designs
FDs and keys are used to define normal forms for relations
FDs are constraints that are derived from the meaning and interrelationships of the data attributes
A set of attributes X functionally determines a set of attributes Y if the value of X determines a unique value for Y
![Page 27: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/27.jpg)
27DBMS odd 2010 D.W.W
Functional Dependencies (2)
X -> Y holds if whenever two tuples have the same value for X, they must have the same value for Y
For any two tuples t1 and t2 in any relation instance r(R): If t1[X]=t2[X], then t1[Y]=t2[Y]
X -> Y in R specifies a constraint on all relation instances r(R)
Written as X -> Y; can be displayed graphically on a relation schema as in Figures. ( denoted by the arrow: ).
FDs are derived from the real-world constraints on the attributes
![Page 28: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/28.jpg)
28DBMS odd 2010 D.W.W
Examples of FD constraints (1)
social security number determines employee nameSSN -> ENAME
project number determines project name and locationPNUMBER -> {PNAME, PLOCATION}
employee ssn and project number determines the hours per week that the employee works on the project{SSN, PNUMBER} -> HOURS
![Page 29: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/29.jpg)
29DBMS odd 2010 D.W.W
Examples of FD constraints (2)
An FD is a property of the attributes in the schema R
The constraint must hold on every relation instance r(R)
If K is a key of R, then K functionally determines all attributes in R (since we never have two distinct tuples with t1[K]=t2[K])
![Page 30: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/30.jpg)
30DBMS odd 2010 D.W.W
Ex
{S#, CN} → Grade, S# → DNAME, DNAME → DHead.
STUDENT_DEPT (S#, DName, DHead, CN, Grade)
FDs over STUDENT_DEPT:
S# DHead CN GradeDNAME
fd1
fd2
fd3
![Page 31: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/31.jpg)
31DBMS odd 2010 D.W.W
SSN → ENAMEPNUMBER → {PNAME, PLOCATION}{SSN, PNUMBER} → HOURS
SSN → {ENAME, BDATE, ADDRESS, DNUMBER}DNUMBER → {DNAME, DMGRSSN}
![Page 32: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/32.jpg)
32DBMS odd 2010 D.W.W
Determining FDs Must Understand the Semantics of Data Based on Schema or
Current/Future Instances
What are FDs Below?TEXT → COURSE?COURSE → TEXT?
What if I add Row “James, Web Databases, Al-Nour”?
![Page 33: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/33.jpg)
33DBMS odd 2010 D.W.W
2.2 Inference Rules for FDs (1)
Given a set of FDs F, we can Infer Additional FDs that Hold whenever the FDs in F Hold
For Example, Consider:
F = {SSN →{EName, BDate, Address, DNumber}, DNumber → {DName, DMGRSSN} }
What are Additional Fds?
SSN → EName SSN → BDate SSN → SSN
SSN → Address SSN → Dnumber
DNumber → Dname DNumber → DMGRSSN
![Page 34: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/34.jpg)
34DBMS odd 2010 D.W.W
Inference RulesArmstrong’s Inference Rules
Derived Inference Rules
1. Reflective (IR1): If X ⊇ Y, then X → Y.
2. Augmentation (IR2): If { X →Y} then XZ→YZ.
3. Transitive (IR3): If { X→Y, Y→Z } then X →Z.
4. Decomposition: If { X→YZ } then X →Y.5. Additive (Union): If {X→Y, X→Z } then X →YZ.
6. Pseudotransitive: If {X→Y, WY→Z } then W X →Z.
![Page 35: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/35.jpg)
35DBMS odd 2010 D.W.W
Closure of a set F of FDs is the set F+ of all FDs that can be inferred from F
Closure of a set of attributes X with respect to F is the set X + of all attributes that are functionally determined by X
X + can be calculated by repeatedly applying IR1, IR2, IR3 using the FDs in F
![Page 36: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/36.jpg)
36DBMS odd 2010 D.W.W
Alg of ClosureGiven a Set of FDs, Each X on LHS of a FD, Closure
X+ are the Attributes Functionally Determined by X
Algorithm 10.1 Determining X+, Closure of X under F
X+ := X;
repeat
1. oldX+ := X+ ;
2. for each FD Y → Z in F do
3. if X+ ⊇ Y then X+ := X+ ∪ Z;
until (X+ = old X+ );
![Page 37: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/37.jpg)
37DBMS odd 2010 D.W.W
Applying AlgLet F = {SSN → ENAME,
PNUMBER → {PNAME, PLOCATION}, {SSN, PNUMBER} → HOURS }
{SSN}+ = {SSN, ENAME}{PNUMBER}+ = {PNUMBER, PNAME, PLOCATION } {SSN, PNUMBER}+ = {SSN, PNUMBER, ENAME,
PNAME, PLOCATION, HOURS}For Example: Computer {SSN, PNUMBER}+
Is {SSN, PNUMBER} ⊇ SSN? Yes - Add in ENAME
Is {SSN, PNUMBER, ENAME} ⊇ PNUMBER? Yes - Add in PNAME, PLOCATION
Is {SSN, PNUMBER, ENAME, PNAME, PLOCATION} ⊇ {SSN, PNUMBER}?
Yes - Add in HOURS
![Page 38: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/38.jpg)
38DBMS odd 2010 D.W.W
Interpreting Closure SetWhat Does {SSN}+ = {SSN, ENAME} Equate to?
SSN → ENAME, SSN → SSN, SSN → SSN, ENAMEWhat about {PNUMBER}+ ={PNUMBER,PNAME,PLOCATION}
PNUMBER → PNAME
PNUMBER → PLOCATION
PNUMBER → PNUMBER
PNUMBER → {PNAME, PLOCATION}
PNUMBER → {PNUMBER, PLOCATION}
PNUMBER → {PNAME, PNUMBER}
PNUMBER → {PNUMBER, PNAME, PLOCATION}Can you do:{SSN, PNUMBER}+ = {SSN, PNUMBER, ENAME,
PNAME, PLOCATION, HOURS}
![Page 39: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/39.jpg)
39DBMS odd 2010 D.W.W
Compute {DNUMBER}+
Is {DNUMBER} ⊇ SSN ? No - Continue to Next FD
Is {DNUMBER} ⊇ DNUMBER ? Yes - Add in DNAME, DMGRSSN
{DNUMBER}+ = {DNUMBER, DNAME, DMGRSSN}
FYI: {SSN} + ={SSN, ENAME, BDATE, ADDRESS, DNUMBER, DNAME, DMGRSSN}
Let G = {SSN → {ENAME, BDATE, ADDRESS, DNUMBER}, DNUMBER → {DNAME, DMGRSSN} }
![Page 40: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/40.jpg)
40DBMS odd 2010 D.W.W
Inference Rules Properties
Theorem 1:X → A1A2... An Holds in a Relation Scheme R if and only if X → Ai Holds for all i = 1, ..., n
Theorem 2:Armstrong's Inference Rules are Sound and Complete
Given a set F of Functional Dependencies:By Sound we mean every FD that we can infer from F by using
Armstrong’s Inference Rules is in F+
By Complete we mean every FD in F+ (that F implies) can be Inferred from F by using Armstrong’s Inference Rules
![Page 41: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/41.jpg)
41DBMS odd 2010 D.W.W
2.3 Equivalence of Sets of FDs
TE is Covered by F (F is said to cover E), if Every FD in E is also in F+
Two sets of FDs E and F are equivalent if:– Every FD in E can be Inferred from F, and
– Every FD in F can be Inferred from E
Hence, F and G are Equivalent if F+=G+
Definition: F covers F if every FD in F can be Inferred from F (i.e., if F+ ⊆ E+)
E and F are Equivalent if E Covers F and F Covers E
![Page 42: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/42.jpg)
42DBMS odd 2010 D.W.W
3 Normal Forms Based on Primary
Keys 3.1 Normalization of Relations 3.2 Practical Use of Normal Forms 3.3 Definitions of Keys and Attributes
Participating in Keys 3.4 First Normal Form3.5 Second Normal Form3.6 Third Normal Form
![Page 43: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/43.jpg)
43DBMS odd 2010 D.W.W
Normalization of Relation
Normalization: The process of decomposing unsatisfactory "bad" relations by breaking up their attributes into smaller relations
Normal form: Condition using keys and FDs of a relation to certify whether a relation schema is in a particular normal form
![Page 44: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/44.jpg)
44DBMS odd 2010 D.W.W
1NF based on definition of relation2NF, 3NF, BCNF based on keys and FDs of a
relation schema4NF based on keys, multi-valued dependencies :
MVDs; 5NF based on keys, join dependencies : JDs
Additional properties may be needed to ensure a good relational design (lossless join, dependency preservation)
![Page 45: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/45.jpg)
45DBMS odd 2010 D.W.W
Whats Normal Form A Normal Form is a Condition using Keys and FDs to Certify
Whether a Relation Schema meets Criteria
– Primary keys (1NF, 2NF, 3NF)
– All Candidate Keys ( 2NF, 3NF, BCNF)
– Multivalued Dependencies (4NF)
– Join Dependencies (5NF)
5 NF4NF
3NF
2NF
1NF
![Page 46: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/46.jpg)
46DBMS odd 2010 D.W.W
Practical Use of Norm Form
Normalization is carried out in practice so that the resulting designs are of high quality and meet the desirable properties
The practical utility of these normal forms becomes questionable when the constraints on which they are based are hard to understand or to detect
The database designers need not normalize to the highest possible normal form. (usually up to 3NF, BCNF or 4NF)
Denormalization: the process of storing the join of higher normal form relations as a base relation—which is in a lower normal form
![Page 47: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/47.jpg)
47DBMS odd 2010 D.W.W
Keys and Participating
A superkey of a relation schema R = {A1, A2, ...., An}
is a set of attributes S subset-of R with the property that no two tuples t1 and t2 in any legal relation state r
of R will have t1[S] = t2[S]
A key K is a superkey with the additional property that removal of any attribute from K will cause K not to be a superkey any more.
![Page 48: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/48.jpg)
48DBMS odd 2010 D.W.W
If a relation schema has more than one key, each is called a candidate key. One of the candidate keys is arbitrarily designated to be the primary key, and the others are called secondary keys.
A Prime attribute must be a member of some candidate key
A Nonprime attribute is not a prime attribute—that is, it is not a member of any candidate key.
![Page 49: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/49.jpg)
49DBMS odd 2010 D.W.W
3.2 First Normal Form
Disallows composite attributes, multivalued attributes, and nested relations; attributes whose values for an individual tuple are non-atomic
Considered to be part of the definition of relation
![Page 50: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/50.jpg)
50DBMS odd 2010 D.W.W
All Attributes Must Be Atomic Values:– Only Simple and Indivisible Values in the Domain of
Attributes.
– Each Attribute in a 1NF Relation is a Single Value
– Disallows Composite Attributes, Multivalued Attributes, and Nested Relations (Non-Atomic)
1NF Relation cannot have an Attribute Value :– A Set of Values (Set-Value)
– A Tuple of Values (Nested Relation)
1NF is a Standard Assumption of Relation DBs
![Page 51: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/51.jpg)
51DBMS odd 2010 D.W.W
Normalization into 1NFConsider Following Department RelationWhat is the Inherent Problem?
![Page 52: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/52.jpg)
52DBMS odd 2010 D.W.W
Normalization nested relations into 1NF
Transition to:
![Page 53: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/53.jpg)
53DBMS odd 2010 D.W.W
Possible Solutions Decompose: Move the Attribute DLOCATIONS that Violates
1NF into a Separate Relation DEPT_LOCATIONS(DNUMBER, DLOCATION)
Expand the key to have a Separate Tuple in the DEPARTMENT relation for each location (below)
Introduce DLOC1, DLOC2, DLOC3, if there are Three Maximum Locations
Problems with Each? Best Solution?
![Page 54: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/54.jpg)
54DBMS odd 2010 D.W.W
3.3 Second Normal Form (1) Uses the concepts of FDs, primary key
Definitions:Prime attribute - attribute that is member of the
primary key KFull functional dependency - a FD Y -> Z where
removal of any attribute from Y means the FD does not hold any moreExamples: - {SSN, PNUMBER} -> HOURS is a full FD since neither SSN -> HOURS nor PNUMBER -> HOURS hold
- {SSN, PNUMBER} -> ENAME is not a full FD (it is called a partial dependency ) since SSN -> ENAME also holds
![Page 55: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/55.jpg)
55DBMS odd 2010 D.W.W
Second Normal Form (2)
A relation schema R is in second normal form (2NF) if every non-prime attribute A in R is fully functionally dependent on the primary key
R can be decomposed into 2NF relations via the process of 2NF normalization
![Page 56: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/56.jpg)
56DBMS odd 2010 D.W.W
Second Normal Form Focuses on the Concepts of Primary Keys and Full Functional Dependencies
Intuitively:– A Relation Schema R is in Second Normal Form (2NF) if
Every Non-Prime Attribute A in R is Fully Functionally Dependent on the Primary Key
– R can be Decomposed into 2NF Relations via the Process of 2NF Normalization
– Successful Process Typically Involves Decomposing R into Two or More Relations
– Iteratively Applying to Each Relation in Schema
![Page 57: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/57.jpg)
57DBMS odd 2010 D.W.W
Full FD - Formally:Given R(U) and X, Y⊆U. If X→Y holds, and there exists no such X’ that X’⊂X, and X’→Y holds over R, then Y is fully dependent on X, denoted as X→Y
Full FD- Intuitively: A FD X→Y where Removal of any Attribute from X means the FD no Longer Holds
– {SSN, PNUMBER} → HOURS is full since Neither
SSN HOURS nor PNUMBER → HOURS holds
– What about in the Following:
![Page 58: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/58.jpg)
58DBMS odd 2010 D.W.W
Partial FD - Formally:Given R(U) and X, Y⊆U. If X→Y holds but Y is not fully dependent on X ( X→Y), then Y is partially functional dependent on X, denoted by X→Y
Partial FD - Intuitively: Removal of a Attribute from the R.H.S. still Results in a Valid FD– {SSN, PNUMBER} → ENAME is Partial since Removing
PNUMBER still Results in the Valid FD SSN → ENAME
– Are Following Full or Partial?
![Page 59: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/59.jpg)
59DBMS odd 2010 D.W.W
Formal 2NF Definition R ∈ 2NF iff– (i) R ∈ 1NF;
– (ii) all Non-Key Attributes in R are Fully Functional Dependent on Every Key.
Alternative Definition: R ∈ 2NF iff the Attributes are Either – a Candidate Key, or
– Fully Dependent on Every Key.
Reason: Partial Functional Dependencies may cause Update Problems
![Page 60: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/60.jpg)
60DBMS odd 2010 D.W.W
ExConsider the Example Below
STUDENT_DEPT(S#, DName, DHead, CN, Grade)
STUDENT_DEPT ∈ 1NF
“{S#, CN} → DName, DHead” is a Partial FD which causes Update Anomalies
But STUDENT_DEPT ∉ 2NF
S# DHead CN GradeDName
fd1
fd2
fd3
![Page 61: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/61.jpg)
61DBMS odd 2010 D.W.W
STUDENT_DEPT(S#, DName, DHead, CN, Grade)
Insertion Anomalies: – No Department Can Be Recorded if it has No Student Who
Enrolls Courses
Deletion Anomalies: – Delete the Last Student in a Department will also Delete the
Department
Update Anomalies: – Change a Head of a Department must Modify All Students in
that Department Due to Redundancies
![Page 62: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/62.jpg)
62DBMS odd 2010 D.W.W
Decomposition into 2NF by Separating Course Information from Department Information (Link S#)
S_D(S#, DName, DHead)
DHeadDName
fd2
fd3
S#
S_C(S#, CN, Grade)
fd1
S# CN Grade
![Page 63: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/63.jpg)
63DBMS odd 2010 D.W.W
Another Ex
EMP_PROJ is 1NF with Key SSN, PNUMBER but…
– SSN → ENAME - Means ENAME, a Non-Prime Attribute, Depends Partially on SSN, PNUMBER, i.e., Depend on Only SSN and not Both
– PNUMBER → {PNAME, PLOCATION} - Means PNAME, PLOCATION, two Non-Prime Attributes, Depends Partially on SSN, PNUMBER, i.e., Depend on Only PNUMEBER and not Both
![Page 64: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/64.jpg)
64DBMS odd 2010 D.W.W
What Does Decomposition Below Accomplish?– ENAME Fully Dependent on SSN
– PNAME, PLOC Fully Dependent on PNUMBER
Result: 2NF for EP1, EP2, and EP3
![Page 65: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/65.jpg)
65DBMS odd 2010 D.W.W
Consider 1NF Lots to Track Building Lots for Towns
What is the 2NF Problem?– FD3: COUNTY_NAME → TAX_RATE Means TAX_RATE
Depends Partially on Candidate Key {PROPERTY_ID#,COUNTY_NAME}
– All Other Non-Prime Attributes are Fine
![Page 66: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/66.jpg)
66DBMS odd 2010 D.W.W
What Does Decomposition Below Accomplish?– TAX_RATE Fully Dependent on COUNTY_NAME
Result: 2NF for LOTS1 and LOTS2
![Page 67: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/67.jpg)
67DBMS odd 2010 D.W.W
Third Normal Form (3NF)
Third Normal Form Focuses on the Concepts of Primary Keys and Transitive Functional Dependencies
Intuitively:– A Relation Schema R is in Third Normal Form (3NF) if it is in
2NF and no Non-Prime Attribute A in R is Transitively Dependent on Primary Key
– R can be Decomposed into 3NF Relations via the Process of 3NF Normalization
In X→Y and Y→ Z , with X as the Primary Key, there is only a a problem only if Y is not a candidate key. EMP(SSN, Emp#, Salary), SSN → Emp# → Salary isn’t Problem Since Emp# is a Candidate Key
![Page 68: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/68.jpg)
68DBMS odd 2010 D.W.W
Transitive FDsTransitive FD - Formally:
Given R(U) and X, Y⊆U. If X→Y, Y⊆X and Y→X, Y→Z, then Z is called transitively functional dependent on X.
Transitive FD - Intuitively: a FD X→ Z that can be derived from two FDs X→Y and Y→Z– SSN → ENAME is non-transitive Since there is no set of Attributes X
where SSN → X and X → ENAME
S# DHead CN GradeDNAME
fd1
fd2
fd3
![Page 69: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/69.jpg)
69DBMS odd 2010 D.W.W
Formal 3NF Definition R ∈ 3NF iff– (i) R ∈ 2NF;
– (ii) No Non-Key Attribute of R is Transitively Dependent on Every Candidate Key.
Alternative Definition: R ∈ 3NF iff for every FD X → Y, either – X is a superkey, or
– Y is a key attribute.
Reason: Transitive Functional Dependencies may cause Update Problems
![Page 70: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/70.jpg)
70DBMS odd 2010 D.W.W
ExSTUDENT_DEPT(S#, DName, DHead, CN, Grade) ∉ 2NF
S_C(S#, CN, Grade) ∈ 2NF
S_D(S#, DName, DHead) ∈ 2NF
S_D ∉ 3NFS_C ∈ 3NF But
“S# → DHead” is a Transitive FD in S_D and “DHead” is non-key attribute.
![Page 71: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/71.jpg)
71DBMS odd 2010 D.W.W
S_C(S#, CN, Grade) ∈ 2NF
S_D(S#, DName, DHead) ∈ 2NF
S_D (S#, DName)
DEPT(DName, DHead)∈ 3NF
fd2 S# → DName
fd3 DName → DHead
DHeadDNameS#
fd S# → DHead
Decompose to Eliminate the Transitivity Within S_D
![Page 72: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/72.jpg)
72DBMS odd 2010 D.W.W
EMP_DEPT is 2NF with Key SSN, but there are Two Transitive Dependencies (Undesirable)– SSN → DNUMBER and DNUMBER → DNAME
Means DNAME, Neither Key Nor Subset of Key, is Transitively Dependent on SSN
– SSN is the Only Candidate Key of EMP_DEPT!
– Note: Also Similar Problem with SSN and DMGRSSN via DNUMBER
![Page 73: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/73.jpg)
73DBMS odd 2010 D.W.W
To Attain 3NF, Decompose into ED1 and ED2Intuitively - we are Separating Out Employees and
Departments from One Another
![Page 74: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/74.jpg)
74DBMS odd 2010 D.W.W
Recall 2NF Solution for Building Lots Problem
What is the 3NF Problem? Violate Alternative Defn.– In LOTS1, FD4 AREA → PRICE
AREA is not a SuperkeyPRICE not a Prime Attribute of LOTS1
![Page 75: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/75.jpg)
75DBMS odd 2010 D.W.W
Decompose to Introduce a Separate Key AREA
Result: 3NF for LOTS1A and LOTS1B
![Page 76: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/76.jpg)
76DBMS odd 2010 D.W.W
SummarySTUDENT_DEPT
1NF
S# DHead CN GradeDName
fd1
fd2
fd3 S_C S_D2NF
eliminate partial FDs
fd1
S# CN Grade DHeadDName
fd2
fd3
S#
DHead
S#S_D
DName
DEPT
S_C
3NF
eliminate transitive FDs
fd1
S# CN Grade
DName
fd3
fd2
![Page 77: Fundamentals of Database Systems - · PDF fileChapter Outline 1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes ... in Tuples and Update](https://reader031.vdocuments.us/reader031/viewer/2022022423/5a9de72a7f8b9aee528ded3a/html5/thumbnails/77.jpg)
77DBMS odd 2010 D.W.W
Test Remedy (Normalization)
1NF Relation should have Form new relations for each nonatomic no nonatomic attributes attribute or nested relation. or nested relations.
2NF For relations where primary Decompose and set up a new relation key contains multiple for each partial key with its dependent attributes, no nonkey attribute(s). Make sure to keep a attribute should be relation with the original primary key functionally dependent on and any attributes that are fully a part of the primary key. functionally dependent on it.
3NF Relation should not have a Decompose and set up a relation that nonkey attribute functionally includes the nonkey attribute(s) that determined by another nonkey functionally determine(s) other attribute (or by a set of nonkey nonkey attribute(s). attributes.) That is, there should be no transitive dependency of a nonkey attribute on the primary key.