M30232 Database Systems Development Coursework 1 Assessment Brief 2024-25 | UoP

M30232 Assessment Marks

ITEM R/D  (Individual CW1 30/07/2025 - 70%) 

• Design a fully functional database that will address business needs
• Implement the designed database along with a sample insert
• Produce 4 queries suitable for the business 

Assessment Notes

Assessment Description

The design and approach of this assessment MUST be specifically related to the case study and not generalisation.

You are part of a team of junior database developers working for Performance Systems PLC and have been in post for approximately 3 months. During this time, you have started to have an understanding of relational databases and have been working on different existing database systems that have been developed by the company’s Senior DBA (You can assume that Val would have this role).

One of the Company’s clients has asked for a system to be developed for them. The Senior DBA does not have the time to devote to this project and so has asked you to work out the design, development, implementation and justification for him. He will then check it over before it is delivered to the clients. 

The work will be an iterative process, conducted in 3 stages with different firm deadlines.  

Therefore, the work you produce is for a Senior DBA, and as such, you can assume they understand more about databases than you do; however, make sure that you are clarifying odd or unclear aspects of your design through assumptions. Your report should take this into account, and you should not spend time describing or explaining topics that the senior DBA already knows (e.g. why you have a PK in a table), but you should analyse their importance and relevance in the given case study. This coursework follows the lifecycle of database implementation.  

• The first stage is to try and identify the data and the organisational / linkage of the data for the system (Requirements). We represent this in an ERD.
• The second stage is an iteration of the requirements and producing the code (Implementation).
• The 3rd stage is to test and asses the database output against business requirements (Evaluation). 

R/D - Case Study

Read carefully the case study before you start to propose a database solution. 

Assessment Requirements

ITEM R/D Individual CW (70%) - Deadline 30/07/2025 @ 16:00 | 100 Marks

Requirements

• Using the information provided by the case study, create a simple diagram (digitally) where you present the initially identified entities and the relationship between them - direct and inferred entities - (no attributes). The diagram must be electronically created (e.g. not hand-drawn). Hand-drawn diagrams will receive 0 marks. (5 Marks)
• Using the information given in the case study, draw an Entity Relationship Diagram (ERD) for the system. You MUST resolve any M: M relationships and include the Primary (PK), Foreign Keys (FK) and Composite Keys (PK/FK) - if any -  in each of the tables, including all suitable attributes, data type and data size. The ERD must be electronically created (e.g. not hand-drawn), and the design should be as realistic as possible (e.g. attribute names, data type/ size).  (15 Marks)
• Include any assumptions you have made in the design. The assumptions should clarify ambiguous aspects of your design. Do not repeat the brief statements (e.g. “Customers table will record customers” or “A customer can make more than one booking”) (5 Marks)
• A complete Data Dictionary that will show all constraints and additional descriptions if necessary. The Data Dictionary must follow this template. For obvious attributes, a description is not needed (e.g. user_name, user_email etc).  (15 Marks)
• The physical implementation of the database. ALL SQL code (CREATE) and sample data that you have used for testing the database (INSERT). The code must be organised in the correct CREATE/INSERT sequence, as it will be tested on the VM, having appropriate comments where necessary. The code should work as it was provided, as we are just using COPY/PASTE into the VM. The physical database should follow the ERD you have created for requirement 2.

Check your code before submission. If the database does not work, there will be no way to assess your database functionality. We will not fix your code. (30 Marks) 

• To evaluate the database functionality, data extraction and manipulation with SQL, based on the implemented database, you are required to create 4 queries that you consider will be useful for the business, along with a description of what the query does and why it will be useful for the business.

A copy of ALL queries must be available as a SQL file as well. (20 Marks) 

Innovation & Excellence (10 Marks)

Awarded for extraordinary work that goes beyond minimum requirements (e.g. ready to implement database, out-of-the-box thinking, coverage of business aspects that are very logical but were not listed in the case study, extremely complex database design), with innovative aspects. Also appropriate CW layout, references grammar & punctuation and anything else that is outstanding. 

The deadline for submission is 30/07/2025 @ 16:00. 

The submission box will accept only 1 file as a PDF and one as SQL. Make sure that you submit BOTH files. 

Name your submission as upxxxxx_rd, where X is your UP number.

The PDF file should contain your Initial Entities, the complete ERD, Assumptions, Data Dictionary and screenshots of the 4 queries output along with the query itself.    

Submission checklist

• Initial Entities - Direct and Inferred (PDF)
• ERD (PDF)
• Assumptions (PDF)
• Data Dictionary (PDF)
• The SQL for the database (CREATE and INSERT statements) (SQL)
• 4 Queries SQL code (PDF & SQL)
• 4 queries output screenshots (PDF)

Please read carefully and submit ALL required elements. 

The CW will be assessed against the following criteria:

• Rational and Logical aspects in relation to databases best practices and case study
• Suitable, sensible and complete set of attributes with appropriate data type and size
• Organisation and layout of designs and report document
• Database optimisation (through appropriate constraints, checks, data type/size)
• SQL formatting, comments and errors
• Overall complexity  (logical & physical)
• Query Relevance: How well queries meet business needs.
• SQL Feature Use: Application of joins, aggregates, subqueries, and advanced SQL constructs.
• Query Complexity: Sophistication and complexity of SQL queries.
• SQL Formatting: Readability and organisation of SQL code.
• Code Accuracy: Correctness of SQL syntax and logic.
• Understanding: Grasp of data relationships and insight extraction.
• References, grammar & punctuation