Department Of Software Engineering

< HOSPITAL DATA ANALYSIS >

REPORT

SUBMITTED TO: SIR SHOAIB

SUBMITTED BY:
 RABEEA FATIMA (049)
 REENA QURESHI (052)

SEMESTER: IV-B
CATEGORY: FINAL PROJECT

1|Page
Contents:
1. Project
Scope………………………………………………………………………………...4
1.1. Data
Domain…………………………………………………………………………………………...4
1.2. Entities and Relationships………………………………………………………………..
…………………...4
1.3. System
scope……………………………………………………………………………………………...4
2. Goals and
Objectives………………………………………………………………………..4
2.1. Primary
Goal…………………………………………………………………………………………...4
2.2. Functional
Objectives…………………………………………………………………………………..5
2.2.1. Sleep Monitoring…………………………………………………………………………… …..5
2.2.2. Lifestyle habit
Tracking……………………………………………………………………………………….5
2.2.3. Well-Being
Evaluation………………………………………………………………………………………..5
2.3. Analytical
Objectives……………………………………………………………………………………....5

[Link] Analysis…………………………………………………………………………………..5
[Link]
Insights…………………………………………………………………………………………...5
[Link] Visualization…………………………………………………………………………….…5
2.4. Technical
Objectives……………………………………………………………………………………..6
[Link]…………………………………………………………………………………………6
[Link]
dependencies……………………………………………………………………………………....7
2.4.3.. Primary and Composite
keys………………………………………………………………………………….7
[Link]
Operations……………………………………………………………………………………………..7
[Link] Server
Integration………………………………………………………………………………………..7
3. Basic
Features………………………………………………………………………………..8

4. Code and Queries……………………………………………………………………...

…….8
2|Page
 4.1. Creation Of
Tables……………………………………………………………………………………..8,9
4.2Implementing JOIN functions…………………………………………………………….……..9
[Link] SELECTION………………………………………………………………………..10
4.4. Implementing PROJECTION…………………………………………………………………...11
4.5. SELECTION + PROJECTION + JOIN…………………………………………………………12

5. Output and Screenshots……………………………………………………………………

13
5.1. Front-End Implementation……………………………………………………………………………
13
5.2. Performing CRUD
Operations………………………………………………………………………..16

3|Page
1. PROJECT SCOPE

