Description
The question is a group project and i need complete solving in it. Please follow the rules given, its a fairly intermediate assignment for database
Project
Deadline: Thursday 04/12/2025 @ 23:59
[Total Mark is 14]
Student Details:
CRN:
Name:
Name:
Name:
ID:
ID:
ID:
Instructions:
• You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on
Blackboard via the allocated folder. These files must not be in compressed format.
• It is your responsibility to check and make sure that you have uploaded both the correct files.
• Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between
words, hide characters, use different character sets, convert text into image or languages other than English
or any kind of manipulation).
• Email submission will not be accepted.
• You are advised to make your work clear and well-presented. This includes filling your information on the cover
page.
• You must use this template, failing which will result in zero mark.
• You MUST show all your work, and text must not be converted into an image, unless specified otherwise by
the question.
• Late submission will result in ZERO mark.
• The work should be your own, copying from students or other resources will result in ZERO mark.
• Use Times New Roman font for all your answers.
Pg. 01
Project Instructions
• You can work on this project as a group of a minimum of 2 and a maximum of 3
students. Each group member must submit the project individually with all group
member names and IDs mentioned on the cover page.
• You must use this template and failing to do so will result in a zero mark.
• The work should be your own. Copying from other students or other resources will
result in a zero mark. Zero marks will also be given if you try to bypass SafeAssign
(e.g., misspell words, remove spaces between words, hide characters, use different
character sets, convert text into an image or languages other than English).
Project Mark Distribution
This project is worth a total of 14 marks, distributed as follows:
• Conceptual Schema Design: Design a Conceptual Schema using ER modelling
concepts, including Entities, Relationships, Attributes, Participation (Total or
Partial), and Cardinality.
• Normalization:
o Tables before Normalization.
o Tables after Normalization Using the mapping algorithm.
• Database Implementation:
o Use a DBMS like MySQL to create the normalized tables and populate
your tables with at least 10 rows.
o Execute the requested sample queries.
Project Submission Content
Each student must submit one report containing the following:
• Project description.
• ER Diagram.
• All schemas before normalization.
• All schemas after normalization.
• All SQL statements for creating tables and inserting data.
• All requested queries and their results.
• Screenshots from your DBMS (e.g., MySQL) of all the tables before and after
population and query results.
Pg. 02
Project title
Database System for a Smart Healthcare Clinic Network
Project Description
Design and implement a database system for a smart healthcare clinic network operating
in multiple cities. The network manages patient registrations, appointment bookings,
doctor schedules, electronic prescriptions, billing, multiple clinics/branches, medical
inventory, security for sensitive data, and user roles (patients, doctors, admin staff). The
system must support reporting, analytics, and enforce security and privacy protocols.
Project Tasks and Mark Distribution
Task
Description
CLOs
Covered
Marks
Conceptual
Design
Design ER diagram: entities (patients,
doctors, clinics etc.), relationships, attributes,
participation, cardinality
CLO1,
CLO3,
CLO4
3
Prenormalization
Tables before normalization
CLO3,
CLO4
2
Normalization
Apply mapping algorithm, normalize and
justify
DBMS
Implementation
Using MySQL (or other DBMS): create
tables, insert at least 10 example rows per
table
Queries and
Analytics
Execute queries: see “Sample Queries” below
CLO3,
CLO4
2
Documentation
& Security
Describe security controls (user roles, access,
sensitive data protection), reflect on different
models used
CLO2,
CLO5
1
CLO3,
CLO4,
CLO5
CLO3,
CLO4,
CLO5
3
3
Pg. 03
Sample Queries & Requirements
• List the names and contact details of patients with more than two appointments
in the last month.
• List all available doctors by specialty at a given clinic (e.g., in Riyadh).
• Retrieve all prescriptions issued by a doctor within a specific time frame.
• List medicines running low in stock at any location (inventory management).
• Display appointments longer than 30 minutes, with patient and doctor details.
• Produce a report of revenue generated per clinic (by location/city).
• For security, explain how user authentication, authorization, and data encryption
would be enforced, and who has access to what data.
• Illustrate example role-permission tables (e.g., only certain staff can see billing
or medical data).
Purchase answer to see full
attachment
