Representing Entities:
Each entity type in an E-R model is convened into
a relation. The entity sets represented by rectangles become relations with the
same names as entity names (written inside the rectangles). The primary key of
the entity type becomes the primary key of the corresponding relation. Each
relation is also analyzed according to the normalization rules.
For example, a 'STUDENT' entity type is shown in
figure below. This entity contains the attributes Roll-No, Name, Address and
Marks.
You can easily convert the STUDENT entity into
relation. In this case, the entity name becomes the relation name and the
attributes of the entity become the columns of the relation. So the
corresponding relation of the above figure is represented as:
STUDENT (Roll-No, Name, Address, Marks)
The 'STUDENT' relation with sample data is given
below.
| Roll-No |
Name |
Address |
Marks |
| 1 |
S |
City: L |
753 |
| 2 |
R |
City: M |
759 |
| 3 |
H |
City: B |
792 |
| 4 |
F |
City: F |
774 |
| 5 |
B |
City: K |
811 |
Representing Composite Attributes in Database:
A composite attribute of an entity is divided
into smaller components. Each component of composite attribute is represented
with separate column in the relation. For example, Address attribute of STUDENT
entity is divided into ZipCode, City, StreetAddress etc. as shown below.
So the corresponding relation of the above figure
is represented as:
STUDENT (Roll-No, Name, Marks, Address, City,
ZipCode)
Representing Multi-Valued Attributes in Database:
If an entity contains multi-valued attributes,
then each attribute is represented with separate relation. For example, 'Phone'
multi-valued attribute of STUDENT entity is shown below.
So the corresponding relations of the above
figure are represented as:
STUDENT (Roll-No, Name, Address)
TELEPHONE (Roll-No, Phone)
It is noted that Roll-No and Phone attributes are
used as key for TELEPHONE
Representation of Weak Entities:
A weak entity depends for its existence on
another entity known as owner or parent. The weak entity itself should have some
attributes that can be associated with the owner's primary key. The weak
entities may have referential dependencies or general existence dependencies.
The existence dependencies must be recorded in the relational design so that no
application will create a weak entity without its proper parent. Similarly, a
business rule must be implemented so. that when the parent is deleted, the weak
entity is also deleted.
The weak entity relation is associated with the
parent relation. It is compulsory to add the key of the parent entity to the
weak entity's relation and this added attribute becomes part of the weak
entity's key. So to represent the weak entity for its existence on owner entity,
we create a relation whose attributes include all the attributes of the weak
entity, plus the primary key of the owner entity. It means that primary key of
the parent relation is used as foreign key the weak entity relation.
For example, if there are two entities such as
STUDENT and TELEPHONE. The TELEPHONE is weak entity and its existence depends on
the STUDENT entity. The primary key of STUDENT entity is Std-ID. Thus Std-ID is
also added in TELEPHONE entity. The key of TELEPHONE entity becomes a composite
key such as (Std-ID, Sr-No).
The STUDENT and TELEPHONE relations are:
STUDENT (StdID, Name, Address)
TELEPHONE (StdID, SrNo, Phone)
|
