Отдел кадров и бухгалтерия некоторой компании:

-- сотрудники: ФИО, паспортные данные, дом. и моб. телефоны;

-- отдел: комната, раб. телефоны (в т.ч. местный), подчинённые сотрудники, должность, тип(ы) работы, задание(я), проект(ы), размер зарплаты, форма зарплаты (почасовая, фиксированная).

Построение use case диаграммы

Возьмем за правило считать классы сущностями. Объектной модели можно сопоставить модели данных из-за постоянного характера классов. Стойкие классы могут выступать в качестве постоянного хранения данных во время работы приложения. Следовательно, для всех постоянных классов можно применить утверждение, что они могут использовать однозначное отображение в сущностях. Этот процесс называется маппированием.

Отношения Отделы, Сотрудник и Штатное расписание, Выполняемая_работа и Контактная_инфо выявленные на этапе построения концептуальной модели характеризуются следующими атрибутами (табл. 3.1).

Таблица 3.1 Атрибуты отношения Отделы

Отношению Отделы соответствует полная ФЗ Number > Name, Room, Phone.

Таблица 3.2 Атрибуты отношения Сотрудник

Отношению Сотрудник соответствует полная ФЗ ID > Pasport, Info, Contact_Info, Spesiallity, Years.

Таблица 3.3 Атрибуты отношения Штатное расписание

Отношению Штатное расписание соответствует полная ФЗ ID > Dolzhnost, Money.

Таблица 3.4 Атрибуты отношения Выполняемая работа

Отношению Выполняемая работа соответствует полная ФЗ Task > Salary, Kind_of_work, Kind_of_salary.

Таблица 3.5 Атрибуты отношения Контактная инфо

Отношению Контактная инфо соответствует полная ФЗ ID > Phone, Address, E-mail, Others.

Анализ функциональных зависимостей, которые имеют место для отношений Отделы, Сотрудник, Штатное расписание, Выполняемая работа, Контактная инфо показывают, что они полные. Следовательно, универсальное отношение Заработная плата нормализовано.

Логическая реализация базы данных

Реализуем таблицу Отделы. Для идентификации записей введем первичный ключ Number.

Таблица 4.1 Обоснование типов полей табл. Отделы

Реализуем таблицу Сотрудник. Для идентификации записей введем первичный ключ ID_s.

Таблица 4.2 Обоснование типов полей табл. Сотрудник

Реализуем таблицу Штатное расписание. Для идентификации записей введем первичный ключ ID.

Таблица 4.3 Обоснование типов полей табл. Штатно_ расписание

Реализуем таблицу Выполняемая работа. Для идентификации записей введем первичный ключ Task.

Таблица 4.4 Обоснование типов полей табл. Выполняемая_работа

Реализуем таблицу Контактная инфо. Для идентификации записей введем мигрирующий первичный ключ ID_s.

Таблица 4.5 Обоснование типов полей табл. Контактная_инфо

Сущности Отделы и Сотрудник, Отделы и Штатное расписание соединены связями «один-ко-многим». При этом происходит мигрирование первичного ключа ID_отдела и преобразование его во внешний. Сущности Сотрудник и Выполняемая работа, Сотрудник и Контактная инфо соединены связями «один-к-одному». При этом происходит мигрирование первичного ключа ID_сотрудника и преобразование его во внешний ключ (Выполняемая работа) или PFK (Контактная инфо).

Окончательная реляционная модель базы данных выглядит следующим образом:

Физическая реализация базы данных

Исходя из описанных выше утверждений, была спроектирована диаграмма классов, которая удовлетворяет этим тезисам (рис. 4.1).

Как видно из диаграммы классов она состоит из 4 классов: Типы, Объекты, Атрибуты и Параметры. В каждом классе, кроме класса Параметры, имеется 3 атрибута, из которых один - это идентификатор данного класса. В классы Объекты и Атрибуты мигрирует идентификатор из класса Типы. Класс Параметры не имеет своего идентификатора, но в него мигрируют идентификаторы из классов Атрибуты и Объекты.

Рисунок 4.1 -- Диаграмма классов

Проектирование метамодели базы данных

Отношения Типы, Объекты, Атрибуты и Параметры, выявленные на этапе построения концептуальной модели характеризуются следующими атрибутами (табл. 3.1).

Таблица 3.1 Атрибуты отношения «Типы»

Таблица 3.2 Атрибуты отношения «Объекты»

