4.Нормализация 3нф (не менее 3 таблиц)
Из определения 3-ой нормальной формы: отношение находится в третьей нормальной форме если оно уже 2НФ и любой её не ключевой атрибут функционально зависит только от первичного ключа.
Отношение оказалось в 3НФ. Нормализация выполнена.
5.Структура таблиц. (Код создания, описание столбцов)
Таблица 1 на псевдокоде:
Create table worker
( worker_name varchar(100) not null PRIMARY KEY
,section varchar(100) not null FOREIGN KEY REFERENCES section_table
,duty varchar(100) not null FOREIGN KEY REFERENCES duty_table)
Описание столбцов:
worker_name - полное имя сотрудника. тип строка (до 100 символов), первичный ключ.
section - отдел. тип строка (до 100 символов), внешний ключ .
duty - должность. тип строка (до 100 символов), внешний ключ.
Таблица 2 на псевдокоде:
Create table duty_table
( duty varchar(100) not null PRIMARY KEY
,PC tinyint not null DEFAULT 0)
Описание столбцов:
duty - Должность. тип строка (до 100 символов), первичный ключ.
PC - Признак наличия PC, тип число (tinyint), по умалчанию 0;
Таблица 3 на псевдокоде:
Create table section_table
( section varchar(100) not null PRIMARY KEY
,tel varchar(15) NOT NULL
,fax varchar(15) NULL)
Описание столбцов:
section - отдел. тип строка (до 100 символов), первичный ключ.
tel - Номер телефона в формате (495)-123-45-67.
fax - Номер факса в формате (495)-123-45-67.
В описании столбцов применяется формат номера телефона в виде (XXX)-XXX-XX-XX, однако в самой таблице "отдел" телефон записан в формате ХХХ-ХХХХ. Обратите внимание (в описании столбца) почему было принято решение записать телефон именно в таком формате (с кодом города). Нужно понимать что архитектура БД может потерпеть некое косметическое изменение.Здесь следует добавить ограничение CHECK для правильного ввода номера телефона в нужном формате.
6. Схема бд
Нарисовать от руки (временно) Отчет: Сдать 1 часть работы (в ворде)
2 Часть. Физическая модель
1.Создать базу данных в конкретной субд (на выбор) (не менее 2 таблиц)
Создана БД "worker" и нужные таблицы. Переходим к диаграмме базы данных "worker":
Будет лучше если мы (проектировщик) будем использовать в качестве ключа таблиц section_table и duty_table не "строковые" (varchar) а цифровые типа идентификатора: т.е. по хорошему нужно ещё добавить столбцы duty_id и section_id в соответствующих таблицах.
Добавляем (или создаём) нужные нам таблицы. Получаем схему (диаграмму) БД "worker".
2.Создать схему бд
В физической модели БД можно разглядеть все ключи и отношения между таблицами.
3. Создать представление для вывода данных из двух таблиц.
4. Заполнить таблицы (10 записей в основной таблице)
Отчет: Распечатать
1.Содержимое таблиц в Excel.(Синтаксис команды или применение утилиты "Import and Export Data)
2.Схему БД
3. Представление (синтаксис и содержание –скрин)
4. Шаблоны процедур, функций или триггеров. (по желанию)
Процедура:
CREATE procedure proc_workers_in_section @section varchar(100) as
SELECT S.section, count(*) as cnt
into #t -- записали результат во временную таблицу
FROM worker.dbo.section_table S with (nolock) -- Грязное чтение
inner join
worker.dbo.worker W with (nolock) -- Грязное чтение
ON S.section = W.section
WHERE S.section = @section
GROUP BY S.section
-- Выводим результат из временной таблицы.
SELECT * from #t;
drop table #t;
Функция типа "table-valued":
CREATE FUNCTION tfunc_workers_in_section (@section varchar(100))
RETURNS @t1 table
(
section varchar(100)
,cnt int )
as
BEGIN
INSERT @t1 (section, cnt)
SELECT S.section, count(*) as cnt
FROM worker.dbo.section_table S with (nolock) -- Грязное чтение
inner join
worker.dbo.worker W with (nolock) -- Грязное чтение
ON S.section = W.section
WHERE S.section = @section
GROUP BY S.section
RETURN
END
Функция типа "scalar-valued":
CREATE FUNCTION sfunc_workers_in_section (@section varchar(100))
RETURNS int
AS
BEGIN
declare @cnt int
SELECT @cnt = count(*)
FROM worker.dbo.section_table S with (nolock) -- Грязное чтение
inner join
worker.dbo.worker W with (nolock) -- Грязное чтение
ON S.section = W.section
WHERE S.section = @section
GROUP BY S.section
RETURN @cnt
END
Триггер:
CREATE trigger t_worker on worker.dbo.worker
after /*BEFORE*/ update
as
declare @old_value varchar(50), @host_name varchar(50);
declare @new_value varchar(50);
select @old_value=deleted.worker_name, @host_name = HOST_NAME() from deleted;
select @new_value=inserted.worker_name from inserted;
-- Заполняем уже созданную таблицу
INSERT WORKER_history (worker_name, HOST_NAME)
values (@old_value, @host_name), (@new_value, @host_name)
Проектирование БД завершено. БД создана, таблицы созданы, отношение между таблицами установлено.