Лабораторные работы по проектированию баз данных

СОДЕРЖАНИЕ
1. ЛАБОРАТОРНАЯ РАБОТА № 1. Тема «Выбор и обследование предметной области. Построение инфологической модели предметной области»
2. ЛАБОРАТОРНАЯ РАБОТА № 2. Тема «Этап логического
проектирования. Выбор модели данных»
3. ЛАБОРАТОРНАЯ РАБОТА № 3. Тема «Этап логического
проектирования. Нормализация отношений»
4. ЛАБОРАТОРНАЯ РАБОТА № 4. Тема «Логическое проектирование баз данных. Создание логической модели базы
данных. Разработка схем документов и запросов пользователей»
5. ЛАБОРАТОРНАЯ РАБОТА № 5. Тема: «Работа с интерфейсом Visual FoxPro 5.0»
6. ЛАБОРАТОРНАЯ РАБОТА № 6. Тема: "Проектирование
структуры базы данных. Создание индексов и связей между
таблицами"
7. ЛАБОРАТОРНАЯ РАБОТА № 7. Тема "Работа с таблицами и представлениями. Передача данных между массивами и
таблицами"
Лабораторная работа № 1
Тема: «Выбор и обследование предметной области.
Построение инфологической модели предметной области»
(4 часа)
Цель лабораторной работы:
Привитие практических навыков проектирования баз данных на этапе обследования предметной области и построения ее инфологической модели.
Задание на лабораторную работу:
выбрать предметную область, содержащую не менее трех сущностей (объектов);
 сформулировать не менее трех запросов пользователей:
 первый – должен содержать поисковый алгоритм (например, определение
товара, пользующегося наибольшим спросом; определение стоимости партии
товара, отгруженной в конкретную дату и т.п.);
 второй – должен быть реализован в виде экранной формы, содержащей
информацию пользователя;
 третий – в виде отчета (документ, ведомость), содержащего данные как
минимум из двух сущностей.
 на основании запросов пользователей определить атрибутивный состав
сущностей, установить связи между ними, определить тип связи (1:1; 1:М; М:1;
М:М);
 построить инфологическую модель предметной области в виде ERдиаграммы;

Теоретические сведения
Основные понятия
Предметная область (ПО) – элементы материальной системы, информация о которых хранится и обрабатывается в экономической информационной
системе (ЭИС) (например: институт, завод, торговая база, библиотека).
Фрагмент предметной области – часть ПО, которая, в свою очередь,
может выступать как самостоятельная предметная область (например: факультет института, цех завода, склад торговой базы, читальный зал библиотеки).
Информационный объект – идентифицируемый объект реального мира,
некоторое понятие или процесс, относящиеся к ПО (например, информационные объекты предметной области «Институт»: студент, преподаватель, дисциплина).
Сущность – любой различимый объект (объект, который мы можем отличить от другого), информацию о котором необходимо хранить в базе данных.
(На языке модели «Сущность-связь» информационный объект – есть сущность.)
Атрибут – поименованная характеристика сущности. Его наименование
должно быть уникальным для конкретного типа сущности, но может быть одинаковым для различного типа сущностей (например, ЦВЕТ может быть определен для многих сущностей: СОБАКА, АВТОМОБИЛЬ, ДЫМ и т.д.). Атрибуты
используются для определения того, какая информация должна быть собрана о
сущности. Примерами атрибутов для сущности АВТОМОБИЛЬ являются ТИП,
МАРКА, НОМЕРНОЙ ЗНАК, ЦВЕТ и т.д.
В каждой сущности необходимо выбрать ключевые атрибуты.
Ключ – минимальный набор атрибутов, по значениям которых можно
однозначно найти требуемый экземпляр сущности. Минимальность означает,
что исключение из набора любого атрибута не позволяет идентифицировать
сущность по оставшимся.
Связь - это ассоциация, установленная между несколькими сущностями.
Между двумя сущностям, например, А и В возможны три вида связей.
Первый тип – связь «один к одному» (1:1): в каждый момент времени
каждому представителю (экземпляру) сущности А соответствует 1 или 0 представителей сущности В, например:
студент может не "заработать" стипендию, может получить обычную или
одну из повышенных стипендий, следовательно:
между сущностями СТУДЕНТ и СТИПЕНДИЯ существует связь
НАЗНАЧЕНИЕ, тип связи – 1:1
Второй тип – связь «один ко многим» (1:М): одному представителю
сущности А соответствуют 0, 1 или несколько представителей сущности В,
например:
квартира может пустовать, в ней может жить один или несколько жильцов. Т.О., между сущностями ЖИЛЕЦ и КВАРТИРА существует связь ПРОПИСКА, тип связи - 1:М
Третий тип – связь «многие ко многим» (M:М): одному представителю
сущности А соответствуют 0, 1 или несколько представителей сущности В и
наоборот: одному представителю сущности В соответствуют 0, 1 или несколько
представителей сущности А. Например, данное изделие может поставляться
многими поставщиками, и данный поставщик может поставлять много наименований изделий. Между сущностями ИЗДЕЛИЕ и ПОСТАВЩИК существует
связь ПОСТАВКА, тип связи - М:М.
Сбор данных, анализ информационных потребностей и проектирование концептуальной модели
Конечной целью сбора и анализа требований к данным является выявление имеющихся задач по обработке информации, которая должна быть представлена в БД.
Выявление информационных объектов и связей между ними состоит:
 в выборе информационных объектов;
 в задании необходимых свойств для каждого объекта;
 в определении ограничений, накладываемых на информационные объекты;
 в выявлении связей между объектами, типов этих связей.
