CQ Insurance Database: ER Diagram, Business Rules, Logical Design

This document presents a database design solution for CQ Insurance, a company offering home-building and home-content insurance policies to Australian residents. The design includes an Entity-Relationship (ER) diagram illustrating the relationships between key entities such as AGENT_DET, CUST_DET, PROP_DET, ACCI_REC, POLICY_DET, and PAYMENT_DET. The ER diagram is based on specific business rules and assumptions, such as one-to-many relationships between customers and their properties, accidents, and policies. The solution also provides a logical design outlining the tables, attributes, primary keys, and foreign key relationships needed to implement the database. The design ensures data integrity and efficient management of customer, policy, agent, property, and payment information for CQ Insurance. Desklib provides access to this and many other solved assignments.

Running head: DATABASE DESIGN
Database Design of CQ Insurance
Name of Student-
Name of University-
Author’s Note-

1Database Design
1. ER Diagram
Figure 1: Database Design of the company
2. Assumptions and Business Rules
Business Rules taken for the ERD:
Entities Relationship
AGENT_DET - CUST_AGENT One - one relationship
AGENT_DET - CUST_DET One - many relationship
CUST_DET - PROP_DET One - many relationship
CUST_DET - ACCI_REC One - many relationship
CUST_DET - POLICY_DET One - many relationship

2Database Design
CUST_DET - PAYMENT_DET One - many relationship
PROP_DET - POLICY_DET One - one relationship
ACCI_REC - POLICY_DET One - one relationship
POLICY_DET - PAYMENT_DET One - one relationship
Assumptions made for the ERD:
The CUST_AGENT has a connection with AGENT_DET entity, which has
AGENT_ID and C_ID as foreign key. The relation between AGENT_DET and the
CUST_AGENT has a one to one relationship. C_ADDRESS and C_PHONE are the multi-
valued attributes.
In PAYMENT_DET entity, the PAY_ID is primary key, and C_ID and POLICY_ID is
the foreign key.
In CUST_DET entity, C_ID is assumed as the primary key and POLICY_ID,
AGENT_ID, and ACCI_IDis assumed as a foreign key.
In PROP_DET, the C_ID is assumed as the foreign key, and the PROP_ID is assumed as
primary key.
In ACCI_REC entity, the ACCI_IDis primary key and C_ID is assumed as a foreign
key.
In AGENT_DET entity, AGENT_ID is assumed as a primary key. A_ADDRESS and
A_PHONE are the multi-valued attributes.

3Database Design
In POLICY_DET entity, the POLICY_ID is a primary key and ACCI_IDand PROP_ID
is foreign key.
3. Logical Design
CUST_AGENT (AGENT_ID, C_ID, CA_DET)
Foreign key (AGENT_ID) references AGENT_DET (AGENT_ID)
Foreign key (C_ID) references CUST_DET (C_ID)
PAYMENT_DET (PAY_ID, C_ID, POLICY_TYPE, POLICY_ID, NO_OF_YRS,
PREMIUM_PAY)
Foreign key (C_ID) references CUST_DET (C_ID)
Foreign key (POLICY_ID) references POLICY_DET (POLICY_ID)
CUST_DET (C_ID, C_FNAME, C_LNAME, C_PHONE, C_EMAIL, C_ADDRESS,
POLICY_ID, AGENT_ID, ACCI_ID)
Foreign key (POLICY_ID) references POLICY_DET (POLICY_ID)
Foreign key (AGENT_ID) references AGENT_DET (AGENT_ID)
Foreign key (ACCI_ID) references ACCI_REC (ACCI_ID)
PROP_DET (PROP_ID, PROP_NAME, TYPE_OF_PROP, PROP_DES, C_ID)
Foreign key (C_ID) references CUST_DET (C_ID)
ACCI_REC (ACCI_ID, C_ID, ACCI_TYPE, TIME_OF_OCCUR, POLICY_TYPE)

4Database Design
Foreign key (C_ID) references CUST_DET (C_ID)
AGENT_DET (AGENT_ID, A_NAME, A_ADDRESS, A_PHONE, A_EMAIL)
POLICY_DET (POLICY_ID, POLICY_TYPE, ACCI_ID, AMOUNT_PAID, PROP_ID,
POLICY_DES)
Foreign key (ACCI_ID) references ACCI_REC (ACCI_ID)
Foreign key (PROP_ID) references PROP_DET (PROP_ID)

5Database Design
Bibliography
Cagiltay, N. E., Ozcelik, E., & Ozcelik, N. S. (2015). The effect of competition on learning in
games. Computers & Education, 87, 35-41.
Guarino, N., & Guizzardi, G. (2015, June). “We need to discuss the Relationship”: Revisiting
Relationships as Modeling Constructs. In International Conference on Advanced
Information Systems Engineering (pp. 279-294). Springer, Cham.
Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to databases and
data warehouses. Prospect Press.
Roiger, R. J. (2017). Data mining: a tutorial-based primer. CRC Press.