Н.В. Усманова - Проектирование баз данных (Методические указания по выполнению курсовой работы), страница 7

Схема отношения ЗАКАЗЧИКИ (Clients)Содержание поляНомер заказчикаИмя поляC_IDЗаказчикАдрес заказчикаКонтактное лицоТелефонC_COMPANYC_ADRC_PERSONC_PHONEТип, длинаN(4)V(40)V(50)V(50)V(30)Примечаниясуррогатныйпервичный ключобязательное полеобязательное полеобязательное полеТаблица 12. Схема отношения ПРОЕКТЫ (Projects)Содержание поляНомер проектаИмя поляP_IDТип, длинаN(6)Название проектаСокращённое названиеОтделP_TITLEP_ABBRP_DEPARTV(100)С(10)V(12)ЗаказчикРуководительP_COMPANYP_CHIEFДата начала проектаДата окончания проектаP_BEGINP_ENDDDРеальная дата окончанияP_FINISHDСтоимость проектаP_COSTN(4)N(4)N(10)Примечанияобязательноеуникальное полеобязательное полепервичный ключвнешнийключ(кDeparts)внешний ключ (к Clients)внешнийключ(кEmployees)обязательное полеобязательноеполе,больше даты началапроектабольше даты началапроектаобязательное поле, > 0Таблица 13. Схема отношения ЭТАПЫ ПРОЕКТА (Stages)Содержание поляПроектИмя поляS_PROНомер этапаНазвание этапаДата начала этапаS_NUMS_TITLES_BEGINТип, длинаC(10)N(2)V(200)D31Примечаниявнешнийсоставнойключ(к первичныйProjects)ключобязательное полеобязательное полеДата окончания этапаS_ENDРеальная дата окончанияСтоимость этапаПолученная сумма поэтапуФорма отчётностиS_FINISHS_COSTS_SUMDN(10)N(10)S_FORMV(100)Dобязательное поле, большедаты началабольше даты начала этапаобязательное полеобязательноеполе,значение по умолчанию – 0обязательное полеТаблица 14.

Схема отношения УЧАСТИЕ (Job)Содержание поляИмя поляПроектJ_PROТип,длинаC(10)СотрудникJ_EMPN(4)Примечания*внешний ключ (к составнойProjects)первичныйвнешний ключ (к ключEmployees)обязательное полеРольJ_ROLEV(20)ДоплатаJ_BONUSN(2)Схема базы данных после нормализации приведена на рис. 6.Рис. 6. Окончательная схема БД проектной организации2.4. Определение дополнительных ограничений целостностиПеречислим ограничения целостности, которые не указаны в табл.

1–10.1. Атрибут Вид образования может принимать одно из следующих значений:'начальное', 'среднее', 'средне-специальное', 'высшее'.2. Атрибут Роль может принимать одно из двух значений: 'исполнитель' или'консультант'.323. В поле Доплата хранится величина доплаты сотруднику за участие в проекте (впроцентах к его окладу). Значение поля больше либо равно 0.4. Нумерация в поле Номер этапа начинается с 1 и является непрерывной длякаждого проекта.5. Дата начала первого этапа проекта должна соответствовать началу проекта вцелом, дата завершения последнего этапа должна соответствовать завершениюпроекта в целом.

Этапы не должны пересекаться по времени и между ними не должнобыть разрывов.6. Стоимость проекта должна быть равна сумме стоимостей всех этапов этогопроекта. Ограничения 4-6 нельзя реализовать в схеме отношения. В реальных БДподобные ограничения целостности реализуются вручную или программно (черезвнешнее приложение или специальную процедуру контроля данных – триггер).Примечание. Вопросы архивирования данных в этом пособии подробно не рассматриваются. Носледует отметить, что обычно архив является частью БД и представляет собой набор отдельныхтаблиц, которые не связаны с оперативной частью БД внешними ключами.

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

