Untitled

-- CENTRAL PART

-- Inventory (iid, quantity, max_capacity)
CREATE TABLE Inventory(
    iid number(6) constraint inventory_iid_pk primary key,
    quantity number(4) constraint inventory_check_quantity CHECK (quantity > 0),
    max_capacity number(4) constraint inventory_max_capacity_nn not null
);

-- Product (pid, price, name, iid )
CREATE TABLE Product(
    pid number(6) constraint product_pid_pk primary key,
    price number(6) constraint product_price_nn not null,
    pname varchar2(20) constraint product_name_nn not null,
    iid number(6) constraint product_iid_fk references inventory(iid)
);


-- Household ( pid , size, material)
CREATE TABLE Household(
    pid number(6) constraint household_pid_fk primary key references product(pid),
    hsize varchar2(8) constraint household_hsize_nn not null,
    material varchar2(10) constraint household_material_nn not null
);

-- Household_Features ( pid , feature_id, description)
CREATE TABLE Household_Features(
    featureid number(4),
    pid number(6) constraint household_features_pid_fk references household(pid),
    fdescription varchar2(30) constraint household_features_fdescription_nn not null,
    -- constraint household_features_pid_and_featureid_uk UNIQUE (pid, featureid)
    constraint household_features_pid_and_featureid_pk PRIMARY KEY (pid, featureid)
);


-- Electronic ( pid , power_source, type, brand)
CREATE TABLE Electronic(
    pid number(6) constraint electronic_pid_fk primary key references product(pid),
    power_source varchar2(10) constraint electronic_power_source_nn not null,
    etype varchar2(10) constraint electronic_etype_nn not null,
    brand varchar2(18) constraint electronic_brand_nn not null
);

-- Electronic_Specifications ( pid , specification_id, description)
CREATE TABLE Electronic_Specifications(
    spec_id number(4),
    pid number(6) constraint electronic_specifications_pid_fk references electronic(pid),
    sdescription varchar2(30) constraint electronic_specifications_sdescription_nn not null,
    constraint electronic_specifications_pid_and_spec_id_pk PRIMARY KEY (pid, spec_id)
);

-- Clothing ( pid , gender, size, fabric)
CREATE TABLE Clothing(
    pid number(6) constraint clothing_pid_fk primary key references product(pid),
    gender char(1), -- Might be unisex
    csize number(2) constraint clothing_csize_nn not null,
    fabric varchar2(20) constraint clothing_fabric_nn not null
);

-- LEFT PART

-- Customer(cid, first_name, last_name)
CREATE TABLE Customer(
    cid number(7) constraint customer_cid_pk primary key,
    fname varchar2(25) constraint customer_fname_nn not null,
    lname varchar2(25) constraint customer_lname_nn not null
);

-- Sale (s_id, date, time, cid )
CREATE TABLE Sale(
    s_id number(10) constraint sale_s_id_pk primary key,
    sdate date constraint sale_sdate_nn not null,
    stime timestamp constraint sale_stime_nn not null, 
    cid number(7) constraint sale_cid_fk references customer(cid)
);


-- Sale_Product (spid, s_id, pid, quantity)
CREATE TABLE Sale_Product(
    s_id number(10) constraint sale_product_s_id_fk references sale(s_id),
    pid number(6) constraint sale_product_pid_fk references product(pid),
    spid number(10),
    quantity number(4) constraint check_quantity CHECK (quantity > 0),
    constraint sale_product_pid_and_sid_and_spid_pk PRIMARY KEY (pid, s_id, spid)
);


-- RIGHT PART

-- Supplier (suid, phone, address, name)
CREATE TABLE Supplier(
    suid number(5) constraint supplier_suid_pk primary key,
    phone char(11) constraint supplier_phone_nn not null,
    address varchar2(25) constraint supplier_address_nn not null,
    sname varchar2(25) constraint supplier_sname_nn not null
);

-- Purchase (puid, date, time, suid )
CREATE TABLE Purchase(
    puid number(8) constraint purchase_puid_pk primary key,
    pdate date constraint purchase_pdate_nn not null,
    ptime timestamp constraint purchase_ptime_nn not null, 
    suid number(5) constraint Purchase_suid_fk references supplier(suid)
);


-- Supplied_Product (spid, pid , puid , quantity)
CREATE TABLE Supplied_Product(
    puid number(8) constraint supplied_product_puid_fk references purchase(puid),
    pid number(6) constraint supplied_product_pid_fk references product(pid),
    spid number(10),
    quantity number(4) constraint supplied_product_check_quantity CHECK (quantity > 0),
    constraint supplied_product_pid_and_puid_and_spid_pk PRIMARY KEY (pid, puid, spid)
);