{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

05-design-theory-notes

05-design-theory-notes - Relational Database Design Theory...

Info iconThis preview shows pages 1–5. 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) 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
Background image of page 1

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

View Full Document Right Arrow Icon
2 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 X Y Z a b c a b ? 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 2
3 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 ) (Not a good design, and we will see why later)
Background image of page 3

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

View Full Document Right Arrow Icon
4 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 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}