bigpractice-datawarehouse

CREATE USER user001 IDENTIFIED BY password001;
GRANT CONNECT TO user001;
GRANT CONNECT, RESOURCE, DBA TO user001;
-- GRANT CREATE SESSION GRANT ANY PRIVILEGE TO user001;
GRANT UNLIMITED TABLESPACE TO user001;
GRANT CREATE SESSION TO user001;
GRANT CREATE TABLE TO user001;
GRANT CREATE VIEW TO user001;


create table customer
(
    id             NUMBER GENERATED BY DEFAULT AS IDENTITY,
    name           VARCHAR2(100),
    city_code      VARCHAR2(100),
    first_order_at DATE,
    PRIMARY KEY (id)
);

create table travel_customer
(
    cus_id  NUMBER,
    courier VARCHAR2(100),
    time    DATE,
    PRIMARY KEY (cus_id),
    CONSTRAINT fk_customer FOREIGN KEY (cus_id) REFERENCES customer (id)
);

create table postoffice_customer
(
    cus_id         NUMBER,
    office_address VARCHAR2(100),
    time           DATE,
    PRIMARY KEY (cus_id),
    CONSTRAINT fk_post_office_customer FOREIGN KEY (cus_id) REFERENCES customer (id)
);

create table office
(
    id      NUMBER GENERATED BY DEFAULT AS IDENTITY,
    name    NVARCHAR2(100),
    address NVARCHAR2(100),
    state   NVARCHAR2(100),
    time    DATE,
    PRIMARY KEY (id)
);

create table store
(
    id        NUMBER GENERATED BY DEFAULT AS IDENTITY,
    office_id NUMBER,
    phone     NVARCHAR2(20),
    time      Date,
    primary key (id),
    CONSTRAINT fk_store_office FOREIGN KEY (office_id) REFERENCES office (id)
);

create table merchandise
(
    id          NUMBER GENERATED BY DEFAULT AS IDENTITY,
    description NVARCHAR2(500),
    sz          NVARCHAR2(100),
    weight      NUMBER,
    price       Number,
    time        Date,
    primary key (id)
);

create table stored_merchan
(
    store_id       NUMBER,
    merchandise_id NUMBER,
    quantity       NUMBER,
    time           DATE,
    primary key (store_id, merchandise_id),
    CONSTRAINT fk_stored_merchan_store FOREIGN KEY (store_id) REFERENCES store (id),
    CONSTRAINT fk_stored_merchan_merchan FOREIGN KEY (merchandise_id) REFERENCES merchandise (id)
);

create table cus_order (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY ,
    time NUMBER,
    cus_id NUMBER,
    PRIMARY KEY (id)
);

create table placed_merchan (
    order_id NUMBER,
    merchan_id NUMBER,
    quantity NUMBER,
    price NUMBER,
    time DATE,
    primary key (order_id, merchan_id) ,
    CONSTRAINT fk_place_merchan_order FOREIGN KEY (order_id) REFERENCES cus_order(id),
    CONSTRAINT fk_place_merchan_merchan FOREIGN KEY (merchan_id) REFERENCES merchandise(id)
);

CREATE DATABASE dw;
use dw;
create table customer
(
id INT PRIMARY KEY IDENTITY (1, 1),
name NVARCHAR(100),
city_code NVARCHAR(100),
first_order_at DATE,
);

create table travel_customer
(
cus_id INT,
courier NVARCHAR(100),
time DATE,
PRIMARY KEY (cus_id),
CONSTRAINT fk_customer FOREIGN KEY (cus_id) REFERENCES customer (id)
);

create table postoffice_customer
(
cus_id INT,
office_address NVARCHAR(100),
time DATE,
PRIMARY KEY (cus_id),
CONSTRAINT fk_post_office_customer FOREIGN KEY (cus_id) REFERENCES customer (id)
);

create table office
(
id INT PRIMARY KEY IDENTITY (1, 1),
name NVARCHAR(100),
address NVARCHAR(100),
state NVARCHAR(100),
time DATE,
);

create table store
(
id INT PRIMARY KEY IDENTITY (1, 1),
office_id INT,
phone NVARCHAR(20),
time Date,
CONSTRAINT fk_store_office FOREIGN KEY (office_id) REFERENCES office (id)
);

create table merchandise
(
id INT PRIMARY KEY IDENTITY (1, 1),
description NVARCHAR(500),
sz NVARCHAR(100),
weight INT,
price INT,
time Date,
);

create table stored_merchan
(
store_id INT,
merchandise_id INT,
quantity INT,
time DATE,
primary key (store_id, merchandise_id),
CONSTRAINT fk_stored_merchan_store FOREIGN KEY (store_id) REFERENCES store (id),
CONSTRAINT fk_stored_merchan_merchan FOREIGN KEY (merchandise_id) REFERENCES merchandise (id)
);

create table cus_order (
id INT PRIMARY KEY IDENTITY (1, 1) ,
time INT,
cus_id INT,
);

create table placed_merchan (
order_id INT,
merchan_id INT,
quantity INT,
price INT,
time DATE,
primary key (order_id, merchan_id) ,
CONSTRAINT fk_place_merchan_order FOREIGN KEY (order_id) REFERENCES cus_order(id),
CONSTRAINT fk_place_merchan_merchan FOREIGN KEY (merchan_id) REFERENCES merchandise(id)
);