CQ Insurance Database Design: ER Diagram, Business Rules, and Schema

This document presents a database design project for CQ Insurance, encompassing an Entity-Relationship (ER) diagram and a logical schema. The ER diagram illustrates the relationships between entities such as agentsDetails, customerDetails, accidentsRecord, policyDetails, propertyDetails, and paymentDetails. The design incorporates business rules that define the relationships between these entities, such as one-to-many relationships between customerDetails and related entities like propertyDetails and accidentsRecord. Assumptions are made regarding primary and foreign keys within each entity. The logical design outlines the structure of the database tables, specifying attributes, primary keys, and foreign key relationships to ensure data integrity and efficient data management. This project provides a comprehensive overview of the database structure required to manage CQ Insurance's data effectively. Desklib offers a variety of resources, including past papers and solved assignments, to aid students in their studies.

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

Running head: Database Design of CQ Insurance
1. ER Diagram
Figure 1: Database Design of CQ Insurance
2. Assumptions and Business Rules
Business Rules taken for the ERD:
Entities Relationship
agentsDetails - customerAgent One - one relationship
agentsDetails - customerDetails One - many relationship
customerDetails - propertyDetails One - many relationship
customerDetails - accidentsRecord One - many relationship
customerDetails - policyDetails One - many relationship
customerDetails - paymentDetails One - many relationship

Running head: Database Design of CQ Insurance
propertyDetails - policyDetails One - one relationship
accidentsRecord - policyDetails One - one relationship
policyDetails - paymentDetails 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:
In agentsDetails entity, agentID is assumed as a primary key.
The customerAgent has a connection with agentsDetails entity, which has agentID
and customerID as foreign key. The relation between agentsDetails and the customerAgent
has a one to one relationship.
In customerDetails entity, customerID is assumed as the primary key and policyID,
agentID, and accidentID is assumed as a foreign key.
In accidentsRecord entity, the accidentID is primary key and customerID is assumed
as a foreign key.
In policyDetails entity, the policyID is a primary key and accidentID and propertyID
is foreign key.
In propertyDetails, the customerID is assumed as the foreign key, and the propertyID
is assumed as primary key.
In paymentDetails entity, the Payment_ID is primary key, and customerID and
policyID is the foreign key.

Running head: Database Design of CQ Insurance
3. Logical Design
customerDetails (customerID, customerFName, customerLName, customerPhone,
customerEmailID, customerAddress, policyID, agentID, accidentID )
Foreign key (policyID) references policyDetails (policyID)
Foreign key (agentID) references agentsDetails (agentID)
Foreign key (accidentID) references accidentsRecord (accidentID)
agentsDetails (agentID, agentName, agentAddress, agentPhone, agentEmailID)
customerAgent (agentID, customerID, caDetails)
Foreign key (agentID) references agentsDetails (agentID)
Foreign key (customerID) references customerDetails (customerID)
accidentsRecord (accidentID, customerID, accidentType, timeOfOccurance, policyType)
Foreign key (customerID) references customerDetails (customerID)
propertyDetails (propertyID, propertyName, typeOfProperty, propertyDescription,
customerID)
Foreign key (customerID) references customerDetails (customerID)
policyDetails (policyID, policyType, accidentID, amountToBePaid, propertyID,
policyDetails)
Foreign key (accidentID) references accidentsRecord (accidentID)
Foreign key (propertyID) references propertyDetails (propertyID)

Running head: Database Design of CQ Insurance
paymentDetails (Payment_ID, customerID, policyType, policyID, numberOfYears,
premiumPayment)
Foreign key (customerID) references customerDetails (customerID)
Foreign key (policyID) references policyDetails (policyID)

Running head: Database Design of CQ Insurance
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.