WALGREENS DATABASE MANAGEMENT SYSTEMS

By Group 14

Anirudha Kulakarni
Rakesh Kumar Kavuru
Vineesha Reddy Konda
Antharvedi Narasimha Charyulu Srimath Tirumala Gudimella
Sriramoju Rahul
 INTRODUCTION

Walgreens's National Pharmacy company requires a database system to manage its operations
effectively. The system will store information about various entities such as patients, doctors,
medications, employee data, payroll, health insurance, prescription, clinics, vaccinations,
locations, and inventory. In this project, we will implement a distributed database system
determining relationships between entities.

ENTITIES AND ATTRIBUTES

1. Medication: It contains information about the medicines present in the pharmacy.

a. medicine_id: integer is the primary key.
b. medicine_name: string
c. dosage: integer
d. manufacture_date: integer
e. expiry_date: integer

2. Patients: It contains information about patients who visit the pharmacy.

a. patient_id: integer is the primary key.
b. name: string
c. dob: integer
d. contact_details: integer
e. medicine_id: integer

3. Doctors: It stores information about the doctors who prescribe medications to the patients.
a. doctor_id: integer is the primary key.
b. specialization: string
c. doctor_name: string
d. experience: integer

4. Employee Data: This entity contains information about the employees working at the
pharmacy company.
a. employee_id: integer is the primary key.
b. employee_name: string
c. designation: string
d. salary: integer
e. pay_date: date
f. hours_worked: integer
g. contact number: integer
h. address: string
i. manager: string
j. department_id: integer
5. Payroll: This entity contains the employee’s personal profile and bank account details.
a. ssn_id: integer is the primary key
b. employee_name: string
c. employee_id: integer
d. account_number: integer
e. pay_date: date
f. hours_worked: integer

6. Health Insurance: This entity stores information about the insurance policies held
by the customers/patients of the Pharmacy company.
a. policy_number: integer is the primary key.
b. type: string
c. medical_history: string
d. coverage_plan: integer
e. premium_amount: integer

7. Prescription: It contains information about the medicines present in the pharmacy.

a. patient_id: integer is the primary key
b. doctor_name: string
c. medicine_name: string
d. dosage: integer
e. prescription_date: date

8. Clinics: This entity stores information about the clinics that are part of the Pharmacy Company.
a. branch_id: integer is the primary key
b. name: string
c. type: string
d. contact_details: integer

9. Vaccinations: This entity stores information about the vaccinations that the Pharmacy
Company offers.
a. vaccination_id: integer is the primary key
b. name: string
c. type: string
d. dosage: integer
e. date_of_vaccination: integer
f. age: integer
g. patient_name: string

10. Location: It contains information about the various branches of the Pharmacy.
a. Location id: integer is the primary key
b. doctor_name: integer
c. employee_name: integer
d. address: string
Added a new table ‘Inventory’
11. Inventory: This entity contains medicines and their available quantity.
a. medicine_id int primary key
b. medicine_name string
c. location_id int
d. quantity int
RELATIONSHIPS

1 to 1 Binary relationship
a. Employee and Payroll: Each employee can have only one payroll and each
payroll belongs to only one employee.

1 to many Binary relationships

a. Patients and Prescriptions: A patient can have multiple prescriptions, but
each prescription belongs to only one patient.
b. Patients and Health insurance: A patient can have multiple insurance policies, but
each insurance policy belongs to only one patient.
c. Patients and Vaccinations: A patient can have multiple vaccinations, but each
vaccination belongs to only one patient.
d. Doctors and Prescriptions: A doctor can prescribe multiple prescriptions, but
each prescription is prescribed by only one doctor.
e. Doctors and medications: A doctor can prescribe multiple medications, but
each medication is prescribed by only one doctor to a patient.
f. Doctors and Vaccinations: A doctor can prescribe multiple vaccinations, but each
vaccination is prescribed by only one doctor.

many to many Binary relationships

a. Patients and Clinics: A patient can visit multiple clinics and each clinic can have
multiple recurring patients.
b. Employee and Locations: An employee can work at multiple locations and each
location can have many employees working.
c. Medications and Clinics: Clinics can have multiple medications and
one medication can be present at multiple clinics.
d. Medications and Prescriptions: Prescriptions can have multiple medications and
one medication can be prescribed to multiple patients.
e. Doctors and locations: A doctor can work at multiple locations and each location
can have many doctors working.

ER DIAGRAM
TRANSFORMATION OF E-R DIAGRAM INTO RELATIONS:

Primary keys are highlighted in bold color and foreign keys are yellow underlined.
1. Medication: medicine_id, medicine_name, dosage, manufacture date, expiry date
2. Patients: patient_id, name, dob, contact_details, medicine_id
3. Doctors: doctor_id, specialization, doctor_name, experience
4. Employee Data: employee_id, employee_name, designation, salary, date_of_joining,
contact number, address, manager, department_id.
5. Payroll: ssn_id, employee_name, employee_id, account_number
 6. Health Insurance: policy_number, type, coverage_plan, premium_amount
7. Prescription: patient_id, doctor_name, medicine_name, dosage
8. Clinics: branch_id, name, type, contact_details
9. Vaccinations: vaccination_id, name, type, dosage, date_of_vaccination, age, patient_name
10. Location: Location_id, doctor_name, employee_name, address
11. Inventory: medicine_id, medicine_name, location_id, quantity

TABLES
1. Medication Table:

Create Syntax:

CREATE TABLE Medication (

medicine_id Integer PRIMARY KEY,
medicine_name varchar2(25),
dosage Integer constraint dos CHECK (dosage >= 3 AND dosage < 15),
manufacture_date Integer NOT NULL,
expiry_date Integer NOT NULL
);

Insert Syntax:

INSERT INTO Medication (medicine_id, medicine_name, dosage, manufacture_date,

expiry_date)
VALUES (1001, 'Amoxicillin', 10, 20220101, 20221231);
INSERT INTO Medication (medicine_id, medicine_name, dosage, manufacture_date,
expiry_date)
VALUES (1002, 'Ibuprofen', 5, 20220115, 20221231);

INSERT INTO Medication (medicine_id, medicine_name, dosage, manufacture_date,

expiry_date)
VALUES (1003, 'Aspirin', 8, 20220201, 20221231);

INSERT INTO Medication (medicine_id, medicine_name, dosage, manufacture_date,

expiry_date)
VALUES (1004, 'Naproxen', 10, 20220115, 20221231);

INSERT INTO Medication (medicine_id, medicine_name, dosage, manufacture_date,

expiry_date)
VALUES (1005, 'Paracetamol', 7, 20220101, 20221231);

INSERT INTO Medication (medicine_id, medicine_name, dosage, manufacture_date,

expiry_date)
VALUES (1006, 'Metformin', 10, 20220101, 20221231);

INSERT INTO Medication (medicine_id, medicine_name, dosage, manufacture_date,

expiry_date)
VALUES (1007, 'Lisinopril', 6, 20220115, 20221231);

INSERT INTO Medication (medicine_id, medicine_name, dosage, manufacture_date,

expiry_date)
VALUES (1008, 'Simvastatin', 10, 20220101, 20221231);

INSERT INTO Medication (medicine_id, medicine_name, dosage, manufacture_date,

expiry_date)
VALUES (1009, 'Atorvastatin', 10, 20220201, 20221231);

INSERT INTO Medication (medicine_id, medicine_name, dosage, manufacture_date,

expiry_date)
VALUES (1010, 'Amlodipine', 5, 20220215, 20221231);
Selecting the data from the Medication Table:

2. Patient table

Create Syntax:

