• +1-617-874-1011 (US)
  • +44-117-230-1145 (UK)
Live Chat
Follow Us:

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');

mysql project solution

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');

mysql project solution

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');

mysql project solution

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');

mysql project solution

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');

mysql project solution

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');

mysql project solution

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)

mysql project solution

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))

mysql project solution

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

mysql project solution

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'

mysql project solution

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'



Keywords: MYSQL Tutorials, Data Relations homework help, SQL homework help, SQL project Help, Oracle Homework Help, XML homework help, XML and Relational Algebra assignment help, SQL Queries and Updates homework assignment help, XML Queries and Transformations tutoring help, Relational Database Design concept, Higher-Level Design help in UML and ERD, Data Warehousing and Data Mining, help with SQL 2008 database, support for Database help, Microsoft access and mysql database programmer, database development and xml database support, homework helper for asp database and uml modeling, uml sequence diagram and uml class diagram, database design development and software SQL server 2008.