Our Services

Get 15% Discount on your First Order

[rank_math_breadcrumb]

Saudi Electronic University Homework Help

Three Projecet Two of them for IT403 and One for IT 402

Description

I have three projects: two for IT403 and one for IT402.

College of Computing and Informatics

Project
Deadline: Thursday 04/12/2025 @ 23:59
[Total Mark is 14]
Student Details:

CRN:

Name:
Name:
Name:

ID: S
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

Prenormalization

Tables before normalization

CLO3,
CLO4

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

Documentation
& Security

Describe security controls (user roles, access,
sensitive data protection), reflect on different
models used

CLO2,
CLO5

CLO3,
CLO4,
CLO5
CLO3,
CLO4,
CLO5

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).

Pg. 04
Answer:

Project Description
This project presents the design and implementation of a database system for a smart
healthcare clinic network that operates in multiple cities. The main objective of the
system is to organize and store clinical data in a structured way, so that information
about patients, doctors, appointments, prescriptions, medicines, and billing can be
accessed and managed efficiently.
The database supports several core operations that are essential for daily clinic
activities. These include registering new patients, assigning doctors to clinics, booking
and managing appointments, recording electronic prescriptions, tracking the stock of
medicines in each clinic, and issuing billing records for completed visits. By storing
this information in a single integrated database, the system reduces data duplication
and makes it easier to retrieve accurate information when needed.
The system is also designed to support reporting and basic analytics. For example, the
database can be used to generate reports on the number of appointments per clinic, the
revenue generated by each branch, or the medicines that are running low in stock.
These reports help clinic management to monitor performance and make informed
decisions.
Because the data includes sensitive medical and financial information, the project also
considers security and access control. The design assumes that different categories of
users, such as patients, doctors, and administrative staff, will have different access
rights. A role-based access approach is suggested, where each role is allowed to view

Pg. 05
or update only the data that is relevant to its responsibilities. This helps protect patient
privacy and supports compliance with medical data confidentiality requirements.

Conceptual Design (ER Diagram)
2.1 Entities and Main Attributes
The smart clinic database is built around several core entities. Each entity represents a
real object or concept in the healthcare environment, such as a patient, a doctor, or an
appointment. The table below summarizes the main entities and their key attributes.
Primary keys are indicated in brackets.
Entity
Patient

Key Attribute(s)
Main Attributes (summary)
PatientID (PK)
FirstName, LastName, Gender, DateOfBirth,
Phone, Email
Doctor
DoctorID (PK)
DoctorName, Specialty, Phone, Email, ClinicID
(FK)
Clinic
ClinicID (PK)
ClinicName, City, Address, Phone
Appointment AppointmentID
ApptDateTime, DurationMinutes, Reason,
(PK)
PatientID (FK), DoctorID (FK), ClinicID (FK)
Prescription PrescriptionID
PrescriptionDate, Notes, AppointmentID (FK)
(PK)
Medicine
MedicineID (PK) MedicineName, Form, Strength, UnitPrice
Billing
BillingID (PK)
BillingDate, Amount, PaymentMethod,
AppointmentID (FK)

Notes:
•
•

Each primary key (PK) uniquely identifies a record in its table.
Foreign keys (FK) link records between tables and reflect real relationships in
the clinic workflow.

Pg. 06
•

Many-to-many relationships (such as medicines in prescriptions or medicines
stored in clinics) will be handled later using separate junction tables when we
move from ER design to relational schemas.

2.2 Relationships, Participation, and Cardinality
The following table describes how the entities are connected in the system. For each
relationship, the table shows the entities involved, the cardinality, and a short
description of the business meaning.
Relationship

Entities
Involved
Patient –
Appointment

Cardinality

Schedules

Doctor –
Appointment

1:M

Located_In

Clinic –
Appointment

1:M

Works_At

Clinic –
Doctor

1:M

Generate

Appointment 1 : 0..1
– Prescription

Contains

Prescription – M : M
Medicine

Stocks

Clinic –
Medicine

Attends

1:M

M:M

