Физическое проектирование БД

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

Приведём фрагмент описания схемы БД на DDL:

Отношение POSTS (должности):

CREATE TABLE Posts (

p_id INTEGER PRIMARY KEY,

p_post VARCHAR(30) NOT NULL,

p_salary FLOAT NOT NULL);

Отношение ROOMS (комнаты):

CREATE TABLE Rooms (

r_no INT PRIMARY KEY,

r_tel VARCHAR (10),

UNIQUE(r_no, r_tel));

Отношение EMPLOYEES (сотрудники):

CREATE TABLE Employees (

e_id INTEGER PRIMARY KEY,

e_fname VARCHAR(20) NOT NULL,

e_lname VARCHAR(30) NOT NULL,

e_born DATE,

e_sex CHAR(1) NOT NULL,

e_post INT REFERENCES posts,

e_room INT,

e_tel VARCHAR(10),

e_inn CHAR(12) NOT NULL,

e_passp CHAR(12) NOT NULL,

e_org VARCHAR(30) NOT NULL,

e_pdate DATE NOT NULL,

e_addr VARCHAR(50),

FOREIGN KEY(e_room,e_tel) REFERENCES Rooms(r_no,r_tel));

Другие отношения описываются аналогично.

Права доступа пользователей, описанные в п. 2.4.4, предоставляются с помощью команды GRANT. Рассмотрим для примера права сотрудника компании user1, который принимает и обслуживает заказы. Права доступа к отношениям CUSTOMERS, ORDERS, ITEMS могут быть описаны следующим образом:

grant insert, update on customers to user1;

grant insert, update, delete on orders to user1;

grant insert, update, delete on items to user1;

Для реализации частичного доступа к отношению BOOKS следует создать соответствующее представление и предоставить доступ к этому представлению:

create view goods (id, title, circul, price, rest)

as select b_contract, b_title, b_circul, b_price, b_rest

from books;

grant select on goods to user1;

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

Список всех текущих проектов (книг, находящихся в печати и в продаже):

create view edits as

select b_contract, b_title, b_date

from books

where b_rest is null or b_rest > 0;

Список редакторов, работающих над книгами:

create view edits (title, fname, lname) as

select b_title, e_fname, e_lname /*ответственный редактор*/

from books, employees e

where b_edit=e_id and

(b_publ is null or b_publ > sysdate);

union /*sysdate – текущая дата*/

select b_title, a_fname, a_lname

from books, employees e, editors d

where b.b_contract=d.b_id and d.e_id=a.e_id and

(b_publ is null or b_publ > sysdate)

order by 1;

Определение общей прибыли от продаж по текущим проектам:

create view edits (title, total) as

select b_title, (circul–rest)*price–advance

from books

where b_rest is null or b_rest > 0;

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

CREATE INDEX e_posts ON Employees(e_post);

CREATE INDEX b_editors ON Books(b_edit);

CREATE UNIQUE INDEX r_tel ON Rooms(r_no,r_tel);

Пример выполнения запросов на СУБД Microsoft Access

Запустите MS Access. Создайте новую БД с наименованием library.mdb. В окне БД выберите вкладку «Запросы» и нажмите кнопку «Создать». Выберите «Конструктор», затем закрыв диалог добавления таблиц и запросов кнопкой «Закрыть» перейдите в режим SQL. В текстовом редакторе SQL-кода наберите свой запрос, выполните его и сохраните его с осмысленным названием.

Аналогично создайте и выполните остальные запросы.

Перейдите на вкладку «Таблицы» и убедитесь, что таблицы созданы. Выбрав таблицу можно посмотреть и исправить её структуру через «Конструктор», а в режиме «Таблица» можно ввести данные.

Через меню «Сервис» - «Схема данных» можно увидеть аналог ER-диаграммы