DOC PREVIEW
UW CSE 444 - Functional Dependencies and Relational Schema Design

This preview shows page 1-2-17-18-19-35-36 out of 36 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 36 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Functional Dependencies and Relational Schema DesignRelational Schema DesignFunctional DependenciesOutlineSlide 5ExamplesIn GeneralExampleMore ExamplesFinding the Keys of a RelationFinding the KeysSlide 12Slide 13Rules for FD’sRules in FD’s (continued)Slide 16Closure of a set of AttributesClosure AlgorithmSlide 19Why Is the Algorithm Correct ?Relational Schema Design (or Logical Design)Slide 22Slide 23Slide 24Relation DecompositionDecompositions in GeneralIncorrect DecompositionNormal FormsBoyce-Codd Normal FormSlide 30Decompose it into BCNFWhat About This?BCNF DecompositionExample DecompositionOther ExampleCorrect DecompositionsFunctional Dependencies and Relational Schema DesignRelational Schema DesignPersonbuysProductnameprice name ssnConceptual Model:Relational Model:(plus FD’s)Normalization:Functional Dependencies•A form of constraint (hence, part of the schema)•Finding them is part of the database design•Also used in normalizing the relationsOutline•Functional dependencies and keys (3.4,3.5)•Normal forms: BCNF (3.6)Functional DependenciesDefinition: If two tuples agree on the attributes A , A , … A 1 2 n then they must also agree on the attributesB , B , … B 1 2 mFormally: A , A , … A 1 2 nB , B , … B 1 2 mMain (and simplest) example: keysHow many different FDs are there?Examples•EmpID Name, Phone, Position•Position Phone•but Phone PositionEmpID Name Phone PositionE0045 Smith 1234 ClerkE1847 John 9876 SalesrepE1111 Smith 9876 SalesrepE9999 Mary 1234 lawyerIn General•To check A B, erase all other columns•check if the remaining relation is many-one (called functional in mathematics)… A … B X1 Y1 X2 Y2 … …ExampleEmpID Name Phone PositionE0045 Smith 1234 ClerkE1847 John 9876 SalesrepE1111 Smith 9876 SalesrepE9999 Mary 1234 lawyerMore ExamplesProduct: name price, manufacturerPerson: ssn name, ageCompany: name stock price, presidentKey of a relation is a set of attributes that: - functionally determines all the attributes of the relation - none of its subsets determines all the attributes.Superkey: a set of attributes that contains a key.Finding the Keys of a RelationGiven a relation constructed from an E/R diagram, what is its key?Rules: 1. If the relation comes from an entity set, the key of the relation is the set of attributes which is the key of the entity set.addressname ssnPersonPerson(address, name, ssn)Finding the KeysPersonbuysProductnameprice name ssnbuys(name, ssn, date)dateRules: 2. If the relation comes from a many-many relationship, the key of the relation is the set of all attribute keys in the relations corresponding to the entity setsFinding the KeysBut: if there is an arrow from the relationship to E, then we don’t need the key of E as part of the relation key.PurchaseProductPersonStorePayment Methodnamecard-nossnsnamePurchase(name , sname, ssn, card-no)Finding the KeysMore rules:•Many-one, one-many, one-one relationships•Multi-way relationships•Weak entity sets(Try to find them yourself, check book)Rules for FD’sA , A , … A 1 2 nB , B , … B 1 2 mA , A , … A 1 2 n1Is equivalent toBA , A , … A 1 2 n2BA , A , … A 1 2 nmB…Splitting rule and Combing ruleRules in FD’s (continued)A , A , … A 1 2 niATrivial RuleWhy ?Rules in FD’s (continued)A , A , … A 1 2 nTransitive Closure RuleB , B , … B1 2 mA , A , … A 1 2 n1B , B …, B2 m1C , C …, C2 p1C , C …, C2 pIfandthenWhy ?Closure of a set of AttributesGiven a set of attributes {A1, …, An} and a set of dependencies S.Problem: find all attributes B such that:any relation which satisfies S also satisfies:A1, …, An B The closure of {A1, …, An}, denoted {A1, …, An} ,is the set of all such attributes B+Closure AlgorithmStart with X={A1, …, An}.Repeat until X doesn’t change do: if is in S, and C is not in X then add C to X.B , B , … B 1 2 nCB , B , … B 1 2nare all in X, andExampleA B CA D E B DA F BClosure of {A,B}: X = {A, B, }Closure of {A, F}: X = {A, F, }Why Is the Algorithm Correct ?•Show the following by induction:–For every B in X:•A1, …, An B•Initially X = {A1, …, An} -- holds•Induction step: B1, …, Bm in X–Implies A1, …, An B1, …, Bm–We also have B1, …, Bm C–By transitivity we have A1, …, An C•This shows that the algorithm is sound; need to show it is completeRelational Schema Design(or Logical Design)Main idea:•Start with some relational schema•Find out its FD’s•Use them to design a better relational schemaRelational Schema DesignPersonbuysProductnameprice name ssnConceptual Model:Relational Model:(plus FD’s)Normalization:Relational Schema DesignGoal: eliminate anomalies•Redundancy anomalies•Deletion anomalies•Update anomaliesRelational Schema DesignName SSN Phone NumberFred 123-321-99 (201) 555-1234Fred 123-321-99 (206) 572-4312Joe 909-438-44 (908) 464-0028Joe 909-438-44 (212) 555-4000Anomalies:Redundancy = repeat dataupdate anomalies = need to update in many placesdeletion anomalies = need to delete many tuplesRecall set attributes (persons with several phones):Note: SSN no longer a key hereRelation DecompositionSSN Name123-321-99 Fred909-438-44 JoeSSN Phone Number123-321-99 (201) 555-1234123-321-99 (206) 572-4312909-438-44 (908) 464-0028909-438-44 (212) 555-4000Break the relation into two:Decompositions in GeneralA , A , … A 1 2 nLet R be a relation with attributes Create two relations R1 and R2 with attributes B , B , … B 1 2 mC , C , … C 1 2 lSuch that:B , B , … B 1 2 mC , C , … C 1 2 l A , A , … A 1 2 nAnd -- R1 is the projection of R on -- R2 is the projection of R on B , B , … B 1 2 mC , C , … C 1 2 lIncorrect DecompositionName CategoryGizmo GadgetOneClick CameraDoubleClick CameraPrice Category19.99 Gadget24.99 Camera29.99 CameraName Price CategoryGizmo 19.99 GadgetOneClick 24.99 CameraOneClick 29.99


View Full Document

UW CSE 444 - Functional Dependencies and Relational Schema Design

Documents in this Course
XML

XML

48 pages

SQL

SQL

25 pages

SQL

SQL

42 pages

Recovery

Recovery

30 pages

SQL

SQL

36 pages

Indexes

Indexes

35 pages

Security

Security

36 pages

Wrap-up

Wrap-up

6 pages

SQL

SQL

37 pages

More SQL

More SQL

48 pages

SQL

SQL

35 pages

XML

XML

46 pages

Triggers

Triggers

26 pages

Load more
Download Functional Dependencies and Relational Schema Design
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Functional Dependencies and Relational Schema Design and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Functional Dependencies and Relational Schema Design 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?