Пример физического проектирования БД

1. Курс «Базы данных» Тема: Пример физического проектирование БД.

Барабанщиков
Игорь Витальевич
1

2. План лекции

Соглашение об именовании объектов БД
Необходимость денормализации.
Выбор структур хранения для таблиц.
Выбор индексов.

3. Даталогическая модель «Продукты питания»

Окончательный вариант реляционной модели
(Схемы БД)
3

4. Стандарт именования объектов БД

• При именовании объектов БД необходимо
использовать какой-либо стандарт.
• Имена объектов БД должны быть
информативными.
• Возможный вариант именования объектов
БД: Префикс_ТипОбъекта_ИмяОбъектаБД
• Пример: Продажа продуктов питания – Food
Product Sale (FPS): FPS_TS_CITY

5. Сокращения для объектов БД

Сокращение
Тип объекта БД
TS
Таблица справочная
TT
Таблица транзакционная
TI
Таблица итоговая
IU
Индекс уникальный
IN
Индекс обычный (неуникальный)
SQ
Последовательность
VW
Обзор (представление)

6. Таблица «Города»

• Индекс-таблица
create table FPS_TS_CITY
(
CITY_ID
NUMBER(6)
not null,
CITY_NAME VARCHAR2(30) not null,
constraint FPS_IU_CITY_PK
primary key (CITY_ID)
)
organization index;

7. Таблица «Поставщики»

• Обычная таблица (куча)
create table FPS_TS_PROVIDER
(
PROVIDER_ID
NUMBER(6)
not null,
PROVIDER_NAME VARCHAR2(30) not null,
CITY_ID
NUMBER(6)
not null,
ADDRESS
VARCHAR2(100) not null,
BOSS_NAME
VARCHAR2(50),
PHONE
VARCHAR2(10) not null,
FAX
VARCHAR2(10)
);

8. Таблица «Поставщики» - Ограничения

• Первичный ключ
alter table FPS_TS_PROVIDER
add constraint FPS_IU_PROVIDER_PK
primary key (PROVIDER_ID);
• Внешний ключ
alter table FPS_TS_PROVIDER
add constraint FPS_IN_PROVIDER_FK1
foreign key (CITY_ID)
references FPS_TS_CITY (CITY_ID);

9. Таблица «Поставщики» - Индексы

• Индекс для внешнего ключа
create index FPS_IN_PROVIDER_FK1
on FPS_TS_PROVIDER (CITY_ID);
• Индекс для поиска по имени поставщика
create index FPS_IN_PROVIDER_1
on FPS_TS_PROVIDER (PROVIDER_NAME);

10. Таблица «Продукты»

• Обычная таблица (куча)
create table FPS_TS_PRODUCT
(
PRODUCT_ID
NUMBER(6)
not null,
PRODUCT_NAME VARCHAR2(50) not null,
UNIT
VARCHAR2(10) not null,
STORAGE_TIME NUMBER(3)
not null,
CONDITION
VARCHAR2(100)
);

11. Таблица «Продукты» - Ограничения

• Первичный ключ
alter table FPS_TS_PRODUCT
add constraint FPS_IU_PRODUCT_P
primary key (PRODUCT_ID);
• Ограничение CHECK (бизнес-правило)
alter table FPS_TS_PRODUCT
add constraint fps_ch_product_1
check (storage_time < 250);

12. Таблица «Продукты» - Индексы

• Уникальный индекс
create unique index FPS_IU_PRODUCT_1
on FPS_TS_PRODUCT (PRODUCT_NAME);
• Составной индекс
create index FPS_IN_PRODUCT_1 on
FPS_TS_PRODUCT (UNIT, STORAGE_TIME);

13. Таблица «Продажи»

• Обычная таблица (куча)
create table FPS_TT_SALE
(
SALE_DATE
DATE
PRODUCT_ID NUMBER(6)
QUANTITY
NUMBER(3)
PRICE
NUMBER(9,2)
);
not null,
not null,
not null,
not null

14. Таблица «Продажи» - Ограничения

• Первичный ключ (составной)
alter table FPS_TT_SALE
add constraint FPS_IU_SALE_PK
primary key (PRODUCT_ID, SALE_DATE);
• Внешний ключ
alter table FPS_TT_SALE
add constraint FPS_IN_SALE_FK1
foreign key (PRODUCT_ID);

