● Create the following tables for HMS(Hospital Management System):
The Doctor_master table contains information about doctors in the hospital.
DOCTOR_MASTER(doctor_id,doctor_name,dept)
Column Name Data type & Data Description
size
doctor_id Varchar(15) doctorid must be unique and not null
doctor_name Varchar2(15) Doctor name should be not null
Dept Varchar2(15) Dept name should be not null
Sample Data:
doctor_id doctor_nam dept
e
D0001 Ram ENT
D0002 Rajan ENT
D0003 Smita Eye
D0004 Bhavan Surgery
D0005 Sheela Surgery
D0006 Nethra Surgery
The room_master table contains information about room availability in the hospital.
ROOM_MASTER(room_no, room_type, status)
Column Name Data type & Data Description
size
room_no Varchar(15) roomno must be unique and not null
room_type Varchar2(15) room type name should be not null
status Varchar2(15) status name should be not null
Sample Data:
room_no room_type status
R0001 AC occupied
R0002 Suite vacant
R0003 NonAC vacant
R0004 NonAC occupied
R0005 AC vacant
R0006 AC occupied
The patient_master table contains information about patients in the hospital.
PATIENT_MASTER(pid, name,age,weight,gender,address,phoneno,disease,doctorid)
Column Name Data type & Data Description
size
pid Varchar(15) pid must be unique and not null
name Varchar2(15) name should be not null
age number(15) age should be not null
Weight number(15) weight should be not null
Gender Varchar(10) gender should be not null
Address Varchar(50) address should be not null
phoneno varchar(10) phoneno should be not null
Disease Varchar(50) disease should be not null
Doctor_id Varchar(5) Must be an existing doctor
Sample Data:
pid name age weight gender address phoneno disease Doctor_id
P0001 Gita 35 65 F Chennai 9867145678 Eye D0003
Infection
P0002 Ashish 40 70 M Delhi 9845675678 Asthma D0003
P0003 Radha 25 60 F Chennai 9867166678 Pain in D0005
heart
P0004 Chandra 28 55 F Bangalor 9978675567 Asthma D0001
e
P0005 Goyal 42 65 M Delhi 8967533223 Pain in D0004
Stomach
The room_allocation table contains information about the allocation of room to the patients in
the hospital.
ROOM_ALLOCATION(room_no, pid, admission_date,release_date)
Column Name Data type & Data Description
size
room_no Varchar(15) Must be an existing rooms
pid Varchar2(15) Must be an existing patients
admission_date date Admission date should be not null
Release_date date
Sample Data:
room_no pid admission_date Release_date
R0001 P0001 15-oct-16 26-oct-16
R0002 P0002 15-nov-16 26-nov-16
R0002 P0003 01-dec-16 30-dec-16
R0004 P0001 01-jan-17 30-jan-17
NOTE:
You are supposed to fill in the given records in DOCTOR_MASTER, ROOM_MASTER,
PATIENT_MASTER and ROOM_ALLOCATION tables following these rules:
1. Identify the primary key and foreign key (if applicable) in each table.
2. Take care of the constraints and the relationships among the tables.
QUERIES
Query #1: Display the patients who were admitted in the month of january.
Query #2: Display the female patient who is not suffering from ashma
Query #3: Count the number of male and female patients.
Query #4: Display the patient_id,patient_name, doctor_id, doctor_name, room_no,
room_type and admission_date.
Query #5: Display the room_no which was never allocated to any patient.
Query #6: Display the room_no, room_type which are allocated more than once.
PRIME For Internal Use Only Version 4.2