![]() КАТЕГОРИИ:
АстрономияБиологияГеографияДругие языкиДругоеИнформатикаИсторияКультураЛитератураЛогикаМатематикаМедицинаМеханикаОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРиторикаСоциологияСпортСтроительствоТехнологияФизикаФилософияФинансыХимияЧерчениеЭкологияЭкономикаЭлектроника
|
Реализация проекта базы данныхМы условились не привязываться к конкретной СУБД и выполнять описание логической схемы БД на 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); Разработка стратегии резервного копирования Интенсивность обновления разработанной базы данных низкая, поэтому для обеспечения сохранности вполне достаточно проводить полное резервное копирование БД раз в день (перед окончанием рабочего дня). Для разработанной БД нет необходимости держать сервер включенным круглосуточно, поэтому можно создать соответствующее задание операционной системы, которое будет автоматически запускаться перед выключением сервера.
|