Ограничения, направленные на поддержание целостности данных.
Целостность – понимается как правильность данных в любой момент
времени.
В модели имеется три типа ограничений на значения:
1. ограничения на допустимые значения в наборе значений (домене). Домен
можно трактовать как область определения атрибута, которая может быть задана либо непрерывным или дискретным интервалом, либо фиксированным списком значений (пол "Муж" или "Жен");
2. ограничения на разрешенные значения для каждого атрибута. Например,
возраст сотрудников может быть ограничен интервалом от 18 до 65 лет;
3. ограничения на существующие значения в базе данных. Например, сумма
отчислений с зарплаты сотрудника не должна превышать самой зарплаты.
Одним из наиболее удобных инструментов унифицированного представления данных, независимого от реализующего его программного обеспечения,
является модель "сущность-связь" (entity - relationship model, ER - model).
После выбора сущностей, задания атрибутов и анализа связей необходимо
перейти к построению инфологической модели (концептуальной схемы БД) в
виде ER-диаграммы, где сущности обозначаются прямоугольниками, связи –
ромбами.
Пример выполнения лабораторной работы
Дано:
Предметная область: Торговля.
Подсистема: Предприятие оптовой торговли
Фрагмент: Учет товаров на складе
Автоматизации подлежит задача «Учет товаров на предприятии оптовой
торговли» с целью получения актуальной информации по наличию товаров на
складе.
Множество атрибутов:
№ Наименование атрибута
Идентификатор
1
Номер склада
НОМ_СКЛАДА
2
Номер прейскуранта товара
НОМ_ПРЕЙСК
3
Наименование товара
НАИМ_ТОВ
4
Единица измерения
ЕД_ИЗМ
5
Покупная цена единицы товара
ЦЕНА_ПОК
6
Цена реализации единицы товара
ЦЕНА_ПРОД
7
Наименование поставщика
НАИМ_ПОСТ
8
Наименование валюты
ВАЛЮТА
10 Адрес поставщика
АДР_ПОСТ
11 Банковские реквизиты поставщика
БАНК_ПОСТ
12 Номер договора с поставщиком
НОМ_ДОГ_ПОСТ
13 Дата поставки товара по договору с поставщиком ДАТА_ДОГ_ПОСТ
14
15
16
17
18
19
20
21
22
23
Количество поставляемого поставщиком товара
по договору
Количество фактически полученного от поставщика товара
Наименование покупателя
Адрес покупателя
Банковские реквизиты покупателя
Номер договора о продаже товара
Дата отгрузки товара по договору с покупателем
Количество отгружаемого товара по договору
Количество фактически отгруженного товара покупателю
Дата фактической отгрузки товара покупателю
КОЛ_ДОГ_ПОСТ
КОЛ_ФАКТ_ПОСТ
НАИМ_ПОК
АДР_ПОК
БАНК_ПОК
НОМ_ДОГ_ПОК
ДАТА_ДОГ_ПОК
КОЛ_ДОГ_ПОК
КОЛ_ФАКТ_ПОК
ДАТА_ДОГ_ПОК
Характеристика предметной области
Предприятие оптовой торговли выступает одновременно покупателем и
продавцом товаров. Товар закупается крупным оптом партиями у поставщиков
и хранится на складах. Со складов товар реализуется мелкооптовыми партиями
покупателям.
Характеристика товара приводится в прайс-листе "Перечень товаров, на
складе".
При покупке товара между предприятием и поставщиком заключается
договор поставки. Так же при продаже товара между предприятием и покупателем заключается договор реализации. Каждый договор содержит полные реквизиты предприятия и поставщика (или покупателя), наименование товара; его
качественные, количественные и стоимостные характеристики; вид доставки,
форму оплаты и др.
При заключении договора осуществляется проверка наличия на складе
достаточного количества товара.
На предприятии ведется учет выполнения договорных обязательств поставщиков, а так же обязательств перед покупателями.
В результате решения задачи необходимо выполнить разработку подсистемы ведения данных (ввод новых товаров; удаление (отказ от реализации) товаров; корректировка имеющихся данных и др.) и спроектировать следующие
выходные документы (запросы пользователей):
1) Ведомость «Учет выполнения договорных обязательств поставщиков товаров» в разрезе поставщиков и видов товаров, в натуральном и стоимостном
выражении (за учетный период и по запросам);
2) Форма «Отгрузка товаров покупателям» в разрезе покупателей и видов
товаров, в натуральном и стоимостном выражении (ежедневно);
3) Справка «Учет поставщиков товара» (по запросу);
4) Форма «Учет товара на складе» в разрезе поставщиков и видов товаров, в
натуральном и стоимостном выражении (за учетный период и по запросам).
Ограничения и допущения ПО:
1. Номер прейскуранта товара - уникальный порядковый номер товара в
прайс-листе. Первая цифра номера идентифицирует группу товара (например,
1- строительные материалы, 2-горюче-смазочные материалы, 3 – сельхозпродукция, 4-товары народного потребления и др.)
2. Номер склада – уникальный номер. На предприятии не может быть двух
складов с одним и тем же номером.
3. Банковские реквизиты поставщика (покупателя) – содержат: наименование банка, город банка, расчетный счет банка, расчетный счет поставщика
(покупателя) в банке.
4. Наименование валюты – допускается валюта: российский рубль, немецкая марка, американский доллар. Все расчеты осуществляются по текущему
курсу в тенге.
5.
Количество фактически отгруженного товара покупателю не должно
превышать количества отгружаемого товара по договору
Для разработки схем документов, отражающих запросы пользователей,
необходимо составить таблицу соответствия между элементами данных и отчетами их содержащими, например:
Запросы
Идентификатор
Ведомость
Форма
Справка
Форма
атрибута
«Учет выполне- «Отгрузка
«Учет по- «Учет тония договорных товаров по- ставщиков
вара на
обязательств по- купателям»
товара»
складе»
ставщиков товаров»
НОМ_СКЛАДА
*
НОМ_ПРЕЙСК
*
НАИМ_ТОВ
*
*
*
*
ЕД_ИЗМ
*
*
*
ЦЕНА_ПОК
*
*
ЦЕНА_ПРОД
*
*
НАИМ_ПОСТ
*
*
ВАЛЮТА
*
*
АДР_ПОСТ
*
БАНК_ПОСТ
*
НОМ_ДОГ_ПОСТ
*
*
ДАТА_ДОГ_ПОСТ
*
КОЛ_ДОГ_ПОСТ
*
*
КОЛ_ФАКТ_ПОСТ
*
*
*
НАИМ_ПОК
*
АДР_ПОК
*
БАНК_ПОК
*
НОМ_ДОГ_ПОК
*
ДАТА_ДОГ_ПОК
*
КОЛ_ДОГ_ПОК
*
КОЛ_ФАКТ_ПОК
ДАТА_ДОГ_ПОК
*
*
*
Для разработки ЕR-модели необходимо выделить объекты ПО и их атрибутивный состав:
На основании необходимых запросов выделим следующие сущности с атрибутами (ключевые атрибуты выделены подчеркиванием):
ТОВАР (НОМ_ПРЕЙСК, НАИМ_ТОВАРА, ИЗМ_ТОВАРА, ЦЕНА_ПОКУП, ЦЕНА_ПРОД);
ПОСТАВЩИК
(НАИМ_ПОСТ,
АДРЕС_ПОСТ,
БАНК_ПОСТ,
НОМ_ДОГ_ПОСТ, ДАТА_ДОГ_ПОСТ, НОМ_ПРЕЙСК, КОЛ_ДОГ_ПОСТ,
КОЛ_ФАКТ_ПОСТ, ВАЛЮТА, НОМ_ СКЛАДА);
ПОКУПАТЕЛЬ (НАИМ_ПОК, АДРЕС_ПОК, БАНК_ПОК, НОМ_ДОГ_ПОК,
ДАТА_ДОГ_ПОК, ДАТА_ФАКТ_ПОК, НОМ_ПРЕЙСК, КОЛ_ДОГ_ПОК,
КОЛ_ФАКТ_ПОК, ВАЛЮТА, НОМ_ СКЛАДА);
Проведем анализ связей между сущностями:
Название сущностей
ТОВАР, ПОСТАВЩИК ТОВАР, ПОКУПАТЕЛЬ -
Название связей
ПОСТАВКА
РЕАЛИЗАЦИЯ
После выбора сущностей, задания атрибутов и анализа связей между
сущностями
проектируем концептуальную схему БД. в виде ER-диаграммы, где
ПОСТАВЩИК
сущности обозначаются прямоугольниками, связи – ромбами.
поставка
ТОВАР
Реализация
ПОКУПАТЕЛЬ
Рис.1 Концептуальная схема “ Учет товаров на предприятии оптовой торговли ”
Лабораторная работа № 2
Тема: «Этап логического проектирования.
Выбор модели данных»
(4 часа)
Цель лабораторной работы:
Использование теоретических знаний и применение их на практике при выборе
модели данных, адекватно отражающей предметную область проектируемой
базы данных.
Задание на лабораторную работу:


