Database Systems

Overview
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. Present your work under headings such as Task A, Task B, etc, and allocate your effort in proportion to the marks for each task.

Stage 1

Task A

• Select any suitable database design as a basis
• 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 lookups1, not only entities). Each entity should normally have five or more attributes. Explain the reasons for any modifications you make to the original schema. Choose an example that interests you!
• Create an annotated ER diagram for the schema; you may use draw.io or any suitable tool to author the diagram. Be sure to use UML notation.

Task B

• Propose for a realistic report2 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).

These two conditions should be met by a single query.

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 that the report you proposed under task B is returning the correct answer. (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 2. Allocate approx. 4 - 6 hours' work to Stage 1.

Stage 1 should be submitted to the appropriate Moodle upload link by the stated deadline. Please ensure all submissions are clearly labelled with your matriculation number.

Stage 2

You may wish to amend your database design based on feedback from Stage 1 before continuing; in that case, explain your modifications, and present your final ER diagram. You may use HeidiSQL or any appropriate tools to interact with MariaDB. In your submission, include the SQL used to create your tables, load your test data, and run your report query in textual format. If you include any screenshots showing your work, please ensure these are legible.

Task D

• Derive a relational schema corresponding to your ER diagram of stage 1.
• Ensure that your schema is in third normal form. For each normal form:
o demonstrate that your schema already meets the normal form, or
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:
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.

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.

Attachment:- Database Systems.rar