L08 - Conceptual Database Design Normalization (Schema...

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

View Full Document Right Arrow Icon
Conceptual Database Design Normalization (Schema Refinement)
Background image of page 1

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

View Full DocumentRight Arrow Icon
CS174A: Database Design 2 Functional Dependencies (FDs) ± X and Y are sets of attributes in relation R ± A functional dependency X Y holds over relation R if for each allowable instance r of R , for every pair of tuples t 1 , t 2 in r , π X ( t 1 ) = π X ( t 2 ) implies π Y ( t 1 ) = π Y ( t 2 ) ± X Y is an assertion about a relation R that if two tuples of R agree on all the attributes of X , then they must also agree on all the attributes of Y Say “ X Y holds in R
Background image of page 2
CS174A: Database Design 3 yes W H ? Yes LR R ? 30 10 8 22 Smiley 231-31-5368 10 7 7 10 W 40 8 48 Attishoo 123-22-3666 40 8 35 Madayan 612-67-4134 32 5 35 Guldu 434-26-3751 30 5 35 Smethurst 131-24-3650 H R L N S no N H ? Example ± Given two tuples in r , if their X values agree, then their Y values must also agree R W ? Yes trivial FD
Background image of page 3

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

View Full DocumentRight Arrow Icon
CS174A: Database Design 4 Example Drinkers ( name , addr , beersLiked , manf , favBeer ) ± Reasonable FD’s to assert: name addr name favBeer beersLiked manf WickedAle Pete’s WickedAle Voyager Janeway Bud WickedAle favBeer A.B. Bud Voyager Janeway A.B. Bud Enterprise Spock manf beersLiked addr name
Background image of page 4
CS174A: Database Design 5 Where Do Keys Come From? ± We could simply assert a key K . Then the only FD’s are K A for all atributes A , and K turns out to be the only key obtainable from the FD’s ± We could assert FD’s and deduce the keys by systematic exploration E/R gives us FD’s from entity-set keys and many-one relationships ± While most FD’s come from E/R keyness and many-one relationships, some are really physical laws ± Example: “no two courses can meet in the same room at the same time” tells us: hour room course
Background image of page 5

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

View Full DocumentRight Arrow Icon
CS174A: Database Design 6 Functional Dependencies and Keys ± An FD is a statement about all allowable relations Must be identified based on semantics of application Given some allowable instance of R , we can check if it violates some FD f , but we cannot tell if f holds over R ! ± K is a superkey for R means that K R However, K R does not require K to be minimal!
Background image of page 6
CS174A: Database Design 7 Keys of Relations ± K is a key for relation R if: 1) Set K functionally determines all attributes of R 2) For no proper subset of K is (1) true ± If K satisfies (1), but perhaps not (2), then K is a superkey ± Note E/R keys have no requirement for minimality, as in (2) for relational keys
Background image of page 7

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

View Full DocumentRight Arrow Icon
CS174A: Database Design 8 Example: FDs and Superkey ± Consider relation Drinkers ( name , addr , beersLiked , manf , favBeer ) ± { name , beersLiked } is a superkey because together these attributes determine all the other attributes name addr favBeer beersLiked manf
Background image of page 8
CS174A: Database Design 9 Example: Key ± { name , beersLiked } is a key because neither { name } nor { beersLiked } is a superkey name doesn’t manf ; beersLiked doesn’t addr ± In this example, there are no other keys, but lots of superkeys Any superset of { name , beersLiked }
Background image of page 9

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

View Full DocumentRight Arrow Icon
Image of page 10
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 05/02/2010 for the course CS 174a taught by Professor Zhin during the Fall '09 term at UCSB.

Page1 / 67

L08 - Conceptual Database Design Normalization (Schema...

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

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