Пример проектирования базы данных "Сложная система, спроектированная наспех, никогда не работает, и исправить её, чтобы заставить работать, невозможно". Законы Мерфи. 16-й закон системантики

База данных проектной организации Общая постановка задачи: база данных создаётся для информационного обслуживания руководства организации, руководителей проектов и участников проектов. Основная деятельность организации – выполнение проектов. Договоры на выполнение проектов заключаются с заказчиками (каждый договор – с одним заказчиком). Каждый проект должен быть выполнен в заданные сроки, каждый проект может состоять из нескольких этапов. Если проект состоит из одного этапа, то сроки его выполнения должны совпадать со сроками выполнения проекта в целом. По каждому этапу определяется форма отчетности и сумма оплаты. БД должна содержать данные об отделах организации, проектах и сотрудниках, участвующих в реализации проектов. Сотрудник может участвовать в проекте в качестве руководителя, исполнителя или консультанта.

Сущности предметной области Отделы. Атрибуты: название, аббревиатура, комнаты, телефоны. Атрибуты: ФИО, паспортные данные, дата рождения, пол, ИНН (индивидуальный номер налогоплательщика), номер пенсионного страхового свидетельства, адреса, телефоны (рабочий, домашний, мобильный), данные об образовании (вид образования – высшее, среднее и т.д., – специальность, номер диплома, дата окончания учебного заведения), должность, оклад, логин (имя пользователя). Сотрудники. Атрибуты: номер договора; полное название проекта; сокращённое название проекта; дата подписания договора; заказчик; контактные данные заказчика; дата начала проекта; дата завершения проекта; сумма по проекту; дата реальной сдачи проекта; сумма, полученная по проекту на текущую дату. Проекты. Атрибуты: номер по порядку, название, дата начала этапа, дата завершения этапа, форма отчетности, сумма по этапу, дата реальной сдачи этапа; сумма, полученная по этапу на текущую дату. Этапы проекта.

Особенности предметной области Каждый отдел занимает одно или несколько помещений (комнат), в каждом помещении может быть один или несколько стационарных телефонов. Каждый сотрудник работает в определённом отделе, в каждом отделе могут работать несколько сотрудников. Каждый проект относится к определённому отделу, каждый отдел может отвечать за несколько проектов. Каждый сотрудник может принимать участие в выполнении нескольких проектов, над каждым проектом может трудиться несколько сотрудников. Для каждого проекта назначается руководитель из числа сотрудников отдела, к которому относится проект. Каждый проект может состоять из нескольких этапов. Оклад сотрудника зависит от занимаемой должности, за участие в проектах сотрудник получает дополнительное вознаграждение. Отделы – сотрудники: Отделы – проекты: Сотрудники – проекты: Проекты – этапы: Сотрудники – оплата: Отделы – комнаты:

ER-диаграмма (модель предметной области) На ER-диаграмме используются следующие обозначения:

Анализ информационных задач и круга пользователей системы 1. Руководители организации: заключение новых договоров; назначение руководителей проектов; получение списка всех участников проектов; изменение должностных окладов и штатного расписания; получение полной информации о проектах; внесение изменений в данные о проектах; архивирование данных по завершённым проектам. 2. Руководитель проекта: назначение участников проекта; получение списка сотрудников, работающих над проектом; получение полной информации о проекте, руководителем которого он является; получение сведений о сотрудниках, которые могут стать участниками проекта; определение размера дополнительного вознаграждения сотрудников по конкретному проекту; внесение изменений в данные об этапах проекта.

Анализ информационных задач и круга пользователей системы 3. Сотрудники отдела кадров: приём/увольнение сотрудников; внесение изменений в данные о сотрудниках. 4. Бухгалтеры: получение ведомости на выплату зарплаты. 5. Сотрудники – участники проектов: просмотр данных о других участниках проекта; просмотр данных о сроках сдачи проекта и форме отчётности. Каждая группа выполняет определённые задачи и обладает разными правами доступа к системе. Перечень задач является основой для разработки приложений и выдаётся программистам в качестве задания (вместе со схемой БД).

Логическое проектирование РБД Уточнённая ER- диаграмма

Логическое проектирование РБД Преобразование ER-диаграммы в схемы БД. Осуществляется в соответствии с правилами преобразования, рассмотренными ранее. Для схемы БД будем использовать обозначения:

