Database Design Using Entity-Relationship Models

Transformation of Entity-Relationship Models into Relational Database DesignTrees, Networks, and Bills of Materials

Transformation of ERM into Relational Database Designs

Representing Entities (incl. Weak Entities)Representing HAS-A RelationshipsRepresenting Recursive RelationshipsRepresenting IS-A Relationships (Subtypes)

Representing Entities With Relations

• At first you construct a relation to include all of the entity’s attributes.

• Then you normalize this relation (you may develop sets of relations in DK/NF). Fig. 6-1,2,3

• For ID-dependent weak entities the key should be the key of the parent and the key of the entity itself. Fig. 6-4

Representing Binary HAS-A Relationships

One-to-One RelationshipsEach entity is represented by a relationThe key of one of the relations is stored as a foreign key in the other. Fig. 6-5,6

One-to-Many RelationshipsOne-to-many are parent-to-child(ren)Each entity is represented by a relationThe key of the parent relation is stored as a foreign key in the child relation. Fig. 6-8,9,10

Many-to-Many RelationshipsEach entity is represented by a relationA third relation, the intersection relation, is used to represent the relationship itself.The data structure diagramFig. 6-11,12,13,14

Representing Recursive Relationships

Recursive relationships may be 1:1, 1:N, or N:M Each type is represented the same way as the corresponding type of HAS-A relationships described earlier.With many-to-many recursive relationships the rows of the tables can be parent or child rows.Fig. 6-15,16,17,18

Representing IS-A Relationships (Subtypes)

One relation is needed for the supertype and one relation for each subtype. The key of the subtype relations may be the key of the supertype.One or more of the subtypes may have a key of its own.Example pg. 153 and fig. 6-19

Trees, Networks, and Bills of Materials

TreesTrees or hierarchies; Nodes; Branches; Parent; Children; Twins or siblings.A hierarchy or tree is a collection of records organized in such a way that all relationships are 1:N. All records (except the root) in a tree have exactly one parent. Fig. 6-20

Simple NetworksA simple network is a data structure of elements with one-to-many relationships.Elements may have more than one parent as long as the parents are of different types.Fig. 6-21,22,23

Complex NetworksOne of the relationships among entities is a many-to-many relationship.Requires an intersection relation. Fig. 6-24

• Bills of Materials It is a special form of a network. Most commonly represented as a M:N recursive relationship. Fig. 6-26