Case Study-1

Hospital Management System

AIM

XYZ hospital is a multi speciality hospital that includes a number of departments, rooms, doctors, nurses, compounders, and other staff working in the hospital. Patients having different kinds of ailments come to the hospital and get checkup done from the concerned doctors. If required they are admitted in the hospital and discharged after treatment.

The aim of this case study is to design and develop a database for the hospital to maintain the records of various departments, rooms, and doctors in the hospital. It also maintains records of the regular patients, patients admitted in the hospital, the check up of patients done by the doctors, the patients that have been operated, and patients discharged from the hospital.

DESCRIPTION

In hospital, there are many departments like Orthopedic, Pathology, Emergency, Dental, Gynecology, Anesthetics, I.C.U., Blood Bank, Operation Theater, Laboratory, M.R.I., Neurology, Cardiology, Cancer Department, Corpse, etc. There is an OPD where patients come and get a card (that is, entry card of the patient) for check up from the concerned doctor. After making entry in the card, they go to the concerned doctor’s room and the doctor checks up their ailments. According to the ailments, the doctor either prescribes medicine or admits the patient in the concerned department. The patient may choose either private or general room according to his/her need. But before getting admission in the hospital, the patient has to fulfill certain formalities of the hospital like room charges, etc. After the treatment is completed, the doctor discharges the patient. Before discharging from the hospital, the patient again has to complete certain formalities of the hospital like balance charges, test charges, operation charges (if any), blood charges, doctors’ charges, etc.

Next we talk about the doctors of the hospital. There are two types of the doctors in the hospital, namely, regular doctors and call on doctors. Regular doctors are those doctors who come to the hospital daily. Call on doctors are those doctors who are called by the hospital if the concerned doctor is not available.

TABLES DESCRIPTION

Following are the tables along with constraints used in Hospital Management database.

  1. DEPARTMENT: This table consists of details about the various departments in the hospital. The information stored in this table includes department name, department location, and facilities available in that department.

    Constraint: Department name will be unique for each department.

  2. ALL_DOCTORS: This table stores information about all the doctors working for the hospital and the departments they are associated with. Each doctor is given an identity number starting with DR or DC prefixes only.

    Constraint: Identity number is unique for each doctor and the corresponding department should exist in DEPARTMENT table.

  3. DOC_REG: This table stores details of regular doctors working in the hospital. Doctors are referred to by their doctor number. This table also stores personal details of doctors like name, qualification, address, phone number, salary, date of joining, etc.

    Constraint: Doctor’s number entered should contain DR only as a prefix and must exist in ALL_DOCTORS table.

  4. DOC_ON_CALL: This table stores details of doctors called by hospital when additional doctors are required. Doctors are referred to by their doctor number. Other personal details like name, qualification, fees per call, payment due, address, phone number, etc., are also stored.

    Constraint: Doctor’s number entered should contain DC only as a prefix and must exist in ALL_DOCTORS table.

  5. PAT_ENTRY: The record in this table is created when any patient arrives in the hospital for a check up. When patient arrives, a patient number is generated which acts as a primary key. Other details like name, age, sex, address, city, phone number, entry date, name of the doctor referred to, diagnosis, and department name are also stored. After storing the necessary details patient is sent to the doctor for check up.

    Constraint: Patient number should begin with prefix PT. Sex should be M or F only. Doctor’s name and department referred must exist.

  6. PAT_CHKUP: This table stores the details about the patients who get treatment from the doctor referred to. Details like patient number from patient entry table, doctor number, date of check up, diagnosis, and treatment are stored. One more field status is used to indicate whether patient is admitted, referred for operation or is a regular patient to the hospital. If patient is admitted, further details are stored in PAT_ADMIT table. If patient is referred for operation, the further details are stored in PAT_OPR table and if patient is a regular patient to the hospital, the further details are stored in PAT_REG table.

    Constraint: Patient number should exist in PAT_ENTRY table and it should be unique.

  7. PAT_ADMIT: When patient is admitted, his/her related details are stored in this table. Information stored includes patient number, advance payment, mode of payment, room number, department, date of admission, initial condition, diagnosis, treatment, number of the doctor under whom treatment is done, attendant name, etc.

    Constraint: Patient number should exist in PAT_ENTRY table. Department, doctor number, room number must be valid.

  8. PAT_DIS: An entry is made in this table whenever a patient gets discharged from the hospital. Each entry includes details like patient number, treatment given, treatment advice, payment made, mode of payment, date of discharge, etc.

    Constraint: Patient number should exist in PAT_ENTRY table.

  9. PAT_REG: Details of regular patients are stored in this table. Information stored includes date of visit, diagnosis, treatment, medicine recommended, status of treatment, etc.

    Constraint: Patient number should exist in patient entry table. There can be multiple entries of one patient as patient might be visiting hospital repeatedly for check up and there will be entry for patient’s each visit.

  10. PAT_OPR: If patient is operated in the hospital, his/her details are stored in this table. Information stored includes patient number, date of admission, date of operation, number of the doctor who conducted the operation, number of the operation theater in which operation was carried out, type of operation, patient’s condition before and after operation, treatment advice, etc.

    Constraint: Patient number should exist in PAT_ENTRY table. Department, doctor number should exist or should be valid.

  11. ROOM_DETAILS: It contains details of all rooms in the hospital. The details stored in this table include room number, room type (general or private), status (whether occupied or not), if occupied, then patient number, patient name, charges per day, etc.

    Constraint: Room number should be unique. Room type can only be G or P and status can only be Y or N.

