DIT1133 Assignment: Hospital Management System ER Diagram, Relational Schema, And SQL Implementation

DIT1133 Question:

Scenario: Hospital Management System

A hospital wants to develop a database system to manage its operations. The system should store information about patients, doctors, appointments, and departments.

1. Patients have a unique patient ID, name, gender, date of birth, and phone number.
2. Doctors have a unique doctor ID, name, specialty, and department.
3. Departments have a department ID, department name, and location.
4. Appointments record which patient met which doctor, along with the appointment date, time, and status(completed, cancelled, no show, in progress).
5. A doctor can belong to only one department but can treat many patients.
6. A patient can have multiple appointments with different doctors.

DIT1133 Assignment Tasks

1. Draw an ER Diagram (ERD)

1. Identify entities, attributes, and relationships.

2. Specify primary key for each entity, and cardinality of a relationship.

2. Convert the ERD to a Relational Schema

1. Demonstrate tables with attributes, primary keys, and foreign keys.

3. Creating database and write SQL queriesUsing appropriate DBMS (Ms Access OR Oracle Live SQL), answer the following questions:

1. Create tables with constraints.

2. Insert at least 10 records into each table. The inserted data should be meaningful and sufficient to demonstrate the SQL queries listed in Question 3 below.

4. Write SQL queries to:

1. List all appointments for a specific patient.

2. Retrieve doctors working in the “Cardiology” department.

3. Find patients with no appointments.

4. Count the number of appointments per doctor.

5. Display the details of all appointments that have a status of ‘Cancelled’ or ‘No Show’. The query result should include appointment information along with the corresponding patient details.

Deliverables:

Your submission should include the following:

1. A report (.pdf) which include the following:
   Coursework cover sheet
   Table of contents
   Design of the database – ERD
   List tables with attributes, primary keys, and foreign keys.
   Database creation and records, queries and screenshots of the query answer.
2. Save your file and you MUST name the file as
3. “DIT1133_Assignment_yourID_yourname.pdf”.
4. Upload the SOFTCOPY onto the SPARK coursework submission link (or other platforms – to be confirmed by your lecturer) before or on 14 July 2025. All coursework should be submitted on the scheduled dateline. Extension is not normally granted, but in exceptional case, limited extension may be granted.
5. In term of late submission, a 10% deduction per day of total marks, excluding weekends and public holiday(s). Late submission between 5 to 10 days will deduct 50% of the total marks. After >10 days, submission is not accepted and it will result in an automatic 0% for the assignment.

DIT1133 Report Format:

Your report should follow the formats below:

• Font: Times New Roman, 12 (except for diagrams)
• Line Spacing: 1.5 lines
• Alignment: Justify
• Page Layout: Moderate page margin on A4 size paper
• Header: Module Code & Module Name (left), “Individual Assignment” (right)
• Footer: Page Number (Page X of Y, centre)

DIT1133 Grading Rubric:

This assignment will be graded based on a 100-point rubric. The following details about the categories in the rubric:

DIT1133 Assignment: Hospital Management System ER Diagram, Relational Schema, and SQL Implementation