(NEED ANSWER URGENT IN 2 HOURS) Hospital Management System (HMS) Aim: Akrab Hospitalisamulti-specialtyhospitalthatincludesanumberofdepartments, rooms, doctors, nurses, compounders, and other staff working in the hospital. Patients havingdifferent kindsof ailments come tothehospital andget checkupdonefromthe concerneddoctors.If required they are admitted in the hospital and discharged after treatment. Theaimofthiscasestudyistodesignanddevelopadatabaseforthehospitaltomaintain the records of various departments, rooms, and doctorsin the hospital. It also maintains recordsof theregularpatients,patientsadmittedinthehospital,thecheckupofpatients donebythe doctors,thepatientsthathavebeenoperated,andpatientsdischargedfrom thehospital. 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,CancerDepartment,Corpse,etc.Thereisan OPDwherepatientscome andgetacard(thatis,entrycardofthepatient)forcheckupfrom the concerneddoctor.After makingentryinthecard,theygototheconcerneddoctors room and the doctor checks up their ailments. According to the ailments, the doctor eitherprescribesmedicineor admitsthe patient in the concerned department.The patient may choose either private or general room accordingtohis/herneed.Butbeforegetting admission in the hospital, the patient hasto fulfill certain formalities ofthe 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 doctorsin the hospital, namely,regular doctors andcall on doctors.Regular doctors are those doctorswho come to the hospital daily.Calls on doctors are those doctors who are called by the hospital if the concerned doctoris not available. Table Description: Following are the tables along with constraints used in Hospital Management database. 1. DEPARTMENT: Thistable consists of details about the various departments in the hospital. The information stored in this table includes department name, department location,andfacilitiesavailableinthatdepartment. Constraint: Department name will be unique for each department. 2. ALL_DOCTORS: Thistable storesinformation about all the doctors working for the hospital and the departmentsthey are associated with. Each doctoris given an identity numberstartingwithDR orDCprefixes 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 arereferredtobytheirdoctornumber.Thistablealsostorespersonaldetails of doctors like name, qualification, address, phone number, salary, date of joining,etc. Constraint:DoctorsnumberenteredshouldcontainDRonlyasaprefixandmust exist in ALL_DOCTORStable. 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 alsostored. Constraint:DoctorsnumberenteredshouldcontainDConlyasaprefixandmust exist in ALL_DOCTORStable. 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 detailslike 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 doctorforcheckup. Constraint: Patient numbershould begin with prefix PT. Sex should beM or F only. Doctorsname anddepartmentreferredmust exist. 6. PAT_CHKUP: This table stores the details about the patients who get treatment from thedoctorreferredto.Detailslikepatientnumberfrompatiententrytable, 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 patientisreferredforoperation,thefurther details are stored in PAT_OPR table and if patient is a regular patient to the hospital,the further details are stored in PAT_REGtable. 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,roomnumbermust be valid. 8. PAT_DIS:Anentryismadeinthistablewheneverapatient getsdischargedfrom the hospital. Each entry includes details like patient number, treatment given, treatment advice,paymentmade,modeofpayment,dateofdischarge,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 checkup and therewill beentry for patients each visit. 10. PAT_OPR: If patient is operated in the hospital, his/her details are stored in thistable. Information stored includes patient number, date of admission, date of operation, number of the doctor who conducted the operation, number of the Operation Theaterin whichoperationwas carriedout,typeofoperation, patients conditionbeforeandafter operation,treatment advice, etc. Constraint: Patient number should exist in PAT_ENTRY table. Department, doctor numbershould exist orshould 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, chargesperday,etc. Constraint: Roomnumbershould be unique. Roomtype can only beG or P and status can only be Y orN

a) Based on the information above, you need to draw ERD (Conceptual Database Design Model). (10 marks) b) Complete the ERD / EERD, you need to provide with us the Relational Database Schema (Logical Database Design Model). (