Таблица 3.3 Атрибуты отношения «Атрибуты»

Таблица 3.3 Атрибуты отношения «Параметры»

Отношение Параметры играет роль связи «Многие ко Многим», а атрибут ID_отношения показывает эту связь.

Логическая реализация метамодели

Реализуем таблицу object_types. Для идентификации записей введем первичный ключ object_type_id.

Таблица 5.1 Обоснование типов полей таблицы object_types:

Реализуем таблицу objects. Для идентификации записей введем первичный ключ object_id.

Таблица 5.2 Обоснование типов полей таблицы objects:

Реализуем таблицу attributes. Для идентификации записей введем первичный ключ attr_id.

Таблица 5.3 Обоснование типов полей таблицы attributes:

Реализуем таблицу cf_par. Первичных ключей таблица не имеет.

Таблица 5.4 Обоснование типов полей таблицы params:

Схема метамодели была реализована так:

Физическая реализация метамодели

Скрипты

delete from params;

delete from objects;

delete from attributes;

delete from object_types;

--object_types (object_type_id, name, description)

insert into object_types values (1, 'Department', null);

insert into object_types values (2, 'Worker', null);

insert into object_types values (3, 'List_of_memb', 'List_of_members');

insert into object_types values (4, 'Work', null);

insert into object_types values (5, 'Contact_info', 'Contact_information');

--attributes (attr_id, object_type_id, name)

insert into attributes values (1, 1, 'Room');

insert into attributes values (2, 1, 'Phone');

insert into attributes values (3, 2, 'Pasport');

insert into attributes values (4, 2, 'Spesiallity');

insert into attributes values (5, 2, 'Years');

insert into attributes values (6, 3, 'Salary');

insert into attributes values (7, 4, 'Task');

insert into attributes values (8, 4, 'Salary');

insert into attributes values (9, 4, 'Kind_of_work');

insert into attributes values (10, 4, 'Kind_of_salary');

insert into attributes values (11, 5, 'Phone');

insert into attributes values (12, 5, 'Address');

insert into attributes values (13, 5, 'E-mail');

insert into attributes values (14, 5, 'Others');

--objects (object_id, object_type_id, name)

--Department

insert into objects values (10, 1, 'Бухгалтерия');

insert into objects values (20, 1, 'Отдел кадров');

insert into objects values (30, 1, 'Пропускн_пункт');

insert into objects values (40, 1, 'Тех_отдел');

insert into objects values (50, 1, 'Тест_отдел');

--Worker

insert into objects values (11, 2, 'Костюк А. О.');

insert into objects values (12, 2, 'Миненко В. А.');

insert into objects values (13, 2, 'Чешкин Б. В.');

insert into objects values (14, 2, 'Синь Е. Р.');

insert into objects values (15, 2, 'Ешеренев П. Г.');

insert into objects values (16, 2, 'Соломкина А. А.');

insert into objects values (17, 2, 'Масько П. Л.');

insert into objects values (18, 2, 'Кучер Ф. Г.');

insert into objects values (19, 2, 'Шинкарюк Л. Д.');

insert into objects values (21, 2, 'Воронная Н. Д.');

--List_of_memb

insert into objects values (1, 3, 'Бухгалтер');

insert into objects values (2, 3, 'Менеджер о\к');

insert into objects values (3, 3, 'Вахтер');

insert into objects values (4, 3, 'Менеджер т\о');

insert into objects values (5, 3, 'Тестер');

--params (object_id, attr_id, text_value, number_value, date_value)

--Department => Room

insert into params (object_id, attr_id, number_value) values (10, 1, 21);

insert into params (object_id, attr_id, number_value) values (20, 1, 143);

insert into params (object_id, attr_id, number_value) values (30, 1, null);

insert into params (object_id, attr_id, number_value) values (40, 1, 32);

insert into params (object_id, attr_id, number_value) values (50, 1, 24);

--Department => Phone

insert into params (object_id, attr_id, text_value) values (10, 2, '(0542)236-743');

insert into params (object_id, attr_id, text_value) values (20, 2, '(0542)298-756');

insert into params (object_id, attr_id, text_value) values (30, 2, '(0542)255-733');

insert into params (object_id, attr_id, text_value) values (40, 2, '(0542)278-736');

insert into params (object_id, attr_id, text_value) values (50, 2, '(0542)243-721');

--Worker => Pasport

