lecture 13: relational decomposition and relational algebra february 5 th, 2003
TRANSCRIPT
![Page 1: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/1.jpg)
Lecture 13:
Relational Decomposition and Relational Algebra
February 5th, 2003
![Page 2: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/2.jpg)
Summary of Previous Discussion
• FD’s are given as part of the schema. You derived keys from them. You can also specify keys directly (they’re just another FD)
• First, compute the keys and the FD’s that hold on the relation you’re considering.
• Then, look for violations of BCNF. There may be a few. Choose one.
![Page 3: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/3.jpg)
Summary (continued)
• You may need to decompose the decomposed relations:– To decide that, you need to project the FD’s on
the decomposed relations.– The end result may differ, depending on which
violation you chose to decompose by. They’re all correct.
• Relations with 2 attributes are in BCNF.
![Page 4: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/4.jpg)
Summary (continued –2)
• If you have only a single FD representing a key, then the relation is in BCNF.
• But, you can still have another FD that forces you to decompose.
• This is all really pretty simple if you think about it long enough.
![Page 5: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/5.jpg)
Boyce-Codd Normal Form
A simple condition for removing anomalies from relations:
In English (though a bit vague):
Whenever a set of attributes of R is determining another attribute, should determine all the attributes of R.
A relation R is in BCNF if:
Whenever there is a nontrivial dependency A1, ..., An B
in R , {A1, ..., An} is a key for R
A relation R is in BCNF if:
Whenever there is a nontrivial dependency A1, ..., An B
in R , {A1, ..., An} is a key for R
![Page 6: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/6.jpg)
Example Decomposition Person(name, SSN, age, hairColor, phoneNumber)
SSN name, age, hairColorage hairColor
Decompose in BCNF (in class):
Step 1: find all keys ssn, phoneNumber
SSN, name, age, hairColorSSN, phoneNumber
Step 2: now decompose
![Page 7: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/7.jpg)
Other Example
• R(A,B,C,D) A B, B C
• Keys:
• Violations of BCNF:
![Page 8: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/8.jpg)
Correct Decompositions A decomposition is lossless if we can recover:
R(A,B,C)
R1(A,B) R2(A,C)
R’(A,B,C) should be the same as R(A,B,C)
R’ is in general larger than R. Must ensure R’ = R
Decompose
Recover
![Page 9: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/9.jpg)
Correct Decompositions
• Given R(A,B,C) s.t. AB, the decomposition into R1(A,B), R2(A,C) is lossless
![Page 10: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/10.jpg)
3NF: A Problem with BCNFUnit Company Product
Unit Company
Unit Product
FD’s: Unit Company; Company, Product UnitSo, there is a BCNF violation, and we decompose.
Unit Company
No FDs
![Page 11: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/11.jpg)
So What’s the Problem?
Unit Company Product
Unit Company Unit Product
Galaga99 UW Galaga99 databasesBingo UW Bingo databases
No problem so far. All local FD’s are satisfied.Let’s put all the data back into a single table again:
Galaga99 UW databasesBingo UW databases
Violates the dependency: company, product -> unit!
![Page 12: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/12.jpg)
Solution: 3rd Normal Form (3NF)
A simple condition for removing anomalies from relations:
A relation R is in 3rd normal form if :
Whenever there is a nontrivial dependency A1, A2, ..., An Bfor R , then {A1, A2, ..., An } a super-key for R, or B is part of a key.
A relation R is in 3rd normal form if :
Whenever there is a nontrivial dependency A1, A2, ..., An Bfor R , then {A1, A2, ..., An } a super-key for R, or B is part of a key.
![Page 13: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/13.jpg)
Relational Algebra
• Formalism for creating new relations from existing ones
• Its place in the big picture:
Declartivequery
language
Declartivequery
languageAlgebraAlgebra ImplementationImplementation
SQL,relational calculus
Relational algebraRelational bag algebra
![Page 14: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/14.jpg)
Relational Algebra• Five operators:
– Union: – Difference: -– Selection:– Projection: – Cartesian Product:
• Derived or auxiliary operators:– Intersection, complement– Joins (natural,equi-join, theta join, semi-join)– Renaming:
![Page 15: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/15.jpg)
1. Union and 2. Difference
• R1 R2
• Example: – ActiveEmployees RetiredEmployees
• R1 – R2
• Example:– AllEmployees -- RetiredEmployees
![Page 16: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/16.jpg)
What about Intersection ?
• It is a derived operator
• R1 R2 = R1 – (R1 – R2)
• Also expressed as a join (will see later)
• Example– UnionizedEmployees RetiredEmployees
![Page 17: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/17.jpg)
3. Selection
• Returns all tuples which satisfy a condition
• Notation: c(R)
• Examples– Salary > 40000 (Employee)
– name = “Smithh” (Employee)
• The condition c can be =, <, , >, , <>
![Page 18: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/18.jpg)
Selection Example
EmployeeSSN Name DepartmentID Salary999999999 John 1 30,000777777777 Tony 1 32,000888888888 Alice 2 45,000
SSN Name DepartmentID Salary888888888 Alice 2 45,000
Find all employees with salary more than $40,000.Salary > 40000 (Employee)
![Page 19: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/19.jpg)
4. Projection
• Eliminates columns, then removes duplicates
• Notation: A1,…,An (R)
• Example: project social-security number and names:– SSN, Name (Employee)
– Output schema: Answer(SSN, Name)
![Page 20: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/20.jpg)
Projection Example
EmployeeSSN Name DepartmentID Salary999999999 John 1 30,000777777777 Tony 1 32,000888888888 Alice 2 45,000
SSN Name999999999 John777777777 Tony888888888 Alice
SSN, Name (Employee)
![Page 21: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/21.jpg)
5. Cartesian Product
• Each tuple in R1 with each tuple in R2
• Notation: R1 R2
• Example: – Employee Dependents
• Very rare in practice; mainly used to express joins
![Page 22: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/22.jpg)
Cartesian Product Example Employee Name SSN John 999999999 Tony 777777777 Dependents EmployeeSSN Dname 999999999 Emily 777777777 Joe Employee x Dependents Name SSN EmployeeSSN Dname John 999999999 999999999 Emily John 999999999 777777777 Joe Tony 777777777 999999999 Emily Tony 777777777 777777777 Joe
![Page 23: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/23.jpg)
Relational Algebra• Five operators:
– Union: – Difference: -– Selection:– Projection: – Cartesian Product:
• Derived or auxiliary operators:– Intersection, complement– Joins (natural,equi-join, theta join, semi-join)– Renaming:
![Page 24: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/24.jpg)
Renaming
• Changes the schema, not the instance
• Notation: B1,…,Bn (R)
• Example:– LastName, SocSocNo (Employee)
– Output schema: Answer(LastName, SocSocNo)
![Page 25: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/25.jpg)
Renaming Example
EmployeeName SSNJohn 999999999Tony 777777777
LastName SocSocNoJohn 999999999Tony 777777777
LastName, SocSocNo (Employee)
![Page 26: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/26.jpg)
Natural Join• Notation: R1 R2⋈• Meaning: R1 R2 = ⋈ A(C(R1 R2))
• Where:– The selection C checks equality of all common
attributes– The projection eliminates the duplicate common
attributes
![Page 27: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/27.jpg)
Natural Join Example
EmployeeName SSNJohn 999999999Tony 777777777
DependentsSSN Dname999999999 Emily777777777 Joe
Name SSN DnameJohn 999999999 EmilyTony 777777777 Joe
Employee Dependents = Name, SSN, Dname( SSN=SSN2(Employee x SSN2, Dname(Dependents))
![Page 28: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/28.jpg)
Natural Join
• R= S=
• R ⋈ S=
A B
X Y
X Z
Y Z
Z V
B C
Z U
V W
Z V
A B C
X Z U
X Z V
Y Z U
Y Z V
Z V W
![Page 29: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/29.jpg)
Natural Join
• Given the schemas R(A, B, C, D), S(A, C, E), what is the schema of R ⋈ S ?
• Given R(A, B, C), S(D, E), what is R ⋈ S ?
• Given R(A, B), S(A, B), what is R ⋈ S ?
![Page 30: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/30.jpg)
Theta Join
• A join that involves a predicate
• R1 ⋈ R2 = (R1 R2)
• Here can be any condition
![Page 31: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/31.jpg)
Eq-join
• A theta join where is an equality
• R1 ⋈A=B R2 = A=B (R1 R2)
• Example:– Employee ⋈SSN=SSN Dependents
• Most useful join in practice
![Page 32: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/32.jpg)
Semijoin
• R ⋉ S = A1,…,An (R ⋈ S)
• Where A1, …, An are the attributes in R
• Example:– Employee ⋉ Dependents
![Page 33: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/33.jpg)
Semijoins in Distributed Databases
• Semijoins are used in distributed databases
SSN Name
. . . . . .
SSN Dname Age
. . . . . .
EmployeeDependents
network
Employee ⋈ssn=ssn (age>71 (Dependents))Employee ⋈ssn=ssn (age>71 (Dependents))
T = SSN age>71 (Dependents)R = Employee T⋉
Answer = R ⋈ Dependents
![Page 34: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/34.jpg)
Complex RA Expressions
Person Purchase Person Product
name=fred name=gizmo
pid ssn
seller-ssn=ssn
pid=pid
buyer-ssn=ssn
name
![Page 35: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/35.jpg)
Operations on Bags
A bag = a set with repeated elements
All operations need to be defined carefully on bags• {a,b,b,c}{a,b,b,b,e,f,f}={a,a,b,b,b,b,b,c,e,f,f}• {a,b,b,b,c,c} – {b,c,c,c,d} = {a,b,b,d}
• C(R): preserve the number of occurrences
• A(R): no duplicate elimination
• Cartesian product, join: no duplicate elimination
Important ! Relational Engines work on bags, not sets !
Reading assignment: 5.3 – 5.4
![Page 36: Lecture 13: Relational Decomposition and Relational Algebra February 5 th, 2003](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697c0021a28abf838cc2bbc/html5/thumbnails/36.jpg)
Finally: RA has Limitations !
• Cannot compute “transitive closure”
• Find all direct and indirect relatives of Fred• Cannot express in RA !!! Need to write C program
Name1 Name2 Relationship
Fred Mary Father
Mary Joe Cousin
Mary Bill Spouse
Nancy Lou Sister