ER图2关系模型：九步转换算法

3.2.3 Mapping from ER Models to RelationalModels∙Mapping Algorithm∙ExampleThere is almost a one-to-one correspondence between the ER constructs and the relational ones. The two major distinctions are:1.In a relational schema, relationships are represented implicitly through primary andforeign keys of participating entities.2.In a relational schema, columns of relations cannot be multi-valued or composite.Composite attributes are replaced with their simple component ones, andmulti-valued attributes are stored in a separate relation.一.Mapping AlgorithmWe can translate an ER schema to a relational schema by following a nine-step algorithm based on the one given in Elmasri and Navathe 1994. The algorithm attempts to minimize the need for joins and NULL values when defining relations (Steps 2, 4, and 5).1.For each strong entity E:o Create a new table.o Include as its columns, all the simple attributes and simple components of the composite attributes of E.o Identify the primary key and the alternate keys. Do not include anymulti-valued attribute as a key. If the only unique field of an entity is amulti-valued attribute, introduce an artificial primary key field.2.For each weak entity W that is associated with only one 1:1 identifying ownerrelationship:o Identify the table T of the owner entity type.o Include as columns of T, all the simple attributes and simple components of the composite attributes of W.3.For each weak entity W that is associated with a 1:N or M:N identifying relationship,or participates in more than one relationship:o Create a new table T.o Include as its columns, all the simple attributes and simple components of the composite attributes of W.o Form the primary key of T as follows:▪In the case of a 1:N owner relationship, by including as a foreign key in T, the primary key of the owner entity. The primary key of T isthe combination of W's partial key and the foreign key.▪In the case of an M:N owner relationship, create a new column that will hold unique values. (In this case, the association between theweak entity and its owner entity will be specified in Step 6.)4.For each binary 1:1 relationship R:o Identify the tables S and T of the participating entity types.o Choose S (preferably the one with total participation).o Include as a foreign key in S, the primary key of T.o Include as Columns of S, all the simple attributes and simple components of the composite attributes of R.5.For each binary 1:N relationship R:o Identify the table S (at the N-side) and T of the participating entities.o Include as a foreign key in S, the primary key of T.o Include as columns of S, all the simple attributes and simple components of composite attributes of R.6.For each N-ary relationship (including binary N:M relationship) R:o Create a new table T.o Include as columns of T, all the simple attributes and simple components of composite attributes of R.o Include as a foreign keys, the primary keys of the participating (strong or weak) entity types.o Specify as the primary key of T, the list of foreign keys.7.For each multi-valued attribute A:o Create a new table T.o Include as columns of T, the simple attribute or simple components of the attribute A.o In table T, include as a foreign key, the primary key of the entity or relationship type that has A.o Specify as the primary key of T, the foreign key and the columnscorresponding to A.8.For each specialization with disjoint subclasses:o Create a new table T i for each subclass S i.o Include as columns of T i, the simple attributes and simple component attributes of the superclass.o Include as columns of T i, the simple attributes and simple component attributes specific to S i.o Identify the primary key.9.For each specialization with overlapping subclasses:o Create a new table O for the superclass.o Include as columns of O, the simple attributes and the simple component attributes of the superclass.o Identify its primary key and alternate keys.o Create a new table T i for each subclass S i.o Include as columns of T i, the simple attributes and simple component attributes specific to S i.o Include as a foreign key in T i(to be part of the primary key of T i), the primary key of O.二.ExampleLet us convert our library ER schema into a relational one using the above mapping algorithm. We tag each definition with the step Si (i = 1..9) in the algorithm that introduced it.∙In step1, we map all the entities types into tables and define their primary keys PKs and alternate keys AKs. Note that Author in TITLE is a multi-valued attribute andas such will be handled in Step 7. All the composite attributes, e.g., DriverLic inMEMBER, are broken down to their components that are included as separatecolumns.∙S1 TITLE(Name, ISBN, CallNumber, Year, Publisher);∙S1 PK(CallNumber)∙S1 AK(ISBN)∙∙S1 MEMBER(MemNo, DriverLicState, DriverLicNo, Fname, MI, Lname, Address, PhoneNumber);∙S1 PK(MemNo)∙S1 AK(DriverLicState,DriverLicNo)∙∙S1 BOOK(Book_Id, Edition);∙S1 PK(Book_Id)∙∙S1 LIBRARIAN(SSN, Name, Address, Salary, Gender, Birthday);∙S1 PK(SSN)∙∙S1 SECTION(SectNo, Name);∙S1 PK(SectNo)∙In step 2, we do not take any action, since there are no weak entities satisfying the 1:1 relationship constraint. DEPENDENT is a weak entity with 1:N relationship constraint, namely, DEPENDS, and will be handled in the next step. If we had to take any action, this would have been along the lines of step 4.∙In step 3, we create a table for each remaining weak entity that was not handled in step2. In our example, we create only one, namely DEPENDENT.∙S3 DEPENDENT(LIBSSN, Name, Birthday, Kinship);∙S3 PK(LIBSSN, Name)∙S3 FK(LIBSSN) --> LIBRARIAN(SSN)The primary key is formed by LIBSSN, the primary key of the owner entity LIBRARIAN, and Name, the partial key of DEPENDENT. LIBSSN is a foreign key (FK) in the DEPENDENT table.∙In Step 4, we consider the binary 1:1 relationships. In our example, MANAGES is such a relationship. Since all sections have a head librarian, we choose the SECTION table into which to capture (collapse) the relationship. By choosing SECTION, we avoid storing any NULL values, which would have been the case, had we decided to choose librarians. This is because not all librarians head a section and for any librarian who is not a head librarian, the corresponding column would be NULL.We collapse the MANAGES relationship by including the primary key of LIBRARIAN, i.e., the SSN of the head librarian, as a foreign key in SECTION, i.e., HeadSSN.S1 SECTION(SectNo, Name, HeadSSN);S1 PK(SectNo)S4 FK(HeadSSN) --> LIBRARIAN(SSN)∙In step 5, we consider 1:N relationships. Here we have five such relationships in our example, namely, BORROW, CHECKS, COPY, WORKS and SUPERVISES. In each case, we choose the table that corresponds to the N-side of the relationship and collapse the relationship in the selected table including all the attributes of the relationship as columns in the table.The reason for selecting the N-side table is as follows. Given that each row in the N-side table can be related to only one row of the 1-side table, we need to introduce only one column in the N-side table to capture the relationship. This will bea foreign key in the N-side table corresponding to the primarykey of the 1-side table. If we had selected the 1-side table, then we would have to specify as many foreign keys as the numberof related entities. In general, N is not bounded and hence we cannot predict how many foreign key columns to introduce. If we attempt to make a prediction we run the risk of either overestimating or underestimating. If we overestimeate, our table will contain a great number of NULL values which is a waste of space. If we underestimate, our table will not be able to store a relationship and hence our database will fail to meet fully its intended usage.In our example, we choose BOOK for BORROW, BOOK for CHECKS, and BOOK for COPY. Hence we introduce three foreign keys in BOOK, namely, BorrowerMemNo, LibCheck and CallNumber, respectively.S1 BOOK(Book_Id, Edition, BorrowerMemNo, BorrowDueDate, CallNumber,LibCheck);S1 PK(Book_Id)S5 FK(BorrowerMemNo) --> MEMBER(MemNo)S5 FK(CallNumber) --> TITLE(CallNumber)S5 FK(LibCheck) --> LIBRARIAN(SSN)For the WORKS and SUPERVISES relationships we choose LIBRARIAN and introduce the foreign keys Section and SuperSSN.S1 LIBRARIAN(SSN, Name, Address, Salary, Gender, Birthday, SuperSSN, Section);S1 PK(SSN)S5 FK(SUPERSSN) --> LIBRARIAN(SSN)S5 FK(Section) --> SECTION(SectNo)∙In step 6, we consider the M:N relationships. In our example, HOLD is such a relationship. We create a new table to store this relationship and its attribute, namely Date. As above, the relationship is expressed using foreign keys. For each participating table, in our example, MEMBER and TITLE, we include a foreign key referring to their primary keys, namely MemNo and CallNumber, respectively.∙S6 HOLD(MemNo, CallNumber, Date);∙S6 PK(MemNo, CallNumber)∙S6 FK(MemNo) --> MEMBER(MemNo)∙S6 FK(CallNumber) --> TITLE(CallNumber)The reason for creating a new table is that we cannot collapse an N:M relationship in any of the participating tables without guessing the required number of foreign keys as we discussed in step 5, above.∙In the last step for ER schemas, step 7, we consider all multi-value attributes. In our example, we have one multi-valued attribute, namely, Authors in TITLE. We storesuch an attribute as a separate table, e.g., AUTHOR, whose rows correspond to each of the values of the multi-valued attribute. In order to relate these values to the originating table, we include the primary key of the originating table, in our case, TITLE, as a foreign key in AUTHOR.∙S7 AUTHOR(CallNumber, Fname, MI, Lname);∙s7 PK(CallNumber, Fname, MI, Lname)∙s7 FK(CallNumber) --> TITLE(CallNumber)Let us conclude by listing the entire relational schema produced above. It is worth recalling here that PK, AK, and FK correspond to PRIMARY KEY, UNIQUE and FOREIGN KEY REFERENCES statements, respectively, in SQL.S1 TITLE(Name, ISBN, CallNumber, Year, Publisher);S1 PK(CallNumber)S1 AK(ISBN)S1 MEMBER(MemNo, DriverLicState, DriverLicNo, Fname, MI, Lname, Address, PhoneNumber);S1 PK(MemNo)S1 AK(DriverLicState, DriverLicNo)S1 BOOK(Book_Id, Edition, BorrowerMemNo, BorrowDueDate, CallNumber, LibCheck); S1 PK(Book_Id)S5 FK(BorrowerMemNo) --> MEMBER(MemNo)S5 FK(CallNumber) --> TITLE(CallNumber)S5 FK(LibCheck) --> LIBRARIAN(SSN)S1 LIBRARIAN(SSN, Name, Address, Salary, Gender, Birthday, SuperSSN, Section); S1 PK(SSN)S5 FK(SuperSSN) --> LIBRARIAN(SSN)S5 FK(Section) --> SECTION(SectNo)S1 SECTION(SectNo, Name, HeadSSN);S1 PK(SectNo)S4 FK(HeadSSN) --> LIBRARIAN(SSN)S3 DEPENDENT(LIBSSN, Name, Birthday, Kinship);S3 PK(LIBSSN, Name)S3 FK(LIBSSN) --> LIBRARIAN(SSN)S6 HOLD(MemNo, CallNumber, Date);S6 PK(MemNo, CallNumber)S6 FK(MemNo) --> MEMBER(MemNo)S6 FK(CallNumber) --> TITLE(CallNumber)S7 AUTHOR(CallNumber, Fname, MI, Lname);S7 PK(CallNumber, Fname, MI, Lname)S7 FK(CallNumber) --> TITLE(CallNumber)© Copyright 1999-2004, iCarnegie, Inc. All rights reserved.。