Database Design and Development: A CQ Insurance (CQI) Case Study

This case study focuses on the database design and development for CQ Insurance (CQI). It includes an Entity Relationship Diagram (ERD) that models the entities and relationships within the insurance company's data. The design is based on specific assumptions and business rules related to customers, properties, policies, and agents. The logical design section translates the ERD into relational schemas, defining primary and foreign keys to ensure data integrity. The study covers customer information, property details, policy enrollment, claims processing, and agent management, providing a comprehensive overview of the database structure required for CQ Insurance.

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. Entity Relationship diagram........................................................................................................2
2. Assumptions and Business rules..................................................................................................2
2.1 Assumptions..........................................................................................................................2
2.2 Business Rules.......................................................................................................................3
3. Logical Design.............................................................................................................................4
Bibliography....................................................................................................................................6

2DATABASE DESIGN AND DEVELOPMENT
1. Entity Relationship diagram
Figure 1: ER diagram in context to the CQ Insurance case study
(Source: Created by Author)
2. Assumptions and Business rules
2.1 Assumptions
The ERD has been designed by analyzing the entities from the provided case study on
CQ Insurance (CQI). The design of Entity Relationship diagram has been done by making
certain assumptions as every element has not been clearly stated in the provided case study. The

3DATABASE DESIGN AND DEVELOPMENT
list of assumptions being taken into the consideration while designing the ERD are presented as
below:
 An individual person may hold various properties that may or may not contain
items.
 An individual person will be able to enroll in only one policy from the three types
of policies such as Home-buildings policy, Home-contents policy or combined
Home-buildings-contents policy.
 A property can be enrolled with only one policy by an individual customer.
 The contents of the properties may or may not be under insurance coverage as that
depends on the type of policy being chosen by the customer.
 Agents are involved for processing of claims being requested by the customers.
2.2 Business Rules
The ERD in context to the case study of CQ Insurance (CQI) has been designed from the
defined rules and procedures. The designing has been done by making various assumptions to
ensure appropriate reflection of the entities and attributes. The business rules that are essential in
context to the case study of CQ Insurance (CQI) are stated as below:
 The policies being enrolled by the customers will be stored using unique
identifiers by following specific set of rules and guidelines. The customers will be
able to view or make transactions on behalf of their policies using the unique
identifiers denoted as Pol_ID means Policy ID.
 The customer information is stored on behalf of each property being held by an
individual customer. Specific rules are set to check whether the home contents are
under the insurance coverage or not whenever a claim request is sent by customer.

4DATABASE DESIGN AND DEVELOPMENT
 The policy details of the customers will be stored in the company as per the
choice that has been made by the individual customer. The policy entity will
consist of policy type, date, duration such that the customers can easily view the
information using unique identifier.
 The information of agents will be stored with the help of using unique identifier
such that every details of them can be retrieved using that identification number
only.
3. Logical Design
The relations that has been drawn from the designed ERD in context to the case study of
CQ Insurance is presented as below:
Entity Relations
Customer Customer_ID (primary key), Customer_name, Customer_address,
Customer_email
Property Prop_ID (primary key), Prop_address, Prop_info, Customer_ID
foreignkey (Customer_ID) references Customer (Customer_ID)
Content Cont_ID (primary key), Cont_desc, Cont_value, Prop_ID
foreignkey (Prop_ID) references Property (Prop_ID)
Policy Pol_ID (primary key), Pol_type, Pol_details, Pol_date, Pol_duration
Home-buildings
policy
HBPol_ID (primary key), HBPol_details, HBPol_date, HBPol_duration
Home-contents
policy
HCPol_ID (primary key), HCPol_details, HCPol_date, HCPol_duration

5DATABASE DESIGN AND DEVELOPMENT
Home-buildings-
contents policy
HBCPol_ID (primary key), HBCPol_details, HBCPol_date,
HBCPol_duration
Policy Enrolment PEnrol_ID (primary key), Pol_ID (foreign key), Prop_ID (foreign key),
Cont_ID (foreign key), Customer_ID (foreign key), PEnrol_Date
foreignkey(Pol_ID) references Policy(Pol_ID)
foreignkey(Prop_ID) references Property(Prop_ID)
foreignkey(Cont_ID) references Content(Cont_ID)
foreignkey(Customer_ID) references Customer(Customer_ID)
Payment Pol_ID (foreign key), Claims_ID (foreign key), Amount
foreignkey(Pol_ID) references Policy(Pol_ID)
foreignkey(Claims_ID) references Claims(Claims_ID)
Claims Claims_ID (primary key), PEnrol_ID (foreign key), Customer_ID
(foreign key), Agnt_ID (foreign key), Claims_date
foreignkey(PEnrol_ID) references Policy Enrolment(PEnrol_ID)
foreignkey(Customer_ID) references Customer(Customer_ID)
foreignkey(Agnt_ID) references Agent(Agnt_ID)
Agent Agnt_ID (primary key), Agnt_name, Agnt_salary, Agnt_contNo,
Agnt_email

6DATABASE DESIGN AND DEVELOPMENT
Bibliography
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.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Foster, E.C. and Godbole, S., 2016. Database Modeling and Design. In Database Systems (pp.
101-137). Apress, Berkeley, CA.
Jukic, N., Vrbsky, S. and Nestorov, S., 2016. Database systems: Introduction to databases and
data warehouses. Prospect Press.
Klochkov, Y., Klochkova, E., Antipova, O., Kiyatkina, E., Vasilieva, I. and Knyazkina, E., 2016,
September. Model of database design in the conditions of limited resources. In Reliability,
Infocom Technologies and Optimization (Trends and Future Directions)(ICRITO), 2016 5th
International Conference on (pp. 64-66). IEEE.
Krishna, P.R., Khandekar, A. and Karlapalem, K., 2016. Modeling dynamic relationship types
for subsets of entity type instances and across entity types. Information Systems, 60, pp.114-126.
Letkowski, J., 2015. Doing database design with MySQL. Journal of Technology Research, 6,
p.1.
Mancas, C., 2016. Conceptual Data Modeling and Database Design: A Fully Algorithmic
Approach, Volume 1: The Shortest Advisable Path. CRC Press.