-- Address Table CREATE COLUMN TABLE Address ( AddressID int Generated By Default as IDENTITY(start with 1 increment by 1) NOT NULL, AddressLine1 nvarchar(60) NOT NULL, AddressLine2 nvarchar(60) NULL, City nvarchar(30) NOT NULL, StateProvince nvarchar(50) NOT NULL, CountryRegion nvarchar(50) NOT NULL, PostalCode nvarchar(15) NOT NULL, rowguid varbinary(16) NOT NULL DEFAULT SYSUUID, ModifiedDate datetime NOT NULL DEFAULT CURRENT_DATE, CONSTRAINT PK_Address_AddressID PRIMARY KEY (AddressID), CONSTRAINT UK_Address_rowguid UNIQUE (rowguid) ); -- ProductCategory Table CREATE COLUMN TABLE ProductCategory ( ProductCategoryID int generated by default as identity(start with 1 increment by 1) NOT NULL, ParentProductCategoryID int NULL, Name nvarchar(50) NOT NULL, rowguid varbinary(16) NOT NULL DEFAULT SYSUUID, ModifiedDate datetime NOT NULL DEFAULT Current_Date, CONSTRAINT PK_ProductCategory_ProductCategoryID PRIMARY KEY (ProductCategoryID), CONSTRAINT AK_ProductCategory_Name UNIQUE (Name), CONSTRAINT AK_ProductCategory_rowguid UNIQUE (rowguid) ); ALTER TABLE ProductCategory ADD CONSTRAINT FK_ProductCategory_1 FOREIGN KEY (ParentProductCategoryID) REFERENCES ProductCategory(ProductCategoryID); -- ProductDescription Table CREATE COLUMN TABLE ProductDescription ( ProductDescriptionID int generated by default as identity(start with 1 increment by 1) NOT NULL, Description nvarchar(400) NOT NULL, rowguid varbinary(16) NOT NULL DEFAULT SYSUUID, ModifiedDate datetime NOT NULL DEFAULT current_date, CONSTRAINT PK_ProductDescription_ProductDescriptionID PRIMARY KEY(ProductDescriptionID), CONSTRAINT AK_ProductDescription_rowguid UNIQUE (rowguid) ); -- ProductModel Table CREATE COLUMN TABLE ProductModel ( ProductModelID int generated by default as identity(start with 1 increment by 1) NOT NULL, Name nvarchar(50) NOT NULL, CatalogDescription nvarchar(5000) NULL, rowguid varbinary(16) NOT NULL default sysuuid, ModifiedDate datetime NOT NULL default current_date, CONSTRAINT PK_ProductModel_ProductModelID PRIMARY KEY (ProductModelID), CONSTRAINT AK_ProductModel_Name UNIQUE (Name), CONSTRAINT AK_ProductModel_rowguid UNIQUE (rowguid) ); -- ProductModelProductDescription Table CREATE COLUMN TABLE ProductModelProductDescription ( ProductModelID int NOT NULL, ProductDescriptionID int NOT NULL, Culture nchar(6) NOT NULL, rowguid varbinary(16) NOT NULL DEFAULT SYSUUID, ModifiedDate datetime NOT NULL DEFAULT CURRENT_DATE, CONSTRAINT PK_ProductModelProductDescription_ProductModelID_ProductDescriptionID_Culture PRIMARY KEY (ProductModelID, ProductDescriptionID, Culture), CONSTRAINT AK_ProductModelProductDescription_rowguid UNIQUE (rowguid) ); ALTER TABLE ProductModelProductDescription ADD CONSTRAINT FK_ProductModelProductDescription_1 FOREIGN KEY(ProductDescriptionID) REFERENCES ProductDescription (ProductDescriptionID); ALTER TABLE ProductModelProductDescription ADD CONSTRAINT FK_ProductModelProductDescription_2 FOREIGN KEY(ProductModelID) REFERENCES ProductModel (ProductModelID); -- Product Table CREATE COLUMN TABLE Product ( ProductID int generated by default as IDENTITY(start with 1 increment by 1) NOT NULL, Name nvarchar(50) NOT NULL, ProductNumber nvarchar(25) NOT NULL, Color nvarchar(15) NULL, StandardCost decimal(19,4) NOT NULL, ListPrice decimal(19,4) NOT NULL, Size nvarchar(5) NULL, Weight decimal(8, 2) NULL, ProductCategoryID int NULL, ProductModelID int NULL, SellStartDate datetime NOT NULL, SellEndDate datetime NULL, DiscontinuedDate datetime NULL, ThumbNailPhoto blob NULL, ThumbnailPhotoFileName nvarchar(50) NULL, rowguid varbinary(16) NOT NULL DEFAULT SYSUUID, ModifiedDate datetime NOT NULL DEFAULT current_date, CONSTRAINT PK_Product_ProductID PRIMARY KEY (ProductID), CONSTRAINT AK_Product_Name UNIQUE (Name), CONSTRAINT AK_Product_ProductNumber UNIQUE (ProductNumber), CONSTRAINT AK_Product_rowguid UNIQUE (rowguid) ); ALTER TABLE Product ADD CONSTRAINT FK_Product_ProductCategory_ProductCategoryID FOREIGN KEY(ProductCategoryID) REFERENCES ProductCategory (ProductCategoryID); ALTER TABLE Product ADD CONSTRAINT FK_Product_ProductModel_ProductModelID FOREIGN KEY(ProductModelID) REFERENCES ProductModel (ProductModelID); ALTER TABLE Product ADD CHECK ( ListPrice >= 0.00 ); ALTER TABLE Product ADD CHECK ( StandardCost >= 0.00 ); ALTER TABLE Product ADD CHECK ( Weight > 0.00 ); --ALTER TABLE Product ADD CHECK ( COALESCE(SellEndDate, SellStartDate) >= SellStartDate ); ALTER TABLE Product ADD CHECK ( SellEndDate >= SellStartDate ); -- Customer Table CREATE COLUMN TABLE Customer ( CustomerID int generated by default as IDENTITY(start with 1 increment by 1) NOT NULL, NameStyle tinyint NOT NULL DEFAULT 0, Title nvarchar(8) NULL, FirstName nvarchar(50) NOT NULL, MiddleName nvarchar(50) NULL, LastName nvarchar(50) NOT NULL, Suffix nvarchar(10) NULL, CompanyName nvarchar(128) NULL, SalesPerson nvarchar(256) NULL, EmailAddress nvarchar(50) NULL, Phone nvarchar(25) NULL, PasswordHash varchar(128) NOT NULL, PasswordSalt varchar(10) NOT NULL, rowguid varbinary(16) NOT NULL DEFAULT SYSUUID, ModifiedDate datetime NOT NULL DEFAULT Current_Date, CONSTRAINT PK_Customer_CustomerID PRIMARY KEY (CustomerID), CONSTRAINT AK_Customer_rowguid UNIQUE (rowguid) ); -- CustomerAddress Table CREATE COLUMN TABLE CustomerAddress ( CustomerID int NOT NULL, AddressID int NOT NULL, AddressType varchar(50) NOT NULL, rowguid varbinary(16) NOT NULL DEFAULT SYSUUID, ModifiedDate datetime NOT NULL DEFAULT Current_Date, CONSTRAINT PK_CustomerAddress_CustomerID_AddressID PRIMARY KEY (CustomerID, AddressID), CONSTRAINT AK_CustomerAddress_rowguid UNIQUE (rowguid) ); ALTER TABLE CustomerAddress ADD CONSTRAINT FK_CustomerAddress_Address_AddressID FOREIGN KEY(AddressID) REFERENCES Address (AddressID); ALTER TABLE CustomerAddress ADD CONSTRAINT FK_CustomerAddress_Customer_CustomerID FOREIGN KEY(CustomerID) REFERENCES Customer (CustomerID); -- CustomerAddress Table CREATE COLUMN TABLE SalesOrderHeader ( SalesOrderID int generated by default as IDENTITY(start with 1 increment by 1) NOT NULL, RevisionNumber tinyint NOT NULL DEFAULT 0, OrderDate datetime NOT NULL DEFAULT current_date, DueDate datetime NOT NULL, ShipDate datetime NULL, Status tinyint NOT NULL DEFAULT 1, OnlineOrderFlag tinyint NOT NULL DEFAULT 1, SalesOrderNumber nvarchar(25) as ( ifnull( concat('SO', TO_VARCHAR(SalesOrderID) ), '*** ERROR ***')), PurchaseOrderNumber nvarchar(25) NULL, AccountNumber nvarchar(15) NULL, CustomerID int NOT NULL, ShipToAddressID int NULL, BillToAddressID int NULL, ShipMethod nvarchar(50) NOT NULL, CreditCardApprovalCode varchar(15) NULL, SubTotal decimal(19,4) NOT NULL DEFAULT 0.00, TaxAmt decimal(19,4) NOT NULL DEFAULT 0.00, Freight decimal(19,4) NOT NULL DEFAULT 0.00, TotalDue AS (ifnull((SubTotal+TaxAmt)+Freight,0)), Comment nvarchar(5000) NULL, rowguid varbinary(16) NOT NULL DEFAULT SYSUUID, ModifiedDate datetime NOT NULL DEFAULT current_date, CONSTRAINT PK_SalesOrderHeader_SalesOrderID PRIMARY KEY (SalesOrderID), CONSTRAINT AK_SalesOrderHeader_rowguid UNIQUE (rowguid) ); ALTER TABLE SalesOrderHeader ADD CONSTRAINT FK_SalesOrderHeader_Address_BillTo_AddressID FOREIGN KEY(BillToAddressID) REFERENCES Address (AddressID); ALTER TABLE SalesOrderHeader ADD CONSTRAINT FK_SalesOrderHeader_Address_ShipTo_AddressID FOREIGN KEY(ShipToAddressID) REFERENCES Address (AddressID); ALTER TABLE SalesOrderHeader ADD CONSTRAINT FK_SalesOrderHeader_Customer_CustomerID FOREIGN KEY(CustomerID) REFERENCES Customer (CustomerID); ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_DueDate CHECK ( DueDate >= OrderDate ); ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Freight CHECK ( Freight >= 0.00 ); -- ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_ShipDate01 CHECK ( ifnull(ShipDate, OrderDate) >= OrderDate ); ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_ShipDate01 CHECK ( ShipDate >= OrderDate ); ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Status_1 CHECK ( Status >= 0 ); ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Status_2 CHECK ( Status <= 8 ); ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_SubTotal CHECK ( SubTotal >= 0.00 ); ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_TaxAmt CHECK ( TaxAmt >= 0.00 ); -- SalesOrderDetail Table CREATE COLUMN TABLE SalesOrderDetail ( SalesOrderID int NOT NULL, SalesOrderDetailID int generated by default as IDENTITY(start with 1 increment by 1) NOT NULL, OrderQty smallint NOT NULL, ProductID int NOT NULL, UnitPrice decimal(19,4) NOT NULL, UnitPriceDiscount decimal(19,4) NOT NULL DEFAULT 0.0, LineTotal decimal(19,4) as (IFNULL(( UnitPrice * (1.0 - UnitPriceDiscount) * OrderQty ), 0)), rowguid varbinary(16) NOT NULL DEFAULT SYSUUID, ModifiedDate datetime NOT NULL DEFAULT CURRENT_DATE, CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID PRIMARY KEY (SalesOrderID, SalesOrderDetailID), CONSTRAINT AK_SalesOrderDetail_rowguid UNIQUE (rowguid) ); ALTER TABLE SalesOrderDetail ADD CONSTRAINT CK_SalesOrderDetail_OrderQty CHECK ( OrderQty > 0 ); ALTER TABLE SalesOrderDetail ADD CONSTRAINT CK_SalesOrderDetail_UnitPrice CHECK ( UnitPrice >= 0 ); ALTER TABLE SalesOrderDetail ADD CONSTRAINT CK_SalesOrderDetail_UnitPriceDiscount CHECK ( UnitPriceDiscount >= 0 ); ALTER TABLE SalesOrderDetail ADD CONSTRAINT FK_SalesOrderDetail_Product_ProductID FOREIGN KEY(ProductID) REFERENCES Product (ProductID); ALTER TABLE SalesOrderDetail ADD CONSTRAINT FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID FOREIGN KEY(SalesOrderID) REFERENCES SalesOrderHeader (SalesOrderID) ON DELETE CASCADE;