Логическое проектирование РБД Схема реляционной базы данных: Связь не может быть обязательной в обе стороны, поэтому снимаем условие обязательности со связи Проекты-Этапы (со стороны ПК). Схема содержит 3 цикла: "сотрудники–проекты–участие–сотрудники" "отделы–сотрудники–проекты–отделы" "отделы–сотрудники–участие–проекты–отделы".

Логическое проектирование РБД Рассмотрим способы разрешения циклов на более простом примере:

Логическое проектирование РБД Составление схем реляционных отношений: Каждое реляционное отношение соответствует одной сущности (объекту ПрО) и в него вносятся все атрибуты этой сущности. Для каждого отношения определяются первичный ключ и внешние ключи (в соответствии со схемой БД). В том случае, если базовое отношение не имеет потенциальных ключей, вводится суррогатный первичный ключ, который не несёт смысловой нагрузки и служит только для идентификации записей.

Логическое проектирование РБД

Аномалии модификации данных При неправильно спроектированной схеме БД могут возникнуть аномалии выполнения операций модификации данных. Рассмотрим эти аномалии на примере следующего отношения: ПОСТАВКИ (Номер поставки, Название товара, Цена товара, Количество, Дата поставки, Название поставщика, Адрес поставщика) Различают аномалии обновления, удаления и добавления. Аномалия обновления может возникнуть в том случае, когда информация дублируется. Другие аномалии возникают тогда, когда две и более сущности объединены в одно отношение. Например: Аномалия обновления: изменился адрес поставщика. Если от него было несколько поставок, то придется менять несколько записей. Аномалия удаления: при удалении в архив записей обо всех поставках определённого поставщика все данные об этом поставщике (название, адрес) будут утеряны. Аномалия добавления: нельзя добавить сведения о поставщике, пока от него нет ни одной поставки. Для решения проблемы аномалии модификации данных при проектировании РБД проводится нормализация отношений.

Отношения БД после нормализации Нормализация проводится до 3НФ.

Отношения БД после нормализации

Схема БД после нормализации

Определение дополнительных ограничений целостности Перечислим ограничения целостности, которые не указаны в табл. 6–15. 1.Атрибут Роль может принимать одно из двух значений: 'исполнитель' или 'консультант'. 2.В поле Доплата хранится величина доплаты сотруднику за участие в проекте (в процентах к его окладу). Значение поля больше либо равно 0. 3.Нумерация в поле Номер этапа начинается с 1 и является непрерывной для каждого проекта. 4.Дата начала первого этапа проекта должна соответствовать началу проекта в целом, дата завершения последнего этапа должна соответствовать завершению проекта в целом. Этапы не должны пересекаться по времени и между ними не должно быть разрывов. 5.Стоимость проекта должна быть равна сумме стоимостей всех этапов этого проекта.

Описание групп пользователей и прав доступа S – select, I – insert, U – update, D – delete. Права на изменение данных в таблице УЧАСТИЕ будут назначены через представление, т.к. изменять данные этой таблицы может только руководитель проекта.

Физическое проектирование РБД При использовании СУБД Oracle примерная последовательность создания объектов БД следующая: 1.Создание БД (create database). 2.Создание пользователей (create user). 3.Создание пользовательских типов (create type). 4.Создание кластеров и таблиц (create cluster, create table). 5.Создание представлений (create view). 6.Создание синонимов (create synonym). 7.Создание последовательностей (create sequence). 8.Назначение прав доступа (grant). 9.Заливка данных (Oracle Loader, imp.exe,…). 10.Создание индексов (create index). 11.Создание процедур и функций (create procedure, create function). 12.Создание триггеров (create trigger).

Физическое проектирование РБД Создание отношений: Отношение Departs (отделы): create table departs ( d_idvarchar(12) primary key, d_namevarchar(100) not null); Отношение Rooms (комнаты): create table rooms ( d_depart varchar(12) references departs(d_id), r_room numeric(4) not null, r_phonevarchar(20), unique(r_room, r_phone)); Отношение Posts (должности): create table posts ( p_postvarchar(30) primary key, p_salarynumeric(8,2) not null check (p_salary >= 4500));

Физическое проектирование РБД Создание отношений: Отношение Employees (сотрудники): create table employees ( e_idnumeric(4) primary key, e_fname varchar(25) not null, e_lnamevarchar(30) not null, e_borndate not null, e_sexchar(1) check (e_sex in ('ж','м')), e_paspchar(10) not null unique, e_datedate not null, e_givenvarchar(50) not null, e_innchar(12) not null unique, e_penschar(14) not null unique, e_departvarchar(12) references departs, e_postvarchar(30) references posts, e_roomnumeric(4) not null, e_phonevarchar(20) not null, e_loginvarchar(30), foreign key(e_room,e_phone) references rooms (r_room, r_phone));

