COIT20247 Assignment 2: Database Design, Queries and Report Generation

This report details the process of mapping entities from an ER diagram to relational database tables, focusing on normalization principles. It covers the design and development of a database using MS Access, including screenshots of table designs, SQL queries, and datasheet views. Various use case scenarios are implemented, and the report includes examples of queries and report generation. The document also reflects on the challenges faced and lessons learned during the assignment, such as implementing integrity constraints and mastering the 'group by' clause in SQL queries, concluding with a summary of the database design, implementation, and the formulation of queries and reports.

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

Figure 2Assessment_report Design view
Figure 3Accessor data view
Figure 4Accessor design view
6

Figure 5Claim data view
7

Figure 6Claim design view
Figure 7Content_item data view
Figure 8Content_item design view
8

Figure 9Home_building data view
Figure 10Home_building design view
Figure 11Home_content data view
9

Figure 12Home_content design view
Figure 13Person data view
Figure 14Person design view
10

Figure 15Policy data view
Figure 16Policy design view
Figure 17Rejected_claim data view
11