Description
One patient can attend many
appointments over time, but each
appointment is linked to a single patient.
One doctor can have many
appointments, while each appointment is
scheduled with exactly one doctor.
One clinic can host many appointments,
and each appointment takes place in one
specific clinic.
One clinic can employ many doctors, but
each doctor in this model has one main
clinic assignment.
An appointment may generate a
prescription, and each prescription is
associated with a single appointment.
A prescription can contain several
medicines, and the same medicine can
appear in many different prescriptions.
In the relational model, this will be
implemented using a junction table
(Prescription_Medicine).
A clinic can stock multiple medicines,
and each medicine can be stored in more
than one clinic. In the relational model,

Pg. 07

Incur

Appointment
– Billing

1 : 0..1

this will be implemented using a
junction table (Clinic_Medicine).
An appointment may incur one billing
record if it is chargeable, and each
billing record is linked to a single
appointment.

These entities and relationships form the conceptual schema of the smart healthcare clinic
network. They describe the main objects in the system and how they interact, before any
detailed relational or SQL implementation is applied.

Schemas Before Normalization (Pre-Normalization)
In this part, the conceptual design is converted into relational schemas using the basic
ER-to-relational mapping algorithm. At this stage, the focus is on capturing all the
required data and relationships. Normalization rules (1NF, 2NF, 3NF) will be applied
later in the next part.
The following tables show the schemas before normalization. For each schema, the
attributes and keys are listed.

3.1 Mapped Schemas Before Normalization
Schema Name
Patient

Clinic

Doctor

Attributes
PatientID,
FirstName,
LastName, Gender,
DateOfBirth, Phone,
Email
ClinicID,
ClinicName, City,
Address, Phone
DoctorID,
DoctorName,
Specialty, Phone,
Email, ClinicID

Keys
Primary Key: PatientID

Primary Key: ClinicID

Primary Key: DoctorID; Foreign
Key: ClinicID → Clinic(ClinicID)

Pg. 08
Appointment

AppointmentID,
ApptDateTime,
DurationMinutes,
Reason, PatientID,
DoctorID, ClinicID
Prescription
PrescriptionID,
PrescriptionDate,
Notes,
AppointmentID
Medicine
MedicineID,
MedicineName,
Form, Strength,
UnitPrice
Billing
BillingID,
BillingDate,
Amount,
PaymentMethod,
AppointmentID,
ClinicID
Prescription_Medicine PrescriptionID,
MedicineID,
Quantity, Dosage,
FrequencyDays

Clinic_Medicine

ClinicID,
MedicineID,
StockAmount

Primary Key: AppointmentID;
Foreign Keys: PatientID →
Patient(PatientID), DoctorID →
Doctor(DoctorID), ClinicID →
Clinic(ClinicID)
Primary Key: PrescriptionID;
Foreign Key: AppointmentID →
Appointment(AppointmentID)
Primary Key: MedicineID

Primary Key: BillingID; Foreign
Keys: AppointmentID →
Appointment(AppointmentID),
ClinicID → Clinic(ClinicID)

Primary Key: (PrescriptionID,
MedicineID); Foreign Keys:
PrescriptionID →
Prescription(PrescriptionID),
MedicineID →
Medicine(MedicineID)
Primary Key: (ClinicID,
MedicineID); Foreign Keys:
ClinicID → Clinic(ClinicID),
MedicineID →
Medicine(MedicineID)

3.2 Notes on the Pre-Normalization Stage
•

The Appointment table currently stores DoctorID and ClinicID. Because the
doctor is already linked to a clinic, this may later create a transitive
dependency.

Pg. 09
•
•

The Billing table also stores both AppointmentID and ClinicID. Since an
appointment is already linked to a clinic, this may introduce redundancy.
These issues will be examined and corrected in Part 4 (Normalization).

At this point, the schemas correctly reflect the ER design, but they may still contain
redundancy and hidden dependencies. The next step is to apply normalization rules to
improve the design.

Normalization (1NF, 2NF, 3NF)
The purpose of normalization is to reduce redundancy, avoid update anomalies, and
make sure that every table stores data in a clear and consistent way. In this project, the
schemas from Part 3 are examined against First Normal Form (1NF), Second Normal
Form (2NF), and Third Normal Form (3NF).

4.1 First Normal Form (1NF)
A table is in 1NF if:
•
•

Each attribute (column) contains only atomic values (no repeating groups or
lists).
Each record is unique and identified by a primary key.

Application to our schemas:
From Part 3, all attributes in the tables are simple and single-valued. For example:
•

