Hospital Management Database Schema
Part 1: Design the Database
Database Name : sunnydb
(i)The databases fields together with their datatypes : -
1. Fields for person table
Id:INT, (Primary Key)
firstName:VARCHAR,
middleName:VARCHAR,
surname:VARCHAR,
email:VARCHAR,
contactNumber:BIGINT,
dateRelated toBirth:Date,
gender:VARCHAR,
emergancyContactNumber:BIGINT,
addressId:INT (foreign Key related to Address table)
2. Fields for address table
Id:INT, (Primary Key)
country:VARCHAR,
state:VARCHAR,
city:VARCHAR,
street:VARCHAR,
house_number:VARCHAR
3. Fields for staff_type table
id:INT, (Primary Key)
type:VARCHAR
4. Fields for staff_grade table
id:INT, (Primary Key)
grade:VARCHAR
5. Fields for staff_category table
id:INT, (Primary Key)
6. Fields for staff table
id:INT, (Primary Key)
staffCategoryId:INT ( foreign key related to staff_category table)
staffTypeId:INT ( foreign key related to staff_type table)
staffGradeId:INT ( foreign key related to staff_grade table)
personId:INT ( foreign key related to person table)
7. Fields for er_shift table
id:INT,(Primary Key)
name: VARCHAR,
startTime:TIME,
endTIME: TIME,
inchargeStaffId:INT ( foreign key related to staff table)
8. Fields for staff table
id:INT, (Primary Key)
emergancyRoomShiftId: INT ( foreign key related to er_shift table)
staffId:INT (foreign key related to staff table)
9. Fields for patient table
id:INT, (Primary Key)
personId: INT ( foreign key related to person table)
admittedBy: INT(foreign key related to Staff Table)
supervisedBy: INT(foreign key related to Staff Table)
bedId: INT(foreign key related to Staff Table)
medicationId: INT(Foregin Key related to Medication table)
admittedDate: Date
age:INT
10. Fields for Medication table
id:INT
name:VARCHAR
dosage:Decimal
11. Fields for Bed Table
Id:INT(Primary Key)
bedNo:INT(UNIQUE Key)
supervisedBy:INT(foreign key related to Staff table)
Query Solutions:
Part3: Query the Database :-
--i)
Select p.firstName, p.surname inner join staff s
on assign.staffId=s.id inner join er_shift shift
on assign.emergancyRoomShifId= shift.id inner join staff_type type
on s.staffTypeId=s.id inner join person p on p.id=s.personId
where type.type='Nurse' and p.gender='female'
and date between '2018-10-01' and '2018-11-30';
--ii)
SELECT firstName FROM sunnydb.person where surname='Poon' and id in (select personId from sunnydb.petient where admittedDate='2018-08-31');
--iii)
Select ps.firstName, p.age, p.admittedDate from petient p inner join staff s
on p.personId=s.personId inner join person ps where ps.id=s.personId;
--iv)
select pr.id, pr.firstName, pr.surname from staff s inner join staff_type st on s.staffTypeId=st.id inner join petient p on p. supervisedBy =s.id inner join person pr on pr.id=s.personId
where st.type='Nurse' group by p.supervisedBy having (count(p.supervisedBy) > 2) ;