Scribd
Upload
19 views7 pages

Hospital Database Schema Design

The document outlines the schema for a hospital database, including tables for Doctor, Nurse, Patient, Receptionist, Room, Diagnose, Treat, Visit, and Use, each with specified attributes and primary keys. It provides SQL commands for creating the database and tables, inserting sample data, and confirms that the design adheres to the Third Normal Form (3NF) for normalization. The structure ensures that all attributes are atomic, fully dependent on primary keys, and free of transitive dependencies.

Uploaded by

ramymohamed7801
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
19 views7 pages

Hospital Database Schema Design

The document outlines the schema for a hospital database, including tables for Doctor, Nurse, Patient, Receptionist, Room, Diagnose, Treat, Visit, and Use, each with specified attributes and primary keys. It provides SQL commands for creating the database and tables, inserting sample data, and confirms that the design adheres to the Third Normal Form (3NF) for normalization. The structure ensures that all attributes are atomic, fully dependent on primary keys, and free of transitive dependencies.

Uploaded by

ramymohamed7801
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Schema

1. Doctor

- Dr_ID (PK)

- Dr_Name

- Dr_Gender

- Dr_Specialization

- Dr_Add

2. Nurse

- N_ID (PK)

- N_Name

- N_Email

- N_Add

3. Patient

- P_ID (PK)

- P_Name

- P_Gender

- P_EntryDate

- P_ExitDate

4. Receptionist

- R_ID (PK)

- R_Name

- R_Gender

- R_Add
5. Room

- R_Number (PK)

- R_Serial

- R_EntryDate

- R_ExitDate

6. Diagnose

- Dr_ID (FK)

- P_ID (FK)

7. Treat

- N_ID (FK)

- P_ID (FK)

8. Visit

- R_ID (FK)

- P_ID (FK)

9. Use

- P_ID (FK)

- R_Number (FK)
SQL for database and tables creation:
CREATE DATABASE HospitalDB;

USE HospitalDB;

CREATE TABLE Doctor (

Dr_ID INT PRIMARY KEY,

Dr_Name VARCHAR(100),

Dr_Gender VARCHAR(10),

Dr_Specialization VARCHAR(100),

Dr_Add VARCHAR(255)

);

CREATE TABLE Nurse (

N_ID INT PRIMARY KEY,

N_Name VARCHAR(100),

N_Email VARCHAR(100),

N_Add VARCHAR(255)

);

CREATE TABLE Patient (

P_ID INT PRIMARY KEY,

P_Name VARCHAR(100),

P_Gender VARCHAR(10),

P_EntryDate DATE,

P_ExitDate DATE

);
CREATE TABLE Receptionist (

R_ID INT PRIMARY KEY,

R_Name VARCHAR(100),

R_Gender VARCHAR(10),

R_Add VARCHAR(255)

);

CREATE TABLE Room (

R_Number INT PRIMARY KEY,

R_Serial VARCHAR(50),

R_EntryDate DATE,

R_ExitDate DATE

);

CREATE TABLE Diagnose (

Dr_ID INT,

P_ID INT,

PRIMARY KEY (Dr_ID, P_ID),

FOREIGN KEY (Dr_ID) REFERENCES Doctor(Dr_ID),

FOREIGN KEY (P_ID) REFERENCES Patient(P_ID)

);

CREATE TABLE Treat (

N_ID INT,

P_ID INT,
PRIMARY KEY (N_ID, P_ID),

FOREIGN KEY (N_ID) REFERENCES Nurse(N_ID),

FOREIGN KEY (P_ID) REFERENCES Patient(P_ID)

);

CREATE TABLE Visit (

R_ID INT,

P_ID INT,

PRIMARY KEY (R_ID, P_ID),

FOREIGN KEY (R_ID) REFERENCES Receptionist(R_ID),

FOREIGN KEY (P_ID) REFERENCES Patient(P_ID)

);

CREATE TABLE Use (

P_ID INT,

R_Number INT,

PRIMARY KEY (P_ID, R_Number),

FOREIGN KEY (P_ID) REFERENCES Patient(P_ID),

FOREIGN KEY (R_Number) REFERENCES Room(R_Number)

);
SQL - Insert Sample Data:
INSERT INTO Doctor VALUES (1, 'Dr. Ahmed', 'Male', 'Cardiology', 'Cairo');

INSERT INTO Nurse VALUES (1, 'Nurse Laila', 'laila@[Link]', 'Alexandria');

INSERT INTO Patient VALUES (1, 'Mohamed Ali', 'Male', '2023-01-10', '2023-01-20');

INSERT INTO Receptionist VALUES (1, 'Sara Ibrahim', 'Female', 'Giza');

INSERT INTO Room VALUES (101, 'R101', '2023-01-10', '2023-01-20');

INSERT INTO Diagnose VALUES (1, 1);

INSERT INTO Treat VALUES (1, 1);

INSERT INTO Visit VALUES (1, 1);

INSERT INTO Use VALUES (1, 101);


Normalization:
The database design follows 3NF (Third Normal Form):

1NF:

- All attributes are atomic and each cell contains a single value.

2NF:

- All non-key attributes are fully functionally dependent on the primary key.

3NF:

- No transitive dependency exists, all attributes are dependent only on the primary key.

You might also like