5. Проектирование БД

1.

ПРОЕКТИРОВАНИЕ
БАЗ ДАННЫХ
1

2.

Задачи проектирования БД
Обеспечение
хранения в БД
необходимой информации;
Обеспечение
получения
данных по запросам;
Сокращение избыточности и
дублирования данных;
Обеспечение целостности БД.
2

3.

Этапы проектирования БД
Системный анализ предметной
области
Концептуальное (инфологическое)
моделирование
Логическое (даталогическое)
проектирование
Физическое проектирование
3

4.

Предметная область – это часть реального
мира, данные о которой мы хотим отразить в
базе данных.
Модель предметной области – это
формализованные знания о предметной
области, выраженные при помощи каких-либо
средств.
4

5.

Изучение предметной области
складывается из:
• наблюдения протекающих в
ней процессов
• изучения документов,
циркулирующих в системе
• интервьюирования
участников этих процессов
5

6.

Язык моделирования – набор графических
нотаций, которые используются для описания
моделей в процессе проектирования.
6

7.

Концептуальное проектирование БД –
это процесс создания высокоуровневой
семантической модели для данных, которые
присутствуют в определенной предметной
области.
Важно, что такая модель никак не зависит
от любых аспектов ее физической реализации:
СУБД
прикладных программ
языков программирования
аппаратной платформы и др.
Строится
на
основе
требований7
пользователей.

8.

Пример
Описание предметной области:
В интернет-магазине продаются книги. Каждая
книга имеет название, написана одним автором,
относится к одному жанру, имеет определенную цену.
В магазине в наличии есть несколько экземпляров
каждой книги.
Покупатель регистрируется на сайте интернетмагазина, задает свое имя и фамилию, электронную
почту и город проживания. Он может сформировать
один или несколько заказов, для каждого заказа
написать какие-то пожелания. Каждый заказ
включает одну или несколько книг, каждую книгу
можно заказать в нескольких экземплярах.
8

9.

Проектирование концептуальной модели базы
данных
Шаг 1. Детально проанализировать предметную область
и выделить те информационные объекты, которые будут
храниться в базе данных
В интернет-магазине продаются книги. Каждая
книга имеет название, написана одним автором,
относится к одному жанру, имеет определенную цену. В
магазине в наличии есть несколько экземпляров
каждой книги.
Покупатель регистрируется на сайте интернетмагазина, задает свое имя и фамилию, электронную
почту и город проживания. Он может сформировать
один или несколько заказов, для каждого заказа
написать какие-то пожелания. Каждый заказ включает
одну или несколько книг, каждую книгу можно
9
заказать в нескольких экземплярах. Для каждого
города известно среднее время доставки книг.

10.

Проектирование концептуальной модели базы
данных
Шаг 1. Детально проанализировать предметную область
и выделить те информационные объекты, которые будут
храниться в базе данных
В интернет-магазине продаются книги. Каждая
книга имеет название, написана одним автором,
относится к одному жанру, имеет определенную цену. В
магазине в наличии есть несколько экземпляров
каждой книги.
Покупатель регистрируется на сайте интернетмагазина, задает свое имя и фамилию, электронную
почту и город проживания. Он может сформировать
один или несколько заказов, для каждого заказа
написать какие-то пожелания. Каждый заказ включает
одну или несколько книг, каждую книгу можно
10
заказать в нескольких экземплярах. Для каждого
города известно среднее время доставки книг.

11.

Проектирование концептуальной модели базы
данных
Шаг 1. Детально проанализировать предметную область
и выделить те информационные объекты, которые будут
храниться в базе данных
В интернет-магазине продаются книги. Каждая
книга имеет название, написана одним автором,
относится к одному жанру, имеет определенную цену. В
магазине в наличии есть несколько экземпляров
каждой книги.
Покупатель регистрируется на сайте интернетмагазина, задает свое имя и фамилию, электронную
почту и город проживания. Он может сформировать
один или несколько заказов, для каждого заказа
написать какие-то пожелания. Каждый заказ включает
одну или несколько книг, каждую книгу можно
11
заказать в нескольких экземплярах. Для каждого
города известно среднее время доставки книг.

12.

Проектирование концептуальной модели базы
данных
Шаг 1. Детально проанализировать предметную область
и выделить те информационные объекты, которые будут
храниться в базе данных
В интернет-магазине продаются книги. Каждая
книга имеет название, написана одним автором,
относится к одному жанру, имеет определенную цену.
В магазине в наличии есть несколько экземпляров
каждой книги.
Покупатель регистрируется на сайте интернетмагазина, задает свое имя и фамилию, электронную
почту и город проживания. Он может сформировать
один или несколько заказов, для каждого заказа
написать какие-то пожелания. Каждый заказ включает
одну или несколько книг, каждую книгу можно
12
заказать в нескольких экземплярах. Для каждого
города известно среднее время доставки книг.

13.