insert into params (object_id, attr_id, text_value) values (11, 3, 'MB347856, Сумским ГРО УМВД 20.09.03, прописка - г. Сумы');

insert into params (object_id, attr_id, text_value) values (12, 3, 'MB836472, Сумским ГРО УМВД 15.05.96, прописка - г. Сумы');

insert into params (object_id, attr_id, text_value) values (13, 3, 'MB917382, Конотопским ГРО УМВД 20.03.93, прописка - г. Конотоп');

insert into params (object_id, attr_id, text_value) values (14, 3, 'MB153728, Сумским ГРО УМВД 17.04.76, прописка - г. Сумы');

insert into params (object_id, attr_id, text_value) values (15, 3, 'MB153728, Белопольским ГРО УМВД 28.08.74, прописка - г. Ворожба');

insert into params (object_id, attr_id, text_value) values (16, 3, 'MB635217, Сумским ГРО УМВД 25.06.71, прописка - г. Сумы');

insert into params (object_id, attr_id, text_value) values (17, 3, 'MB762534, Ахтырским ГРО УМВД 20.09.95, прописка - г. Ахтырка');

insert into params (object_id, attr_id, text_value) values (18, 3, 'MB453277, Харьковским ГРО УМВД 10.09.96, прописка - г. Харьков');

insert into params (object_id, attr_id, text_value) values (19, 3, 'MB256346, Киевским ГРО УМВД 05.06.92, прописка - г. Киев');

insert into params (object_id, attr_id, text_value) values (21, 3, 'MB736488, Сумским ГРО УМВД 20.09.98, прописка - г. Сумы');

--Worker => Spesiallity

insert into params (object_id, attr_id, text_value) values (11, 4, 'Бухгалтер');

insert into params (object_id, attr_id, text_value) values (12, 4, 'Бухгалтер');

insert into params (object_id, attr_id, text_value) values (13, 4, 'Менеджер');

insert into params (object_id, attr_id, text_value) values (14, 4, 'Вахтер');

insert into params (object_id, attr_id, text_value) values (15, 4, 'Вахтер');

insert into params (object_id, attr_id, text_value) values (16, 4, 'Вахтер');

insert into params (object_id, attr_id, text_value) values (17, 4, 'Менеджер');

insert into params (object_id, attr_id, text_value) values (18, 4, 'Тестер');

insert into params (object_id, attr_id, text_value) values (19, 4, 'Тестер');

insert into params (object_id, attr_id, text_value) values (21, 4, 'Тестер');

--Worker => Years

insert into params (object_id, attr_id, date_value) values (11, 5, TO_DATE('2007-02-22', 'YYYY-MM-DD'));

insert into params (object_id, attr_id, date_value) values (12, 5, TO_DATE('2002-04-12', 'YYYY-MM-DD'));

insert into params (object_id, attr_id, date_value) values (13, 5, TO_DATE('2005-02-12', 'YYYY-MM-DD'));

insert into params (object_id, attr_id, date_value) values (14, 5, TO_DATE('2010-07-30', 'YYYY-MM-DD'));

insert into params (object_id, attr_id, date_value) values (15, 5, TO_DATE('2009-05-20', 'YYYY-MM-DD'));

insert into params (object_id, attr_id, date_value) values (16, 5, TO_DATE('2004-06-18', 'YYYY-MM-DD'));

insert into params (object_id, attr_id, date_value) values (17, 5, TO_DATE('2007-10-29', 'YYYY-MM-DD'));

insert into params (object_id, attr_id, date_value) values (18, 5, TO_DATE('2001-10-22', 'YYYY-MM-DD'));

insert into params (object_id, attr_id, date_value) values (19, 5, TO_DATE('2002-11-28', 'YYYY-MM-DD'));

insert into params (object_id, attr_id, date_value) values (21, 5, TO_DATE('2003-12-01', 'YYYY-MM-DD'));

--List_of_memb => Salary

insert into params (object_id, attr_id, number_value) values (1, 6, 2000);

insert into params (object_id, attr_id, number_value) values (2, 6, 1800);

insert into params (object_id, attr_id, number_value) values (3, 6, 800);

insert into params (object_id, attr_id, number_value) values (4, 6, 2500);

insert into params (object_id, attr_id, number_value) values (5, 6, 2300);

--Work => Task

insert into params (object_id, attr_id, text_value) values (11, 7, null);