Patient has attributes such as FirstName, LastName, Phone – each field holds

one value.
•
•

Appointment stores one date-time per row in ApptDateTime.

Many-to-many relationships are already separated into their own tables
(Prescription_Medicine, Clinic_Medicine).

There are no repeating groups such as Medicine1, Medicine2, etc. Therefore, all
schemas from Part 3 are already in 1NF.

Pg. 10
4.2 Second Normal Form (2NF)
A table is in 2NF if:
1. It is already in 1NF.
2. Every non-key attribute is fully dependent on the whole primary key (no partial
dependency).
Partial dependencies can only appear when the primary key is composite (made of
more than one attribute).
Application to our schemas:
•

•

Tables such as Patient, Clinic, Doctor, Appointment, Prescription,
Medicine, and Billing have single-attribute primary keys (for example,
PatientID). Therefore, they satisfy 2NF by definition, because there is no part of
a key to cause partial dependency.
Junction tables (Prescription_Medicine, Clinic_Medicine) have composite
primary keys:
o Prescription_Medicine primary key: (PrescriptionID, MedicineID).
Non-key attributes (Quantity, Dosage, FrequencyDays) depend on the
full combination of (PrescriptionID, MedicineID).
o Clinic_Medicine primary key: (ClinicID, MedicineID). Non-key
attribute (StockAmount) depends on the full combination of (ClinicID,
MedicineID).

There are no attributes that depend only on part of a composite key. Therefore, all
schemas are in 2NF.

4.3 Third Normal Form (3NF)
A table is in 3NF if:
1. It is already in 2NF.
2. There are no transitive dependencies, meaning that non-key attributes do not
depend on other non-key attributes.
3. Non-key attributes depend directly on the primary key only.

Pg. 11
In our design, two important transitive dependencies appear.

Case 1: Appointment and Clinic
From Part 3, we have:
•

•

In table Doctor:
o DoctorID → ClinicID
(Each doctor is assigned to exactly one clinic.)
In table Appointment:
o AppointmentID → DoctorID, ClinicID

Because a doctor is already linked to one clinic, we have the following dependencies:
1. AppointmentID → DoctorID
2. DoctorID → ClinicID
This means there is a transitive dependency:
AppointmentID → DoctorID → ClinicID
The attribute ClinicID in the Appointment table is therefore redundant. The clinic of
the appointment can always be found by looking at the clinic of the assigned doctor.
Solution (Case 1):
•
•

Remove ClinicID from the Appointment table.
The clinic can be obtained when needed by joining Appointment → Doctor →
Clinic.

Case 2: Billing and Clinic
From Part 3, we have:
•

In table Appointment (before fixing Case 1):

Pg. 12
AppointmentID → DoctorID, ClinicID
(The clinic can be derived from the doctor.)
In table Billing:
o BillingID → AppointmentID, ClinicID
o

•

Using the dependencies:
1. BillingID → AppointmentID
2. AppointmentID → ClinicID
We obtain another transitive dependency:
BillingID → AppointmentID → ClinicID
Again, ClinicID is not directly needed inside Billing, because the clinic can be found
through the appointment (and then through the doctor if required).
Solution (Case 2):
•
•

Remove ClinicID from the Billing table.
When the clinic associated with a bill is needed, it can be retrieved via joins:
Billing → Appointment → Doctor → Clinic.

4.4 Result After Applying 3NF
After removing the redundant ClinicID from Appointment and Billing, all tables
satisfy 3NF:
•
•
•

Every non-key attribute depends directly on the primary key of its table.
There are no known transitive dependencies.
Redundant data about clinics is eliminated from Appointment and Billing.

Pg. 13
4.5 Schemas After Normalization (3NF)
The final normalized schemas are:
Schema Name
Patient

Attributes
PatientID,
FirstName,
LastName, Gender,
DateOfBirth, Phone,
Email
Clinic
ClinicID,
ClinicName, City,
Address, Phone
Doctor
DoctorID,
DoctorName,
Specialty, Phone,
Email, ClinicID
Appointment
AppointmentID,
ApptDateTime,
DurationMinutes,
Reason, PatientID,
DoctorID
Prescription
PrescriptionID,
PrescriptionDate,
Notes,
AppointmentID
Medicine
MedicineID,
MedicineName,
Form, Strength,
UnitPrice
Billing
BillingID,
BillingDate,
Amount,
PaymentMethod,
AppointmentID
Prescription_Medicine PrescriptionID,
MedicineID,

