Created
July 7, 2020 07:44
-
-
Save hirosumee/443e5adb6fd8f9e862ae0587a63c1d0f to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create database warehouse; | |
go; | |
use warehouse; | |
drop table if exists fact_sale_tbl; | |
drop table if exists time_tbl; | |
drop table if exists customer_tbl; | |
drop table if exists store_tbl; | |
drop table if exists stored_merchan_tbl; | |
drop table if exists merchandise_tbl; | |
go; | |
create table time_tbl | |
( | |
id INT PRIMARY KEY IDENTITY (1, 1), | |
day INT, | |
month INT, | |
quarter INT, | |
year INT, | |
full_date date | |
) | |
create table customer_tbl | |
( | |
id INT PRIMARY KEY, | |
type NVARCHAR(30), | |
country_code INT, | |
name NVARCHAR(400), | |
first_ordered_at DATE, | |
courier NVARCHAR(200), | |
address NVARCHAR(200), | |
sign_up_at date, | |
--CONSTRAINT fk_cs_stime FOREIGN KEY (sign_up_at) REFERENCES time_tbl(id), | |
) | |
create table store_tbl | |
( | |
id INT PRIMARY KEY, | |
phone NVARCHAR(200), | |
country_code INT, | |
office_name NVARCHAR(400), | |
office_address NVARCHAR(400), | |
state NVARCHAR(100), | |
store_created_at date, | |
office_created_at date, | |
--CONSTRAINT fk_s_stime FOREIGN KEY (store_created_at) REFERENCES time_tbl(id), | |
--CONSTRAINT fk_s_otime FOREIGN KEY (office_created_at) REFERENCES time_tbl(id), | |
); | |
create table merchandise_tbl | |
( | |
id INT PRIMARY KEY, | |
description NVARCHAR(500), | |
sz NVARCHAR(100), | |
weight INT, | |
price INT, | |
producted_at date | |
--CONSTRAINT fk_m_time FOREIGN KEY (producted_at) REFERENCES time_tbl(id), | |
); | |
create table stored_merchan_tbl | |
( | |
id INT PRIMARY KEY IDENTITY (1, 1), | |
store_id INT, | |
merchan_id INT, | |
quatity INT, | |
stored_at date | |
CONSTRAINT fk_sm_store FOREIGN KEY (store_id) REFERENCES store_tbl (id), | |
CONSTRAINT fk_sm_merchan FOREIGN KEY (merchan_id) REFERENCES merchandise_tbl (id), | |
--CONSTRAINT fk_sm_time FOREIGN KEY (stored_at) REFERENCES time_tbl(id), | |
); | |
create table fact_sale_tbl | |
( | |
id INT PRIMARY KEY IDENTITY (1, 1), | |
time_id INT, | |
store_id INT, | |
mechandise_id INT, | |
order_id INT, | |
customer_id INT, | |
quantity INT, | |
price FLOAT, | |
total_price FLOAT | |
CONSTRAINT fk_fs_time FOREIGN KEY (time_id) REFERENCES time_tbl (id), | |
CONSTRAINT fk_fs_mechan FOREIGN KEY (mechandise_id) REFERENCES merchandise_tbl (id), | |
CONSTRAINT fk_fs_custo FOREIGN KEY (customer_id) REFERENCES customer_tbl (id), | |
CONSTRAINT fk_fs_stor FOREIGN KEY (store_id) REFERENCES store_tbl (id) | |
); | |
go; |
Author
hirosumee
commented
Jul 7, 2020
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment