# if you're installing this on your own computer, un-comment and run the following lines, so that you place the tables into their own schema drop schema if exists Assignment2; create schema Assignment2; use Assignment2;

SET foreign_key_checks = 0;

drop table if exists Supplier; drop table if exists Delivery; Drop table if exists Item; drop table if exists Department; drop table if exists Employee; drop table if exists Sale;

CREATE TABLE Item ( ItemID SMALLINT, ItemName VARCHAR(50) NOT NULL, ItemType CHAR(1), ItemColour VARCHAR(20), PRIMARY KEY (ItemID) );

CREATE TABLE Employee ( EmployeeID SMALLINT, EmployeeName VARCHAR(50), EmployeeSalary DECIMAL(8,2), DepartmentID SMALLINT, BossID SMALLINT, PRIMARY KEY (EmployeeID), FOREIGN KEY (BossID) references Employee(EmployeeID), FOREIGN KEY (DepartmentID) references Department(DepartmentID) );

CREATE TABLE Department ( DepartmentID SMALLINT, DepartmentName VARCHAR(50) NOT NULL, DepartmentFloor INTEGER, DepartmentPhone INTEGER, ManagerID SMALLINT NOT NULL, PRIMARY KEY (DepartmentID), FOREIGN KEY (ManagerID) references Employee(EmployeeID) );

CREATE TABLE Sale ( SaleID INTEGER NOT NULL, SaleQTY INTEGER, ItemID SMALLINT NOT NULL, DepartmentID SMALLINT NOT NULL, PRIMARY KEY (SaleID), FOREIGN KEY (ItemID) references Item(ItemID), FOREIGN KEY (DepartmentID) references Department(DepartmentID) );

CREATE TABLE Supplier ( SupplierID SMALLINT NOT NULL, SupplierName VARCHAR(25), SupplierPhone VARCHAR(16), PRIMARY KEY (SupplierID) );

CREATE TABLE Delivery ( DeliveryID INTEGER NOT NULL, DeliveryQTY INTEGER NOT NULL, ItemID SMALLINT NOT NULL, DepartmentID SMALLINT NOT NULL, SupplierID SMALLINT NOT NULL, PRIMARY KEY (DeliveryID), FOREIGN KEY (ItemID) references Item(ItemID), FOREIGN KEY (DepartmentID) references Department(DepartmentID), FOREIGN KEY (SupplierID) references Supplier(SupplierID) );

15. Find bosses who are in the same department as their employees