COIT20247 Assignment 2: Relational Database Design and Implementation

COIT20247
ASSIGNMENT 2

Contents
Introduction.................................................................................................................................................4
Normalization..............................................................................................................................................5
Relational Database Implementation..........................................................................................................6
Database creation....................................................................................................................................6
Queries..................................................................................................................................................13
Report....................................................................................................................................................18
Report Implementation.............................................................................................................................18
Interesting things which I learnt while doing the assignment...............................................................18
Requirement which was relatively difficult to solve but was completed successfully...........................18
Conclusion.................................................................................................................................................18
1

List of figures
Figure 1 Assessment_report data view.......................................................................................................5
Figure 2 Assessment_report Design view....................................................................................................5
Figure 3 Accessor data view........................................................................................................................6
Figure 4 Accessor design view.....................................................................................................................6
Figure 5 Claim data view.............................................................................................................................6
Figure 6 Claim design view..........................................................................................................................7
Figure 7 Content_item data view................................................................................................................7
Figure 8 Content_item design view.............................................................................................................7
Figure 9 Home_building data view..............................................................................................................8
Figure 10 Home_building design view.........................................................................................................8
Figure 11 Home_content data view............................................................................................................8
Figure 12 Home_content design view.........................................................................................................9
Figure 13 Person data view.........................................................................................................................9
Figure 14 Person design view......................................................................................................................9
Figure 15 Policy data view.........................................................................................................................10
Figure 16 Policy design view......................................................................................................................10
Figure 17 Rejected_claim data view..........................................................................................................10
Figure 18 Rejected_claim design view.......................................................................................................11
Figure 19 Settled_claim data view.............................................................................................................11
Figure 20 Settled_claim design view..........................................................................................................11
Figure 21 Query.........................................................................................................................................12
Figure 22 Output.......................................................................................................................................12
Figure 23 Query.........................................................................................................................................13
Figure 24 Output.......................................................................................................................................13
Figure 25 Query.........................................................................................................................................14
Figure 26 Output.......................................................................................................................................14
Figure 27 Query.........................................................................................................................................15
Figure 28 Output.......................................................................................................................................15
Figure 29 Query.........................................................................................................................................15
Figure 30 Output.......................................................................................................................................16
Figure 31 Query.........................................................................................................................................16
Figure 32 Output.......................................................................................................................................16
Figure 33 report.........................................................................................................................................17
2

Introduction
The report introduces the mapping of various entities which are used in ER diagram to
implement the relations in the database. This file report also introduces the normalization of
relation tables and their data information like attributes and entities. The report performed
various use case scenarios in design part and provides all screenshots of designing and
developing of database design like tables of view datasheet, SQL designs and datasheet view of
queries and also provides some screenshots of forms and reports. In this report, will also shows
the implementation of database report which will helpful for users to discuss regarding all the
issues and bugs occurs while executing or implementing the program file.
3

Normalization
Assessment_report(report_id, assessor_id, report_date, initial_recommendation, report_type)
Assessor(assessor_id, qualification_name, date_qualified, person_id)
Claim(claim_id, lodge_date, claim_status, item_name, quantity, item_status, person_id,
policy_id)
Content_item(content_item_id, home_content_id, item_name, manufacturer, model,
purchase_price, quantity, claimed_status)
Home_building(home_building_id, policy_id, house_type_year_build, has_alarm_fitted,
has_window_locks, insured_amount, building_premium_amount)
Home_content(home_content_id, policy_id, content_preminum_amount)
Person(person_id, person_name, street, city, post_code, person_type)
Policy(policy_id, effective_from_date, premium_amount, paid_date, policy_type,
total_policy_amount)
Rejected_claim(rejected_claim_id, claim_id, rejected_date, rejected_reason)
Settled_claim(settled_claim_id, claim_id, settled_date, amount_settled)
Assessor(assessor_id, qualification_name, date_qualified, person_id)
Functional Dependency :
accessor_id -> person_id
The relation meets the 3rd normal form (3NF). Here the accessor_id is the primary key of the
table accessor. The accessor_id has a foreign key named person_id which is referenced from
the table person. In this relation, an accessor_id will have its own qualification and date on
which he is qualified.
4

Claim(claim_id, lodge_date, claim_status, item_name, quantity, item_status, person_id)
Functional Dependency:
Claim_id -> person_id
The relation meets the 3rd normal form (3NF). Here the claim_id is the primary key of the table
claim. The claim table has a foreign key named person_id which is referenced from the table
person. In this relation, claim_id is mapped with the person_id and the item on which the claim
is done by the person. Each claim_id will be unique and will contain the information that which
person needs the claim and on which item.
Relational Database Implementation
Database creation
Figure 1 Assessment_report data view
5