15. Таблица «Заказы»

• Обычная таблица (куча)
create table FPS_TT_ORDER
(
ORDER_DATE DATE
PROVIDER_ID NUMBER(6)
PRODUCT_ID NUMBER(6)
QUANTITY
NUMBER(6)
);
not null,
not null,
not null,
not null

16. Таблица «Заказы» - Ограничения

• Первичный ключ (составной)
alter table FPS_TT_ORDER
add constraint FPS_IU_ORDER_3
primary key (ORDER_DATE, PRODUCT_ID, PROVIDER_ID);
• Внешний ключ 1
alter table FPS_TT_ORDER
add constraint FPS_IN_ORDER_1
foreign key (PROVIDER_ID);
• Внешний ключ 2
alter table FPS_TT_ORDER
add constraint FPS_IN_ORDER_2
foreign key (PRODUCT_ID);

17. Таблица «Заказы» - Индексы

• Уникальный индекс будет автоматически
создан для первичного ключа.
• Индекс для внешнего ключа 1
create index FPS_IN_ORDER_FK1
on FPS_TT_ORDER (PROVIDER_ID);
• Индекс для внешнего ключа 2
create index FPS_IN_ORDER_FK2
on FPS_TT_ORDER (PRODUCT_ID);

18. Таблица «Поставки»

• Обычная таблица (куча)
create table FPS_TT_SUPPLY
(
SUPPLY_DATE DATE
PROVIDER_ID NUMBER(6)
PRODUCT_ID NUMBER(6)
QUANTITY
NUMBER(6)
PRICE
NUMBER(9,2)
CREATE_DATE DATE
);
not null,
not null,
not null,
not null,
not null,
not null

19. Таблица «Поставки» - Ограничения

• Первичный ключ (составной)
alter table FPS_TT_SUPPLY
add constraint FPS_IU_SUPPLY_PK
primary key (SUPPLY_DATE, PRODUCT_ID, PROVIDER_ID);
• Внешний ключ 1
alter table FPS_TT_SUPPLY
add constraint FPS_IN_SUPPLY_1 foreign key (PRODUCT_ID);
• Внешний ключ 2
alter table FPS_TT_SUPPLY
add constraint FPS_IN_SUPPLY_2
foreign key (PROVIDER_ID);

20. Таблица «Поставки» - Индексы

• Уникальный индекс будет автоматически
создан для первичного ключа.
• Индекс для внешнего ключа 1
create index FPS_IN_SUPPLY_FK1 on
FPS_TT_SUPPLY (PRODUCT_ID);
• Индекс для внешнего ключа 2
create index FPS_IN_SUPPLY_FK2 on
FPS_TT_SUPPLY (PROVIDER_ID);

21. Последовательности

• Последовательность для таблицы «Города»
create sequence FPS_SQ_CITY
start with 1 increment by 1 nocache;
• Последовательность для таблицы «Поставщики»
create sequence FPS_SQ_PROVIDER
start with 1 increment by 1 nocache;
• Последовательность для таблицы «Продукты»
create sequence FPS_SQ_PRODUCT
start with 1 increment by 1 nocache;

22. Денормализация таблицы «Продукты»

Проблема: Для определения возможности продажи любого продукта
надо проводить дополнительные вычисления:
• Вычислять общее количество поставленных и проданных продуктов
• Вычислять ОСТАТОК = ПОСТАВЛЕНО - ПРОДАНО
22

23. Денормализация таблицы «Продукты»

• Для того, чтобы упростить логику работы с
этой БД надо в таблицу «Продукты» добавить
поле «Количество»:
alter table FPS_TS_PRODUCT add
QUANTITY number(6) default 0 not null;
• Это пример восходящей денормализации.
• Для поддержания согласованности данных
надо использовать серверную логику:
- увеличивать значение при поставке продукта;
- уменьшать значение при продаже продукта;

24. Итоги

• Выполнено физическое проектирование БД
«Продукты питания».
• Подготовлен SQL-скрипт создания объектов
БД для СУБД Oracle.
• Следующий этап – реализация сложных
правил бизнес-логики с помощью
хранимых процедур.