COM736 Database Systems and Data Analytics Coursework Task 1 2026 | Wrexham University

Assessment Task Details Sun Medical Hospital (SMH)

This assignment aims to test your skills in database design and implementation for Sun Medical Hospital. These will be reinforced by developing the practical skills required to use a query language's structures and features to maintain and interrogate a relational database management system using SQL in Google Colab environment. Sun Medical Hospital (SMH) is a medical institute based in America that provides different kinds of healthcare services to the nation. Initial types of SMH services include medical support for emergency healthcare and General Practice.

Recently, SMH intended to expand their services to include dental and optic care to their institute, and you are hired to redesign and implement their current database system. The diagram below (provided separately) illustrates the existing processes from the medical system.

This assessment consists of two parts:

Part 1: Entity-Relationship Design (35%)

In this part, you will design an improved database structure for Sun Medical Hospital that accommodates their expanded services.

Your tasks are to prepare a complete document with an improved ER diagram to support their extended services; your documentation should illustrate the:

1. Entity-Relationship Diagram (15%): Create a complete ER diagram that extends the existing system to support the newly added services (dental and optic care). Clearly pinpoint the newly added components.

2. Design Notes (10%): Provide comprehensive design notes explaining your ER diagram, including rationale for design decisions, entity relationships, and how the extended services are integrated.

3. Relational Schema (10%): Develop a complete relational schema consisting of definitions of domains and relations that represent the entity and relationship types, including primary keys, foreign keys, and any constraints.

Part 2: SQL Database Implementation and Queries (35%)

In this part, you will implement the database schema designed in Part 1 using SQL and demonstrate its functionality through various queries. Students will create tables,
populate them with sample data, and demonstrate the functionality of their database by executing SQL queries that fulfil specific real-world scenarios.

Section A: Database Schema Implementation (15%)

1.    Using SQL (Structured Query Language), create a database schema that accurately reflects your ER diagram from Part 1.
2.    Define appropriate primary keys, foreign keys, and constraints to ensure data integrity.
3.    Populate the database with at least five (5) rows of sample data per table.

Section B: SQL Queries for Management Scenarios (20%)
After creating the database, demonstrate its functionality by writing SQL queries to address the following 10 management scenarios:

1.    Retrieve the list of patients (Persons) who have been admitted to a specific ward.
2.    List all operations performed by a specific surgeon (Staff).
3.    Get the total number of admissions per ward.
4.    Identify patients who have been admitted but have not yet received an operation.
5.    Retrieve a list of operations of a specific type (OP_TYPE) performed within a given time period.
6.    Find patients who received an operation and display their corresponding observations.
7.    Determine the most frequently performed operation type in the hospital.
8.    List staff members who have performed at least one operation as a surgeon or anaesthetist.
9.    Identify admissions where the patient expected to receive an operation but did not.
10.    Retrieve patients who have undergone multiple operations, listing the types of operations they received.

• ER Diagram (including new services)

• Design Notes

• Relational Schema (domains, relations, keys, constraints)

• Database Schema Implementation (table structure, relationships, constraints)

15 marks

• SQL Queries for 10 Management Scenarios (correctness and efficiency)

20 marks

Overall Report Quality

30 marks

• Clarity of explanations and documentation

• Sample data quality and appropriateness

• Academic referencing and professional presentation