Task T1: EERD (7 marks)
Using the information given in the case study, draw an Enhanced Entity-Relationship Diagram for the database system. Do NOT include any attributes on the EERD.
The EERD should fit on ONE page of the submitted PDF document.
Task T2: Rationale and Assumptions (3 marks)
Clearly state your rationale for the design and state any assumptions you have made about the system. These assumptions should only relate to the design of the system. For example you may have decided to include or omit certain entities, or you may have decided to generalise some entities – tell us why… (ca 200 words)
Task T3: Data Dictionary (7 marks)
Create a data dictionary for the tables you have identified in your EERD. If your EERD contains many-to-many relationships, the data dictionary must resolve them. The tables MUST be in 3NF, contain a range of attributes and clearly indicate the keys.
For each table, the data dictionary should include the following:
- The attributes for the table, as they will be named in the database
- The data type and size you have chosen for each attribute
- Where appropriate, whether an attribute is a Primary and/or Foreign Key (and for Foreign Keys the table they relate to)
- A short explanation of the attribute and why you have included it (where it is not obvious) – for example if you have an attribute called NINO it would be hard to guess what it is so you need to state that it is National Insurance Number! However if it is FName it probably does not need additional
You must use the template we have provided for you at the end of this document to complete this task.
Task T4: SQL Tables (3 marks)
Using your data dictionary, create the tables for the case study database, with the attributes, specifying primary and foreign keys. Submit the SQL commands you have written for this.
Task T5: Security (7 marks)
Discuss the security measures you will take to protect the proposed database system.
Your discussion should focus on database security. Explain the threats present and the countermeasures available. Focus on FOUR security issues that must relate to the technical aspects of DB security and also must relate to the coursework database. (ca 500 words)
Task T6: Optimization (7 marks)
A database system should be optimised to provide the best performance. Discuss the need for optimisation in the case study database, especially envisioning its future growth. Identify three different optimisation techniques implemented in SQL; show how these optimizations can apply to the coursework database. (ca 500 words)
Task T7: Web Database (7 marks)
The database may have a number of users to whom it should be available through a client system. Discuss the needs of the case study company, the benefits and/or drawbacks of using Web technologies. Also discuss the architecture and technology options for making the proposed database available with Web technologies. Propose ONE Web page where users would input data into the database, and ONE page that would show data from the database. Both pages must be meaningful and valuable in the case study setting; include sketches of the layout of the two pages. (ca 500 words)
Task T8: Professional, Legal and Ethical Issues (5 marks)
Developing a database system requires a consideration of the professional, legal and ethical issues of the proposed system. Research and discuss the issues that you would consider if you were developing this system for a real client. (ca 300 words)
First Submission T1 – T4
T1: EERD (7 marks)
Completeness 1 mark
Meaningful entities 1 mark
Meaningful relationships 1 mark
Cardinality, Participation 1 mark
Logical and clear layout 1 mark
Use of enhanced concepts 1 mark
Proper notation 1 mark
Excellence or serious errors can be rewarded or penalized with an adjustment.
T2: Rationale and Assumptions (3 marks)
A sensible rational argument and assumptions that relate to the design of the system as well as a rational discussion about why the design has the features it has. Marks will not be given for re-stating the case study.
2–3 marks: A good discussion that lists assumptions made on top of the case study text, and provides a rationale for non-trivial modelling decisions.
0–1 marks: A weak discussion that restates the case study or repeats what’s on the diagram.
T3: Data Dictionary (7 marks)
A comprehensive data dictionary where the tables relate to the EERD. Sensible attributes for each table. Appropriate naming of attributes, correct data types selected (MySQL only).
Identification of keys, identification of attributes. Template given has been clearly used.
6–7 marks: A comprehensive DD that relates exactly to the EERD, sensible choice of attributes and data types. Data types are correct for above stated RDBMS. All PKs and FKs in the correct table. Tables are in 3NF. Attributes relevant, explanations given where required, domains etc. present.
3–5 marks: A good DD that has minor errors only in Data types, sizes, attributes etc.
0–2 marks: Significant errors in the DD, does not relate to the EERD, contains errors in data types, attributes keys, no explanations given.
T4: SQL Tables (3 marks)
2–3 marks: A comprehensive set of tables that contain the correct syntax, structure, keys, data types and sensible attributes for each table. Tables, data types and attributes are the same as stated in the data dictionary.
0–2 marks: Some errors in syntax, structure, keys, data types or attributes for each table. Tables, data types and attributes are not the same as stated in the data dictionary.
Second Submission T5 – T8
T5: Security (7 marks)
A good answer will focus on database security issues and not on network or internet security. The discussion will be in-depth and detailed showing an understanding of the technical issues of implementing security, the strengths and weakness of each security solution, and how it applies to the case study. It may also cover the issues with the users and how they may affect security. Again technically oriented, showing the student understands not only the concept but also how it works, the benefits and disadvantages. The answer will relate to the case study database and will not be a general discussion on the issues.
5–7 marks: focus on database security issues and not on network or internet security; detailed in-depth discussion showing an understanding of the technical issues of implementing security, the strengths and weakness of each security solution, and how it applies to the case study; may also cover the issues with the users and how they may affect security; technically oriented showing an understanding of not only the concept but how it works, the benefits and disadvantages; relates to the case study database and is not a general discussion on the issues.
3–5 marks: focus mostly on database security issues and not network or internet security, discussion shows an understanding of the technical issues of implementing security, the strengths and weakness of each security issue; shows an understanding but a less technical explanation may be given; an attempt to relate the discussion to the case study
0–3 marks: not entirely related to database security, may contain other security issues; may show some misunderstanding of the topic and may not explain any advantages or disadvantages; no technical information given; no relation to the case study
T6: Optimization (7 marks)
At the top level students will show they understand the concepts of optimization. There will be mention of SQL tuning and the heuristics for optimizing queries. Mention of RAT graphs. Also issues such as file types and indexing will be referred to and the contribution to performance that they make. Students should show they understand how queries can be optimized through the use of Relational Algebra and/or Relational Calculus. A clear discussion on optimisation covering the need for good response and throughput for the system. Different optimisation techniques discussed.
5–7 marks: excellent discussion showing a good understanding of the issues of optimization, and also how this is implemented by MySQL, with appropriate examples based on the case study.
3–5 marks: some understanding of the issues of optimisation, some explanation of how it would work, some examples based on the case study.
0–3 marks: unsatisfactory discussion, with no examples based on the case study; description of optimisation taken mainly from other sources, with little if any original thought from the student, and little if any reference to the case study, indication that the student does not understands the issues involved.
T7: Web Database (7 marks)
At the top level students will discuss the needs of the database users in the case study company, understand the role of Web technologies in relation to the database, and present the pros and cons of using Web technologies to serve the users. Technically-oriented discussion of the considered Web technologies and the architecture of the user-facing software built on top of the database. Well-targeted example input and output pages, shown in context of the whole Web-based system, with considerations of usability and business value, perhaps with illustrations.
5–7 marks: good understanding of the role of Web technologies in relation to databases, familiarity with some technologies with a considered discussion of benefits and drawbacks; good relation to the case study, well-selected input and output pages that show an understanding of usability and the needs of the users.
3–5 marks: some understanding of the role of Web technologies, limited knowledge of relevant technologies, or simple input/output pages.
0–3 marks: poor work that shows a lack of understanding of the role of Web technologies in relation to databases, no familiarity with any such technologies, and only trivial input/output pages.
T8: Professional, Legal and Ethical Issues (5 marks)
3–5 marks: a good discussion of the issues, related well to the case study.