CSC 742 Database Management Systems Topic 6 Database Design Spring 2002 CSC 742 DBMS by Dr Peng Ning 1 Mapping ER Diagrams to Relations Regular Entity Type Create a relation R Include simple attributes and simple components of composite attributes Choose one of the key attributes as the primary key Multi valued attributes Don t include in R To be discussed later Spring 2002 CSC 742 DBMS by Dr Peng Ning 2 1 Exercise MI Lname Fname Name Salary Bdate SSN Employee Sex Spring 2002 CSC 742 DBMS by Dr Peng Ning 3 Mapping ER Diagrams to Relations Cont d Weak Entity Type E Create a relation R Include all simple attributes and simple components of composite attributes Include the primary key of the relation corresponding to the owner entity type of E Spring 2002 CSC 742 DBMS by Dr Peng Ning 4 2 Exercise Bdate SSN Employee Dependents of Dependent Name Spring 2002 BDate Sex CSC 742 DBMS by Dr Peng Ning 5 Mapping ER Diagrams to Relations Cont d Binary 1 1 relationship R Suppose S and T are the relations corresponding to the entity types participating R Choose either S or T and include the primary of T or S as foreign key Include the simple attributes and the simple components of composite attributes of R Better to choose the one with total participation in R Spring 2002 CSC 742 DBMS by Dr Peng Ning 6 3 Exercise Name SSN Employee Location Bdate 1 Manages 1 Department Since Spring 2002 CSC 742 DBMS by Dr Peng Ning 7 Mapping ER Diagrams to Relations Cont d Binary 1 N relationship R Suppose S and T are the relations corresponding to the entity types participating R and S is the N side Choose either S and include the primary key of T as foreign key in S Include simple attributes and simple components of composite attributes in S Spring 2002 CSC 742 DBMS by Dr Peng Ning 8 4 Exercise Name SSN Employee Location Bdate Works for 1 Department Since Spring 2002 CSC 742 DBMS by Dr Peng Ning 9 Mapping ER Diagrams to Relations Cont d Binary M N relationship R Suppose S and T are the relations corresponding to the entity types participating R Create a relation U Include in U the primary keys of both S and T as foreign keys They form the primary key of U Include the simple attributes and the simple components of composite attributes of R Spring 2002 CSC 742 DBMS by Dr Peng Ning 10 5 Exercise Bdate SSN Employee Hours Works on Project Name Spring 2002 Location CSC 742 DBMS by Dr Peng Ning 11 Mapping ER Diagrams to Relations Cont d Multi valued attribute A Suppose A is an attribute of the entity type corresponding relation S Create a relation R Include an attribute corresponding to A and the primary key K of S as a foreign key The primary key of R is the combination of A and K Spring 2002 CSC 742 DBMS by Dr Peng Ning 12 6 Exercise Name Location Department Spring 2002 CSC 742 DBMS by Dr Peng Ning 13 Mapping ER Diagrams to Relations Cont d The n ary relationship R Create a new relation S Include the primary keys of all the relations corresponding to the participating entity types in R They form the primary key of S Include the simple attributes and the simple components of composite attributes of R Spring 2002 CSC 742 DBMS by Dr Peng Ning 14 7 Exercise PartNo SName PartNo Supplier Supply Part Project ProjName Spring 2002 CSC 742 DBMS by Dr Peng Ning 15 Mapping EER Diagrams to Relations Subclass superclass relationships Commonly four options Assume there are a super class C and m subclasses S1 S2 Sm Assume the attributes of C are k a1 an and k is the key attribute Spring 2002 CSC 742 DBMS by Dr Peng Ning 16 8 Mapping EER Diagrams to Relations Cont d Option 1 Create a relation L for C with all its attributes and have k as the primary key For each subclass Si create a relation Li with attributes k and all the attributes of Si The primary key of Si is k Intuition keep the attributes of the individual classes separately Spring 2002 CSC 742 DBMS by Dr Peng Ning 17 Mapping EER Diagrams to Relations Cont d Option 2 For each subclass Si create a relation Li with all the attributes of C and all the attributes of Si The primary key of Si is k Intuition replicate the attributes of the super class in subclasses Spring 2002 CSC 742 DBMS by Dr Peng Ning 18 9 Mapping EER Diagrams to Relations Cont d Option 3 Create a single relation with the attributes of the super class and all the subclasses plus a type attribute The type attribute is used to indicate the subclass to which each tuple belong Intuition store all classes together For disjoint specialization Spring 2002 CSC 742 DBMS by Dr Peng Ning 19 Mapping EER Diagrams to Relations Cont d Option 4 Create a single relation with the attributes of the super class and all the subclasses plus m type attributes The type attributes boolean attributes indicating whether the tuple belongs to the corresponding subclasses Intuition Store all classes together For overlapping specialization Spring 2002 CSC 742 DBMS by Dr Peng Ning 20 10 Exercise MI Lname Fname Name Salary Bdate SSN Employee Sex d Secretary TypingSpeed Spring 2002 Technican Engineer TGrade EngType CSC 742 DBMS by Dr Peng Ning 21 Mapping EER Diagrams to Relations Cont d Multiple inheritance all superclasses have the same key Spring 2002 CSC 742 DBMS by Dr Peng Ning 22 11 Design Guidelines Have schemas that are easy to explain Keep different entities and relationships apart where possible at least in base relations Prevent anomalies in insertion deletion modification Spring 2002 CSC 742 DBMS by Dr Peng Ning 23 Employee Name SSN Smith 111 22 3333 01 11 71 1 Tom 222 33 4444 02 14 68 1 Bdate DNumber Department DName DNumber MgrSSN Research 1 111 22 3333 EMP DEPT Name SSN Bdate DNumber DName MgrSSN Smith 111 22 3333 01 11 71 1 Research 111 22 3333 Tom 222 33 4444 02 14 68 1 Research 111 22 3333 Spring 2002 CSC 742 DBMS by Dr Peng Ning 24 12 Design Guidelines Cont d Avoid NULL values in base relations although they may occur in views NULLs should apply rarely and have well defined meaning Not applicable unknown absent known but absent Prevent spurious tuples Spring 2002 Registration Reg1 Loc Spring 2002 CSC 742 DBMS by Dr Peng Ning 25 StudentID Name Course Location 1 Smith CSC101 V 150 1 Smith CSC102 V 100 2 John CSC101 V 150 StudentID Name Course 1 Smith CSC101 1 Smith CSC102 2 John CSC101 StudentID Location 1 V 150 1 V 100 2 V 150 CSC 742 DBMS by Dr Peng Ning 26 13 Functional Dependencies A constraint R is treated as a set of attributes below For subsets X and Y of R X Y means that For all relations r …