Базы данных / ЛР1_2014

ПМИ	БАЗЫ ДАННЫХ	2014-2015

ЛАБОРАТОРНАЯ РАБОТА № 1

Цель работы: приобретение навыков анализа предметной области и построения модели «сущность-связь».

Задания

1.Изучить приведенный в лабораторной работе пример анализа предметной области и построения ER-модели в MS OFFICE VISIO в нотации IDEF1X.

2.Выполните индивидуальное задание: на основании анализа приведенного описания предметной области создайте список всех сущностей, информацию о которых, скорее всего, потребуется сохранять в базе данных; для каждой сущности определите необходимый список атрибутов; определите список всех связей и их характеристик. Номер варианта, совпадает с номером ФИО студента в учебном журнале.

3.Выполните проектирование ER-модели в MS OFFICE VISIO в нотации IDEF1X.

4.Покажите результат проектирования преподавателю.

5.Ответьте на контрольные вопросы.

Пример выполнения задания

Дано описание предметной области. Выполнить анализ предметной области. Построить модель сущность-связь в MS OFFICE VISIO в нотации IDEF1X.

В гипотетическом пункте обмена валют создается локальная информационная система, призванная автоматизировать процесс учета сделок купли-продажи валюты. Создаваемая система должна обеспечить ввод, хранение и поиск информации о сделках, совершенных в данном пункте обмена. Каждой сделке присваивается уникальный информационный код. Информация о сделке должна содержать сведения о дате и времени сделки, суммах покупаемой и продаваемой валют, фамилии инициалах и учетном номере личного дела кассира. Система должна позволять вычислить денежный оборот за один год или несколько дней, а также осуществлять поиск информации о сделках по паспорту клиента.

Решение

1. Проведем анализ предметной области с целью выделения сущностей. Ясно, что в модели должна присутствовать сущность СДЕЛКА.

Понятие сделки подразумевает участие двух совершающих ее сторон и наличие предмета сделки. Так как участниками сделки является клиент и кассир, а предметом сделки является валюта, необходимо выделить также сущности ВАЛЮТА, КЛИЕНТ, КАССИР.

Перечисленные сущности внесем в ER-диаграмму и выполним описание атрибуты сущностей:

−запустите программу MS OFFICE VISIO;

−выберите из списка Категории шаблонов папку Программное обеспечение и базы данных;

−далее из перечня Готовые шаблоны выберите шаблон Схема модели базы данных – будет создана пустая диаграмма (пустой лист для размещения элементов диаграммы);

Качала Н.М.

Страница 1

ПМИ	БАЗЫ ДАННЫХ	2014-2015

−установите удобный вам размер схемы (например, 100 %);

−выберите драйвер Microsoft SQL Server: пункты меню База данных → Параметры → Драйверы, в окне Драйверы базы данных на закладке

Драйверы выделите Microsoft SQL Server и нажмите кнопку ОК;

−выберите графическую нотацию: База данных →Параметры →Документ →Вкладка Общие →Набор Символов → IDEF1X;

−на Вкладка Общие установите переключатель Имена, видимые на схеме –

концептуальные имена;

−на Вкладка Отношение установите флажки на Отношения и Мощность и нажмите кнопку ОК;

−из контейнера Фигуры перетащите на диаграмму элемент Сущность. Щелкните по нему правой кнопкой мыши и в контекстном меню выберите Свойства базы данных – соответствующее окно будет открыто в нижней части экрана;

−убедитесь, что в списке Категории свойств базы данных указатель стоит на пункте Определение, и введите: Физическое имя – SDELKA,

Концептуальное имя – СДЕЛКА;

−установите указатель на элемент Столбцы;

−убедитесь, что переключатель стоит в положении Физический тип данных (Microsoft SQL Server) и введите информацию о следующих столбцах:

−последовательно разместите на схеме остальные сущности и задайте свойства их атрибутов.

2.Проведем анализ связей (отношений) между сущностями.

Для задания связи между двумя сущностями необходимо указать тип связи, направления связи (родительская и дочерние сущности), мощность связи, допустимость пустых значений (NULL), требования по обеспечению ссылочной целостности, а в некоторых случаях и роль.

Для задания связей между указанными сущностями составим описание предметной области при помощи ряда истинных высказываний на естественном языке:

−любой клиент должен совершить одну или несколько сделок;

−каждую сделку может совершать только один клиент;

−любой кассир может обслуживать одну или несколько сделок, но может не обслуживать и ни одной сделки, например, только принят на работу;

