ER到关系变换

1 .Chapter 9 (6/5e ): ER to Relational Transformation Prof. Steven A. Demurjian, Sr. Computer Science & Engineering Department The University of Connecticut 191 Auditorium Road, Box U-155 Storrs, CT 06269-3155 steve@engr.uconn.edu http://www.engr.uconn.edu/~steve (860) 486 - 4818 The Majority of these slides are being used with the permission of Dr. Ling Lui, Associate Professor, College of Computing, Georgia Tech. Other slides (figures) have been adapted from the AWL web site for the textbook.

2 .Designing a Relational DB Schema Defining Relations Deciding which Attributes belong Together in Each Relation Choosing Appropriate Names for the Relations and Their Attributes Specifying the Domains and Data Types of the Various Attributes Identifying the Candidate Keys and Choosing a PK for Each Relation, and Specifying All Foreign Keys Two Techniques for Relational Schema Design Using ER-to-Relational Mapping (Chapter 9) Relational Normalization Theory (Chapter 14)

3 .Design Process - Where are we? Conceptual Design Conceptual Schema (ER Model) Logical Design Logical Schema (Relational Model) Step 1: ER-to-Relational Mapping Analysis of Schema Step 2: Normalization Normalized Schema

4 .ER-to-Relational Mapping Algorithm Step 1: For Each Regular Entity Type E Create a Relation R E Include only the Simple Attributes of a Composite Attribute Step 2: For Each Weak Entity Type W with Owner Entity Type E Create a Relation R W Include as Attributes All Simple Attributes of W Primary Key attribute(s) of the Relation that Corresponds to W’s Owner Entity Type E

5 .ER-to-Relational Mapping Algorithm Step 3: For Each 1:1 Relationship Identify the Relations R 1 and R 2 Include as Foreign Key of one Relation the Primary Key of the Other Relation Step 4: For each Regular 1:n Relationship Include as Foreign Key in the Entity Type at the n-side of the Relationship, the Primary Key of the Entity Type at the 1-side of the Relationship

6 .ER-to-Relational Mapping Algorithm Step 5: For Each Binary n:m Relationship Create a New Relation, whose Attributes Include All Simple Attributes of the n:m Relationship as Non-key Attributes PKs of the Relations that Represent the Participating Entity Types, as FK Attributes in this New Relation Step 6: For Each Multi-valued Attribute A Create a New Relationship R that Includes An Attribute Corresponding to A The PK Attribute of the Relation Whose Corresponding Entity Type or Relationship Has A as an Attribute

7 .ER-to-Relational Mapping Algorithm Step 7: For Each n-ary Relationship R, n>2 Create a New Relation to Represent R Step 8: Convert Each Specialization for Superclass C with Attributes {k, A 1 , …, A n } (k is the PK), where C has n Subclasses {S 1 , ..., S n } Create a Relation S i for each Subclass Entity (1<= i <= n) with Attributes Attrs(S i ) = {k}   {attributes of S i }, and PK{S i } = k Note that the Relation for C was created in an Earlier Step Note also that there are Three Other Options for Mapping Specialization Hierarchies

8 .Recall our Ongoing Example EMPLOYEE PROJECT Responsibility Duration Budget Project Name Project No Employee No Employee Name Salary Title WORKS ON Address City Apt. # Street # Location 1 N MANAGES BALANCE Expenses 1 RECORDS ACCOUNT Income 1 Date Amount SUPPLY SUPPLIER Supplier No Supplier Name Location Credit N M PART L Part No Part Name Weight Color CONTAIN M N Made-up of Consists of 1 1 QTY SALESPERSON Car Region SECRETARY Specialty Office ENGINEER Project Office  Acount # d o MANUFACTURED_PART PURCHASED_PART Batch No Drawing No Price  

9 .Can we Transition the Previous ER to ... Note that there are no subclasses here!

10 .Step 1 - Handling Entities For Each Regular Entity Type E in the E-R Schema, Create a Relation R Include as Attributes in R Only the Simple Attributes of E For Composite Attributes of E, Include Only their Constituent Simple Attributes in R The Key of E Becomes the PK of R If There is More Than One Key Attributes of E, Then Choose One as the Primary Key of R For Multi-Valued Attributes – Create a new Table in Step 6

11 .From the Following Strong Entity Types: Create Four Corresponding Relations with Attribute Keys as PKs Step 1 – Example SUPPLIER Supplier No Supplier Name Location Credit PROJECT Budget Project Name Project No Location PART Part No Part Name Weight Color EMPLOYEE Employee No Employee Name Salary Title Address City Apt. # Street #

12 . Step 1 – Generate a Table EMPLOYEE Employee No Employee Name Salary Title Address City Apt. # Street # The Keys are Underlined Note that Address becomes Three Attributes EMPLOYEE( ENO , ENAME, TITLE, SALARY, APT#, STREET, CITY)

13 . Step 1 – Generate a Table PROJECT Budget Project Name Project No Location The Keys are Underlined PROJECT( PJNO , PNAME, BUDGET) Note that LOCATION is Not Included in PROJECT – handled later in Step 6

14 . Step 1 – Generate a Table PART Part No Part Name Weight Color The Keys are Underlined PART( PNO , PNAME, WGT, COLOR)

15 . Step 1 – Generate a Table SUPPLIER Supplier No Supplier Name Location Credit The Keys are Underlined SUPPLIER( SNO , SNAME, CREDIT, LOCATION)