Реализовать инфологическую модель, полученную в результате выполнения лабораторной работы № 1 в терминах:
а) иерархической модели;
б) сетевой модели;
в) реляционной модели.
Обосновать выбор конкретной модели данных для проектирования базы
данных в конкретной предметной области.
Теоретические сведения
На этапе логического проектирования создается модель, пригодная для
реализации средствами какой-либо определенной СУБД.
Существует большое разнообразие сложных типов данных, но исследования, проведенные на большом практическом материале, показали, что среди
них можно выделить несколько наиболее общих. Обобщенные структуры называют также моделями данных, т.к. они отражают представление пользователя
о данных реального мира.
Любая модель данных должна содержать три компоненты:
 структура данных - описывает точку зрения пользователя на представление данных.
 набор допустимых операций, выполняемых на структуре данных.
 ограничения целостности - механизм поддержания соответствия данных
предметной области на основе формально описанных правил.
Общеизвестны иерархическая, сетевая и реляционная модели, в последнее время все большее значение приобретает объектно-ориентированный подход к представлению данных.
Иерархическая модель данных.
Структура данных.
Организация данных иерархического типа определяется в терминах: элемент, запись (группа), групповое отношение.
Атрибут (элемент данных) - наименьшая единица структуры данных.
Обычно каждому элементу при описании базы данных присваивается уникальное имя. По этому имени к нему обращаются при обработке. Элемент данных
также часто называют полем.

Запись - именованная совокупность атрибутов. Использование записей
позволяет за одно обращение к базе получить некоторую логически связанную
совокупность данных. Именно записи изменяются, добавляются и удаляются.
Тип записи определяется составом ее атрибутов. Экземпляр записи - конкретная запись с конкретным значением элементов
Групповое отношение - иерархическое отношение между записями двух
типов. Родительская запись (владелец группового отношения) называется исходной записью, а дочерние записи (члены группового отношения) - подчиненными. Иерархическая база данных может хранить только такие древовидные
структуры.
Корневая запись каждого дерева обязательно должна содержать ключ с
уникальным значением. Ключи некорневых записей должны иметь уникальное
значение только в рамках группового отношения. Каждая запись идентифицируется полным сцепленным ключом, под которым понимается совокупность
ключей всех записей от корневой по иерархическому пути.
Для групповых отношений в иерархической модели обеспечивается автоматический режим включения и фиксированное членство. Это означает, что
для запоминания любой некорневой записи в БД должна существовать ее родительская запись. При удалении родительской записи автоматически удаляются
все подчиненные.
 Операции над данными:
добавить в базу данных новую запись. Для корневой записи обязательно
формирование значения ключа.
изменить значение данных предварительно извлеченной записи. Ключевые данные не должны подвергаться изменениям.
удалить некоторую запись и все подчиненные ей записи.
извлечь:
извлечь корневую запись по ключевому значению, допускается также последовательный просмотр корневых записей
извлечь следующую запись (следующая запись извлекается в порядке левостороннего обхода дерева)
В операции извлечь допускается задание условий выборки (например,
извлечь данные о сотрудниках с окладом более 10 тысяч тенге.)
Как видим, все операции изменения применяются только к одной "текущей" записи (которая предварительно извлечена из базы данных). Такой подход
к манипулированию данных получил название навигационного.
 Ограничения целостности.
Поддерживается только целостность связей между владельцами и членами группового отношения (никакой потомок не может существовать без предка). Как уже отмечалось, не обеспечивается автоматическое поддержание соответствия парных записей, входящих в разные иерархии.
Сетевая модель данных

Структура данных.
Сетевая модель данных определяется в тех же терминах, что и иерархическая. Она состоит из множества записей, которые могут быть владельцами
или членами групповых отношений. Связь между записью-владельцем и записью-членом также имеет вид 1:М.
Основное различие этих моделей состоит в том, что в сетевой модели
запись может быть членом более, чем одного группового отношения. Согласно
этой модели каждое групповое отношение именуется и проводится различие
между его типом и экземпляром. Тип группового отношения задается его
именем и определяет свойства общие для всех экземпляров данного типа. Экземпляр группового отношения представляется записью-владельцем и множеством (возможно пустым) подчиненных записей. При этом имеется следующее
ограничение: экземпляр записи не может быть членом двух экземпляров групповых отношений одного типа (т.е. сотрудник, например, не может работать в
двух отделах предприятия).
 Операции над данными.
добавить - внести запись в БД и, в зависимости от режима включения, либо
включить ее в групповое отношение, где она объявлена подчиненной, либо не
включать ни в какое групповое отношение;
включить в групповое отношение - связать существующую подчиненную запись с записью-владельцем;
переключить- связать существующую подчиненную запись с другой записьювладельцем в том же групповом отношении;
обновить- изменить значение элементов предварительно извлеченной записи;
извлечь - извлечь записи последовательно по значению ключа, а также используя групповые отношения - от владельца можно перейти к записям - членам, а
от подчиненной записи к владельцу набора;
удалить - убрать из БД запись. Если эта запись является владельцем группового отношения, то анализируется класс членства подчиненных записей. Обязательные члены должны быть предварительно исключены из группового отношения, фиксированные удалены вместе с владельцем, необязательные останутся в БД.
Исключить из группового отношения - разорвать связь между записьювладельцем и записью-членом.
 Ограничения целостности.
Как и в иерархической модели обеспечивается только поддержание целостности по ссылкам (владелец отношения - член отношения).
Реляционная модель данных
Структура данных.
В основе реляционной модели данных лежит понятие отношения,
которое используется как инструмент моделирования данных.
Отношения удобно представлять в виде таблиц. Строки таблицы соответствуют кортежам. Каждая строка фактически представляет собой описание одного объекта реального мира, характеристики которого содержатся в столбцах.
Можно провести аналогию между элементами реляционной модели данных и

элементами модели "сущность-связь". Реляционные отношения соответствуют
наборам сущностей, а кортежи - сущностям. Поэтому, также как и в модели
"сущность-связь" столбцы в таблице, представляющей реляционное отношение,
называют атрибутами.
Атрибут, значение которого однозначно идентифицирует кортежи, называется ключевым (или просто ключом). Если кортежи идентифицируются
только сцеплением значений нескольких атрибутов, то говорят, что отношение
имеет составной ключ.
Отношение может содержать несколько ключей. Всегда один из ключей
объявляется первичным, его значения не могут обновляться. Все остальные
ключи отношения называются возможными ключами.
В отличие от иерархической и сетевой моделей данных в реляционной
отсутствует понятие группового отношения. Для отражения ассоциаций между
кортежами разных отношений используется дублирование их ключей.
 Ограничения целостности
В реляционной модели данных определены два базовых требования обеспечения целостности:
 целостность ссылок
 целостность сущностей.
Целостность сущностей.
Требование целостности сущностей заключается в следующем: каждый
кортеж любого отношения должен отличатся от любого другого кортежа этого
отношения (т.е. любое отношение должно обладать первичным ключом).
Вполне очевидно, что если данное требование не соблюдается (т.е. кортежи в рамках одного отношения не уникальны), то в базе данных может хранится противоречивая информация об одном и том же объекте.
Целостность ссылок
Сложные объекты реального мира представляются в реляционной базе
данных в виде кортежей нескольких нормализованных отношений, связанных
между собой. При этом:
Связи между данными отношениями описываются в терминах функциональных зависимостей.
Для отражения функциональных зависимостей между кортежами разных
отношений используется дублирование первичного ключа одного отношения
(родительского) в другое (дочернее). Атрибуты, представляющие собой копии
ключей родительских отношений, называются внешними ключами.
Требование целостности по ссылкам состоит в следующем: для каждого
значения внешнего ключа, появляющегося в дочернем отношении, в родительском отношении должен найтись кортеж с таким же значением первичного
ключа.
 Операции над данными.