−каждую сделку может обслуживать только один кассир;

−любая валюта может покупаться и продаваться при разных сделках;

−при совершении сделки должна покупаться одна валюта и продаваться другая валюта.

Качала Н.М.

Страница 2

ПМИ	БАЗЫ ДАННЫХ	2014-2015

Анализ проведенных высказываний позволяет выделить четыре связи (название связей – глаголы):

КЛИЕНТ совершает СДЕЛКУ. КАССИР обслуживает сделку. ВАЛЮТА покупается при СДЕЛКЕ. ВАЛЮТА продается при СДЕЛКЕ.

Все четыре связи являются связями типа «один-ко-многим». Во всех четырех случаях сущность СДЕЛКА является дочерней.

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

Все связи, за исключением первой, могут иметь мощность 0,1 или более. Первая связь не может иметь мощность равную 0.

Во всех связей внешние ключи сущности СДЕЛКА не могут принимать пустых значений, т.к. при отсутствии экземпляра хотя бы одной из родительских сущностей экземпляр сущности СДЕЛКА перестает описывать сделку по обмену валюты.

Перечисленные связи внесем в ER-диаграмму и выполним описание атрибуты сущностей

−нажмите на кнопку Соединительная линия в панели инструментов

Стандартная;

−наведите указатель мыши на родительскую таблицу (например, КЛИЕНТ) – таблица будет выделена красным цветом;

−проведите указателем мыши линию от родительской к дочерней таблице СДЕЛКА (если все сделано правильно, то на схеме появится пунктирная линия с жирной точкой со стороны дочерней сущности);

−установите мощности каждой связи: выделите связь и в списке Категории свойств базы данных установите указатель на пункт Прочее – в панели слева от списка категорий.

Витоге схема должна выглядеть следующим образом:

3. Сохраните модель на диске (имя модели должно совпадать с фамилией студента), например, Z:\IvanovER.vsd.

Качала Н.М.

Страница 3

ПМИ	БАЗЫ ДАННЫХ	2014-2015

Варианты заданий

1.Специализированная библиотека располагает книжным фондом определенной тематической направленности. Предполагается, что каждая книга фонда может быть как в одном экземпляре, так и в нескольких. Поэтому каждой книге соответствует уникальный инвентарный номер и библиотечный код. Данные о книге содержатся в библиографической карточке, карточки объединяются в каталоги. Существует два вида каталогов: алфавитный и тематический; в алфавитном каталоге карточки отсортированы по фамилии автора, а в тематическом – сначала по темам, а в пределах каждой темы – по фамилии автора.

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

Создаваемая информационная система предназначена для ведения данных: о книгах (регистрация новых поступлений, списание литературы), о читателях (регистрация новых читателей, удаление информации о выбывших читателях), а также о перемещении книг между библиотекой и читателями. В системе должны быть реализованы возможности просмотра и поиска как среди книг, так и среди читателей; выдача информации о месте нахождения книги: в фонде или у читателя с указанием номера читательского билета.

2.Ателье мод выполняет заказы клиентов на индивидуальный пошив одежды. В ателье существует каталог моделей и каталог тканей. По каталогу моделей клиент выбирает модель, а по каталогу тканей – ткань, из которой будет выполнена модель, и заказывает ее пошив в ателье.

Заказ каждого клиента содержит: Ф.И.О. клиента, информацию о модели (ее номер из каталога моделей), информацию о ткани (номер из каталога тканей), Ф.И.О. закройщика (исполнителя заказа), дату приема заказа, даты примерок, отметку о выполнении заказа, дату выполнения заказа.

В каталоге моделей каждая модель имеет уникальный номер, для каждой модели указывается рекомендуемая ткань, необходимый расход ткани для данной модели с учетом ширины ткани, цена готовой модели, включающая цену ткани и стоимость пошива изделия.

В каталоге тканей каждая ткань имеет уникальный номер, название, а также указываются ее ширина и цена за 1 метр.

В ателье есть склад тканей. В книге учета тканей на складе для каждой ткани указывается общий метраж, который изменяется, если принимается заказ на изготовление модели из данной ткани. При отсутствии ткани в достаточном количестве для реализации модели заказ на эту модель не принимается.

В системе должно быть реализовано: хранение каталогов тканей и моделей, документов по заказам; контроль наличия ткани на складе; вычисление общей стоимости заказов каждого закройщика за указанный период.

