1 files changed, 34 insertions, 0 deletions
diff --git a/assignments/1/run.sql b/assignments/1/run.sql
new file mode 100644
index 0000000..d1ce2ea
--- /dev/null
+++ b/assignments/1/run.sql
@@ -0,0 +1,34 @@
+DROP TABLE IF EXISTS employees CASCADE;
+DROP TABLE IF EXISTS projects CASCADE;
+DROP TABLE IF EXISTS departments CASCADE;
+
+CREATE TABLE departments (
+  id bigint primary key,
+  budget decimal
+);
+
+CREATE TABLE projects (
+  id bigint primary key,
+  department_id bigint references departments(id),
+  budget decimal
+);
+
+CREATE TABLE employees (
+  id bigint primary key,
+  department_id bigint references departments(id),
+  project_id bigint references projects(id),
+  hired_at date,
+  title varchar(255),
+  phone_number varchar(255) NOT NULL,
+  office_number varchar(255) NOT NULL,
+  area varchar(255),
+  salary decimal,
+  manager bit
+);
+
+ALTER TABLE employees ADD CONSTRAINT departments_fk FOREIGN KEY (department_id) REFERENCES departments(id);
+ALTER TABLE employees ADD CONSTRAINT projects_fk FOREIGN KEY (project_id) REFERENCES projects(id);
+ALTER TABLE projects ADD CONSTRAINT departments_fk FOREIGN KEY (department_id) REFERENCES departments(id);
+CREATE UNIQUE INDEX managers_idx on employees(department_id, manager) WHERE manager IS NULL;
+CREATE UNIQUE INDEX employee_office_idx on employees(office_number);
+CREATE UNIQUE INDEX employee_phone_idx on employees(phone_number);