Реализация проекта базы данных

Мы условились не привязываться к конкретной СУБД и выполнять описание логической схемы БД на SQL-92. Приведём описание схемы БД на DDL.

Создание таблиц

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>=4500));

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 date not null,

e_sex char(1) check(e_sex in ('ж','м')), e_pasp char(10) not null unique, e_date date not 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).

5. Отношение 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 number(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 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));

10. Отношение 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 ('исполнитель', 'консультант')));

Создание представлений (готовых запросов)

Приведём примеры нескольких готовых запросов (представлений):

1. Список всех текущих проектов (sysdate – функция, возвращающая текущую дату, определена в СУБД Oracle; в других системах аналогичная функция может называться по-другому, например, getdate() в Transact-SQL, now() в MS Access, currdate() в MySQL и т.д.):

create view curr_projects as select *

from projects

where p_begin<=sysdate and sysdate<=p_end;

2. Определение суммы по текущим проектам, полученной на текущую дату:

create or replace view summ(title, cost, total) as select p_title, p_cost, sum(s_sum)

from curr_projects, stages

where p_abbr=s_pro group by p_title, p_cost;

3. Список сотрудников, участвующих в текущих проектах:

create view participants (project, name, role) as select p_abbr, e_fname||' '||e_lname, 'руководитель'

from curr_projects, employees

where p_chief=e_id

union all

select p_abbr, e_fname||' '||e_lname, j_role

from curr_projects, employees, job where p_abbr=j_pro and e_id=j_emp

order by 1, 3 desc;

4. Список рабочих телефонов сотрудников:

create or replace view worktel (name, room, phone) as select e_fname||' '||e_lname, e_room, e_phone

from employees order by 1;

5. Форма отчётности и сроки выполнения этапов по текущим проектам:

create or replace view reports as select s_pro, s_num, s_title, s_begin, s_end, s_form

from stages order by 1, 2;

6. Данные о проектах для руководителя проектов:

create or replace view my_projects as select *

from projects p

where exists (select * from employees e where e.e_id=p.p_chief and e.e_login=user);

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

7. Данные об этапах проектов для руководителя проектов:

create or replace view my_stages as select s.*

from stages 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);

8. Данные об участниках проектов для руководителя проектов:

create or replace view my_staff as select j.*

from job 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);

9. Данные о других участниках проекта:

create or replace view my_emps as select je.j_pro, e.e_fname||' '||e.e_lname e_name, e_depart, e_post, e_phone, e_room

from employees 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);

Для того чтобы можно было работать с этими представлениями, соответствующим пользователям нужно назначить права доступа к представлениям. Эти права перечислены в табл. 17.

Таблица 17. Права доступа к представлениям

Назначение прав доступа

Права доступа пользователей предоставляются с помощью команды 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 to staff; Если сотрудник не является руководителем проекта, он не получит данных через этот запрос и не сможет воспользоваться правами доступа к нему.

Права доступа участников проекта (сотрудников, staff) к представлению my_emps могут быть описаны следующим образом:

grant select on my_emps to staff;

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

Создание индексов

Анализ готовых запросов показывает, что для повышения эффективности работы с данными необходимо создать индексы для всех внешних ключей. Приведём примеры создания индексов:

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);

Разработка стратегии резервного копирования

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