3.Различные страховые компании заключают договоры с больницей на стационарное лечение застрахованных лиц. Пациент с направлением, выданным врачомэкспертом страховой компании, обращается в приемное отделение больницы для госпитализации. Наряду с застрахованными пациентами госпитализируются и граждане, не имеющие полисов.

Качала Н.М.

Страница 4

ПМИ	БАЗЫ ДАННЫХ	2014-2015

Вприемном отделении на обратившегося заводится история болезни. На каждого пациента заводится одна история болезни, имеющая уникальный номер. При каждом повторном обращении вносятся дополнительные записи. Кроме диагноза, в истории болезни отражаются ФИО и возраст больного, серия и номер паспорта, дата поступления

идата выписки, диагноз, отделение, страховая компания, номер полиса. Перевод в другое отделение сопровождается новой записью в истории болезни. Во время прохождения лечения страховая компания не меняется, но при повторном обращении пациент может быть застрахован в другой страховой компании.

Два раза в месяц (5 и 20 числа) формируются реестры пролеченных больных и счета-фактуры для страховых компаний, клиенты которых выписались в данный период времени (либо с 1 по 15 число, либо с 16 числа и до конца месяца).

Впроектируемой системе должны поддерживаться следующие функции:

ввод информации о поступающих пациентах, просмотр и редактирование информации о поступивших и выписанных;

поиск информации о пациенте по номеру истории болезни, ФИО, паспорту;

ведение справочника страховых компаний, содержащего их название, серию полиса, адрес, банковские реквизиты;

ведение справочника медико-экономического стандарта (МЭС), отражающего код диагноза по МЭС, профиль диагноза, наименование диагноза, срок лечения. В каждый профиль (например, кардиологический) входит несколько диагнозов (например, инфаркт, стенокардия …);

справочник тарифов по отделениям (тариф одного койко-дня в отделении не зависит от диагноза, считать, что тариф не меняется во времени).

4. Некая организация-исполнитель работает по договорам с другими организациями.

Вдоговоре указываются предмет договора, стоимость договора, сроки выполнения работ, адреса и банковские реквизиты сторон (для упрощения – учесть только расчетный счет), размер предоплаты. Остальные условия договора стандартные и не хранятся в базе данных в качестве атрибутов договора.

Договор между организацией-исполнителем и организацией-заказчиком подписывают первые руководители предприятий.

Каждая организация-заказчик может иметь несколько договоров. Каждый договор имеет уникальный регистрационный номер.

Договор сопровождается календарным планом, в котором для каждого этапа работ указываются название, сроки выполнения, стоимость этапа и ожидаемые результаты. Предполагается, что каждый договор включает несколько этапов.

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

Впроектируемой информационной системе должны поддерживаться следующие

функции:

хранение информации о договорах;

контроль выполнения сроков календарного плана;

подготовка актов приемки-сдачи работ;

контроль поступления денежных сумм от заказчика.

Качала Н.М.

Страница 5

ПМИ	БАЗЫ ДАННЫХ	2014-2015

5. База данных создается для некой компании, занимающейся издательской деятельностью. Компания имеет три дочерних издательства.

Изданию каждой книги предшествует подписание издательством контракта с автором или коллективом авторов. Книга может быть издана только в издательстве, подписавшем контракт. В контракте оговаривается размер гонорара (другие возможные атрибуты контракта не учитывать).

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

Книга для издания принимается после редактирования. Один и тот же редактор может работать над несколькими книгами. В каждой книге может быть несколько редакторов.

В проектируемой информационной системе должны поддерживаться следующие функции:

хранение информации о выпускаемых книгах (название, тираж, цена, дата выхода, издательство) и их авторах (ФИО, адрес, телефон);

проведение анализа деятельности каждого издательства: какие книги изданы каждым издательством, каков их общий тираж и т.д.;

хранение данных о редакторах (ФИО, адрес, телефон, а также информацию о книгах, которые они редактировали);

учет подписанных контрактов и расчет гонораров авторам.

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

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

Вкаждой группе тренируется не менее 5 и не более 10 человек.

Врасписании отражается номер группы, день недели, время занятий, фамилия тренера. Каждая группа может заниматься несколько раз в неделю, время занятий при этом одно и то же.

Каждый клиент может приобрести несколько абонементов в разные группы. Стоимость абонемента зависит от вида занятий и времени их проведения.

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

Тренер может работать с несколькими группами в соответствии с расписанием. В БД должна храниться информация и о тренерах, которые на текущий момент не ведут ни одной группы.

