Module 2 - DBMS - 1ST INTERNAL - Converting ER To Table

Education and Research
We enable you to leverage knowledge anytime, anywhere!
Converting ER Diagram to relational schema
ER/CORP/CRS/DB92 Ver. No.: 0.1 Confidential Copyright © 2008, Infosys Technologies Ltd.
Converting Strong entity types
 Each entity type becomes a table
 Each single-valued attribute becomes a column
 Derived attributes are ignored
 Composite attributes are represented by its equivalent parts
 Multi-valued attributes are represented by a separate table
 The key attribute of the entity type becomes the primary key of the
table
Copyright © 2008, Infosys Technologies Ltd. 2 Confidential

Entity example
 Here address is a composite

attribute
 Years of service is a derived

attribute (can be calculated
from date of joining and
current date)
 Skill set is a multi-valued

attribute
Employee (E#, Name, Door_No, Street, City, Pincode, Date_Of_Joining)
And
Emp_Skillset( E#, Skillset)

Converting weak entity types
 Weak entity types are
converted into a table of their
own, with the primary key of the
strong entity acting as a foreign
key in the table.
 This foreign key along with the

key of the weak entity forms the
composite primary key of this
weak table
 The Relational Schema
Employee (E# ,EmpName,DateOfJoining,SkillSet)
Dependant (Employee, Dependant_ID, Name, Address)

Converting relationships
 The way relationships are represented depends on the cardinality

and the degree of the relationship
 The possible cardinalities are:

1:1, 1:M, N:M
 The degrees are:

» Unary
» Binary
» Ternary

Binary 1:1
Employee 1 head 1 Department

of
l
partia otal
T
 Case 1: Combination of participation types
 The primary key of the partial participant will become the foreign key
in the total participant.
Employee( E#, EName,DateOfJoining,SkillSet)
Department (Dept#, DName,Location,Head)

Binary 1:1
Employe Sits_on CHAIR

e
 Case 2: Uniform participation types
 The primary key of either of the participants can become a foreign key in the
other
Employee (EmpCode,EmpName,DateOfJoining)
Chair( Item#, Model, Location, Used_by)
(OR)
Employee (EmpCode,EmpName,DateOfJoining,Sits_on)
Chair (Item#, Model, Location)

Binary 1:N
1 N
Teacher Teaches Subject
The primary key of the relation on the “1” side of the relationship
becomes a foreign key in the relation on the “N” side.
Teacher (TeacherID, Name, Telephone, Cabin)
Subject (SubCode, SubName, Duration, TeacherID)

Binary M:N
M N
Student Enrolls Course
 A new table is created to represent the relationship which contains two

foreign keys - one from each of the participants in the relationship.
 The primary key of the new table is the combination of the two foreign
keys.
 Student (StudentID,SName,DOB,Address) Course(CourseID,CName)
Enrolls (SID, CID)

Unary 1:1
• Consider employees who are also
a couple
• The primary key field itself will
become foreign key in the same
table
Employee( E#, EName,…Spouse)

Unary 1:N
• The primary key field itself

will become foreign key in
the same table.
• Same as unary 1:1
Employee( E#, EName, DateOfJoining, SkillSet, Manager)

Unary M:N
M Guarantor_of
N
Employee
 There will be two resulting tables. One to represent the entity and
another to represent the M:N relationship as follows
Employee( E#, EName, DateOfJoining, SkillSet)
Guaranty( Guarantor, Beneficiary)

Ternary relationship
 Represented by a new table.
 The new table contains three

foreign keys - one from each of
the participating Entities.
 The primary key of the new table

is the combination of all three
foreign keys.
 Prescription (DocID, PatCode,
MedName)
Visit the following link for more

information
Why Build A Logical Data Model
Deriving Logical Schema for Online Retail Application
 There are four strong entities in the ER Diagram of Online Retail Application.
 Customer
 Item
 Bill
 Supplier
 One binary 1:N relationships

 Pays
 One binary M:N relationship

 OrderedTo
 One Ternary Relationship

 Purchase

Deriving Logical Schema for Online Retail
Application
So we can think of the following relations
Customer(CustomerId, CustomerName, DateOfRegistration, UserId,

Password) AccountNo
is multi
valued
Attribute
BankInfo(CustomerId, AccountNo)
 CustomerId is the Primary key of the Customer relation.

 (CustomerId,AccountNo) is the primary key of BankInfo relation
 CustomerId of BankInfo relation is foreign key and refers to CustomerId
attribute of Customer relation.

Application
Billing(BillId, BillDate, CustomerId,AccountNo) Pay relationship is

1:N so no need to
create separate
 BillId is the Primary key of the Billing relation relation
 (CustomerId,AccountNo) are composite foreign key and refers
(CustomerId,AccountNo) column of BankInfo relation.
Item(ItemId,ItemName,QtyOnHand,UnitPrice,UnitOfMeasurement,ReOrderLevel,ReOr
derQty,Discount)
SupplierId is
ItemSupplier(ItemId, SupplierId) multi valued
in Item entity
 ItemId is the Primary key of the Item relation

 (ItemId,SupplierId) is primary key of ItemSupplier relation
 ItemId of ItemSupplier relation is foreign key to itemId of Item relation
16 Confidential
Copyright © 2008, Infosys Technologies Ltd.
Application
Supplier(SupplierId, SupplierName,SupplierContactNo)
ItemOrdered(ItemId,SupplierId,QtyOrdered,DeliveryStatus,OrderDate,
DeliveryDate) OrderedTo
relationship
is M:N so
new relation
is required
 SupplierId is Primary key of Supplier Relation
 (ItemId,SupplierId) is composite primary key of ItemOrdered relation
 ItemId of ItemOrdered relation is foreign key to Item relation
 SupplierId of ItemOrdered relation is foreign key to Supplier relation

Deriving Logical Schema for Online Retail Application
CustomerPurchase(CustomerId, ItemId, BillId, QtyPurchased, NetPrice)
Purchase relationship is
ternary so new relation is
required.
 (CustomerId,ItemId,BillId) are composite Primary key of

CustomerPurchase relation
 CustomerId of CustomerPurchase relation is foreign key to Customer
relation
 ItemId of CustomerPurchase relation is foreign key to Item relation
 BillId of CustomerPurchase relation is foreign key of Billing relation
18 Confidential
Copyright © 2008, Infosys Technologies Ltd.

Module 2 - DBMS - 1ST INTERNAL - Converting ER To Table

Uploaded by

Copyright:

Available Formats

Module 2 - DBMS - 1ST INTERNAL - Converting ER To Table

Uploaded by

Document Information

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

Module 2 - DBMS - 1ST INTERNAL - Converting ER To Table

Uploaded by

Copyright:

Available Formats

Education and Research

We enable you to leverage knowledge anytime, anywhere!

Converting ER Diagram to relational schema

 Each entity type becomes a table

 Each single-valued attribute becomes a column

 Derived attributes are ignored

 Composite attributes are represented by its equivalent parts

 Multi-valued attributes are represented by a separate table

Copyright © 2008, Infosys Technologies Ltd. 2 Confidential

 Here address is a composite

 Years of service is a derived

 Skill set is a multi-valued

Copyright © 2008, Infosys Technologies Ltd. 3 Confidential

 This foreign key along with the

 The Relational Schema

Employee (E# ,EmpName,DateOfJoining,SkillSet)

Dependant (Employee, Dependant_ID, Name, Address)

Copyright © 2008, Infosys Technologies Ltd. 4 Confidential

 The way relationships are represented depends on the cardinality

 The possible cardinalities are:

 The degrees are:

Copyright © 2008, Infosys Technologies Ltd. 5 Confidential

Employee 1 head 1 Department

 Case 1: Combination of participation types

Employee( E#, EName,DateOfJoining,SkillSet)

Department (Dept#, DName,Location,Head)

Employe Sits_on CHAIR

Chair( Item#, Model, Location, Used_by)

Chair (Item#, Model, Location)

Copyright © 2008, Infosys Technologies Ltd. 7 Confidential

Teacher (TeacherID, Name, Telephone, Cabin)

Subject (SubCode, SubName, Duration, TeacherID)

Copyright © 2008, Infosys Technologies Ltd. 8 Confidential

 A new table is created to represent the relationship which contains two

Enrolls (SID, CID)

Copyright © 2008, Infosys Technologies Ltd. 9 Confidential

Employee( E#, EName,…Spouse)

Copyright © 2008, Infosys Technologies Ltd. 10 Confidential

• The primary key field itself

• Same as unary 1:1

Employee( E#, EName, DateOfJoining, SkillSet, Manager)

Copyright © 2008, Infosys Technologies Ltd. 11 Confidential

Employee( E#, EName, DateOfJoining, SkillSet)

Guaranty( Guarantor, Beneficiary)

 Represented by a new table.

 The new table contains three

 The primary key of the new table

Visit the following link for more

 One binary 1:N relationships

 One binary M:N relationship

 One Ternary Relationship

Copyright © 2008, Infosys Technologies Ltd. 14 Confidential

So we can think of the following relations

Customer(CustomerId, CustomerName, DateOfRegistration, UserId,

 CustomerId is the Primary key of the Customer relation.

Copyright © 2008, Infosys Technologies Ltd. 15 Confidential

Billing(BillId, BillDate, CustomerId,AccountNo) Pay relationship is

 ItemId is the Primary key of the Item relation

Copyright © 2008, Infosys Technologies Ltd. 17 Confidential