16 . The Keys are Underlined EMPLOYEE( ENO , ENAME, TITLE, SALARY, APT#, STREET, CITY) PROJECT( PJNO , PNAME, BUDGET) SUPPLIER( SNO , SNAME, CREDIT, LOCATION) PART( PNO , PNAME, WGT, COLOR) Notes: LOCATION is Not Included in PROJECT – handled later in Step 6 ACCOUNT is Weak and ENGINEER, SECRETARY, and SALESPERSON are Subclasses Step 1 – Summary

17 . Step 2 – Weak Entities For Each Weak Entity Type W Associated with the Strong Entity Type E in the E-R Schema, Create a Relation R Attributes of R are the Simple Attributes of W (or the Simplified Versions of Composite Attributes) Include Among the Attributes of R all of the Key Attributes of Strong Entity E These are the Foreign Keys of R The PK of R is the Combination of the PK of E and the Partial Key of W

18 .From: Create relation ACCOUNT as follows ACCOUNT( PJNO , ACNO , INCOME, EXPENSES) Note that ACNO Corresponds to Account# on ACCOUNT PJNO Corresponds to ProjectNo on PROJECT We’ll Handle Records Relationship in Separate Step foreign key Step 2 – Example Expenses ACCOUNT Income Acount #

19 .If there was a Dependent … Create a Dependent Table: DEPENDENT( ENO , NAME , SEX, BIRTHDATE, RELATIOSHIP) Primary Key from EMPLOYEE (ENO) PLUST DEPENDENT (NAME)

20 . Step 3 – 1:1 Relationships For Each 1:1 Relationship R in E-R Schema where the Two Related Entities are E1 and E2 Let Relations S and T Correspond to E1 and E2 Respectively Choose One of the Relations, Preferably One Whose Participation in R is Total (Say S) Include in S as a FK, the PK of T If there are Attributes Associated With the Relationship R, Include Them in S You May Want to Rename the Attributes When You Do This

21 . For 1:1 Relationship MANAGES between the EMPLOYEE and PROJECT Entities Choose PROJECT as S, Since its Participation in the MANAGES relationship is Total Include in PROJECT the PK of EMPLOYEE PROJECT( PJNO , PNAME, BUDGET) Becomes PROJECT( PJNO , PNAME, BUDGET, MGR ) Step 3 – Example MANAGES 1 1 PROJECT Budget Project Name Project No Location EMPLOYEE Employee No Employee Name Salary Title Address City Apt. # Street #

22 . Include in EMPLOYEE … EMPLOYEE( ENO , ENAME, TITLE, SALARY, APT#, STREET, CITY) Becomes EMPLOYEE( ENO , ENAME, TITLE, SALARY, APT#, STREET, CITY, MGR ) What’s the Problem with this? What’s Another Way of Doing this? MANAGES 1 1 PROJECT Budget Project Name Project No Location EMPLOYEE Employee No Employee Name Salary Title Address City Apt. # Street #

23 . For 1:1 Relationship RECORDS between PROJECT and ACCOUNT Entities: Choose ACCOUNT as S (ACCOUNT is a Weak Entity, so this is the only choice that makes sense) Include PJNO (done in step 2) and BALANCE ACCOUNT( PJNO , ACNO , INCOME, EXPENSES) Becomes ACCOUNT( PJNO , ACNO , INCOME, EXPENSES, BALANCE ) Step 3 – Example RECORDS 1 1 PROJECT Budget Project Name Project No Location BALANCE Expenses ACCOUNT Income Acount #

24 . For Each Regular (Non-weak) Binary 1:N Relationship Type R in the E-R Schema Identify the Relation S that Corresponds to the Entity Type at the N-side of the Relationship Let the Other Relation on the 1-side be T Include in S as a Foreign Key, the Primary Key of T If There are Attributes Associated with the Relationship R, Include them in S as well Step 4 – 1:N Relationships

25 . Step 4 – Example We have only the WORKS ON relationship Defined between PROJECT and EMPLOYEE N side of the Relationship is EMPLOYEE 1 side of the Relationship is PROJECT WORKS_ON 1 N PROJECT Budget Project Name Project No Location EMPLOYEE Employee No Employee Name Salary Title Address City Apt. # Street # Responsibility Duration

26 . Step 4 – Example Include in EMPLOYEE Primary Key (PJNO) of PROJECT Attributes of the WORKS ON relationship (Duration & Responsibility ) This is Since EMPLOYEE WORKS ON Only 1 PROJECT EMPLOYEE( ENO , ENAME, TITLE, SALARY, APT#, STREET, CITY) Becomes EMPLOYEE( ENO , ENAME, TITLE, SALARY, APT#, STREET, CITY, PJNO, DURATION, RESP )

27 . What Happens if we Choose EMPLOYEE? EMPLOYEE( ENO , ENAME, TITLE, SALARY, APT#, STREET, CITY, MGR ) Is this Possible? Suppose Steve Manages P1/P2 What Happens in Table? What if Employee Manage Multiple Projects? MANAGES N 1 PROJECT Budget Project Name Project No Location EMPLOYEE Employee No Employee Name Salary Title Address City Apt. # Street # EMPLOYEE( E1 , Steve, Prof, 60000, 2, Smith St, Storrs, P1 ) EMPLOYEE( E1 , Steve, Prof, 60000, 2, Smith St , Storrs , P2 )

28 . Step 5 – M:N Relationships For each Binary M:N Relationship Type R Connecting E1 and E2 in the ER schema, create a relation S: Include as FKs of S, the PKs of the two relations that correspond to E1 and E2 These Attributes, Together, Form the primary key of S Also Include in S any Attributes of the Relationship R

29 . Step 5 – M:N Relationships We have one M:N Relationship: CONTAIN, which is a Recursive Relationship over the PART Entity We create the following relation: CONTAIN( PNO , CPNO , QTY) Recursive Relationship Requires us to Distinguish the Part No as PNO and CPNO PART Part No Part Name Weight Color CONTAIN M N Made-up of Consists of QTY