DEV Community

faxridd1n
faxridd1n

Posted on • Edited on

Faxriddin

Построите ER-диаграмму базы данных

  1. Сведения о профессии(Код профессии, Название профессии, Классификация профессии, Оклад),
  2. Паспортные данные сотрудников (№ Записи, Табельный номер, Серия паспорта, Номер, Кем выдан, Когда выдан),
  3. Социальные льготы (№ Записи, Табельный номер, Наименование льгот, № Документа, Дата выдачи документа),
  4. Должность (Код должности, Должность, Оклад),
  5. Штатное расписание (№ по порядку, Код должности, Код отдела),
  6. Отдел(Код Отдела, Название Отдела, Номер Кабинета, Номер Телефона Отдела, Начальник отдела),
  7. Сведения о медицинском обследовании(№ Записи, Табельный номер, Дата обследования, Место обследования, Заключение врачей),
  8. Сотрудники(Табельный Номер, Фамилия, Имя, Отчество, Пол, Семейное положение, Домашний Адрес, Номер Телефона, Образование, Стаж работы, Дата Приёма, Код Отдела, Должность, Код профессии),
  9. Сведения о повышении квалификации(№ Записи, Табельный номер, Сведения о повышении квалификации, Дата повышения, Вид повышения, Наименование учреждения),
  10. Увольнения(№ Записи, Табельный номер, Дата увольнения, Статья, Причина увольнения),
  11. Отпуска(№ Записи, Вид отпуска, Табельный номер, Дата отпуска, Количество дней отпуска, Дни дополнительного отпуска, Дата окончания отпуска, Основание),
  12. Сведения о командировках(№ Записи, Табельный номер,Куда командирован (Страна, город, организация),№ Приказа, Дата приказа, С дата, По дату, Количество дней, Цель командировки),
  13. Сведения о поощрениях(№ Записи, Табельный номер, Мотив поощрения, Вид поощрения, Дата, № документа, Основание, Сумма). 
CREATE TABLE Сотрудники (
    ИдентификаторСотрудника INTEGER PRIMARY KEY,
    Фамилия VARCHAR(50),
    Имя VARCHAR(50),
    Отчество VARCHAR(50),
    Пол CHAR(1),
    СемейноеПоложение VARCHAR(50),
    Адрес VARCHAR(255),
    Телефон VARCHAR(15),
    Образование VARCHAR(50),
    ОпытРаботы INT,
    ДатаПриема DATE,
    ИдентификаторОтдела INT,
    ИдентификаторДолжности INT,
    КодПрофессии INT,
    FOREIGN KEY (ИдентификаторОтдела) REFERENCES Отделы(ИдентификаторОтдела),
    FOREIGN KEY (ИдентификаторДолжности) REFERENCES Должности(ИдентификаторДолжности),
    FOREIGN KEY (КодПрофессии) REFERENCES Профессии(КодПрофессии)
);

CREATE TABLE ПаспортныеДанные (
    ИдентификаторЗаписи INTEGER PRIMARY KEY,
    ИдентификаторСотрудника INT,
    СерияПаспорта VARCHAR(10),
    НомерПаспорта VARCHAR(10),
    КемВыдан VARCHAR(100),
    ДатаВыдачи DATE,
    FOREIGN KEY (ИдентификаторСотрудника) REFERENCES Сотрудники(ИдентификаторСотрудника)
);

CREATE TABLE Профессии (
    КодПрофессии INTEGER PRIMARY KEY, 
    НазваниеПрофессии VARCHAR(100),
    Классификация VARCHAR(50),
    Оклад DECIMAL(10, 2)
);

CREATE TABLE Должности (
    ИдентификаторДолжности INTEGER PRIMARY KEY,
    НазваниеДолжности VARCHAR(100),
    Оклад DECIMAL(10, 2)
);

CREATE TABLE ШтатноеРасписание (
    ИдентификаторРасписания INTEGER PRIMARY KEY,
    ИдентификаторДолжности INT,
    ИдентификаторОтдела INT,
    FOREIGN KEY (ИдентификаторДолжности) REFERENCES Должности(ИдентификаторДолжности),
    FOREIGN KEY (ИдентификаторОтдела) REFERENCES Отделы(ИдентификаторОтдела)
);

CREATE TABLE Отделы (
    ИдентификаторОтдела INTEGER PRIMARY KEY,
    НазваниеОтдела VARCHAR(100),
    НомерКомнаты VARCHAR(10),
    Телефон VARCHAR(15),
    Руководитель VARCHAR(100)
);

CREATE TABLE МедицинскиеОсмотры (
    ИдентификаторЗаписи INTEGER PRIMARY KEY,
    ИдентификаторСотрудника INT,
    ДатаОсмотра DATE,
    МестоОсмотра VARCHAR(100),
    МедицинскоеЗаключение TEXT,
    FOREIGN KEY (ИдентификаторСотрудника) REFERENCES Сотрудники(ИдентификаторСотрудника)
);

CREATE TABLE Квалификация (
    ИдентификаторЗаписи INTEGER PRIMARY KEY,
    ИдентификаторСотрудника INT,
    ИнформацияОКвалификации TEXT,
    ДатаКвалификации DATE,
    ТипКвалификации VARCHAR(50),
    НазваниеУчреждения VARCHAR(100),
    FOREIGN KEY (ИдентификаторСотрудника) REFERENCES Сотрудники(ИдентификаторСотрудника)
);

CREATE TABLE Увольнения (
    ИдентификаторЗаписи INTEGER PRIMARY KEY,
    ИдентификаторСотрудника INT,
    ДатаУвольнения DATE,
    Статья TEXT,
    ПричинаУвольнения TEXT,
    FOREIGN KEY (ИдентификаторСотрудника) REFERENCES Сотрудники(ИдентификаторСотрудника)
);

CREATE TABLE Отпуска (
    ИдентификаторЗаписи INTEGER PRIMARY KEY,
    ТипОтпуска VARCHAR(50),
    ИдентификаторСотрудника INT,
    ДатаНачалаОтпуска DATE,
    КоличествоДней INT,
    ДополнительныеДни INT,
    ДатаОкончания DATE,
    ПричинаОтпуска TEXT,
    FOREIGN KEY (ИдентификаторСотрудника) REFERENCES Сотрудники(ИдентификаторСотрудника)
);

CREATE TABLE Командировки (
    ИдентификаторЗаписи INTEGER PRIMARY KEY,
    ИдентификаторСотрудника INT,
    МестоНазначения TEXT,
    НомерПриказа VARCHAR(20),
    ДатаПриказа DATE,
    ДатаНачала DATE,
    ДатаОкончания DATE,
    КоличествоДней INT,
    Цель TEXT,
    FOREIGN KEY (ИдентификаторСотрудника) REFERENCES Сотрудники(ИдентификаторСотрудника)
);

CREATE TABLE Поощрения (
    ИдентификаторЗаписи INTEGER PRIMARY KEY,
    ИдентификаторСотрудника INT,
    Мотивация TEXT,
    ТипПоощрения VARCHAR(50),
    ДатаПоощрения DATE,
    НомерДокумента VARCHAR(20),
    Основание TEXT,
    Сумма DECIMAL(10, 2),
    FOREIGN KEY (ИдентификаторСотрудника) REFERENCES Сотрудники(ИдентификаторСотрудника)
);

CREATE TABLE СоциальныеЛьготы (
    ИдентификаторЗаписи INTEGER PRIMARY KEY,
    ИдентификаторСотрудника INT,
    НазваниеЛьготы VARCHAR(100),
    НомерДокумента VARCHAR(20),
    ДатаВыдачи DATE,
    FOREIGN KEY (ИдентификаторСотрудника) REFERENCES Сотрудники(ИдентификаторСотрудника)
);
Enter fullscreen mode Exit fullscreen mode

Image description

Image description

-- Таблица Отделы
INSERT INTO Отделы (ИдентификаторОтдела, НазваниеОтдела, НомерКомнаты, Телефон, Руководитель)
VALUES 
(1, 'Бухгалтерия', '101', '1234567890', 'Иванов Иван Иванович'),
(2, 'Кадровый отдел', '102', '1234567891', 'Петрова Мария Сергеевна'),
(3, 'IT отдел', '201', '1234567892', 'Сидоров Андрей Николаевич'),
(4, 'Маркетинг', '202', '1234567893', 'Кузнецова Ольга Владимировна'),
(5, 'Продажи', '301', '1234567894', 'Васильев Сергей Викторович');

-- Таблица Должности
INSERT INTO Должности (ИдентификаторДолжности, НазваниеДолжности, Оклад)
VALUES 
(1, 'Бухгалтер', 50000.00),
(2, 'HR менеджер', 45000.00),
(3, 'Программист', 70000.00),
(4, 'Маркетолог', 55000.00),
(5, 'Менеджер по продажам', 60000.00);

-- Таблица Профессии
INSERT INTO Профессии (КодПрофессии, НазваниеПрофессии, Классификация, Оклад)
VALUES 
(1, 'Бухгалтерия', 'Финансы', 50000.00),
(2, 'Управление персоналом', 'HR', 45000.00),
(3, 'Программирование', 'Технологии', 70000.00),
(4, 'Маркетинг', 'Реклама', 55000.00),
(5, 'Продажи', 'Коммерция', 60000.00);

-- Таблица Сотрудники
INSERT INTO Сотрудники (ИдентификаторСотрудника, Фамилия, Имя, Отчество, Пол, СемейноеПоложение, Адрес, Телефон, Образование, ОпытРаботы, ДатаПриема, ИдентификаторОтдела, ИдентификаторДолжности, КодПрофессии)
VALUES 
(1, 'Иванов', 'Алексей', 'Сергеевич', 'М', 'Женат', 'ул. Пушкина, 10', '89991112233', 'Высшее', 5, '2020-01-15', 1, 1, 1),
(2, 'Сидорова', 'Екатерина', 'Андреевна', 'Ж', 'Не замужем', 'ул. Лермонтова, 22', '89992223344', 'Среднее', 3, '2021-06-10', 2, 2, 2),
(3, 'Петров', 'Николай', 'Иванович', 'М', 'Женат', 'ул. Чехова, 15', '89993334455', 'Высшее', 8, '2019-03-12', 3, 3, 3),
(4, 'Кузьмина', 'Мария', 'Сергеевна', 'Ж', 'Замужем', 'ул. Толстого, 18', '89994445566', 'Среднее специальное', 4, '2022-11-20', 4, 4, 4),
(5, 'Васильев', 'Игорь', 'Алексеевич', 'М', 'Женат', 'ул. Горького, 5', '89995556677', 'Высшее', 10, '2018-05-01', 5, 5, 5);

-- Таблица Паспортные данные сотрудников
INSERT INTO ПаспортныеДанные (ИдентификаторЗаписи, ИдентификаторСотрудника, СерияПаспорта, НомерПаспорта, КемВыдан, ДатаВыдачи)
VALUES 
(1, 1, '1234', '567890', 'УФМС России', '2015-07-01'),
(2, 2, '4321', '098765', 'УФМС России', '2018-03-15'),
(3, 3, '5678', '123456', 'УФМС России', '2010-10-20'),
(4, 4, '8765', '654321', 'УФМС России', '2012-09-12'),
(5, 5, '3456', '789012', 'УФМС России', '2014-11-25');

-- Таблица Штатное расписание
INSERT INTO ШтатноеРасписание (ИдентификаторРасписания, ИдентификаторДолжности, ИдентификаторОтдела)
VALUES 
(1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 4, 4),
(5, 5, 5);

-- Таблица Медицинские осмотры
INSERT INTO МедицинскиеОсмотры (ИдентификаторЗаписи, ИдентификаторСотрудника, ДатаОсмотра, МестоОсмотра, МедицинскоеЗаключение)
VALUES 
(1, 1, '2022-10-15', 'Клиника №1', 'Годен'),
(2, 2, '2021-05-12', 'Клиника №2', 'Годен'),
(3, 3, '2023-03-10', 'Клиника №3', 'Годен'),
(4, 4, '2020-09-20', 'Клиника №4', 'Годен'),
(5, 5, '2022-07-05', 'Клиника №5', 'Годен');

-- Таблица Повышение квалификации
INSERT INTO Квалификация (ИдентификаторЗаписи, ИдентификаторСотрудника, ИнформацияОКвалификации, ДатаКвалификации, ТипКвалификации, НазваниеУчреждения)
VALUES 
(1, 1, 'Сертификат бухгалтера', '2023-01-10', 'Профессиональная', 'Финансовая Академия'),
(2, 2, 'Курс HR менеджера', '2022-03-15', 'Образовательная', 'Институт Управления'),
(3, 3, 'Курс программиста', '2023-06-20', 'Образовательная', 'Технический Университет'),
(4, 4, 'Курс маркетолога', '2021-09-10', 'Образовательная', 'Школа Маркетинга'),
(5, 5, 'Курс менеджера по продажам', '2022-11-01', 'Профессиональная', 'Коммерческий Институт');

-- Таблица Увольнения
INSERT INTO Увольнения (ИдентификаторЗаписи, ИдентификаторСотрудника, ДатаУвольнения, Статья, ПричинаУвольнения)
VALUES 
(1, 2, '2023-05-10', 'Статья 81', 'По инициативе работодателя'),
(2, 4, '2023-08-20', 'Статья 77', 'По соглашению сторон'),
(3, 5, '2023-09-30', 'Статья 79', 'Истечение срока договора'),
(4, 1, '2023-06-15', 'Статья 80', 'По собственному желанию'),
(5, 3, '2023-11-10', 'Статья 83', 'Обстоятельства непреодолимой силы');

-- Таблица Отпуска
INSERT INTO Отпуска (ИдентификаторЗаписи, ТипОтпуска, ИдентификаторСотрудника, ДатаНачалаОтпуска, КоличествоДней, ДополнительныеДни, ДатаОкончания, ПричинаОтпуска)
VALUES 
(1, 'Ежегодный', 1, '2023-07-01', 14, 0, '2023-07-15', 'Плановый отпуск'),
(2, 'Ежегодный', 2, '2023-08-01', 14, 0, '2023-08-15', 'Плановый отпуск'),
(3, 'Ежегодный', 3, '2023-06-01', 14, 0, '2023-06-15', 'Плановый отпуск'),
(4, 'Ежегодный', 4, '2023-09-01', 14, 0, '2023-09-15', 'Плановый отпуск'),
(5, 'Ежегодный', 5, '2023-10-01', 14, 0, '2023-10-15', 'Плановый отпуск');

-- Таблица Командировки
INSERT INTO Командировки (ИдентификаторЗаписи, ИдентификаторСотрудника, МестоНазначения, НомерПриказа, ДатаПриказа, ДатаНачала, ДатаОкончания, КоличествоДней, Цель)
VALUES 
(1, 1, 'Москва', '12345', '2023-02-15', '2023-03-01', '2023-03-05', 5, 'Обучение'),
(2, 2, 'Санкт-Петербург', '54321', '2023-04-10', '2023-04-15', '2023-04-20', 5, 'Семинар'),
(3, 3, 'Новосибирск', '67890', '2023-06-20', '2023-07-01', '2023-07-07', 7, 'Рабочая встреча'),
(4, 4, 'Казань', '09876', '2023-08-25', '2023-09-01', '2023-09-03', 3, 'Конференция'),
(5, 5, 'Екатеринбург', '11223', '2023-11-12', '2023-11-20', '2023-11-22', 3, 'Совещание');

-- Таблица Поощрения
INSERT INTO Поощрения (ИдентификаторЗаписи, ИдентификаторСотрудника, Мотивация, ТипПоощрения, ДатаПоощрения, НомерДокумента, Основание, Сумма)
VALUES 
(1, 1, 'Превышение KPI', 'Премия', '2023-01-10', '12345', 'Плановое', 5000.00),
(2, 2, 'Инициативность', 'Бонус', '2023-02-15', '54321', 'Экстренное', 3000.00),
(3, 3, 'Результативность', 'Премия', '2023-03-20', '67890', 'Плановое', 7000.00),
(4, 4, 'Инициативность', 'Бонус', '2023-04-15', '09876', 'Плановое', 3500.00),
(5, 5, 'Улучшение продаж', 'Премия', '2023-05-12', '11223', 'Плановое', 10000.00);

-- Таблица Социальные льготы
INSERT INTO СоциальныеЛьготы (ИдентификаторЗаписи, ИдентификаторСотрудника, НазваниеЛьготы, НомерДокумента, ДатаВыдачи)
VALUES 
(1, 1, 'ДМС', '12345', '2022-05-01'),
(2, 2, 'Компенсация жилья', '54321', '2022-06-01'),
(3, 3, 'Обучение детей', '67890', '2022-07-01'),
(4, 4, 'Оплата проезда', '09876', '2022-08-01'),
(5, 5, 'Субсидия на жилье', '11223', '2022-09-01');
Enter fullscreen mode Exit fullscreen mode

Image description

Image description

Image description

Извлечение данных из базы данных с помощью MS SQL SERVER.

1

SELECT НазваниеДолжности,Оклад FROM Должности
Enter fullscreen mode Exit fullscreen mode

Image description

2

SELECT DISTINCT Образование FROM Сотрудники
Enter fullscreen mode Exit fullscreen mode

Image description
3

SELECT * 
FROM Сотрудники
WHERE Пол = 'М';
Enter fullscreen mode Exit fullscreen mode

Image description
4

SELECT * FROM Профессии
ORDER BY Оклад DESC;
Enter fullscreen mode Exit fullscreen mode

Image description
5

SELECT * FROM Сотрудники
WHERE ОпытРаботы > 3 
AND Образование = 'Среднее специальное'
Enter fullscreen mode Exit fullscreen mode

Image description
6

SELECT * FROM Должности
WHERE НазваниеДолжности = 'Бухгалтер' AND (ИдентификаторДолжности > 3 OR Оклад > 45000);
Enter fullscreen mode Exit fullscreen mode

Image description
7

SELECT * FROM Должности
WHERE Оклад NOT IN ('45000', '55000');
Enter fullscreen mode Exit fullscreen mode

Image description

8

UPDATE Отделы
SET НомерКомнаты = 999
WHERE ИдентификаторОтдела = 1;
Enter fullscreen mode Exit fullscreen mode

Image description
9

DELETE FROM ПаспортныеДанные WHERE СерияПаспорта = 5678;
Enter fullscreen mode Exit fullscreen mode

Before Update
Image description
After Update

Image description
10

SELECT * 
FROM Поощрения
WHERE Сумма > 3000
LIMIT 3;
Enter fullscreen mode Exit fullscreen mode

Image description
11

SELECT MIN(КоличествоДней)
FROM Командировки;
Enter fullscreen mode Exit fullscreen mode

Image description
12

SELECT MAX(КоличествоДней)
FROM Командировки;
Enter fullscreen mode Exit fullscreen mode

Image description
13

SELECT COUNT(ИдентификаторЗаписи)
FROM СоциальныеЛьготы
WHERE НомерДокумента IS 67890;
Enter fullscreen mode Exit fullscreen mode

Image description
14

SELECT SUM(КоличествоДней) AS [New Column]
FROM Отпуска
WHERE ДополнительныеДни = 0;
Enter fullscreen mode Exit fullscreen mode

Image description
15

SELECT AVG(Оклад) AS [New Column]
FROM Профессии
WHERE КодПрофессии > 1;
Enter fullscreen mode Exit fullscreen mode

Image description
16

SELECT * FROM Сотрудники
WHERE Отчество LIKE 'А%';
Enter fullscreen mode Exit fullscreen mode

Image description
17

SELECT * FROM Должности
WHERE НазваниеДолжности IN ('HR менеджер', 'Маркетолог');
Enter fullscreen mode Exit fullscreen mode

Image description

18

SELECT * FROM Должности
WHERE НазваниеДолжности NOT IN ('HR менеджер', 'Маркетолог');
Enter fullscreen mode Exit fullscreen mode

Image description
19

SELECT * FROM МедицинскиеОсмотры
WHERE ИдентификаторСотрудника BETWEEN 2 AND 4;
Enter fullscreen mode Exit fullscreen mode

Image description
20

SELECT Сотрудники.Имя AS Name, 
       Сотрудники.Фамилия AS SURNAME, 
       ПаспортныеДанные.ИдентификаторЗаписи AS Job, 
       ПаспортныеДанные.ИдентификаторЗаписи AS Job
FROM Увольнения
INNER JOIN Сотрудники ON Увольнения.ИдентификаторСотрудника = Сотрудники.ИдентификаторСотрудника
INNER JOIN ПаспортныеДанные ON Увольнения.ИдентификаторЗаписи = ПаспортныеДанные.ИдентификаторЗаписи;
Enter fullscreen mode Exit fullscreen mode

Image description
21

SELECT Сотрудники.Имя, Сотрудники.СемейноеПоложение, Профессии.Классификация, Профессии.Оклад
FROM Профессии
LEFT JOIN Сотрудники ON Сотрудники.ИдентификаторСотрудника = Профессии.КодПрофессии;
Enter fullscreen mode Exit fullscreen mode

Image description
22

SELECT Сотрудники.Имя, Сотрудники.СемейноеПоложение, Профессии.Классификация, Профессии.Оклад
FROM Профессии
RIGHT JOIN Сотрудники ON Сотрудники.ИдентификаторСотрудника = Профессии.КодПрофессии;
Enter fullscreen mode Exit fullscreen mode

24

SELECT COUNT(ИдентификаторОтдела), НомерКомнаты
FROM Отделы
GROUP BY НомерКомнаты;
Enter fullscreen mode Exit fullscreen mode

Image description
27

SELECT Имя, Отчество
FROM Сотрудники
WHERE EXISTS (SELECT Оклад FROM Профессии 
              WHERE Сотрудники.ИдентификаторСотрудника = Профессии.КодПрофессии AND Профессии.Оклад >60000);
Enter fullscreen mode Exit fullscreen mode

Image description

28

INSERT INTO NewWorkers (NewCustomerID, NewFirstName, NewLastNamee)
SELECT ИдентификаторСотрудника, Фамилия, Фамилия
FROM Сотрудники;
Enter fullscreen mode Exit fullscreen mode

Image description

Top comments (0)