Database-management-DBMS Lab 3 Solved

Consider the hospital management systems used in assignment for Lab Day 2. For this system, we need to answer the set of query requirements mentioned below. Accordingly, update the ER-Diagram you have drawn and submitted against Lab Day 2 assignment. From the ER-Diagram, derive the tables using correct rules for relational model generation from ER model. Draw the current ER diagram and the tables on a piece of paper. For the ER diagram, do NOT use Crow’s feet notation. Clearly mark the columns, primary keys and foreign keys as appropriate for each table. This forms your database schema.  

 

Create the tables as drawn on the paper in the database using appropriate Create table SQL statements. You may choose appropriate data types for each column. Write SQL statements to answer the queries given below. You may test the SQLs by inserting appropriate rows in the tables. Note that, your database schema must correspond to the (updated) ER-Diagram you are submitting.   

 

No need to do anything on ERDPlus.

 

Write your roll number and name on the piece of paper where you draw the ER diagram and database schema.  

  

Through Moodle, submit a zip file containing the image files for the ER Diagram & the database schema and a text file containing your Create & Select SQL statements. (Name it as Lab3_<Roll_no>.zip).  

[10 Marks for ER model + 20 Marks for Database schema + 10 Marks for Create Table SQLs + (5×8=40 marks for Select SQLs)]

 

1.      List the patients (Patient_Id, Name) so far admitted under each doctor (Doctor_Id, Name).

2.      List the patients (Patient_Id, Name) currently admitted under each doctor (Doctor_Id, Name), i.e., the patients who have been admitted but not yet been released.

3.      List the patients (Patient_Id, Name) so far admitted under each doctor (Doctor_Id, Name) who have been admitted under more than one department

4.      List the patients (Patient_Id, Name) so far admitted under each doctor (Doctor_Id, Name) who have not been admitted in the same department to which the doctor belongs.

5.      List the patients (Patient_Id, Name) so far admitted under each doctor (Doctor_Id, Name) who have been admitted in at least one department to which the doctor does not belong.

6.      List the visit details of patients (Patient_Id, Name, Date of Admission, Date of Release). Date of Release will be NULL if the patient is currently admitted. Note that, if the same person visits the hospital more than once, we need to display details of each visit (hint: if the same patient visits the hospital again, he/she will be given the same patient id). Bonus of 10 Marks if you can handle the situation in which a patient is admitted on a day, released on the same day and is again admitted on the same day (total for today’s assignment cannot exceed full marks with the bonus).  

7.      List for each doctor (Doctor_Id, Name), the number of patients currently admitted under him.    

8.      For each state, for each department having more than 5 wards, list the number of patients currently admitted (Only for those departments which have more than 2 patients currently admitted).   

 

[Penalty for plagiarism/copying: You will be awarded 0 for all the problems for the lab day and an additional 5 marks will be deducted out of the total of 40 in Lab. All persons involved will be awarded the same penalty irrespective of who has copied from whom]