ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ Основные задачи проектирования баз данных

ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ Основные задачи проектирования баз данных (БД): - обеспечение хранения в БД всей необходимой информации; - обеспечение возможности получения данных по всем (необходимым) запросам пользователей; - сокращение избыточности и дублирования данных; - обеспечение целостности данных (правильности их содержания): исключение противоречий в содержании данных, исключение их потери и т. д.

Основными этапами проектирования БД являются три этапа: - концептуальное (инфологическое) проектирование; - логическое (даталогическое) проектирование; - физическое проектирование.

Концептуальное проектирование – это построение формализованной модели предметной области (ПО). Такая модель строится с использованием стандартных языковых средств, обычно графических, например, ER-диаграммы. На основе описания выбранной ПО определяется состав и структура данных ПО, которые должны находиться в БД и обеспечивать выполнение необходимых запросов и задач пользователя.

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

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

Инфологическое проектирование БД. На этапе инфологического проектирования в ходе сбора информации о предметной области (ПО) требуется выяснить: 1. Основные объекты ПО (объекты, о которых должна храниться информация в БД). 2. Атрибуты объектов. 3. Связи между объектами. 4. Основные запросы к БД. Инфологическое проектирование – это графический метод проектирования БД, заключающийся в построении ER-модели (модели «Сущность - Связь» ).

