Database Design Coursework - Tasks A-F

(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.

Task A

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 or any suitable tool to author the diagram.

Task B

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  Use data from at least three tables (not counting lookup tables).

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

Task C

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.

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.

Task D

• 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,

o amend it so that it does.

Task E

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

DDL statements to create all database objects

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.

Task F

• 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).