MasterCreateTable

CREATE TABLE USER
   	 (Username varchar(50) NOT NULL,
   	   Password varchar(50) NOT NULL, 
		FirstName varchar(15),
      	LastName varchar(15),
      	Status varchar(15), 
      	PRIMARY KEY (Username)

   	 );

CREATE TABLE USEREMAIL
   	 ( Username varchar(50) NOT NULL,
      	Email varchar(50) NOT NULL,

      	PRIMARY KEY (Email),
      	FOREIGN KEY (Username) REFERENCES User(Username)

	ON DELETE CASCADE
	ON UPDATE CASCADE
);

CREATE TABLE VISITOR
   	 ( Username varchar(50) NOT NULL,
      	PRIMARY KEY (Username),
      	FOREIGN KEY (Username) REFERENCES User(Username)

	ON DELETE CASCADE
	ON UPDATE CASCADE  
      	);

CREATE TABLE EMPLOYEE
   	 (Username varchar(50) NOT NULL,
      	EmployeeID char(9) NOT NULL,
      	Phone char(10) NOT NULL,
      	EmployeeAddress varchar(50),
      	EmployeeCity varchar(50),
      	EmployeeState varchar(20),
      	EmployeeZipcode char(5),
      	PRIMARY KEY (Username),
      	FOREIGN KEY (Username) REFERENCES User(Username)

	ON DELETE CASCADE
	ON UPDATE CASCADE

      	);


CREATE TABLE Administrator
   	 (Username varchar(50) NOT NULL,

      	PRIMARY KEY (Username),
      	FOREIGN KEY (Username) REFERENCES Employee(Username)

	ON DELETE CASCADE
	ON UPDATE CASCADE
      	);


CREATE TABLE Manager
   	 (Username varchar(50) NOT NULL,

      	PRIMARY KEY (Username),
      	FOREIGN KEY (Username) REFERENCES Employee(Username)

ON DELETE CASCADE
	ON UPDATE CASCADE  
      	);

CREATE TABLE Staff
   	 (Username varchar(50) NOT NULL,

   	 PRIMARY KEY (Username),
      	FOREIGN KEY (Username) REFERENCES Employee(Username)

	ON DELETE CASCADE
	ON UPDATE CASCADE
    	);


CREATE TABLE Site
   	 ( SiteName varchar(50),
      	SiteAddress varchar(50),
      	SiteZipcode int(5),
      	OpenEveryday boolean NOT NULL,
      	ManagerUsername varchar(50) NOT NULL,

      	PRIMARY KEY (SiteName),
      	FOREIGN KEY (ManagerUsername) REFERENCES Manager(Username)

	ON DELETE CASCADE 
	ON UPDATE CASCADE
      	);

CREATE TABLE Event   
   	 ( SiteName varchar(50) NOT NULL,
      	EventName varchar(50) NOT NULL, 
      	StartDate date NOT NULL,
      	EndDate date NOT NULL,
      	EventPrice decimal(9,2) NOT NULL,
     	Capacity int,
        CHECK (Capacity > 0),
      	MinStaffRequired int UNSIGNED NOT NULL,
     	Description MEDIUMTEXT NOT NULL,

      	PRIMARY KEY (SiteName, EventName, StartDate),
      	FOREIGN KEY (SiteName) REFERENCES Site(SiteName)

      	ON DELETE CASCADE
	ON UPDATE CASCADE
      	);

CREATE TABLE Assign_To
   	 ( StaffUsername varchar(50) NOT NULL,
      	SiteName varchar(50) NOT NULL,
      	EventName varchar(50) NOT NULL,
      	StartDate date NOT NULL,

      	PRIMARY KEY (StaffUsername, SiteName, EventName, StartDate),
      	FOREIGN KEY (StaffUsername) REFERENCES Staff(Username)
	ON DELETE CASCADE
	ON UPDATE CASCADE,
      	FOREIGN KEY (SiteName, EventName, StartDate) REFERENCES Event(SiteName,  EventName, StartDate)
	ON DELETE CASCADE
	ON UPDATE CASCADE
      	);
        #Error Code: 1050. Table 'user' already exists Error Code: 3734. Failed to add the foreign key constraint. Missing column 'staffusername' for constraint 'assign_to_ibfk_1' in the referenced table 'Staff'



CREATE TABLE Transit
   	 ( TransitType varchar(15) NOT NULL,
      	TransitRoute varchar(15) NOT NULL,
      	TransitPrice decimal(9,2) NOT NULL,

      	PRIMARY KEY (TransitType, TransitRoute)

      	);

CREATE TABLE Connect
   	 ( SiteName varchar(50) NOT NULL,
      	TransitType varchar(15) NOT NULL,
      	TransitRoute varchar(15) NOT NULL,

      	PRIMARY KEY (SiteName, TransitType, TransitRoute),
      	FOREIGN KEY (SiteName) REFERENCES Site(SiteName)
	ON DELETE CASCADE
	ON UPDATE CASCADE,
      	FOREIGN KEY (TransitType, TransitRoute) REFERENCES Transit(TransitType, TransitRoute)
	ON DELETE CASCADE
	ON UPDATE CASCADE

      	);

CREATE TABLE TakeTransit
   	 (Username varchar(50) NOT NULL,
      	TransitType varchar(50) NOT NULL,
      	TransitRoute varchar(50) NOT NULL,
      	TransitDate date NOT NULL,

      	PRIMARY KEY (Username, TransitType, TransitRoute, TransitDate),
      	FOREIGN KEY (Username) REFERENCES User(Username)
	ON DELETE CASCADE
	ON UPDATE CASCADE,
      	FOREIGN KEY (TransitType, TransitRoute) REFERENCES Transit(TransitType, TransitRoute)
	ON DELETE CASCADE
	ON UPDATE RESTRICT
      	);


CREATE TABLE Visit_Site
   	 (VisitorUsername varchar(50) NOT NULL,
      	SiteName varchar(50) NOT NULL,
      	VisitSiteDate date NOT NULL,

      	PRIMARY KEY (VisitorUsername, SiteName, VisitSiteDate),
      	FOREIGN KEY (VisitorUsername) REFERENCES Visitor(Username)
	ON DELETE CASCADE
	ON UPDATE CASCADE,
      	FOREIGN KEY (SiteName) REFERENCES Site(SiteName)
	ON DELETE CASCADE
	ON UPDATE CASCADE
      	);
        #Error Code: 3734. Failed to add the foreign key constraint. Missing column 'name' for constraint 'visit_site_ibfk_2' in the referenced table 'Site'


CREATE TABLE Visit_Event
   	 (VisitorUsername varchar(16) NOT NULL,
      	SiteName varchar(64) NOT NULL,
      	EventName varchar(64) NOT NULL,
      	StartDate date NOT NULL,
      	VisitEventDate date NOT NULL,

      	PRIMARY KEY (VisitorUsername, SiteName, EventName, StartDate, VisitEventDate),
      	FOREIGN KEY (VisitorUsername) REFERENCES Visitor(Username)
	ON DELETE CASCADE
	ON UPDATE CASCADE,
      	FOREIGN KEY (SiteName, EventName, StartDate) REFERENCES Event(SiteName, EventName, StartDate)
	ON DELETE CASCADE
	ON UPDATE CASCADE
      	);