05-design-theory

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

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

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

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

View Full Document