1. Create all tables in Hospital database as per the requirement given below;

a) The primay key for each table to be created as specified (refer database)

b) Appropriate data type and size should be chosen for each attribute

c) Appropriate integrity constraints should be used while creating tables

(NULL, NOT NULL, FOREIGN KEY, CHECK) - Refer Figure 1

d) The values for some attributes should be as follows; include appropriate

CHECK constraints to achieve them.

i) Primary key values should be created with uniformity. For example,

Doc_ID can be like 'D0001' [five characters long and start with 'D'],

Staff_ID like 'S0001', Pres_ID like 'PR00001' and so on.

ii) Attributes and permitted values (you can decide and include such

values wherever required)

2. Populate each table with appropriate, valid and meaningful data.

3. Add some attributes with few tables and justify the additions.

4. Write DML queries to achieve the following;

a) Find the details of all doctors.

b) Display all the hospital bill details.

c) List the doctors who are specialized in 'Cardiology' and 'Neurology'

d) List all the appointments made for consultation room number 111, on '11-Jan-

2020'.

e) Display all the test types that have the values in the range of 25 and 75.

f) Find the diagnosis details of the patient with prescription id 'PR00012'.

g) Display the name of the patients whose gender is female or the contact number is 9878987890.

h) Find the staff name and staff id who are not working in the department 'D102'

i) Find the patients who are admitted on '01-May-2020' in the bed 100.

j) Delete the test results that are 'Positive'

k) Increase the discount with 5% more for all the patients whose bill amount is greater than 100000.

l) Change the HOD of cardiology department with doctor 'D0003'

m) Delete the prescribed medicines records that have the brand name 'XYZ'

n) Modify the low value and high value to 10 and 30 respectively for the clinical test 'urine'

o) Update the contact number of all staffs who are in the category 'Nurse'

p) Delete the staff records that have designations 'junior attender' or 'technician' and belongs to the department 'D190'.

Transcribed Image Text:

Attributes Table Qualification Doctor Specialist Doctor Dept Name Department Estd_Date Category Designation Staff Department Staff Gender Start_time Patient In Patient Permitted values 'MBBS', 'MS', 'MD', 'BDS', 'MDS' 'Ophthalmology', 'Diabetes', 'Cardiology', 'General medicine', ... Cardiology', 'Intensive care unit' 'Neurology' 'Oncology' 'Obstetrics and gynaecology' 'Diabetes'... Should be later than '01-Jan-2010' 'Nurse', 'Lab technician', 'Attender', 'Helper'... 'Staff nurse', 'Head nurse', 'Technician', 'Senior technician', 'Senior attender', 'Junior attender' 'M', 'F', and 'T' (for third gender) Date in start_time attribute should not be older than the date_of_admission value. Also, it should not be later than End_time. Description Test Types You can use the names of tests like 'Blood test', 'Urine test', etc. Attributes Table Qualification Doctor Specialist Doctor Dept Name Department Estd_Date Category Designation Staff Department Staff Gender Start_time Patient In Patient Permitted values 'MBBS', 'MS', 'MD', 'BDS', 'MDS' 'Ophthalmology', 'Diabetes', 'Cardiology', 'General medicine', ... Cardiology', 'Intensive care unit' 'Neurology' 'Oncology' 'Obstetrics and gynaecology' 'Diabetes'... Should be later than '01-Jan-2010' 'Nurse', 'Lab technician', 'Attender', 'Helper'... 'Staff nurse', 'Head nurse', 'Technician', 'Senior technician', 'Senior attender', 'Junior attender' 'M', 'F', and 'T' (for third gender) Date in start_time attribute should not be older than the date_of_admission value. Also, it should not be later than End_time. Description Test Types You can use the names of tests like 'Blood test', 'Urine test', etc.

Answer rating: 100% (QA)

SQL code for creating and populating the tables in the Hospital database sql CREATE TABLE Doctor DocID CHAR5 NOT NULL Name VARCHAR50 NOT NULL Speciali... View the full answer