CQ Insurance Database ER Diagram and Logical Design - Week 5

This project provides a comprehensive solution for designing a database for CQ Insurance, an Australian company offering home building and contents insurance. The solution includes an Entity Relationship (ER) diagram illustrating the relationships between key entities such as agents, customers, properties, accidents, policies, and payments. Business rules and assumptions are clearly stated to define the constraints and characteristics of the data model. The logical design translates the ER diagram into a relational schema, defining tables with primary and foreign keys to ensure data integrity and enforce relationships between entities. The project includes defining the tables for customer details, agent details, customer agent relationships, accident records, property details, policy details, and payment details. This assignment showcases a structured approach to database design, from conceptual modeling to logical implementation, providing a solid foundation for building a robust and efficient insurance database.

Running head: CQ INSURANCE DATABASE DIAGRAM
CQ Insurance Database Diagram
Name of Student-
Name of University-
Author’s Note-

1CQ INSURANCE DATABASE DIAGRAM
1. ER Diagram
Figure 1: Data Relationship Diagram of CQI
2. Business Rules and Assumptions
Business Rules taken for the ERD:
Entities Relationship
agents_details - customer_agent One - one relationship
agents_details - customer_details One - many relationship
customer_details - property_details One - many relationship

2CQ INSURANCE DATABASE DIAGRAM
customer_details - accidents_record One - many relationship
customer_details - policy_details One - many relationship
customer_details - payment_details One - many relationship
property_details - policy_details One - one relationship
accidents_record - policy_details One - one relationship
policy_details - payment_details One - one relationship
Assumptions made for the ERD:
All total there are seven entities in the ER Diagram. The assumptions that are taken for
the ERD are:
agents_details entity: agent_ID is assumed as a primary key.
The customer_agent has a connection with agents_details entity, which has agent_ID
and customer_ID as foreign key. The relation between agents_details and the customer_agent
has a one to one relationship. The customer_agent is a subtype entity.
In customer_details entity, customer_ID is assumed as the primary key and policy_ID,
agent_ID, and accident_ID is assumed as a foreign key. The customer_details is a super entity as
it has relationship with many sub entities.
In accidents_record entity, the accident_ID is primary key and customer_ID is assumed
as a foreign key.

3CQ INSURANCE DATABASE DIAGRAM
In policy_details entity, the policy_ID is a primary key and accident_ID and property_ID
is foreign key.
In property_details, the customer_ID is assumed as the foreign key, and the property_ID
is assumed as primary key.
In payment_details entity, the Payment_ID is primary key, and customer_ID and
policy_ID is the foreign key.
3. Logical Design
customer_details (customer_ID, customer_fname, customer_lname, customer_phone,
customer_email, customer_address, policy_ID, agent_ID, accident_ID )
Foreign key (policy_ID) references policy_details (policy_ID)
Foreign key (agent_ID) references agents_details (agent_ID)
Foreign key (accident_ID) references accidents_record (accident_ID)
agents_details (agent_ID, agent_name, agent_address, agent_phone, agent_email)
customer_agent (agent_ID, customer_ID, ca_description)
Foreign key (agent_ID) references agents_details (agent_ID)
Foreign key (customer_ID) references customer_details (customer_ID)
accidents_record (accident_ID, customer_ID, accident_type, time_of_occurance, policy_type)
Foreign key (customer_ID) references customer_details (customer_ID)

4CQ INSURANCE DATABASE DIAGRAM
property_details (property_ID, property_name, type_of_property, property_description,
customer_ID)
Foreign key (customer_ID) references customer_details (customer_ID)
policy_details (policy_ID, policy_type, accident_ID, amount_to_be_paid, property_ID,
policy_description)
Foreign key (accident_ID) references accidents_record (accident_ID)
Foreign key (property_ID) references property_details (property_ID)
payment_details (Payment_ID, customer_ID, policy_type, policy_ID, number_of_years,
premium_payment)
Foreign key (customer_ID) references customer_details (customer_ID)
Foreign key (policy_ID) references policy_details (policy_ID)

5CQ INSURANCE DATABASE DIAGRAM
Bibliography
Cagiltay, N. E., Ozcelik, E., & Ozcelik, N. S. (2015). The effect of competition on learning in
games. Computers & Education, 87, 35-41.
Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to databases and
data warehouses. Prospect Press.
Muthalagu, I. (2016). PLM Data Structure and Modeling Techniques for Engineering
Applications.
Roiger, R. J. (2017). Data mining: a tutorial-based primer. CRC Press.