Get Instant Help From 5000+ Experts For

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
Assignment: Choosing, Adapting, and Testing a Relational Scheme

Stage 1

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


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


Marking criteria


(A) ER diagram

Context & citation of original example (1) Structure and labelling in diagram (6) Correct use of ER conventions (2) Accurate representation of design (1) Clarity and presentation. (2)


(B) Report query

Realistic suggestion, clear description (2) Requires data from >= 3 tables (1) Includes an aggregation (1)


(C) Test plan

Test plan relates inputs (data) to expected outputs (result) (3)

Plan outlines necessary test data (1)




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.


Marking criteria


(D) Scheme and Normalisation

·         Schema documentation (2)

·         Normalisation (9) (3 per normal form)


(E) Implementation – DDL and DML


·         Tables & data are present on soc-web-liv-11 (3)

·         Is each table/column definition correct? (4)

·         Are primary and foreign keys correctly defined? (4) Data

·         Does the script insert the test data correctly? (1)

·         Is test data chosen appropriately for the report and test plan? (2)


(F) Report query & Testing

·         Query runs on soc-web-liv-11 and gives results shown in the report (2)

·         Is the SQL query well-constructed? (2)

·         Presentation of the query results (data format, column headings, etc) (1)





• 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

sales chat
sales chat