Описание групп пользователей и прав доступаОпишем для каждой группы пользователей права доступа к каждой таблице. Правадоступа должны быть распределены так, чтобы для каждого объекта БД был хотя быодин пользователь, который имеет право добавлять и удалять данные из объекта.Права приведены в табл. 11. Используются следующие сокращения:s – чтение данных (select);i – добавление данных (insert);u – модификация данных (update);d – удаление данных(delete).Таблица 15.

Права доступа к таблицам для групп пользователейТаблицыОтделыКомнатыДолжностиСотрудникиАдресателефоныОбразованиеЗаказчикиПроектыЭтапыпроектовУчастиеРуководителиорганизацииSSSIUDSSSГруппы пользователей (роли)Сотрудник Руководител Бухгалтери отд.и проектовыкадровSIUDSSSUIDSSSSUIDSSSUIDSSSUIDSSIUDSIUDSIUDSSSUISS33SSУчастникипроектовSПрава на изменение данных в таблице УЧАСТИЕ будут назначены черезпредставление, т.к. изменять данные этой таблицы может только руководительпроекта.Права назначает администратор БД (или администратор безопасности, еслисистема сложная и администраторов несколько).Приведём описание схемы БД на DDL.ГЛАВА 3.

ФИЗИЧЕСКОЕ ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННОЙ БАЗЫДАННЫХ3.1. Создание таблиц1. Отношение Departs (отделы):create table departs (d_id varchar(12) primary key,d_name varchar(100) not null);2. Отношение Rooms (комнаты):create table rooms (d_depart varchar(12)references departs(d_id),r_room numeric(4) not null,r_phone varchar(20),unique(r_room, r_phone));3. Отношение Posts (должности):create table posts (p_post varchar(30) primary key,p_salary numeric(8,2) not null check(p_salary>=12130));4. Отношение Employees (сотрудники):create table employees (e_id numeric(4) primary key,e_fname varchar(25) not null,e_lname varchar(30) not null,e_born datenot null,e_sex char(1) check(e_sex in ('ж','м')),e_pasp char(10)not null unique,e_date datenot null,e_given varchar(50) not null,e_inn char(12)not null unique,e_pens char(14)not null unique,e_depart varchar(12) references departs,e_post varchar(30) references posts,e_room numeric(4) not null,e_phone varchar(20) not null,e_login varchar(30),foreign key(e_room,e_phone)references rooms(r_room,r_phone));(Если внешний ключ ссылается на первичный ключ отношения, его можно неуказывать, как в случае ссылок на Departs и Posts).345.

Отношение Edu (образование):create table edu (u_id numeric(4) references employees,u_type varchar(20) not null,u_spec varchar(40),u_diplom varchar(15),u_year foat(4) not null,check(u_spec in ('начальное', 'среднее', 'высшее', 'среднеспециальное')));6. Отношение AdrTel (адреса-телефоны):create table adrtel (a_id numeric(4) references employees,a_adr varchar(50),a_phone varchar(30));7. Отношение 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));8.

Отношение Projects (проекты):create table projects (p_id numeric(6) not null unique,p_title varchar(100) not null,p_abbr char(10)primary key,p_depart varchar(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));9. Отношение Stages (этапы проектов):create table stages (s_pro char(10)references projects,s_num numeric(2) not null,s_title varchar(200) not null,s_begin datenot null,s_end datenot null,s_finish date,s_cost numeric(10) not null,s_sum numeric(10) not null,s_form varchar(100) not null,35check (s_cost>0),check (s_end>s_begin),check (s_finish is null or s_finish>s_begin));10.

