- -- Suppliers
- DROP TABLE Suppliers CASCADE CONSTRAINTS;
- CREATE TABLE Suppliers
- (
- SupplierID NUMBER(8) NOT NULL,
- CompanyName VARCHAR2(40) NOT NULL,
- 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(200),
- CONSTRAINT Suppliers_SupplierID_pk PRIMARY KEY (SupplierID)
- );
- -- Customers
- DROP TABLE Customers CASCADE CONSTRAINTS;
- CREATE TABLE Customers
- (
- CustomerID CHAR(5) NOT NULL,
- CompanyName VARCHAR2(40) NOT NULL,
- 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),
- Email VARCHAR2(50),
- CONSTRAINT Customers_CustomerID_pk PRIMARY KEY (CustomerID),
- CONSTRAINT Customers_Email_UK UNIQUE (Email)
- );
- -- Categories
- DROP TABLE Categories CASCADE CONSTRAINTS;
- CREATE TABLE Categories
- (
- CategoryID NUMBER(8) NOT NULL,
- CategoryName VARCHAR2(15) NOT NULL,
- Description VARCHAR2(300),
- CategoryCode NUMBER (6,0),
- CONSTRAINT Categories_CategoryID_pk PRIMARY KEY (CategoryID),
- CONSTRAINT Categories_CategoryCode_UK UNIQUE (CategoryCode)
- );
- -- Shippers
- DROP TABLE Shippers CASCADE CONSTRAINTS;
- CREATE TABLE Shippers
- (
- ShipperID NUMBER(8) NOT NULL,
- CompanyName VARCHAR2(40) NOT NULL,
- Phone VARCHAR2(24),
- CONSTRAINT Shippers_ShipperID_pk PRIMARY KEY (ShipperID)
- );
- -- Products
- DROP TABLE Products CASCADE CONSTRAINTS;
- CREATE TABLE Products
- (
- ProductID NUMBER(8) NOT NULL,
- ProductName VARCHAR2(40) NOT NULL,
- SupplierID NUMBER(8),
- CategoryID NUMBER(8),
- QuantityPerUnit VARCHAR2(20),
- UnitPrice NUMBER(8,2),
- UnitsInStock NUMBER(6),
- UnitsOnOrder NUMBER(6),
- ReorderLevel NUMBER(6),
- Discontinued NUMBER(1) NOT NULL,
- CONSTRAINT Products_ProductID_pk PRIMARY KEY (ProductID),
- CONSTRAINT Products_Suppliers_fk FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID),
- CONSTRAINT Products_Categories_fk FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
- CONSTRAINT Products_UnitPrice_CK CHECK (UnitPrice >= 0),
- CONSTRAINT Products_ReorderLevel_CK CHECK (ReorderLevel >= 0),
- CONSTRAINT Products_UnitsInStock_CK CHECK (UnitsInStock >= 0),
- CONSTRAINT Products_UnitsOnOrder_CK CHECK (UnitsOnOrder >= 0)
- );
- -- Employees
- DROP TABLE Employees CASCADE CONSTRAINTS;
- CREATE TABLE Employees
- (
- EmployeeID NUMBER(8) NOT NULL,
- LastName VARCHAR(20)NOT NULL,
- FirstName VARCHAR2(10) NOT NULL,
- 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),
- Notes VARCHAR2(600),
- ReportsTo NUMBER(8),
- PhotoPath VARCHAR2(255),
- SIN CHAR(9),
- CONSTRAINT Employees_EmployeeID_pk PRIMARY KEY (EmployeeID),
- CONSTRAINT Employees_ReportsTo_fk FOREIGN KEY (ReportsTo) REFERENCES Employees (EmployeeID),
- CONSTRAINT Employees_SIN_UK UNIQUE (SIN)
- );
- --Orders
- DROP TABLE Orders CASCADE CONSTRAINTS;
- CREATE TABLE Orders
- (
- OrderID NUMBER(8) NOT NULL,
- CustomerID CHAR(5),
- EmployeeID NUMBER(8),
- TerritoryID VARCHAR2(20),
- OrderDate DATE,
- RequiredDate DATE,
- ShippedDate DATE,
- ShipVia NUMBER(8),
- Freight NUMBER(8,2),
- ShipName VARCHAR2(40),
- ShipAddress VARCHAR2(60),
- ShipCity VARCHAR2(15),
- ShipRegion VARCHAR2(15),
- ShipPostalCode VARCHAR2(10),
- ShipCountry VARCHAR2(15),
- CONSTRAINT Orders_OrderID_pk PRIMARY KEY (OrderID),
- CONSTRAINT Orders_Customers_fk FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
- CONSTRAINT Orders_Employees_fk FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
- CONSTRAINT Orders_ShipVia_fk FOREIGN KEY (ShipVia)
- REFERENCES Shippers (ShipperID)
- );
- -- OrderDetails
- DROP TABLE OrderDetails CASCADE CONSTRAINTS;
- CREATE TABLE OrderDetails
- (
- OrderID NUMBER(8) NOT NULL,
- ProductID NUMBER(8) NOT NULL,
- UnitPrice NUMBER(8,2) NOT NULL,
- Quantity NUMBER(8) NOT NULL,
- Discount NUMBER(2,2) NOT NULL,
- CONSTRAINT OrderDetails_OID_PID_pk PRIMARY KEY (OrderID, ProductID),
- CONSTRAINT OrderDetails_Orders_fk FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
- CONSTRAINT OrderDetails_Products_fk FOREIGN KEY (ProductID) REFERENCES Products (ProductID),
- CONSTRAINT OrderDetails_Discount_CK CHECK (Discount >= 0 AND Discount <= 1),
- CONSTRAINT OrderDetails_Quantity_CK CHECK (Quantity > 0),
- CONSTRAINT OrderDetails_UnitPrice_CK CHECK (UnitPrice >= 0)
- );