w74WTtdq

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