Q. SQL HOSPITAL Database Implementation

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

a.SQL Update Operations of the Hospital database by creating 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)