/* Chapter 5 - Examples */
  
  CREATE TABLE Inventory
  (
     ProductID SMALLINT(4) UNSIGNED ZEROFILL,
     Quantity INT UNSIGNED
  );
  
  CREATE TABLE Catalog
  (
     ProductID SMALLINT,
     Price DECIMAL(7,2),
     Weight FLOAT(8,4)
  );
  
  CREATE TABLE Catalog
  (
     ProductID SMALLINT,
     Description VARCHAR(40),
     Category CHAR(3),
     Price DECIMAL(7,2)
  );
  
  CREATE TABLE Inventory
  (
     ProductID SMALLINT UNSIGNED,
     Name VARCHAR(40),
     Photo BLOB,
     Quantity INT UNSIGNED
  );
  
  CREATE TABLE Catalog
  (
     ProductID SMALLINT UNSIGNED,
     Name VARCHAR(40),
     DescriptionDoc TEXT CHARACTER SET latin1 COLLATE latin1_bin
  );
  
  CREATE TABLE Orders
  (
     OrderID SMALLINT UNSIGNED,
     BikeModel VARCHAR(40),
     BikeColor ENUM('red', 'blue', 'green', 'yellow'),
     BikeOptions SET('rack', 'light', 'helmet', 'lock')
  );
  
  CREATE TABLE BookOrders
  (
     OrderID SMALLINT UNSIGNED,
     BookID SMALLINT UNSIGNED,
     Copyright YEAR,
     OrderDate TIMESTAMP
  );
  
  CREATE TABLE Catalog
  (
     ProductID SMALLINT UNSIGNED NOT NULL,
     Name VARCHAR(40) NOT NULL
  );
  
  CREATE TABLE AuthorBios
  (
     AuthID SMALLINT UNSIGNED NOT NULL,
     YearBorn YEAR NOT NULL,
     CityBorn VARCHAR(40) NOT NULL DEFAULT 'Unknown'
  );
  
  CREATE TABLE AuthorBios
  (
     AuthID SMALLINT UNSIGNED NOT NULL,
     YearBorn YEAR NOT NULL,
     NumBooks SMALLINT NOT NULL DEFAULT 1
  );
  
  CREATE TABLE Orders
  (
     OrderID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
     ModelID SMALLINT UNSIGNED NOT NULL,
     ModelDescrip VARCHAR(40)
  );
  
  CREATE TABLE Orders
  (
     OrderID SMALLINT UNSIGNED NOT NULL,
     ModelID SMALLINT UNSIGNED NOT NULL,
     ModelDescrip VARCHAR(40),
     PRIMARY KEY (OrderID)
  );
  
  CREATE TABLE Orders
  (
     OrderID SMALLINT UNSIGNED NOT NULL,
     ModelID SMALLINT UNSIGNED NOT NULL,
     ModelDescrip VARCHAR(40),
     PRIMARY KEY (OrderID, ModelID)
  );
  
  CREATE TABLE Catalog
  (
     ProductID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
     Name VARCHAR(40) NOT NULL,
     PRIMARY KEY (ProductID)
  );
  
  CREATE TABLE Orders
  (
     OrderID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
     ModelID SMALLINT UNSIGNED NOT NULL REFERENCES Models (ModelID),
     ModelDescrip VARCHAR(40)
  );
  
  CREATE TABLE Orders
  (
     OrderID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
     ModelID SMALLINT UNSIGNED NOT NULL,
     ModelDescrip VARCHAR(40),
     FOREIGN KEY (ModelID) REFERENCES Models (ModelID)
        ON DELETE CASCADE ON UPDATE CASCADE
  );
  
  CREATE TABLE AuthorBios
  (
     AuthID SMALLINT UNSIGNED NOT NULL,
     YearBorn YEAR NOT NULL,
     CityBorn VARCHAR(40) NOT NULL DEFAULT 'Unknown'
  )
  ENGINE=INNODB;
  
  CREATE TABLE Books
  (
     BookID SMALLINT NOT NULL,
     BookName VARCHAR(40) NOT NULL,
     PubID SMALLINT NOT NULL DEFAULT 'Unknown'
  )
  ENGINE=INNODB;
  
  ALTER TABLE Books
  ADD PRIMARY KEY (BookID),
  ADD CONSTRAINT fk_1 FOREIGN KEY (PubID) REFERENCES Publishers (PubID),
  ADD COLUMN Format ENUM('paperback', 'hardcover') NOT NULL AFTER BookName;
  
  ALTER TABLE Books
  DROP PRIMARY KEY,
  DROP FOREIGN KEY fk_1,
  DROP COLUMN Format;
  
  CREATE TABLE Orders
  (
     OrderID SMALLINT UNSIGNED NOT NULL,
     ModelID SMALLINT UNSIGNED NOT NULL,
     ModelDescrip VARCHAR(40),
     PRIMARY KEY (OrderID),
     UNIQUE (OrderID, ModelID)
  );
  
  CREATE TABLE Orders
  (
     OrderID SMALLINT UNSIGNED NOT NULL,
     ModelID SMALLINT UNSIGNED NOT NULL,
     PRIMARY KEY (OrderID),
     INDEX (ModelID)
  );
  
  CREATE TABLE Orders
  (
     OrderID SMALLINT UNSIGNED NOT NULL,
     ModelID SMALLINT UNSIGNED NOT NULL,
     ModelName VARCHAR(40),
     PRIMARY KEY (OrderID),
     FULLTEXT (ModelName)
  );
  
  CREATE TABLE Orders
  (
     OrderID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
     ModelID SMALLINT UNSIGNED NOT NULL
  );
  
  ALTER TABLE Orders
  ADD UNIQUE (OrderID, ModelID);
  
  CREATE TABLE Orders
  (
     OrderID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
     ModelID SMALLINT UNSIGNED NOT NULL
  );
  
  CREATE INDEX index_1 ON Orders (ModelID);
  
  CREATE TABLE Orders
  (
     OrderID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
     ModelID SMALLINT UNSIGNED NOT NULL,
     UNIQUE unique_1 (OrderID, ModelID)
  );
  
  ALTER TABLE Orders
  DROP INDEX unique_1;