-------------------------------------------------------- -- DDL for Table CATEGORIES -------------------------------------------------------- CREATE TABLE "NORTHWIND"."CATEGORIES" ("CATEGORYID" NUMBER(1,0), "CATEGORYNAME" VARCHAR2(15), "DESCRIPTION" CLOB, "PICTURE" BLOB); -------------------------------------------------------- -- DDL for Table CUSTOMERS -------------------------------------------------------- CREATE TABLE "NORTHWIND"."CUSTOMERS" ("CUSTOMERID" VARCHAR2(5), "COMPANYNAME" VARCHAR2(40), "CONTACTNAME" VARCHAR2(30), "CONTACTTITLE" VARCHAR2(30), "ADDRESS" VARCHAR2(60), "CITY" VARCHAR2(15), "REGION" VARCHAR2(15), "POSTALCODE" VARCHAR2(10), "COUNTRY" VARCHAR2(15), "PHONE" VARCHAR2(24), "FAX" VARCHAR2(24)); -------------------------------------------------------- -- DDL for Table EMPLOYEES -------------------------------------------------------- CREATE TABLE "NORTHWIND"."EMPLOYEES" ("EMPLOYEEID" NUMBER(1,0), "LASTNAME" VARCHAR2(20), "FIRSTNAME" VARCHAR2(10), "TITLE" VARCHAR2(30), "TITLEOFCOURTESY" VARCHAR2(25), "BIRTHDATE" DATE, "HIREDATE" DATE, "ADDRESS" VARCHAR2(60), "CITY" VARCHAR2(15), "REGION" VARCHAR2(15), "POSTALCODE" VARCHAR2(10), "COUNTRY" VARCHAR2(15), "HOMEPHONE" VARCHAR2(24), "EXTENSION" VARCHAR2(4), "PHOTO" BLOB, "NOTES" CLOB, "REPORTSTO" NUMBER, "PHOTOPATH" VARCHAR2(255)); -------------------------------------------------------- -- DDL for Table ORDER_DETAILS -------------------------------------------------------- CREATE TABLE "NORTHWIND"."ORDER_DETAILS" ("ORDERID" NUMBER, "PRODUCTID" NUMBER, "UNITPRICE" NUMBER, "QUANTITY" NUMBER, "DISCOUNT" NUMBER); -------------------------------------------------------- -- DDL for Table ORDERS -------------------------------------------------------- CREATE TABLE "NORTHWIND"."ORDERS" ("ORDERID" NUMBER, "CUSTOMERID" VARCHAR2(5), "EMPLOYEEID" NUMBER, "ORDERDATE" DATE, "REQUIREDDATE" DATE, "SHIPPEDDATE" DATE, "SHIPVIA" NUMBER, "FREIGHT" NUMBER, "SHIPNAME" VARCHAR2(40), "SHIPADDRESS" VARCHAR2(60), "SHIPCITY" VARCHAR2(15), "SHIPREGION" VARCHAR2(15), "SHIPPOSTALCODE" VARCHAR2(10), "SHIPCOUNTRY" VARCHAR2(15)); -------------------------------------------------------- -- DDL for Table PRODUCTS -------------------------------------------------------- CREATE TABLE "NORTHWIND"."PRODUCTS" ("PRODUCTID" NUMBER, "PRODUCTNAME" VARCHAR2(40), "SUPPLIERID" NUMBER, "CATEGORYID" NUMBER, "QUANTITYPERUNIT" VARCHAR2(20), "UNITPRICE" NUMBER, "UNITSINSTOCK" NUMBER, "UNITSONORDER" NUMBER, "REORDERLEVEL" NUMBER, "DISCONTINUED" NUMBER); -------------------------------------------------------- -- DDL for Table SHIPPERS -------------------------------------------------------- CREATE TABLE "NORTHWIND"."SHIPPERS" ("SHIPPERID" NUMBER, "COMPANYNAME" VARCHAR2(40), "PHONE" VARCHAR2(24)); -------------------------------------------------------- -- DDL for Table SUPPLIERS -------------------------------------------------------- CREATE TABLE "NORTHWIND"."SUPPLIERS" ("SUPPLIERID" NUMBER, "COMPANYNAME" VARCHAR2(40), "CONTACTNAME" VARCHAR2(30), "CONTACTTITLE" VARCHAR2(30), "ADDRESS" VARCHAR2(60), "CITY" VARCHAR2(15), "REGION" VARCHAR2(15), "POSTALCODE" VARCHAR2(10), "COUNTRY" VARCHAR2(15), "PHONE" VARCHAR2(24), "FAX" VARCHAR2(24), "HOMEPAGE" VARCHAR2(2000)); -------------------------------------------------------- -- Constraints for Table CUSTOMERS -------------------------------------------------------- ALTER TABLE "NORTHWIND"."CUSTOMERS" ADD CONSTRAINT "PK_CUSTOMERS" PRIMARY KEY ("CUSTOMERID") ENABLE; ALTER TABLE "NORTHWIND"."CUSTOMERS" MODIFY ("COMPANYNAME" NOT NULL ENABLE); ALTER TABLE "NORTHWIND"."CUSTOMERS" MODIFY ("CUSTOMERID" NOT NULL ENABLE); -------------------------------------------------------- -- Constraints for Table ORDER_DETAILS -------------------------------------------------------- ALTER TABLE "NORTHWIND"."ORDER_DETAILS" ADD CONSTRAINT "PK_ORDER_DETAILS" PRIMARY KEY ("ORDERID", "PRODUCTID") ENABLE; ALTER TABLE "NORTHWIND"."ORDER_DETAILS" ADD CONSTRAINT "CK_UNITPRICE" CHECK (UnitPrice >= 0) ENABLE; ALTER TABLE "NORTHWIND"."ORDER_DETAILS" ADD CONSTRAINT "CK_QUANTITY" CHECK (Quantity > 0) ENABLE; ALTER TABLE "NORTHWIND"."ORDER_DETAILS" ADD CONSTRAINT "CK_DISCOUNT" CHECK (Discount >= 0 and (Discount <= 1)) ENABLE; ALTER TABLE "NORTHWIND"."ORDER_DETAILS" MODIFY ("DISCOUNT" NOT NULL ENABLE); ALTER TABLE "NORTHWIND"."ORDER_DETAILS" MODIFY ("QUANTITY" NOT NULL ENABLE); ALTER TABLE "NORTHWIND"."ORDER_DETAILS" MODIFY ("UNITPRICE" NOT NULL ENABLE); ALTER TABLE "NORTHWIND"."ORDER_DETAILS" MODIFY ("PRODUCTID" NOT NULL ENABLE); ALTER TABLE "NORTHWIND"."ORDER_DETAILS" MODIFY ("ORDERID" NOT NULL ENABLE); -------------------------------------------------------- -- Constraints for Table ORDERS -------------------------------------------------------- ALTER TABLE "NORTHWIND"."ORDERS" ADD CONSTRAINT "PK_ORDERS" PRIMARY KEY ("ORDERID") ENABLE; ALTER TABLE "NORTHWIND"."ORDERS" MODIFY ("ORDERID" NOT NULL ENABLE); -------------------------------------------------------- -- Constraints for Table SUPPLIERS -------------------------------------------------------- ALTER TABLE "NORTHWIND"."SUPPLIERS" ADD CONSTRAINT "PK_SUPPLIERS" PRIMARY KEY ("SUPPLIERID") ENABLE; ALTER TABLE "NORTHWIND"."SUPPLIERS" MODIFY ("COMPANYNAME" NOT NULL ENABLE); ALTER TABLE "NORTHWIND"."SUPPLIERS" MODIFY ("SUPPLIERID" NOT NULL ENABLE); -------------------------------------------------------- -- Constraints for Table CATEGORIES -------------------------------------------------------- ALTER TABLE "NORTHWIND"."CATEGORIES" ADD CONSTRAINT "PK_CATEGORIES" PRIMARY KEY ("CATEGORYID") ENABLE; ALTER TABLE "NORTHWIND"."CATEGORIES" MODIFY ("CATEGORYNAME" NOT NULL ENABLE); ALTER TABLE "NORTHWIND"."CATEGORIES" MODIFY ("CATEGORYID" NOT NULL ENABLE); -------------------------------------------------------- -- Constraints for Table PRODUCTS -------------------------------------------------------- ALTER TABLE "NORTHWIND"."PRODUCTS" ADD CONSTRAINT "PK_PRODUCTS" PRIMARY KEY ("PRODUCTID") ENABLE; ALTER TABLE "NORTHWIND"."PRODUCTS" ADD CONSTRAINT "CK_UNITSONORDER" CHECK (UnitsOnOrder >= 0) ENABLE; ALTER TABLE "NORTHWIND"."PRODUCTS" ADD CONSTRAINT "CK_UNITSINSTOCK" CHECK (UnitsInStock >= 0) ENABLE; ALTER TABLE "NORTHWIND"."PRODUCTS" ADD CONSTRAINT "CK_REORDERLEVEL" CHECK (ReorderLevel >= 0) ENABLE; ALTER TABLE "NORTHWIND"."PRODUCTS" ADD CONSTRAINT "CK_PRODUCTS_UNITPRICE" CHECK (UnitPrice >= 0) ENABLE; ALTER TABLE "NORTHWIND"."PRODUCTS" MODIFY ("DISCONTINUED" NOT NULL ENABLE); ALTER TABLE "NORTHWIND"."PRODUCTS" MODIFY ("PRODUCTNAME" NOT NULL ENABLE); ALTER TABLE "NORTHWIND"."PRODUCTS" MODIFY ("PRODUCTID" NOT NULL ENABLE); -------------------------------------------------------- -- Constraints for Table SHIPPERS -------------------------------------------------------- ALTER TABLE "NORTHWIND"."SHIPPERS" ADD CONSTRAINT "PK_SHIPPERS" PRIMARY KEY ("SHIPPERID") ENABLE; ALTER TABLE "NORTHWIND"."SHIPPERS" MODIFY ("COMPANYNAME" NOT NULL ENABLE); ALTER TABLE "NORTHWIND"."SHIPPERS" MODIFY ("SHIPPERID" NOT NULL ENABLE); -------------------------------------------------------- -- Constraints for Table EMPLOYEES -------------------------------------------------------- ALTER TABLE "NORTHWIND"."EMPLOYEES" ADD CONSTRAINT "EMPLOYEES_PK" PRIMARY KEY ("EMPLOYEEID") ENABLE; ALTER TABLE "NORTHWIND"."EMPLOYEES" MODIFY ("FIRSTNAME" NOT NULL ENABLE); ALTER TABLE "NORTHWIND"."EMPLOYEES" MODIFY ("LASTNAME" NOT NULL ENABLE); ALTER TABLE "NORTHWIND"."EMPLOYEES" MODIFY ("EMPLOYEEID" NOT NULL ENABLE); -------------------------------------------------------- -- Ref Constraints for Table ORDER_DETAILS -------------------------------------------------------- ALTER TABLE "NORTHWIND"."ORDER_DETAILS" ADD CONSTRAINT "FK_ORDER_DETAILS_ORDERS" FOREIGN KEY ("ORDERID") REFERENCES "NORTHWIND"."ORDERS" ("ORDERID") ENABLE; ALTER TABLE "NORTHWIND"."ORDER_DETAILS" ADD CONSTRAINT "FK_ORDER_DETAILS_PRODUCTS" FOREIGN KEY ("PRODUCTID") REFERENCES "NORTHWIND"."PRODUCTS" ("PRODUCTID") ENABLE; -------------------------------------------------------- -- Ref Constraints for Table ORDERS -------------------------------------------------------- ALTER TABLE "NORTHWIND"."ORDERS" ADD CONSTRAINT "FK_ORDERS_CUSTOMERS" FOREIGN KEY ("CUSTOMERID") REFERENCES "NORTHWIND"."CUSTOMERS" ("CUSTOMERID") ENABLE; ALTER TABLE "NORTHWIND"."ORDERS" ADD CONSTRAINT "FK_ORDERS_EMPLOYEES" FOREIGN KEY ("EMPLOYEEID") REFERENCES "NORTHWIND"."EMPLOYEES" ("EMPLOYEEID") ENABLE; -------------------------------------------------------- -- Ref Constraints for Table PRODUCTS -------------------------------------------------------- ALTER TABLE "NORTHWIND"."PRODUCTS" ADD CONSTRAINT "FK_PRODUCTS_CATEGORIES" FOREIGN KEY ("CATEGORYID") REFERENCES "NORTHWIND"."CATEGORIES" ("CATEGORYID") ENABLE; grant select on Northwind.Orders to NorthwindDW; grant select on Northwind.Customers to NorthwindDW; grant select on Northwind.Categories to NorthwindDW; grant select on Northwind.Products to NorthwindDW; grant select on Northwind.Employees to NorthwindDW; grant select on Northwind.Order_Details to NorthwindDW;