Keys
Primary Key: PatientID

Primary Key: ClinicID

Primary Key: DoctorID; Foreign
Key: ClinicID → Clinic(ClinicID)

Primary Key: AppointmentID;
Foreign Keys: PatientID →
Patient(PatientID), DoctorID →
Doctor(DoctorID)
Primary Key: PrescriptionID;
Foreign Key: AppointmentID →
Appointment(AppointmentID)
Primary Key: MedicineID

Primary Key: BillingID; Foreign
Key: AppointmentID →
Appointment(AppointmentID)

Primary Key: (PrescriptionID,
MedicineID); Foreign Keys:
PrescriptionID →

Pg. 14
Quantity, Dosage,
FrequencyDays
Clinic_Medicine

ClinicID,
MedicineID,
StockAmount

Prescription(PrescriptionID),
MedicineID →
Medicine(MedicineID)
Primary Key: (ClinicID,
MedicineID); Foreign Keys:
ClinicID → Clinic(ClinicID),
MedicineID →
Medicine(MedicineID)

These normalized schemas will be used in the next part to create the actual database
tables in a DBMS.
Database Implementation (SQL)
In this part, the normalized schema is implemented using MySQL. The following SQL
statements are used to create the database, define the tables according to the 3NF
design, and insert sample data. These statements were executed in MySQL to build and
populate the smart clinic database.
5.1 Create the Database and Select It
CREATE DATABASE it403_smart_clinic;
USE it403_smart_clinic;

5.2 Create Tables (3NF Schema)
— Patient Table
CREATE TABLE Patient (
PatientID INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Gender
VARCHAR(10),
DateOfBirth DATE,
Phone
VARCHAR(20),
Email
VARCHAR(100)

Pg. 15
);
— Clinic Table
CREATE TABLE Clinic (
ClinicID INT AUTO_INCREMENT PRIMARY KEY,
ClinicName VARCHAR(100) NOT NULL,
City
VARCHAR(50) NOT NULL,
Address
VARCHAR(150),
Phone
VARCHAR(20)
);
— Doctor Table
CREATE TABLE Doctor (
DoctorID INT AUTO_INCREMENT PRIMARY KEY,
DoctorName VARCHAR(100) NOT NULL,
Specialty VARCHAR(80),
Phone
VARCHAR(20),
Email
VARCHAR(100),
ClinicID INT,
FOREIGN KEY (ClinicID) REFERENCES Clinic(ClinicID)
);
— Appointment Table (ClinicID removed after 3NF)
CREATE TABLE Appointment (
AppointmentID INT AUTO_INCREMENT PRIMARY KEY,
ApptDateTime DATETIME NOT NULL,
DurationMinutes INT,
Reason
VARCHAR(150),
PatientID
INT,
DoctorID
INT,
FOREIGN KEY (PatientID) REFERENCES Patient(PatientID),
FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID)
);
— Prescription Table

Pg. 16
CREATE TABLE Prescription (
PrescriptionID INT AUTO_INCREMENT PRIMARY KEY,
PrescriptionDate DATE
NOT NULL,
Notes
VARCHAR(255),
AppointmentID INT,
FOREIGN KEY (AppointmentID) REFERENCES Appointment(AppointmentID)
);
— Medicine Table
CREATE TABLE Medicine (
MedicineID INT AUTO_INCREMENT PRIMARY KEY,
MedicineName VARCHAR(100) NOT NULL,
Form
VARCHAR(40),
Strength
VARCHAR(40),
UnitPrice DECIMAL(8,2)
);
— Billing Table (ClinicID removed after 3NF)
CREATE TABLE Billing (
BillingID INT AUTO_INCREMENT PRIMARY KEY,
BillingDate DATETIME NOT NULL,
Amount
DECIMAL(10,2),
PaymentMethod VARCHAR(30),
AppointmentID INT,
FOREIGN KEY (AppointmentID) REFERENCES Appointment(AppointmentID)
);
— Prescription_Medicine (junction for prescription–medicine)
CREATE TABLE Prescription_Medicine (
PrescriptionID INT,
MedicineID INT,
Quantity
INT,
Dosage
VARCHAR(80),
FrequencyDays INT,
PRIMARY KEY (PrescriptionID, MedicineID),

Pg. 17
FOREIGN KEY (PrescriptionID) REFERENCES Prescription(PrescriptionID),
FOREIGN KEY (MedicineID) REFERENCES Medicine(MedicineID)
);
— Clinic_Medicine (junction for clinic–medicine inventory)
CREATE TABLE Clinic_Medicine (
ClinicID INT,
MedicineID INT,
StockAmount INT,
PRIMARY KEY (ClinicID, MedicineID),
FOREIGN KEY (ClinicID) REFERENCES Clinic(ClinicID),
FOREIGN KEY (MedicineID) REFERENCES Medicine(MedicineID)
);

