cTDBjSGD

1-- For test --
2DROP TABLE IF EXISTS Positions, Owls, Race, Departs, DangerLevel, Manufacturer, Products, Category, Items, Employer, Sales;
3-- From here --
4	
5CREATE TABLE IF NOT EXISTS Positions (
6	po_id int PRIMARY KEY,
7	po_name Varchar(40) NOT NULL,
8	po_salary int NOT NULL
9);
10
11CREATE TABLE IF NOT EXISTS Owls (
12	o_num Varchar(30) PRIMARY KEY
13);
14
15CREATE TABLE IF NOT EXISTS Race (
16	r_race Varchar(20) PRIMARY KEY
17);
18
19CREATE TABLE IF NOT EXISTS Departs (
20	d_num int PRIMARY KEY,
21	d_name Varchar(30) UNIQUE
22);
23
24CREATE TABLE IF NOT EXISTS DangerLevel (
25	dl_name Varchar(20) PRIMARY KEY,
26	dl_level int NOT NULL
27);
28
29CREATE TABLE IF NOT EXISTS Manufacturer (
30	mf_id int PRIMARY KEY,
31	mf_name Varchar(30) NOT NULL,
32	mf_country Varchar(40) NOT NULL
33);
34
35CREATE TABLE IF NOT EXISTS Products (
36	p_id int PRIMARY KEY,
37	p_name Varchar(20) NOT NULL,
38	p_prod_date date NOT NULL,
39	p_manufacturer int REFERENCES Manufacturer (mf_id) NOT NULL,
40	p_danger Varchar(20) REFERENCES DangerLevel (dl_name) NOT NULL,
41	p_price int NOT NULL,
42	p_depart int REFERENCES Departs (d_num)
43);
44	
45CREATE TABLE IF NOT EXISTS Category (
46	c_id int PRIMARY KEY,
47	c_name Varchar(30) NOT NULL,
48	c_depart int REFERENCES Departs (d_num) NOT NULL
49);
50	
51CREATE TABLE IF NOT EXISTS Items (
52	i_quantity int NOT NULL,
53	i_id int UNIQUE REFERENCES Products (p_id) NOT NULL
54);
55
56CREATE TABLE IF NOT EXISTS Employer (
57	e_id int PRIMARY KEY,
58	e_fio varchar(50) NOT NULL,
59	e_position int REFERENCES Positions (po_id) NOT NULL,
60	e_depart int REFERENCES Departs(d_num) NOT NULL,
61	e_owl Varchar(30) UNIQUE REFERENCES Owls(o_num),
62	e_salary int NOT NULL,
63	e_race varchar(20) REFERENCES Race (r_race),
64	e_director int REFERENCES Employer(e_id)
65);
66
67CREATE TABLE IF NOT EXISTS Sales (
68	s_date date NOT NULL,
69	s_employee int REFERENCES Employer (e_id) NOT NULL,
70	s_sum int NOT NULL,
71	s_id int UNIQUE NOT NULL
72);
73
74