Step 1: Mapping of Regular Entity Types

For each regular entity type E, create a relation R that includes all the simple attributes of E

Take simple component attributes of Composite Attributes

Called entity relations

Each tuple represents an entity instance

Choose one of the candidate keys as a PK

Step 2: Mapping of Weak Entity Types

For each weak entity type W, create a relation R

Include all simple attributes of the entity type as attributes of R

Include simple components of composite attributes.

Include primary key attribute of owner as foreign key attributes of R. With weak entities types we can choose cascade for a FK action.

Step 3: Mapping of Binary 1:1 Relationship Types

For each binary 1:1 relationship type S and T

Identify relations that correspond to entity types participating in R

Possible approaches:

Foreign key approach ( First Choice )

Merged relationship approach

Cross-reference or relationship relation approach

Step 3: Mapping of Binary 1:1 Relationship Types S and T Entities

Foreign Key Approach

Choose either S or T to include a FK to the primary key of the other.

Typically you should choose the relation that is in total participation for the relationship.

Any Relationship type attributes should be moved to the relation with the foreign key.

Step 3: Mapping of Binary 1:1 Relationship Types S and T Entities

Merged Relation Approach

Merge S and T into one relation.

Should only be done if both participants are in a total participation relationship with the other. ( This would mean they would both always have the same number of rows )

Step 3: Mapping of Binary 1:1 Relationship Types S and T Entities

Cross Reference or relationship relation approach

Create new relation R as cross reference or lookup table relation

A FK to the PK of S, and a FK to the PK of R will be added to R.

Any relationship attributes will be added to R.

One of the FK will be Rs primary key, and the other will be a unique index.

Step 4: Mapping of Binary 1:N Relationship Types

There are 2 possible approaches

Foreign Key approach

Cross-Reference or relationship relation approach

Step 4: Mapping of Binary 1:N Relationship Types Foreign Key Approach

For each regular binary 1:N relationship type

Identify relation that represents participating entity type at N-side of relationship type, well refer to as S

Include primary key of other entity type as foreign key in S

Include simple attributes of 1:N relationship type as attributes of S

Use the relationship relation (cross-reference) option as in the third option for binary 1:1 relationships

Create relation R

Create Attributes for R that are the PKs of the relations S and T. Make them foreign keys.

Make the N side relation S, the Primary Key in R.

Useful, if not many of the S-side relation participate in the relationship.

Step 5: Mapping of Binary M:N Relationship Types

For each binary M:N relationship type

Create a new relation S

Include primary key of participating entity types as foreign key attributes in S

Include any simple attributes of M:N relationship type

Cascade can probably be used on both foreign keys

Step 6: Mapping of Multivalued Attributes

For each multivalued attribute

Create a new relation, R

Primary key of R is the combination of A, ( the multivalued attribute) and K

If the multivalued attribute is composite, include its simple components

FK can be a cascade operation.

Step 7: Mapping of N-ary Relationship Types

For each n-ary relationship type R

Create a new relation S to represent R

Include primary keys of participating entity types as foreign keys

Include any simple attributes as attributes

FKs can be Cascade delete

7. Use the algorithm we used in Chapter 9 to convert the following ER diagram into relational DB design. Work through the steps of the algorithm to create a solution Author Chapters Genre Book N Written By 1- Author Name Date Reviewed By First Last Rating Reviewer Name