Coursework Brief For this coursework you are required to complete two database tasks.
For Task 1 you must design a database from a list of requirements, producing an entityrelationship diagram, a determinancy diagram and a relational schema, with a detailed account of how the requirements have been translated into the diagrams. These should be submitted in a Word file.
For Task 2 you must produce a database in Access and perform various database operations. You should submit the Access database and a Word file containing the answers to the problems.
Task 1:
Design a database system to maintain data associated with criminal court cases in the UK. The text below has been produced from a requirements elicitation exercise.
• Each judge has a list of outstanding cases over which he will preside. Only one judge presides per case.
• For each case one prosecuting counsel is appointed to represent the Public Prosecutions Service. Cases are scheduled at one Crown Court for an estimated duration from a given start date.
• A case can try more than one offence. Each offence is for one crime in law. Each offence can have one or more defendants.
• Each defendant can have one or more defending barristers. If an offence has multiple defendants, each defendant can have one or more defence counsel defending.
• Defendants may have more than one outstanding case against them.
(i) Produce an entity-relationship diagram which represents the domain of criminal court cases.
(ii) Assign a number of surrogate identifiers to the entities in the domain and produce a determinancy diagram to document the dependencies between data-items.
(iii) Accommodate both the entity-relationship diagram and the determinancy diagram produced to a relational schema expressed in the bracketing notation.
Task 2: The following tables illustrate some data held by a supplier of sports commodities.
Using the data from the SALES, ITEMS and EMPLOYEES tables above, construct an Access database using suitable key fields and setting up suitable relationships between the tables. Submit your answers to the following problems in a Word file.
i) Print a list of all salespersons that have made a sale (remove duplicates).
ii) Calculate and print out the total number of Balaclavas sold.
iii) Calculate and print out the average age of those Employees who have a salary less than £10000.
iv) Print a list of the Salesperson references for those employees who made more than 1 sale.
v) Print out the name of the employee who sold a Sun Hat.
vi) Print out the maximum number, minimum number and the total value of purchases for each item.
vii) As a result of a national pay increase the following increases are to be implemented.
Salaries below £4000 to be increased 8%
Salaries of £4000 and over and below £8000 to be increased 10%
Salaries of £8000 and over and below £14000 to be increased by 12%
Salaries of £14000 and over to be increased 15%
Update the salaries accordingly.
viii) Print a list of the names of those employees who sold items of clothing.
ix) Remove the ‘Williams’ entry from the EMPLOYEES table. Comment on any implications.
x) Add to the EMPLOYEES table a record for ‘Evans’ who is 23 and on a salary of £2000.
Key Marking criteria will include:
• Accuracy of the calculations
• Appropriateness of the methodology
• Clarity and conciseness of the descriptions of the methodology and the conclusions Word
Limit The word limit for this individual assignment is 2000 words.