-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdatabase_creation.sql
154 lines (112 loc) · 7.24 KB
/
database_creation.sql
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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
---------------------------------------------------------------------------------------------------
-- DROP DATABASE ----------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
DROP DATABASE IF EXISTS dbdw_pec2;
---------------------------------------------------------------------------------------------------
-- CREATE DATABASE --------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
CREATE DATABASE dbdw_pec2;
---------------------------------------------------------------------------------------------------
-- SET PATH TO DATABASE ---------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
SET search_path TO dbdw_pec2;
---------------------------------------------------------------------------------------------------
-- DROP SCHEMA ------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
DROP SCHEMA IF EXISTS erp;
---------------------------------------------------------------------------------------------------
-- CREATE SCHEMA ----------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
CREATE SCHEMA erp;
---------------------------------------------------------------------------------------------------
-- DROP TABLES ------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS erp.tb_employees;
DROP TABLE IF EXISTS erp.tb_projects;
DROP TABLE IF EXISTS erp.tb_project_budget;
DROP TABLE IF EXISTS erp.tb_tasks;
DROP TABLE IF EXISTS erp.tb_task_project;
DROP TABLE IF EXISTS erp.tb_task_assigment;
---------------------------------------------------------------------------------------------------
-- CREATE TABLES ----------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
BEGIN WORK;
---------------------------------------------------------------------------------------------------
-- Create Table Employees -------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
CREATE TABLE erp.tb_employees ( -- te
employees_id INT,
name CHARACTER VARYING(20) NOT NULL,
role CHARACTER VARYING(30) NOT NULL,
phone CHARACTER(13) NOT NULL,
CONSTRAINT pk_employees PRIMARY KEY (employees_id)
);
---------------------------------------------------------------------------------------------------
-- Create Table Projects --------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
CREATE TABLE erp.tb_projects ( -- tp
project_id INT,
project_name CHARACTER VARYING(30) NOT NULL,
description CHARACTER VARYING(40),
start_date DATE NOT NULL,
end_date DATE NOT NULL,
priority CHARACTER(6) NOT NULL,
sales_manager INT,
budget NUMERIC(8,2) NOT NULL,
CONSTRAINT pk_projects PRIMARY KEY (project_id),
CONSTRAINT fk_projects_employees FOREIGN KEY (sales_manager) REFERENCES erp.tb_employees(employees_id),
CONSTRAINT u_project_name UNIQUE(project_name),
CONSTRAINT ck_priority CHECK(priority in ('high','medium','low'))
);
---------------------------------------------------------------------------------------------------
-- Create Table Project Budget --------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
CREATE TABLE erp.tb_project_budget ( -- tpb
project_budget_id INT,
project_id INT NOT NULL,
concept CHARACTER VARYING(50) NOT NULL,
units INT NOT NULL,
unit_price NUMERIC(8,2) NOT NULL,
CONSTRAINT pk_project_budget PRIMARY KEY (project_budget_id),
CONSTRAINT fk_project_budget_projects FOREIGN KEY (project_id) REFERENCES erp.tb_projects(project_id)
);
---------------------------------------------------------------------------------------------------
-- Create Table Tasks -----------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
CREATE TABLE erp.tb_tasks ( -- tt
task_id CHARACTER(2),
task_name CHARACTER(50) NOT NULL,
CONSTRAINT pk_tasks PRIMARY KEY (task_id)
);
---------------------------------------------------------------------------------------------------
-- Create Table Task Project ----------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
CREATE TABLE erp.tb_task_project ( -- ttp
task_project_id INT,
project_id INT NOT NULL,
task_id CHARACTER(2),
start_date DATE NOT NULL,
end_date DATE NOT NULL,
parent_id INT DEFAULT NULL,
CONSTRAINT pk_task_project PRIMARY KEY (task_project_id),
CONSTRAINT fk_projects FOREIGN KEY (project_id) REFERENCES erp.tb_projects(project_id),
CONSTRAINT fk_tasks FOREIGN KEY (task_id) REFERENCES erp.tb_tasks(task_id),
CONSTRAINT fk_task_project FOREIGN KEY (parent_id) REFERENCES erp.tb_task_project(task_project_id),
CONSTRAINT ck_task_project CHECK(parent_id IS NULL OR parent_id <= task_project_id)
);
---------------------------------------------------------------------------------------------------
-- Create Table Task Assignment -------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
CREATE TABLE erp.tb_task_assigment ( -- tta
task_assigment_id INT,
employees_id INT NOT NULL,
task_project_id INT NOT NULL,
task_done BOOLEAN NOT NULL DEFAULT FALSE,
CONSTRAINT pk_task_assigment PRIMARY KEY (task_assigment_id),
CONSTRAINT fk_employees FOREIGN KEY (employees_id) REFERENCES erp.tb_employees(employees_id),
CONSTRAINT fk_task_project FOREIGN KEY (task_project_id) REFERENCES erp.tb_task_project(task_project_id)
);
---------------------------------------------------------------------------------------------------
-- COMMIT TABLES ----------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
COMMIT WORK;