Task A
Select any suitable database design as a basis from those available at: 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 your modified schema; you may use draw.io 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:
use data from at least three tables (not counting lookup tables).
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. (Approx. 1 page, excluding any test data).
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 any appropriate tools to interact with MariaDB; you should include screenshots in your report showing your work.
Task D
Derive a relational schema corresponding to your ER diagram of stage 1.
Ensure that your scheme meets third normal form. Document each normal form separately, and for each form, either (a) demonstrate that your schema already meets the normal form, or (b) 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?
Comments