· 7 years ago · Dec 22, 2018, 05:02 PM
1DROP TABLE IF EXISTS medicine CASCADE;
2DROP TABLE IF EXISTS active_substance CASCADE;
3DROP TABLE IF EXISTS certificate CASCADE;
4DROP TABLE IF EXISTS laboratory CASCADE;
5DROP TABLE IF EXISTS distributor CASCADE;
6DROP TABLE IF EXISTS contact_person CASCADE;
7DROP TABLE IF EXISTS delivery CASCADE;
8DROP TABLE IF EXISTS delivery_contents CASCADE;
9DROP TABLE IF EXISTS depot CASCADE;
10DROP TABLE IF EXISTS pharmacy CASCADE;
11DROP TABLE IF EXISTS orders CASCADE;
12DROP TABLE IF EXISTS car_order CASCADE;
13DROP TABLE IF EXISTS car CASCADE;
14DROP TABLE IF EXISTS pharmacy_contents CASCADE;
15DROP TABLE IF EXISTS fabricator CASCADE;
16DROP TABLE IF EXISTS international_medicine_name CASCADE;
17
18CREATE TABLE IF NOT EXISTS active_substance (
19 id integer PRIMARY KEY,
20 name text NOT NULL UNIQUE,
21 formula text NOT NULL UNIQUE
22);
23
24CREATE TABLE IF NOT EXISTS laboratory (
25 id integer PRIMARY KEY,
26 name text NOT NULL UNIQUE,
27 supervisor text NOT NULL
28);
29
30CREATE TABLE IF NOT EXISTS certificate (
31 number integer PRIMARY KEY,
32 validity date,
33 lab integer REFERENCES laboratory
34);
35
36CREATE TABLE IF NOT EXISTS international_medicine_name (
37 id integer PRIMARY KEY,
38 international_name text NOT NULL UNIQUE
39);
40
41CREATE TABLE IF NOT EXISTS fabricator (
42 id integer PRIMARY KEY,
43 fabricator text NOT NULL UNIQUE
44);
45
46CREATE TABLE IF NOT EXISTS medicine (
47 id integer PRIMARY KEY,
48 tradename text NOT NULL UNIQUE,
49 internation_name integer REFERENCES international_medicine_name,
50 dosage_form text CHECK (dosage_form IN
51 ('tablet', 'pill', 'mixture', 'powder', 'capsule', 'cream', 'paste', 'liquid')), -- assume that's all, although there are a lot of dosage forms actually
52 fabricator integer REFERENCES fabricator,
53 active_substance integer REFERENCES active_substance,
54 certificate integer REFERENCES certificate
55);
56
57CREATE TABLE IF NOT EXISTS contact_person (
58 id integer PRIMARY KEY,
59 first_name text NOT NULL,
60 last_name text NOT NULL,
61 phone_number varchar(15) CHECK (phone_number SIMILAR TO '([0-9])*')
62);
63
64CREATE TABLE IF NOT EXISTS distributor (
65 id integer PRIMARY KEY,
66 address text NOT NULL UNIQUE,
67 bank_account text NOT NULL UNIQUE,
68 contact_person integer REFERENCES contact_person
69);
70
71CREATE TABLE IF NOT EXISTS depot (
72 id integer PRIMARY KEY,
73 address text NOT NULL
74);
75
76CREATE TABLE IF NOT EXISTS delivery (
77 id integer PRIMARY KEY,
78 depot integer REFERENCES depot,
79 distributor integer REFERENCES distributor,
80 storekeeper text NOT NULL,
81 arrival_time timestamp
82);
83
84CREATE TABLE IF NOT EXISTS delivery_contents (
85 delivery integer REFERENCES delivery,
86 medicine integer REFERENCES medicine,
87 number_of_packs integer CHECK (number_of_packs > 0),
88 weight_of_pack float CHECK (weight_of_pack > 0),
89 number_of_items_per_pack integer CHECK (number_of_items_per_pack > 0),
90 item_cost integer CHECK (item_cost > 0),
91 PRIMARY KEY (delivery, medicine)
92);
93
94CREATE TABLE IF NOT EXISTS pharmacy (
95 address text NOT NULL UNIQUE,
96 id_number integer PRIMARY KEY CHECK (id_number > 0)
97);
98
99CREATE TABLE IF NOT EXISTS pharmacy_contents (
100 pharmacy integer REFERENCES pharmacy,
101 medicine integer REFERENCES medicine,
102 price integer CHECK (price > 0),
103 number_of_items integer CHECK (number_of_items > 0),
104 PRIMARY KEY (pharmacy, medicine)
105);
106
107CREATE TABLE IF NOT EXISTS car (
108 id integer PRIMARY KEY,
109 overhaul_date date,
110 registration_id varchar(15) CHECK (registration_id SIMILAR TO '[A-Z0-9]*')
111);
112
113CREATE TABLE IF NOT EXISTS car_order (
114 id integer PRIMARY KEY,
115 car integer REFERENCES car,
116 date date,
117 depot integer REFERENCES depot
118);
119
120CREATE TABLE IF NOT EXISTS orders (
121 id serial PRIMARY KEY,
122 car_order integer REFERENCES car_order,
123 pharmacy integer REFERENCES pharmacy,
124 medicine integer REFERENCES medicine,
125 items integer CHECK (items > 0),
126 UNIQUE (car_order, pharmacy, medicine)
127);
128
129INSERT INTO active_substance VALUES(0, 'aqua pura', 'H2O');
130INSERT INTO active_substance VALUES (1, 'oxygen', 'O2');
131INSERT INTO active_substance VALUES (2, 'sugar', 'C17H21NO4');
132
133INSERT INTO laboratory VALUES (0, 'lab1', 'kuzmin');
134INSERT INTO laboratory VALUES (1, 'lab2', 'kirakosyan');
135
136INSERT INTO certificate VALUES(0, '2017-03-14', 0);
137INSERT INTO certificate VALUES(1, '2017-03-15', 0);
138INSERT INTO certificate VALUES(2, '2019-07-21', 0);
139
140INSERT INTO international_medicine_name VALUES(0, 'im1');
141INSERT INTO international_medicine_name VALUES (1, 'im2');
142INSERT INTO international_medicine_name VALUES (2, 'im3');
143
144INSERT INTO fabricator VALUES (0, 'f1');
145INSERT INTO fabricator VALUES (1, 'f2');
146
147INSERT INTO pharmacy VALUES ('some street, 15', 1);
148INSERT INTO pharmacy VALUES ('another street, 12', 2);
149
150INSERT INTO medicine VALUES (0, 'med', 0, 'pill', 0, 0, 0);
151INSERT INTO medicine VALUES (1, 'homeop', 1, 'liquid', 1, 2, 1);
152INSERT INTO medicine VALUES (2, 'other', 2, 'pill', 1, 1, 2);
153
154INSERT INTO pharmacy_contents VALUES (1, 1, 2000, 5);
155INSERT INTO pharmacy_contents VALUES (1, 2, 500, 1);
156INSERT INTO pharmacy_contents VALUES (2, 2, 700, 2);
157
158INSERT INTO contact_person VALUES (0, 'X', 'Y', '8');
159
160INSERT INTO distributor VALUES (0, 'some addr', '0123', 0);
161
162INSERT INTO depot VALUES (0, 'yet another addr');
163INSERT INTO depot VALUES (1, 'yet another addr 2');
164INSERT INTO depot VALUES (2, 'yet another addr 3');
165
166-- id, depot, distributor, storekeeper, arrival_time
167INSERT INTO delivery VALUES (0, 0, 0, 'keeper', '2017-04-11');
168INSERT INTO delivery VALUES (1, 1, 0, 'keeper2', '2018-04-11');
169INSERT INTO delivery VALUES (2, 2, 0, 'keeper3', '2015-04-11');
170
171-- delivery, medicine, number_of_packs, weight_of_pack, number_of_items_per_pack, item_cost
172INSERT INTO delivery_contents VALUES (0, 1, 2, 100, 20, 10); -- 2 * 20 * 10
173INSERT INTO delivery_contents VALUES (0, 2, 4, 20, 20, 1); -- 4 * 20 * 1
174INSERT INTO delivery_contents VALUES (1, 0, 1, 1, 1, 100); -- 1 * 1 * 100
175INSERT INTO delivery_contents VALUES (2, 1, 2, 1, 2, 400); -- 2 * 2 * 400
176
177-- id, date, number
178INSERT INTO car VALUES (0, '2017-04-12', 'A5667UI');
179INSERT INTO car VALUES (1, '2018-04-12', 'Z');
180
181-- id, car, date, depot
182INSERT INTO car_order VALUES (0, 0, '2018-05-12', 0);
183INSERT INTO car_order VALUES (1, 1, '2019-05-12', 1);
184INSERT INTO car_order VALUES (2, 0, '2019-09-12', 1);
185INSERT INTO car_order VALUES (3, 0, '2018-09-12', 2);
186
187-- id, car_order, pharmacy, medicine, items
188INSERT INTO orders VALUES (0, 0, 1, 0, 5);
189INSERT INTO orders VALUES (1, 0, 1, 1, 3);
190INSERT INTO orders VALUES (2, 0, 2, 0, 4);
191INSERT INTO orders VALUES (3, 1, 2, 1, 7);
192INSERT INTO orders VALUES (4, 2, 2, 0, 1);
193INSERT INTO orders VALUES (5, 3, 2, 0, 4);