E-R DIAGRAM

The E-R diagram of Hospital Management database is shown here.

bm01-ufig1
RELATIONAL DATABASE SCHEMA FOR CASE STUDY

The relational database schema for Hospital Management database is as follows:

  1. DEPARTMENT (D_NAME, D_LOCATION, FACILITIES)
  2. ALL_DOCTORS (DOC_NO, DEPARTMENT)
  3. DOC_REG(DOC_NO, D_NAME, QUALIFICATION, SALARY, EN_TIME, EX_TIME, ADDRESS, PH_NO, DOJ)
  4. DOC_ON_CALL (DOC_NO, D_NAME, QUALIFICATION, FS_PR_CL, PYMT_DU, ADDRESS, PH_NO)
  5. PAT_ENTRY (PAT_NO, PAT_NAME, CHKUP_DT, PT_AGE, SEX, RFRG_CSTNT, DIAGNOSIS, RFD, ADDRESS, CITY, PH_NO, DEPARTMENT)
  6. PAT_CHKUP (PAT_NO, DOC_NO, DIAGNOSIS, STATUS, TREATMENT)
  7. PAT_ADMIT (PAT_NO, ADV_PYMT, MODE_PYMT, ROOM_NO, DEPTNAME, ADMTD_ON, COND_ON, INVSTGTN_DN, TRMT_SDT, ATTDNT_NM)
  8. PAT_DIS (PAT_NO, TR_ADVS, TR_GVN, MEDICINES, PYMT_GV, DIS_ON)
  9. PAT_REG (PAT_NO, DATE_VIS, CONDITION, TREATMENT, MEDICINES, DOC_NO, PAYMT)
  10. PAT_OPR (PAT_NO, DATE_OPR, IN_COND, AFOP_COND, TY_OPERATION, MEDICINES, DOC_NO, OPTH_NO, OTHER_SUG)
  11. ROOM_DETAILS (ROOM_NO, TYPE, STATUS, RM_DL_CRG, OTHER_CRG)
IMPLEMENTATION IN SQL SERVER

We have used SQL server to create Hospital Management database. In this section, we will discuss how to create tables, insert values in tables, and retrieve data from tables.

Creating Tables

