-
Notifications
You must be signed in to change notification settings - Fork 1
/
Employees_db_schema.sql
105 lines (92 loc) · 3.29 KB
/
Employees_db_schema.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
-- Data Engineering
DROP TABLE IF EXISTS departments CASCADE;
DROP TABLE IF EXISTS employees CASCADE;
DROP TABLE IF EXISTS dept_emp CASCADE;
DROP TABLE IF EXISTS dept_manager CASCADE;
DROP TABLE IF EXISTS salaries CASCADE;
DROP TABLE IF EXISTS titles CASCADE;
CREATE TABLE "departments" (
-- Department number is a primary key, and it is
-- aslo found in department employees and department manager list
"dept_no" VARCHAR NOT NULL,
-- Department names
"dept_name" VARCHAR NOT NULL,
CONSTRAINT "pk_departments" PRIMARY KEY (
"dept_no"
)
);
CREATE TABLE "titles" (
-- Title id is a primary key,
-- and it also found in employees as emp_title_id
"title_id" VARCHAR NOT NULL,
-- List of titles
"title" VARCHAR NOT NULL,
CONSTRAINT "pk_titles" PRIMARY KEY (
"title_id"
)
);
CREATE TABLE "employees" (
-- Employees number is a primary key
-- and also found in department employees, department manager
-- and salaries list
"emp_no" INT NOT NULL,
-- Employees have a title id employees(emp_title_id)
-- So, this id has relationship with-
-- the composite foreign key titles(title_id)
"emp_title_id" VARCHAR NOT NULL,
-- Employees birth date
"birth_date" DATE NOT NULL,
-- Employees first name
"first_name" VARCHAR NOT NULL,
-- Employees last name
"last_name" VARCHAR NOT NULL,
-- Employees sex
"sex" VARCHAR NOT NULL,
-- Employees hired date
"hire_date" DATE NOT NULL,
CONSTRAINT "pk_employees" PRIMARY KEY (
"emp_no"
)
);
CREATE TABLE "dept_emp" (
-- Employees number in department employees list and
-- which shared a unique key with employees(emp_no)
"emp_no" INT NOT NULL,
-- Department number in department employees list and
-- which shared a unique key with dept_emp(dept_no)
"dept_no" VARCHAR NOT NULL
);
CREATE TABLE "dept_manager" (
-- Department number in department manger list and
-- which shared a unique key with dept_emp(dept_no)
"dept_no" VARCHAR NOT NULL,
-- Employees number in department manger list and
-- which shared a unique key with employees(emp_no)
"emp_no" INT NOT NULL
);
CREATE TABLE "salaries" (
-- Employees number in salaries and
-- which shared unique keys with employees(emp_no)
"emp_no" INT NOT NULL,
-- Employees salaries
"salary" INT NOT NULL
);
ALTER TABLE "employees" ADD CONSTRAINT "fk_employees_emp_title_id" FOREIGN KEY("emp_title_id")
REFERENCES "titles" ("title_id");
ALTER TABLE "dept_emp" ADD CONSTRAINT "fk_dept_emp_emp_no" FOREIGN KEY("emp_no")
REFERENCES "employees" ("emp_no");
ALTER TABLE "dept_emp" ADD CONSTRAINT "fk_dept_emp_dept_no" FOREIGN KEY("dept_no")
REFERENCES "departments" ("dept_no");
ALTER TABLE "dept_manager" ADD CONSTRAINT "fk_dept_manager_dept_no" FOREIGN KEY("dept_no")
REFERENCES "departments" ("dept_no");
ALTER TABLE "dept_manager" ADD CONSTRAINT "fk_dept_manager_emp_no" FOREIGN KEY("emp_no")
REFERENCES "employees" ("emp_no");
ALTER TABLE "salaries" ADD CONSTRAINT "fk_salaries_emp_no" FOREIGN KEY("emp_no")
REFERENCES "employees" ("emp_no");
-- Viewing the tables
SELECT * FROM departments;
SELECT * FROM titles;
SELECT * FROM employees;
SELECT * FROM dept_emp;
SELECT * FROM dept_manager;
SELECT * FROM salaries;