Database Design and Development: CQ Insurance Case Study

This report details the database design and development for CQ Insurance (CQI), a company providing home and contents insurance. It begins with an Entity Relationship Diagram (ERD) illustrating entities like Customers, Policy, Property, Home Contents, Policy Enrolment, Claims, and Agent, along with their relationships. The report outlines assumptions made during the ERD design, such as a single person owning multiple properties and selecting one policy per property. It also defines business rules, including unique policy numbers, customer and property data storage, and agent association with policies. The logical design section maps the ERD to relations, providing a relational database schema in the third normal form, including attributes for each entity. Finally, the report includes a list of references used in the design process.

Running head: DATABASE DESIGN AND DEVELOPMENT
Database design and development: A Case Study of CQ Insurance (CQI)
Student Name:
University Name:

1DATABASE DESIGN AND DEVELOPMENT
Table of Contents
1. ER diagram for CQ Insurance.....................................................................................................2
2. Assumptions and Business rules..................................................................................................2
2.1 Assumptions..........................................................................................................................2
2.2 Business Rules.......................................................................................................................3
3. Logical Design.............................................................................................................................4
References........................................................................................................................................6

2DATABASE DESIGN AND DEVELOPMENT
1. ER diagram for CQ Insurance
Figure 1: ER diagram for CQ Insurance
(Source: Created by Author)
2. Assumptions and Business rules
2.1 Assumptions
The ERD (Entity Relationship Diagram) has been presented above according to the case
of CQ Insurance (CQI) that provides insurance policies for Home buildings as well as contents.

3DATABASE DESIGN AND DEVELOPMENT
The assumptions that has been made for designing the ERD from the CQ Insurance case study
are listed as below:
 A single person may be owner of different properties and the properties may
contain several items.
 A single person will be able to select only one policy for each of the properties.
 The items contained within the properties may or may not be contained within
insurance coverage as it depends on the type of chosen policy.
 There is involvement of agents who are working as employees of the
organization to handle the claims processing for customers.
 A single agent will be associated with handling of several number of policies.
2.2 Business Rules
The design of the ER diagram has been done in accordance with the set of rules that has
been defined in the case study of CQ Insurance. Various assumptions has been made while
designing the ER diagram. The assumptions led to changes in the defined business rules from
certain perspectives. The business rules that has been determined after making the assumptions
are illustrated as below:
 Unique policy numbers will be used to store the various policies that will be
enrolled by the customers by following a set of specific standards and guidelines.
The customers will have the capability to handle their policies by using their
unique policy numbers.
 The details of customers are stored by the company along with the property
details and the various contents inside the properties that will be under insurance

4DATABASE DESIGN AND DEVELOPMENT
coverage. The information of properties of the customers and the contents will be
stored separately.
 The company will also store the policy details of customers in which they have
enrolled according to their requirement. The information of policy will contain the
enrolment date, policy type and the payable premium amount on behalf of each
customer with a unique identification number.
 The company will also have record of the agents that will be identified using
unique identification number. The agent id will be used to fetch the details of each
agent being associated with the policies being enrolled in the company.
3. Logical Design
The various entities and the related attributes that has been identified from the case study
of CQ Insurance are already in third normal form. The entities that has been determined from the
ER diagram are listed as below:
 Customers
 Policy
 Property
 Home Contents
 Policy Enrolment
 Claims
 Agent

5DATABASE DESIGN AND DEVELOPMENT
The ER diagram can be mapped into relations to form a Logical design for developing
database for CQ Insurance. Hence, the relations that has been determined from the designed ER
diagram are as below:
Customer (CustomerID (primary key), CustName, CustAddress, CustEmail)
Property (PropertyID (primary key), Location, Details, CustomerID (foreign key))
Home Contents (ContentsID (primary key), Description, MarketValue, PropertyID
(foreign key))
Policy (PolicyNo (primary key), Details, PolDate, Duration)
Policy Enrolment (EnrolmentID (primary key), PolicyNo (foreign key), PropertyID
(foreign key), ContentsID (foreign key), CustomerID (foreign key), PEnrolDate)
Claims (ClaimsID (primary key), ClaimsDate, AgentID (foreign key), EnrolmentID
(foreign key), CustomerID (foreign key))
Agent (AgentID (primary key), AgntName, Salary, ContactNo, AgntMail)

6DATABASE DESIGN AND DEVELOPMENT
References
Al-Btoush, A.A.S., 2015. Extracting Entity Relationship Diagram (ERD) from English
Sentences. International Journal of Database Theory and Application, 8(2), pp.235-244.
Al-Masree, H.K., 2015. Extracting Entity Relationship Diagram (ERD) from relational database
schema. International Journal of Database Theory and Application, 8(3), pp.15-26.
Dedhia, R., Jain, A. and Deulkar, K., 2015. Techniques to automatically generate Entity
Relationship Diagram.
Hingorani, K., Gittens, D. and Edwards, N., 2017. REINFORCING DATABASE CONCEPTS
BY USING ENTITY RELATIONSHIPS DIAGRAMS (ERD) AND NORMALIZATION
TOGETHER FOR DESIGNING ROBUST DATABASES. Issues in Information Systems, 18(1).
Jukic, N., Vrbsky, S. and Nestorov, S., 2016. Database systems: Introduction to databases and
data warehouses. Prospect Press.
Kruse, S.L.K. and Wells, M.G., 2016. Optionality of ERD Relationships: Project for the
Introduction to Database Course. In Proceedings of the EDSIG Conference ISSN (Vol. 2473, p.
3857).
Letkowski, J., 2015. Doing database design with MySQL. Journal of Technology Research, 6,
p.1.
Yoshizumi, T., Kirishima, T., Goto, T., Tsuchida, K. and Yaku, T., 2016, July. A graph grammar
for entity relationship diagrams. In Industrial Informatics (INDIN), 2016 IEEE 14th
International Conference on (pp. 810-815). IEEE.