прак. Практическая работа № 1 «Преобразование реляционной БД в сущност. Практическая работа 1 Преобразование реляционной бд в сущности и связи
 Скачать 414.86 Kb.
|
|
Практическая работа № 1 «Преобразование реляционной БД в сущности и связи» Цель работы: выработать практические навыки моделирования предметной области и построении ER-модели данных, закрепить технологию проектирования БД, закрепить основные понятия теории реляционных баз данных, освоить технологию построения ER-диаграмм, научиться получать реляционные БД из ER-диаграмм Сущность-связь Работа с базой данных начинается с построения модели. Наиболее распространённой является ER-модель (entity-relationship model) - модель "Сущность-связь". Для "ручного" построения ER-модели на практике будем использовать простую систему обозначений, предложенную Питером Ченом (обозначения, встречающиеся в разных источниках, могут отличаться от нижеприведённых): Рисунок 1.1. Сущность - связь Первичный ключ - атрибут или группа атрибутов, однозначно идентифицирующих объект. Первичный ключ может состоять из нескольких атрибутов, тогда подчёркивается каждый из них. Связи между объектами могут быть 3-х типов: Один - к одному. Этот тип связи означает, что каждому объекту первого вида соответствует не более одного объекта второго вида, и наоборот. Например: сотрудник может руководить только одним отделом, и у каждого отдела есть только один руководитель. Один - ко многим. Этот тип связи означает, что каждому объекту первого вида может соответствовать более одного объекта второго вида, но каждому объекту второго вида соответствует не более одного объекта первого вида. Например: в каждом отделе может быть множество сотрудников, но каждый сотрудник работает только в одном отделе. Многие - ко многим. Этот тип связи означает, что каждому объекту первого вида может соответствовать более одного объекта второго вида, и наоборот. Например: каждый счёт может включать множество товаров, и каждый товар может входить в разные счета. Реляционная структура данных В конце 60-х годов появились работы, в которых обсуждались возможности применения различных табличных даталогических моделей данных. Наиболее значительной из них была статья сотрудника фирмы IBM д-ра Эдварда Кодда (Codd E.F., A Relational Model of Data for Large Shared Data Banks. CACM 13: 6, June 1970), где впервые был применён термин «реляционная модель данных». Будучи математиком по образованию, Э. Кодд предложил использовать для обработки данных аппарат теории множеств (объединение, пересечение, разность, декартово произведение). Он показал, что любое представление данных сводится к совокупности двумерных таблиц особого вида, известного в математике как отношение – relation. Наименьшая единица данных реляционной модели – это отдельное атомарное (неразложимое) для данной модели значение данных. Так, в одной предметной области фамилия, имя и отчество могут рассматриваться как единое значение, а в другой – как три различных значения. Доменом называется множество атомарных значений одного и того же типа. Так, на рис. домен пунктов отправления (назначения) – множество названий населённых пунктов, а домен номеров рейса – множество целых положительных чисел. Таблица 1.1. Рейс Номер рейса Дни недели Пункт отправления Время вылета Пункт назначения Время прибытия Тип самолета Стоимость билета 138 2_4_7 Баку 21.12 Москва 0.52 ИЛ-86 115.00 57 3_6 Ереван 7.20 Киев 9.25 ТУ-154 92.00 1234 2_6 Казань 22.40 Баку 23.50 ТУ-134 73.50 242 1 по 7 Киев 14.10 Москва 16.15 ТУ-154 57.00 86 2_3_5 Минск 10.50 Сочи 13.06 ИЛ-86 78.50 137 1_3_6 Москва 15.17 Баку 18.44 ИЛ-86 115.00 241 1 по 7 Москва 9.05 Киев 11.05 ТУ-154 57.00 577 1_3_5 Рига 21.53 Таллин 22.57 АН-24 21.50 78 3_6 Сочи 18.25 Баку 20.12 ТУ-134 44.00 578 2_4_6 Таллин 6.30 Рига 7.37 АН-24 21.50 Смысл доменов состоит в следующем. Если значения двух атрибутов берутся из одного и того же домена, то, вероятно, имеют смысл сравнения, использующие эти два атрибута (например, для организации транзитного рейса можно дать запрос «Выдать рейсы, в которых время вылета из Москвы в Сочи больше времени прибытия из Архангельска в Москву»). Если же значения двух атрибутов берутся из различных доменов, то их сравнение, вероятно, лишено смысла: стоит ли сравнивать номер рейса со стоимостью билета? Отношение на доменах D1, D2, ..., Dn состоит из заголовка и тела. На рис. 1.2 приведён пример отношения для расписания движения самолётов (таблица 1). Ai - атрибуты, Vi - значения атрибутов. Рисунок 1.2. Отношение с математической точки зрения Заголовок отношения состоит из такого фиксированного множества атрибутов A1, A2, ..., An, что существует взаимно однозначное соответствие между этими атрибутами Ai и определяющими их доменами Di (i=1,2,...,n). Тело отношения состоит из меняющегося во времени множества кортежей, где каждый кортеж состоит в свою очередь из множества пар атрибут-значение (Ai:Vi), (i=1,2,...,n), по одной такой паре для каждого атрибута Ai в заголовке. Для любой заданной пары атрибут-значение (Ai:Vi) Vi является значением из единственного домена Di, который связан с атрибутом Ai. Степень отношения – это число его атрибутов. Отношение степени один называют унарным, степени два – бинарным, степени три – тернарным, ..., а степени n – n-арным. Степень отношения «Рейс» (таблица 1) равна 8. Кардинальное число или мощность отношения – это число его кортежей. Мощность отношения «Рейс» равна 10. Кардинальное число отношения изменяется во времени в отличие от его степени. Поскольку отношение – это множество, а множества по определению не содержат совпадающих элементов, то никакие два кортежа отношения не могут быть дубликатами друг друга в любой произвольно заданный момент времени. Пусть R – отношение с атрибутами A1, A2, ..., An. Говорят, что множество атрибутов K=(Ai, Aj, ..., Ak) отношения R является возможным ключом R тогда и только тогда, когда удовлетворяются два независимых от времени условия: Уникальность: в произвольный заданный момент времени никакие два различных кортежа R не имеют одного и того же значения для Ai, Aj, ..., Ak. Минимальность: ни один из атрибутов Ai, Aj, ..., Ak не может быть исключен из K без нарушения уникальности. Каждое отношение обладает хотя бы одним возможным ключом, поскольку по меньшей мере комбинация всех его атрибутов удовлетворяет условию уникальности. Один из возможных ключей (выбранный произвольным образом) принимается за его первичный ключ. Остальные возможные ключи, если они есть, называются альтернативными ключами. Вышеупомянутые и некоторые другие математические понятия явились теоретической базой для создания реляционных СУБД, разработки соответствующих языковых средств и программных систем, обеспечивающих их высокую производительность, и создания основ теории проектирования баз данных. Также на практике широко используются неформальные эквиваленты этих понятий: Отношение – Таблица, Кортеж – Строка таблицы или Запись, Атрибут – Столбец Таблицы или Поле. При этом принимается, что «запись» означает «экземпляр записи», а «поле» означает «имя и тип поля». Задание для практической работы Задание 1. Проектирование реляционных Баз Данных. Вариант 1. Построить реляционную таблицу Базы Данных имён родственников студентов вашей группы, содержащую данные об именах родителей, братьев и сестёр студентов. Вариант 2. Построить реляционную таблицу Базы Данных домов, где живут студенты вашей группы, содержащую данные о районе расположения дома, количестве этажей в нем и номере этажа, где живет студент. Вариант 3. Построить реляционную таблицу Базы Данных дней рождения студентов вашей группы, содержащую данные о дате рождения, знаке зодиака и годе по Китайскому календарю. Вариант 4. Построить реляционную таблицу Базы Данных Сотовых телефонов студентов вашей группы, содержащую данные о модели телефона, типе корпуса, операторе. Технология выполнения работы и оформление отчёта 1.1. Придумайте заголовок отношения и запишите его в отчёт. 1.2. Определите атрибуты отношения. Начертите сетку таблицы в отчёт и занесите в неё атрибуты. 1.3. Опросите студентов вашей группы и занесите полученные данные в таблицу. 1.4. На чертеже таблицы укажите чему соответствуют понятия: Заголовок отношения, тело отношения, атрибут отношения, кортеж отношения. 1.5. Определите и запишите в отчёт степень отношения и мощность отношения. 1.6. Дайте определение первичного ключа. Укажите Первичный ключ получившегося отношения 1.7. Докажите, что у вас получилась реляционная таблица, для этого укажите типы данных всех атрибутов. Задание 2. Проектирование Баз Данных. ER-диаграммы. Формулировка задания. По описанию предметной области построить логическую модель БД методом ER-диаграмм, на основании которой построить набор таблиц БД. Вариант 1. Описание предметной области (Ресторан). Посетители ресторана обслуживаются за столиками. За одним столом может располагаться не более 4 посетителей, каждый из которых может сделать заказ тех или иных блюд. Столики обслуживают официанты. У одного официанта в обслуживании несколько столов. Задачи для БД: • Есть ли свободные столы? • Сколько посетителей обслужил официант за смену? • Сколько каких блюд было реализовано? Вариант 2. Описание предметной области (Колледж). Студенты колледжа объединены в группы. Набор дисциплин, изучаемых студентом, зависит от номера группы в которой он учится. Преподаватели читают дисциплины и выставляют зачеты студентам. Один преподаватель может читать несколько дисциплин, но каждую дисциплину ведет один преподаватель. Задачи для БД: • Какие дисциплины изучает студент? • Какая оценка у студента по данной дисциплине? • Кто выставил эту оценку? Вариант3. Описание предметной области (Театральная касса). В театральной кассе продаются билеты на спектакли. Стоимость билета зависит от ряда, театра и спектакля. Каждый день в театре может идти не более одного спектакля. Спектакль характеризуется названием и автором. Каждый покупатель может купить сколько угодно билетов на любые спектакли. Задачи для БД: • Какие спектакли идут в определенный день? • Есть ли билеты на конкретный спектакль? • Сколько стоит конкретный билет? Вариант 4. Описание предметной области (Грузоперевозки). АТП имеет грузовые автомобили с гос. номерами и организует перевозки для своих заказчиков. Стоимость перевозки зависит от расстояния и грузоподъемности автомобиля, который ее выполняет. Каждый заказчик может сделать заказ нескольких перевозок. Одну перевозку выполняет один грузовик. Задачи для БД: • Какие грузовики свободны? • Какой заказчик сделал самый дорогой заказ? • Какой грузовик выполнил наибольшее количество заказов? Технология выполнения работы 1. Построение ER-диаграммы. 1.1. Выберите из описания предметной области все существительные. Продумайте, какие из них будут соответствовать сущностям, а какие атрибутам сущностей. Зарисуйте в отчет все сущности с их атрибутами согласно обозначениям, принятым в ER-диаграммах. 1.2. На рисунке подчёркиванием атрибутов обозначьте для каждой сущности уникальный идентификатор (Ключ). При необходимости добавьте сущностям атрибуты, которые помогут однозначно отличить каждый экземпляр сущности. 1.3. Определите и включите в схему связи сущностей. Подпишите названия связей и пронумеруйте связи. Для первой связи укажите тип и модальность. Для всех связей запишите их прочтение слева направо и справа налево. 1.4. Если в схеме присутствуют связи типа «много-со-многими» уберите их путем ввода дополнительной сущности. Измененную схему зарисуйте в отчет. 2. Получение реляционной схемы из ER-диаграммы. 2.1. Каждая сущность превращается в таблицу. Имя сущности – имя таблицы. Набор всех таблиц – БД. Вспомните, что такое схема БД. Запишите схему вашей БД в отчет. 2.2. Зарисуйте все полученные таблицы с их заголовками и названиями столбцов. Выделите потенциальные и внешние ключи (если есть) для каждой таблицы. Укажите столбцы, допускающие неопределенные значения. 2.3. Докажите, что полученные отношения находятся в Первой нормальной форме. (Первая нормальная форма (1NF) — это свойство отношения в реляционной базе данных. Отношение находится в первой нормальной форме тогда и только тогда, когда ни один домен атрибутов не имеет отношений в качестве элементов. Или более неформально, что ни один столбец таблицы не может иметь таблиц в качестве значений (или повторяющихся групп).) Следующие сценарии сначала иллюстрируют, как дизайн базы данных может нарушать первую нормальную форму, за которыми следуют соответствующие примеры. Конструкции, нарушающие 1NF Эта таблица транзакций по кредитным картам клиентов не соответствует первой нормальной форме: Заказчик Идентификатор клиента Транзакции Абрахам 1 Идентификатор транзакции Дата Сумма 12890 14 октября 2003 −87 12904 15 октября 2003 −50 Исаак 2 Идентификатор транзакции Дата Сумма 12898 14 октября 2003 −21 Джейкоб 3 Идентификатор транзакции Дата Сумма 12907 15 октября 2003 −18 14920 20 ноября 2003 −70 15003 27 ноября 2003 −60 Каждому клиенту соответствует "повторяющаяся группа" транзакций. Такая структура может быть представлена в иерархической базе данных, но не в базе данных SQL, поскольку SQL не поддерживает вложенные таблицы. Автоматизированная оценка любого запроса, связанного с транзакциями клиентов, в целом включает в себя два этапа: 1. Распаковка одной или нескольких групп транзакций клиентов, позволяющая проверять отдельные транзакции в группе, и 2. Получение результата запроса на основе результатов первого этапа Например, чтобы узнать денежную сумму всех транзакций, которые произошли в октябре 2003 года для всех клиентов, система должна знать, что она должна сначала распаковать группу транзакций каждого клиента, а затем суммировать суммы всех транзакций, полученных таким образом, где дата транзакции приходится на октябрь 2003 года. Одной из важных идей Кодда было то, что структурную сложность можно уменьшить. Уменьшенная структурная сложность дает пользователям, приложениям и СУБД больше возможностей и гибкости для формулирования и оценки запросов. Более нормализованный эквивалент приведенной выше структуры может выглядеть следующим образом: Конструкции, соответствующие 1NF Чтобы привести модель к первой нормальной форме, мы можем выполнить нормализацию. Нормализация (до первой нормальной формы) - это процесс, при котором атрибуты с непростыми доменами извлекаются для разделения автономных отношений. Извлеченные отношения дополняются внешними ключами, ссылающимися на первичный ключ отношения, которое его содержало. Процесс может быть применен рекурсивно к непростым доменам, вложенным в несколько уровней. [4] В этом примере идентификатор клиента является первичным ключом содержащих отношений и поэтому будет добавлен в качестве внешнего ключа к новому отношению: Заказчик Идентификатор клиента Абрахам 1 Исаак 2 Джейкоб 3 Идентификатор клиента Идентификатор транзакции Дата Сумма 1 12890 14 октября 2003 −87 1 12904 15 октября 2003 −50 2 12898 14 октября 2003 −21 3 12907 15 октября 2003 −18 3 14920 20 ноября 2003 −70 3 15003 27 ноября 2003 −60 В измененной структуре первичным ключом является {Идентификатор клиента} в первом отношении, {Идентификатор клиента, идентификатор транзакции} во втором отношении. Теперь каждая строка представляет отдельную транзакцию по кредитной карте, и СУБД может получить интересующий ответ, просто найдя все строки с датой, приходящейся на октябрь, и суммируя их суммы. Структура данных размещает все значения на равных основаниях, предоставляя каждое из них напрямую СУБД, поэтому каждое потенциально может напрямую участвовать в запросах; тогда как в предыдущей ситуации некоторые значения были встроены в структуры более низкого уровня, которые должны были обрабатываться особым образом. Соответственно, нормализованный дизайн подходит для обработки запросов общего назначения, в то время как ненормализованный дизайн - нет. Стоит отметить, что эта конструкция отвечает дополнительным требованиям для второй и третьей нормальной формы |