Page 107 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes To enable the readers for a better understanding of ER diagram we shall present few examples
in this study. The reader should go through these examples carefully for a better and easier way
of writing ER diagrams.


Case Study Insurance Policy Management System
onsider a popular and common problem in the today’s world: Insurance Policy
Management System. The Software Requirements Specifications (SRS) for this
Cproblem can be stated as follows:
1. The Insurance Company has many branches with branchid, branch name, address or
location, phone numbers, fax, etc.
2. In every branch there are different types of staff working. For example, there is a
branch manager, field officers, development personnel, secretarial assistants, etc. It
is necessary to keep track of staffed, staff name, address, position, salary, date of
birth, etc.
3. Apart from the regular employees there are part-time staff called insurance agents
who work on commission basis.
4. The Insurance Company must store the details of policy holders: policy holder’s
name, policy number, address, tenure, maturity amount, premium amount, etc. It is
mandatory to mention the nominees in every policy.

With our knowledge of ER diagram studied so far, the various entity sets and the attributes
can be identified as follows:

Step-1 and Step-2: Entity Sets and Attributes

1. Branches BranchID, BranchName, Addr, Phone
2. Staff StaffID, StaffName, Addr

3. Customers PolicyNo, Name, Addr, Age, Phone
4. Policy (weak) PolicyName, Tenure, MatAmount, PremAmount, Bonus

Sub Entity Sets of Staff

1. Managers Exp, Qualification
2. DevOfficers Qualification
3. SectAssts TypingSpeed
4. FullTime Salary, Position
5. Agents Type, Comm
Policy is a weak entity, because without a customer or policy holder the insurance policy
doesn’t exist. Also, the entity set Policy does not have primary key, but only partial
key (PolicyName). The name of the policy may be as follows: Endowment, Moneyback,
Medical, etc.
Figure depicts the ER Diagram for Insurance Policy management with all the constraints
incorporated.
Contd...

100 LOVELY PROFESSIONAL UNIVERSITY