Skip to content

Latest commit

 

History

History
278 lines (215 loc) · 11.1 KB

Практика №2 - Доработка базы данных и оптимизация.md

File metadata and controls

278 lines (215 loc) · 11.1 KB
Raw

Практика №2 - Доработка базы данных и оптимизация

Работы в рамках дисциплины:
Клиент-серверные системы управления базами данных
Предыдущая работа:
Практика №1 - Создание базы данных
Следующая работа:
Практика №3 - Администрирование
Полный SQL код:
practic2.sql

*Все имена в данной работе вымышлены и не имеют ничего общего с реальными людьми

Задание

  1. Добавить в выбранную по вариантам базу данных дополнительно пять связанных отношений;
  2. Сделать 20 уникальных запросов к базе данных (уникальные означает неповторяющиеся сочетания ключевых слов)
  3. Внести не менее 10 замечаний по работе базы данных (нарушения НФ, оптимизация типизации, оптимизация производительности, ограничения, лишние связи и т. д.)

Обновление базы данных

--добавили новые таблицы и связи

 CREATE TABLE staff(
staff_id BIGSERIAL NOT NULL PRIMARY KEY,
prison_id INT NOT NULL,
name VARCHAR(60) NOT NULL,
post_id INT NOT NULL,
FOREIGN KEY (prison_id) REFERENCES prison(id)
);

CREATE TABLE posts(
post_id BIGSERIAL NOT NULL PRIMARY KEY,
title VARCHAR(100),
salary INT NOT NULL
);

CREATE TABLE staff_post(
id BIGSERIAL NOT NULL PRIMARY KEY,
staff_id INT NOT NULL,
post_id INT NOT NULL
);

ALTER TABLE staff_post
ADD FOREIGN KEY (staff_id) REFERENCES staff(staff_id);

ALTER TABLE staff_post
ADD FOREIGN KEY (post_id) REFERENCES posts(post_id);

ALTER TABLE prisoner
ADD COLUMN category INT NOT NULL;

CREATE TABLE health(
health_id BIGSERIAL NOT NULL PRIMARY KEY,
prisoner_id INT NOT NULL,
health BOOLEAN NOT NULL,
contagious BOOLEAN NOT NULL,
FOREIGN KEY (prison_id) REFERENCES prisoner(id)
);

CREATE TABLE category(
category_id BIGSERIAL NOT NULL PRIMARY KEY,
title VARCHAR(100)
);

ALTER TABLE prisoner
ADD FOREIGN KEY (category) REFERENCES category(category_id);

--Заполнили новыми данными
--добавили категории преступлений
INSERT INTO category VALUES (1, 'Low and medium seriousness of the offence');
INSERT INTO category VALUES (2, 'Seriousness of the offence');
INSERT INTO category VALUES (3, 'High seriousness of the offence');

--добавили здоровье
INSERT INTO health VALUES (1, 1, TRUE, FALSE);
INSERT INTO health VALUES (2, 2, FALSE, FALSE);
INSERT INTO health VALUES (3, 3, FALSE, TRUE);
INSERT INTO health VALUES (4, 4, TRUE, FALSE);
INSERT INTO health VALUES (5, 5, FALSE, FALSE);
INSERT INTO health VALUES (6, 6, FALSE, TRUE);
INSERT INTO health VALUES (7, 7, TRUE, FALSE);
INSERT INTO health VALUES (8, 8, FALSE, FALSE);

--добавили категории преступлений в prisoner
UPDATE prisoner SET category = 1 WHERE surname = 'Kokorin' OR surname = 'Mamaev';
UPDATE prisoner SET category = 2 WHERE id = 1 OR id = 2 OR id = 3 OR id = 4;
UPDATE prisoner SET category = 3 WHERE id = 7 OR id = 8;

--добавили должности
INSERT INTO posts VALUES (1, 'Chief', 150000);
INSERT INTO posts VALUES (2, 'Inspector', 100000);

