BEGIN TRANSACTION;

CREATE TABLE Schedule (
   ScheduleID int AUTO_INCREMENT,
   Date varchar(255),
   PRIMARY KEY (ScheduleID)
);

INSERT INTO Schedule (ScheduleID, Date)
VALUES (1, "MONDAY");

INSERT INTO Schedule (ScheduleID, Date)
VALUES (2, "MONDAY");

INSERT INTO Schedule (ScheduleID, Date)
VALUES (3, "MONDAY");

INSERT INTO Schedule (Date)
VALUES ("THURSDAY");

INSERT INTO Schedule (Date)
VALUES ("FRIDAY");

INSERT INTO Schedule (Date)
VALUES ("SATURDAY");

INSERT INTO Schedule (Date)
VALUES ("SUNDAY");

CREATE TABLE ShiftInstance (
   ShiftInstanceID int AUTO_INCREMENT,
   ScheduleID int AUTO_INCREMENT,
   DeptID int AUTO_INCREMENT,
   NurseID int AUTO_INCREMENT,
   Date varchar(255),
   StartTime int,
   EndTime int,
   PRIMARY KEY (ShiftInstanceID),
   FOREIGN KEY (ScheduleID) REFERENCES Schedule(ScheduleID)
);

INSERT INTO ShiftInstance (ShiftInstanceID, ScheduleID, DeptID, NurseID, Date, StartTime, EndTime)
VALUES (1, 1, 1, 1, "9/21/2017", 7, 3);

INSERT INTO ShiftInstance (ShiftInstanceID, ScheduleID, DeptID, NurseID, Date, StartTime, EndTime)
VALUES (2, 2, 2, 2, "9/21/2017", 3, 11);

INSERT INTO ShiftInstance (ShiftInstanceID, ScheduleID, DeptID, NurseID, Date, StartTime, EndTime)
VALUES (3, 3, 3, 3, "9/21/2017", 3, 11);


CREATE TABLE Department (
   DeptID int AUTO_INCREMENT,
   DeptName varchar(255),
   StaffSize int,
   PRIMARY KEY (DeptID)
);

INSERT INTO Department(DeptID, DeptName, StaffSize)
VALUES (1, "SURG", 25);

INSERT INTO Department(DeptID, DeptName, StaffSize)
VALUES (2, "SURG", 25);

INSERT INTO Department(DeptID, DeptName, StaffSize)
VALUES (3, "SURG", 25);

CREATE TABLE Nurses (
   NurseID int AUTO_INCREMENT,
   DeptID int AUTO_INCREMENT,
   FirstName varchar(255),
   LastName varchar(255),
   Title varchar(255),
   IsSalaried int,
   StartDate varchar(255),
   PRIMARY KEY (NurseID),
   FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

INSERT INTO Nurses(NurseID, DeptID, FirstName, LastName, Title, IsSalaried, StartDate)
VALUES (1, 1, "Adam", "Apple", "The Big Cheese", 0, "1/1/1970");

INSERT INTO Nurses(NurseID, DeptID, FirstName, LastName, Title, IsSalaried, StartDate)
VALUES (2, 2, "Betty", "Boop", "The Bigg Cheese", 0, "1/1/1970");

INSERT INTO Nurses(NurseID, DeptID, FirstName, LastName, Title, IsSalaried, StartDate)
VALUES (3, 3, "Charlie", "Chaplin", "The Biggg Cheese", 0, "1/1/1970");

INSERT INTO Nurses(NurseID, DeptID, FirstName, LastName, Title, IsSalaried, StartDate)
VALUES (4, 4, "Debbit", "Donsht", "The Bigggg Cheese", 0, "1/1/1970");


COMMIT;

/* Display all the records from the table */
SELECT Nurses.FirstName, Nurses.LastName, Schedule.Date, ShiftInstance.StartTime, ShiftInstance.EndTime, Department.DeptName FROM Schedule, ShiftInstance, Department, Nurses
WHERE Schedule.ScheduleID = ShiftInstance.ScheduleID
AND ShiftInstance.DeptID = Department.DeptID
AND ShiftInstance.NurseID = Nurses.NurseID
ORDER BY Nurses.LastName;