Assignment: Choosing, Adapting, and Testing a Relational Scheme

In this Assignment, you will

(A) Choose an existing database design, review it, and adapt it for your assignment

(B) Identify a report (standard query) that can be run against your database design

(C) Plan how to test your report and identify the necessary test data

 (D) Create a corresponding relational scheme, and verify it meets first, second and third normal forms

 (E) Implement your database scheme using MariaDB and load the test data

 (F) Then write, run and test the report you identified The task is broken down into two stages so that you can use any feedback from the first stage to help when completing the second.

• Select any suitable database design as a basis from those available at

o http://www.databaseanswers.org/data_models/

• You should modify the design as you deem appropriate to make it more appropriate to the coursework. Your design should contain at least 5 entities. (The number of tables may be higher as some tables represent m:n relationships and lookups, not only entities).

Each entity should normally have three or more attributes. Explain the reasons for any modifications you make to the original schema.

• Create an annotated ER diagram for the schema; you may use draw.io or any suitable tool to author the diagram.

• Propose for a realistic report1 that might be performed on your selected database schema. Note that no SQL is required at this stage. However, when implemented, your report query should

o Summarise at least one set of attribute values (typically as a sum or count).

• Create a test plan saying how you will test your report. What test data will you need? How will you ensure the report is functioning as you expect? You need test only the report you proposed under task B. (Approx. 1 page, excluding any test data).

The purpose of stage 1 is to ensure that you have a good plan for the technical implementation in stage

Allocate approx. 4 – 6 hours’ work to Stage 1.

1 For example, if you chose a banking database design, your report could be “Show all customers in Wales who have a loan of over £2000 but have not made any repayment this month”. Of course, make the example appropriate to the database design you have chosen.

You may wish to amend your database design based on feedback from Stage 1 before continuing; in that case, explain your modifications. You may use HeidiSQL or any appropriate tools to interact with MariaDB; you should include screenshots in your report showing your work.

• Derive a relational schema corresponding to your ER diagram of stage 1.

• Normalise your schema through first, second, and third normal forms. Document each form separately, and for each form, either:

o Demonstrate that your schema already meets the normal form, or

o Amend it so that it does.

• Create a MariaDB database on the server soc-web-liv-11 implementing your database design.

• Document:

o DDL statements to create all database objects

o DML statements to insert test data into all tables

• You should insert sufficient test data to give a good indication of your report in use, and to support your test plan from Stage 1. This will normally require 10 or more rows of data per entity.

• Implement the report query proposed in stage 1 – write and run the corresponding SQL, and present the output from your test data.

• Review the output in terms of your test plan; is the SQL correct?

• (Note that I should get the same result you have documented when I run your query against your database on soc-web-liv-11).

You should submit all SQL code together with the output of your report. Allocate approx 10 hours work to Stage 2.

Submission: Please refer to the detailed submission instructions on Moodle.

Originality: Your submission must be prepared by yourself and for this module; you may not work with others or re-use their work. Please acknowledge all sources of help and material. Please refer to this myNapier page and this ENSA page for more information