6 - 1 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] CIS 8040 - Database Design and Mapping Database Design Outline A Database Design Methodology Mapping of E-R Schemas into Relations6 - 2 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] A DB Design Methodology Requirements Collection & Analysis Conceptual Design Logical Design Physical Design Application Design Identify & describe data and user views Conceptual modeling View integration Operation description Map to specific DB schema Refine the DB schema Refine the operation Tuning up performance User interfaces Application programs Mapping the Conceptual Model to the Logical Model Simple set of rules6 - 3 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Regular Entity Types  Map each regular entity type E to a relation R  Include in R  All simple attribute of E  The simple component (leaf) attributes of all composite attributes  Use the PK of E as the PK of R I nstructor ( Emp#, Fname, Minit, LN ame ) I nstructorN ameFN ame MI nit LN ame LanguageEmp#Note: Relational schema is not yet finished; i.e., no closing parentheses Multivalued Attributes  Create a new relation M for each multivalued attribute of E  Include in M the multiple attribute  Include the PK of R as the FK in M  The PK of M consists of all attributes of M Instructor Language Instructor (Emp#, FName, MInit, LName) Language (LangName, EMP#) Note: PK contains both attributes6 - 4 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Weak Entity Types  Create a relation Rw for each weak entity type Ew  Include in Rw  All attributes of Ew  All attributes of the identifying relationship  Include the PK of the owner entity type as the FK in Rw  Combine the FK and the partial key of Ew if any as the PK of Rw Student ( SSN , DOB, Name, Age, ... ) Student Registers Car Date 1 M Color PNum P_Lot Car ( PNum, Color, P_Lot, Date, SSN, DOB) SSN DOB Relationships… CASE I: Connectivity - 1:1 Relationship  Create a relation Rx for each relationship, if relationship has attributes  Include the PKs of participating entity types as PK in R  Create a relation Rn for each Entity  The FK may be placed in either Entity of the relationship, choose the one that makes the most sense NOTE: To save space, attributes have been omitted from ERD Car Registration Requires CAR (AutoID, Make, Model, Yr, Color) REGISTRATION (TagNo, InsID, ExpDate, AutoID)6 - 5 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Relationships... CASE II: Connectivity - 1:M Relationship In a one-to-many relationship  Choose the many side relation (one of relations), say R1,  Include the PK of the one (other) side relation as the FK in R1  If relationship contains attributes, include in R1 SalesRep Customer Services M 1 SalesRep(Emp#, FName, Mint, LName , Phone, CommRate) Customer(CustID, CompanyName, Address, Emp#) Relationships... CASE III: Connectivity - M:N Relationship In a many-to-many relationship  Create Relations for each Entity include PKs  Create a Relationship Relation, R1  The PK of R1 consists of the PKs of each Entity relation  Any attributes of the Relationship Relation remain with it Pilot Aircraft flies M N PILOT(PID, PName, Address, DOB, YrsWCo) FLIES (PID, A/C#, Hrs_Flown) AIRCRAFT (A/C#, SeatCapacity) Hrs_Flown6 - 6 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] N-ary Relationships Same as for M:N relationships offer Instructor Course Student M N L Instructor (Emp#, FName, ...) Course (C#, Title, Credits, ...) Student (ID, FName, . . .) Offer (Emp#, C#, ID, Sect#, Time, Location) Sect# Time Location IS-A Relationships Individual relation approach:  Map each entity type to a relation  For each subtype relation Rsub:  Include the PK of its supertype relation as the FK in Rsub  Combine the FK and the PK of Rsub if any as the new PK of Rsub Person Instructor Student Person (SSN, DOB, Name, Age, Address, ...) Instructor (SSN, DOB(fk), Salary, Office, ...) Student (SSN, DOB(fk), Year, GPA, ...)6 - 7 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Other Issues Names conflicts can be resolved by renaming the names or by using roles An unary relationship can be mapped as a binary relationship Application programs must enforce connectivity and participation contstraints Supervise Employee M 1 Employee Employee Supervise 1 M Employee(Emp#, Fname, Lname, …, SuperEmp#)