Tom Kelliher, CS 318
Mar. 6, 2002
Relationship between SQL, relational algebra, and calculi.
Further properties of functional dependencies.
Consider the following E/R diagram, schema, and relation instance:
CREATE TABLE Person ( SSN INTEGER, Name CHAR(20), Address CHAR(50), Hobby CHAR(20), PRIMARY KEY (SSN, Hobby));Note that neither SSN nor Hobby may be NULL, since they're part of a primary key.
We have to come up with an effective ``No Hobby'' hobby.
This is counter-intuitive, making three DB changes to reflect one real change.
Another special case!
Person(SSN, Name, Address) Hobby(SSN, Hobby)
SSN(SSN) Name(Name) Address(Address) Hobby(Hobby)
A constraint over a relation R of the form where and are sets of attributes of . If is an instance of , it is said to satisfy this functional dependency if:Informally, we would say determines .For every pair of tuples s and t in , if s and t agree on then s and t agree on .
How are our update anomalies manifest here?
Any other dependencies in the first Person relation?
Entailment: Consider all relation instances over attributes . We say the set of FDs entails the FD f if the following property is true:
If satisfies every FD in , then satisfies FD f.The closure of (written ) is the set of all FDs entailed by . Note .
Proof. Consider a relation instance over attributes . If agree on , then they agree on . Hence, .
Such trivial FDs are entailed by any set of FDs.
Proof is a homework problem.