CREATE TABLE Patients ( patient_id

Integer PRIMARY KEY, name
varchar2(25),
 dob Integer NOT NULL,
contact_details Integer UNIQUE,
medicine_id Integer
);

Insert Syntax:

INSERT INTO Patients (patient_id, name, dob, contact_details, medicine_id)

VALUES (1, 'John Smith', 19851214, 1234567890, 101);
INSERT INTO Patients (patient_id, name, dob, contact_details, medicine_id)
VALUES (2, 'Jane Doe', 19980425, 9876543210, 102);
INSERT INTO Patients (patient_id, name, dob, contact_details, medicine_id)
VALUES (3, 'Mike Johnson', 19720630, '5554443333', 103);
INSERT INTO Patients (patient_id, name, dob, contact_details, medicine_id)
VALUES (4, 'Sarah Lee', 19990101, 7778889999, 104);
INSERT INTO Patients (patient_id, name, dob, contact_details, medicine_id)
VALUES (5, 'Avery Thompson', 19751230, 4443332222, 105);
INSERT INTO Patients (patient_id, name, dob, contact_details, medicine_id)
VALUES (6, 'Ethan Brown', 19890505, 2221110000, 106);
INSERT INTO Patients (patient_id, name, dob, contact_details, medicine_id)
VALUES (7, 'Olivia Davis', 19780419, 3334445555, 101);
INSERT INTO Patients (patient_id, name, dob, contact_details, medicine_id)
VALUES (8, 'Noah Wilson', 19960310, 1112223333, 103);
INSERT INTO Patients (patient_id, name, dob, contact_details, medicine_id)
VALUES (9, 'Emma Clark', 19651231, 6667778888, 105);
INSERT INTO Patients (patient_id, name, dob, contact_details, medicine_id)
VALUES (10, 'Liam Anderson', 19720505, 9998887777, 102);
Selecting the data from patients table:
3. Doctors Table:

Create Syntax:

CREATE TABLE Doctors (

doctor_id Integer PRIMARY KEY,
specialization varchar2(25),
doctor_name varchar2(25) NOT NULL,
experience Integer constraint exp CHECK (experience >= 2 AND experience <= 35)
);

Insert Syntax:
INSERT INTO Doctors (doctor_id, specialization, doctor_name, experience)
VALUES (101, 'Cardiology', 'Dr. John Smith', 15);

INSERT INTO Doctors (doctor_id, specialization, doctor_name, experience)

VALUES (102, 'Pediatrics', 'Dr. Jane Doe', 5);

INSERT INTO Doctors (doctor_id, specialization, doctor_name, experience)

