By8G4czs

1DROP SCHEMA IF EXISTS INSTITUTE CASCADE;
2CREATE SCHEMA INSTITUTE;
3
4CREATE TABLE INSTITUTE.SCIENTISTS (
5  SCIENTIST_ID     INT NOT NULL PRIMARY KEY,
6	SCIENTIST_LAB_ID INT NOT NULL,
7  SCIENTIST_FIO    VARCHAR(200),
8  SCIENTIST_POST   VARCHAR(200),
9  SCIENTIST_DEGREE VARCHAR(200)
10);
11
12CREATE TABLE INSTITUTE.LABORATORIES (
13  LAB_ID          INT NOT NULL PRIMARY KEY,
14  LAB_HEAD_ID     INT REFERENCES INSTITUTE.SCIENTISTS (SCIENTIST_ID),
15  LAB_WORKERS_CNT INT CHECK (LAB_WORKERS_CNT >= 0)
16);
17
18ALTER TABLE INSTITUTE.SCIENTISTS
19  ADD CONSTRAINT SCIENTIST_LAB_ID FOREIGN KEY (SCIENTIST_LAB_ID) REFERENCES INSTITUTE.LABORATORIES (LAB_ID);
20
21CREATE TABLE INSTITUTE.EQUIPMENT (
22  EQUIPMENT_ID    INT NOT NULL PRIMARY KEY,
23  EQUIPMENT_NM    VARCHAR(200),
24  EQUIPMENT_PRICE INT CHECK (EQUIPMENT_PRICE >= 0)
25);
26
27CREATE TABLE INSTITUTE.EQUIPMENT_X_LAB (
28  EQUIPMENT_ID      INT NOT NULL REFERENCES INSTITUTE.EQUIPMENT (EQUIPMENT_ID),
29  EQUIPMENT_LAB_ID  INT REFERENCES INSTITUTE.LABORATORIES (LAB_ID),
30  EQUIPMENT_LAB_CNT INT CHECK (EQUIPMENT_LAB_CNT >= 0)
31);
32
33CREATE TABLE INSTITUTE.RESEARCHES (
34  RESEARCH_ID        INT NOT NULL PRIMARY KEY,
35  RESEARCH_NM        VARCHAR(200),
36  RESEARCH_START_DT  DATE,
37  RESEARCH_FINISH_DT DATE,
38  RESEARCH_BUDGET    INT CHECK (RESEARCH_BUDGET >= 0)
39);
40
41CREATE TABLE INSTITUTE.SPONSORS (
42  SPONSOR_ID      INT NOT NULL PRIMARY KEY,
43  SPONSOR_NM      VARCHAR(200),
44  SPONSOR_COUNTRY VARCHAR(200)
45);
46
47CREATE TABLE INSTITUTE.RESEARCHES_X_LAB (
48  RESEARCH_ID     INT NOT NULL REFERENCES INSTITUTE.RESEARCHES (RESEARCH_ID),
49  RESEARCH_LAB_ID INT REFERENCES INSTITUTE.LABORATORIES (LAB_ID),
50	PRIMARY KEY (RESEARCH_ID, RESEARCH_LAB_ID)
51);
52
53CREATE TABLE INSTITUTE.RESEARCHES_X_SPONSOR (
54  RESEARCH_ID         INT NOT NULL REFERENCES INSTITUTE.RESEARCHES (RESEARCH_ID),
55  RESEARCH_SPONSOR_ID INT REFERENCES INSTITUTE.SPONSORS (SPONSOR_ID),
56	PRIMARY KEY (RESEARCH_ID, RESEARCH_SPONSOR_ID)
57);
58
59INSERT INTO INSTITUTE.LABORATORIES (LAB_ID, LAB_WORKERS_CNT) VALUES
60  (13, 4),
61  (17, 6),
62  (19, 5);
63INSERT INTO INSTITUTE.SCIENTISTS (SCIENTIST_ID, SCIENTIST_LAB_ID, SCIENTIST_FIO, SCIENTIST_POST, SCIENTIST_DEGREE)
64VALUES
65  (777, 13, 'Соломатин Григорий Алексеевич', 'Младший научный сотрудник', 'Кандидат наук'),
66  (333, 13, 'Васильев Павел Константинович', 'Руководитель', 'Доктор наук'),
67  (666, 13, 'Потапов Иван Олегович', 'Лаборант', NULL),
68  (505, 13, 'Михайлов Михаил Анатольевич', 'Научный сотрудник', 'Кандидат наук'),
69  (234, 17, 'Расторгуев Леонид Ильич', 'Младший научный сотрудник', 'Кандидат наук'),
70  (123, 17, 'Прокофьев Роман Андреевич', 'Научный сотрудник', 'Кандидат наук'),
71  (212, 17, 'Стукалов Степан Аркадбевич', 'Старший научный сотрудник', 'Доктор наук'),
72  (256, 17, 'Молчанов Григорий Иванович', 'Старший научный сотрудник', 'Доктор наук'),
73  (512, 17, 'Ситников Алексей Алексеевич', 'Руководитель', 'Доктор наук'),
74  (128, 17, 'Антонов Борис Иванович', 'Лаборант', NULL),
75  (931, 19, 'Ройтберг Кирилл Савельевич', 'Младший научный сотрудник', 'Кандидат наук'),
76  (890, 19, 'Зотов Иван Игоревич', 'Научный сотрудник', 'Доктор наук'),
77  (591, 19, 'Рыбников Павел Анатольевич', 'Руководитель', 'Доктор наук'),
78  (101, 19, 'Чубаров Евгений Степанович', 'Научный работник', 'Кандидат наук'),
79  (813, 19, 'Красов Федор Иванович', 'Лаборант', NULL);
80UPDATE INSTITUTE.LABORATORIES
81SET LAB_HEAD_ID = (SELECT SCIENTIST_ID
82                   FROM INSTITUTE.SCIENTISTS
83                   WHERE SCIENTIST_POST = 'Руководитель' AND SCIENTIST_LAB_ID = LAB_ID);
84INSERT INTO INSTITUTE.EQUIPMENT VALUES
85  (1024, 'Спектрограф', 1000),
86  (1100, 'Ленточный смеситель', 1000),
87  (1200, 'V-образный смеситель', 2000),
88  (1300, 'Кристаллизатор V115U', 1500),
89  (1400, 'Кристаллизатор V116U', 2000),
90  (1500, 'Кристаллизатор A110', 1300),
91  (1600, 'Фильтр B770-210', 900),
92  (1700, 'Автоматический реактор', 10000),
93  (1800, 'Фильтр K330-100', 1000),
94  (1900, 'Фильтр P680-230', 1500),
95  (1999, 'Центрифуга', 3000);
96INSERT INTO INSTITUTE.SPONSORS VALUES
97  (9999, 'РАН', 'Россия'),
98  (8888, 'Клуб анонимных химиков', 'Россия'),
99  (7777, 'Chemistry - science of 21th centuary', 'USA'),
100  (6666, 'Chemistry volunteers', 'Germany'),
101  (5555, 'Life is science', 'USA'),
102  (4444, 'Funny chemistry', 'USA');
103INSERT INTO INSTITUTE.EQUIPMENT_X_LAB VALUES
104  (1024, 13, 1),
105  (1024, 17, 1),
106  (1100, 13, 1),
107  (1100, 19, 1),
108  (1200, 17, 1),
109  (1300, 13, 1),
110  (1400, 17, 1),
111  (1500, 19, 1),
112  (1600, 13, 3),
113  (1800, 13, 1),
114  (1800, 19, 2),
115  (1800, 17, 2),
116  (1900, 19, 1),
117  (1999, 17, 1);
118INSERT INTO INSTITUTE.RESEARCHES VALUES
119  (1, 'Кинетика реакций с участием благородных газов', '1998-10-03', NULL, 10),
120  (2, 'Влияние галогенов на реакции окисления', '1995-11-16', '2024-03-06', 12),
121  (3, 'Скорость протекания реакций расщепления углеводоров', '2002-12-12', '2019-01-18', 10),
122  (4, 'Коррозия металлов в условиях естественной стреды', '2016-01-23', NULL, 5),
123  (5, 'Методы получения спирта из побочных продуктов реакций брожения', '1993-01-05', NULL, 10000),
124  (6, 'Исследование зависимости прочности материала от его состава', '2002-09-19', '2012-10-23', 15);
125INSERT INTO INSTITUTE.RESEARCHES_X_LAB VALUES
126  (1, 13),
127  (2, 13),
128  (3, 19),
129  (4, 17),
130  (5, 13),
131  (5, 17),
132  (5, 19),
133  (6, 19),
134  (6, 17);
135INSERT INTO INSTITUTE.RESEARCHES_X_SPONSOR VALUES
136  (1, 9999),
137  (2, 7777),
138  (3, 6666),
139  (4, 4444),
140  (5, 8888),
141  (6, 5555),
142  (6, 9999);