Five files: hospital.htm , hospitalInsert.htm,

ERdiagramFrankHospital.htm, hospitalQueries.htm , hospital.mdb

hospital.sql (changed suffix from .htm to .sql ) (you can run this file to create a Microsoft Access database using Prof. Devi’s SQLexec.exe ) Prof. Devi wrote it because MS Access does not have a way to run a batch SQL file)

(Creates all the tables, drop command are there so that after making corrections you start from scratch. Integrity constraints are included. Note that the order of creation is important table that are referenced should be created before creating tables that have references them, else you get errors. But dropping must be in reverse order of creation since referencing tables must go first. Initially the drop commands will cause error, since there is nothing to drop, ignore it.)

hospitalInsert.sql (changed suffix from.htm to .sql) (you can run this file to create a Microsoft Access database using Prof. Devi’s SQLexec.exe ) Prof. Devi wrote it because MS Access does not have a way to run a batch SQL file)

(Populates the database. Note the order of insert is important; insert the parent records, i.e., records that may be referenced before inserting a referencing record (child record). The DELETE commands are there so that if you make corrections then deletes start you from scratch when populating the database. You may have to add more data; just copy and paste INSERT statement and change the values. For less typing errors do copy and paste especially when referencing some primary key data in another table.)

E-RdiagramFrankHospital.htm (E-R diagram generated by MS Access)

hospital.mdb (open this in MS Access. The only change I had to make in MS Access is that data type NUMBER in access is NUMERIC in MySQL.)

TO RUN THE FILES IN MySql:

In MySql first create a database called “hospital” as follows:

create database hospital;

Next run the two sql files in MySql as follows:

source filePath/hospital.sql

source filePath/hospitalinsert.sql

For some reason MySQL on my computer does not take AUTO_INCREMENT as in :

create table Patient(PatientId varchar(9) not null AUTO_INCREMENT, PatientName varchar(40),DB datetime,Tel varchar(12),PatientAddress varchar(100),PatientType varchar(10),AdmitingPhysician varchar(9) not null );

Mayby it will take it on yours.

ERdiagramFrankHospital.htm is Entity Relationship diagram for Frank’s Hospital database.

hospitalQueries.htm file gives the solutions to the 22 SQL queries.