Mapping an ERD to a Relational Database

Mapping an ERD to a Relational Database • To map an ERD to a relational database, 4 rules are defined to govern how tables are constructed. • Rule for entity types • Rule for relationships • Rule for attributes • 4) Rule for participation constraint

Entities • Each entity set is implemented with a separate relation. • Strong Entities • Strong, or regular, entities are mapped to their own relation. • The PK (Primary Key) is chosen from the set of keys available.

Example: Strong Entities d A c e c d e A

Entities • Each entity set is implemented with a separate relation. • Weak Entities • Weak entities are mapped to their own relation • The PK of the weak entity is the combination of the PKs of entities related through identifying relationships and the discriminator (partial key) of the weak entity.

Example: Weak Entities d c e A B c x y B y x

Relationships • We’ll consider binary relationships only • All relationships involve the use of foreign keys: the PK • attribute of one entity set is added as an attribute to the • relation representing the other entity set • a. Binary One-To-One • In general, with a one-to-one relationship, you have a • choice regarding where to implement the relationship. • You may choose to place a foreign key in one of the two • relations, or in both.

Example: 1-1 d c e c is a FK in B A 1 B x y c 1 B y x

Example: 1-1 d c e X is a FK in A A 1 A c … X 1 B y x

If the participation constraint on an entity set is mandatory, we must choose the table for that entity set. d c e X is a FK in B A 1 A c … X 1 B y x

Relationships • Binary One-To-Many • With a one-to-many relationship you must place a foreign key in the relation corresponding to the many side of the relationship.

Example: 1-n d c e c is a FK placed on the “many” side of the relationship A 1 B n x y c B y x

Relationships • Binary Many-To-Many • A many-to-many relationship must be implemented with a separate relation for the relationship. This new relation will have a composite primary key comprising the primary keys of the participating entity sets plus any discriminator attribute.

Example: m-n x and c are FKs, and together they form the PK of AB d c e A AB m x c s s n B y x

Attributes • All attributes, with the exception of derived and composite attributes, must appear in relations. • Simple, atomic • These are included in the relation created for the pertinent entity set, many-to-many relationship, or n-ary relationship.

Example: Atomic Attributes d A c e c d e A

Attributes • Multi-valued • Each multi-valued attribute is implemented using a new relation. This relation will include the primary key of the original entity set. The primary key of the new relation will be the primary key of the original entity set and the multi-valued attribute. Note that in this new relation, the attribute is no longer multi-valued.

Example: Multi-valued Attributes d A AE c e c d c e A e is a multi-valued attribute.

Attributes • Composite • Composite attributes are not included. However the atomic attributes comprising the composite attribute must appear in the pertinent relation.

Example: Composite Attributes d A e c n c d e A

c x d y A B • 4. Participation constraints • If a relationship is mandatory for an entity set, then if the entity set is on the “many” side of the relationship, then a specification is required to ensure a foreign key has a value, and that it cannot be null. N 1

A B c d x y c The “required” property for attribute c is set “yes”.

c x d y A B • 5. Participation constraints • Otherwise, if the entity set is on the “one” side of a relationship, then a check constraint or database trigger can be specified to ensure compliance. N 1

A B c d x y c A program should be produced to check that any value appearing in c-column in table A must appear at least once in c-column in table B.

Summary • Entities • a. Each entity set is implemented with a separate relation. • Weak Entities • Weak entities are mapped to their own relation • The PK of the weak entity is the combination of the PKs of entities related through identifying relationships and the discriminator (partial key) of the weak entity;

2. All relationships involve foreign keys. If the relationship • is identifying then the primary key of the strong entity • must be propagated to the relation representing the weak • entity. • Binary One-To-One • In general, with a one-to-one relationship, you have • a choice regarding where to implement the • relationship. You may choose to place a foreign key • in one of the two relations, or in both. • b. Binary One-To-Many • With a one-to-many relationship you must place the • foreign key in the relation corresponding to the many • side of the relationship.

c. Binary Many-To-Many A many-to-many relationship must be implemented with a separate relation for the relationship. This new relation will have a composite primary key comprising the primary keys of the participating entity sets plus any discriminator attribute.

3. Attributes • a. Simple, atomic • These are included in the relation created for the • pertinent entity set, many-to-many relationship, or n-ary • relationship. • b. Multi-Valued • A multi-valued attribute is implemented using a new • relation. This relation will include the primary key of the • entity set. The primary key of the new relation will be • the primary key of the entity set and the multi-valued • attribute. Note that in this relation, the attribute is no • longer multi-valued. • c. Composite • Composite attributes must be replaced by their equivalent • atomic attributes in the pertinent relation.

4.Participation constraints. If a relationship is mandatory for an entity set, then if the entity set is on the “many” side of the relationship, a specification is required. If the entity set is on the “one” side of a relationship, then that “one” side will be chosen as the primary table and the other is chosen as the referenced table. A NOT NULL constraint for the foreign key should be specified.

Mapping an ERD to a Relational Database