Mapping (Converting) ERD INTO Relation (Tables) | Database Management System

Mapping (Converting) ERD INTO Relation (Tables) | Database Management System

Overview

  • map 1:1 relationships into relations
  • map 1:m relationships into relations
  • map m:n relationships into relations
  • differences between mapping optional and mandatory relationships.

What is a Relation?

A relation is a table that holds the data we are interested in. It is two-dimensional and has rows and columns.

Each entity type in the ER model is mapped into a relation.

  • The attributes become the columns.
  • The individual entities become the rows.

 

Relations can be represented textually as:
tablename (primary key, attribute 1, attribute 2, . . . , foreign key)

If matric no was the primary key, and there were no foreign keys, then the table above could be represented as:

student (matric no, name, address, date of birth)

When referring to relations or tables, cardinality is considered to the number of rows in the relation or table, and arity is the number of Columns in a table or attributes in a relation.

Foreign Keys

A foreign key is an attribute (or group of attributes) that is the primary key to another relation.

  • Roughly, each foreign key represents a relationship between two entity types.
  • They are added to relations as we go through the mapping process.
  • They allow the relations to be linked together.
  • A relation can have Several foreign keys.
  • It will generally have a foreign key from each table that it is related to.
  • Foreign keys are usually shown in italics or with a wiggly underline.

Preparing to map the ER Model

Before we start the actual mapping process we need to be certain that we have simplified the ER model as much as possible.

This is the ideal time to check the model, as it is really the last chance to make changes to the ER model without causing major complications.

Mapping 1:1 Relationships

Before tackling a 1:1 relationship, we need to know its optionality.
There are three possibilities the relationship can be:

  • mandatory at both ends
  • mandatory at one end and optional at the other
  • optional at both ends

Mandatory at both Ends

If the relationship is mandatory at both ends it is often possible to subsume one entity type into the other.

  • The choice of which entity type subsumes the other depends on which is the most important entity type (more attributes, better key, Semantic nature of them).
  • The result of this amalgamation is that all the attributes of the swallowed up’ entity become attributes of the more important entity.
  • The key of the subsumed entity type becomes a normal attribute.
  • If there are any attributes in common, the duplicates are removed.
  • The primary key of the new combined entity is usually the same as that of the original more important entity type.

When not to Combine

There are a few reason why you might not combine a 1:1 mandatory relationship.

  • The two entity types represent different entities in the ‘real world’.
  • The entities participate in very different relationships with other entities.
  • Efficiency considerations when fast responses are required or different patterns of updating occur to the two different entity types.

If not Combined…

  • If the two entity types are kept separate then the association between them must be represented by a foreign key.
  • The primary key of one entity type comes the foreign key in the other.
  • It does not matter which way around it is done but you should not have a foreign key in each entity.

Example:

  • Two entity types, staff and contract.
  • Each member of Staff must have one Contract and each contract must have one member of Staff associated with it.
  • It is therefore a mandatory relations at both ends.

 

Fig. Mandatory Relationship

  1.  These to entity types could be amalgamated into one.
    Staff (emp no, name, cont_no, start, end, position, salary)
  2. Or kept apart and a foreign key used
    Staff (emp no name, contract no.)
    Contract(cont_no start end position salary)
  3. Or
    Staff (emp no, name)
    Contract (C.Ont no, Start, end, position, salary, emp no)

Mandatory Optional

The entity type of the optional end may be subsumed into the mandatory end as in the previous example. It is better NOT to subsume the mandatory end into the optional end as this will create null entries.

 

Fig. With 1. Optional End

If we add to the specification that each staff member may have at most one contract (thus making the relation optional at one end).

Map the foreign key into Staff-the key is null for staff without a contract.

Staff (emp no, name, contract no)
Contract (Cont no start, end, position, salary)

Map the foreign key into Contract – emp_no is mandatory thus never null.

Staff (emp no, name)
Contract(cont no start, end position, salary, emp_no)

Example:

Consider this example:

  • Staff “Gordon”, emp no 10, contract no 11.
  • Staff “Andrew’, emp no 11, no contract.
  • Contract 11, from 1st Jan 2001 to 10th Jan 2001, lecturer, on $2.00 a year.

Foreign key in Staff:

 

However, Foreign Key in Contract

 

As you can see, both ways store the same information, but the second way has no NULLS. Mandatory Optional – Subsume?

The reasons for not subsuming are the same as before with the following additional reason. Very few of the entities from the mandatory end are involved in the relationship. This could cause a lot of wasted Space with many blank or null entries.

If only a few lecturers manage Courses and Course is subsumed into Lecturer then there would be many null entries in the table.

Lecturer (lect no, l name, Cno, c. name, type, yr vetted, external)

It would be better to keep them separate.

Lecturer (lect no, 1_name )
Course (Cno, C_name, type, yr_Vetted, external, lect_no)

Summary

So for 1:1 optional relationships, take the primary key from the ‘mandatory end and add it to the ‘optional end as a foreign key.

So, given entity types A and B, where AB is a relationship where the A end it optional, the result would be:
A (primary key, attribute, , . . , foreign key to B )

A (primary key, attribute,  . . . )

Optional at both Ends
Such examples Cannot be amalgamated as you could not selecta primary key. Instead, one foreign key is used as before.

Fig. Optional End

  • Each Staff member may lease up to one car
  • Each car may be leased by at most one member of Staff
  • If these were Combined together…

Staff car (emp no, name, regno, year, make, type, colour)
what would be the primary key?

  • If emp no is used then all the cars which are not being leased will not have a key.
  • Similarly, if the regno is used, all the staff not leasing a car will not have a key.
  • A compound key will not work either.

Mapping 1:m relationships

To map 1:m relationships, the primary key on the ‘one side of the relationship is added to the many

Fig. Mapping 1:m Relationships

For example, the 1:m relationship ‘course-student’:

Assuming that the entity types have the following attributes:
Course ( course_no, C_name)
Student (matric_no, st name, dob)

Then after mapping, the following relations are produced:
Course (Course ՈO, C_name)
Student (Inatric nO, St name, dob, Course mo)

 

If an entity type participates in several 1:m relationships, then you apply the rule to each relationship, and add foreign keys as appropriate.

Mapping n:m relationships

If you have some min relationships in your ER model then these are mapped in the following manner.

 

Fig. Mapping n:m Relationships

A new relation is produced which contains the primary keys from both sides of the relationship.

These primary keys form a composite primary key.

Thus
Student (matric_no, st_name, dob)
Module (module no, m name, level, credits)

Becomes
Student (matric no, st name, dob)
Module (module no. In name, level, credits)
Studies (matric IIO, Inodule no)

 

Fig. After Mapping an:m Relationship

This is equivalent to:
Student (matric_no, st_name, dob)
Module (module_no, In_name, level, credits)
Study ()

Summary

  • 1-1 relationships Depending on the optionality of the relationship, the entities are either combined or the primary key of one entity type is placed as a foreign key in the other relation.
  • 1-m relationships The primary key from the ‘one side’ is placed as a foreign key in the ‘many side’.
  • m-n relationships A new relation is Created with the primary keys from each entity forming a composite key.