Q. SQL HOSPITAL Database Implementation 1.Do the following operation from given

Question

Q. SQL HOSPITAL Database Implementation

1.Do the following operation from given hospital schema hospital data.

b.Simple SQL Queries of the Hospital database

c.Complex SQL Queries

d. SQL views for the HOSPITAL Database and the content of the View Tables.

If picture not visible then Use following : picture data

# hospital-schema.sql file

#

DROP DATABASE IF EXISTS hospital;

CREATE DATABASE hospital;

USE hospital;

#DROP TABLE department; #CASCADE CONSTRAINTS;

CREATE TABLE department (

dname varchar(15) not null,

dlocation varchar(40),

deptID integer(9),

primary key (deptID)  

);

# # is used for comment in MySQL

#DROP TABLE employee;

CREATE TABLE employee (

fname varchar(15) not null,

minit varchar(1),

lname varchar(15) not null,

type varchar(15),

eid varchar(15),

ssn char(9),

startdate date,

salary decimal(10,2),

specialty varchar(25),

schedule varchar(25),

deptID integer(9),

primary key (eid)

# foreign key (deptID) references department(deptID)

);

ALTER TABLE employee ADD (

foreign key (deptID) references department(deptId)

#on delete ... on update ...??????????????

);

#DROP TABLE doctor; #CASCADE CONSTRAINTS;

CREATE TABLE doctor (

experience integer (4),

deid varchar(15),

  

primary key(deid),

foreign key (deid) references employee (eid)

);

#DROP TABLE nurse; #CASCADE CONSTRAINTS;

CREATE TABLE nurse (

certification varchar(25) not null,

neid varchar(15) not null,

deid varchar(15) not null,

primary key(deid),

foreign key (neid) references employee(eid),

foreign key (deid) references doctor(deid)

);

# # is used for comment in MySQL

#DROP TABLE patient;

CREATE TABLE patient (

fname varchar(15) not null,

minit varchar(1),

lname varchar(15) not null,

ssn char(9),

diagnosis varchar(15) not null,

address varchar(25) not null,

dateofadmit date,

dateofdischarge date,

tid varchar(15) not null,

pid varchar(15) not null,

primary key (pid)

# surrogate key (tid) references transactionid(tid)

);

#DROP TABLE treats; #CASCADE CONSTRAINTS;

CREATE TABLE treats (

deid varchar(15) not null,

pid varchar(15) not null,

  

primary key(deid),

foreign key (deid) references doctor(deid),

foreign key (pid) references patienid(pid)

);

#DROP TABLE observes; #CASCADE CONSTRAINTS;

CREATE TABLE observes (

neid varchar(15) not null,

pid varchar(15) not null,

  

primary key (neid),

foreign key (neid) references nurse(neid),

foreign key (pid) references patientid(pid)

);

# # is used for comment in MySQL

#DROP TABLE other employee;

CREATE TABLE otheremployee (

task varchar(15) not null,

oeid varchar(15) not null,

  

primary key(oeid)

# foreign key (oeid) references employeeID(eid)

);

# # is used for comment in MySQL

#DROP TABLE transaction;

CREATE TABLE transaction (

amount decimal(8,2) not null,

date date,

time date,

pid varchar(15),

tid varchar(15) not null,

address varchar(25) not null,

primary key(tid),

foreign key (pid) references patient(pid)

);

0 hospital-schema Notepad File Edit Fa View Help l hospital-schema.sql file DROP DATARASE TF EXTSTS hospital; CREATE DATABASE hospital; USE hospital; DROP TARIF department; #CASCADE CONSTRAT NTS; CREATE TABLE department varchar 15) not null, dname dlocationvchar(40), deptTD primary key (deptID) integer(9), )i # is used for comment in MySQL DROP TABLE employee CREAIt IABLt employee( fae varchar(15) not nul, minit chr(1), Iname varchar(15) not null, Lype ah(15, varchar( char(9), ssn startdate date salary decimal10,2), specialty varchar(2, schedule varchar(25, deptID primary kcy (cid) foreign key (deptID) references department (deptID) integer(9), )i ALTER TARILE employee ADD foreign key (deptID) references department (deptId) update . . ? ? ? ? ? ? ? ? ? ? ? ? ? ? ton delete )i on ·.. UDROP TABLE doctor; #CASCADE CONSTRAINTS; CREATE TABLE doctor( experience integer (a), deid varchar(15) primary key(deid), forcign kcy (deid) references employee cid)

Explanation / Answer

Update operation by creating SQL data for Hospital database

Inserting values in the department table

insert into department (dname,dlocation,deptID) values ('radiology','east london',1);
insert into department (dname,dlocation,deptID) values ('opd','east london',2);
insert into department (dname,dlocation,deptID) values ('pathlab','west london',3);
insert into department (dname,dlocation,deptID) values ('surgery','south london',4);
insert into department (dname,dlocation,deptID) values ('icu','north london',5);

Inserting values in employee

insert into employee(fname,minit,lname,type,eid,ssn,startdate,salary,specialty,schedule,deptID) values ('sam','F','joseph','orthodentist','1','100','1984-02-14',2000,'teeth','sunday',2);
insert into employee(fname,minit,lname,type,eid,ssn,startdate,salary,specialty,schedule,deptID) values ('john','K','doe','surgeon','2','101','1987-02-14',2060,'heart','weekends',4);
insert into employee(fname,minit,lname,type,eid,ssn,startdate,salary,specialty,schedule,deptID) values ('Theodore','S','james','pediatrician','3','102','1990-02-14',1500,'child','Mondays',2);
insert into employee(fname,minit,lname,type,eid,ssn,startdate,salary,specialty,schedule,deptID) values ('samantha','s','wiley','nurse','4','103','1989-02-01',750,'generic','everyday',2);
insert into employee(fname,minit,lname,type,eid,ssn,startdate,salary) values ('james','x','camroon','MD','5','104','1970-02-01',7500);

Inserting values into doctor

insert into doctor(experience,deid) values (5,'2');
insert into doctor(experience,deid) values (6,'1');
insert into doctor(experience,deid) values (4,'3');

Inserting values into nurses

insert into nurse(certification,neid,deid) values ('red cross','1',2);

Inserting values into patient

insert into patient(fname,minit,lname,ssn,diagnosis,address,dateofadmit,dateofdischarge,tid,pid) values ("Robert","D","Frost","201","kidneystone","ireland","2018-04-01","2018-04-10","333","1");

Inserting values into treats

insert into treats(deid,pid) values ("2","1");

Inserting values into observes

insert into observes(neid,pid) values('1','1');

Inserting values into otheremployee
insert into otheremployee(task,oeid) values ('management','5');

Inserting values into transaction
insert into transaction(amount,date,time,pid,tid,address) values (500,"2018-04-10","2018-04-10","1","333","ireland");

Simple SQL queries

shows the entire table department
select * from department;

displays treatment details where patient id is 1
select * from treats where pid = '1';

display the department which are situated in east london
select * from department where dlocation = "east london";

Complex SQL queries

shows the total salary being paid to doctors

select sum(salary) from employee where eid in (select deid from doctor);

shows the highest paid employee in hospital

select concat(fname," ",minit," ",lname) as name,salary from employee order by salary DESC LIMIT 1;

Creating views in database (we can create a view with the fields we require to have instead of all the fields given in the database)

create view doctor_details as select fname,minit,lname,salary from employee where type in ('surgeon','pediatrician');

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.