5.3 Insert Sample Data
5.3.1 Insert Clinics

INSERT INTO Clinic (ClinicName, City, Address, Phone) VALUES
(‘Riyadh Central Clinic’, ‘Riyadh’, ‘King Fahd Road’,

‘0111001001’),

(‘Riyadh North Clinic’,

‘Riyadh’, ‘Anas Bin Malik Street’, ‘0111001002’),

(‘Jeddah Sea Clinic’,

‘Jeddah’, ‘Corniche Road’,

‘0122002001’),

(‘Jeddah South Clinic’,

‘Jeddah’, ‘Prince Majid Street’,

‘0122002002’),

(‘Dammam Gulf Clinic’,

‘Dammam’, ‘King Saud Street’,

(‘Makkah Haram Clinic’,

‘Makkah’, ‘Haram Area’,

‘0124004001’),

(‘Madinah Central Clinic’, ‘Madinah’, ‘Central Area’,

‘0145005001’),

‘0133003001’),

Pg. 18
(‘Abha Heights Clinic’,

‘Abha’,

‘Mountain Road’,

(‘Tabuk North Clinic’,

‘Tabuk’, ‘King Khalid Street’,

(‘Taif Garden Clinic’,

‘Taif’,

‘Garden District’,

‘0176006001’),
‘0147007001’),

‘0128008001’);

College of Computing and Informatics

Project
Deadline: Thursday 04/12/2025 @ 23:59
[Total Mark is 14]
Student Details:

CRN:

Name:
Name:
Name:

ID: S
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.

• 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.

Task

Description

CLOs
Covered

Marks

Conceptual
Design

Design ER diagram: entities (patients,
doctors, clinics etc.), relationships, attributes,
participation, cardinality

CLO1,
CLO3,
CLO4

Prenormalization

Tables before normalization

CLO3,
CLO4

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

Documentation
& Security

Describe security controls (user roles, access,
sensitive data protection), reflect on different
models used

CLO2,
CLO5

CLO3,
CLO4,
CLO5
CLO3,
CLO4,
CLO5

Pg. 04
Answer:

Pg. 05
or update only the data that is relevant to its responsibilities. This helps protect patient
privacy and supports compliance with medical data confidentiality requirements.

Notes:
•
•

Each primary key (PK) uniquely identifies a record in its table.
Foreign keys (FK) link records between tables and reflect real relationships in
the clinic workflow.

Pg. 06
•

Many-to-many relationships (such as medicines in prescriptions or medicines
stored in clinics) will be handled later using separate junction tables when we
move from ER design to relational schemas.

Entities
Involved
Patient –
Appointment

Cardinality

Schedules

Doctor –
Appointment

1:M

Located_In

Clinic –
Appointment

1:M

Works_At

Clinic –
Doctor

1:M

Generate

Appointment 1 : 0..1
– Prescription

Contains

Prescription – M : M
Medicine

Stocks

Clinic –
Medicine

Attends

1:M

M:M

Pg. 07

Incur

Appointment
– Billing

1 : 0..1

this will be implemented using a
junction table (Clinic_Medicine).
An appointment may incur one billing
record if it is chargeable, and each
billing record is linked to a single
appointment.

3.1 Mapped Schemas Before Normalization
Schema Name
Patient

Clinic

Doctor

Attributes
PatientID,
FirstName,
LastName, Gender,
DateOfBirth, Phone,
Email
ClinicID,
ClinicName, City,
Address, Phone
DoctorID,
DoctorName,
Specialty, Phone,
Email, ClinicID

