05-design-theory

05-design-theory - Announcements (Thu. Sep. 15) Homework #1...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Relational Database Design Theory CPS 116 Introduction to Database Systems 2 Announcements (Thu. Sep. 15) ± Homework #1 due next Tuesday ² Web-based submission preferred over hard copies ± Watch your email for announcements of “refreshes” of /home/dbcourse/ on your virtual machine 3 Motivation ± How do we tell if a design is bad, e.g., StudentEnroll ( SID , name , CID )? ² This design has redundancy, because the name of a student is recorded multiple times, once for each course the student is taking • Update, insertion, deletion anomalies ± How about a systematic approach to detecting and removing redundancy in designs? ² Dependencies, decompositions, and normal forms SID name CID 142 Bart CPS116 142 Bart CPS114 857 Lisa CPS116 857 Lisa CPS130 …… 4 Functional dependencies ± A functional dependency (FD) has the form X Y , where X and Y are sets of attributes in a relation R ± X Y means that whenever two tuples in R agree on all the attributes in X , they must also agree on all attributes in Y XYZ abc ab? ……… Must be b Could be anything 5 FD examples Address ( street_address , city , state , zip ) ± street_address , city , state zip ± zip city , state ± zip , state zip ? ² This is a trivial FD ² Trivial FD: LHS RHS ± zip state , zip ? ² This is non-trivial, but not completely non-trivial ² Completely non-trivial FD: LHS Å RHS = 6 Keys redefined using FD’s A set of attributes K is a key for a relation R if ± K all (other) attributes of R ² That is, K is a “super key” ± No proper subset of K satisfies the above condition ² That is, K is minimal
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 7 Reasoning with FD’s Given a relation R and a set of FD’s F ± Does another FD follow from F ? ² Are some of the FD’s in F redundant (i.e., they follow from the others)? ± Is K a key of R ? ² What are all the keys of R ? 8 Attribute closure ± Given R , a set of FD’s F that hold in R , and a set of attributes Z in R : The closure of Z (denoted Z + ) with respect to F is the set of all attributes { A 1 , A 2 , …} functionally determined by Z (that is, Z A 1 A 2 …) ± Algorithm for computing the closure ² Start with closure = Z ² If X Y is in F and X is already in the closure, then also add Y to the closure ² Repeat until no more attributes can be added 9 A more complex example StudentGrade ( SID , name , email , CID , grade ) ± SID name , email ± email SID ± SID , CID grade (Not a good design, and we will see why later) 10 Example of computing closure ± F includes: ² SID name , email ² email SID ² SID , CID grade ± { CID , email } + = ? ± email SID ² Add SID ; closure is now { CID , email , SID } ± SID name , email ² Add name , email ; closure is now { CID , email , SID , name } ± SID , CID grade ² Add grade ; closure is now all the attributes in StudentGrade 11 Using attribute closure Given a relation R and set of FD’s F ± Does another FD X Y follow from F ?
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This document was uploaded on 01/17/2012.

Page1 / 7

05-design-theory - Announcements (Thu. Sep. 15) Homework #1...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online