IWD2-03/INFO-3169 Databases 2/labs/Lab 5/Lab 5.sql

-- (1) Customers --
DROP TABLE Customers CASCADE CONSTRAINTS; -- uncomment after 1st run

CREATE TABLE customers (
    customerId CHAR(5),
    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),
);

-- (2) Employees --
DROP TABLE Employees CASCADE CONSTRAINTS; -- uncomment after 1st run

CREATE TABLE employees (
    employeeId NUMBER(8,0),
    lastName VARCHAR2(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,0),
    photograph VARCHAR2(255),
    SIN CHAR(9),

    CONSTRAINT Employees_EmployeeId_pk PRIMARY KEY (employeeId),
    CONSTRAINT Employees_SIN_uk UNIQUE (SIN),
    CONSTRAINT Employees_ReportsTO_fk REFERENCES employees(employeeId),
);

-- (3) Shippers --
DROP TABLE Shippers CASCADE CONSTRAINTS; -- uncomment after 1st run

CREATE TABLE shippers (
    shipperId NUMBER(8,0) NOT NULL,
    companyName VARCHAR2(40) NOT NULL,
    phone VARCHAR2(24),

    CONSTRAINT Shippers_Shipper_ID_pk PRIMARY KEY (shipperId),
);

-- (4) Suppliers --
DROP TABLE Suppliers CASCADE CONSTRAINTS; -- uncomment after 1st run

CREATE TABLE suppliers (
    supplierId NUMBER(8,0) 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),
);

-- (5) Categories ---
DROP TABLE Categories CASCADE CONSTRAINTS; -- uncomment after 1st run

CREATE TABLE categories (
    categoryId NUMBER(8,0),
    categoryName VARCHAR2(15) NOT NULL,
    categoryCode NUMBER(6,0),
    description VARCHAR2(300),

    CONSTRAINT Categories_CategoryID_pk PRIMARY KEY (categoryId),
    CONSTRAINT Categories_CategoryCode_uk UNIQUE(categoryCode),
);

-- (6) Orders --
DROP TABLE Orders CASCADE CONSTRAINTS; -- uncomment after 1st run

CREATE TABLE orders (
    orderId NUMBER(8,0) NOT NULL,
    customerId CHAR(5),
    employeeId NUMBER(8,0),
    territoryId VARCHAR2(20),
    orderDate DATE,
    requiredDate DATE,
    shippedDate DATE,
    shipVia NUMBER(8,0),
    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_CustomerId_fk REFERENCES customers(customerId),
    CONSTRAINT Orders_EmployeeId_fk REFERENCES employees(employeeId),
    CONSTRAINT Orders_ShippedVia_fk REFERENCES shippers(shipperId),
);

-- (7) Products --
DROP TABLE Products CASCADE CONSTRAINTS; -- uncomment after 1st run

CREATE TABLE products (
    productId NUMBER(8,0) NOT NULL,
    productName VARCHAR2(40) NOT NULL,
    supplierId NUMBER(8,0),
    categoryId NUMBER(8,0),
    quantityPerUnit VARCHAR2(20),
    unitPrice NUMBER(8,2),
    unitsInStock NUMBER(6,0),
    unitsOnOrder NUMBER(6,0),
    reorderLevel NUMBER(6,0),
    discontinued NUMBER(1,0) NOT NULL,

    CONSTRAINT Products_ProductID_pk PRIMARY KEY (productId),
    CONSTRAINT Products_UnitPrice_ck CHECK (unitPrice >= 0),
    CONSTRAINT Products_ReorderLevel_ck CHECK (reorderLevel >= 0),
    CONSTRAINT Products_Quantity_ck CHECK (quantity >= 0),
    CONSTRAINT Products_UnitsOnOrder_ck CHECK (unitsOnOrder >= 0),
    CONSTRAINT Products_SupplierId_fk REFERENCES suppliers(supplierId),
    CONSTRAINT Products_CategoryId_fk REFERENCES categories(categoryId),
);

-- (8) OrderDetails --
DROP TABLE OrderDetails CASCADE CONSTRAINTS; -- uncomment after 1st run

CREATE TABLE orderDetails (
    orderId NUMBER(8,0) NOT NULL,
    productId NUMBER(8,0) NOT NULL,
    unitPrice NUMBER(8,2) NOT NULL,
    quantity NUMBER(6,0) NOT NULL,
    discount NUMBER(5,2) NOT NULL,

    CONSTRAINT orderDetails_OID_PID_pk PRIMARY KEY (orderId, 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),
    CONSTRAINT orderDetails_ProductId_fk REFERENCES products(productId),
);

SELECT table_name, 'Levi McLean' FROM user_tables
    WHERE table_name != 'HTMLDB_PLAN_TABLE';