Database Development Management 7 - Assignment 1 Report on ER Diagram

This report details the design of a database for an insurance company, beginning with an Entity-Relationship (ER) diagram illustrating entities like customers, agents, policies, claims, and properties. It outlines key assumptions, such as customers owning multiple properties and enrolling in various policies, which inform the development of business rules. These rules govern data integrity and relationships within the database. The report then progresses to the logical design, ensuring the database adheres to 3rd Normal Form (3NF), and presents the database schema, specifying attributes and primary/foreign keys for each entity. The ER diagram uses dashed lines to show weak entities and red lines for multi-valued attributes, providing a clear visual representation of the database structure. Finally, the report includes a bibliography of relevant database design resources.

Running head: DATABASE DEVELOPMENT MANAGEMENT
Assessment item 1—Assignment 1
Name of the Student
Student number
Unit number
Name of the University
Author’s Note

1
DATABASE DEVELOPMENT MANAGEMENT
Table of Contents
1. ER Diagram.....................................................................................................................2
2. Assumptions and the Business Rules..............................................................................3
3. Development of the Logical Design................................................................................5
Bibliography........................................................................................................................7

2
DATABASE DEVELOPMENT MANAGEMENT
1. ER Diagram
The weak entity is shown using the dashed lines and the multivalued attributes are shown
in red colour. The attributes of the tables are given below:
Entity Attributes
Customer Customer_ID (primary key),
Customer_Name, Street, Suburb, State,
postcode, Email
Agent Agent_ID (Primary key), Name, Email,
Contact_Number, Salary
Properties Property_ID (Primary key), Customer_ID

3
DATABASE DEVELOPMENT MANAGEMENT
(Foreign key), Details, Location
Claims Insurance_Claim_ID (Primary key),
Policy_ID (Foreign key), Customer_ID
(Foreign key), Agent_ID (Foreign key),
date_claimed
Home_Item Item_ID (primary key), Property_ID (Foreign
key), Description, Market_Value
Enrolled_policy Policy_ID (primary key), Customer_ID
(Foreign key), Policy_Number (Foreign key),
Item_ID (Foreign key), Property_ID (Foreign
key), Date of policy enrolled
Policy Policy_number (primary key), Duration,
Start_Date, End_Date, Details
The dashed lines are used for the demonstration of the relation between the weak entities
and the red lines are used for the demonstration of the multivalued attributes.
2. Assumptions and the Business Rules
The assumption is important for designing the database and it helps the development
team to create a relationship between the constraints and the data models .The business process
can also be used for the identification of the scope and role of the project. The assumptions that
are made for the development of the entity relationship diagram are listed below:

4
DATABASE DEVELOPMENT MANAGEMENT
 A customer may have more than one property and it may contain different items
eligible for insurance
 The customer needs to enroll the with the different set of policy for claiming the
insurance
 There may be more than one agents working for the insurance company and
assisting the customer for handling the policies and get the claim amount
 An agent is capable of handling more than one number of policy and meet the
client’s needs
Business Rules
The entity relationship diagram is developed following the set of business rules of the
insurance company. Some of the constraints are assumed during the development of the entity
relationship diagram and the assumption can cause the business rules to be changed at a minor
level and the new business rules are created as follows:
 A set of policy would be maintained for the CQ insurance recording all the details
of the insurance policy that is required to be stored in the information system
along with the policy number as the primary key. The policy number would be
used for the management of the policy.
 The details of the customers are recorded with all the details such as their name,
address, phone number and the details of their properties. Two different tables are
used for recording the details of the properties and the items.
 The enrollment details of the policy and stored in the information system that can
be used during the claiming of the policy. The customer ID, policy number and

5
DATABASE DEVELOPMENT MANAGEMENT
the other details are used as a foreign key to find all the details of the enrollment
with the enrollment ID.
 The details of the agent should be stored in the database of the information system
such that the details of the agent can be obtained from the information system and
maintain the records easily.
3. Development of the Logical Design
The entity relationship model is created including the following set of attributes and
entities and they are in the 3NF. The following list contains the entity set contained in the ER
diagram.
 Properties
 Customer
 Home Item
 Claims ‘
 Agents
 Policy, and
 Enrolled Policy
The database schema for the entity relationship diagram is as follows:
Properties (Property_ID [PK], Customer_ID [FK], Details, Location)
Customer (Customer_ID [PK], Customer_Name, Street, Suburb, State, Postcode,
Email)
Home Item (Item_ID [PK], Property_ID [FK], Description, Market_Value)
Policy (Policy_number [PK], Duration, Start_Date, End_Date, Details)

6
DATABASE DEVELOPMENT MANAGEMENT
Enrolled Policy (Policy_ID [PK], Customer_ID [FK], Policy_Number[FK],
Item_ID [FK], Property_ID [FK], Date of policy enrolled)
Claims (Insurance_Claim_ID [PK], Policy_ID [FK], Customer_ID [FK],
Agent_ID [FK], Date_Claimed)
Agent (Agent_ID [PK], Name, Email, Contact Number, Salary)