Операции обработки кортежей.
Эти операции связаны с изменением состава кортежей в каком-либо отношении.
добавить - необходимо задать имя отношения и ключ кортежа.
удалить - необходимо указать имя отношения, а также идентифицировать кортеж или группу кортежей, подлежащих удалению.
изменить - выполняется для названного отношения и может корректировать
как один, так и несколько кортежей.
Операции обработки отношений.
На входе каждой такой операции используется одно или несколько отношений, результатом выполнения операции всегда является новое отношение.
В реляционной алгебре определены следующие операций обработки отношений:
Проекция: Операция проекции представляет из себя выборку из каждого кортежа отношения значений атрибутов, входящих в список A, и удаление из полученного отношения повторяющихся строк.
Выборка: На входе используется одно отношение, результат - новое отношение, построенное по той же схеме, содержащее подмножество кортежей исходного отношения, удовлетворяющих условию выборки.
Объединение: Отношения-операнды в этом случае должны быть определены
по одной схеме. Результирующее отношение содержит все строки операндов за
исключением повторяющихся.
Пересечение: На входе операции два отношения, определенные по одной схеме. На выходе - отношение, содержащие кортежи, которые присутствуют в
обоих исходных отношениях.
Разность: Операция во многом похожая на ПЕРЕСЕЧЕНИЕ, за исключением
того, что в результирующем отношении содержатся кортежи, присутствующие
в первом и отсутствующие во втором исходных отношениях.
Декартово произведение: Входные отношения могут быть определены по разным схемам. Схема результирующего отношения включает все атрибуты исходных. Кроме того:
степень результирующего отношения равна сумме степеней исходных
отношений;
мощность результирующего отношения равна произведению мощностей
исходных отношений.
Соединение: Данная операция имеет сходство с декартовым произведением.
Однако, здесь добавлено условие, согласно которому вместо полного произведения всех строк в результирующее отношение включаются только строки,
удовлетворяющие определенному соотношению между атрибутами соединения
(А1,A2) соответствующих отношений.
Пример выполнения лабораторной работы
а) Построение иерархической модели.
Рассмотрим предметную область, описанную в лабораторной работе №1
(см рис.2.1): Если каждый поставщик может поставлять несколько наименова-
ний товаров, но товар не может поставляться более чем одним поставщиком.
Товар может быть реализован нескольким покупателям, то для информационной системы управления учета товаров необходимо создать групповое отношение в виде дерева, состоящее из записи ПОСТАВЩИК, записи ТОВАР и записи
ПОКУПАТЕЛЬ. Это отношение показано на рис. (а) (Для простоты полагается,
что имеются только две дочерние записи).
Иерархическая модель реализует отношение между исходной и дочерней
записью по схеме 1:М, то есть одной родительской записи может соответствовать любое число дочерних.
Допустим теперь, что покупателю может быть реализовано более чем в
одно наименование товара (т.е. возникает связь типа M:М). В этом случае в базу данных необходимо ввести еще одно групповое отношение, в котором ПОКУПАТЕЛЬ будет являться исходной записью, а ТОВАР - дочерней (рис. (б)).
Таким образом, мы вынуждены дублировать информацию.
Приведенное выше предположение, о том, что товар не может поставляться более чем одним поставщиком, не отражает реальной ситуации. Следовательно, необходимо ввести еще одно групповое отношение, в котором ТОВАР будет являться исходной записью, а ПОСТАВЩИК - дочерней (рис. (в)).
Мы еще раз дублируем информацию.
ПОСТАВЩИК
ТОВАР
ТОВАР
ПОКУПАТЕЛЬ
ТОВАР
ТОВАР
(б)
ПОКУПАТЕЛЬ
ПОКУПАТЕЛЬ
(а)
ТОВАР
ПОСТАВЩИК
ПОСТАВЩИК
(в)
Рис.2.1 Иерархическая модель
Операции, которые можно выполнить с данными:
Найти указанное дерево БД (например, товар «А»);
Перейти от одного дерева к другому;
Перейти от одной записи к другой внутри дерева (например, от товара - к первому покупателю);
Перейти от одной записи к другой в порядке обхода иерархии;
Вставить новую запись в указанную позицию;
Удалить текущую запись.
б) Построение сетевой модели.
Иерархическая структура преобразовывается в сетевую следующим образом
(см. рис. 2.2):
Деревья (a) и (б), показанные на рис. 2.1, заменяются одной сетевой
структурой, в которой запись ТОВАР входит в два групповых отношения. Для
отображения типа M:N вводится записи ТОВАР-ПОКУПАТЕЛЬ и ПОСТАВЩИК-ТОВАР, которые служит только для связи записей ТОВАР и ПОКУПАТЕЛЬ (ПОСТАВЩИК и ТОВАР), см. рис. 2.2.(в этой записи может храниться
информация, например, количество данного товара по данному покупателю
(поставщику))
ПОСТАВЩИК
ТОВАР
ПОСТАВЩИК-ТОВАР
ПОСТАВЩИК-ТОВАР
ПОКУПАТЕЛЬ
ТОВАР
ТОВАР
ТОВАР-ПОКУПАТЕЛЬ
ТОВАР-ПОКУПАТЕЛЬ
Рис.2.2 Сетевая модель
Из этого примера видны недостатки иерархических БД: Излишнее дублирование записей
Операции, которые можно выполнить с данными:
 Найти конкретную запись в наборе однотипных записей (товар «А»);
 Перейти от предка к первому потомку по некоторой связи;
 Перейти к следующему потомку в некоторой связи (от товара «А» к товару
«Б»);
 Перейти от потомка к предку по некоторой связи;
 Создать новую запись;
 Удалить запись;
 Модифицировать запись;
 Включить в связь;
 Исключить из связи;
 Переставить в другую связь
