VGTS2Pd4

1DROP TABLE IF EXISTS employees;
2DROP TABLE IF EXISTS titles;
3DROP TABLE IF EXISTS departaments;
4DROP TABLE IF EXISTS dept_emp;
5DROP TABLE IF EXISTS dept_manager;
6DROP TABLE IF EXISTS salaries;
7
8
9CREATE TYPE egender AS ENUM (
10  'M', 
11  'F');
12
13CREATE TABLE employees(
14   id	SERIAL	NOT NULL,
15   emp_no	SERIAL	NOT NULL,
16   birth_date	DATE    NOT NULL,
17   first_name	TEXT    NOT NULL,
18   last_name	TEXT    NOT NULL,
19   gender	egender NOT NULL,
20   hire_date DATE	NOT NULL,
21   PRIMARY KEY(id)
22);
23
24CREATE TABLE titles(
25   id	SERIAL,
26   emp_no SERIAL references employees(emp_no),
27   title TEXT	NOT NULL,
28   from_date DATE NOT NULL,
29   to_date DATE NOT NULL,
30   PRIMARY KEY(id)
31);
32
33CREATE TABLE departaments(
34   id	SERIAL,
35   dept_no SERIAL	NOT NULL,
36   dept_name	TEXT    NOT NULL,
37   PRIMARY KEY(id)
38);
39
40CREATE TABLE dept_emp(
41   id	SERIAL,
42   emp_no SERIAL references employees(emp_no),
43   dept_no SERIAL references departaments(dept_no),
44   from_date DATE NOT NULL,
45   to_date DATE NOT NULL,
46   PRIMARY KEY(id)
47);
48
49CREATE TABLE dept_manager(
50   id	SERIAL,
51   dept_no SERIAL references departaments(dept_no),
52   emp_no SERIAL references employees(emp_no),
53   from_date DATE NOT NULL,
54   to_date DATE NOT NULL,
55   PRIMARY KEY(id)
56);
57
58CREATE TABLE salaries(
59   id	SERIAL,
60   emp_no SERIAL references employees(emp_no),
61   salary INT NOT NULL,
62   from_date DATE NOT NULL,
63   to_date DATE NOT NULL,
64   PRIMARY KEY(id)
65);
66
67