Detailed Report: Entity Relationship Diagram for CQ Insurance Company

This report provides a detailed Entity Relationship Diagram (ERD) for the CQ Insurance Company. It begins by outlining the assumptions made during the diagram's creation, such as a single person owning multiple properties with insured items. The report then details the business rules governing the ERD, which include the storage of policy details, customer information, property and item specifics, policy enrollment records, and agent details. The logical design of the database is presented, including entities like Customers, Properties, Home Items, Policies, Policy Enrollment, Claims, and Insurance Agents. Each entity's attributes and primary keys are defined, ensuring the database is in the third normal form. The report concludes with a bibliography of relevant academic sources.

Running head: ENTITY RELATIONSHIP DIAGRAM
Entity Relationship Diagram
Name of the Student:
Name of the University:
Author Note

1
ENTITY RELATIONSHIP DIAGRAM
ER Diagram
Figure 1: Entity relationship diagram of CQ Insurance Company
Source: (created by Author)
Assumptions
The Entity relationship diagram for the CQ Insurance Company has been described in
this report. For the creation of the ER Diagram the following assumptions are taken into account:
 A single person would be owning many properties, the properties would be having a
number of items in them. The items would be applicable for the insurance policies.
 The insurance company would be having a set of policies in which the customers would
be enrolling themselves.
 There would be a number of agents that would working with the company and helping
the customers in obtaining their claims.
 It is also assumed that a single agent would be able to handle a number of policies.

2
ENTITY RELATIONSHIP DIAGRAM
Business rules
The ER Diagram has been constructed according to the set of business rules provided in
the case study. However, during the construction of the ER Diagram a number of assumptions
have been made. These assumptions have resulted in the alternation of the business rules to a
certain extent. Hence, the new set of business rules are:
 The insurances company would be maintaining a set of policies and all the details of the
policies of the company will be stored in the system with unique policy numbers. The
customers would be able handle the polices using the unique policy ids.
 The company would also be storing the details of the customer, their property details and
the items within the properties that they are looking to insure. The properties and the
items should be stored separately.
 The company also needs to store the policy enrollment details of the company. The
policy enrollment details should be providing the company with the details about the date
of enrollment, policy number and the customer identification number. The enrolments
should also be stored with a unique enrollment id.
 The system should also store the details of the agents of the company, who would be
identified with the agent ids and the details of the agent can be easily fetched by their
unique identification numbers. Additionally, the system should also be able to store the
details of the agents such as their name, salaries and contact details.
Logical Designs
The ER Diagram design includes sets of attributes and entities that are already in the third
normal form. The set of entities in the diagram are listed below:

3
ENTITY RELATIONSHIP DIAGRAM
 Customers
 Properties
 Home Items
 Policies
 Home Content Policies
 Home Building Policies
 Policy enrollment
 Claims
 Insurance Agent
The logical design of the database that is to be created from ER Diagram is provided below:
Customers (CustomerNumber (primary key), Name, Address, Email)
Properties (PropertyId (primary key), Location, Details, CustomerNumber (foreign key))
Home Items (ItemId (primary key), Description, MarketValue, PropertyId (foreign key) )
Policy (PolicyNumber (primary key), Details, date, Duration)
Home Content Policy (PolicyNumber (primary key), Details, date, Duration)
Home Building Policy (PolicyNumber (primary key), Details, date, Duration)
Policy Enrollment (PolicyId (primary key), CustomerNumber (foreign key), PropertyId
(foreign key), ItemId (foreign key), PolicyNumber (foreign key), PolicyDate)
Claims (ClaimsId (primary key), claimsDate, AgentId (primary key), PolicyId (primary key),
CustomerNumber (primary key))
Insurance Agent (AgentId (primary key), AgentName, Salary, ContactNumber, AgentMail)

4
ENTITY RELATIONSHIP DIAGRAM

5
ENTITY RELATIONSHIP DIAGRAM
Bibliography
Cagiltay, N. E., Ozcelik, E., & Ozcelik, N. S. (2015). The effect of competition on learning in
games. Computers & Education, 87, 35-41.
Celis-Morales, C., Livingstone, K. M., Marsaux, C. F., Macready, A. L., Fallaize, R.,
O’donovan, C. B., ... & San-Cristobal, R. (2016). Effect of personalized nutrition on
health-related behaviour change: evidence from the Food4me European randomized
controlled trial. International journal of epidemiology, 46(2), 578-588.
Di Mascio, D., Magro-Malosso, E. R., Saccone, G., Marhefka, G. D., & Berghella, V. (2016).
Exercise during pregnancy in normal-weight women and risk of preterm birth: a
systematic review and meta-analysis of randomized controlled trials. American Journal
of Obstetrics & Gynecology, 215(5), 561-571.
Ezeigwe, E. R., Tan, M. T., Khiew, P. S., & Siong, C. W. (2015). One-step green synthesis of
graphene/ZnO nanocomposites for electrochemical capacitors. Ceramics
International, 41(1), 715-724.