creating_tables.sql

DROP DATABASE IF EXISTS CRYPTOZOO;
CREATE DATABASE CRYPTOZOO;
USE CRYPTOZOO;

CREATE TABLE LOCATION(
	Location VARCHAR(25) NOT NULL,
	Max_Occupancy INT,
	PRIMARY KEY(Location));

CREATE TABLE STAFF(
	Staff_Name VARCHAR(25),
	Staff_ID CHAR(9) NOT NULL,
	Is_Contracted BOOLEAN,
	Salary DECIMAL(10, 2),
	PRIMARY KEY(Staff_ID));

CREATE TABLE WORK_TEAM(
	Staff_ID VARCHAR(25),
    Mgr_ID CHAR(9),
    PRIMARY KEY(Staff_ID, Mgr_ID), 
    FOREIGN KEY(Staff_ID) REFERENCES STAFF(Staff_ID),
    FOREIGN KEY(Mgr_ID) REFERENCES STAFF(Staff_ID)
		ON UPDATE CASCADE ON DELETE RESTRICT);


CREATE TABLE EXHIBIT(
	Name VARCHAR(30) NOT NULL,
	Location VARCHAR(25),
    Mgr_ID CHAR(9),
	PRIMARY KEY(Name),
	FOREIGN KEY(Mgr_ID) REFERENCES STAFF(Staff_ID)
		ON UPDATE CASCADE ON DELETE SET NULL,
	FOREIGN KEY(Location) REFERENCES LOCATION(Location)
		ON UPDATE CASCADE ON DELETE RESTRICT);

CREATE TABLE SPECIES(
	S_Name VARCHAR(30),
	Is_Endangered BOOLEAN,
	PRIMARY KEY(S_Name));

CREATE TABLE ANIMAL(
	Animal_Name VARCHAR(25),
	ID CHAR(9) NOT NULL,
	Species_Name VARCHAR(30),
    Exhibit VARCHAR(30),
	PRIMARY KEY(ID),
	FOREIGN KEY(Species_Name) REFERENCES SPECIES(S_Name)
		ON UPDATE CASCADE ON DELETE RESTRICT); 

CREATE TABLE SERVICE(
	Service_Name VARCHAR(25),
    Mgr_ID CHAR(9),
	Location VARCHAR(25),
	Is_Commercial BOOLEAN,
	PRIMARY KEY(Service_Name, Mgr_ID),
	FOREIGN KEY(Location) REFERENCES LOCATION(Location)
		ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY(Mgr_ID) REFERENCES STAFF(Staff_ID)
		ON UPDATE CASCADE ON DELETE CASCADE);

CREATE TABLE EQUIPMENT (
	Type VARCHAR(30) NOT NULL,
	Location VARCHAR(25),
	Equipment_ID CHAR(9),
	PRIMARY KEY(Equipment_ID),
	FOREIGN KEY(Location) REFERENCES LOCATION(Location)
		ON UPDATE CASCADE ON DELETE SET NULL);
        
CREATE TABLE IN_USE (
	Equipment_ID CHAR(9),
	Service VARCHAR(25),
	Staff_ID CHAR(9),
	PRIMARY KEY(Equipment_ID),
    FOREIGN KEY(Equipment_ID) REFERENCES EQUIPMENT(Equipment_ID)
		ON UPDATE CASCADE ON DELETE RESTRICT,
	FOREIGN KEY(Service) REFERENCES SERVICE(Service_Name)
		ON UPDATE CASCADE ON DELETE RESTRICT,
	FOREIGN KEY(Staff_ID) REFERENCES STAFF(Staff_ID)
		ON UPDATE CASCADE ON DELETE RESTRICT);

CREATE TABLE QUARANTINE(
	Animal_ID CHAR(9) NOT NULL,
	Injury VARCHAR(50),
	Assigned_Vet CHAR(9),
	Projected_Release_Date DATE,
	PRIMARY KEY(Animal_ID, Assigned_Vet, Injury),
	FOREIGN KEY(Animal_ID) REFERENCES ANIMAL(ID)
		ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY(Assigned_Vet) REFERENCES STAFF(Staff_ID)
		ON UPDATE CASCADE ON DELETE RESTRICT);

CREATE TABLE EVENT(
	Name VARCHAR(50),
	Date DATETIME,
	Hours TIME,
	Description VARCHAR(140),
	Cost DECIMAL(10, 2) NOT NULL,
	PRIMARY KEY (Name, Date));


CREATE TABLE EVENT_STAFF(
	Staff_ID CHAR(9) NOT NULL,
	Event_Name VARCHAR(50),
	Date DATETIME,
	Hours DECIMAL(10, 2),
	PRIMARY KEY(Staff_ID, Event_Name, Date),
	FOREIGN KEY(Staff_ID) REFERENCES STAFF(Staff_ID)
		ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY(Event_Name, Date) REFERENCES EVENT(Name, Date)
		ON UPDATE CASCADE ON DELETE CASCADE);