CS122aSlides17

CS122aSlides17 - CS122A / EECS116 Introduction to Data...

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

View Full Document Right Arrow Icon
CS122A / EECS116 Introduction to Data Management Spring 2009 Prof. Mike Carey Bren School of ICS UC Irvine Slides based on previous CS122a lecture notes as well as  material borrowed from U-Wisconsin, Stanford, & Berkeley
Background image of page 1

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

View Full DocumentRight Arrow Icon
CS122A/EECS116 Notes 16 2 Announcements Homework and project notes HW #7 will come out tomorrow sometime It will be due one week from Friday night Buying time to include some of everything Still working kinks out of a couple of the problems Remember that we are dropping one assignment But, also remember that HW’s are a chance to practice Today: Relational database design theory ( cont. ) Last time we covered various normal forms Today we’ll talk more about theory (and automation) Warning: not the quarter’s most exciting lecture ( ) Any lingering questions…?
Background image of page 2
3 The Database Design Process Let’s take a look at the process… Conceptual Modeling – usually based on ER diagrams ER schema is then transformed to a relational schema Resulting relational schema is normalized to generate a “good” schema ( schema normalization process ) Designer may add additional integrity constraints to reflect real-world constraints – CHECK, UNIQUE, FOREIGN KEY , … Schema may be tested, using example data, to evaluate its quality and correctness and usefulness Results are analyzed and corrections to schema are made Corrections may be translated back up to the conceptual model to keep the conceptual description of data consistent CS122A/EECS116 Notes 16
Background image of page 3

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

View Full DocumentRight Arrow Icon
4 Schema Normalization Decompose relational schemas to achieve the following goals Removing redundancy Avoiding anomalies Result of normalization is a semantically equivalent schema Able to represent the same information as the original Able to reconstruct the original schema(s) (lossless join property) Advantages: More efficient updates, greater space efficiency Disadvantages: Will now require several relations to be joined E.g. list names of sailors who’ve reserved a red Windsurfer (May tolerate some redundancy for performance/convenience) Normal forms: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, … CS122A/EECS116 Notes 16
Background image of page 4
5 Reasoning about FD’s R(A,B,C,D) Functional dependencies: A B (fd1) B C (fd2) Then we can show the following FD must hold: A C (fd3) Proof: Suppose fd3 does not hold So, there are tuples r1, r2 in R: r1[A] = r2[A], but r1[C] <> r2[C] Because of fd1 and r1[A] = r2[A]: r1[B] = r2[B] Because of fd2 and r1[B] = r2[B]: r1[C] = r2[C] Contradiction! So fd3 must hold! Transitivity rule of Armstrong’s Axioms (see next slide) CS122A/EECS116 Notes 16
Background image of page 5

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

View Full DocumentRight Arrow Icon
6 Armstrong’s Axioms (Let X and Y denote attributes or sets of attributes.) Transitivity: If X Y, and Y Z, then X Z. Ex:
Background image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 06/18/2009 for the course CS 122a taught by Professor Carey during the Spring '09 term at UC Irvine.

Page1 / 22

CS122aSlides17 - CS122A / EECS116 Introduction to Data...

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

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