-1
DROP TABLE IF EXISTS Trans;
DROP TABLE IF EXISTS Location;
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Movie;
DROP TABLE IF EXISTS VideoGame;
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS Department;

CREATE TABLE Location(
LocationID INT NOT NULL AUTO_INCREMENT,
KioskNumber Int(5) NOT NULL,
Address varchar(50) NOT NULL,
City Char(35) NOT NULL,
State Char(2) NOT NULL,
PostalCode INT(5) NOT NULL,
PRIMARY KEY (LocationID)
);
CREATE TABLE Employee(
EmployeeID INT NOT NULL AUTO_INCREMENT,
FirstName Char(25) NOT NULL,
LastName Char(25) NOT NULL,
DepartmentID Char(35) NOT NULL,
Address VarChar(40) NOT NULL,
City Char(35) NOT NULL,
State Char(2) NOT NULL,
PostalCode Int(5) NOT NULL, 
Phone Char(12) NOT NULL UNIQUE,
EmployeeEmail VarChar(50) NOT NULL,
PRIMARY KEY (EmployeeID),
FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
);
CREATE TABLE Department (
DepartmentID INT NOT NULL AUTO_INCREMENT,
DepartmentName CHAR(35) NOT NULL,
DepartmentPhone Char(12) NOT NULL UNIQUE,
PRIMARY KEY (DepartmentID)
);
CREATE TABLE VideoGame (
VideoGameID Int NOT NULL AUTO_INCREMENT,
Title Char(50) NOT NULL,
Genre Char(20) NOT NULL,
Rating Decimal(3,1) NOT NULL,
PRIMARY KEY (VideoGameID)
);
CREATE TABLE Movie (
MovieID int NOT NULL AUTO_INCREMENT,
Title CHAR(50) NOT NULL,
Genre Char(20) NOT NULL,
Rating Decimal(3,1) NOT NULL,
PRIMARY KEY (MovieID)
);
Create Table Customer (
CustomerID INT NOT NULL AUTO_INCREMENT,
FirstName CHAR(35) NOT NULL,
LastName CHAR(35) NOT NULL,
Address VARCHAR(40) NOT NULL,
City CHAR(35) NOT NULL,
State CHAR(2) NOT NULL,
PostalCode INT(5),
Phone CHAR(12) NOT NULL UNIQUE,
Email VARCHAR(50) NOT NULL,
CreditCard BIGINT(16),
PRIMARY KEY (CustomerID)
);
CREATE TABLE TRANS (
TransactionID INT NOT NULL AUTO_INCREMENT,
CustomerID INT NOT NULL,
RentalDate DATE NOT NULL,
MovieID INT,
VideoGameID INT,
LocationID INT NOT NULL,
PRIMARY KEY (TransactionID),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY (MovieID) REFERENCES Movie(MovieID),
FOREIGN KEY (VideoGameID) REFERENCES VideoGame(VideoGameID),
FOREIGN KEY (LocationID) REFERENCES Location(LocationID)
);
1

DepartmentID in Employees is referencing to a table that doesn't exist as a result it raises error.Always create tables in the order of dependency.Swap the order of table creation i.e push Department table ahead of Employees table and foreign key must be same data type of referencing key.

DROP TABLE IF EXISTS Trans;
DROP TABLE IF EXISTS Location;
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Movie;
DROP TABLE IF EXISTS VideoGame;
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS Department;

CREATE TABLE Location(
LocationID INT NOT NULL AUTO_INCREMENT,
KioskNumber Int(5) NOT NULL,
Address varchar(50) NOT NULL,
City Char(35) NOT NULL,
State Char(2) NOT NULL,
PostalCode INT(5) NOT NULL,
PRIMARY KEY (LocationID)
);
CREATE TABLE Department (
DepartmentID INT NOT NULL AUTO_INCREMENT,
DepartmentName CHAR(35) NOT NULL,
DepartmentPhone Char(12) NOT NULL UNIQUE,
PRIMARY KEY (DepartmentID)
);
CREATE TABLE Employee(
EmployeeID INT NOT NULL AUTO_INCREMENT,
FirstName Char(25) NOT NULL,
LastName Char(25) NOT NULL,
-- DepartmentID  Char(35) NOT NULL, (data type not matching)
DepartmentID  INT  NOT NULL,
Address VarChar(40) NOT NULL,
City Char(35) NOT NULL,
State Char(2) NOT NULL,
PostalCode Int(5) NOT NULL, 
Phone Char(12) NOT NULL UNIQUE,
EmployeeEmail VarChar(50) NOT NULL,
PRIMARY KEY (EmployeeID),
FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
);

CREATE TABLE VideoGame (
VideoGameID Int NOT NULL AUTO_INCREMENT,
Title Char(50) NOT NULL,
Genre Char(20) NOT NULL,
Rating Decimal(3,1) NOT NULL,
PRIMARY KEY (VideoGameID)
);
CREATE TABLE Movie (
MovieID int NOT NULL AUTO_INCREMENT,
Title CHAR(50) NOT NULL,
Genre Char(20) NOT NULL,
Rating Decimal(3,1) NOT NULL,
PRIMARY KEY (MovieID)
);
Create Table Customer (
CustomerID INT NOT NULL AUTO_INCREMENT,
FirstName CHAR(35) NOT NULL,
LastName CHAR(35) NOT NULL,
Address VARCHAR(40) NOT NULL,
City CHAR(35) NOT NULL,
State CHAR(2) NOT NULL,
PostalCode INT(5),
Phone CHAR(12) NOT NULL UNIQUE,
Email VARCHAR(50) NOT NULL,
CreditCard BIGINT(16),
PRIMARY KEY (CustomerID)
);
CREATE TABLE TRANS (
TransactionID INT NOT NULL AUTO_INCREMENT,
CustomerID INT NOT NULL,
RentalDate DATE NOT NULL,
MovieID INT,
VideoGameID INT,
LocationID INT NOT NULL,
PRIMARY KEY (TransactionID),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY (MovieID) REFERENCES Movie(MovieID),
FOREIGN KEY (VideoGameID) REFERENCES VideoGame(VideoGameID),
FOREIGN KEY (LocationID) REFERENCES Location(LocationID)
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.