RelationalDesign: schema_creation.sql

File schema_creation.sql, 8.2 KB (added by 183164, 13 days ago)
Line 
1-- =============================================================
2-- CityFix - schema_creation.sql
3-- Креирање на шемата project и сите табели (PostgreSQL)
4-- Релационен модел добиен со парцијална трансформација
5-- од ЕР моделот v01.
6-- Скриптата може да се извршува повеќепати: ја брише
7-- постоечката шема и ја креира одново.
8-- =============================================================
9
10DROP SCHEMA IF EXISTS project CASCADE;
11CREATE SCHEMA project;
12
13SET search_path TO project;
14
15-- -------------------------------------------------------------
16-- ADMINS
17-- -------------------------------------------------------------
18CREATE TABLE admins (
19 admin_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
20 full_name VARCHAR(100) NOT NULL,
21 email VARCHAR(150) NOT NULL,
22 password VARCHAR(255) NOT NULL,
23
24 CONSTRAINT pk_admins PRIMARY KEY (admin_id),
25 CONSTRAINT uq_admins_email UNIQUE (email),
26 CONSTRAINT ck_admins_email CHECK (email ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$')
27);
28
29-- -------------------------------------------------------------
30-- WORKERS
31-- -------------------------------------------------------------
32CREATE TABLE workers (
33 worker_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
34 full_name VARCHAR(100) NOT NULL,
35 email VARCHAR(150) NOT NULL,
36 password VARCHAR(255) NOT NULL,
37
38 CONSTRAINT pk_workers PRIMARY KEY (worker_id),
39 CONSTRAINT uq_workers_email UNIQUE (email),
40 CONSTRAINT ck_workers_email CHECK (email ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$')
41);
42
43-- -------------------------------------------------------------
44-- CITIZENS
45-- -------------------------------------------------------------
46CREATE TABLE citizens (
47 citizen_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
48 full_name VARCHAR(100) NOT NULL,
49 email VARCHAR(150) NOT NULL,
50 phone VARCHAR(20),
51 password VARCHAR(255) NOT NULL,
52
53 CONSTRAINT pk_citizens PRIMARY KEY (citizen_id),
54 CONSTRAINT uq_citizens_email UNIQUE (email),
55 CONSTRAINT ck_citizens_email CHECK (email ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$'),
56 CONSTRAINT ck_citizens_phone CHECK (phone ~ '^\+?[0-9]{6,19}$')
57);
58
59-- -------------------------------------------------------------
60-- CATEGORIES (Manages: N:1 кон admins, тотално учество)
61-- -------------------------------------------------------------
62CREATE TABLE categories (
63 category_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
64 name VARCHAR(80) NOT NULL,
65 admin_id INTEGER NOT NULL,
66
67 CONSTRAINT pk_categories PRIMARY KEY (category_id),
68 CONSTRAINT uq_categories_name UNIQUE (name),
69 CONSTRAINT fk_categories_admin FOREIGN KEY (admin_id)
70 REFERENCES admins (admin_id)
71 ON UPDATE CASCADE ON DELETE RESTRICT
72);
73
74-- -------------------------------------------------------------
75-- REPORTS (Submits: N:1 кон citizens, Classifies: N:1 кон categories)
76-- -------------------------------------------------------------
77CREATE TABLE reports (
78 report_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
79 description TEXT NOT NULL,
80 location_text VARCHAR(255),
81 latitude NUMERIC(9,6),
82 longitude NUMERIC(9,6),
83 status VARCHAR(20) NOT NULL DEFAULT 'submitted',
84 priority VARCHAR(10) NOT NULL DEFAULT 'low',
85 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
86 citizen_id INTEGER NOT NULL,
87 category_id INTEGER NOT NULL,
88
89 CONSTRAINT pk_reports PRIMARY KEY (report_id),
90 CONSTRAINT fk_reports_citizen FOREIGN KEY (citizen_id)
91 REFERENCES citizens (citizen_id)
92 ON UPDATE CASCADE ON DELETE RESTRICT,
93 CONSTRAINT fk_reports_category FOREIGN KEY (category_id)
94 REFERENCES categories (category_id)
95 ON UPDATE CASCADE ON DELETE RESTRICT,
96 CONSTRAINT ck_reports_status
97 CHECK (status IN ('submitted', 'received', 'in_progress', 'resolved', 'rejected')),
98 CONSTRAINT ck_reports_priority
99 CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
100 CONSTRAINT ck_reports_latitude CHECK (latitude BETWEEN -90 AND 90),
101 CONSTRAINT ck_reports_longitude CHECK (longitude BETWEEN -180 AND 180),
102 -- координатите се внесуваат заедно или воопшто не се внесуваат
103 CONSTRAINT ck_reports_coordinates
104 CHECK ((latitude IS NULL) = (longitude IS NULL)),
105 -- мора да постои барем адреса или координати
106 CONSTRAINT ck_reports_location
107 CHECK (location_text IS NOT NULL OR latitude IS NOT NULL)
108);
109
110-- -------------------------------------------------------------
111-- PHOTOS (Has: N:1 кон reports, тотално учество)
112-- -------------------------------------------------------------
113CREATE TABLE photos (
114 photo_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
115 image_url VARCHAR(500) NOT NULL,
116 report_id INTEGER NOT NULL,
117
118 CONSTRAINT pk_photos PRIMARY KEY (photo_id),
119 CONSTRAINT fk_photos_report FOREIGN KEY (report_id)
120 REFERENCES reports (report_id)
121 ON UPDATE CASCADE ON DELETE CASCADE
122);
123
124-- -------------------------------------------------------------
125-- STATUS_LOGS (Logs: N:1 кон reports, тотално учество;
126-- Updates: N:1 кон workers, парцијално учество)
127-- -------------------------------------------------------------
128CREATE TABLE status_logs (
129 log_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
130 status VARCHAR(20) NOT NULL,
131 changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
132 note TEXT,
133 report_id INTEGER NOT NULL,
134 worker_id INTEGER,
135
136 CONSTRAINT pk_status_logs PRIMARY KEY (log_id),
137 CONSTRAINT fk_status_logs_report FOREIGN KEY (report_id)
138 REFERENCES reports (report_id)
139 ON UPDATE CASCADE ON DELETE CASCADE,
140 CONSTRAINT fk_status_logs_worker FOREIGN KEY (worker_id)
141 REFERENCES workers (worker_id)
142 ON UPDATE CASCADE ON DELETE SET NULL,
143 CONSTRAINT ck_status_logs_status
144 CHECK (status IN ('submitted', 'received', 'in_progress', 'resolved', 'rejected'))
145);
146
147-- -------------------------------------------------------------
148-- COMMENTS (Comments on: N:1 кон reports; Writes: N:1 кон workers)
149-- -------------------------------------------------------------
150CREATE TABLE comments (
151 comment_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
152 content TEXT NOT NULL,
153 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
154 report_id INTEGER NOT NULL,
155 worker_id INTEGER NOT NULL,
156
157 CONSTRAINT pk_comments PRIMARY KEY (comment_id),
158 CONSTRAINT fk_comments_report FOREIGN KEY (report_id)
159 REFERENCES reports (report_id)
160 ON UPDATE CASCADE ON DELETE CASCADE,
161 CONSTRAINT fk_comments_worker FOREIGN KEY (worker_id)
162 REFERENCES workers (worker_id)
163 ON UPDATE CASCADE ON DELETE RESTRICT
164);
165
166-- -------------------------------------------------------------
167-- ASSIGNMENTS (Assigns: N:1 кон admins; Assigned via: N:1 кон reports;
168-- Receives: N:1 кон workers; сите со тотално учество)
169-- -------------------------------------------------------------
170CREATE TABLE assignments (
171 assignment_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
172 assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
173 note TEXT,
174 admin_id INTEGER NOT NULL,
175 report_id INTEGER NOT NULL,
176 worker_id INTEGER NOT NULL,
177
178 CONSTRAINT pk_assignments PRIMARY KEY (assignment_id),
179 CONSTRAINT fk_assignments_admin FOREIGN KEY (admin_id)
180 REFERENCES admins (admin_id)
181 ON UPDATE CASCADE ON DELETE RESTRICT,
182 CONSTRAINT fk_assignments_report FOREIGN KEY (report_id)
183 REFERENCES reports (report_id)
184 ON UPDATE CASCADE ON DELETE CASCADE,
185 CONSTRAINT fk_assignments_worker FOREIGN KEY (worker_id)
186 REFERENCES workers (worker_id)
187 ON UPDATE CASCADE ON DELETE RESTRICT,
188 -- истиот работник не може двапати да биде доделен на иста пријава
189 CONSTRAINT uq_assignments_report_worker UNIQUE (report_id, worker_id)
190);