Keys
Primary Key: PatientID

Primary Key: ClinicID

Primary Key: DoctorID; Foreign
Key: ClinicID → Clinic(ClinicID)

Pg. 08
Appointment

Clinic_Medicine

ClinicID,
MedicineID,
StockAmount

Primary Key: BillingID; Foreign
Keys: AppointmentID →
Appointment(AppointmentID),
ClinicID → Clinic(ClinicID)

3.2 Notes on the Pre-Normalization Stage
•

The Appointment table currently stores DoctorID and ClinicID. Because the
doctor is already linked to a clinic, this may later create a transitive
dependency.

Pg. 09
•
•

At this point, the schemas correctly reflect the ER design, but they may still contain
redundancy and hidden dependencies. The next step is to apply normalization rules to
improve the design.

4.1 First Normal Form (1NF)
A table is in 1NF if:
•
•

Each attribute (column) contains only atomic values (no repeating groups or
lists).
Each record is unique and identified by a primary key.

Application to our schemas:
From Part 3, all attributes in the tables are simple and single-valued. For example:
•

Patient has attributes such as FirstName, LastName, Phone – each field holds

one value.
•
•

Appointment stores one date-time per row in ApptDateTime.

Many-to-many relationships are already separated into their own tables
(Prescription_Medicine, Clinic_Medicine).

There are no repeating groups such as Medicine1, Medicine2, etc. Therefore, all
schemas from Part 3 are already in 1NF.

•

There are no attributes that depend only on part of a composite key. Therefore, all
schemas are in 2NF.

Pg. 11
In our design, two important transitive dependencies appear.

Case 1: Appointment and Clinic
From Part 3, we have:
•

•

In table Doctor:
o DoctorID → ClinicID
(Each doctor is assigned to exactly one clinic.)
In table Appointment:
o AppointmentID → DoctorID, ClinicID

Remove ClinicID from the Appointment table.
The clinic can be obtained when needed by joining Appointment → Doctor →
Clinic.

Case 2: Billing and Clinic
From Part 3, we have:
•

In table Appointment (before fixing Case 1):

Pg. 12
AppointmentID → DoctorID, ClinicID
(The clinic can be derived from the doctor.)
In table Billing:
o BillingID → AppointmentID, ClinicID
o

•

Remove ClinicID from the Billing table.
When the clinic associated with a bill is needed, it can be retrieved via joins:
Billing → Appointment → Doctor → Clinic.

4.4 Result After Applying 3NF
After removing the redundant ClinicID from Appointment and Billing, all tables
satisfy 3NF:
•
•
•

Every non-key attribute depends directly on the primary key of its table.
There are no known transitive dependencies.
Redundant data about clinics is eliminated from Appointment and Billing.

Pg. 13
4.5 Schemas After Normalization (3NF)
The final normalized schemas are:
Schema Name
Patient

Keys
Primary Key: PatientID

Primary Key: ClinicID

Primary Key: DoctorID; Foreign
Key: ClinicID → Clinic(ClinicID)

Primary Key: BillingID; Foreign
Key: AppointmentID →
Appointment(AppointmentID)

Primary Key: (PrescriptionID,
MedicineID); Foreign Keys:
PrescriptionID →

Pg. 14
Quantity, Dosage,
FrequencyDays
Clinic_Medicine

ClinicID,
MedicineID,
StockAmount

Prescription(PrescriptionID),
MedicineID →
Medicine(MedicineID)
Primary Key: (ClinicID,
MedicineID); Foreign Keys:
ClinicID → Clinic(ClinicID),
MedicineID →
Medicine(MedicineID)

5.3 Insert Sample Data
5.3.1 Insert Clinics

INSERT INTO Clinic (ClinicName, City, Address, Phone) VALUES
(‘Riyadh Central Clinic’, ‘Riyadh’, ‘King Fahd Road’,

‘0111001001’),

(‘Riyadh North Clinic’,

‘Riyadh’, ‘Anas Bin Malik Street’, ‘0111001002’),

(‘Jeddah Sea Clinic’,

‘Jeddah’, ‘Corniche Road’,

‘0122002001’),

(‘Jeddah South Clinic’,

‘Jeddah’, ‘Prince Majid Street’,

‘0122002002’),