Проектирование концептуальной модели базы
данных
Шаг 1. Детально проанализировать предметную область
и выделить те информационные объекты, которые будут
храниться в базе данных
В интернет-магазине продаются книги. Каждая
книга имеет название, написана одним автором,
относится к одному жанру, имеет определенную цену.
В магазине в наличии есть несколько экземпляров
каждой книги.
Покупатель регистрируется на сайте интернетмагазина, задает свое имя и фамилию, электронную
почту и город проживания. Он может сформировать
один или несколько заказов, для каждого заказа
написать какие-то пожелания. Каждый заказ включает
одну или несколько книг, каждую книгу можно
13
заказать в нескольких экземплярах. Для каждого
города известно среднее время доставки книг.

14.

Проектирование концептуальной модели базы
данных
Шаг 1. Детально проанализировать предметную область
и выделить те информационные объекты, которые будут
храниться в базе данных
В интернет-магазине продаются книги. Каждая
книга имеет название, написана одним автором,
относится к одному жанру, имеет определенную цену.
В магазине в наличии есть несколько экземпляров
каждой книги.
Покупатель регистрируется на сайте интернетмагазина, задает свое имя и фамилию, электронную
почту и город проживания. Он может сформировать
один или несколько заказов, для каждого заказа
написать какие-то пожелания. Каждый заказ включает
одну или несколько книг, каждую книгу можно
14
заказать в нескольких экземплярах. Для каждого
города известно среднее время доставки книг.

15.

Проектирование концептуальной модели базы
данных
Шаг 1. Детально проанализировать предметную область
и выделить те информационные объекты, которые будут
храниться в базе данных
В интернет-магазине продаются книги. Каждая
книга имеет название, написана одним автором,
относится к одному жанру, имеет определенную цену.
В магазине в наличии есть несколько экземпляров
каждой книги.
Покупатель регистрируется на сайте интернетмагазина, задает свое имя и фамилию, электронную
почту и город проживания. Он может сформировать
один или несколько заказов, для каждого заказа
написать какие-то пожелания. Каждый заказ включает
одну или несколько книг, каждую книгу можно
15
заказать в нескольких экземплярах. Для каждого
города известно среднее время доставки книг.

16.

Проектирование концептуальной модели базы
данных
Шаг 2. Для каждого выделенного информационного
объекта указать его характеристики, для этого:
а) сначала выделить их в описании предметной области;
В интернет-магазине продаются книги. Каждая книга
имеет название, написана одним автором, относится к
одному жанру, имеет определенную цену. В магазине в
наличии есть несколько экземпляров каждой книги.
Покупатель регистрируется на сайте интернетмагазина, задает свое имя и фамилию, электронную
почту и город проживания. Он может сформировать один
или несколько заказов, для каждого заказа написать
какие-то пожелания. Каждый заказ включает одну или
несколько книг, каждую книгу можно заказать в
16
нескольких экземплярах. Для каждого города известно
среднее время доставки книг.

17.

Проектирование концептуальной модели базы
данных
б) затем связать их с информационным объектом:
Книга – название, количество, цена;
Автор – фамилия и инициалы;
Жанр – название;
Покупатель (клиент) – фамилия и имя, электронная
почта;
Город – название, среднее время доставки;
Заказ – код заказа, книги в заказе, количество книг в
заказе, пожелания.
17

18.

Проектирование концептуальной модели базы
данных
Шаг 3. Нарисовать схему, на которой изобразить
информационные объекты в виде прямоугольников:
Жанр
Автор
Книга
Город
Клиент
Заказ
18

19.

Проектирование концептуальной модели базы
данных
Шаг 4. Установить связи между информационными
объектами. Связь «один ко многим» обозначить в виде →,
«многие ко многим» – ↔.
Жанр
Автор
Книга
Город
Клиент
Заказ
19

20.

Проектирование концептуальной модели базы
данных
Жанр
Автор
Книга
Город
Клиент
Заказ
Каждая книга написана одним автором,
каждый автор написал несколько книг,
следовательно между этими таблицами связь
«один ко многим»;
20

21.

Проектирование концептуальной модели базы
данных
Жанр
Автор
Книга
Город
Клиент
Заказ
Каждая книга может включаться в
несколько заказов, один заказ может содержать
несколько книг, между этими таблицами связь
«многие ко многим»;
21

22.

Проектирование концептуальной модели базы
данных
Жанр
Автор
Книга
Город
Клиент
Заказ
Каждый клиент может сформировать несколько
заказов, каждый заказ формируется только
одним клиентом и т.д.
22

23.

Логическое проектирование БД – это
процесс создания информационной модели
на основе выбранной модели структурной
организации данных при их хранении и
обработке.
Логическое проектирование БД –
процесс без выбора конкретной СУБД и без
учета остальных аспектов физической
реализации БД.
Логическая модель данных создается
путем
преобразования
концептуальной
модели с учетом особенностей выбранной 23
модели организации данных.

24.

Пример логической модели
24

25.

Связь «один ко многим»
Связь «один ко многим»
имеет место, когда одной
записи главной таблицы
соответствует
несколько
записей связанной таблицы,
а каждой записи связанной
таблицы
соответствует
только одна запись главной
таблицы.
Обозначается это так:
25

