To complete this assignment, submit the following three files to Webcourses:

1. An SQL file (i.e. save the file with file extension .sql) containing the SQL written to perform the tasks.
2. An exported SQL file using MySQL Workbench Data Export option.
3. An ER Diagram generated by MySQL Workbench.

Assignment Scope

1. Use database hospital.
2. Alter tables in the database.
3. Create new tables.
4. Insert rows into tables.

To access the database:

1. Launch the MySQL Command Line Client executable or MySQL Workbench
2. Login in using the password set during installation

Department.sql

/* NOTE: replace text employeeId with the associated value from table physician

given the physician's name */

('General Medicine', employeeId) ~ Robert Apostol

('Surgery', employeeId) ~ ElliotReid

('Psychiatry', employeeId) ~ Molly Clock

('Pediatrics', employeeId) ~ Doug Ross

('Intensive Care Unit', employeeId) ~ Robert Chase

('Emergency', employeeId) ~ Leonard McCoy

('Oncology', employeeId) ~ Percival Cox

('Cardiology', employeeId) ~ Bob Kelso

('Neurology', employeeId) ~ Gregory House

('Materinity', employeeId) ~ Rosa Altino

Oncall.sql

/* NOTE: replace text employeeId with the associated value from table nurse given

the nurse's name */

Carla Espinosa ~ (employeeId, 1, 1, '2020-03-01 07:00:00', '2020-03-01 19:00:00')

Kitty Forman ~ (employeeId, 1, 2, '2020-03-01 19:00:00', '2020-03-02 07:00:00')

Margaret Houlihan ~ (employeeId, 1, 3, '2020-03-02 07:00:00', '2020-03-02

19:00:00')

Laverne Roberts ~ (employeeId, 2, 1, '2020-03-02 19:00:00', '2020-03-03 07:00:00')

Paul Flowers ~ (employeeId, 2, 2, '2020-03-03 07:00:00', '2020-03-03 19:00:00')

Carol Hathaway ~ (employeeId, 2, 3, '2020-03-03 19:00:00', '2020-03-04 07:00:00')

Samantha Taggart ~ (employeeId, 3, 1, '2020-03-04 07:00:00', '2020-03-04 19:00:00')

Abby Lockhart ~ (employeeId, 3, 2, '2020-03-04 19:00:00', '2020-03-05 07:00:00')

Peter Petrelli ~ (employeeId, 3, 3, '2020-03-05 07:00:00', '2020-03-05 19:00:00')

Jackie Peyton ~ (employeeId, 1, 1, '2020-03-05 19:00:00', '2020-03-06 07:00:00')

Ann Perkins ~ (employeeId, 1, 2, '2020-03-06 07:00:00', '2020-03-06 19:00:00')

Dell Parker ~ (employeeId, 1, 3, '2020-03-06 19:00:00', '2020-03-07 07:00:00')

Christine Chapel ~ (employeeId, 2, 1, '2020-03-07 07:00:00', '2020-03-07 19:00:00')

Audrey Hardy ~ (employeeId, 2, 2, '2020-03-07 19:00:00', '2020-03-08 07:00:00')

Bobbie Spenser ~ (employeeId, 2, 3, '2020-03-08 07:00:00', '2020-03-08 19:00:00')

Terri Alden ~ (employeeId, 3, 1, '2020-03-08 19:00:00', '2020-03-09 07:00:00')

Mary Young ~ (employeeId, 3, 2, '2020-03-09 07:00:00', '2020-03-09 19:00:00')

Charlie Fairhead ~ (employeeId, 3, 3, '2020-03-09 19:00:00', '2020-03-10 07:00:00')

Tina Seabrook ~ (employeeId, 1, 1, '2020-03-10 07:00:00', '2020-03-10 19:00:00')

Physician.Sql

/* NOTE: replace text 'id' with the ID value associated with the new row in table

position

for General Medicine */

('Philip', 'Buck', id, 111223333)

('Apostol', 'Robert', id, 222334444)

('Badola', 'Jesus', id, 333445555)

('Altino', 'Rosa', id, 444556666)

('Hamilton', 'Ann', id, 555667777)

Position.Sql

('General Medicine')

Tasks Description 1. Alter table physician so that column ssn is unique. 2. Alter table nurse so that column ssn is unique. | 3. Alter table insurance so that column company Name is unique. 4. Alter table medicalProcedure so that column name is unique. 5. Alter table medication so that column name is unique. 6. Alter table position so that column title is unique. | 7. Alter table roomType so that column roomDesc is unique. 8. Insert data into table position. 9. Insert data into table physician. 10. Create table named department with the following attributes, data types, and constraints: a. ID, integer, not null, auto increment b. name, variable character, 50 characters, not null c. head, integer, not null d. primary key is column ID e. foreign key is column head, references table physician, column employeeld 11. Insert data into table department. 12. Create table named onCall with the following attributes, data types, and constraints: f. ID, integer, not null, auto increment g. nurse, integer, not null h. blockFloor, integer, not null i. blockCode, integer, not null j. startDateTime, timestamp, not null k. endDateTime, timestamp, not null 1. primary key is column ID m. foreign key is column nurse, references table nurse, column employeeld 13. Insert data into table on Call