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

This assignment presents a database design and development solution for CQ Insurance (CQI). It includes an Entity Relationship (ER) diagram illustrating the relationships between customers, properties, contents, policies, and agents. The solution outlines key assumptions, such as customers owning multiple properties and enrolling in single policies, and business rules, including storing customer and property records separately and assigning unique policy numbers. The ER diagram is then mapped into a logical design, defining relations for Customer, Property, Contents, Policy, Policy Enrolment, Claim, and Agent, with primary and foreign keys specified to ensure data integrity. This detailed design provides a foundation for implementing a robust database system for CQ Insurance (CQI).

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.