/* 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;