26.

Этапы реализации связи «один ко многим»
на этапе логического проектирования
Пример:
Один автор может
написать несколько книг, а
одна книга написана только
одним автором. Для каждой
книги известны ее
количество и цена.
1 этап.
Создать таблицу author, в
которую включить
уникальных авторов книг,
хранящихся на складе и
таблицу book :
26

27.

Этапы реализации связи «один ко
многим» на этапе логического
проектирования
2 этап.
Обе таблицы должны
содержать
первичный
ключ, в таблице book он
уже
есть,
в
таблицу author добавим
ключ author_id:
27

28.

Этапы реализации связи «один ко
многим» на этапе логического
проектирования
3 этап.
Включить
в
таблицу book связанный
столбец
(внешний
ключ, FOREIGN KEY),
соответствующий
по
имени и типу ключевому
столбцу главной таблицы
(в нашем случае это
столбец author_id).
28

29.

Связь «многие ко многим»
Связь «многие ко многим» имеет место
когда каждой записи одной таблицы
соответствует несколько записей во второй, и
наоборот, каждой записи второй таблицы
соответствует несколько записей в первой.
Обозначается это так:
Например, книга «12 стульев» написана двумя
авторами Ильфом И.А. и Петровым Е.П. С другой
стороны, эти авторы написали и другие книги,
например «Золотой теленок».
29

30.

Этапы реализации связи «многие
ко многим» на следующем примере:
Один автор может написать несколько
книг, а одна книга может быть написана
несколькими авторами. Для каждой книги
известны ее количество и цена.
1. Создать
таблицу author, в
которую включить
уникальных авторов
книг, хранящихся на
складе и таблицу book :
30

31.

Этапы реализации связи «многие
ко многим»:
2. В обеих таблицах необходимо
определить первичный ключ:
31

32.

Этапы реализации связи «многие ко
многим»:
3. Создать новую таблицу-связку, состоящую из
двух столбцов, соответствующих по имени и типу
ключевым столбцам исходных таблиц. Каждый из этих
столбцов является внешним ключом (FOREIGN
KEY)
и связан с ключевым столбцом каждой
таблицы.
32

33.

Этапы реализации связи «многие
ко многим»:
4. Необходимо определиться с первичным ключом
таблицы-связки:
1 способ: Сделать два ключевых столбца, тогда
все записи в этой таблице должны быть уникальными,
то есть не повторяться.
2 способ: Но в некоторых случаях записи в таблицесвязке могут повторяться, например, если мы будем
продавать книги покупателям (один человек может
купить несколько книг, а одну и ту же книгу могут
купить несколько человек). Тогда в таблицу-связку
нужно включить дополнительные столбцы для
идентификации
записей,
например,
дату
33
продажи, также добавляют первичный ключ.

34.

Этапы реализации связи «многие
ко многим»:
Используем 2 способ:
34

35.

Физическое проектирование БД – это
описание способа реализации логической
модели БД.
На этапе Концептуального и Логического
проектирования определяется «Что делать?»,
на этапе Физического проектирования –
«Как делать?».
Физическое проектирование
выполняется для конкретной СУБД:
(MySQL, Oracle, MS SQL Server,
PostgreSQL, DB2)
35

36.

CASE средства
(Computer - Aided
Software
Engineering) – это
инструмент,
который позволяет
автоматизировать
процесс
разработки
информационной
системы
и
программного
обеспечения.
36

37.

Типовые возможности
проектирования БД
CASE-средств
Все
программные
продукты
данной
категории можно разделить на несколько
подкатегорий:
средства автоматизации, входящие в
состав конкретной СУБД (обычно
работают в режиме «клиент-сервер»);
автономные программные продукты;
веб-сервисы,
доступ
к
которым
осуществляется посредством браузера.

38.

Типовые возможности
проектирования БД
CASE-средств
В составе
СУБД
Oracle SQL Developer Data
Modeler
MySQL WorkBench
Автономные
ERWin DataModeler
MS Visio
Вебсервисы
ERDplus
Vertabelo
Creately

39.

MySQL Workbench
— инструмент для визуального
проектирования баз данных,
интегрирующий проектирование,
моделирование, создание и
эксплуатацию БД для системы баз
данных MySQL,
— бесплатная десктопная программа
для работы с MySQL.
39

40.

Создание ER - диаграммы
(диаграммы "сущность-связь")
40

41.

Создание ER - диаграммы
ER-диаграмма
представляет
собой
физическую модель взаимосвязей между
объектами. Она позволяет визуализировать то,
как объекты связаны друг с другом, как
работают эти связи и какие аспекты этих
связей могут быть улучшены.
ER-диаграммы помогают разбить сложные
системы и базы данных на более простые
элементы. Они позволяют увидеть, как
соединяются
и
перекрываются
взаимосвязанные объекты, чтобы лучше
41
понять функционирование системы.

42.

Создание ER - диаграммы
File → New Model (Ctrl + N)
42

43.

Создание ER - диаграммы
43