Система должна поддерживать следующие функции:

удовлетворение потребностей администратора в информации;

учет количества проданных абонементов и суммы вырученных средств.

7. Разрабатывается информационная система для оптовой торговой фирмы. В проектируемой системе должны поддерживаться следующие функции:

хранение информации о покупателях;

печать накладных на отпущенные товары;

контроль наличия товаров на складе.

Фирма имеет несколько складов. Каждый склад имеет свое наименование.

Каждый товар может храниться на нескольких складах и быть проданным с любого склада. Каждый товар имеет наименование, текущую цену, характеризуется единицей

Качала Н.М.

Страница 6

ПМИ	БАЗЫ ДАННЫХ	2014-2015

измерений. Цена одно и того же вида товара может быть разной в зависимости от времени поступления на склад. Цена одного и того же товара в разных накладных, выписанных в разное время, может быть различной.

Покупатели покупают товар, получая при этом накладные, в которые внесены данные о количестве и цене купленного товара, общей сумме, дате выписки накладной. Номер накладной уникален в пределах склада. На разных складах может быть одинаковые номера накладных.

Каждый покупатель может получить несколько накладных. Каждая накладная обязана выписываться на одного покупателя. Каждая накладная должна содержать хотя бы один товар. Каждый товар может быть продан нескольким покупателям по нескольким накладным.

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

8. Клиент может открыть счет в отделении банка. При открытии первого счета клиенту присваивается идентификационный номер (ИНК). При открытии последующих счетов ИНК не изменяется.

Банк ведет чековые счета (счета, на которые клиент может выписывать чеки), сберегательные счета (клиент имеет сберегательные книжки, а банк выплачивает проценты), счета ссуд (клиенты должны возвратить банку ссуду с процентами) и счета по закладным (клиенты должны делать регулярные вклады с процентами).

Номер счета является уникальным. Клиент может иметь несколько счетов с различными номерами в одном отделении банка.

Номер счета в совокупности с датой, временем и типом операции однозначно определяют банковскую операцию.

Ежедневно для управляющего отделения банка подготавливается «Отчет управляющего отделением (особые случаи)» (см. рис.). Этот отчет содержит сведения лишь об особых операциях, например, когда превышен остаток на счете ссуд с

автоматическим переводом средств или когда остаток на сберегательном счете превышает 100 тыс. долларов.

Выполнить проектирование схемы БД, позволяющей составить этот отчет.

Качала Н.М.

Страница 7

ПМИ	БАЗЫ ДАННЫХ	2014-2015

9. Фирма занимается ремонтом квартир. Разрабатывается база данных для работы с клиентами.

С каждым клиентом фирма заключает договор, в который вносятся данные о клиенте (фамилия, имя, отчество, адрес, контактный телефон, факс), данные о ремонтируемом помещении (адрес, телефон), даты начала и окончания работы, стоимость работ. Одновременно с договором составляется смета с указанием видов работ, их объема и стоимости, материалов, количества и стоимости материалов. Стоимость работ по договору не меняется.

Каждый клиент может заключить несколько договоров по ремонту разных помещений или одного и того же помещения.

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

Материалы для ремонта берутся из имеющихся на складе фирмы. Каждый материал имеет свой идентификационный код. Если подобный материал поступает на склад по новой цене, то вновь поступивший материал получает новый идентификационный код. Допускается при выполнении работ по одному договору использовать один и тот же материал по разным ценам. При заключении договора в складских документах в соответствии с планируемым использованием оперативно уменьшается количество материалов.

Работы по договору завершаются актом приемки-сдачи, где указываются фактическая дата окончания работ. Каждый договор закрывается одним актом.

Договор и акт хранятся в течение двух лет. Отсчет времени идет с момента подписания акта приемки-сдачи.

Впроектируемой системе должны поддерживаться следующие функции:

учет заключенных договоров;

учет претензий по выполненным работам;

учет материалов на складе;

определение доходов фирмы за различные периоды;

анализ соответствия фактических и договорных сроков выполнения работ.

10.Фирма занимается приготовлением блюд на заказ для ответственных приемов

иторжественных событий. Фирма обслуживает жителей города и ближайших пригородов. У фирмы есть несколько постоянных клиентов, иногда появляются те, кто впервые

обращается на фирму.

Каждый клиент может делать несколько заказов, и каждый заказ регистрируется только на одного клиента. Каждый заказ оплачивается по отдельному счету, в котором указывается номер заказа и сумма к оплате. Как правило, оплата осуществляется одновременно с заказом. Постоянные клиенты могут сделать заказ по телефону с последующей оплатой.

