cs520/SQLexamples/dataware_house_schemas.sql at master · IITDBGroup/cs520 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
-- ********************************************************************************
-- * EXAMPLES FOR THE STAR AND SNOWFLAKE SCHEMAS USED TO ENCODE DATA CUBES
-- * AS RELATIONAL DATABASES
-- ********************************************************************************

-- ********************************************************************************
-- * STAR SCHEMA
-- ********************************************************************************
DROP SCHEMA star CASCADE;
CREATE SCHEMA star;

-- DIMENSION TIME: year --> month ------> day (D_min)
--                      \            /
--                       -> woy    -/
CREATE TABLE star.time (
  time_id SERIAL PRIMARY KEY,
  tyear INT,
  tmonth INT,
  twoy INT,
  tday INT
  );

-- DIMENSION LOCATION: country -> state -> zip -> street
CREATE TABLE star.location (
  location_id SERIAL PRIMARY KEY,
  country TEXT,
  state TEXT,
  zip NUMERIC(5,5),
  street TEXT
  );

-- FACT TABLE (num_sales) for dimensions location and time
CREATE TABLE star.fact (
  time_id INT NOT NULL,
  location_id INT NOT NULL,
  num_sales INT,
  PRIMARY KEY (time_id, location_id),
  FOREIGN KEY (time_id) REFERENCES star.time,
  FOREIGN KEY (location_id) REFERENCES star.location
  );

-- ********************************************************************************
-- * STAR SCHEMA
-- ********************************************************************************
DROP SCHEMA snowflake CASCADE;
CREATE SCHEMA snowflake;

-- DIMENSION TIME
CREATE TABLE snowflake.time_year (
  time_year_id SERIAL PRIMARY KEY,
  tyear INT
);

CREATE TABLE snowflake.time_month (
  time_month_id SERIAL PRIMARY KEY,
  time_year_id INT NOT NULL REFERENCES snowflake.time_year,
  tmonth INT
);

CREATE TABLE snowflake.time_woy (
  time_woy_id INT PRIMARY KEY,
  time_year_id INT NOT NULL REFERENCES snowflake.time_year,
  woy INT
);

-- this is D_min for dimension time
CREATE TABLE snowflake.time_day (
  time_day_id SERIAL PRIMARY KEY,
  time_month_id INT NOT NULL REFERENCES snowflake.time_month,
  time_woy_id INT NOT NULL REFERENCES snowflake.time_woy,
  day INT
);

-- DIMENSION LOCATION
CREATE TABLE snowflake.location_country (
  location_country_id SERIAL PRIMARY KEY,
  country TEXT
);

CREATE TABLE snowflake.location_state (
  location_state_id SERIAL PRIMARY KEY,
  location_country_id INT NOT NULL REFERENCES snowflake.location_country,
  state TEXT
  );

CREATE TABLE snowflake.location_zip (
  location_zip_id SERIAL PRIMARY KEY,
  location_state_id INT NOT NULL REFERENCES snowflake.location_state,
  zip NUMERIC(5,5)
  );

CREATE TABLE snowflake.location_street (
  location_street_id SERIAL PRIMARY KEY,
  location_zip_id INT NOT NULL REFERENCES snowflake.location_zip,
  street TEXT
  );

-- the fact table
CREATE TABLE snowflake.fact (
  time_id INT NOT NULL,
  location_id INT NOT NULL,
  num_sales INT,
  PRIMARY KEY (time_id, location_id),
  FOREIGN KEY (time_id) REFERENCES snowflake.time_day,
  FOREIGN KEY (location_id) REFERENCES snowflake.location_street
  );


-- ********************************************************************************
-- * INSERT EXAMPLE DATA
-- ********************************************************************************
INSERT INTO star.location VALUES (DEFAULT, 'USA', 'IL', 60616, '10 W 31st Street');
INSERT INTO star.location VALUES (DEFAULT, 'USA', 'IL', 60616, '155 W 33st Street');
INSERT INTO star.location VALUES (DEFAULT, 'USA', 'IL', 60615, '1034 53rd Street');
-- and so on