ER-Diagram Exercises

Q 01) Design an ER-diagram for a Bank Management System.

----------------------------------------------------------------------------

ER diagram of Bank has the following description :

• Bank have Customer.

• Banks are identified by a name, code, address of main office.
• Banks have branches.
• Branches are identified by a branch_no., branch_name, address.
• Customers are identified by name, cust-id, phone number, address.
• Customer can have one or more accounts.
• Accounts are identified by account_no., acc_type, balance.
• Customer can avail loans.
• Loans are identified by loan_id, loan_type and amount.
• Account and loans are related to bank’s branch.

This bank ER diagram illustrates key information about bank, including entities such as branches,
customers, accounts, and loans. It allows us to understand the relationships between entities.

Entities and their Attributes are :

• Bank Entity : Attributes of Bank Entity are Bank Name, Code and Address.
Code is Primary Key for Bank Entity.
• Customer Entity : Attributes of Customer Entity are Customer_id, Name, Phone Number
and Address.
• Customer_id is Primary Key for Customer Entity.
• Branch Entity : Attributes of Branch Entity are Branch_id, Name and Address.
• Branch_id is Primary Key for Branch Entity.
• Account Entity : Attributes of Account Entity are Account_number, Account_Type and
Balance.
• Account_number is Primary Key for Account Entity.
• Loan Entity : Attributes of Loan Entity are Loan_id, Loan_Type and Amount.
• Loan_id is Primary Key for Loan Entity.

Institute of Computer Engineering Technology

Relationships are :

Bank has Branches => 1 : N

One Bank can have many Branches but one Branch can not belong to many Banks, so the
relationship between Bank and Branch is one to many relationship.

Branch maintain Accounts => 1 : N

One Branch can have many Accounts but one Account can not belong to many Branches, so the
relationship between Branch and Account is one to many relationship.

Branch offer Loans => 1 : N

One Branch can have many Loans but one Loan can not belong to many Branches, so the
relationship between Branch and Loan is one to many relationship.

Account held by Customers => M : N

One Customer can have more than one Accounts and also One Account can be held by one or more
Customers, so the relationship between Account and Customers is many to many relationship.

Loan availed by Customer => M : N

(Assume loan can be jointly held by many Customers).
One Customer can have more than one Loans and also One Loan can be availed by one or more
Customers, so the relationship between Loan and Customers is many to many relationship.

Q 02) Design an ER-diagram for a Library Management System.

-------------------------------------------------------------------------------
• The Library Management System database keeps track of readers with the following
considerations.
•
• The system keeps track of the staff with a single point authentication system comprising
login Id and password.

• Staff maintains the book catalog with its ISBN, Book title, price(in INR), category(novel,
general, story), edition, author Number and details.

• A publisher has publisher Id, Year when the book was published, and name of the book.
• Readers are registered with their user_id, email, name (first name, last name), Phone no
(multiple entries allowed), communication address.

• The staff keeps track of readers.

• Readers can return/reserve books that stamps with issue date and return date. If not returned
within the prescribed time period, it may have a due date too.

• Staff also generate reports that has readers id, registration no of report, book no and
return/issue info.

Institute of Computer Engineering Technology

Entities and their Attributes:

• Book Entity : It has authno, isbn number, title, edition, category, price. ISBN is the Primary
Key for Book Entity.

• Reader Entity : It has UserId, Email, address, phone no, name. Name is composite attribute
of firstname and lastname. Phone no is multi valued attribute. UserId is the Primary Key for
Readers entity.

• Publisher Entity : It has PublisherId, Year of publication, name. PublisherID is the Primary
Key.

• Authentication System Entity : It has LoginId and password with LoginID as Primary Key.

• Reports Entity : It has UserId, Reg_no, Book_no, Issue/Return date. Reg_no is the Primary
Key of reports entity.
• Staff Entity : It has name and staff_id with staff_id as Primary Key.

• Reserve/Return Relationship Set : It has three attributes: Reserve date, Due date, Return
date.

Relationships between Entities:

• A reader can reserve N books but one book can be reserved by only one reader. The
relationship 1:N.
• A publisher can publish many books but a book is published by only one publisher. The
relationship 1:N.
• Staff keeps track of readers. The relationship is M:N.
• Staff maintains multiple reports. The relationship 1:N.
• Staff maintains multiple Books. The relationship 1:N.
• Authentication system provides login to multiple staffs. The relation is 1:N.

Institute of Computer Engineering Technology

Q 03) Design an ER-diagram for a Shop.
-------------------------------------------------

ER diagram of Company has the following description :

• Customer can place several orders.

• Each order may have several items.
• Items are identified by an itemCode.
• Each order is associated with number of items.
• Customers are identified by name,customer id, address, salary.
• An order has one customer & orders are identified by OrderId.

Entities and their Attributes are:

• Customer Entity : Attributes of Customer Entity are Name, customerId, Address & Salary.
• customerId is Primary Key for Customer Entity.
• Orders Entity : Attributes of Orders Entity are OrderId, OrderDate.
• OrderId is Primary Key for Orders Entity.
• Item Entity : Attributes of Item Entity are itemCode, unitPrice, description & qtyOnHand.
• itemCode is Primary Key for Item Entity.
• OrderDetail Assosiate Entity : Attributes of OrderDetail Assosiate Entity is qty.

04) ER Diagram for a University.

• A University contains many Faculties.

• The Faculties in turn are divided into several Schools.

• Each School offers numerous programs and each program contains many courses.

• Lecturers can teach many different courses and even the same course numerous times.

• Courses can also be taught by many lecturers.

• A student is enrolled in only one program but a program can contain many students.

• Students can been rolled in many courses at the same time and the courses have many
students enrolled.

Institute of Computer Engineering Technology

//---------------------------------------------------------------------------------------------------------------
Fill in cardinality

*The university contains many faculties

*Each faculty is divided into several schools
*Each school offers numerous programs
*Each program contains many courses
*Each school employs many lecturers
*Lecturers can teach many courses
*Lecturers can teach the same course many times
*Courses can be taught by more than one lecturer
*A student is enrolled in only one program
*Students can be enrolled in many courses at the same time
*Courses have many students enrolled

Institute of Computer Engineering Technology