Converting ER model Into Relational Table Tarek ElShishtawy

Converting ER model Into Relational Table Tarek El-Shishtawy Professor Ass. Of Computer Engineering 1

Mapping Regular Entities l Each regular entity type in an ER diagram is transformed into a relation. The name given to the relation is generally the same as the entity type. l Each simple attribute of the entity type becomes an attribute of the relation. – The identifier of the entity type becomes the primary key of the corresponding relation. 2

Example Table PLAYER Player Name Roger Federer Roddick Nation Switzerland USA Position 1 2 Number of won 5 4 Entity name = Name of the relation or table. l Attributes of ER diagram=Column name of the table. l 3

Example using Oracle designer notations 4

Converting Composite Attribute CUSTOMER Customer-ID l Name Street City State Zip only the simple component attributes of the composite attribute are included in the relation. 5

Mapping Multivalued Attributes in ER l One way to map a multivalued attribute is to create two tables. 6

Converting “Weak Entities” 1 m 7

Converting “Weak Entities” l l l For each weak entity type, create a new relation All of the simple attributes as attributes of the relation. The primary key of the identifying relation as a foreign key attribute to this new relation. The primary key of the new relation is the combination of the primary key of the identifying and the partial identifier of the weak entity type. In this example DEPENDENT is weak entity. 8

Converting “Weak Entities” 9

Converting Binary Relationship Mapping one-to-Many Relationship 1 m One customer can give many orders. 10

Mapping one-to-Many Relationship 11

Using Oracle Designer notation 12

Binary one-to-one relationship l Can be viewed as a special case of one-to -many relationships. l First, two relations are created, one for each of the participating entity types. l Second, the primary key of one of the relations is included as a foreign key in the other relation. l Example Parking place and employee 13

Many to Many relationships M-M Needs an intersection table Note: Pre-requests requires more additional table 14

Many to Many relationships 15

Mapping Associative Entity to Tables Without Identifier 16

Mapping Associative Entity to Tables 17

Mapping Associative Entity to Tables With Identifier 18

Mapping Associative Entity to Tables With Identifier 19

Converting Unary Relationship Each employee has exactly one manager. A given employee may manage zero to many employees. The foreign key in the relation is named Manager. ID 20

Converting Unary Relationship 21

Converting Ternary Relationship 22

Converting Ternary Relationship 23