Functional DependenciesOutlineSlide 3ExamplesIn GeneralExampleMore ExamplesFinding the Keys of a RelationRules for Binary RelationshipsRules for Multiway RelationshipsFunctional DependenciesWednesday, October 4 2000Outline•Definition and keys (3.5)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 mMotivating example for the study of functional dependencies:Name Social Security Number Phone NumberExamples•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 … BX1 Y1X2 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 ssnPersonRules for Binary RelationshipsSeveral cases are possible for a binary relationship E1 - E2: 1. Many-many: the key includes the key of E1 together with the key of E2.What happens for: 2. Many-one: 3. One-one:PersonbuysProductnameprice name ssnRules for Multiway RelationshipsNone, really.Except: 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
View Full Document
Unlocking...