Functional Dependencies - Data Base Management System - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Functional Dependencies - Data Base Management System - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

• Revision for Mid

Functional Dependencies

• FDs defined over two sets of attributes: X, Y ⊂ R
• Notation: X  Y reads as “X determines Y”
• If X  Y, then all tuples that agree on X must also agree on Y

X Y Z

R

Candidate Keys

• an attribute (or set of attributes) that

uniquely identifies a row

• primary key is a special candidate key

• values cannot be null

• e.g.

• ENROLL (Student_ID, Name, Address, …)
  • PK = Student_ID
  • candidate key = Name, Address

EMPLOYEE2 (Emp_ID, Course_Title, Name,

Dept_Name, Salary, Date_Completed)

Emp_ID

Course_ Title

Name

Dept_ Name

Salary Date_Comp.

not fully functionally

dependant on the primary key

Second Normal Form ( 2NF )

• it is based on the concept of full functional dependency.
• A functional dependency X→Y is a full functional

dependency , for any attribute A ∈ X, {X - {A}} → Y.

Second Normal Form

Second normal form: Let R’ be a relation, and let F be the set of governing FDs. An attribute belongs to R’ is prime if a key of R’ contains A. In other words, A is prime in R’ if there exists K<R’ such that (1) K->R’, (2) for all B belongs to K, (K-B)->R’ not belongs to F+, and (3) A belongs to K

Third Normal Form

• The definition of 3NF is similar to that of BCNF, with the only difference being the third condition.
• Recall that a key for a relation is a minimal set of attributes that uniquely determines all other attributes. - A must be part of a key (any key, if there are several). - It is not enough for A to be part of a superkey, because this condition is satisfied by every attribute.

A relation R is in 3NF if, for all X → A that holds over R  A ∈ X ( i.e., X → A is a trivial FD ), or  X is a superkey, or  A is part of some key for R

If R is in BCNF, obviously it is in 3NF.

• Suppose that a dependency X  A causes a

violation of 3NF. There are two cases:

• X is a proper subset of some key K. Such a

dependency is sometimes called a partial

dependency. In this case, we store (X,A) pairs

redundantly.

• X is not a proper subset of any key. Such a

dependency is sometimes called a transitive

dependency, because it means we have a chain

of dependencies K  XA.

• Motivation of 3NF
  • By making an exception for certain dependencies involving key attributes, we can ensure that every relation schema can be decomposed into a collection of 3NF relations using only decompositions.
  • Such a guarantee does not exist for BCNF relations.
  • It weaken the BCNF requirements just enough to make this guarantee possible.
• Unlike BCNF, some redundancy is possible with 3NF.
  • The problems associate with partial and transitive dependencies persist if there is a nontrivial dependency XA and X is not a superkey, even if the relation is in 3NF because A is part of a key.

Reserves

 Assume: sid → cardno (a sailor uses a unique credit card to pay for reservations).  Reserves is not in 3NF  sid is not a key and cardno is not part of a key  In fact, (sid, bid, day) is the only key.  (sid, cardno) pairs are redundantly.

Today’s lecture

• Where does SQL differ from relational model?

• What are some other features of SQL?

• How can we extend the relational algebra to

match more closely SQL?

Duplicate rows

• Consider our relation instances from lecture 6,

Reserves, Sailors and Boats

• Consider

SELECT rating,age FROM Sailors;

• We get a relation that doesn’t satisfy our

definition of a relation!

• RECALL: We have the keyword DISTINCT to

remove duplicates