ERD To Relational Schema Mapping

ERD To Relational Schema Mapping
The ER Model is intended as a description of real-world entities. Although it is

constructed in such a way as to allow easy translation to the relational schema model,
this is not an entirely trivial process. The ER diagram represents the conceptual level of
database design meanwhile the relational schema is the logical level for the database
design. We will be following the simple rules:
1. Entities and Simple Attributes:

An entity type within ER diagram is turned into a table. You may preferably keep the
same name for the entity or give it a sensible name but avoid DBMS reserved words
as well as avoid the use of special characters
Each attribute turns into a column (attribute) in the table. The key attribute of the
entity is the primary key of the table which is usually underlined. It can be composite
if required but can never be null.
Note: It is highly recommended that every table should start with its primary key
attribute conventionally named as Table name ID.
For example:
The initial relational schema is expressed in the following format writing the table
names with the attributes list inside a parenthesis as shown below for
Persons (person id, name, last name, email)
Persons and Phones are Tables. name, last name, are Table Columns (Attributes).
2. Multi-Valued Attributes:
A multi-valued attribute is usually represented with a double-line oval.
If you have a multi-valued attribute, take the attribute and turn it into a new entity or
table of its own. Then make a 1: N relationship between the new entity and the existing
one. In simple words. 1. Create a table for the attribute. 2. Add the primary (id) column
of the parent entity as a foreign key within the new table as shown below:

Phones (phone id, person id, phone)
3. 1:1 Relationships:
A male marries 0 or 1 female and vice versa as well. So, it is 1:1 cardinality with partial
participation constraint from both. First Convert each entity and relationship to
tables. Male table corresponds to Male Entity with key as M-Id. Similarly, Female table
corresponds to Female Entity with key as F-Id. Marry Table represents relationship
between Male and Female (Which Male marries which female). So, it will take attribute
M-Id from Male and F-Id from Female.
Binary relationship with 1:1 cardinality will have 2 table if partial participation of both
entities in the relationship. If at least 1 entity has total participation, number of tables
required will be 1.
Male (male id, other attributes, Female id)

Female (female id, other attributes)
For cases when the Person is not married i.e. has no female ID, the attribute can set to
NULL
Male (male id, other attributes)

Female (female id, other attributes, male id)
4. 1: N Relationships:
This is the tricky part. For simplicity, use attributes in the same way as 1:1 relationship
but we have only one choice as opposed to two choices. For instance, the Person can
have a House from zero to many, but a House can have only one Person. To
represent such relationship the person id as the Parent node must be placed within the
Child table as a foreign key but not the other way around as shown next:
It should convert to:

House (house id, num, address, person id)
5. N: N Relationships:
We normally use tables to express such type of relationship. This is the same for N to
any relationship of ER diagrams. For instance, The Person can live or work in many
countries. Also, a country can have many people. To express this relationship within a
relational schema we use a separate table as shown below:
It should convert to:

Countries (country id, name, code)
Has (person id, country id)
6. Weak Entity:
In this scenario, an employee can have many dependents and one dependent can
depend on one employee. A dependent does not have any existence without an
employee (e.g.; you as a child can be dependent of your father in his company). So, it
will be a weak entity and its participation will always be total. Weak Entity does not have
key of its own. So, its key will be combination of key of its identifying entity (E-Id of
Employee in this case) and its partial key (D-Name).
First Convert each entity and relationship to tables. Employee table corresponds to Employee
Entity with key as E-Id. Similarly, Dependents table corresponds to Dependents Entity with key
as D-Name and E-Id. Has Table represents relationship between Employee and Dependents
(Which employee has which Dependents). So, it will take attribute E-Id from Employee and D-
Name from Dependents
Employee (E id, other attributes)

Dependent (D-name, E id, other dependent attributes)
7. Composite Attributes:
 Relational model doesn’t handle composite attributes

 When mapping E-R composite attributes to relation schema:
 Each component attribute maps to a separate attribute in relation
schema
 In relation schema, simply can’t refer to composite as a whole
 (Can adjust this mapping for databases that support composite
types)
For example:
Student (name, first name, last name)

ERD To Relational Schema Mapping

Uploaded by

Copyright:

Available Formats

ERD To Relational Schema Mapping

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

ERD To Relational Schema Mapping

Uploaded by

Copyright:

Available Formats

ERD To Relational Schema Mapping

The ER Model is intended as a description of real-world entities. Although it is

1. Entities and Simple Attributes:

Persons (person id, name, last name, email)

Persons (person id, name, last name, email)

Male (male id, other attributes, Female id)

Male (male id, other attributes)

It should convert to:

Persons (person id, name, last name, email)

It should convert to:

Persons (person id, name, last name, email)

Employee (E id, other attributes)

 Relational model doesn’t handle composite attributes

Student (name, first name, last name)

You might also like