Database Design and Development: A Case Study of CQ Insurance

The assignment requires students to analyze the database requirements of CQ Insurance and design a suitable database schema. This involves creating an Entity-Relationship (ER) diagram, making assumptions about customer policies, defining business rules, and designing tables for customers, policies, properties, home contents, policy enrollments, claims, and agents.

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.