Физическое проектирование РБД Создание отношений. Отношение Grades (виды образования): create table grades ( g_type varchar(20) primary key); Отношение Edu (образование): create table edu ( u_id numeric(4) references employees, u_type varchar(20) not null references grades, u_spec varchar(40), u_diplom varchar(15), u_year number(4) not null, check(u_spec in ('начальное', 'среднее', 'высшее', 'средне-специальное'))); Отношение AdrTel (адреса-телефоны): create table adrtel ( a_id numeric(4) references employees, a_adr varchar(50), a_phone varchar(30));

Физическое проектирование РБД Создание отношений : Отношение Clients (заказчики): create table clients (c_id numeric(4) primary key, c_company varchar(40) not null, c_adr varchar(50) not null, c_person varchar(50) not null, c_phone varchar(30)); Отношение Projects (проекты): create table projects ( p_id numeric(6) not null unique, p_title varchar(100) not null, p_abbr char(10) primary key, p_departvarchar(12) references departs, p_company numeric(4) references clients, p_chief numeric(4) references employees, p_begin date not null, p_end date not null, p_finish date, p_cost numeric(10) not null check(p_cost>0), check (p_end>p_begin), check (p_finish is null or p_finish>p_begin));

Физическое проектирование РБД Создание отношений. Отношение Stages (этапы проектов): create table stages ( s_pro char(10) references projects, s_num numeric(2) not null, s_title varchar(200) not null, s_begin date not null, s_end date not null, s_finish date, s_cost numeric(10) not null, s_sum numeric(10) not null, s_form varchar(100) not null, check (s_cost>0), check (s_end>s_begin), check (s_finish is null or s_finish>s_begin)); Отношение Job (участие): create table job ( j_pro char(10) references projects, j_emp numeric(2) references employees, j_role varchar(20) not null, j_bonus numeric(2) not null, check(j_bonus>0), check (j_role in ('исполнитель', 'консультант')));

Физическое проектирование РБД Создание представлений (готовых запросов): Список всех текущих проектов: create view curr_projects as select* fromprojects wheresysdate between p_begin and p_end; Определение суммы по текущим проектам, полученной на текущую дату: create or replace view summ (title, cost, total) as selectp_title, p_cost, sum(s_sum) fromcurr_projects, stages wherep_abbr=s_pro group by p_title, p_cost;

Физическое проектирование РБД Создание представлений (готовых запросов): Список сотрудников, участвующих в текущих проектах: create view participants (project, name, role) as selectp_abbr, e_fname||' '||e_lname, 'руководитель' fromcurr_projects, employees wherep_chief=e_id union all selectp_abbr, e_fname||' '||e_lname, j_role fromcurr_projects, employees, job wherep_abbr=j_pro and e_id=j_emp order by 1, 3 desc; Список рабочих телефонов сотрудников: create or replace view worktel (name, room, phone) as selecte_fname||' '||e_lname, e_room, e_phone fromemployees order by 1;

Физическое проектирование РБД Создание представлений (готовых запросов): Форма отчётности и сроки выполнения по текущим проектам: create or replace view reports as selects_pro, s_num, s_title, s_begin, s_end, s_form fromstages order by 1, 2; Данные о проектах для руководителя проектов: create or replace view my_projects as select* fromprojects p where exists (select * from employees e where e.e_id=p.p_chief and e.e_login=user); Данные об этапах проектов для руководителя проектов: create or replace view my_stages as selects.* fromstages s where exists (select * from employees e, projects p where e.e_id=p.p_chief and e.e_login=user and s.s_pro=p.p_abbr);

Физическое проектирование РБД Создание представлений (готовых запросов): Данные об участниках проектов для руководителя проектов: create or replace view my_staff as selectj.* fromjob j where exists (select * from employees e, projects p where e.e_id=p.p_chief and e.e_login=user and j.j_pro=p.p_abbr) WITH CHECK OPTION; Данные о других участниках проекта: create or replace view my_emps as selectje.j_pro, e.e_fname||' '||e.e_lname e_name, e_depart, e_post, e_phone, e_room fromemployees e, job je where e.e_id=je.j_emp and exists (select * from job jm, employees m where m.e_id=jm.j_emp and m.e_login=user and je.j_pro=jm.j_pro) WITH CHECK OPTION;

Физическое проектирование РБД Назначение прав доступа к представлениям: