COMP4128 Databases, Interfaces and Software Design Principles
Coursework 1 (30%)
Title: Smart Campus Facility Booking System
Total Weight: 30%
Parts: 3
Due Date: November 17, 2025 (Monday), 12.00PM
Submission Format: SQL statements in .sql file + Report in PDF format
Overview
In this coursework, you will design and implement a Smart Campus Facility Booking System using the database concepts learned in lectures. The system models how students, staff, and administrators interact with campus facilities (e.g., classrooms, labs, sports halls, equipment).
The coursework is divided into three parts:
1. Entity-Relationship Diagram (ERD)
2. Normalization
3. SQL Implementation (DDL & DML)
This assignment will help you understand data modelling, normalization, and advanced SQL queries in a practical context.
Part 1: Entity-Relationship Diagram (ERD)
Objective:
• Develop an ERD that represents the relationships between entities in a facility booking system.
Requirements:
• Identify and define at least the following core entities:
o Facility: facilities with different types (labs, classrooms, sports halls).
o User: students, staff, administrators (with roles).
o Booking: booking details with time slots.
o Department: departments to which users belong.
o Optional: Equipment (e.g., projectors, lab devices) that can also be reserved; OR any entity that you think is suitable.
• Establish at least four relationships, with at least one many-to-many (M:M) relationship (e.g., users booking multiple facilities).
• Use correct notation for primary keys, foreign keys, and relationship cardinalities.
Deliverables:
• Relationships and assumptions clearly explained.
• A clear and well-documented ERD that captures the necessary relationships.
Part 2: Normalization
Objective:
• Perform. normalization on the entities and relationships identified in Part 1. Your goal is to eliminate redundancy and ensure that the database is structured efficiently.
Requirements:
• Normalize your database design to at least the 3rd Normal Form. (3NF).
• Document each step of the normalization process, beginning from 1NF, then 2NF, and 3NF.
• Provide the updated schema/relations after each normalization to reflect any changes made to the design.
• Explicitly state design decisions such as:
o Why certain attributes were separated into new tables.
o How normalization improved the structure.
Deliverables:
• A step-by-step description/visualization of the normalization process.
• An updated schema/ERD reflecting the normalized design.
Part 3: SQL Statements
Objective:
• Implement the database design using SQL with additional business rules and advanced queries.
Requirements:
(a) DDL Statements
• Create all necessary tables with proper constraints (PK, FK, NOT NULL, UNIQUE, etc).
• Enforce business rules where possible, for example:
o A facility cannot be double-booked at the same time.
o Students can only book up to N facilities per week (state your chosen value of N and how it is enforced).
o Certain facilities can only be booked by staff/admin.
(b) DML Statements
• Insert, update, and delete data with realistic sample values.
• Write advanced SQL queries, for example:
o Nested subqueries (e.g., find the student with the most bookings in a given month).
o Aggregate queries with GROUP BY + HAVING (e.g., facilities booked more than 10 times).
o Joins across at least three tables.
o A small set of reusable reporting queries (e.g., list weekly booking statistics).
• Provide screenshots of successful execution for each.
Deliverables:
• A list of all DDL and DML SQL statements, in both .sql file and report.
• Screenshots of the output after execution of each SQL statement with explanations.
Submission Guidelines
• Format: SQL Statements in .sql file format and report in PDF format. Combine all parts into a single report in PDF format, clearly labelling each section (Part 1, Part 2, and Part 3). There is no strict page limit of the report. Font size 12. All screenshots in the report should be large enough for a clear visual inspection. You do not need to show all the resulting screenshots. You may only show screenshots whenever appropriate.
• Deadline: Submit your assignment by November 17, 2025 (Monday), 12.00PM via Moodle.
• File Naming Convention: Use the following naming convention for the filename: “FirstLast_1234567.sql” and “ FirstLast_1234567.pdf”.
• Late Submission: The standard late submission policy applies, i.e. 5% deduction of the total mark for every 24 hours (including weekends and holidays).
Marking Criteria
1. Part 1: Entity-Relationship Diagram (ERD) (20%)
– The clarity of the relationships with correct cardinalities and includes at least one M:M relationship.
– The completeness of the ERD, including all necessary entities, attributes, and relationships. Correct use of primary and foreign keys.
– ERD shows complete conceptual and logical accuracy, using professional notation with correct PKs, FKs, and participation constraints.
– Includes well-justified design rationale and consistent with SQL schema.
2. Part 2: Normalization (20%)
– Normalization steps from 1NF → 2NF → 3NF. Each normalization step must be explicitly demonstrated with dependency analysis and clear reasoning for every decomposition.
– Use of functional dependencies, decomposition justification, and schema updates.
– Correct presentation of every step to obtain normalized tables with appropriate data types, constraints, and relationships.
3. Part 3: SQL Statements (20%)
– DDL statements are correct, implementing the schema accurately with proper constraints.
– DDL includes comprehensive constraints (PK, FK, UNIQUE, NOT NULL, etc)
– DML includes diverse query types: joins (≥3 tables), nested subqueries, grouping, condition, reporting views, etc.
– Screenshots and explanations show tested, validated, and error-free results.
4. Part 4: Documentation & Analytical Component (40%)
– Report clarity, design justification, critical reflection, and presentation quality.
– Report is professionally written and formatted, clearly structured (Parts 1–3 labelled), with concise yet detailed explanations.
– Includes critical analysis, not just description (e.g., why this schema was chosen, how normalization reduces redundancy, etc.).
– References design principles accurately and avoid generic discussion.
– Screenshots and outputs well-organized.
The total will be normalized to a weight of 30% for this module.