The CREATE TABLE commands for the relations of Hospital Management database along with the required constraints are specified here.

  1. CREATE TABLE DEPARTMENT 
    ( D_NAME VARCHAR(20) CONSTRAINT DNPRKY PRIMARY KEY,
  D_LOCATION VARCHAR(35),
  FACILITIES VARCHAR(50)
);
  2. CREATE TABLE ALL_DOCTORS 
    ( DOC_NO VARCHAR(8) CONSTRAINT DCPRKY PRIMARY KEY
      CONSTRAINT LIKETHESE CHECK (DOC_NO LIKE ‘DR%’ OR
      DOC_NO LIKE ‘HD%’ OR DOC_NO LIKE ‘DA%’ OR DOC_NO
      LIKE ‘DC%’),
      DEPARTMENT VARCHAR(20) CONSTRAINT ADDFRKYDMT
      REFERENCES DEPARTMENT
);
  3. CREATE TABLE DOC_REG 
    ( DOC_NO VARCHAR(8) CONSTRAINT DCRGFRKY REFERENCES
      ALL_DOCTORS CONSTRAINT DRLIKE CHECK (DOC_NO LIKE
      ‘DR%’),
  D_NAME VARCHAR(25),
  QUALIFICATION VARCHAR(10),
  SALARY NUMERIC(6),
  EN_TIME SMALLDATETIME,
  EX_TIME SMALLDATETIME,
  ADDRESS VARCHAR(50),
  PH_NO VARCHAR(20),
  DOJ SMALLDATETIME
);
  4. CREATE TABLE DOC_ON_CALL 
    ( DOC_NO VARCHAR(8) CONSTRAINT DCCLFRKY REFERENCES
      ALL_DOCTORS CONSTRAINT DCLIKE CHECK (DOC_NO LIKE
      ‘DC%’),
  D_NAME VARCHAR(25),
  QUALIFICATION VARCHAR(10),
  FS_PR_CL NUMERIC(5),
  PYMT_DU NUMERIC(6),
  ADDRESS VARCHAR(50),
  PH_NO VARCHAR(20)
);
  5. CREATE TABLE PAT_ENTRY 
    ( PAT_NO VARCHAR(8) CONSTRAINT PTPRKY PRIMARY KEY
      CONSTRAINT PTLIKE CHECK (PAT_NO LIKE ‘PT%’),
  PAT_NAME VARCHAR(25),
  CHKUP_DT SMALLDATETIME,
  PT_AGE NUMERIC(3),
  SEX VARCHAR(1) CONSTRAINT SEX CHECK (SEX IN
      (‘M’, ‘F’)),
  RFRG_CSTNT VARCHAR(25),
  DIAGNOSIS VARCHAR(50),
  RFD VARCHAR(1) CONSTRAINT RFYN CHECK (RFD
      IN (‘Y’, ‘N’)),
  ADDRESS VARCHAR(50),
  CITY VARCHAR(10),
  PH_NO VARCHAR(10),
  DEPARTMENT VARCHAR(20) CONSTRAINT PTETDPTNFRKYDPT
      REFERENCES DEPARTMENT
);
  6. CREATE TABLE PAT_CHKUP 
    ( PAT_NO VARCHAR(8) CONSTRAINT PRCKRFKY REFERENCES
      PAT_ENTRY,
  DOC_NO VARCHAR(8) CONSTRAINT PTRFDCPRKY REFERENCES
      ALL_DOCTORS,
  DIAGNOSIS VARCHAR(50),
  STATUS VARCHAR(10),
  TREATMENT VARCHAR(35)
);
  7. CREATE TABLE ROOM_DETAILS 
    ( ROOM_NO VARCHAR(6) CONSTRAINT RMNOPRKY PRIMARY KEY
      CONSTRAINT RNLIKE CHECK (ROOM_NO LIKE ‘RN%’),
  TYPE VARCHAR(1) CONSTRAINT WDINGP CHECK (TYPE
      IN (‘G’, ‘P’)),
  STATUS VARCHAR(1) CONSTRAINT INYN CHECK (STATUS
      IN (‘Y’, ‘N’)),
  RM_DL_CRG NUMERIC(4),
  OTHER_CRG NUMERIC(5)
);
  8. CREATE TABLE PAT_ADMIT 
    ( PAT_NO VARCHAR(8) CONSTRAINT PRADRFKYPE REFERENCES
    PAT_ENTRY CONSTRAINT PRADPRKY PRIMARY KEY,
  ADV_PYMT NUMERIC(6) CONSTRAINT ADVPYMT CHECK
    (ADV_PYMT>=500),
  MODE_PYMT VARCHAR(5) CONSTRAINT MDPYMT CHECK
    (MODE_PYMT IN (‘CHEQUE’, ‘CASH’)),
  ROOM_NO VARCHAR(6) CONSTRAINT PARMNOFRKYOCDT
    REFERENCES ROOM_DETAILS,
  DEPTNAME VARCHAR(20) CONSTRAINT PAFRKYDP REFERENCES
    DEPARTMENT,
  ADMTD_ON SMALLDATETIME,
  COND_ON VARCHAR(50),
  INVSTGTN_DN VARCHAR(20),
  TRMT_SDT SMALLDATETIME,
  ATTDNT_NM VARCHAR(20)
);
  9. CREATE TABLE PAT_DIS 
    ( PAT_NO VARCHAR(8) CONSTRAINT PADSFRKY REFERENCES
    PAT_ADMIT,
  TR_ADVS VARCHAR(50),
  TR_GVN VARCHAR(50),
  MEDICINES VARCHAR(50),
  PYMT_GV NUMERIC(7),
  DIS_ON SMALLDATETIME
);
  10. CREATE TABLE PAT_REG 
    ( PAT_NO VARCHAR(8) CONSTRAINT PARERFKY REFERENCES
      PAT_ENTRY,
  DATE_VIS SMALLDATETIME,
  CONDITION VARCHAR(30),
  TREATMENT VARCHAR(50),
  MEDICINES VARCHAR(50),
  DOC_NO VARCHAR(8) CONSTRAINT PAREDCPRKY REFERENCES
      ALL_DOCTORS,
  PAYMT NUMERIC(4)
);
  11. CREATE TABLE PAT_OPR 
    ( PAT_NO VARCHAR(8) CONSTRAINT PAOPRFKY REFERENCES
      PAT_ENTRY,
  DATE_OPR SMALLDATETIME,
  IN_COND VARCHAR(35),
  AFOP_COND VARCHAR(35),
  TY_OPERATION VARCHAR(40),
  MEDICINES VARCHAR(5),
  DOC_NO VARCHAR(8) CONSTRAINT PAOPDCPRKY REFERENCES
      ALL_DOCTORS,
  OPTH_NO VARCHAR(4),
  OTHER_SUG VARCHAR(30)
);

