COIT20247 Assessment 2: Database Design, SQL Queries, and Reports

This assignment solution for COIT20247, covers the design and implementation of a database system. It begins with an Entity-Relationship Diagram (ERD) that visually represents the database structure, followed by the relational schema defining tables, attributes, and primary/foreign keys. The solution then addresses normalization, ensuring data integrity and minimizing redundancy through functional dependency analysis. DDL (Data Definition Language) statements are provided to create the database tables, while DML (Data Manipulation Language) statements demonstrate data insertion and retrieval. The assignment includes SQL queries to extract specific information and create reports. An implementation report details the learning process, challenges, and design choices. References to relevant resources are also included. The database design incorporates integrity constraints to enforce data accuracy and consistency, such as validation rules, data types, and required fields. The solution demonstrates a practical understanding of database design principles, SQL implementation, and report generation.

Assessment 2
COIT20247
Student Name
Student ID

Contents
Sl. No Title Page No.
1 ERD 2
2 Relational Schema & Integrity Constraints 3
3 Normalization (Functional Dependency) 11
4 DDL and DML Statements 13
5 Query and Report 21
6 Implementation Report 23
References 24
1

ERD
2

Relational Schema
Location (Location_Id, Street, City, Post_Code)
Primary Key (Location_Id)
Person_Type (Person_Type_Id, Person_Type_Name)
Primary Key (Person_Type_Id)
Qualification (Qualification_Id, Qualification_Name)
Primary Key (Qualification_Id)
Report_Type (Report_Type_Id, Report_Type_Name)
Primary Key (Report_Type_Id)
Person (Person_Id, Person_Name, Location_Id, Person_Type_Id)
Primary Key (Person_Id),
Foreign Key (Location_Id) References Location (Location_Id),
Foreign Key (Person_Type_Id) References Person_Type (Person_Type_Id)
Assessor (Person_Id)
Primary Key (Person_Id),
Foreign Key (Person_Id) References Person (Person_Id)
Ass_Qualification (Person_Id, Qualification_Id, Date_Qualified)
Primary Key (Person_Id, Qualification_Id),
Foreign Key (Person_Id) References Person (Person_Id),
Foreign Key (Qualification_Id) References Qualification (Qualification_Id)
3

Property (Property_Id, Location_Id, Person_Id)
Primary Key (Property_Id),
Foreign Key (Location_Id) References Location (Location_Id),
Foreign Key (Person_Id) References Person (Person_Id)
Policy (Policy_Id, Effective_From_Date, Premium_Amount, Paid_Date, Is_Home_BuildingPolicy,
Is_Home_ContentsPolicy, Property_Id)
Primary Key (Person_Id),
Foreign Key (Property_Id) References Property (Property_Id)
Home_Content (Policy_Id, Content_Premimum_Amount)
Primary Key (Policy_Id),
Foreign Key (Policy_Id) References Policy (Policy_Id)
House_Type (House_Type_Id, House_Type_Name)
Primary Key (House_Type_Id)
Home_Building (Policy_Id, House_Type_Id, Year_Built, Is_Alarm_Fitted, Has_Window_Locks,
Issued_Amount, Building_Premium_Amount)
Primary Key (Policy_Id),
Foreign Key (Policy_Id) References Policy (Policy_Id),
Foreign Key (House_Type_Id) References House_Type (House_Type_Id)
Policy_Status (Policy_Status_Id, Policy_Status_Name)
Primary Key (Policy_Status_Id)
4

Policy_Claim (Claim_Id, Lodged_Date, Policy_Status_Id, Policy_Id)
Primary Key (Claim_Id),
Foreign Key (Policy_Id) References Policy (Policy_Id),
Foreign Key (Policy_Status_Id) References Policy_Status (Policy_Status_Id)
Settled_Claim(Claim_Id, Settled_Date, Amount_Settled)
Primary Key (Claim_Id),
Foreign Key (Claim_Id) References Policy_Claim (Claim_Id)
Rejected_Claim (Claim_Id, Rejected_Date, Rejected_Reason)
Primary Key (Claim_Id),
Foreign Key (Claim_Id) References Policy_Claim (Claim_Id)
Content_Item (Item_Id, Item_Name, Manufacturer, Model, Purchase_Date, Quantity,
Claimed_Status, Policy_Id)
Primary Key (Item_Id),
Foreign Key (Policy_Id) References Home_Content (Policy_Id)
Claim_Item (Claim_Id, Item_Id, Quantity, Item_Status)
Primary Key (Claim_Id, Item_Id),
Foreign Key (Claim_Id) References Policy_Claim (Claim_Id),
Foreign Key (Item_Id) References Content_Item (Item_Id)
Assessment_Report (Report_Id, Report_Date, Initial_Recommendation, Person_Id, Claim_Id,
Report_Type_Id)
Primary Key (Report_Id),
Foreign Key (Person_Id) References Assessor (Person_Id),
Foreign Key (Claim_Id) References Policy_Claim (Claim_Id),
Foreign Key (Report_Type_Id) References Report_Type (Report_Type_Id)
5

Final_Assessment_Report (Report_Id, Final_Assessment_Date, Total_Amount,
Cost_Report_Demo)
Primary Key (Report_Id),
Foreign Key (Report_Id) References Assessment_Report (Report_Id)
6

Integrity Constraints
Insured amount should be positive currency and expressed in 2 decimals only
Set Validation Rule to >=0 and Validation Rule to Premium Amount Should be greater than or
equal to zero and Data Type to Currency and Decimal Places to 2
7

Policy date should have appropriate date value
Set the format to Short Date
8

Name of persons such as customer and assessor should not be null
Set Required to Yes and Allow zero length to No
9

Each policy should be related to one property/home
From ERD, Each policy belong to any one of the property
10

Functional Dependencies
Relationship 1
INF
In Policy table,
Policy (Policy_Id, Effective_From_Date, Premium_Amount, Paid_Date, Is_Home_BuildingPolicy,
Is_Home_ContentsPolicy, Property_Id, Content_Premium_Amount)
Policy_Id is the primary key. It is used to prevent the repeated group of data in the table
2NF
Remove partial dependency
In Policy table, Property_Id is the foreign key. It avoids partial dependencies in the table
3NF
Removed transitive dependency
Policy (Policy_Id, Effective_From_Date, Premium_Amount, Paid_Date,
Is_Home_BuildingPolicy, Is_Home_ContentsPolicy, Property_Id)
Home_Content (Policy_Id, Content_Premium_Amount)
11