insert into params (object_id, attr_id, text_value) values (12, 7, null);

insert into params (object_id, attr_id, text_value) values (13, 7, null);

insert into params (object_id, attr_id, text_value) values (14, 7, null);

insert into params (object_id, attr_id, text_value) values (15, 7, null);

insert into params (object_id, attr_id, text_value) values (16, 7, null);

insert into params (object_id, attr_id, text_value) values (17, 7, null);

insert into params (object_id, attr_id, text_value) values (18, 7, 'тестирование офисного прогр. обесп.');

insert into params (object_id, attr_id, text_value) values (19, 7, 'тестирование пользовательского прогр. обесп.');

insert into params (object_id, attr_id, text_value) values (21, 7, 'тестирование системного прогр. обесп.');

--Work => Salary

insert into params (object_id, attr_id, number_value) values (11, 8, 2000);

insert into params (object_id, attr_id, number_value) values (12, 8, 2000);

insert into params (object_id, attr_id, number_value) values (13, 8, 1800);

insert into params (object_id, attr_id, number_value) values (14, 8, 800);

insert into params (object_id, attr_id, number_value) values (15, 8, 800);

insert into params (object_id, attr_id, number_value) values (16, 8, 800);

insert into params (object_id, attr_id, number_value) values (17, 8, 2500);

insert into params (object_id, attr_id, number_value) values (18, 8, 2300);

insert into params (object_id, attr_id, number_value) values (19, 8, 2300);

insert into params (object_id, attr_id, number_value) values (21, 8, 2300);

--Work => Kind_of_work

insert into params (object_id, attr_id, text_value) values (11, 9, 'на предпр.');

insert into params (object_id, attr_id, text_value) values (12, 9, 'на предпр.');

insert into params (object_id, attr_id, text_value) values (13, 9, 'на предпр.');

insert into params (object_id, attr_id, text_value) values (14, 9, 'на предпр.');

insert into params (object_id, attr_id, text_value) values (15, 9, 'на предпр.');

insert into params (object_id, attr_id, text_value) values (16, 9, 'на предпр.');

insert into params (object_id, attr_id, text_value) values (17, 9, 'на предпр.');

insert into params (object_id, attr_id, text_value) values (18, 9, 'на предпр.');

insert into params (object_id, attr_id, text_value) values (19, 9, 'на дому');

insert into params (object_id, attr_id, text_value) values (21, 9, 'на предпр.');

--Work => Kind_of_salary

insert into params (object_id, attr_id, text_value) values (11, 10, 'почасовая');

insert into params (object_id, attr_id, text_value) values (12, 10, 'почасовая');

insert into params (object_id, attr_id, text_value) values (13, 10, 'почасовая');

insert into params (object_id, attr_id, text_value) values (14, 10, 'почасовая');

insert into params (object_id, attr_id, text_value) values (15, 10, 'почасовая');

insert into params (object_id, attr_id, text_value) values (16, 10, 'почасовая');

insert into params (object_id, attr_id, text_value) values (17, 10, 'почасовая');

insert into params (object_id, attr_id, text_value) values (18, 10, 'фиксир.');

insert into params (object_id, attr_id, text_value) values (19, 10, 'фиксир.');

insert into params (object_id, attr_id, text_value) values (21, 10, 'фиксир.');

--Contact_info

--Phone

insert into params (object_id, attr_id, text_value) values (11, 11, '(0542)736-839');

insert into params (object_id, attr_id, text_value) values (12, 11, '(0542)736-839');

insert into params (object_id, attr_id, text_value) values (13, 11, '(05456)5-36-34');

insert into params (object_id, attr_id, text_value) values (14, 11, '(0542)674-256');

insert into params (object_id, attr_id, text_value) values (15, 11, '(05423)7-36-89');

insert into params (object_id, attr_id, text_value) values (16, 11, '(0542)648-820');

insert into params (object_id, attr_id, text_value) values (17, 11, '(05446)2-49-88');

insert into params (object_id, attr_id, text_value) values (18, 11, '(065)473-68-39');

insert into params (object_id, attr_id, text_value) values (19, 11, '(044)273-61-21');

insert into params (object_id, attr_id, text_value) values (21, 11, '(0542)836-358');

--Address

insert into params (object_id, attr_id, text_value) values (11, 12, 'г. Сумы, ул. Ахтырская, 3');

insert into params (object_id, attr_id, text_value) values (12, 12, 'г. Сумы, ул. Кирова, 32');