Inserting Values in Tables

We have shown insertion of only one record in all the tables. A number of records can be added (as and when required) in the same way.

  1. INSERT INTO DEPARTMENT 
    VALUES (‘Cardiology’, ‘FirstFloor’, ‘GENERAL AND SPECIALIZED SURGERY’);
  2. INSERT INTO ALL_DOCTORS 
    VALUES (‘DR001’, ‘Cardiology’);
  3. INSERT INTO DOC_REG 
    VALUES (‘DR001’, ‘DR.RAGINI ARAORA’, ‘MBBS, MD’, ‘25000’, ‘8:00:00
AM’, ‘8:00:00 PM’, ‘230-F, MAYUR VIHAR NEW
DELHI’, ‘2568931’, ‘02/02/2009’);
  4. INSERT INTO DOC_ON_CALL 
    VALUES (‘DC001’, ‘DR.SUSANTA PRADHAN’, ‘MBBS, MD’, ‘500’, ‘1000’, ‘120F-NOIDA’, ‘2587496’);
  5. INSERT INTO PAT_ENTRY 
    VALUES (‘PT002’, ‘MR.ALOK’, ‘02/02/2009’, ‘23’, ‘M’, ‘DR.BRIJESH’, ‘SWELLING IN JOINTS’, ‘Y’, ‘330F-NOIDA’, ‘NEW DELHI’, ‘7584289’, ‘Cardiology’);
  6. INSERT INTO PAT_CHKUP 
    VALUES (‘PT001’, ‘DR001’, ‘CRACK IN ANKLE BONE’, ‘SERIOUS’, ‘PLASTER’);
  7. INSERT INTO PAT_ADMIT 
    VALUES (‘PT001’, ‘150000’, ‘CASH’, ‘RN001’, ‘Cardiology’, ‘05/02/2 009’, ‘SERIOUS’, ‘LEAKAGE IN HEART’, ‘06/02/2009’, ‘MR. SUDHANSU’);
  8. INSERT INTO PAT_DIS 
    VALUES (‘PT001’, ‘REST FOR 6 MNTHS’, ‘OBSERVED FOR WEEK’, ‘ANTI ALLERGICS’, ‘275000’, ‘5/12/2009’);
  9. INSERT INTO PAT_REG 
    VALUES (‘PT001’, ‘8/2/2009’, ‘STABLE’, ‘CONTINUE THE MEDICINE’, ‘PAINKILLER’, ‘DR001’, ‘100’);
  10. INSERT INTO PAT_OPR 
    VALUES (‘PT001’, ‘6/2/2009’, ‘CONTROLLED’, ‘STABLE’, ‘OPEN HEART SURGERY’, ‘ANALR’, ‘DR001’, ‘10’, ‘UNDER OBSERVATION FOR 24 HOURS’);
  11. INSERT INTO ROOM_DETAILS 
    VALUES (‘RN001’, ‘G’, ‘N’, ‘500’, ‘100’);

