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

This article presents a case study of CQ Insurance (CQI) and discusses the database design and development process. It includes an entity relationship diagram, assumptions, business rules, and logical design. The article is relevant to students studying database design and development, and professionals working in the insurance industry.

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 (ER) diagram...............................................................................................2
2. Assumptions and Business rules..................................................................................................2
2.1 Assumptions..........................................................................................................................2
2.2 Business Rules.......................................................................................................................3
3. Logical Design.............................................................................................................................3
Bibliography....................................................................................................................................5

2DATABASE DESIGN AND DEVELOPMENT
1. Entity Relationship (ER) diagram
Figure 1: Entity Relationship (ER) diagram of CQ Insurance (CQI) case study
(Source: Created by Author)
2. Assumptions and Business rules
2.1 Assumptions
The ERD in context to the case of CQ Insurance (CQI) has been designed by making the
assumptions as listed below:
 Several properties may be owned by an individual customer and a single property
may consist of various contents.

3DATABASE DESIGN AND DEVELOPMENT
 An individual customer may enroll for only one policy either for property,
contents or both property and contents combined policy. This means that a
customer will be able to hold only a single policy for each of their property.
 Agents are involved with the organization for management of policy as well as
claims requested by customers.
2.2 Business Rules
The business rules that are relevant to the designed ERD for CQ Insurance (CQI) are
presented as below:
 The record of customers and their related properties will be stored individually in
the system.
 Unique numbers will be created for identification of the various policies being
enrolled by the customers.
 Every policy have to be enrolled only after association of an agent working with
the association.
3. Logical Design
The designed Entity Relationship diagram (ERD) is mapped into relations as below:
Customer (Cust_no (primary key), Cust_name, Cust_address, Cust_email)
Property (Prop_no (primary key), Prop_address, Prop_notes, Cust_no (foreign key))
Contents (Content_ID (primary key), Content_description, Content_marketvalue,
Prop_no (foreign key))
Policy (Policy_no (primary key), Policy_details, Policy_date, Policy_duration)

4DATABASE DESIGN AND DEVELOPMENT
Policy Enrolment (PolEnrolment_ID (primary key), Policy_no (foreign key), Prop_no
(foreign key), Content_ID (foreign key), Cust_no (foreign key), PolEnrolment_date)
Claim (Claim_ID (primary key), Claim_date, Agent_ID (foreign key), PolEnrolment_ID
(foreign key), Cust_no (foreign key))
Agent (Agent_ID (primary key), Agent_name, Agent_salary, Agent_contactNo,
Agent_mail)

5DATABASE DESIGN AND DEVELOPMENT
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Mitrovic, A. and Suraweera, P., 2016. Teaching database design with constraint-based
tutors. International Journal of Artificial Intelligence in Education, 26(1), pp.448-456.