/* Creates the DVDRentals database */
  CREATE DATABASE DVDRentals;
  
  /* Changes to the DVDRentals database */
  use DVDRentals;
  
  /* Creates the Roles table */
  CREATE TABLE Roles
     (
     RoleID VARCHAR(4) NOT NULL,
     RoleDescrip VARCHAR(30) NOT NULL,
     PRIMARY KEY (RoleID)
     )
  ENGINE=INNODB;
  
  /* Creates the MovieTypes table */
  CREATE TABLE MovieTypes
     (
     MTypeID VARCHAR(4) NOT NULL,
     MTypeDescrip VARCHAR(30) NOT NULL,
     PRIMARY KEY (MTypeID)
     )
  ENGINE=INNODB;
  
  /* Creates the Studios table */
  CREATE TABLE Studios
     (
     StudID VARCHAR(4) NOT NULL,
     StudDescrip VARCHAR(40) NOT NULL,
     PRIMARY KEY (StudID)
     )
  ENGINE=INNODB;
  
  /* Creates the Ratings table */
  CREATE TABLE Ratings
     (
     RatingID VARCHAR(4) NOT NULL,
     RatingDescrip VARCHAR(30) NOT NULL,
     PRIMARY KEY (RatingID)
     )
  ENGINE=INNODB;
  
  /* Creates the Formats table */
  CREATE TABLE Formats
     (
     FormID CHAR(2) NOT NULL,
     FormDescrip VARCHAR(15) NOT NULL,
     PRIMARY KEY (FormID)
     )
  ENGINE=INNODB;
  
  /* Creates the Status table */
  CREATE TABLE Status
     (
     StatID CHAR(3) NOT NULL,
     StatDescrip VARCHAR(20) NOT NULL,
     PRIMARY KEY (StatID)
     )
  ENGINE=INNODB;
  
  /* Creates the Participants table */
  CREATE TABLE Participants
     (
     PartID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     PartFN VARCHAR(20) NOT NULL,
     PartMN VARCHAR(20) NULL,
     PartLN VARCHAR(20) NULL
     )
  ENGINE=INNODB;
  
  /* Creates the Employees table */
  CREATE TABLE Employees
     (
     EmpID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     EmpFN VARCHAR(20) NOT NULL,
     EmpMN VARCHAR(20) NULL,
     EmpLN VARCHAR(20) NOT NULL
     )
  ENGINE=INNODB;
  
  /* Creates the Customers table */
  CREATE TABLE Customers
     (
     CustID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     CustFN VARCHAR(20) NOT NULL,
     CustMN VARCHAR(20) NULL,
     CustLN VARCHAR(20) NOT NULL
     )
  ENGINE=INNODB;
  
  /* Creates the DVDs table */
  CREATE TABLE DVDs
     (
     DVDID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     DVDName VARCHAR(60) NOT NULL,
     NumDisks TINYINT NOT NULL DEFAULT 1,
     YearRlsd YEAR NOT NULL,
     MTypeID VARCHAR(4) NOT NULL,
     StudID VARCHAR(4) NOT NULL,
     RatingID VARCHAR(4) NOT NULL,
     FormID CHAR(2) NOT NULL,
     StatID CHAR(3) NOT NULL,
     FOREIGN KEY (MTypeID) REFERENCES MovieTypes (MTypeID),
     FOREIGN KEY (StudID) REFERENCES Studios (StudID),
     FOREIGN KEY (RatingID) REFERENCES Ratings (RatingID),
     FOREIGN KEY (FormID) REFERENCES Formats (FormID),
     FOREIGN KEY (StatID) REFERENCES Status (StatID)
     )
  ENGINE=INNODB;
  
  /* Creates the DVDParticipant table */
  CREATE TABLE DVDParticipant
     (
     DVDID SMALLINT NOT NULL,
     PartID SMALLINT NOT NULL,
     RoleID VARCHAR(4) NOT NULL,
     PRIMARY KEY (DVDID, PartID, RoleID),
     FOREIGN KEY (DVDID) REFERENCES DVDs (DVDID),
     FOREIGN KEY (PartID) REFERENCES Participants (PartID),
     FOREIGN KEY (RoleID) REFERENCES Roles (RoleID)
     )
  ENGINE=INNODB;
  
  /* Creates the Orders table */
  CREATE TABLE Orders
     (
     OrderID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     CustID SMALLINT NOT NULL,
     EmpID SMALLINT NOT NULL,
     FOREIGN KEY (CustID) REFERENCES Customers (CustID),
     FOREIGN KEY (EmpID) REFERENCES Employees (EmpID)
     )
  ENGINE=INNODB;
  
  /* Creates the Transactions table */
  CREATE TABLE Transactions
     (
     TransID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     OrderID INT NOT NULL,
     DVDID SMALLINT NOT NULL,
     DateOut DATE NOT NULL,
     DateDue DATE NOT NULL,
     DateIn DATE NOT NULL,
     FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
     FOREIGN KEY (DVDID) REFERENCES DVDs (DVDID)
     )
  ENGINE=INNODB;