SQL Queries

Query 1: Retrieve details of the doctor whose doctor number is DR001.

  SELECT *
  FROM ALL_DOCTORS
  WHERE DOC_NO = ‘DR001’;

Query 2: Retrieve details of the regular doctors having salary between 25000 and 30000.

  SELECT *
  FROM DOC_REG WHERE
  SALARY BETWEEN 25000 and 30000;

Query 3: Retrieve names of all the patients with elimination of duplicates.

  SELECT DISTINCT PAT_NAME
  FROM PAT_ENTRY;

Query 4: Retrieve number, name, qualification, present salary, salary incremented by 30%, address, phone number, and date of joining of all the regular doctors.

  SELECT DOC_NO, D_NAME, QUALIFICATION, SALARY, SALARY+SALARY*.30, ADDRESS, PH_NO, DOJ
  FROM DOC_REG;

Query 5: Retrieve details of all the patients admitted in Cardiology or Orthopedic department.

  SELECT *
  FROM PAT_ADMIT
  WHERE DEPTNAME IN (‘Cardiology’, ‘Orthopedic’);

Query 6: Retrieve name and age of all the patients and rename the columns as PATIENT_NAME and AGE, respectively in the output.

  SELECT PAT_NAME AS PATIENT_NAME, PT_AGE AS AGE
  FROM PAT_ENTRY;

Query 7: Retrieve the sum of salaries of the regular doctors.

  SELECT SUM (SALARY)
  FROM DOC_REG;

Query 8: Delete the tuple from ALL_DOCTORS where DOC_NO is DR002.

  DELETE FROM ALL_DOCTORS
  WHERE DOC_NO = ‘DR002’;

Query 9: Delete tuples of regular doctors having salary less than the average salary of regular doctors.

  DELETE FROM DOC_REG
  WHERE SALARY < (SELECT AVG (SALARY)
  FROM DOC_REG);

Query 10: Increase the fees per call of all the visiting doctors of the hospital by 2%.

  UPDATE DOC_ON_CALL
  SET FS_PR_CL = FS_PR_CL*1.02;

Query 11: Modify the location of Orthopedic department to FourthFloor.

  UPDATE DEPARTMENT
  SET D_LOCATION = ‘FourthFloor’
  WHERE D_NAME = ‘Orthopedic’;

Query 12: Retrieve all the doctor numbers, department name, department location, and facilities provided by the department.

  SELECT DOC_NO, DEPARTMENT, D_LOCATION, FACILITIES
  FROM ALL_DOCTORS, DEPARTMENT;