insert into params (object_id, attr_id, text_value) values (13, 12, 'г. Конотоп, ул. Ленина, 257');

insert into params (object_id, attr_id, text_value) values (14, 12, 'г. Сумы, ул. Октября, 3');

insert into params (object_id, attr_id, text_value) values (15, 12, 'г. Ворожба, ул. Колпака, 34');

insert into params (object_id, attr_id, text_value) values (16, 12, 'г. Сумы, ул. Харьковска, 23');

insert into params (object_id, attr_id, text_value) values (17, 12, 'г. Ахтырка, ул. Шевченка, 2');

insert into params (object_id, attr_id, text_value) values (18, 12, 'г. Харьков, ул. Курская, 18');

insert into params (object_id, attr_id, text_value) values (19, 12, 'г. Киев, ул. О.Вишни, 78');

insert into params (object_id, attr_id, text_value) values (21, 12, 'г. Сумы, ул. Береговая, 104');

--E-mail

insert into params (object_id, attr_id, text_value) values (11, 13, 'jhsdfjskl@yandex.ru');

insert into params (object_id, attr_id, text_value) values (12, 13, '123mjhj@yandex.ru');

insert into params (object_id, attr_id, text_value) values (13, 13, 'loigerg@mail.ru');

insert into params (object_id, attr_id, text_value) values (14, 13, 'vcmhdl@bk.ru');

insert into params (object_id, attr_id, text_value) values (15, 13, 'oehcpspss@yandex.ru');

insert into params (object_id, attr_id, text_value) values (16, 13, 'juise@yandex.ru');

insert into params (object_id, attr_id, text_value) values (17, 13, 'lidufklasjd@mail.ru');

insert into params (object_id, attr_id, text_value) values (18, 13, 'isdkasd@yandex.ru');

insert into params (object_id, attr_id, text_value) values (19, 13, 'lskdjflskdj@yandex.ru');

insert into params (object_id, attr_id, text_value) values (21, 13, 'kudfkjdkdldd@yandex.ru');

--Other

insert into params (object_id, attr_id, text_value) values (11, 14, null);

insert into params (object_id, attr_id, text_value) values (12, 14, null);

insert into params (object_id, attr_id, text_value) values (13, 14, 'ICQ - 456789');

insert into params (object_id, attr_id, text_value) values (14, 14, null);

insert into params (object_id, attr_id, text_value) values (15, 14, null);

insert into params (object_id, attr_id, text_value) values (16, 14, null);

insert into params (object_id, attr_id, text_value) values (17, 14, null);

insert into params (object_id, attr_id, text_value) values (18, 14, null);

insert into params (object_id, attr_id, text_value) values (19, 14, null);

insert into params (object_id, attr_id, text_value) values (21, 14, null);

commit;

Создание выборок

1. SELECT o.name, p1.text_value , EXTRACT (YEAR FROM SYSDATE) - EXTRACT (YEAR FROM p2.date_value) Stazh

FROM objects o JOIN params p1

ON o.object_id=p1.object_id AND p1.attr_id=4

JOIN params p2

ON o.object_id=p2.object_id AND p2.attr_id=5; -- выводит сотрудников, их специальности и стаж работы (использование функции - EXTRACT)

2. select o.name, p1.text_value, p2.text_value, p3.number_value

from objects o join params p1

on o.object_id=p1.object_id and p1.attr_id=4

join params p2

on p1.object_id=p2.object_id and p2.attr_id=3

join params p3

on p2.object_id=p3.object_id and p3.attr_id=8; -- выводит ФИО, специальность, паспортные данные и заработную плату сотрудников (выборка)

3. select distinct text_value

from params

where attr_id=4; -- выводит уникальные записи должностей (проекция)

Выводы

Выполняя курсовую работу, я освоила язык для создания баз данных - SQL. Моей задачей было создание базы данных некоего предприятия. Во время выполнения задания я изучила основные понятия и компоненты этого языка: типы моделей, структура сущностей (таблиц), выражения SQL , DML (разность, проекция, пересечение, выборка, объединение), соединения, функции, операторы (арифметические, AND, IN, NOT IN…), иерархию данных, метамодель и многое другое.

Самым сложным в понимании мне показались соединения и метамодель, но я преодолела эти трудности.

Итогом моей работы стала полноценная база данных предприятия, которую можно использовать для различных целей.

Список литературы