в) Построение реляционной модели.
Информационные объекты, выделенные в лабораторной работе №1, можно представить в виде следующих отношений с соответствующими атрибутами,
в каждом отношении необходимо выделить ключи:
1. ТОВАР
Ном_прейск Наим_товара изм_товара цена_покуп цена_прод
2. ПОСТАВЩИК
Наим Адрес Банк № дог Дата дог №
Кол-во Кол-во
валюта Ном
Пост пост пост пост поставк прейск Дог пост Факт пост
склада
3. ПОКУПАТЕЛЬ
Наим Адрес Банк № дог Дата Дата Ном
Кол-во Кол-во валюта Ном
Пок Пок Пок Пок дог
факт прейск Дог
Факт
склада
Пок Пок
Пок
Пок
В отличие от иерархической и сетевой моделей данных в реляционной
отсутствует понятие группового отношения. Для отражения ассоциаций между
кортежами разных отношений используется дублирование их ключей. Рассмотренный выше пример, содержащей сведения об учете покупки и реализации товара, применительно к реляционной модели будет иметь вид:
ТОВАР
ПОСТАВЩИК
Ном_прейск
№ дог пост
Наим_товара
Адрес пост
изм_товара
....
цена_покуп
№ прейск
ПОКУПАТЕЛЬ
№ дог Пок
Адрес Пок
....
Ном прейск
Например, связь между отношениями ТОВАР и ПОСТАВЩИК создается путем
копирования первичного ключа "Номер_прейскуранта" из первого отношения
во второе. Таким образом, для того, чтобы получить список поставщиков данного товара, необходимо:
 в таблице ТОВАР установить значение атрибута "Номер прейскуранта", соответствующее данному "Наименованию_товара";
 выбрать из таблицы ПОСТАВЩИК все записи, значение атрибута "Номер_прейскуранта" которых равно полученному на предыдущем шаге;
 для того, чтобы узнать какой товар реализован заказчику, нужно выполнить
обратную операцию;
 определить "Номер_прейскуранта" из таблицы ПОСТАВЩИК;
 по полученному значению найти запись в таблице ТОВАР.
Атрибуты, представляющие собой копии ключей других отношений, называются внешними ключами.
Операции, которые можно выполнить с данными:
 Создать новую запись;
 Удалить запись;
 Модифицировать запись
Вывод:
В реляционной модели достигается гораздо более высокий уровень абстракции данных, чем в иерархической или сетевой.
Предсказуемость результатов работы с данными обеспечивается математической моделью данных, лежащей в основе реляционной модели. Следовательно, любой запрос к базе данных, составленный на корректном языке влечет
ответ, однозначно определяемый схемой БД и конкретными данными. Выбранная предметная область достаточно естественно описывается в терминах отношений. Нет излишнего дублирования записей. Модель наглядна и, при необходимости, можно осуществить доступ к данным любого уровня.
На основании вышесказанного делаем вывод, что наиболее эффективной
моделью для отображения выбранной предметной области и реализации запросов пользователей является реляционная модель.
Вопросы для проверки знаний:
1. Что понимается под моделью данных? Какие модели данных вам известны?
2. Какие основные компоненты должна содержать любая модель данных?
3. Дать характеристику иерархической модели данных.
4. Дать характеристику сетевой модели данных.
5. Какую структуру данных предполагает реляционная модель?
6. Перечислить и привести пример операций обработки отношений,
определенных в реляционной алгебре.
Лабораторная работа № 3
Тема: «Этап логического проектирования.
Нормализация отношений»
(4 часа)
Цель работы:
Практическая реализация теоретических знаний по приведению
отношений к 3НФ, с целью устранения нежелательных функциональных зависимостей.
Задание на лабораторную работу:



Подтвердить выбор ключевых атрибутов отношений, выполненный на первом этапе проектирования (см. лабораторную работу №1);
Провести анализ функциональных зависимостей по данным инфологической модели предметной области (см. лабораторную работу №1);
Выполнить декомпозицию отношений и констатировать наличие:
а) первой нормальной формы (1НФ);
б) второй нормальной формы (2НФ);
в) третьей нормальной формы (3НФ).
Теоретические сведения
Нормализацией называется обратимый пошаговый процесс декомпозиции отношений на более мелкие, с целью устранения нежелательных функциональных зависимостей.
Функциональная зависимость
Если даны два атрибута X и Y некоторого отношения, то говорят, что Y функционально зависит от X, если в любой момент времени каждому значению X
соответствует ровно одно значение Y.
Функциональная зависимость обозначается XY. Отметим, что X и Y могут
представлять собой не только единичные атрибуты, но и группы, составленные
из нескольких атрибутов одного отношения.
Полная функциональная зависимость
Функциональная зависимость XY называется полной, если атрибут Y не зависит функционально от любого точного подмножества X. т.е. Существует
функциональная зависимость X+ZY, и нет функциональных зависимостей
XY, ZY.
Транзитивная функциональная зависимость
Функциональная зависимость XY называется транзитивной, если существует
такой атрибут Z, что имеются функциональные зависимости XZ и ZY и отсутствует функциональная зависимость ZX.
Неключевой атрибут - любой атрибут отношения, не входящий в состав первичного ключа
Взаимно независимые атрибуты
Два или более атрибута взаимно независимы, если ни один из этих атрибутов
не является функционально зависимым от других.
1НФ - первая нормальная форма.
Отношение находится в 1НФ если значения всех его атрибутов атомарны.
2НФ - вторая нормальная форма.
Отношение находится во 2НФ, если оно находится в 1НФ и каждый неключевой атрибут функционально полно зависит от ключа.
3НФ – третья нормальная форма
Отношение находится в 3НФ, если оно находится во 2НФ и каждый неключевой атрибут нетранзитивно зависит от первичного ключа
Пример выполнения лабораторной работы
Для приведения отношения к 3НФ необходимо провести анализ функциональных зависимостей между атрибутами в пределах каждого отношения.
1. ТОВАР (НОМ_ПРЕЙСК, НАИМ_ТОВАРА, ИЗМ_ТОВАРА, ЦЕНА_ПОКУП, ЦЕНА_ПРОД);
НОМ_ПРЕЙСК  [все атрибуты]
 Учет товаров на складах ведется по номеру прейскуранта. Номер прейскуранта каждого товара – уникален. В данном отношении первичным ключом является атрибут НОМ_ПРЕЙСК.
 Все атрибуты являются атомарными, следовательно, отношение находится
