CQI Database Design and Development: ER Diagram and Normalization

This report details the database design and development for Central Queensland Insurance (CQI), focusing on creating a well-structured relational database. It begins with an ER diagram illustrating the entities and relationships within the CQI system, followed by key assumptions made during the design process, such as customer registration for property ownership, assessor evaluation for claims, and variable pricing based on property type and value. The report then outlines the business rules implemented, including unique identifiers for entities, assessor access to customer details, policy claim validity criteria, and data storage considerations. The relational design section defines the tables, attributes, and primary/foreign key relationships for entities like Customer, Policies, Policy Registration, Property, Assessor, and Policy Claims. The report concludes with a bibliography of cited works. Desklib offers a wealth of similar solved assignments and past papers to aid students in their studies.

Running head: DATABASE DESIGN AND DEVELOPMENT
Database Design and Development
Assignment 1: CQI (Central Queensland Insurance)
Name of Student
Name of the University
Author Note

DATABASE DESIGN AND DEVELOPMENT
ER Diagram
Figure 1: ER Diagram (CQ Insurance)
(Source: Created by Author)

DATABASE DESIGN AND DEVELOPMENT
Assumptions
Following assumptions have been assumed for this database design:
 The customer will have to register for the registration of the property, he or she owns and
they will have to provide personal information
 Individuals can own more than one property and each property will be recognized by
customer ID
 The customer will have to contact assessor for the claim and after evaluation only, the
customer could get his or her claim
 Price will be variable for different types of properties based on the initial value or current
market value
 Registration duration will be for an year, not more than that nor less than that value
 Properties details and value will be incorporated within the registration policy
 The Assessor will evaluate the policy claim, the claim failing the date of validity for the
claim will be considered as an invalid claim, and customer will be responded through a
rejection letter
 The properties would be owned by the owners and the items of the properties are stored
in a single table in the database and the valuation of the properties are to be done
according to the location of the property.
 The policy claims and the policy enrollment are stored in different entities in the
database. The policy claims is derived from the policy enrollment table

DATABASE DESIGN AND DEVELOPMENT
Business rules
Following are the set of new business rules after the implementation of assumptions:
 Every detail will be saved in the database under unique identities for the property,
customer details, policies, claims, and assessor
 Assessor can access the details and registration made by the consumer through the unique
customer ID.
 Policy can be claimed only if the validity date is less than a year and it should must pass
the criteria for claiming the value of the property
 Proper and feasible policies will be implemented for the registration of the property in
which the details of the property along with the price must be known
 Customer details and policy details will be saved in a virtual memory I manner to allow
the easy access to the database
 Unique policy registration number will be issued to every customer
 Assessors approval will be the primary statement for providing claims
Relational Design
Customer (Customer_ID (primary key), Cust_name, Cust_Address, Cust_Email,
Cust_ContactNo, Cust_Type)
Sub Attributes
Cust_name (First_name, Last_name)
Cust_Address (City, State, Pincode)
Cust_Type (Regular, Special)

DATABASE DESIGN AND DEVELOPMENT
Policies (Policy_ID (primary key), Property_type, Registration_date, Validity_Duration,
Property_Value)
Sub Attributes
Property_Type (Home_contents, Home_building, Home_Contents& Prop)
Policy Registration (Reg_Id, item_id, Policy_date, Property_id, Customer_id (forgien key),
Policy_id (forgien key))
Property (Property_ID (primary key), Policy_ID (forgien key), Customer_ID (forgien key),
PropertyLocation, PropertyType)
Sub Attribute
Property_Type (Home_contents, Home_building, Home_Contents&Prop, Market_value)
Assessor (Assessor_id (primary key), Customer_ID (forgien key), Net_Salary, Agent_Name,
Contact_Details, agent_Mail)
Sub Attribute
Agent_name (First_name, Last_name)
Policy Claims (Calims_Id (primary key), Policy_ID (forgien key), Customer_ID (forgien key),
Assessor_ID (forgien key), claim_Date)

DATABASE DESIGN AND DEVELOPMENT
Bibliography
Bohe, A., Cervini, G., & Zobi, G. (2017). U.S. Patent No. 9,536,002. Washington, DC: U.S.
Patent and Trademark Office.
Wang, W., Indulska, M., Sadiq, S., & Weber, B. (2017, September). Effect of Linked Rules on
Business Process Model Understanding. In International Conference on Business
Process Management (pp. 200-215). Springer, Cham.