gfMdNdZV

1DROP TABLE IF EXISTS employee CASCADE;
2CREATE TABLE employee 
3(
4    id VARCHAR(20) PRIMARY KEY NOT NULL,
5    name VARCHAR(20) NOT NULL,
6    branch VARCHAR(20) NOT NULL,
7    charge VARCHAR(20) NOT NULL,
8    salary FLOAT(20) NOT NULL,
9    phone_number VARCHAR(10) NOT NULL,
10    active BOOLEAN NOT NULL,
11
12    FOREIGN KEY (branch) REFERENCES branch (name)
13);
14
15DROP TABLE IF EXISTS login CASCADE;
16CREATE TABLE login
17(
18    username VARCHAR(20) PRIMARY KEY NOT NULL,
19    pass VARCHAR(20) NOT NULL
20);
21
22DROP TABLE IF EXISTS branch CASCADE;
23CREATE TABLE branch 
24(
25    name VARCHAR(20) PRIMARY KEY NOT NULL,
26    city VARCHAR(20) NOT NULL,
27    address VARCHAR(30) NOT NULL,    
28    active BOOLEAN NOT NULL  
29);
30
31DROP TABLE IF EXISTS transaction CASCADE;
32CREATE TABLE transaction 
33(
34    client_id VARCHAR(20) PRIMARY KEY NOT NULL,
35    client_phone VARCHAR(20) NOT NULL,
36    employee_id VARCHAR(20) NOT NULL,
37    date DATE NOT NULL,
38
39    FOREIGN KEY (employee_id) REFERENCES employee (id)
40);
41
42DROP TABLE IF EXISTS catalog CASCADE;
43CREATE TABLE catalog
44(
45    furniture_id VARCHAR(20) PRIMARY KEY NOT NULL,
46    price FLOAT(20) NOT NULL,
47    material VARCHAR(20) NOT NULL,
48    weight VARCHAR(20) NOT NULL,
49    color VARCHAR(20) NOT NULL
50);
51
52DROP TABLE IF EXISTS quote CASCADE;
53CREATE TABLE quote 
54(
55    id VARCHAR(20) PRIMARY KEY NOT NULL,
56    furniture_id VARCHAR(20) NOT NULL,
57
58    FOREIGN KEY (furniture_id) REFERENCES catalog (furniture_id)
59);
60
61DROP TABLE IF EXISTS sale CASCADE;
62CREATE TABLE sale 
63(
64    sale_id VARCHAR(20) PRIMARY KEY NOT NULL,
65    value FLOAT(30) NOT NULL,
66    branch VARCHAR(20) NOT NULL,
67    date DATE NOT NULL,
68
69    FOREIGN KEY (branch) REFERENCES branch (name)
70);
71
72DROP TABLE IF EXISTS furniture_sold CASCADE;
73CREATE TABLE furniture_sold
74(
75    sale_id VARCHAR(20) NOT NULL,
76    furniture_id VARCHAR(20) NOT NULL,
77    quantity VARCHAR(20) NOT NULL,
78
79    PRIMARY KEY (sale_id, furniture_id),
80    FOREIGN KEY (sale_id) REFERENCES sale (sale_id),
81    FOREIGN KEY (furniture_id) REFERENCES catalog (furniture_id)
82);
83
84DROP TABLE IF EXISTS inventory CASCADE;
85CREATE TABLE inventory
86(    
87    furniture_id VARCHAR(20) NOT NULL,
88    quantity VARCHAR(20) NOT NULL,
89
90    PRIMARY KEY (furniture_id, quantity),    
91    FOREIGN KEY (furniture_id) REFERENCES catalog (furniture_id)
92);
93
94DROP TABLE IF EXISTS commission CASCADE;
95CREATE TABLE commission
96(    
97    order_id VARCHAR(20) PRIMARY KEY NOT NULL,
98    status BOOLEAN NOT NULL,
99    furniture_id VARCHAR(20) NOT NULL,
100  
101    FOREIGN KEY (furniture_id) REFERENCES catalog (furniture_id)
102);
103
104INSERT INTO employee VALUES('admin', 'soph', 'Cra 84A #14-115', 'admin', 1234567, '12345678', true);
105INSERT INTO login VALUES('admin', '123');