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

Figure 18Rejected_claim design view
Figure 19Settled_claim data view
Figure 20Settled_claim design view
12

Queries
Query:
Figure 21Query
Output:
Figure 22Output
13

Query:
Figure 23Query
Output:
Figure 24Output
Query:
14

Figure 25Query
Output:
Figure 26Output
15

Query:
Figure 27Query
Output:
Figure 28Output
Query:
Figure 29Query
16

Output:
Figure 30Output
Query:
Figure 31Query
Output:
Figure 32Output
17

Report
Figure 33 report
Report Implementation
Interesting things which I learned while doing the assignment
1)Through this assignment, I learned to use the access database properly and to implement
queries and report in the Access database. During the lectures, I was getting all the things
taught but was not perfect for the practical implementation of it.
I successfully learned the practical implementation in Access Database.
2)I learned the integrity constraints and also learned to implement the constraints in the
database. It was very interesting to learn integrity constraints and to implement them.
A requirement which was relatively difficult to solve but was completed
successfully.
I was getting the difficulty in applying the ‘group by’ clause in the queries. I was not properly
getting how to and where to implement the ‘group by’ clause. I saw various lectures and read
about the clause thoroughly and then implemented it. And finally, after trying a lot, I was able
to successfully implement the ‘group by’ clause.
18

Conclusion
This report concludes that all the database is designed and developed in a proper way as this
report also describing the vital database designs which are normalization, mapping of design
into relations, implementation of the database using MS-ACCESS application software and at
last all the forms, queries and reports are properly formulated. And, the points of this report
which are determined also tell about its bugs and errors and covered all the required points of
this database in this report.
19

References
 Create a simple select query 2018, [online] Support.office.com, Available at:
<https://support.office.com/en-us/article/create-a-simple-select-query-de8b1c8d-14e9-
4b25-8e22-70888d54de59> [Accessed 14 May 2018].
 Beeri, C., Bernstein, P.A. and Goodman, N., 1988. A sophisticate's introduction to
database normalization theory. In Readings in Artificial Intelligence and Databases (pp.
468-479).
 Elmasri, R. and Navathe, S., 2010. Fundamentals of database systems. Addison-Wesley
Publishing Company.
 Haverkamp, L.J., Appel, V. and Appel, S.H., 1995. Natural history of amyotrophic lateral
sclerosis in a database population Validation of a scoring system and a model for
survival prediction. Brain, 118(3), pp.707-719.
20