USE bookshopdb

--Create 11 Tables
DROP TABLE Authors
CREATE TABLE Authors
-- Provide column names, data type, size, if nullable & default
       (
       AuthorID SMALLINT IDENTITY(101,1) NOT NULL,
       FirstName VARCHAR(30) NOT NULL DEFAULT 'unknown',
       LastName VARCHAR(30) NOT NULL,
       YearBorn CHAR(4) NOT NULL DEFAULT ' N/A ',
       YearDied CHAR(4) NOT NULL DEFAULT ' N/A ',
       Description VARCHAR(200) NOT NULL DEFAULT 'N/A'
       )

DROP TABLE BookCondition  
CREATE TABLE BookCondition       
( 
       ConditionID TINYINT NOT NULL,
       ConditionName CHAR(10) NOT NULL,
       Description VARCHAR(50) NOT NULL DEFAULT 'N/A'     
)

DROP TABLE BookAuthors
CREATE TABLE BookAuthors
       (
       AuthorID SMALLINT NOT NULL,
       TitleID CHAR(8) NOT NULL
       )

DROP TABLE Books
CREATE TABLE Books
      (
      TitleID CHAR(8) NOT NULL,
      Title VARCHAR(70) NOT NULL,
      Publisher VARCHAR(50) NOT NULL DEFAULT 'N/A',
      PubDate CHAR(4) NOT NULL DEFAULT 'N/A',
      Edition CHAR(4) NOT NULL DEFAULT 'N/A',
      Cost MONEY NOT NULL,
      SRP MONEY NOT NULL,
      ConditionID TINYINT NOT NULL,
      Sold BIT NOT NULL DEFAULT '0',
      )

DROP TABLE BookOrders
CREATE TABLE BookOrders
      (
      OrderID SMALLINT NOT NULL,
      TitleID CHAR(8) NOT NULL 
      )

DROP TABLE Customers
CREATE TABLE Customers
     (
      CustomerID SMALLINT IDENTITY(10,1) NOT NULL,
      FirstName VARCHAR(30) NOT NULL DEFAULT 'unknown',
      LastName VARCHAR(30) NOT NULL DEFAULT 'unknown',
      Phone VARCHAR(24) NOT NULL DEFAULT 'unknown',
      Address1 VARCHAR(60) NOT NULL DEFAULT 'unknown',
      Address2 VARCHAR(60) NOT NULL DEFAULT 'unknown',
      City VARCHAR(15) NOT NULL DEFAULT 'unknown',
      State VARCHAR(7) NOT NULL DEFAULT 'unknown',
      Zip VARCHAR(12) NOT NULL DEFAULT 'unknown'
      )

DROP TABLE Employees
CREATE TABLE Employees
      (
      EmployeeID SMALLINT IDENTITY NOT NULL,
      FirstName VARCHAR(30) NOT NULL,
      LastName VARCHAR(30) NOT NULL,
      Addrees1 VARCHAR(60) NOT NULL,
      Address2 VARCHAR(60) NOT NULL DEFAULT 'N/A',
      City VARCHAR(15) NOT NULL,
      State CHAR(2) NOT NULL,
      Zip VARCHAR(12) NOT NULL,
      Phone VARCHAR(24) NOT NULL,
      DOB DATETIME NOT NULL,
      HireDate DATETIME NOT NULL,
      PositionID TINYINT NOT NULL
      )

DROP TABLE FormOfPayment
CREATE TABLE FormOfPayment
      (
      PaymentID TINYINT NOT NULL,
      PaymentDescrip VARCHAR(12) NOT NULL
      )

DROP TABLE Orders
CREATE TABLE Orders
      (
      OrderID SMALLINT IDENTITY NOT NULL,
      CustomerID SMALLINT NOT NULL,
      EmployeeID SMALLINT NOT NULL,
      Amount MONEY NOT NULL DEFAULT 0,
      OrderDate DATETIME NOT NULL,
      DeliveryDate DATETIME NULL, 
      PaymentID TINYINT NOT NULL,
      StatusID TINYINT NOT NULL
      )

DROP TABLE OrderStatus
CREATE TABLE OrderStatus
      (
      StatusID TINYINT NOT NULL,
      StatusDescrip VARCHAR(25) NOT NULL 
      )

DROP TABLE Positions
CREATE TABLE Positions
      (
      PositionID TINYINT NOT NULL,
      Title VARCHAR(30) NOT NULL,
      JobDescrip VARCHAR(80) NOT NULL DEFAULT 'N/A'
      )