Facebook
From Baby Peafowl, 4 Years ago, written in Plain Text.
This paste is a reply to from - view diff
Embed
Download Paste or View Raw
Hits: 388
  1. -- Suppliers
  2. DROP TABLE Suppliers CASCADE CONSTRAINTS;
  3. CREATE TABLE Suppliers
  4. (
  5.   SupplierID  NUMBER(8) NOT NULL,
  6.   CompanyName VARCHAR2(40) NOT NULL,
  7.   ContactName VARCHAR2(30),
  8.   ContactTitle VARCHAR2(30),
  9.   Address VARCHAR2(60),
  10.   City VARCHAR2(15),
  11.   Region VARCHAR2(15),
  12.   PostalCode VARCHAR2(10),
  13.   Country VARCHAR2(15),
  14.   Phone VARCHAR2(24),
  15.   Fax VARCHAR2(24),
  16.   Homepage VARCHAR2(200),
  17.   CONSTRAINT Suppliers_SupplierID_pk PRIMARY KEY (SupplierID)
  18. );
  19.  
  20. -- Customers
  21. DROP TABLE Customers CASCADE CONSTRAINTS;
  22. CREATE TABLE Customers
  23. (
  24.   CustomerID CHAR(5) NOT NULL,
  25.   CompanyName VARCHAR2(40) NOT NULL,
  26.   ContactName VARCHAR2(30),
  27.   ContactTitle VARCHAR2(30),
  28.   Address VARCHAR2(60),
  29.   City VARCHAR2(15),
  30.   Region VARCHAR2(15),
  31.   PostalCode VARCHAR2(10),
  32.   Country VARCHAR2(15),
  33.   Phone VARCHAR2(24),
  34.   Fax VARCHAR2(24),
  35.   Email VARCHAR2(50),
  36.   CONSTRAINT Customers_CustomerID_pk PRIMARY KEY (CustomerID),
  37.   CONSTRAINT Customers_Email_UK UNIQUE (Email)
  38. );
  39.  
  40. -- Categories
  41. DROP TABLE Categories CASCADE CONSTRAINTS;
  42. CREATE TABLE Categories
  43. (
  44.   CategoryID NUMBER(8) NOT NULL,
  45.   CategoryName VARCHAR2(15) NOT NULL,
  46.   Description VARCHAR2(300),
  47.   CategoryCode NUMBER (6,0),
  48.   CONSTRAINT Categories_CategoryID_pk PRIMARY KEY (CategoryID),
  49.   CONSTRAINT Categories_CategoryCode_UK UNIQUE (CategoryCode)
  50. );
  51.  
  52. -- Shippers
  53. DROP TABLE Shippers CASCADE CONSTRAINTS;
  54. CREATE TABLE Shippers
  55. (
  56.   ShipperID NUMBER(8) NOT NULL,
  57.   CompanyName VARCHAR2(40) NOT NULL,
  58.   Phone VARCHAR2(24),
  59.   CONSTRAINT Shippers_ShipperID_pk PRIMARY KEY (ShipperID)
  60. );
  61.  
  62. -- Products
  63. DROP TABLE Products CASCADE CONSTRAINTS;
  64. CREATE TABLE Products
  65. (
  66.   ProductID NUMBER(8) NOT NULL,
  67.   ProductName VARCHAR2(40) NOT NULL,
  68.   SupplierID NUMBER(8),
  69.   CategoryID NUMBER(8),
  70.   QuantityPerUnit VARCHAR2(20),
  71.   UnitPrice NUMBER(8,2),
  72.   UnitsInStock NUMBER(6),
  73.   UnitsOnOrder NUMBER(6),
  74.   ReorderLevel NUMBER(6),
  75.   Discontinued NUMBER(1) NOT NULL,
  76.   CONSTRAINT Products_ProductID_pk PRIMARY KEY (ProductID),
  77.   CONSTRAINT Products_Suppliers_fk FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID),
  78.   CONSTRAINT Products_Categories_fk FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
  79.   CONSTRAINT Products_UnitPrice_CK CHECK (UnitPrice >= 0),
  80.   CONSTRAINT Products_ReorderLevel_CK CHECK (ReorderLevel >= 0),
  81.   CONSTRAINT Products_UnitsInStock_CK CHECK (UnitsInStock >= 0),
  82.   CONSTRAINT Products_UnitsOnOrder_CK CHECK (UnitsOnOrder >= 0)
  83. );
  84.  
  85. -- Employees
  86. DROP TABLE Employees CASCADE CONSTRAINTS;
  87. CREATE TABLE Employees
  88. (
  89.   EmployeeID NUMBER(8) NOT NULL,
  90.   LastName VARCHAR(20)NOT NULL,
  91.   FirstName VARCHAR2(10) NOT NULL,
  92.   Title VARCHAR2(30),
  93.   TitleOfCourtesy VARCHAR2(25),
  94.   BirthDate DATE,
  95.   HireDate DATE,
  96.   Address VARCHAR2(60),
  97.   City VARCHAR2(15),
  98.   Region VARCHAR2(15),
  99.   PostalCode VARCHAR2(10),
  100.   Country VARCHAR2(15),
  101.   HomePhone VARCHAR2(24),
  102.   Extension VARCHAR2(4),
  103.   Notes VARCHAR2(600),
  104.   ReportsTo NUMBER(8),
  105.   PhotoPath VARCHAR2(255),
  106.   SIN CHAR(9),
  107.   CONSTRAINT Employees_EmployeeID_pk PRIMARY KEY (EmployeeID),
  108.   CONSTRAINT Employees_ReportsTo_fk FOREIGN KEY (ReportsTo) REFERENCES Employees (EmployeeID),
  109.   CONSTRAINT Employees_SIN_UK UNIQUE (SIN)
  110. );
  111.  
  112. --Orders
  113. DROP TABLE Orders CASCADE CONSTRAINTS;
  114. CREATE TABLE Orders
  115. (
  116.   OrderID NUMBER(8) NOT NULL,
  117.   CustomerID CHAR(5),
  118.   EmployeeID NUMBER(8),
  119.   TerritoryID VARCHAR2(20),
  120.   OrderDate DATE,
  121.   RequiredDate DATE,
  122.   ShippedDate DATE,
  123.   ShipVia NUMBER(8),
  124.   Freight NUMBER(8,2),
  125.   ShipName VARCHAR2(40),
  126.   ShipAddress VARCHAR2(60),
  127.   ShipCity VARCHAR2(15),
  128.   ShipRegion VARCHAR2(15),
  129.   ShipPostalCode VARCHAR2(10),
  130.   ShipCountry VARCHAR2(15),
  131.   CONSTRAINT Orders_OrderID_pk PRIMARY KEY (OrderID),
  132.   CONSTRAINT Orders_Customers_fk FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
  133.   CONSTRAINT Orders_Employees_fk FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
  134.   CONSTRAINT Orders_ShipVia_fk FOREIGN KEY (ShipVia)
  135.   REFERENCES Shippers (ShipperID)
  136. );
  137.  
  138. -- OrderDetails
  139. DROP TABLE OrderDetails CASCADE CONSTRAINTS;
  140. CREATE TABLE OrderDetails
  141. (
  142.   OrderID NUMBER(8) NOT NULL,
  143.   ProductID NUMBER(8) NOT NULL,
  144.   UnitPrice NUMBER(8,2) NOT NULL,
  145.   Quantity NUMBER(8) NOT NULL,
  146.   Discount NUMBER(2,2) NOT NULL,
  147.   CONSTRAINT OrderDetails_OID_PID_pk PRIMARY KEY (OrderID, ProductID),
  148.   CONSTRAINT OrderDetails_Orders_fk FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
  149.   CONSTRAINT OrderDetails_Products_fk FOREIGN KEY (ProductID) REFERENCES Products (ProductID),
  150.   CONSTRAINT OrderDetails_Discount_CK CHECK (Discount >= 0 AND Discount <= 1),
  151.   CONSTRAINT OrderDetails_Quantity_CK CHECK (Quantity > 0),
  152.   CONSTRAINT OrderDetails_UnitPrice_CK CHECK (UnitPrice >= 0)
  153. );
  154.