normalization: normalized_ddl_schema.sql

File normalized_ddl_schema.sql, 4.8 KB (added by 155036, 3 months ago)
Line 
1DROP TABLE IF EXISTS countries CASCADE;
2DROP TABLE IF EXISTS actor_types CASCADE;
3DROP TABLE IF EXISTS roles CASCADE;
4DROP TABLE IF EXISTS statuses CASCADE;
5DROP TABLE IF EXISTS actors CASCADE;
6DROP TABLE IF EXISTS locations CASCADE;
7DROP TABLE IF EXISTS events CASCADE;
8DROP TABLE IF EXISTS event_details CASCADE;
9DROP TABLE IF EXISTS users CASCADE;
10DROP TABLE IF EXISTS plan CASCADE;
11DROP TABLE IF EXISTS subscription CASCADE;
12DROP TABLE IF EXISTS notifications CASCADE;
13DROP TABLE IF EXISTS predictions CASCADE;
14DROP TABLE IF EXISTS conflict_risk CASCADE;
15DROP TABLE IF EXISTS event_analytics CASCADE;
16
17
18CREATE TABLE countries (
19 country_code VARCHAR(5) PRIMARY KEY,
20 country_name VARCHAR(100)
21);
22
23CREATE TABLE actor_types (
24 type_code VARCHAR(10) PRIMARY KEY,
25 type_description VARCHAR(100)
26);
27
28CREATE TABLE roles (
29 role_id SERIAL PRIMARY KEY,
30 role_name VARCHAR(50) NOT NULL
31);
32
33CREATE TABLE statuses (
34 status_code VARCHAR(20) PRIMARY KEY,
35 status_description TEXT
36);
37
38CREATE TABLE actors (
39 actor_id SERIAL PRIMARY KEY,
40 actor_code VARCHAR(10) NOT NULL,
41 actor_name VARCHAR(100) NOT NULL,
42 country_code VARCHAR(5),
43 type_code VARCHAR(10),
44 FOREIGN KEY (country_code) REFERENCES countries(country_code),
45 FOREIGN KEY (type_code) REFERENCES actor_types(type_code)
46);
47
48CREATE TABLE locations (
49 location_id SERIAL PRIMARY KEY,
50 full_name VARCHAR(100) NOT NULL,
51 country_code VARCHAR(5),
52 adm1_code VARCHAR(10),
53 latitude DECIMAL(8, 6),
54 longitude DECIMAL(9, 6),
55 feature_id VARCHAR(20),
56 FOREIGN KEY (country_code) REFERENCES countries(country_code)
57);
58
59CREATE TABLE events (
60 global_event_id SERIAL PRIMARY KEY,
61 sql_date DATE NOT NULL,
62 month_year VARCHAR(6) NOT NULL,
63 year INT NOT NULL,
64 fraction_date DECIMAL(8, 4) NOT NULL,
65 is_root_event BOOLEAN NOT NULL,
66 event_code VARCHAR(10) NOT NULL,
67 goldstein_scale DECIMAL(5, 2),
68 num_mentions INT,
69 num_sources INT,
70 num_articles INT,
71 avg_tone DECIMAL(5, 2)
72);
73
74CREATE TABLE event_details (
75 global_event_id INT,
76 actor_id INT,
77 location_id INT,
78 PRIMARY KEY (global_event_id, actor_id, location_id),
79 FOREIGN KEY (global_event_id) REFERENCES events(global_event_id) ON DELETE CASCADE,
80 FOREIGN KEY (actor_id) REFERENCES actors(actor_id) ON DELETE CASCADE,
81 FOREIGN KEY (location_id) REFERENCES locations(location_id) ON DELETE CASCADE
82);
83
84CREATE TABLE users (
85 user_id SERIAL PRIMARY KEY,
86 first_name VARCHAR(50) NOT NULL,
87 last_name VARCHAR(50) NOT NULL,
88 email VARCHAR(100) UNIQUE NOT NULL,
89 password_hash VARCHAR(255) NOT NULL,
90 role_id INT NOT NULL,
91 last_login_date DATE,
92 register_date DATE NOT NULL,
93 FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE
94);
95
96CREATE TABLE plan (
97 plan_id SERIAL PRIMARY KEY,
98 plan_name VARCHAR(50) NOT NULL,
99 max_events INT,
100 max_notifications INT,
101 max_predictions INT DEFAULT 10
102);
103
104CREATE TABLE subscription (
105 subscription_id SERIAL PRIMARY KEY,
106 user_id INT NOT NULL,
107 plan_id INT NOT NULL,
108 start_date DATE NOT NULL,
109 end_date DATE,
110 status_code VARCHAR(20) NOT NULL,
111 FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
112 FOREIGN KEY (plan_id) REFERENCES plan(plan_id) ON DELETE CASCADE,
113 FOREIGN KEY (status_code) REFERENCES statuses(status_code)
114);
115
116CREATE TABLE notifications (
117 notification_id SERIAL PRIMARY KEY,
118 user_id INT NOT NULL,
119 event_id INT NOT NULL,
120 notification_date DATE NOT NULL,
121 status_code VARCHAR(20) NOT NULL,
122 FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
123 FOREIGN KEY (event_id) REFERENCES events(global_event_id) ON DELETE CASCADE,
124 FOREIGN KEY (status_code) REFERENCES statuses(status_code)
125);
126
127CREATE TABLE predictions (
128 prediction_id SERIAL PRIMARY KEY,
129 event_id INT NOT NULL,
130 actor_id INT NOT NULL,
131 predicted_date DATE NOT NULL,
132 prediction_type VARCHAR(50) NOT NULL,
133 confidence_score DECIMAL(5,2) NOT NULL,
134 FOREIGN KEY (event_id) REFERENCES events(global_event_id) ON DELETE CASCADE,
135 FOREIGN KEY (actor_id) REFERENCES actors(actor_id) ON DELETE CASCADE
136);
137
138CREATE TABLE conflict_risk (
139 conflict_id SERIAL PRIMARY KEY,
140 actor1_id INT NOT NULL,
141 actor2_id INT NOT NULL,
142 risk_score DECIMAL(5, 2),
143 predicted_date DATE NOT NULL,
144 description TEXT,
145 FOREIGN KEY (actor1_id) REFERENCES actors(actor_id) ON DELETE CASCADE,
146 FOREIGN KEY (actor2_id) REFERENCES actors(actor_id) ON DELETE CASCADE
147);
148
149CREATE TABLE event_analytics (
150 analytics_id SERIAL PRIMARY KEY,
151 actor_id INT NOT NULL,
152 event_id INT NOT NULL,
153 date DATE NOT NULL,
154 conflict_coefficient DECIMAL(5, 2),
155 mentions_count INT,
156 avg_tone DECIMAL(5, 2),
157 description TEXT,
158 FOREIGN KEY (actor_id) REFERENCES actors(actor_id) ON DELETE CASCADE,
159 FOREIGN KEY (event_id) REFERENCES events(global_event_id) ON DELETE CASCADE
160);