Каждого клиента фирма обязательно регистрирует (имя клиента, адрес, включая почтовый индекс, город, улицу, дом, телефон). Каждый заказ регистрируется под своим номером, в заказе указывается дата торжества, количество приглашенных гостей, блюда, заказанные клиентом, а также требуемое количество.

Каждый заказ содержит несколько блюд. Каждое блюдо может быть заказано несколькими клиентами. Смета на каждый заказ рассчитывается на основании следующих данных: тип блюда и стоимости одной порции, полученная сумма умножается на требуемое их количество. Эта информация заносится в заказ как сметная стоимость.

Качала Н.М.

Страница 8

ПМИ	БАЗЫ ДАННЫХ	2014-2015

Каждый заказ может требовать труда одного или нескольких работников. Каждый работник может быть занят одним или несколькими заказами. Двадцать процентов прибыли с каждого заказа выплачивается тому сотруднику, который выполнял работу. Если заказом занимались несколько работников, то двадцать процентов делятся между ними. Зарплата каждому работнику начисляется согласно его занятости на заказах.

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

11. Судоходная компания оказывает услуги по перевозке грузов в контейнерах. Каждый контейнер имеет номер и своего владельца, параметры контейнера:

габаритные размеры (высота, ширина, длина), вес и максимальный вес груза.

На каждый контейнер, погруженный на судно, составляется транспортная накладная. В транспортной накладной указывается номер контейнера, номер партии товара, грузоотправитель, грузополучатель и агент. В одну и туже партию может входить несколько различных видов товаров, для каждого из которых указывается код, количество, стоимость, вес.

Разработать базу данных, обеспечивающую руководство фирмы оперативной информацией:

омаршруте каждого судна (судно, номер рейса, порт, дата прибытия, дата отправления);

оданных погрузочных работ: какие контейнеры, взяты или выгружены в каждом порту, особенности погрузочных работ;

околичестве груза, в весовом выражении, перевезенным каждым судном за рейс и

вцелом за текущий год.

Номер рейса указывает текущий год, номер рейса за год и направление рейса (например, 01403W – третий рейс в 2014 году в западном направлении). Судно в базе данных задается бортовым номером, названием и кратким описанием.

12. Фирма занимается выставочной деятельностью. Необходимо разработать базу данных для информационной поддержки процесса организации выставок и анализа выставочной деятельности.

Менеджер выставки согласно объявленной тематике выставки собирает и регистрирует заявки предполагаемых предприятий-участников. По каждой заявке составляется договор, в соответствии с которым участнику выставляется счет. Однако некоторые заявки не заканчиваются составлением договора. Договор содержит следующие атрибуты: номер договора, название организации-участника, ИНН, банковские реквизиты, номер выставочной экспозиции и площадь экспозиции. Разрешение на экспозицию выдается после полной оплаты участия в выставке. За участником остается право отказаться от выставки уже после проведенной оплаты, но сумма возвращается ему не полностью, а с вычетом процентов. Размер процентов зависит от срока до начала выставки.

В приложении к договору каждый участник представляет перечень экспонатов с указанием их стоимости и группы. Информация о каждом экспонате заносится в базу данных.

Система должна выдавать информацию по следующим запросам: список зарегистрированных заявок определенную дату;

список заключенных договоров на определенную дату с указанием предприятияучастника;

Качала Н.М.

Страница 9

ПМИ	БАЗЫ ДАННЫХ	2014-2015

список оплаченных договоров на определенную дату с указанием предприятияучастника и стоимости;

список оплаченных договоров, по которым имеется отказ от участия, дата отказа и удержанная сумма;

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

Контрольные вопросы

1.Что означает аббревиатура IDEF1X?

2.На каком этапе жизненного цикла базы данных строится модель «сущность-связь»?

3.Для чего предназначена модель «сущность-связь»?

4.Что называется типом сущности?

5.Приведите примеры экземпляров сущности для рассматриваемой в индивидуальном задании предметной области.

6.Какая связь назевается идентифицирующей?

7.Укажите назначение первичного ключа отношения.

8.Перечислите требования, которым должен удовлетворять первичный ключ.

9.Каким образом можно задать в Visio параметры документа базы данных?

10.Что называется мощностью связи и как она задается в Visio?

11.Укажите, какие связи в построенной вами модели являются необязательными?

Качала Н.М.

Страница 10