CQ Insurance Database: ER Diagram, Logical Design, and 3NF Relations

This document presents a database design solution for CQ Insurance, focusing on creating an Entity-Relationship (ER) diagram and performing logical design. The ER diagram illustrates entities such as AGENT_INFORMATION, CUSTOM_AGENT, CUSTOM_INFORMATION, PRO_INFO, ACDNT_INFORMATION, PLCY_INFORMATION, and PAYMENT_INFORMATION, along with their relationships and attributes. The assumptions and business rules that govern the ER diagram, including relationship cardinalities and foreign key constraints, are explicitly stated. The logical design translates the ER diagram into a relational schema, defining tables, primary keys, foreign keys, and data types for each entity. The document also includes a bibliography of cited works, providing a comprehensive overview of the database design process for the given insurance case study, available on Desklib for students.

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

1DATABASE DESIGN
1. Entity-Relationship Diagram
Figure 1: Entity-Relationship Diagram

2DATABASE DESIGN
2. Assumptions and Business Rules
Business Rules that are taken for the Entity-Relationship Diagram:
Entities Relationship
AGENT_INFORMATION- CUSTOM_AGENT: One - one relationship
AGENT_INFORMATION- CUSTOM_INFORMATION: One - many relationship
CUSTOM_INFORMATION - PRO_INFO: One - many relationship
CUSTOM_INFORMATION - ACDNT_INFORMATION: One - many relationship
CUSTOM_INFORMATION - PLCY_INFORMATIONRMATION: One - many relationship
CUSTOM_INFORMATION - PAYMENT_INFORMATION: One - many relationship
PRP_INFORMATION- PLCY_INFORMATION: One - one relationship
ACDNT_INFORMATION - PLCY_INFORMATION: One - one relationship
PLCY_INFORMATION - PAYMENT_INFORMATION: One - one relationship
3. Assumptions made for the ERD:
The CUSTOM_AGENT has a relation with AGENT_INFORMATION entity. In this
relation, A_ID and C_ID are the foreign keys. The relation between AGENT_INFORMATION
and the CUSTOM_AGENT has a 1:1 relationship. C_ADDRESS and C_PHNUM are the multi-
valued attributes. The CUSTOM_AGENT is a weak entity.

3DATABASE DESIGN
In PAYMENT_INFORMATION entity, the PAYMENT_ID is primary key, and C_ID
and PLCY_ID is the foreign key.
In CUSTOM_INFORMATION entity, C_ID is taken as the primary key and PLCY_ID,
A_ID, and ACDNT_ID is taken as a foreign key.
In PRO_INFO, the C_ID is taken as the foreign key, and the PRP_ID is taken as primary
key.
In ACDNT_INFORMATION entity, the ACDNT_ID is the primary key and C_ID is
taken as a foreign key.
In AGENT_INFORMATION entity, A_ID is taken as a primary key. A_ADDRESS and
A_PHNUM are the multi-valued attributes.
In PLCY_INFORMATION entity, the PLCY_ID is a primary key and ACDNT_ID and
PRP_ID is foreign key.
4. Logical Design
CUSTOM_AGENT(A_ID, C_ID, CA_INFORMATION)
Foreign key (A_ID) references AGENT_INFORMATION(A_ID)
Foreign key (C_ID) references CUSTOM_INFORMATION (C_ID)
PAYMENT_INFORMATION (PAYMENT_ID, C_ID, PLCY_TYPE, PLCY_ID,
NUM_OF_YRS, PRE_PAY)
Foreign key (C_ID) references CUSTOM_INFORMATION (C_ID)

4DATABASE DESIGN
Foreign key (PLCY_ID) references PLCY_INFORMATION (PLCY_ID)
CUSTOM_INFORMATION (C_ID, C_FNAME, C_LNAME, C_PHNUM, C_EMAILID,
C_ADDRESS, PLCY_ID, A_ID, ACDNT_ID)
Foreign key (PLCY_ID) references PLCY_INFORMATION (PLCY_ID)
Foreign key (A_ID) references AGENT_INFORMATION(A_ID)
Foreign key (ACDNT_ID) references ACDNT_INFORMATION (ACDNT_ID)
PRP_INFORMATION(PRP_ID, PRP_NAME, TYPE_OF_PRP, PRP_INFORMATION, C_ID)
Foreign key (C_ID) references CUSTOM_INFORMATION (C_ID)
ACDNT_INFORMATION (ACDNT_ID, C_ID, ACDNT_TYPE, OCCURANCE_TIME,
PLCY_TYPE)
Foreign key (C_ID) references CUSTOM_INFORMATION (C_ID)
AGENT_INFORMATION(A_ID, A_NAME, A_ADDRESS, A_PHNUM, A_EMAIL)
PLCY_INFORMATION (PLCY_ID, PLCY_TYPE, ACDNT_ID, AMT_PAID, PRP_ID,
PLCY_INFORMATION)
Foreign key (ACDNT_ID) references ACDNT_INFORMATION (ACDNT_ID)
Foreign key (PRP_ID) references PRP_INFORMATION(PRP_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.
Calvanese, D., Montali, M., Patrizi, F., & Rivkin, A. (2015). Implementing data-centric dynamic
systems over a relational DBMS. In Alberto Mendelzon International Workshop on
Foundations of Data Management (p. 209).
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.
Mariotti, M., Gervasi, O., Vella, F., Cuzzocrea, A., & Costantini, A. (2017). Strategies and
systems towards grids and clouds integration: a DBMS-based solution. Future
Generation Computer Systems.