Основные преимущества ER-моделей: • Наглядность. • ER-модели позволяют проектировать БД с большим количеством (десятки, сотни) объектов и атрибутов. • Позволяют избежать возможных ошибок на этапе логического проектирования. • Позволяют наиболее полно оценить специфику моделируемой ПО (правильно и адекватно описать ПО). • ER-модели реализованы во многих системах автоматизированного проектирования БД (например, ER Win, S-Designer, Data. Base Designer (ORACLE)

Конструктивные элементы концептуальной модели данных Объектные множества; Отношения; Составные объектные множества; Атрибуты; Конкретизированные множества; Индикаторы мощности; Ключи.

Объектное множество (ОМ) 2 2 Дата Рождения ФИО 2 3 1 № страхования ЧЕЛОВЕК ●Смирнов 1 Объект элемент – конкретный элемент объектного множества 2 Атрибуты или свойства объекта – информация, описывающая объект 3 Атрибут, значение которого однозначно идентифицируемый объект, можно использовать в качестве ключа

Конкретизация Объектное множество, являющееся подмножеством другого объектного множества. ФИО Дата Рождения ЧЕЛОВЕК ●Смирнов Стипендия № страхования - Обозначает направление включения СТУДЕНТ ●Смирнов Каждый объект-элемент конкретизации является объектом-элементом обобщения. Конкретизированный объект наследует все атрибуты и отношения обобщенного объекта.

Отношение Установленная связь между элементами двух объектных множеств МУЖЧИНА СОСТОИ ТВ БРАКЕ ЖЕНЩИНА СЕМЕЙНАЯ ПАРА Се Отношение само по себе может являться ОТМЕЧАЮ ПРОЖИВ ЗАРАБАТ Т ДАТУ АЮТ СВАДЬБЫ объектным множеством и называется ЫВАЮТ составным объектным множеством. Составные множества ДАТА АДРЕС ДОХОД можно включать в отношения как обычные объектные множества.

Мощность Это максимальное количество элементов одного объектного множества(ОМ), связанных с одним элементом другого ОМ. 1: М М: М НАЛОГОВЫЙ ИНСПЕКТОР 1 * СТУДЕНТ КОНТРОЛ ИРУЕТ ПОСЕЩ АЕТ * ПРЕДПРИНИМАТЕЛЬ * КУРС

Примеры создания концептуальной модели данных При создании концептуальной модели используются: данные, содержащиеся в потенциальных вопросах пользователей; данные, содержащиеся в существующих отчётах и формах

Построим модель данных банка, отражающую реальность конкретного банка, которой можно воспользоваться для ответа на следующие вопросы: Сколько текущих счетов? Сколько сберегательных счетов? Сколько клиентов? У каких клиентов есть и текущие и сберегательные счета? 5. Каков процент сберегательных счетов, баланс которых не превышает 1000 долларов? 6. Какой тип клиентов имеет самый высокий средний баланс счетов? 1. 2. 3. 4.

ет име ИЙ УЩ ТЕК ЧЕТ С КЛИЕНТ и СБЕ меет РЕГ НЫ АТЕЛ ЙС Ь ЧЕТ ТЕКУЩИЙ СЧЕТ СБЕРЕГАТЕЛЬ НЫЙ СЧЕТ У клиента есть текущий счет, если он связан отношением ИМЕЕТТЕКУЩИЙ- СЧЕТ с некоторым элементом объектного множества ТЕКУЩИЙ СЧЕТ. Например: SELECT COUNT(*) FROM CLIENT;

1: 1 1: М 1 * ет име ИЙ УЩ ТЕК ЧЕТ С 1 * М: М ТЕКУЩИЙ СЧЕТ КЛИЕНТ 1 * и СБЕ меет РЕГ НЫ АТЕЛ ЙС Ь ЧЕТ СБЕРЕГАТЕЛЬНЫЙ СЧЕТ 1 *

Дата рождения КЛИЕНТ ФИЗИЧЕСКОЕ ЛИЦО Пол ЮРИДИЧЕСКОЕ ЛИЦО Число служащих Представитель Тип организации

ет име. ЩИЙ У ТЕК ЧЕТ С Дата рождения КЛИЕНТ ФИЗИЧЕСКОЕ ЛИЦО Пол ТЕКУЩИЙ СЧЕТ Баланс СБЕРЕГАТЕЛЬ НЫЙ СЧЕТ Баланс и СБЕ меет РЕ НЫ ГАТЕЛ ЙС Ь ЧЕТ ЮРИДИЧЕСКОЕ ЛИЦО Число служащи х Представител ь Тип организации

Построим модель данных на основе существующих отчетов

Счет консультационной службы Дата Номер счета Проект 09. 10. 2010 349 Система подсчета стоимости Консультант Вид деятельности Количество часов Ставка консультанта Цена Смирнов Системный анализ 30 $ 10/час 300$ Смирнов Системное проектирование 30 $ 10/час 300$ Смирнов Программирование 20 $ 10/час 200$ Ганс Программирование 60 $ 6/час 360$ Итого оплата консультантов 1160$ Другие расходы Описание Цена оплаты Материалы(бумага, ксерокопирование и т. д. ) 25$ Итого другие расходы 25$ Итого к оплате 1185$ Клиент Фирма Лайнекс, К. Маркса 2

Модель данных счета консультационной службы КОНСУЛЬТАНТ КЛИЕНТ ВИД ДЕЯТЕЛЬНОСТИ ПРОЕКТ

Модель данных счета консультационной службы Имя Ставка № Имя КОНСУЛЬТАНТ КЛИЕНТ Адрес № счета ВИД ДЕЯТЕЛЬНОСТИ ПРОЕКТ Дата Название Итого

Модель данных счета консультационной службы Имя Ставка № Имя КОНСУЛЬТАНТ КЛИЕНТ Адрес ЗАНЯТ- В № счета ВИД ДЕЯТЕЛЬНОСТИ ПРОЕКТ Дата Название Итого

Модель данных счета консультационной службы Имя Ставка № Имя КЛИЕНТ КОНСУЛЬТАНТ Адрес ЗАНЯТ- В ДЛ Я ВЫПОЛНЕН - ДЛЯ № счета ВИД ДЕЯТЕЛЬНОСТИ ПРОЕКТ Дата Название Итого

Модель данных счета консультационной службы Имя КЛИЕНТ Количество Имя Ставка № Адрес ВЫПОЛНЕН - ДЛЯ Часы КОНСУЛЬТАНТ № счета ПРОЕКТ ЗАНЯТ- В Дата ДЛ Я Название ВИД ДЕЯТЕЛЬНОСТИ Итого

Модель данных счета консультационной службы Имя КЛИЕНТ Количество Имя Ставка ВЫПОЛНЕН - ДЛЯ Часы № Адрес КОНСУЛЬТАНТ № счета ПРОЕКТ Дата ДЛ Я ЗАНЯТ- В Название ВИД ДЕЯТЕЛЬНОСТИ Описание Цена ДРУГИЕ РАСХОДЫ Итого

Модель данных счета консультационной службы Имя КЛИЕНТ Количество Имя Ставка ВЫПОЛНЕН - ДЛЯ Часы № Адрес КОНСУЛЬТАНТ № счета ПРОЕКТ Дата ДЛ Я ЗАНЯТ- В Название Итого ВИД ДЕЯТЕЛЬНОСТИ Описание Цена ДРУГИЕ РАСХОДЫ НА

Модель данных счета консультационной службы Имя КЛИЕНТ Количество Имя Часы № Ставка КОНСУЛЬТАНТ № счета * * ЗАНЯТ- В Адрес 1 ВЫПОЛНЕН - ДЛЯ * ПРОЕКТ ДЛ Я * Дата 1 Название Итого * ВИД ДЕЯТЕЛЬНОСТИ Описание Цена ДРУГИЕ РАСХОДЫ * НА

1. Каждое объектное множество с атрибутами преобразуется в реляционную таблицу с именем объектного множества. Атрибуты объектного множества становятся атрибутами реляционной таблицы. Если объектное множество имеет ключевой атрибут, он может использоваться в качестве ключа реляционной таблицы. В противном случае к таблице добавляется ключевой атрибут, значения которого будут однозначно определять объекты – элементы исходного объектного множества. В действительности проектировщик должен советоваться с пользователем по поводу выбора ключа реляционной таблицы.

2. Отношение 1: 1 и 1: М становятся внешними ключами. Отношение один – к – одному преобразуется путем помещения ключевого атрибута одного из объектных множеств в качестве атрибута (внешнего ключа) в таблицу второго объектного множества. Отношение один – ко – многим преобразуется путем включения в таблицу, описывающую объект со стороны «многим» в качестве атрибута (внешнего ключа) ключевого атрибута объекта со стороны «один» . Определить ограничения на внешний ключ. 3. Отношение многие – ко – многим преобразуется в реляционную таблицу, первичный ключ которой создаётся из столбцов, соответствующих первичным ключам двух объектных множеств, участвующих в отношении. В этой таблице будет два внешних ключа для которых надо определить ограничения.

4. Конкретизирующие объектные множества преобразуются путём создания отдельных реляционных таблиц, ключи которых совпадают с ключами обобщающих объектных множеств. 5. Рекурсивное отношение преобразовывается в атрибут с именем, соответствующим отношению. 6. Преобразование составных объектных множеств в соответствии с изложенными выше правилами.

Логическая модель данных счёта консультационной службы КЛИЕНТ 1 КОНСУЛЬТАНТ НОМЕР_КЛИЕНТА PK НОМЕР_КОНСУЛЬТАНТА PK ИМЯ_КЛИЕНТА АДРЕС ИМЯ_КОНСУЛЬТАНТА СТАВКА * 1 1 ПРОЕКТ 1 ЗЯНЯТ – ВИДЕ – ДЕЯТЕЛЬНОСТИ – ДЛЯ НОМЕР_КОНСУЛЬТАНТА FK ВИД_ДЕЯТЕЛЬНОСТИ НОМЕР_ПРОЕКТА FK ЧАСЫ КОЛИЧЕСТВО * НОМЕР_ПРОЕКТА PK НОМЕР_КЛИЕНТА FK НАЗВАНИЕ_ПРОЕКТА ИТОГО НОМЕР_СЧЁТА ДАТА_СЧЁТА * ДРУГИЕ РАСХОДЫ НОМЕР_РАСХОДОВ PK * НОМЕР_ПРОЕКТА ОПИСАНИЕ ОПЛАТА

КЛИЕНТ (номер_клиента, имя_клиента, адрес); Схема базы данных счёта ПРОЕКТ (номер_проекта, номер_клиента, консультационной службы название_проекта, итого, номер_счёта, дата_счёта) Внешний ключ: номер_клиента ссылается на КЛИЕНТ; ДРУГИЕ_РАСХОДЫ (номер_расходов, номер_проекта, описание, оплата) Внешний ключ: номер_проекта ссылается на ПРОЕКТ; КОНСУЛЬТАНТ (номер_консультанта, имя_консультанта, ставка); ЗАНЯТ-В-ВИДЕ-ДЕЯТЕЛЬНОСТИ-ДЛЯ (номер_консультанта, вид_деятельности, номер_проекта, часы, количество) Внешние ключи: номер_консультанта ссылается на КОНСУЛЬТАНТ, номер_проекта ссылается на ПРОЕКТ.