The scope of this project covers the complete design and implementation of a relational hospital
management system that handles patient data, diagnoses, medical procedures, and treatment records. The
project includes both backend (SQL Server) and frontend (Windows Forms in C#) components, emphasizing
CRUD operations, data integrity through foreign keys, and structured stored procedures.

1.1. Data Domain

 The database captures real-world clinical data such as patient demographics, diagnosed conditions,
procedures administered, and treatment outcomes.
 The project focuses on hospital data organization, administrative record-keeping, and outcome
tracking.

1.2. Entities and Relationships

 Entities: Patient, Condition_Lookup, Procedure_Lookup, Treatment

 Relationships: The Treatment table references Patient, Condition_Lookup, and Procedure_Lookup through
foreign keys to maintain referential integrity.
 All relationships are enforced using foreign key constraints and transactional stored procedures.

1.3. System Scope

 Backend: Microsoft SQL Server Express (Project database)

 Frontend: C# Windows Forms Application
 Functionality:
o Stored procedures for Insert, Update, Delete, and Select
o Use of joins, projections, and conditional filters
o Parameterized queries for database security
 Outputs: Interactive data forms, validation feedback, tabular views of hospital data

2. GOALS & OBJECTIVES

2.1. Primary Goal

To design and implement a relational hospital database that efficiently stores, manages, and tracks patients,
conditions, medical procedures, and treatments, using C# Windows Forms for the interface.

2.2. Functional Objectives

2.2.1. Hospital Management

To register and update basic patient information including age and gender, using structured stored procedures
and input validation.

4|Page
2.2.2. Medical Classification

To maintain lookup tables (Condition_Lookup, Procedure_Lookup) for standardized disease and treatment
categorization.

2.2.3. Treatment Tracking

To log detailed treatment records for each patient, including:

 Condition and procedure performed

 Cost of treatment
 Length of stay
 Readmission status
 Outcome and patient satisfaction

2.2.4. Data Integrity

To ensure data consistency using:

 Foreign keys
 Stored procedures
 Validation at both frontend and backend levels

2.3. Analytical Objectives

2.3.1. Outcome Analysis

To use SQL queries to evaluate treatment success metrics such as readmission frequency and patient
satisfaction.

2.3.2. Cost Evaluation

To analyze treatment costs and length of stay for budget and planning insights.

2.3.3. Visual Feedback

To display real-time data and status updates via user-friendly forms and data grids in the Windows Forms
interface.

2.4. Technical Objectives

2.4.1. Normalization

1. First Normal Form (1NF):

✅ Goal: Eliminate repeating groups and ensure atomic values.

 All attributes in tables like Patient, Condition_Lookup, and Treatment are atomic.
 No multivalued fields (e.g., each treatment record logs one outcome, one satisfaction rating, etc.).

5|Page
2. Second Normal Form (2NF):
✅ Goal: Eliminate partial dependency on a composite key.

 Junction-like relationships (e.g., between Treatment and Patient/Condition/Procedure) use full

primary keys.
 No attributes depend on only part of any composite key (foreign keys are separate, not combined).

3. Third Normal Form (3NF):

✅ Goal: Remove transitive dependencies.

 All non-key fields in tables such as Treatment (Cost, Outcome, Satisfaction, etc.) depend only on the
primary key (Treatment_ID).
 No indirect dependencies between non-key attributes.

4. Boyce-Codd Normal Form (BCNF):

✅ Goal: Ensure every determinant is a candidate key.

 Functional dependencies such as:

o Patient_ID → Age, Gender
o Condition_ID → Condition_Name
o Procedure_ID → Procedure_Name
o Treatment_ID → Patient_ID, Condition_ID, Procedure_ID, Cost, Outcome, etc.
are all satisfied under BCNF.

5. Fourth Normal Form (4NF):

✅ Goal: Remove multivalued dependencies.

 A single treatment record connects one patient, condition, and procedure at a time.
 No multiple independent multivalued facts in the same table.

6. Fifth Normal Form (5NF):

✅ Goal: Lossless decomposition.

 All decompositions (separate condition, procedure, and patient info) can be recombined without data
loss using foreign key joins.
 No derived data or denormalization needed.

2.4.2. Functional Dependencies

1. Patient (Patient_ID, Age, Gender)

FD: Patient_ID → Age, Gender
2. Condition_Lookup (Condition_ID, Condition_Name)
FD: Condition_ID → Condition_Name
3. Procedure_Lookup (Procedure_ID, Procedure_Name)
FD: Procedure_ID → Procedure_Name
4. Treatment (Treatment_ID, Patient_ID, Condition_ID, Procedure_ID, Cost, Length_of_Stay,
Readmission, Outcome, Satisfaction)
FD: Treatment_ID → Patient_ID, Condition_ID, Procedure_ID, Cost, Length_of_Stay,
Readmission, Outcome, Satisfaction

6|Page
2.4.3. Primary and Composite Keys

 Primary Keys:
o Patient.Patient_ID, Condition_Lookup.Condition_ID,
Procedure_Lookup.Procedure_ID, Treatment.Treatment_ID
 Foreign Keys:
o Treatment.Patient_ID → Patient.Patient_ID
o Treatment.Condition_ID → Condition_Lookup.Condition_ID
o Treatment.Procedure_ID → Procedure_Lookup.Procedure_ID

2.4.4. CRUD Operations

 Implemented for all four tables via stored procedures:

o InsertPatient, UpdatePatient, DeletePatient, etc.
 Interacted through a C# WinForms frontend.

2.4.5. SQL Server Integration

 The database is developed in Microsoft SQL Server Express.

 All operations are connected to a C# frontend via the DbHelper class using stored procedures.

3. BASIC FEATURES:
o Add/Edit/Delete person profiles.
o Query the data using joins, selection, and projection.
o Auto-increment IDs with foreign key relationships.

4. CODE AND QUERIES:

Tables Created In Database:

In Patient Table:

7|Page
In Condition_Lookup Table:

In Procedure_Lookup table:

8|Page
In Treatment Table:

4.4. Implementing JOIN Operation:

Examples:

1. JOIN: Show full treatment details with readable names

Output:

9|Page
 4.4. Implementing SELECTION:

Examples:

1. SELECTION: Show treatments where satisfaction is low (< 5)

Output:

4.5. Implementing PROJECTION:

Examples:
1. PROJECTION: Show only Patient_ID and Cost from Treatment

10 | P a g e
 Output:

4.6. SELECTION + PROJECTION + JOIN:

1. JOIN + SELECTION: Show only female patients with a 'Fracture' condition

Output:

11 | P a g e
[Link] Age, Gender, Condition, and Satisfaction for patients with high satisfaction (≥ 8)

Output:

1. OUTPUT & SCREENSHOTS:

12 | P a g e
 5.1. Front-end Screenshots:

INTERFACE:

SELECTING PATIENT:

13 | P a g e
 SELECTING CONDITION_LOOKUP:

14 | P a g e
 SELECTING PROCEDURE_LOOKUP:

SELECTING TREATMENT:

15 | P a g e
 5.2. CRUD Functionality Implementation:

ADD:

16 | P a g e
 IN DATABASE:

UPDATE:

BEFORE UPDATION:

IN DATABASE:

17 | P a g e
 DELETE:

IN DATABASE:

18 | P a g e
19 | P a g e