в 1НФ.
 Первичный ключ – простой и функциональная зависимость всех неключевых атрибутов от первичного ключа – полная, следовательно, отношение находится во 2НФ.
 Все неключевые атрибуты функционально зависят от первичного ключа,
других функциональных зависимостей нет, следовательно, отношение находится в 3НФ.
2. ПОСТАВЩИК
(НАИМ_ПОСТ,
АДРЕС_ПОСТ,
БАНК_ПОСТ,
НОМ_ДОГ_ПОСТ, ДАТА_ДОГ_ПОСТ, НОМ_ПРЕЙСК, КОЛ_ДОГ_ПОСТ,
КОЛ_ФАКТ_ПОСТ, ВАЛЮТА, НОМ_ СКЛАДА);
 С каждым поставщиком заключается договор о поставке товара, договор
имеет свой уникальный номер договора. Поставка товара по одному договору
может осуществляться на разные склады. Тогда в качестве составного первич-
ного ключа можно использовать следующие атрибуты НОМ_ДОГ_ПОСТ и
НОМ_ СКЛАДА.
 Все атрибуты являются атомарными, следовательно, отношение находится
в 1НФ.
 Первичный ключ – составной.
Имеем следующие функциональные зависимости:
НОМ_ДОГ_ПОСТ+ НОМ_ СКЛАДА  КОЛ_ФАКТ_ПОСТ
Некоторые атрибуты зависят только от части составного ключа НОМ_ДОГ_ПОСТ.
НОМ_ДОГ_ПОСТ  НАИМ_ПОСТ, АДРЕС_ПОСТ, БАНК_ПОСТ, ДАТА_ДОГ_ПОСТ, НОМ_ПРЕЙСК, КОЛ_ДОГ_ПОСТ, ВАЛЮТА.
С учетом этой функциональной зависимости произведем декомпозицию отношения ПОСТАВЩИК на следующие:
 ДОГОВОР ПОСТАВКИ (НОМ_ДОГ_ПОСТ, НАИМ_ПОСТ, АДРЕС_
ПОСТ,
БАНК_ПОСТ,
ДАТА_ДОГ_ПОСТ,
НОМ_ПРЕЙСК,
КОЛ_ДОГ_ПОСТ, ВАЛЮТА)
 ПОСТАВКА (НОМ_ДОГ_ПОСТ, НОМ_ СКЛАДА, КОЛ_ФАКТ_ПОСТ)
В этих отношениях зависимость всех неключевых атрибутов от первичного
ключа – полная, следовательно, оба отношения находятся во 2НФ.
 В отношении ПОСТАВКА все неключевые атрибуты функционально зависят от первичного ключа, других функциональных зависимостей нет, следовательно, это отношение находится в 3НФ.
 В отношении ДОГОВОР ПОСТАВКИ имеем след. ФЗ:
НОМ_ДОГ_ПОСТ  НАИМ_ПОСТ, ДАТА_ДОГ_ПОСТ, НОМ_ПРЕЙСК,
КОЛ_ДОГ_ПОСТ, КОЛ_ФАКТ_ПОСТ, ВАЛЮТА, НОМ_ СКЛАДА.
все неключевые атрибуты функционально зависят от первичного ключа, кроме
того, имеется транзитивная зависимость
НАИМ_ПОСТ  АДРЕС_ПОСТ, БАНК_ПОСТ
С учетом этой функциональной зависимости произведем декомпозицию отношения ДОГОВОР ПОСТАВКИ на следующие:
 ПОСТАВЩИК (НАИМ_ПОСТ, АДРЕС_ПОСТ, БАНК_ПОСТ)
 ДОГОВОР ПОСТАВКИ (НОМ_ДОГ_ПОСТ, НАИМ_ПОСТ, ДАТА_ДОГ_
ПОСТ, НОМ_ ПРЕЙСК, КОЛ_ДОГ_ПОСТ, ВАЛЮТА)
В этих отношениях все неключевые атрибуты функционально зависят от первичного ключа, других функциональных зависимостей нет, следовательно, отношения находятся в 3НФ.
3. ПОКУПАТЕЛЬ
(НАИМ_ПОК,
АДРЕС_ПОК,
БАНК_ПОК,
НОМ_ДОГ_ПОК, ДАТА_ДОГ_ПОК, ДАТА_ФАКТ_ПОК, НОМ_ПРЕЙСК,
КОЛ_ДОГ_ПОК, КОЛ_ФАКТ_ПОК, ВАЛЮТА, НОМ_ СКЛАДА);
Процесс нормализации отношения ПОКУПАТЕЛЬ аналогичен процессу нормализации отношения ПОСТАВЩИК. В результате нормализации этого отношения получили следующие отношения:
 ПОКУПАТЕЛЬ (НАИМ_ПОК, АДРЕС_ПОК, БАНК_ПОК)
 ДОГОВОР РЕАЛИЗАЦИИ (НОМ_ДОГ_ПОК,
НАИМ_ПОК, ДАТА_ДОГ_ПОК, НОМ_ПРЕЙСК, КОЛ_ДОГ_ПОК, ВАЛЮТА)
 РЕАЛИЗАЦИЯ (НОМ_ДОГ_ПОК, НОМ_ СКЛАДА, КОЛ_ФАКТ_ПОК)