Отношение Job (участие):create table job (j_pro char(10)references projects,j_emp numeric(4) references employees,j_role varchar(20) not null,j_bonus numeric(2) not null,check(j_bonus>0),check (j_role in ('исполнитель', 'консультант')));3.2. Создание представлений (готовых запросов)Приведём примеры нескольких готовых запросов (представлений):1. Список всех текущих проектов (getdate – функция, возвращающая текущуюдату, определена в СУБД MySQL; в других системах аналогичная функция можетназываться по-другому, например, now() в MS Access, sysdate в Oracle ит.д.):create view curr_projects asselect *from projectswhere p_begin<=sysdate and sysdate<=p_end;2.Определение суммы по текущим проектам, полученной на текущую дату:create or alter view summ (title, cost, total) asselect p_title, p_cost, sum(s_sum)from curr_projects, stageswhere p_abbr=s_progroup by p_title, p_cost;3.

Данные о проектах для руководителя проектов:create or alter view my_projects asselect *from projects pwhere exists (select * from employees ewhere e.e_id=p.p_chief and e.e_login=user);Функция user возвращает имя пользователя, выполняющего текущий запрос. Такимобразом, каждый пользователь получит данные только о тех проектах, руководителемкоторых является. Используя аналогичный способ, можно ограничить участникапроекта данными только о сотрудниках тех проектов, в которых он сам участвует.4. Данные об участниках проектов для руководителя проектов:Create or alter view my_staff asselect j.*from job jwhere exists (select *from employees e, projects pwhere e.e_id=p.p_chief and e.e_login=userand j.j_pro=p.p_abbr);365. Данные о других участниках проекта:create or alter view my_emps asselect je.j_pro, e.e_fname, e.e_lname e_name,e_depart, e_post, e_phone, e_roomfrom employees e, job jewhere e.e_id=je.j_emp and exists (select *from job jm, employees mwhere m.e_id=jm.j_emp andm.e_login=user and je.j_pro=jm.j_pro);Для того чтобы можно было работать с этими представлениями, соответствующимпользователям нужно назначить права доступа к представлениям.

Эти праваперечислены в табл. 16.Таблица 16. Права доступа к представлениямПредставленияТекущиепроекты(curr_projects)Сумма по текущимпроектам (summ)Проектыдляруководителя(my_projects)Участники проектовдля руководителей(my_staff)Участники проектов(my_emps)Группы пользователей (роли)РуководителиРуководителиУчастники проектоворганизациипроектовSSSSSIUDSIUDS3.3. Назначение прав доступаПрава доступа пользователей предоставляются с помощью команды GRANT.Рассмотрим для примера права сотрудника компании ok_user, который являетсясотрудником отдела кадров. Права доступа к отношениям Departs и Rooms могут бытьописаны следующим образом:grant select, insert, update, delete on departs to ok_user;grant select, insert, update, delete on rooms to ok_user;Права доступа руководителей проектов (сотрудников, staff) к представлениюmy_projects могут быть описаны следующим образом:grant select, insert, update, delete on my_projects tostaff;Если сотрудник не является руководителем проекта, он не получит данных черезэтот запрос и не сможет воспользоваться правами доступа к нему.Права доступа участников проекта (сотрудников, staff) к представлениюmy_emps могут быть описаны следующим образом:grant select on my_emps to staff;37Если сотрудник не является участником проекта, он не получит данных через этотзапрос и не сможет воспользоваться правами доступа к нему.3.4.

Создание индексовАнализ готовых запросов показывает, что для повышения эффективности работыс данными необходимо создать индексы для всех внешних ключей. Приведём примерысоздания индексов:create index e_posts on employees(e_post);create index p_chief on projects(p_chief);create index e_tel on employees(e_room, e_phone);3.5. Разработка стратегии резервного копированияИнтенсивность обновления разработанной базы данных низкая, поэтому дляобеспечения сохранности вполне достаточно проводить полное резервноекопирование БД раз в день (перед окончанием рабочего дня). Для разработанной БДнет необходимости держать сервер включенным круглосуточно, поэтому можносоздать соответствующее задание операционной системы, которое будетавтоматически запускаться перед выключением сервера.38.