MySQL Project Homework Help Solution

1. Creating the database tables

a) Patient table= It tells about the patient details.

CREATE TABLE Patient (pid INT NOT NULL AUTO_INCREMENT, PateintName INT NOT NULL, PateintEmail VARCHAR(25) NOT NULL, PRIMARY KEY (pid) )

b) Doctor table= It tells about the doctor details.

CREATE TABLE Doctor ( Did INT NOT NULL AUTO_INCREMENT, Dname VARCHAR(25) NOT NULL, Specialization VARCHAR(30) NOT NULL, Dphone INT NOT NULL, PRIMARY KEY (Did))

c) Room table= It shows Room details.

CREATE TABLE Room ( Roomid INT NOT NULL AUTO_INCREMENT, RoomNum INT NOT NULL, PRIMARY KEY (Roomid))

d) Service table= It shows the services provided by hospital to the patients.

CREATE TABLE Service ( Serviceid INT NOT NULL AUTO_INCREMENT, ServiceType VARCHAR(20) NOT NULL, PRIMARY KEY (Serviceid) )

e) Appointment table= Whenever a patient books an appointment in the hospital, a appointment table is created.

CREATE TABLE Appointment ( Date DATE NOT NULL, appid INT NOT NULL AUTO_INCREMENT, Charge VARCHAR(20) NOT NULL, pid INT NOT NULL, Did INT NOT NULL, Serviceid INT NOT NULL, Roomid INT NOT NULL, PRIMARY KEY (appid))

f) Nurse table= It show the nurses infromation working in the hospital.

CREATE TABLE Nurse ( `nurseid` INT NOT NULL AUTO_INCREMENT , `nurse_name` VARCHAR(25) NOT NULL , `doctor_id` INT NOT NULL , PRIMARY KEY (`nurseid`))

2. Establishing table relationships

a) Appointment table will have relationship with Patient, Doctor, Room, Service tables by having their primary key as its foreign key.

ALTER TABLE `appointment` ADD FOREIGN KEY (`Did`) REFERENCES `doctor`(`Did`) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE `appointment` ADD FOREIGN KEY (`pid`) REFERENCES `patient`(`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE `appointment` ADD FOREIGN KEY (`Roomid`) REFERENCES `room`(`Roomid`) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE `appointment` ADD FOREIGN KEY (`Serviceid`) REFERENCES `service`(`Serviceid`) ON DELETE RESTRICT ON UPDATE RESTRICT;

b) Nurse table will have relationship with Doctor table by having its primary as its foreign key.

ALTER TABLE `nurse` ADD FOREIGN KEY (`doctor_id`) REFERENCES `doctor`(`Did`) ON DELETE RESTRICT ON UPDATE RESTRICT;

3. Inserting data into the tables

a) Patient table

INSERT INTO `patient` (`pid`, `PateintName`, `PateintEmail`) VALUES (NULL, 'Anil Kapoor', 'anil@gmail.com'), (NULL, 'Boney kapoor', 'boney@gmail.com'), (NULL, 'Chetan bhagat', 'chetan@gmail.com'), (NULL, 'Don Khan', 'don@gmail.com'), (NULL, 'Farhan Akthar', 'farhan@gmail.com'), (NULL, 'Shikhar dhawan', 'shikhar@gmail.com'), (NULL, 'Varun', 'varun@gmail.com'), (NULL, 'Arun', 'Arun@gmail.com'), (NULL, 'Saalu', 'saalu@gmail.com'), (NULL, 'Neha', 'Neha@gmail.com'), (NULL, 'Ram', 'Ram@gmail.com');

b) Doctor Table

INSERT INTO `doctor` (`Did`, `Dname`, `Dmailid`) VALUES (NULL, 'Andrew', 'a@gmail.com'), (NULL, 'Barun', 'b@gmail.com'), (NULL, 'Chetan', 'c@gmail.com'), (NULL, 'Dhawan', 'd@gmail.com'), (NULL, 'Elias', 'e@gmail.com'), (NULL, 'Fulhum', 'f@gmail.com'), (NULL, 'Gandhi', 'g@gmail.com'), (NULL, 'Hardik', 'h@gmail.com'), (NULL, 'Jakob', 'j@gmail.com'), (NULL, 'Sumit', 's@gmail.com');

c) Room table

INSERT INTO `room` (`Roomid`, `RoomNum`) VALUES (NULL, '101'), (NULL, '102'), (NULL, '103'), (NULL, '104'), (NULL, '105'), (NULL, '106'), (NULL, '107'), (NULL, '108'), (NULL, '109'), (NULL, '110');

d) Service table

INSERT INTO `service` (`Serviceid`, `ServiceType`) VALUES (NULL, 'Common Cold'), (NULL, 'Headache'), (NULL, 'fever'), (NULL, 'Blood Pressure'), (NULL, 'Yearly Checkup'), (NULL, 'Diabetes'), (NULL, 'HIV'), (NULL, 'Sore throat'), (NULL, 'Skin'), (NULL, 'Tuberculosis');

e) Appointment table

INSERT INTO `appointment` (`Date`, `appid`, `Charge`, `pid`, `Did`, `Serviceid`, `Roomid`) VALUES ('2018/12/8', NULL, '222', '1', '1', '4', '1'), ('2017/12/4', NULL, '150', '2', '2', '3', '3'), ('2018/8/7', NULL, '2200', '6', '7', '7', '10'), ('2015/12/24', NULL, '222', '4', '5', '5', '4'), ('2016/12/7', NULL, '555', '2', '5', '6', '8'), ('2012/08/07', NULL, '500', '10', '10', '4', '6'), ('2018/10/20', NULL, '1000', '11', '10', '10', '6');

f) Nurse table

INSERT INTO `nurse` (`nurseid`, `nurse_name`, `doctor_id`) VALUES (NULL, 'Radha', '1'), (NULL, 'Neha', '2'), (NULL, 'Priya', '3'), (NULL, 'SNeha', '4'), (NULL, 'Nupur', '5'), (NULL, 'Shilpa', '6'), (NULL, 'Romi', '7'), (NULL, 'ALexa', '8'), (NULL, 'Parul', '9'), (NULL, 'Alia', '10');

4. Building Queries to access specific data

a) Write SQL to show details of doctors and their services during each Appointment(MySQL screenshot of code/results)

Ans= SELECT doctor.Dname, service.ServiceType, appointment.Date FROM ((appointment INNER JOIN doctor ON appointment.Did=doctor.Did) INNER JOIN service ON appointment.Serviceid=service.Serviceid)

b) Write SQL to show details of Did’s of every doctor who does every type of service.

Ans= SELECT appointment.Did FROM appointment WHERE not EXISTS( SELECT service.Serviceid FROM service WHERE NOT EXISTS( SELECT a.Did FROM appointment AS a WHERE a.Did=appointment.Did AND a.Serviceid= service.Serviceid))

c) List the Patient name for whom room number 101 is booked.

SELECT patient.PateintName FROM patient,appointment,room WHERE room.RoomNum=101 AND appointment.pid=patient.pid AND appointment.Roomid=room.Roomid

d) Show the appointment details booked by Patient named ‘Ram’.

Ans= SELECT patient.PateintName, appointment.* FROM patient INNER JOIN appointment ON patient.pid=appointment.pid WHERE patient.PateintName='ram'

e) Show appointment details for doctor named ‘Andrew’.

SELECT doctor.Dname,appointment.Date,appointment.pid,appointment.Roomid,appointment.Serviceid FROM appointment INNER JOIN doctor ON appointment.Did=doctor.Did WHERE doctor.Dname='Andrew'