Query 13: Retrieve number and name of regular doctors along with the name of department in which they work.

  SELECT DOC_REG.DOC_NO, D_NAME, DEPARTMENT
  FROM ALL_DOCTORS INNER JOIN DOC_REG
  ON (ALL_DOCTORS.DOC_NO = DOC_REG.DOC_NO);

Query 14: Modify Query 13 to include names of those departments also for which there is no regular doctor.

  SELECT DOC_REG.DOC_NO, D_NAME, DEPARTMENT
  FROM ALL_DOCTORS LEFT OUTER JOIN DOC_REG
  ON (ALL_DOCTORS.DOC_NO = DOC_REG.DOC_NO);

Query 15: Retrieve the maximum, minimum, and average salary of regular doctors.

  SELECT MAX (SALARY), MIN (SALARY), AVG (SALARY)
  FROM DOC_REG;

Query 16: Find the union of the names of regular doctors and names of call on doctors without eliminating the duplicates.

  (SELECT D_NAME
  FROM DOC_REG)
  UNION ALL
  (SELECT D_NAME
  FROM DOC_ON_CALL);

Query 17: Find the union of name of regular doctor with doctor number DR001 and the name of doctor called by hospital with doctor number DC001.

  (SELECT D_NAME
  FROM DOC_REG
  WHERE DOC_NO=‘DR001’)
  UNION
  (SELECT D_NAME
  FROM DOC_ON_CALL
  WHERE DOC_NO=‘DC001’);

Query 18: Find the union of regular doctors with salary less than 31000 and the regular doctors with salary greater than 20000.

  (SELECT *
  FROM DOC_REG
  WHERE SALARY<31000)
  UNION
  (SELECT *
  FROM DOC_REG
  WHERE SALARY>20000);

Query 19: Retrieve names of those patients who have been operated by the doctor having doctor number DR002.

  SELECT PAT_NAME
  FROM PAT_ENTRY
  WHERE PAT_NO IN (SELECT PAT_NO
               FROM PAT_OPR
               WHERE DOC_NO = ‘DR002’);

Query 20: Retrieve the qualification and average salary of the regular doctors grouped on the basis of qualification and having minimum salary greater than 20000.

  SELECT QUALIFICATION, AVG (SALARY)
  FROM DOC_REG
  GROUP BY QUALIFICATION
  HAVING MIN (SALARY)>20000;

Query 21: Retrieve details of the regular doctors with minimum salary.

  SELECT *
  FROM DOC_REG
  WHERE SALARY = (SELECT MIN (SALARY) FROM DOC_REG);

Query 22: Retrieve number and names of regular patients of the doctor with doctor number DR002.

  SELECT P1.PAT_NO, P2.PAT_NAME
  FROM PAT_REG AS P1 INNER JOIN PAT_ENTRY AS P2
  ON (P1.PAT_NO = P2.PAT_NO)
  WHERE P1.DOC_NO = ‘DR002’;

Query 23: Retrieve number and names of all those patients who have been discharged on the same date as the patient having patient number PT001.

  SELECT P1.PAT_NO, P2.PAT_NAME
  FROM PAT_DIS AS P1 INNER JOIN PAT_ENTRY AS P2
  ON (P1.PAT_NO = P2.PAT_NO)
  WHERE P1.DIS_ON = ( SELECT DIS_ON
                 FROM PAT_DIS
                 WHERE PAT_NO = ‘PT001’);

Query 24: Retrieve patient number and room number of all occupied private rooms.

  SELECT P.PAT_NO, R.ROOM_NO
  FROM PAT_ADMIT AS P INNER JOIN ROOM_DETAILS AS R
  ON (P.ROOM_NO = R.ROOM_NO)
  WHERE R.TYPE = ‘P’ AND R.STATUS = ‘Y’;

Query 25: Create a view containing details of regular doctors having qualification MBBS or MD.

  CREATE VIEW DOC_REG_1
  AS SELECT *
  FROM DOC_REG
  WHERE QUALIFICATION IN (‘MBBS’, ‘MD’);
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset