Экзаменационное задание по дисциплине СУБД

БД состоит из трех отношений – «сотрудник» (employee), «должность» (job) и «отдел» (department). Отношение «сотрудник» связано с отношениями «должность» и «отдел» связями типа «многие-к-одному», т.к. каждый сотрудник работает в каком-то отделе и каждый сотрудник имеет какую-то должность. Кроме того, каждый сотрудник имеет начальника (атрибут «manager»), который также является сотрудником, поэтому отношение «сотрудник» связано само с собой. Значение остальных атрибутов описано в комментариях в сценарии создания БД.

В качестве экзаменационного задания студенту предлагается:

1) Изучить структуру предлагаемой БД.

2) Выполнить сценарий генерации БД и наполнения БД тестовыми наборами данных.

3) Согласно заданию (выдает преподаватель) написать и отладить SQL-запрос.

4) Согласно заданию (выдает преподаватель) написать и отладить триггер или хранимую процедуру (функцию).

Пример запроса:

Для каждого отдела получить количество различных начальников у сотрудников этого отдела.

SELECT Department.Dep_Name, count(distinct EMP2.EMP_ID) FROM Employee AS Emp1, Department, Employee AS Emp2 WHERE Department.DEP_ID = Emp1.DEP_ID AND Emp1.Manager=Emp2.EMP_ID GROUP BY Department.DEP_ID

Результат выполнения:

Другой вариант этого же запроса:

SELECT Department.Dep_Name, count(*) FROM Employee AS Emp1, Department WHERE Emp1.EMP_ID IN (SELECT Manager FROM Employee AS EMP2 WHERE EMP2.DEP_ID=Department.DEP_ID) GROUP BY Department.DEP_ID

Пример триггера:

Триггер, запрещающий сотрудникам отдела иметь начальников, работающих более чем в двух отделах (включая данный).

CREATE

DEFINER = 'root'@'%'

TRIGGER exam.trigger1

BEFORE INSERT

ON exam.employee

FOR EACH ROW

BEGIN

DECLARE N INTEGER;

-- подсчет в N количества отделов, в которых работают начальники сотрудников отдела, куда добавляется сотрудник (с учетом добавляемого сотрудника)

SELECT count(distinct Department.DEP_ID) INTO N FROM Employee AS EMP1, Department, Employee AS EMP2

WHERE EMP1.DEP_ID=NEW.DEP_ID AND (EMP1.Manager=EMP2.EMP_ID OR NEW.Manager=EMP2.EMP_ID) AND EMP2.DEP_ID=Department.DEP_ID;

IF N > 2 THEN SET NEW='Начальники сотрудников данного отдела работают более чем в двух отделах';

END IF;

END

Другой вариант триггера:

BEGIN

DECLARE N INTEGER;

-- подсчет в N количества отделов, в которых работают начальники сотрудников отдела, куда добавляется сотрудник (с учетом добавляемого сотрудника)

SELECT count(*) INTO N FROM Department WHERE Department.DEP_ID IN

(SELECT EMP2.DEP_ID FROM Employee AS EMP1, Employee AS EMP2

WHERE EMP1.DEP_ID=new.DEP_ID AND (EMP1.Manager=EMP2.EMP_ID OR EMP2.EMP_ID=NEW.Manager));

IF N > 2 THEN SET NEW='Начальники сотрудников данного отдела работают более чем в двух отделах';

END IF;

END

Сценарий БД:

-- Скрипт сгенерирован Devart dbForge Studio for MySQL, Версия 4.50.311.1

-- Дата: 16.01.2011 22: 50: 46

-- Версия сервера: 6.0.9-alpha-community

-- Версия клиента: 4.1

--

-- Отключение внешних ключей

--

/*! 40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

--

-- Установка кодировки, с использованием которой клиент будет посылать запросы на сервер

--

SET NAMES 'utf8';

--

-- Установка базы данных по умолчанию

--

USE exam;

--

-- Описание для таблицы department

--

DROP TABLE IF EXISTS department;

CREATE TABLE department (

DEP_ID INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор отдела',

DEP_NAME VARCHAR(30) DEFAULT NULL COMMENT 'Наименование отдела',

PRIMARY KEY (DEP_ID)

)

ENGINE = INNODB

AUTO_INCREMENT = 6

AVG_ROW_LENGTH = 3276

CHARACTER SET cp1251

COLLATE cp1251_general_ci

COMMENT = 'Отдел';

--

-- Описание для таблицы job

--

DROP TABLE IF EXISTS job;

CREATE TABLE job (

JOB_ID INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор должности',

POS_NAME VARCHAR(20) DEFAULT NULL COMMENT 'Наименование должности',

SALARY INT(11) DEFAULT NULL COMMENT 'Зарплата',

PRIMARY KEY (JOB_ID)

)

ENGINE = INNODB

AUTO_INCREMENT = 9

AVG_ROW_LENGTH = 2048

CHARACTER SET cp1251

COLLATE cp1251_general_ci

COMMENT = 'Должность';

--

-- Описание для таблицы employee

--

DROP TABLE IF EXISTS employee;

CREATE TABLE employee (

EMP_ID INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор сотрудника',

EMP_NAME VARCHAR(20) DEFAULT NULL COMMENT 'ФИО сотрудника',

JOB_ID INT(11) DEFAULT NULL COMMENT 'Идентификатор должности',

MANAGER INT(11) DEFAULT NULL COMMENT 'Начальник сотрудника',

DATE_IN DATE DEFAULT NULL COMMENT 'Дата трудоустройства',

BONUS INT(11) DEFAULT NULL COMMENT 'Размер премии (%)',

DEP_ID INT(11) DEFAULT NULL COMMENT 'Идентификатор отдела',

PRIMARY KEY (EMP_ID),

INDEX employee_ibfk_1 (JOB_ID),

INDEX employee_ibfk_2 (DEP_ID),

INDEX employee_ibfk_3 (MANAGER),

CONSTRAINT employee_ibfk_1 FOREIGN KEY (JOB_ID)

REFERENCES job(JOB_ID) ON DELETE RESTRICT ON UPDATE RESTRICT,

CONSTRAINT employee_ibfk_2 FOREIGN KEY (DEP_ID)

REFERENCES department(DEP_ID) ON DELETE RESTRICT ON UPDATE RESTRICT,

CONSTRAINT employee_ibfk_3 FOREIGN KEY (MANAGER)

REFERENCES employee(EMP_ID) ON DELETE RESTRICT ON UPDATE RESTRICT

)

ENGINE = INNODB

AUTO_INCREMENT = 24

AVG_ROW_LENGTH = 819

CHARACTER SET cp1251

COLLATE cp1251_general_ci;

--

-- Вывод данных для таблицы department

--

INSERT INTO department VALUES

(1, 'Директорат'),

(2, 'Отдел сбыта'),

(3, 'Склад'),

(4, 'Производственный отдел'),

(5, 'Бухгалтерия');

--

-- Вывод данных для таблицы job

--

INSERT INTO job VALUES

(1, 'Директор', 12000),

(2, 'Начальник отдела', 8000),

(3, 'Инженер', 6500),

(4, 'Бухгалтер', 4000),

(5, 'Экономист', 4200),

(6, 'Охранник', 2600),

(7, 'Уборщица', 2400),

(8, 'Секретарь', 3800);

--

-- Вывод данных для таблицы employee

--

INSERT INTO employee VALUES

(1, 'Иванов', 1, NULL, '2006-01-19', 10, 1),

(2, 'Петров', 2, 1, '2011-01-16', 2, 2),

(3, 'Орлов', 2, 1, '2011-01-12', 2, 3),

(4, 'Cвиридов', 2, 1, '2008-01-16', 2, 5),

(5, 'Сидоров', 3, 6, '2010-01-07', 6, 2),

(6, 'Конев', 3, 3, '2009-01-13', 6, 3),

(7, 'Маслов', 3, 5, '2011-01-13', 4, 4),

(8, 'Прохоров', 5, 5, '2011-01-19', 5, 2),

(9, 'Демьянов', 6, 3, '2011-01-18', 5, 3),

(10, 'Лукин', 6, 4, '2011-01-07', 5, 5),

(11, 'Иванова', 5, 5, '2011-01-04', 8, 4),

(12, 'Матронова', 5, 5, '2008-01-23', 8, 5),

(13, 'Слогина', 5, 5, '2009-01-14', 8, 5),

(14, 'Малова', 7, 3, '2009-01-14', 4, 3),

(15, 'Игошева', 7, 4, '2011-01-18', 4, 4),

(16, 'Ступина', 8, 1, '2011-01-17', 9, 1),

(17, 'Глухова', 3, 2, '2011-01-16', 3, 2),

(18, 'Пономарев', 4, 2, '2011-01-20', 4, 2),

(19, 'Кузнецов', 5, 1, '2008-01-18', 5, 2),

(20, 'Чулкова', 8, 2, '2011-01-04', 6, 1);

--

-- Включение внешних ключей

--

/*! 40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;