Employee-Database-A-Mystery-in-Two-Parts/employee_db_table_schemas.sql at master · ZGrinacoff/Employee-Database-A-Mystery-in-Two-Parts · GitHub

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
-- -- Exported from QuickDBD: https://www.quickdatabasediagrams.com/
-- -- Link to schema: https://app.quickdatabasediagrams.com/#/d/rj1NjW
-- -- NOTE! If you have used non-SQL datatypes in your design, you will have to change these here.


-- CREATE TABLE "departments" (
--     "dept_no" VARCHAR   NOT NULL,
--     "dept_name" VARCHAR   NOT NULL,
--     CONSTRAINT "pk_departments" PRIMARY KEY (
--         "dept_no"
--      )
-- );

-- CREATE TABLE "employees" (
--     "emp_no" INTEGER   NOT NULL,
--     "birth_date" DATE   NOT NULL,
--     "first_name" VARCHAR   NOT NULL,
--     "last_name" VARCHAR   NOT NULL,
--     "gender" VARCHAR   NOT NULL,
--     "hire_date" DATE   NOT NULL,
--     CONSTRAINT "pk_employees" PRIMARY KEY (
--         "emp_no"
--      )
-- );

-- CREATE TABLE "dept_emp" (
--     "emp_no" INTEGER   NOT NULL,
--     "dept_no" VARCHAR   NOT NULL,
--     "from_date" DATE   NOT NULL,
--     "to_date" DATE   NOT NULL
-- );

-- CREATE TABLE "dept_manager" (
--     "dept_no" VARCHAR   NOT NULL,
--     "emp_no" INTEGER   NOT NULL,
--     "from_date" DATE   NOT NULL,
--     "to_date" DATE   NOT NULL
-- );

-- CREATE TABLE "salaries" (
--     "emp_no" INTEGER   NOT NULL,
--     "salary" INTEGER   NOT NULL,
--     "from_date" DATE   NOT NULL,
--     "to_date" DATE   NOT NULL
-- );

-- CREATE TABLE "titles" (
--     "emp_no" INTEGER   NOT NULL,
--     "title" VARCHAR   NOT NULL,
--     "from_date" DATE   NOT NULL,
--     "to_date" DATE   NOT NULL
-- );

-- 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");

-- ALTER TABLE "titles" ADD CONSTRAINT "fk_titles_emp_no" FOREIGN KEY("emp_no")
-- REFERENCES "employees" ("emp_no");