Таким образом, в результате приведения отношений к 3НФ получили следующие отношения ТОВАР, ПОСТАВЩИК, ДОГОВОР ПОСТАВКИ, ПОСТАВКА, ПОКУПАТЕЛЬ, ДОГОВОР РЕАЛИЗАЦИИ, РЕЛИЗАЦИЯ.
Вопросы для проверки знаний:
1. Что понимается под нормализацией отношений.
2. Перечислите известные вам виды функциональных зависимостей. Дайте их определение.
3. На примере своей предметной области пояснить, что понимается под
первой нормальной формой отношения.
4. На примере своей предметной области пояснить, что понимается под
второй нормальной формой отношения.
5. На примере своей предметной области пояснить, что понимается под
третьей формой отношения.
Лабораторная работа № 4.
Тема «Логическое проектирование баз данных.
Создание логической модели базы данных. Разработка схем
документов и запросов пользователей»
Цель лабораторной работы:
Использование теоретических знаний при проектировании логической модели
БД, реализованной в виде схемы отношений, приведенных к 3НФ.
Задание на лабораторную работу:



На основе отношений, нормализованных до 3НФ создать логическую модель
БД в виде схемы отношений.
Для каждого атрибута отношений указать его тип и размерность.
Разработать схемы документов, указав при этом источник данных для каждого атрибута.
Пример выполнения лабораторной работы.
На предыдущем этапе были получены следующие отношения:
ТОВАР
Ном_прейск
Наим_товара Изм_товара
Цена_покуп
Цена_прод
ПОСТАВЩИК
Наим_Пост
Адрес_Пост
Банк_Пост
ДОГОВОР ПОСТАВКИ
Ном_Дог_Пост Наим_Пост Дата_Дог_Пост Ном_Прейск Кол_Дог_Пост Валюта
ПОСТАВКА
Ном_Дог_Пост
Ном_Склада
ПОКУПАТЕЛЬ
Наим_Пок
Адрес_Пок
Кол_Факт_Пост
Банк_Пок
ДОГОВОР РЕАЛИЗАЦИИ
Ном_Дог_Пок Наим_Пок Дата_Дог_Пок Ном_Прейск Кол_Дог_Пок Валюта
РЕАЛИЗАЦИЯ
Ном_Дог_Пок Ном_Склада
Кол_Факт_Пок
Ключи отношений выделены подчеркиванием.
ТОВАР
Ном_прейск
Наим_товара
Изм_товара
Цена_покуп
Символьный
Символьный
Символьный
Числовой
5 символов
10 символов
6 символов
6 символов
ПОСТАВЩИК
Наим_Пост
Адрес_Пост
Символьный
Символьный
10 символов
15 символов
Цена_прод
Числовой
6 символов
Банк_Пост
Символьный
10 символов
ДОГОВОР ПОСТАВКИ
Ном_Дог_Пост Наим_Пост Дата_Дог_Пост Ном_Прейск Кол_Дог_Пост Валюта
Числовой Символьны
Дата
Символьный Числовой
Симй
вольн.
5 симв.
10 симв.
8 симв.
5 симв.
6 симв.
8 симв.
ПОСТАВКА
Ном_Дог_Пост
Числовой
5 символов
Ном_Склада
Числовой
3 символа
Кол_Факт_Пост
Числовой
8 символов
ПОКУПАТЕЛЬ
Наим_Пок
Адрес_Пок
Банк_Пок
Символьный Символьный Символьный
10 символов
15 символов 10 символов
ДОГОВОР РЕАЛИЗАЦИИ
Ном_Дог_Пок Наим_Пок Дата_Дог_Пок Ном_Прейск Кол_Дог_Пок Валюта
Числовой Символьн
Дата
Символьный
Числовой Символь
5 симв.
ый
10 симв.
РЕАЛИЗАЦИЯ
Ном_Дог_Пок Ном_Склада
Числовой
Числовой
5 символов
3 символа
8 симв.
5 симв.
8 симв.
ный
8 симв.
Кол_Факт_Пок
Числовой
8 символов
Разработаем схемы документов:
Ведомость «Учет выполнения договорных обязательств поставщиков товаров» в разрезе поставщиков и видов товаров, в натуральном и стоимостном
выражении (за учетный период и по запросам).
Для реализации этого запроса потребуются следующие данные:
Отношение
Идентификатор
Атрибута
ТОВАР
НОМ_ПРЕЙСК
НАИМ_ТОВ
ЕД_ИЗМ
ЦЕНА_ПОК
ПОСТАВЩИК
НАИМ_ПОСТ
ВАЛЮТА
АДР_ПОСТ
БАНК_ПОСТ
ДОГОВОР ПОСТАВКИ
НОМ_ДОГ_ПОСТ
НОМ_ПРЕЙСК
ДАТА_ДОГ_ПОСТ
КОЛ_ДОГ_ПОСТ
Тип связи
1:М (Родительское)
1:М (Родительское)
1:М (Родительское)
(Дочернее)
НАИМ_ПОСТ
(Дочернее)
ПОСТАВКА
НОМ_ДОГ_ПОСТ
КОЛ_ФАКТ_ПОСТ
(Дочернее)
Вопросы для проверки знаний:
1. Охарактеризуйте этап логического проектирования.
2. Какие существуют типы связи? Показать на примере своей предметной области.
3. На основании чего осуществляется выбор Родительской и Дочерних
таблиц?
4. Какие типы связи поддерживает Visual FoxPro 5.0? Каким образом
можно обработать связь типа М:М?