VALUES (103, 'Neurology', 'Dr. Mike Johnson, 20);

INSERT INTO Doctors (doctor_id, specialization, doctor_name, experience)

VALUES (104, 'Orthopedics', 'Dr. Sarah Lee', 10);

INSERT INTO Doctors (doctor_id, specialization, doctor_name, experience)

VALUES (105, 'Dermatology', 'Dr. Avery Thompson', 25);

INSERT INTO Doctors (doctor_id, specialization, doctor_name, experience)

VALUES (106, 'Ophthalmology', 'Dr. Ethan Brown', 3);

INSERT INTO Doctors (doctor_id, specialization, doctor_name, experience)

VALUES (107, 'Cardiology', 'Dr. Olivia Davis', 12);
INSERT INTO Doctors (doctor_id, specialization, doctor_name, experience)
VALUES (108, 'Pediatrics', 'Dr. Noah Wilson', 7);

INSERT INTO Doctors (doctor_id, specialization, doctor_name, experience)

VALUES (109, 'Neurology', 'Dr. Emma Clark', 30);

INSERT INTO Doctors (doctor_id, specialization, doctor_name, experience)

VALUES (110, 'Orthopedics', 'Dr. Liam Anderson', 25);

Selecting the data from the Doctors Table:

 3. Employee_Data Table:
Create Syntax
create TABLE Employee_Data(
employee_id INTEGER PRIMARY KEY,
employee_name VARCHAR2(25),
designation VARCHAR2(25),
salary INTEGER constraint sal CHECK (salary >= 12000 AND salary <= 40000),
pay_date date,
hours_worked number,
contact_number INTEGER UNIQUE constraint contact CHECK (length(contact_number) =
10),
address VARCHAR2(50),
manager VARCHAR2(25),
department_id INTEGER NOT NULL
);

Insert Syntax:

INSERT INTO Employee_Data (employee_id, employee_name, designation, salary, pay_date,

hours_worked, contact_number, address, manager, department_id)
VALUES (10001, 'John Smith', 'Manager', 30000, TO_DATE('2022-11-03', 'YYYY-MM-DD'),
10, 9876543210, '123 Main Street', 'David Lee', 101);
INSERT INTO Employee_Data (employee_id, employee_name, designation, salary,
pay_date, hours_worked,contact_number, address, manager, department_id)
VALUES (10002, 'Jane Doe', 'Senior Developer', 25000, TO_DATE('2022-11-03', 'YYYY-MM-
DD'), 12,8765432109, '456 Elm Street', 'John Smith', 102);
INSERT INTO Employee_Data (employee_id, employee_name, designation, salary,
pay_date, hours_worked,contact_number, address, manager, department_id)
VALUES (10003, 'Mike Johnson', 'Junior Developer', 15000, TO_DATE('2022-03-04', 'YYYY-
MM-DD'), 8, 7654321098, '789 Oak
Street', 'Jane Doe', 102);
INSERT INTO Employee_Data (employee_id, employee_name, designation, salary,
pay_date, hours_worked,contact_number, address, manager, department_id)
VALUES(10004, 'Emily Chen', 'HR Coordinator', 18000,TO_DATE('2022-03-06', 'YYYY-MM-
DD'), 7,6543210987, '567 Pine
Street', 'John Smith', 103);
INSERT INTO Employee_Data (employee_id, employee_name, designation, salary,
pay_date, hours_worked,contact_number, address, manager, department_id)
VALUES(10005, 'Tom Lee', 'Sales Associate', 22000,TO_DATE('2022-03-10', 'YYYY-MM-
DD'), 9,5432109876, '345 Cedar
Street', 'David Lee', 104);
INSERT INTO Employee_Data (employee_id, employee_name, designation, salary,
pay_date, hours_worked,contact_number, address, manager, department_id)
VALUES (10006, 'Jenny Wong', 'Marketing Associate', 19000,TO_DATE('2023-03-06', 'YYYY-
MM-DD'), 11, 4321098765, '678
Maple Street', 'David Lee', 105);
INSERT INTO Employee_Data (employee_id, employee_name, designation, salary,
pay_date, hours_worked,contact_number, address, manager, department_id)
VALUES (10007, 'Kevin Johnson', 'Junior Developer', 14000,TO_DATE('2023-03-10', 'YYYY-
MM-DD'), 12,3210987654, '890
Cherry Street', 'Jane Doe', 102);
INSERT INTO Employee_Data (employee_id, employee_name, designation, salary,
pay_date, hours_worked,contact_number, address, manager, department_id)
VALUES (10008, 'Sara Lee', 'Marketing Coordinator', 20000,TO_DATE('2023-03-09', 'YYYY-
MM-DD'), 4,2109876543, '456 Oak
Street', 'David Lee', 105);
INSERT INTO Employee_Data (employee_id, employee_name, designation, salary,
pay_date, hours_worked,contact_number, address, manager, department_id)
VALUES (10009, 'Chris Evans', 'Senior Developer', 28000,TO_DATE('2023-03-04', 'YYYY-
MM-DD'), 3,1098765432, '123 Cedar
Street', 'John Smith', 102);
INSERT INTO Employee_Data (employee_id, employee_name, designation, salary,
pay_date, hours_worked,contact_number, address, manager, department_id)
VALUES (10010, 'Nancy Liu', 'Financial Analyst', 14000,TO_DATE('2023-03-06', 'YYYY-MM-
DD'), 2,1987654321, '789 Pine
Street', 'David Lee', 106);

Selecting the data from the Employee_data table:

 4. Payroll Table:

Create syntax:

CREATE TABLE Payroll (

ssn_id INTEGER PRIMARY KEY,
employee_name VARCHAR2(25),
employee_id INTEGER,
account_number CHAR(11) NOT NULL,
salary INTEGER constraint sal_range CHECK (salary >= 12000 AND salary <= 40000),
pay_date date,
hours_worked Integer,
CONSTRAINT fk_employee_id FOREIGN KEY (employee_id) REFERENCES
Employee_Data (employee_id)
);

Insert Syntax:

INSERT INTO Payroll (ssn_id, employee_name, employee_id,

account_number,salary,pay_date,hours_worked) VALUES
(111111111, 'John Smith', 10001,'12345678901',30000,TO_DATE('2022-11-03', 'YYYY-MM-
DD'), 10);
INSERT INTO Payroll (ssn_id, employee_name, employee_id,
account_number,salary,pay_date,hours_worked) VALUES
(222222222, 'Jane Doe', 10002,'23456789012',25000,TO_DATE('2022-11-03', 'YYYY-MM-
DD'), 12);
INSERT INTO Payroll (ssn_id, employee_name, employee_id,
account_number,salary,pay_date,hours_worked) VALUES
(333333333, 'Mike Johnson', 10003,'34567890123',15000,TO_DATE('2022-03-04', 'YYYY-MM-
DD'), 8);
INSERT INTO Payroll (ssn_id, employee_name, employee_id,
account_number,salary,pay_date,hours_worked) VALUES
(444444444, 'Emily Chen', 10004,'45678901234',18000,TO_DATE('2022-03-06', 'YYYY-MM-
DD'), 7);
INSERT INTO Payroll (ssn_id, employee_name, employee_id,
account_number,salary,pay_date,hours_worked) VALUES
(555555555, 'Tom Lee', 10005,'56789012345',22000,TO_DATE('2022-03-10', 'YYYY-MM-
DD'), 9);
INSERT INTO Payroll (ssn_id, employee_name, employee_id,
account_number,salary,pay_date,hours_worked) VALUES
(666666666, 'Jenny Wong', 10006,'67890123456',19000,TO_DATE('2023-03-06', 'YYYY-MM-
DD'), 11);
INSERT INTO Payroll (ssn_id, employee_name, employee_id,
account_number,salary,pay_date,hours_worked) VALUES
(777777777, 'Kevin Johnson', 10007,'78901234567',14000,TO_DATE('2023-03-10', 'YYYY-
MM-DD'), 12);
INSERT INTO Payroll (ssn_id, employee_name, employee_id,
account_number,salary,pay_date,hours_worked) VALUES
(888888888, 'Sara Lee', 10008,'89012345678',20000,TO_DATE('2023-03-04', 'YYYY-MM-DD'),
3);
INSERT INTO Payroll (ssn_id, employee_name, employee_id,
account_number,salary,pay_date,hours_worked) VALUES
(999999999, 'Chris Evans', 10009,'90123456789',28000,TO_DATE('2023-03-04', 'YYYY-MM-
DD'), 3);
INSERT INTO Payroll (ssn_id, employee_name, employee_id,
account_number,salary,pay_date,hours_worked) VALUES
(1010101010, 'Nancy Liu', 10010,'01234567890',14000,TO_DATE('2023-03-06', 'YYYY-MM-
DD'), 2);
Selecting the data from the payroll table:

5. HealthInsurance Table:

Create syntax:

CREATE TABLE HealthInsurance (

 policy_number INTEGER PRIMARY KEY,
type VARCHAR(25) NOT NULL,
medical_history varchar2(25),
coverage_plan INTEGER,premium_amount INTEGER Constraint premium CHECK
(premium_amount >= 1500 AND premium_amount <= 10000)
);

Insert syntax:

INSERT INTO HealthInsurance (policy_number, type, medical_history, coverage_plan,

premium_amount)
VALUES (1001, 'Individual', 'None', 1, 2500);

INSERT INTO HealthInsurance (policy_number, type, medical_history, coverage_plan,

premium_amount)
VALUES (1002, 'Family', 'Diabetes', 2, 5000);

INSERT INTO HealthInsurance (policy_number, type, medical_history, coverage_plan,

premium_amount)
VALUES (1003, 'Individual', 'Asthma', 1, 3500);

INSERT INTO HealthInsurance (policy_number, type, medical_history, coverage_plan,

premium_amount)
VALUES (1004, 'Family', 'Heart disease', 3, 7500);

INSERT INTO HealthInsurance (policy_number, type, medical_history, coverage_plan,

premium_amount)
VALUES (1005, 'Individual', 'None', 2, 4500);

INSERT INTO HealthInsurance (policy_number, type, medical_history, coverage_plan,

premium_amount)
VALUES (1006, 'Family', 'Cancer', 4, 10000);

INSERT INTO HealthInsurance (policy_number, type, medical_history, coverage_plan,

premium_amount)
VALUES (1007, 'Individual', 'High blood pressure', 1, 2000);

INSERT INTO HealthInsurance (policy_number, type, medical_history, coverage_plan,

premium_amount)
VALUES (1008, 'Family', 'None', 2, 5500);

INSERT INTO HealthInsurance (policy_number, type, medical_history, coverage_plan,

premium_amount)
VALUES (1009, 'Individual', 'Depression', 1, 3000);
INSERT INTO HealthInsurance (policy_number, type, medical_history, coverage_plan,
premium_amount)
VALUES (1010, 'Family', 'Arthritis', 3, 8000);

Selecting the data from Health Insurance Table:

6. Prescription Table:

Create Syntax:

create TABLE Prescription (

patient_id Integer PRIMARY KEY,
doctor_name varchar2(25),
medicine_name varchar2(25),
 dosage integer NOT NULL,
prescription_date date NOT NULL,
constraint fk_patient_id foreign key(patient_id) References Patients(patient_id)
);

Insert syntax:

INSERT INTO Prescription (patient_id, doctor_name, medicine_name, dosage,prescription_date)

VALUES (1, 'Dr. Smith', 'Ibuprofen', 200,TO_DATE('2021-06-02', 'YYYY-MM-DD'));
INSERT INTO Prescription (patient_id, doctor_name, medicine_name, dosage,prescription_date)
VALUES (2, 'Dr. Lee', 'Amoxicillin', 500,TO_DATE('2021-06-02', 'YYYY-MM-DD'));
INSERT INTO Prescription (patient_id, doctor_name, medicine_name, dosage,prescription_date)
VALUES (3, 'Dr. Johnson', 'Lisinopril', 20,TO_DATE('2021-06-02', 'YYYY-MM-DD'));
INSERT INTO Prescription (patient_id, doctor_name, medicine_name, dosage,prescription_date)
VALUES (4, 'Dr. Davis', 'Albuterol', 2,TO_DATE('2021-06-02', 'YYYY-MM-DD'));
INSERT INTO Prescription (patient_id, doctor_name, medicine_name, dosage,prescription_date)
VALUES (5, 'Dr. Wilson', 'Fluoxetine', 40,TO_DATE('2021-06-02', 'YYYY-MM-DD'));
INSERT INTO Prescription (patient_id, doctor_name, medicine_name, dosage,prescription_date)
VALUES (6, 'Dr. Thompson', 'Metoprolol', 25,TO_DATE('2021-06-02', 'YYYY-MM-DD'));
INSERT INTO Prescription (patient_id, doctor_name, medicine_name, dosage,prescription_date)
VALUES (7, 'Dr. Anderson', 'Citalopram', 40,TO_DATE('2021-06-02', 'YYYY-MM-DD'));
INSERT INTO Prescription (patient_id, doctor_name, medicine_name, dosage,prescription_date)
VALUES (8, 'Dr. Lee', 'Loratadine', 10,TO_DATE('2021-06-02', 'YYYY-MM-DD'));
INSERT INTO Prescription (patient_id, doctor_name, medicine_name, dosage,prescription_date)
VALUES (9, 'Dr. Johnson', 'Omeprazole', 20,TO_DATE('2021-06-02', 'YYYY-MM-DD'));
INSERT INTO Prescription (patient_id, doctor_name, medicine_name, dosage,prescription_date)
VALUES (10, 'Dr. Brown', 'Simvastatin', 20,TO_DATE('2021-06-02', 'YYYY-MM-DD'));
Selecting the data from the Prescription Table:

7. Clinics Table:
Create Syntax:

CREATE TABLE Clinics (

branch_id Integer PRIMARY KEY,
name varchar2(25),
type varchar2(25) NOT NULL,
contact_details Integer UNIQUE
);

Insert Syntax:

INSERT INTO Clinics (branch_id, name, type, contact_details)

VALUES (1, 'ABC Clinic', 'Family Medicine', 1234567890);

INSERT INTO Clinics (branch_id, name, type, contact_details)

VALUES (2, 'XYZ Clinic', 'Pediatrics', 9876543210);

INSERT INTO Clinics (branch_id, name, type, contact_details)

VALUES (3, 'CDE Clinic', 'Dermatology', 5554443333);

INSERT INTO Clinics (branch_id, name, type, contact_details)

VALUES (4, 'EFG Clinic', 'Cardiology', 7778889999);

INSERT INTO Clinics (branch_id, name, type, contact_details)

VALUES (5, 'HIJ Clinic', 'Neurology', 4443332222);

INSERT INTO Clinics (branch_id, name, type, contact_details)

VALUES (6, 'LMN Clinic', 'Oncology', 2221110000);

INSERT INTO Clinics (branch_id, name, type, contact_details)

VALUES (7, 'OPQ Clinic', 'Psychiatry', 3334445555);

INSERT INTO Clinics (branch_id, name, type, contact_details)

VALUES (8, 'RST Clinic', 'Orthopedics', 1112223333);

INSERT INTO Clinics (branch_id, name, type, contact_details)

VALUES (9, 'UVW Clinic', 'Gastroenterology', 6667778888);
INSERT INTO Clinics (branch_id, name, type, contact_details)
VALUES (10, 'XYZ2 Clinic', 'Pediatrics', 9998887777);

Selecting the data from Clinics Table:

8. Vaccinations Table:

Create Syntax:

CREATE TABLE Vaccinations (

 vaccination_id INT PRIMARY KEY,
name varchar2(25),
type varchar2(25) constraint vac_typ CHECK(type IN ('flu', 'covid', 'ebola', 'tb')),
dosage Integer UNIQUE,
date_of_vaccination Integer,
age Integer CHECK(age >=3 and age <=60),
patient_name varchar2(25)
);

Insert syntax:

INSERT INTO Vaccinations (vaccination_id, name, type, dosage, date_of_vaccination, age,

patient_name)
VALUES (1, 'Fluzone', 'flu', 1, 20220101, 35, 'John Smith');

INSERT INTO Vaccinations (vaccination_id, name, type, dosage, date_of_vaccination, age,

patient_name)
VALUES (2, 'Moderna', 'covid', 2, 20220102, 45, 'Jane Doe');

INSERT INTO Vaccinations (vaccination_id, name, type, dosage, date_of_vaccination, age,

patient_name)
VALUES (3, 'Zostavax', 'flu', 3, 20220103, 60, 'Mike Johnson');

INSERT INTO Vaccinations (vaccination_id, name, type, dosage, date_of_vaccination, age,

patient_name)
VALUES (4, 'BCG', 'tb', 4, 20220104, 10, 'Sarah Lee');
INSERT INTO Vaccinations (vaccination_id, name, type, dosage, date_of_vaccination, age,
patient_name)
VALUES (5, 'Covisheid', 'covid', 5, 20220105, 29, 'Avery Thompson');

INSERT INTO Vaccinations (vaccination_id, name, type, dosage, date_of_vaccination, age,

patient_name)
VALUES (6, 'Covovax', 'covid', 6, 20220106, 40, 'Ethan Brown');

INSERT INTO Vaccinations (vaccination_id, name, type, dosage, date_of_vaccination, age,

patient_name)
VALUES (7, 'Bacillus Calmette', 'tb', 7, 20220107, 8, 'Olivia Davis');
INSERT INTO Vaccinations (vaccination_id, name, type, dosage, date_of_vaccination, age,
patient_name)
VALUES (8, 'Fluarix', 'flu', 8, 20220108, 55, 'Noah Wilson');

INSERT INTO Vaccinations (vaccination_id, name, type, dosage, date_of_vaccination, age,

patient_name)
VALUES (9, 'Janssen', 'covid', 9, 20220109, 50, 'Emma Clark');

INSERT INTO Vaccinations (vaccination_id, name, type, dosage, date_of_vaccination, age,

patient_name)

VALUES (10, 'MVA-BN Filovirus vaccine', 'ebola', 10, 20220110, 25, 'Liam Anderson');

Selecting the data from the Vaccinations Table:

 9. Location Table:

Create Syntax:

CREATE TABLE Location (

location_id Integer PRIMARY KEY,
doctor_name varchar2(25),
employee_name varchar2(25),
address varchar2(25) UNIQUE
);

Insert Table:

INSERT INTO Location (location_id, doctor_name, employee_name, address)

VALUES (1, 'Dr. Smith', 'John Doe', '123 Main St.');

INSERT INTO Location (location_id, doctor_name, employee_name, address)

VALUES (2, 'Dr. Patel', 'Jane Smith', '456 Maple Ave.');

INSERT INTO Location (location_id, doctor_name, employee_name, address)

VALUES (3, 'Dr. Lee', 'Mike Johnson', '789 Oak Blvd.');

INSERT INTO Location (location_id, doctor_name, employee_name, address)

VALUES (4, 'Dr. Davis', 'Sarah Lee', '1011 Elm St.');

INSERT INTO Location (location_id, doctor_name, employee_name, address)

VALUES (5, 'Dr. Garcia', 'Avery Thompson', '1213 Pine St.');

INSERT INTO Location (location_id, doctor_name, employee_name, address)

VALUES (6, 'Dr. Wilson', 'Ethan Brown', '1415 Cedar Ave.');

INSERT INTO Location (location_id, doctor_name, employee_name, address)

VALUES (7, 'Dr. Nguyen', 'Olivia Davis', '1617 Spruce St.');
INSERT INTO Location (location_id, doctor_name, employee_name, address)
VALUES (8, 'Dr. Kim', 'Noah Wilson', '1819 Birch Rd.');

INSERT INTO Location (location_id, doctor_name, employee_name, address)

VALUES (9, 'Dr. Chen', 'Emma Clark', '2021 Oak St.');

INSERT INTO Location (location_id, doctor_name, employee_name, address)

VALUES (10, 'Dr. Anderson', 'Liam Anderson', '2223 Maple Rd.');

Selecting the data from the Location Table:

10. Inventory

Table: Create Syntax:

CREATE TABLE Inventory (

medicine_id INT PRIMARY KEY,
medicine_name VARCHAR(255) NOT NULL,
 location_id INT NOT NULL,
quantity INT NOT NULL,
FOREIGN KEY (location_id) REFERENCES Location(location_id)
);

Insert Syntax:

INSERT INTO Inventory (medicine_id, medicine_name,location_id,quantity)

VALUES (1001, 'Amoxicillin', '1', '10');
INSERT INTO Inventory (medicine_id, medicine_name,location_id,quantity)
VALUES (1002, 'Ibuprofen', '2', '5');
INSERT INTO Inventory (medicine_id, medicine_name,location_id,quantity)
VALUES (1003, 'Aspirin', '3', '15');
INSERT INTO Inventory (medicine_id, medicine_name,location_id,quantity)
VALUES (1004, 'Naproxen', '4', '0');
INSERT INTO Inventory (medicine_id, medicine_name,location_id,quantity)
VALUES (1005, 'Paracetamol', '5', '20');
INSERT INTO Inventory (medicine_id, medicine_name,location_id,quantity)
VALUES (1006, 'Metformin', '6', '18');
INSERT INTO Inventory (medicine_id, medicine_name,location_id,quantity)
VALUES (1007, 'Lisinopril', '7', '25');
INSERT INTO Inventory (medicine_id, medicine_name,location_id,quantity)
VALUES (1008, 'Simvastatin', '8', '35');
INSERT INTO Inventory (medicine_id, medicine_name,location_id,quantity)
VALUES (1009, 'Atorvastatin', '9', '12');
INSERT INTO Inventory (medicine_id, medicine_name,location_id,quantity)
VALUES (1010, 'Amlodipine', '10', '30');
Selecting the data from the Inventory Table:
1. List the total number of prescriptions group by doctors and pharmacy location issued
on June 2nd, 2021.

Query:

SELECT d.doctor_name, l.address, COUNT(*) AS num_prescriptions

FROM Doctors d
JOIN Prescription p ON d.doctor_name = p.doctor_name
JOIN Location l ON d.doctor_name = l.doctor_name
WHERE p.prescription_date = '02-JUNE-2021'
GROUP BY d.doctor_name, l.address;

2. Find locations with inventories that list at least one missing product (a product that has
a quantity of zero in the inventory).

Query:

SELECT
l.address FROM
Location l
JOIN Inventory i ON l.location_id = i.location_id
 WHERE i.quantity = 0;

3. Find the name of the employee(s) that had worked the most hours on November 3,

2022 Query:

SELECT employee_name, SUM(hours_worked) as total_hours

FROM Employee_Data
WHERE pay_date = '03-NOV-2022'
GROUP BY employee_name
HAVING SUM(hours_worked) = (
SELECT MAX(total_hours)
FROM (
SELECT employee_name, SUM(hours_worked) as total_hours
FROM Employee_Data
WHERE pay_date = '03-NOV-2022'
GROUP BY employee_name
));
4. List the items that currently have the least quantity in inventory.

Query:

SELECT medicine_name, MIN(quantity) as least_quantity

FROM Inventory
GROUP BY medicine_name;
5. Print the payroll from March 4 2022 to March 10 2022 displaying employee name,
hours worked, and total salary for all employees

Query:

SELECT ed.employee_name, p.hours_worked, p.hours_worked * ed.salary as total_salary

FROM Employee_Data ed
JOIN Payroll p ON ed.employee_id = p.employee_id
WHERE p.pay_date >= '04-MAR-2022' AND p.pay_date <= '10-MAR-2022';
6. Design a delete statement to delete employees working less than 5 hours from March
4, 2023, to March 10, 2023.

Deleting the above data from Payroll table (child table) as the payroll table has a foreign
key (employee_id) referencing from the employee_data table. Later, deleting data from
employee_data table

Query:

DELETE FROM payroll

WHERE employee_id IN (
SELECT employee_id
FROM Employee_Data
WHERE pay_date >= '04-MAR-2023'
AND pay_date <= '10-MAR-2023'
AND hours_worked < 5
);
DELETE FROM Employee_Data
WHERE pay_date >= '04-MAR-2023' AND pay_date <= '10-MAR-2023' AND
hours_worked < 5;
 7. Design an update statement to give a 23% salary raise to employees working more than
5 hours from March 4, 2023, to March 10, 2023.

Query:

UPDATE Employee_Data
SET salary = salary * 1.23
WHERE pay_date >= '04-MAR-2023' AND pay_date <= '10-MAR-2023' AND
hours_worked > 5;

10 ADDITIONAL SQL QUERIES

1. Find the total quantity of each medicine in the inventory and display the address.

Query:

SELECT i.medicine_name, SUM(i.quantity) AS total_quantity, l.address

FROM Inventory i
INNER JOIN Location l ON i.location_id = l.location_id
GROUP BY i.medicine_name, l.address;
2. Find the total amount of salary paid to employees in each department.

Query:

SELECT e.department_id, SUM(e.salary) AS total_salary

FROM Employee_Data e
GROUP BY e.department_id;
3. Display the names and total salary expenses for each department, where the total
salary expenses are greater than 30000.

Query:

SELECT department_id, SUM(salary)

FROM Employee_Data
GROUP BY department_id
HAVING SUM(salary) > 30000;
4. Display the names and total quantity of each medicine available at each location,
where the total quantity is greater than the average quantity available for that medicine.

Query:

SELECT medicine_name, location_id, SUM(quantity) AS total_quantity

FROM Inventory
GROUP BY medicine_name, location_id
HAVING SUM(quantity) > (SELECT AVG(quantity) FROM Inventory WHERE
medicine_name = 'aspirin')
ORDER BY total_quantity DESC;
5. Display the total number of employees in each department, and sort the result
in descending order.

Query:

SELECT department_id, COUNT(employee_id) AS total_employees

FROM employee_data
GROUP BY department_id
ORDER BY total_employees DESC;
6. Find the number of patients who have received each vaccination with the vaccination id.

Query:

SELECT v.vaccination_id, v.name, COUNT(p.patient_id) AS num_patients

FROM Vaccinations v
JOIN Patients p ON v.patient_name = p.name
GROUP BY v.vaccination_id, v.name;
7. Find the number of patients assigned to each doctor by specialization.

Query:

SELECT d.specialization, d.doctor_name, COUNT(*) AS num_patients

FROM Doctors d
JOIN Patients p ON d.doctor_id = p.medicine_id
GROUP BY d.specialization, d.doctor_name;
8. Find the number of employees reporting to each manager.

Query:

SELECT manager, COUNT(*) AS num_employees

FROM Employee_Data
GROUP BY manager;
9. Find the highest and lowest salary in each department.

Query:

SELECT e.department_id, MAX(e.salary) as highest_salary, MIN(e.salary) as

lowest_salary
FROM Employee_Data e
GROUP BY e.department_id;
10. Display the average age of patients who have been vaccinated with each vaccination

type: Query:

SELECT type, AVG(age) AS avg_age

FROM Vaccinations
GROUP BY type;
11. Query to find the number of vaccinations administered to each patient in a specific
age range:

Query:

SELECT Vaccinations.patient_name, COUNT(*) AS num_vaccinations

 FROM Vaccinations
WHERE Vaccinations.age BETWEEN 18 AND 50
GROUP BY Vaccinations.patient_name;

UPDATE STATEMENTS

1. Update the medicine name with Dolo where the medicine_id is 1002
UPDATE Medication SET medicine_name='Dolo' WHERE medicine_id=1002;
2. Update the name of the patient as Harry with patient id 6:
UPDATE Patients SET name = 'Harry' WHERE patient_id = 6;

3. Update the experience of the doctor with doctor id 101 to 10 years:

UPDATE Doctors SET experience = '10' WHERE doctor_id = 101;
4. Update the salary of the employee with employee id 10003 to 30000:
UPDATE Employee_Data SET salary = 30000 WHERE employee_id = 10003;

5. Update the premium amount of the health insurance policy with policy number 1004
to 5000:
UPDATE HealthInsurance SET premium_amount = 5000 WHERE policy_number =
1004;
6. Update the name of the clinic with branch id 2 to "RRR Clinic":
UPDATE Clinics SET name = 'RRR Clinic' WHERE branch_id = 2;

7. Update the date_of_vaccination of vaccination table with vaccination id 5 to '20220102':

UPDATE Vaccinations SET date_of_vaccination = '20220102' WHERE vaccination_id =
5;
DELETE STATEMENTS

1. Delete the record from the Medication table where the medicine id is
1001: DELETE FROM Medication WHERE medicine_id = 1001;
2. Delete all the records from the Prescription table where the medicine name is
'Ibuprofen': DELETE FROM Prescription WHERE medicine_name = 'Ibuprofen';

3. Delete all the records from the Health Insurance table where the premium amount is
less than 5000:
DELETE FROM HealthInsurance WHERE premium_amount < 5000;
4. Delete all the records from the Vaccinations table where the date of vaccination is
20220101:
DELETE FROM Vaccinations WHERE date_of_vaccination = '20220101';

5. Delete all the records from the Inventory table where the quantity is
0: DELETE FROM Inventory WHERE quantity = 0;
6. Delete all the records from the Employee Data table where the designation is
'Intern': DELETE FROM Employee_Data WHERE designation = 'Intern';

7. Delete all the records from the Clinics table where the type is
'Neurology': DELETE FROM Clinics WHERE type = 'Neurology';
8. Delete all the records from the Doctors table where the experience is less than 10
years: DELETE FROM Doctors WHERE experience < 10;
STORED PROCEDURES
1. Description
The "UpdateDoctorExperience" procedure is a PL/SQL procedure that takes a "doctor_id" and a "new_experience" as
input parameters, and updates the "experience" field in the "Doctors" table with the new experience value for the
given "doctor_id". If any error occurs during the update operation, the procedure rolls back the transaction and raises
the error.

CREATE OR REPLACE PROCEDURE UpdateDoctorExperience(p_doctor_id IN INTEGER, p_new_experience IN

INTEGER)
IS
BEGIN
UPDATE Doctors
SET experience = p_new_experience
WHERE doctor_id = p_doctor_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/

BEGIN
UpdateDoctorExperience(p_doctor_id => 101, p_new_experience => 10);
END;
/
SELECT * FROM DOCTORS
 2. Description
This procedure retrieves the details of a prescription for a given patient ID.

CREATE OR REPLACE PROCEDURE get_prescription_details(p_patient_id IN INTEGER)

IS
v_doctor_name VARCHAR2(25);
v_medicine_name VARCHAR2(25);
v_dosage INTEGER;
v_prescription_date DATE;
BEGIN
SELECT doctor_name, medicine_name, dosage, prescription_date
INTO v_doctor_name, v_medicine_name, v_dosage, v_prescription_date
FROM Prescription
WHERE patient_id = p_patient_id;

DBMS_OUTPUT.PUT_LINE('Doctor Name: ' || v_doctor_name);

DBMS_OUTPUT.PUT_LINE('Medicine Name: ' || v_medicine_name);
DBMS_OUTPUT.PUT_LINE('Dosage: ' || v_dosage);
DBMS_OUTPUT.PUT_LINE('Prescription Date: ' || v_prescription_date);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Prescription not found for Patient ID: ' || p_patient_id);
END;
/

BEGIN
get_prescription_details(p_patient_id => 2);
END;
/
 3. Description
This procedure updates the contact details of a patient based on their patient ID.

CREATE OR REPLACE PROCEDURE update_contact_details(

p_patient_id IN INTEGER,
p_contact_details IN INTEGER
) AS
BEGIN
UPDATE Patients
SET contact_details = p_contact_details
WHERE patient_id = p_patient_id;

COMMIT;
DBMS_OUTPUT.PUT_LINE('Contact details updated successfully for patient with ID: ' || p_patient_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Patient with ID: ' || p_patient_id || ' not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred while updating contact details for patient with ID: ' ||
p_patient_id);
ROLLBACK;
RAISE;
END;
/

DECLARE
v_patient_id INTEGER := 1;
v_contact_details INTEGER := 999999999;
BEGIN
update_contact_details(v_patient_id, v_contact_details);
END;
/
SELECT * FROM PATIENTS

4. Description
This procedure updates the contact details of a clinic based on its branch ID.

CREATE OR REPLACE PROCEDURE update_clinic_contact_details(

p_branch_id IN INTEGER,
p_contact_details IN INTEGER
) AS
BEGIN
UPDATE Clinics
SET contact_details = p_contact_details
WHERE branch_id = p_branch_id;

COMMIT;
DBMS_OUTPUT.PUT_LINE('Contact details updated successfully for clinic with branch ID: ' ||
p_branch_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Clinic with branch ID: ' || p_branch_id || ' not found.');
 WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred while updating contact details for clinic with branch ID:
' || p_branch_id);
ROLLBACK;
RAISE;
END;
/

BEGIN
update_clinic_contact_details(3,5454545454);
END;
/

SELECT * FROM CLINICS

 5. Description
This procedure calculates the total salary for an employee in the Payroll table based on their hours worked
and updates the salary in the Employee_Data table

CREATE OR REPLACE PROCEDURE calculate_and_update_salary(

p_employee_id IN INTEGER,
p_hours_worked IN INTEGER
) AS
v_salary INTEGER;
BEGIN
SELECT salary INTO v_salary
FROM Employee_Data
WHERE employee_id = p_employee_id;
IF v_salary IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Employee with ID: ' || p_employee_id || ' not found.');
ELSE
v_salary := v_salary + p_hours_worked * 10;
UPDATE Employee_Data
SET salary = v_salary
WHERE employee_id = p_employee_id;
 UPDATE Payroll
SET salary = v_salary, hours_worked = p_hours_worked
WHERE employee_id = p_employee_id;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Salary updated successfully for employee with ID: ' || p_employee_id);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee with ID: ' || p_employee_id || ' not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred while calculating and updating salary for employee with
ID: ' || p_employee_id);
ROLLBACK;
RAISE;
END;
/
BEGIN
calculate_and_update_salary(10001, 12);
END;
/

SELECT * FROM EMPLOYEE_DATA

FUNCTIONS
1. Description
The "GetDoctorLocation" function is a PL/SQL function that takes a "doctor_id" as input and returns the
corresponding "address" from the "Location" table. If no data is found for the given "doctor_id", the
function returns NULL.

CREATE OR REPLACE FUNCTION GetDoctorLocation(p_doctor_id IN INTEGER)

RETURN VARCHAR2
IS
v_address VARCHAR2(25);
BEGIN
SELECT address INTO v_address
FROM Location
WHERE doctor_name = (
SELECT doctor_name FROM Doctors WHERE doctor_id = p_doctor_id
);
RETURN v_address;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
/
DECLARE
v_result VARCHAR2(25);
BEGIN
v_result := GetDoctorLocation(p_doctor_id => 101);
DBMS_OUTPUT.PUT_LINE('Doctor Location: ' || v_result);
END;
/

2. Description
This function calculates the total dosage of a medicine for a given medicine name and patient ID

CREATE OR REPLACE FUNCTION get_total_dosage(p_medicine_name IN VARCHAR2, p_patient_id

IN INTEGER)
RETURN INTEGER
IS
v_total_dosage INTEGER := 0;
BEGIN
SELECT SUM(dosage)
INTO v_total_dosage
FROM Prescription
WHERE medicine_name = p_medicine_name AND patient_id = p_patient_id;

RETURN v_total_dosage;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END;
/
DECLARE
v_total_dosage INTEGER;
BEGIN
v_total_dosage := get_total_dosage('Amoxicillin', 2);
DBMS_OUTPUT.PUT_LINE('Total Dosage: ' || v_total_dosage);
END;
/

3. Description
This is a PL/SQL function named "calculate_total_premium" that takes an input parameter
"p_policy_number" of data type INTEGER and returns an output parameter "v_total_premium" of data type
INTEGER.

The SELECT statement retrieves the values of "coverage_plan" and "premium_amount" from the
"HealthInsurance" table where the policy number matches the input parameter. The retrieved values are then
stored in the variables "v_coverage_plan" and "v_premium_amount".

CREATE OR REPLACE FUNCTION calculate_total_premium(

p_policy_number IN INTEGER
) RETURN INTEGER AS
v_coverage_plan HealthInsurance.coverage_plan%TYPE;
v_premium_amount HealthInsurance.premium_amount%TYPE;
v_total_premium INTEGER;
BEGIN
SELECT coverage_plan, premium_amount
INTO v_coverage_plan, v_premium_amount
FROM HealthInsurance
WHERE policy_number = p_policy_number;

v_total_premium := v_premium_amount + v_coverage_plan * 1000; -- Adding $1000 per coverage plan

RETURN v_total_premium;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Health insurance policy with number: ' || p_policy_number || ' not
found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred while calculating total premium for policy with number:
' || p_policy_number);
RAISE;
END;
/

SELECT calculate_total_premium(1010) AS total_premium

FROM dual;
 4. Description
This is a PL/SQL function named "get_clinic_name" that takes an input parameter "p_branch_id" of data
type INTEGER and returns an output parameter "v_name" of data type VARCHAR2.

The SELECT statement retrieves the value of "name" from the "Clinics" table where the branch ID matches
the input parameter. The retrieved value is then stored in the variable "v_name".

CREATE OR REPLACE FUNCTION get_clinic_name(p_branch_id IN INTEGER) RETURN

VARCHAR2
IS
v_name Clinics.name%TYPE;
BEGIN
SELECT name INTO v_name
FROM Clinics
WHERE branch_id = p_branch_id;
RETURN v_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'Error: Clinic not found.';
WHEN OTHERS THEN
-- Handle other exceptions
RETURN 'Error: Failed to retrieve clinic name.';
END;
/

SELECT get_clinic_name(2) FROM dual;

 5. Description
This function calculates the total salary for an employee in the Payroll table based on their employee ID

CREATE OR REPLACE FUNCTION calculate_total_salary(p_employee_id IN INTEGER

) RETURN INTEGER AS
v_salary INTEGER;
v_hours_worked INTEGER;
BEGIN
SELECT salary, hours_worked INTO v_salary, v_hours_worked
FROM Payroll
WHERE employee_id = p_employee_id;
IF v_salary IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Employee with ID: ' || p_employee_id || ' not found in Payroll table.');
RETURN NULL;
ELSE
v_salary := v_salary + v_hours_worked * 10; -- Assuming hourly rate of $10
RETURN v_salary;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee with ID: ' || p_employee_id || ' not found in Payroll table.');
RETURN NULL;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred while calculating total salary for employee with ID: ' ||
p_employee_id);
RAISE;
END;
/
SELECT calculate_total_salary(10003) FROM dual;
TRIGGERS:
1. Description
This trigger is designed to be fired before inserting or updating rows in the HealthInsurance table. It checks
if the coverage_plan value being inserted or updated is negative, and if so, it raises an application error with
the message "Coverage plan cannot be negative" and the error code -20001. This trigger helps to ensure that
only positive values are allowed for the coverage_plan column in the HealthInsurance table.

CREATE OR REPLACE TRIGGER trg_check_coverage_plan

BEFORE INSERT OR UPDATE ON HealthInsurance
FOR EACH ROW
BEGIN
IF :NEW.coverage_plan < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Coverage plan cannot be negative');
END IF;
END;
/
update healthInsurance set coverage_plan = -1 where policy_number = 1002;
 2. Description
This trigger will be automatically executed before each insert or update operation on the "Employee_Data"
table. It will update the "pay_date" column with the current date, calculate the "salary" based on the
"hours_worked" column assuming an hourly rate of $20, and raise an error if the calculated salary is not
within the allowed range of $12,000 to $40,000.

CREATE OR REPLACE TRIGGER trg_employee_data

BEFORE INSERT OR UPDATE ON Employee_Data
FOR EACH ROW
BEGIN

:NEW.pay_date := SYSDATE;

:NEW.salary := :NEW.hours_worked * 20;

IF :NEW.salary < 12000 OR :NEW.salary > 40000 THEN

RAISE_APPLICATION_ERROR(-20001, 'Salary must be between $12,000 and $40,000.');
END IF;
END;
/
update employee_data set salary = 5000 where employee_id = 10005;
 3. Description
This trigger is set to execute "BEFORE UPDATE" on the "Patients" table, and it will be invoked once for
each row that is updated in the "Patients" table. The trigger logic includes examples of validating the
updated data and setting default values using the :NEW references, which represent the new values being
updated

CREATE OR REPLACE TRIGGER Patients_Before_Update

BEFORE UPDATE ON Patients
FOR EACH ROW
BEGIN

IF :NEW.contact_details IS NULL THEN

RAISE_APPLICATION_ERROR(-20001, 'Contact details cannot be set to NULL.');
END IF;
END;
/
update patients set contact_details = null where patient_id = 2
PACKAGE:
1. Description
The "Employee_Data_Package" is a PL/SQL package that contains procedures and functions to update and
calculate employee data related to salary and bonus.
The package has the following procedures and functions:
Procedure "Update_Salary": This procedure takes an employee ID as input and updates the salary of the
employee based on the hours worked. If the employee has worked more than 40 hours, a bonus is calculated
and added to the salary. The updated salary and bonus information are stored in the "Employee_Data" table.
The procedure also displays a message with the employee ID and the updated salary with the bonus using
the "DBMS_OUTPUT" package.

Function "Calculate_Bonus": This function takes an employee ID as input and calculates the bonus amount
based on the employee's salary and designation. The bonus amount is determined based on different
percentages for different designations (Manager, Supervisor, and other employees). The calculated bonus
amount is returned by the function.

The package body contains the implementation of these procedures and functions using PL/SQL block,
which includes variable declarations, SQL queries to fetch and update data in the "Employee_Data" table,
and conditional statements to calculate bonus based on employee's salary and designation.

CREATE OR REPLACE PACKAGE Employee_Data_Package AS

-- Procedure to update employee's salary based on hours worked
PROCEDURE Update_Salary(p_employee_id IN INTEGER);

-- Function to calculate bonus based on employee's salary and designation

FUNCTION Calculate_Bonus(p_employee_id IN INTEGER) RETURN INTEGER;

END Employee_Data_Package;
/
CREATE OR REPLACE PACKAGE BODY Employee_Data_Package AS
-- Procedure to update employee's salary based on hours worked
PROCEDURE Update_Salary(p_employee_id IN INTEGER) AS
v_employee_salary INTEGER;
v_hours_worked NUMBER;
v_bonus INTEGER;
BEGIN
-- Get employee's current salary and hours worked
SELECT salary, hours_worked INTO v_employee_salary, v_hours_worked
FROM Employee_Data
WHERE employee_id = p_employee_id;
-- Update employee's salary based on hours worked
IF v_hours_worked > 40 THEN
v_bonus := v_hours_worked - 40;
v_employee_salary := v_employee_salary + v_bonus;
UPDATE Employee_Data
SET salary = v_employee_salary, pay_date = SYSDATE
WHERE employee_id = p_employee_id;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || p_employee_id || ' - Salary updated successfully
with bonus: ' || v_bonus);
ELSE
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || p_employee_id || ' - No bonus for hours worked less
than 40.');
END IF;
END Update_Salary;
-- Function to calculate bonus based on employee's salary and designation
FUNCTION Calculate_Bonus(p_employee_id IN INTEGER) RETURN INTEGER AS
v_employee_salary INTEGER;
v_designation VARCHAR2(25);
v_bonus INTEGER;
BEGIN
-- Get employee's current salary and designation
SELECT salary, designation INTO v_employee_salary, v_designation
FROM Employee_Data
WHERE employee_id = p_employee_id;
-- Calculate bonus based on employee's salary and designation
IF v_designation = 'Manager' THEN
v_bonus := v_employee_salary * 0.2;
ELSIF v_designation = 'Supervisor' THEN
v_bonus := v_employee_salary * 0.1;
ELSE
v_bonus := v_employee_salary * 0.05;
END IF;
RETURN v_bonus;
END Calculate_Bonus;
END Employee_Data_Package;
/
ASSUMPTIONS
a. Patients visit clinics to consult with doctors, get prescribed medications, and receive
vaccinations, and details about the type, dosage, and date of vaccination are recorded
in the database.
b. The doctor_name column stores the name of the doctor, and this field is mandatory (NOT
NULL). and the experience column stores the number of years of experience for each
doctor, with a minimum of 2 years and a maximum of 35 years.
c. Health insurance policies are offered by the healthcare organization to patients, and the
policies cover a certain amount of medical expenses based on the plan chosen.
d. The database tracks the medication inventory and ensures that the medicine is not
expired before dispensing it to the patients.
e. The employee_name column in the employees table stores the name of each employee,
and this field is mandatory (NOT NULL). The salary column stores the salary
information for each employee, with a minimum of 12,000 and a maximum of 40,000.
f. The database also maintains a record of the payroll of the employees working in
the healthcare organization.
g. The type column in the clinics table stores information about the type of clinic, such
as oncology, neurology, and cardiology.
h. The Vaccinations table tracks vaccinations given to patients as part of the clinical trials,
such as for flu or COVID-19, as part of monitoring patient health and response to
medication.
i. The Location table tracks the addresses of doctors and employees in the organization.
j. The dosage column is mandatory (NOT NULL) as it represents the key aspect of
the prescription.
k. Employee and Payroll have employee_id which is unique.
l. Patients may have many prescriptions, each prescription belongs to one patient.
m. Patients have multiple health insurance policies but every policy belongs to one patient.
n. Doctors prescribe multiple vaccinations to patients, and patients can have
multiple vaccinations but every vaccination has a doctor who prescribed it to a
particular patient.
o. Clinics have multiple medicines, and medicines can be available at multiple clinics.
p. Every doctor is an employee but not every employee is a doctor.
q. One employee can work at multiple locations.
r. Each clinic can have recurring patients visiting at all times.
s. The Inventory table stores information about the quantity of medicines in stock at
various pharmacy locations. It has columns for medicine_name, location_id, and
quantity.
t. The Inventory table may be updated frequently, with new records added as new medicines
are introduced or existing records updated as stock levels change.
u. The Inventory table may be queried frequently to provide information about which
medicines are in stock at each location, how many units are available, and when
additional stock may be needed.
v. The queries involve joining tables based on common columns such as location
ID, department ID, medicine ID, doctor ID, and patient name.
w. The queries use aggregate functions such as SUM, COUNT, AVG, MAX, and MIN to
calculate the total quantity, total salary, number of patients, highest and lowest salaries,
and average age.
x. The queries use JOINs to retrieve data from multiple tables based on their relationships.
y. The queries use GROUP BY and HAVING clauses to group the data by specific attributes
 and filter the results based on specific conditions.
z. The queries use the ORDER BY clause to sort the results based on specific attributes
and in a specific order.
aa. The queries use UPDATE statements to modify data in the tables present in the database.
bb. The queries use DELETE statements to eliminate data from the tables present in
the database.
cc. Using PROCEDURES code block will be executed when explicitly called by the
user.
dd. input parameters are valid and passed correctly.
ee. user has necessary privileges to execute the procedure.
ff. Using TRIGGERS database operation (insert, update, or delete) will occur on the
specified table.
gg. Data in the table is consistent and complies with constraints and data types.
hh. user has necessary privileges to execute the trigger.
ii. Using FUNCTIONS that the code block will be executed when called by the user.
jj. input parameters are valid and passed correctly.
kk. output value is returned correctly and matches the expected data type and format.
ll. user has necessary privileges to execute the function.
mm. PACKAGES uses Objects and procedures/functions declared in the
specification will be valid when implemented.
nn. package specification will be used and referenced correctly by other programs or
objects.
oo. package specification complies with naming conventions and follows best
practices.

INDIVIDUAL CONTRIBUTIONS TO THE PROJECT

Vineesha Reddy Konda 11639367

a. By reading the requirements, I have gathered a few entities named patients and health insurance.
b. I have listed the attributes of each of the entities which are listed below:

i. ENTITY: patients.

ATTRIBUTES: patient_id, name, dob, contact_details, medicine_id

ii. ENTITY: health insurance.

ATTRIBUTES: policy_number, type, medical_history, coverage_plan, premium_amount.

c. I described a few 1-to-many and many-to-many binary relationships

i. Patients and Prescriptions: A patient can have multiple prescriptions, but each prescription belongs
to only one patient.
ii. Patients and Health insurance: A patient can have multiple insurance policies, but each insurance
policy belongs to only one patient.
 iii. Patients and Vaccinations: A patient can have multiple vaccinations, but each vaccination belongs
to only one patient.
iv. Patients and Clinics: A patient can visit multiple clinics and each clinic can have multiple
recurring patients.

d. I created attributes for patients and health insurance in ER Diagram and helped my team members in
drawing other entities and connected many to many relationships using the draw.io tool.

e. I created tables for patients and health insurance in this project and created Not-Null, unique
constraints, and check constraints for the premium amount column as mentioned in the project.

f. I created a database schema for patients and health insurance tables that includes a primary key, Not
Null, unique key, and check constraints.
g. I created a check constraint for the premium amount column in the health insurance table for
premium_amount should be less than or equal to 10000usd and greater than or equal to 1500usd.
h. I have inserted ten tuples for patients and health insurance tables.

i. I contributed to my team in solving the 7 complex query questions and running them in SQL.
j. I have designed and solved queries for Patients and Health insurance tables.
k. I have designed update and delete statements for Patients and Health insurance tables.
l. I contributed to my team in solving the Procedures, Triggers, Functions and Packages
Queries and running them in SQL Developer.
m. I have designed and solved queries for updating the contact details of a clinic based on its
branch ID from clinics Tables Using Procedure
n. I have designed and solved queries for Getting the name of a clinic based on its branch ID
from clinics Tables Using Functions.