--добавили сотрудников
INSERT INTO staff VALUES (1, 1, 'Ivan', 1);
INSERT INTO staff VALUES (2, 1, 'Nikita', 2);
INSERT INTO staff VALUES (3, 1, 'Maxim', 2);
INSERT INTO staff VALUES (4, 2, 'Sergey', 1);
INSERT INTO staff VALUES (5, 2, 'Mihail', 2);
INSERT INTO staff VALUES (6, 2, 'Andrey', 2);
INSERT INTO staff VALUES (7, 3, 'Artyr', 1);
INSERT INTO staff VALUES (8, 3, 'Egor', 2);
INSERT INTO staff VALUES (9, 3, 'Roman', 2);

Запросы

1. Вывести заключенных, сидящих в Матросской Тишине.

SELECT prisoner.name, prisoner.surname, category.title, prison.name  FROM prisoner
JOIN category ON prisoner.category = category.category_id
JOIN camera ON prisoner.camera = camera.id
JOIN prison ON camera.prison = prison.id
WHERE prison.name = 'Motrosskaya Tishina';

2. Вывести количество сотрудников в Бутырке.

SELECT COUNT(staff_id) FROM staff
JOIN prison ON staff.prison_id = prison.id
WHERE prison.name = 'Butirca';

https://lh6.googleusercontent.com/3pVh6RUS30ZyD_BO53dkeNv86KkGzyzOUuyuXUzlo6IgInR--mYSPrVNDSRUrtDBvlvtX2yOI1OZNguGoO1CQQQcgO9hdd2h1ESqM8JENGg0lXr6KF6mP80wu1IA_qCQcvmbJg6H

3. Найти всех Иванов.

SELECT prisoner.name, staff.name, prison.name FROM prison
JOIN staff ON prison.id = staff.prison_id
JOIN camera ON camera.prison = prison.id
JOIN prisoner ON camera.id = prisoner.id
WHERE prisoner.name = 'Ivan' AND staff.name = 'Ivan';

4. Где находится Михаил Ефремов?

SELECT prisoner.name, prisoner.surname, prison.name FROM prisoner
JOIN camera ON prisoner.camera = camera.id
JOIN prison ON camera.prison = prison.id
WHERE prisoner.name = 'Mihail' AND prisoner.surname = 'Efremov';

https://lh6.googleusercontent.com/LDV08cMvwisojW-wfb4_YEMfbKhy3Je9TqEauWoYi5Tbz5xvD7wDjpWG3FJli4a3Hnp2ylAijXlqaI1Ck5QxIGFBXqIg5Szc6lhIwYSb1BPPqzQmXxl23pvsHp_mXfx47PfJbY0u

5. Когда посадили Кокорина?

SELECT prisoner.name, prisoner.surname, prison_sentence.begin_date 
FROM prisoner
JOIN prison_sentence ON prisoner.id = prison_sentence.prisoner
WHERE prisoner.surname = 'Kokorin';

https://lh6.googleusercontent.com/uX4487CfIwjV38lz36CyjXqpNQgFXWRjbN4OCHisUxxBmuIloHmpNKe8ZsvyuxvClcXJ81gH8LJYxWNvvnZu5BuUaEVfjMa4HfV7oc--KzlXbkbnbFVrOc_YDypGBcVnc71IEz8U

6. Возраст Мамаева.

SELECT prisoner.name, prisoner.surname, age(current_date, born_date) 
FROM prisoner
WHERE prisoner.surname = 'Mamaev';

https://lh5.googleusercontent.com/WDK4dYCgdEIQHViUnYmOaJp4Rt8-FeZ2KWL6FEMoecC5Wr77Z8P706lox1Irn0PeaHK35dcFiROYtj25hG32aqzt38DXbJsQQfr2t-Zt_wf1OzvYknWFWtG17lfxWjwIlAYtOqdr

7. По какой статье сидит и когда освободится Иван Иванов?

SELECT prisoner.name, prisoner.surname, 
prison_sentence.article, prison_sentence.end_date FROM prisoner
JOIN prison_sentence ON prisoner.id = prison_sentence.prisoner
WHERE prisoner.name = 'Ivan' AND prisoner.surname = 'Ivanov';

https://lh5.googleusercontent.com/CZEmnrHzuwiCWqSQpBCHtD4Ls7_7B2wdlhp9RvIwPgvI4AV1dP7o87H2oKDTyajYQne-Ql5TbVLeCHxUmwswqnv4FXlW50dn_IdhyhmpkQf7NsjxD8CoDc9ohMogz2QJhatlns5K

8. Сколько заразных, кто они, где сидят?

SELECT prisoner.name, prisoner.surname, prison.name, camera.num FROM prisoner
JOIN camera ON prisoner.camera = camera.id
JOIN prison ON camera.prison = prison.id
JOIN health ON prisoner.id = health.prisoner_id
WHERE health.contagious = 't';

https://lh3.googleusercontent.com/BIOh3clXgOuHq10z19IMLD6PTAUqtsMS3_PLyqu0qz3j0eIH1AT8rZzKtr5nK8LzWRfVKda1uh0kvBv171gstbPbrlQ_MIH4pMj-3j2M2_vP8JVIwmngyNOvSDAyur4nt1qKR1Am

9. Вывести всех, кому больше 40.

SELECT prisoner.name, prisoner.surname FROM prisoner
WHERE age(current_date, born_date) > interval '40 year';

https://lh4.googleusercontent.com/0noh369yKidYfihiJw3OVoI7u5jNyejZSQjugvyC9TKtgl8XJUF1LfmBxyVocWmnVSqjUIkbr7OvPOfATvY7ivm1OIiH2ri2N4MsHhbvmVEyD5s82vjki32qQK556eRSXz4807mU

10. Вывести сотрудников и место их работы, которые получают больше 100000.

SELECT staff.name, prison.name, posts.salary FROM staff
JOIN prison ON staff.prison_id = prison.id
JOIN staff_post ON staff.staff_id = staff_post.staff_id
JOIN posts ON staff_post.post_id = posts.post_id
WHERE posts.salary > 100000;

https://lh6.googleusercontent.com/Ga435dBI5bbHGvKEVZzy_CEZSsmo6lJjWKKxE0msR-hLf7xq2gSWN_5Xc8gDRCQeq_A7XfFae6APo2Fw6x1azLJuDopUG2D4TKhQUDtVBSVNiI_2HAKgrWIEO26vpBZ7j-aPKPnW

11. Вывести однофамильцев.

SELECT name, surname FROM prisoner
WHERE surname IN
(SELECT surname FROM prisoner
GROUP BY surname
HAVING COUNT(*) > 1);

12. Список преступлений, которых нет в тюрьме.

SELECT article.name FROM article
LEFT JOIN prison_sentence ON prison_sentence.article=article.id
GROUP BY article.id
HAVING COUNT(prison_sentence.id)=0;

13. Заключенные, которые находятся на работах в данный момент.

SELECT prisoner.name AS name, prisoner.surname AS surname FROM prisoner
INNER JOIN work_prisoner ON prisoner.id=work_prisoner.prisoner_id
INNER JOIN work ON work_prisoner.work_id=work.id
WHERE work.work_date=CURRENT_DATE;

14. Количество заключенных в каждой тюрьме.

SELECT prison.name, COUNT(prisoner.id) AS count FROM prisoner
INNER JOIN camera ON prisoner.camera=camera.id
INNER JOIN prison ON camera.prison=prison.id
GROUP BY prison.id;

15. Название тюрьмы с наибольшим количеством заключенных.

SELECT MAX(count) FROM
(SELECT prison.name, COUNT(prisoner.id) AS count FROM prisoner
INNER JOIN camera ON prisoner.camera=camera.id
INNER JOIN prison ON camera.prison=prison.id
GROUP BY prison.id);

Оптимизация

https://lh4.googleusercontent.com/fwm5Has7Wjl_qnuCKbc-GgKqnjsGHJwI99Fi22fzzKmRNuyvoYlAa6-2hdnlc12P_a9gX6lRoqYsuARANkHE9r_oMiVcnshqKguLYOe4PnmEFqokq_IBikUO5fCBJB0_wyyOP91I

  1. Добавить отчество ☝️
  2. Добавить дату смерти
  3. У одной ходки может быть несколько статей, у нас только одна
  4. Увеличить name до 30
  5. Уменьшить surname до 30
  6. Подробно прописать адрес
  7. Добавить начало и конец работы
  8. Добавить num, part в artical
  9. Добавить индексы
  10. Добавить в work ограничения по возрасту и по полу
  11. Удалить post_id из staff