(‘Dammam Gulf Clinic’,

‘Dammam’, ‘King Saud Street’,

(‘Makkah Haram Clinic’,

‘Makkah’, ‘Haram Area’,

‘0124004001’),

(‘Madinah Central Clinic’, ‘Madinah’, ‘Central Area’,

‘0145005001’),

‘0133003001’),

Pg. 18
(‘Abha Heights Clinic’,

‘Abha’,

‘Mountain Road’,

(‘Tabuk North Clinic’,

‘Tabuk’, ‘King Khalid Street’,

(‘Taif Garden Clinic’,

‘Taif’,

‘Garden District’,

‘0176006001’),
‘0147007001’),

‘0128008001’);

College of Computing and Informatics

PROJECT
Deadline: Thursday 04/12/2025 @ 23:59
[Total Mark for this Assignment is 14]
Student Details:

CRN: ###

Name: ###

ID: ###

Name: ###

ID: ###

Name: ###

ID: ###

Name: ###

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.
Restricted – ‫مقيد‬

Project Description and tasks

Pg. 01
Learning
Outcome(s):
Instructors: State
the Learning
Outcome(s) that
match this
question

Project Description and tasks

14 Marks

Project title: “AlBaik” Business Process Improvement
Group requirement: 4 students per group
Submission Guidelines:
•
•
•

Ensure all diagrams and analysis are clear and well-organized.
Use real-world examples to back up your suggestions for process
improvements.
Submit your work as a single document by group leader only once

Total marks: 14
Marking criteria:
Tasks

Mark

Introduction

1 mark

Identify and Describe 5 business processes

4 marks

Modelling the As-Is Process using BPMN 2.0 (2 Models)

4 marks

Analysis of the As-Is Process

3 marks

Suggestions for Improvement

2 marks

Total

14 marks

Case study: Al-Baik, a Saudi Arabian fast-food chain, was founded in 1974. The
industry specializes in selling crispy fried chicken mainly. The meals prices are
affordable for everyone and there are more than 100 branches across the
country. The rapid growth of a l Baik caused operational challenges.
Based on the above case study, you are required to do the following:

Restricted – ‫مقيد‬

Question One

Pg. 02
Learning
Outcome(s):
CLO1: Explain the
interdisciplinary
concepts,
theories, and
trends in ES and
their role in
supporting
business
operations

Question One

1 Marks

1. Introduction:
Giving a brief outline of the business model of Al-Baik industry,
highlighting the current situation and the operational challenges

Answer:
Introduction
AlBaik has grown into one of the strongest fast-food brands in Saudi Arabia, mainly
because of its simple idea: serve affordable fried chicken quickly and consistently. The
company operates more than 100 branches, and most of its success comes from high
daily customer traffic, standardized cooking methods, and a reputation for quality. In
simple terms, the business model focuses on speed, volume, and reliability.
However, this fast expansion brings real operational pressure. As more branches open,
the existing processes start to feel outdated—especially during busy hours when long
queues form, orders pile up in the kitchen, and inventory can run low faster than
expected. You can see this clearly in large branches where customers often wait longer
than they should.
Another challenge comes from communication between departments. For example,
inventory teams, procurement, and kitchen operations don’t always get real-time
updates, which makes it harder to plan accurately. This means that even though the
brand is strong, some internal processes are still slow, manual, or disconnected.
From my point of view, these issues show that AlBaik needs to rethink how it manages
its operations. Improving processes with integrated enterprise systems would help the
company keep its quality and speed even as it continues to grow across the country.

Restricted – ‫مقيد‬

Question Two

Pg. 03
Learning
Outcome(s):
CLO2: Describe
the development
life cycle of ES
and reengineering
best practices.

Question Two

4 Marks

2. Identify and Describe 5 Business Processes
Identify and describe five key business processes at Al Baik. For each
process, provide:
•

Description of the activity in details

•

How the business process is performed?

•

Identify the main Input and Output for each process.
(If there are too many processes, focus on the most relevant one with
current issues).

Answer:

1. Order Taking Process
Description:
This process starts the moment a customer steps up to the counter or uses a self-service
kiosk. It focuses on recording the customer’s order accurately and sending it to the
kitchen without delay.
How it is performed:
A staff member selects the meal items in the POS system, confirms the order with the
customer, and processes the payment. Once completed, the order automatically appears
on the kitchen’s display screens so the team can start preparing it right away.
Input: Customer order details, menu items.
Output: Digital order ticket sent to the kitchen system.

2. Food Preparation Process
Description:
This process involves cooking and preparing the meals using AlBaik’s standard
recipes. Since the brand is known for consistency, the kitchen follows strict frying
times, temperatures, and preparation steps.
How it is performed:
Kitchen staff view the order on their display screens, gather the required ingredients,

Restricted – ‫مقيد‬

Question Two

Pg. 04

and follow the recipe to cook each item. After finishing, they assemble the meal and
place it at the pickup counter.
Input: Order ticket, raw materials (chicken, spices, oil), cooking instructions.
Output: Completed meal ready for serving.

3. Inventory Replenishment Process
Description:
Each branch must monitor daily stock levels for items like chicken, spices, packaging
materials, and beverages. When stock begins to drop, the branch requests
replenishment from the central warehouse.
How it is performed:
Managers check inventory reports, identify shortages, and submit restocking requests.
The central warehouse reviews the request, prepares the required items, and arranges
delivery to the branch.
Input: Current stock data, replenishment request.
Output: Updated inventory and delivered supplies.

4. Supplier Procurement Process
Description:
This process ensures AlBaik secures quality ingredients and materials from reliable
suppliers. It covers everything from sourcing suppliers to receiving goods at the
warehouse.
How it is performed:
The procurement team reviews supplier offers, negotiates pricing and delivery terms,
and places purchase orders. Once the suppliers ship the items, the warehouse team
checks the quality and quantity before storing them.
Input: Purchase orders, supplier contracts, material specifications.
Output: Delivered raw materials, approved invoices.

Restricted – ‫مقيد‬

Question Two

Pg. 05

5. Customer Feedback and Complaint Handling
Process
Description:
AlBaik collects customer feedback from multiple platforms—such as social media,
surveys, email, and phone calls—to understand customer concerns and improve
service.
How it is performed:
Customer service agents log every complaint or suggestion into the system, categorize
it, and forward it to the responsible department (e.g., operations, quality assurance).
The issue is then resolved, and the customer is updated with the final response.
Input: Customer feedback, complaint reports.
Output: Resolution updates, service improvements.

Restricted – ‫مقيد‬

Question Three

Pg. 06
Learning
Outcome(s):
CLO4: Design ES
architectural
models for
various business
processes.

Question Three

3. Modelling the As-Is Process using BPMN 2.0
From the business processes identified above, select 2 main business
processes to create the BPMN 2.0 diagram representing the process, include
Swimlanes for relevant participants.
Any BPMN 2.0 tool can be used (ex: Visio)

Answer:

Restricted – ‫مقيد‬

4 Marks

Pg. 07

Restricted – ‫مقيد‬

Question Three

Question Four

Pg. 08
Learning
Outcome(s):
LO1: Explain the
interdisciplinary
concepts,
theories, and
trends in ES and
their role in
supporting
business
operations

Restricted – ‫مقيد‬

Question Four

3 Marks

4. Analysis of the As-Is Process
Analyse the business processes mentioned in the above step from the time and
quality perspectives. Make sure to include the related issues and explain the
impact on the business.

Question Five

Pg. 09
Learning
Outcome(s):
LO3: Discuss the
issues and
challenges
associated with
implementing ES
and their impacts
on corporate
enterprises.

Restricted – ‫مقيد‬

Question Five

2 Marks

5. Suggestions for improvement:
As a business expert, what is the steps that Al-Baik must take into consideration
to improve their business processes. Give suggestions focusing on operational
efficiency and customer satisfaction.

Purchase answer to see full
attachment

Share This Post

Order a Similar Paper and get 15% Discount on your First Order

Our Services

Three Projecet Two of them for IT403 and One for IT 402

Description

Share This Post

Related Questions

IT478 project

internship

352 project

352 solve new project

it 401 solve project

Project 245-3

245 project new

Management Question

Management Question

Management Question

Management Question

Ecom430- Internship Report +presentation

Management Question

Web Design IT404

CT Module 13: Project Management

Week3—Mgt402

Week3—MGT401

MGT404–S3