Проектирование базы данных

Проектирование информационной системы на основе базы данных. Проектирование   информационной   системы   базы   данных   основывается   на   исследовании информации, циркулирующей внутри данной предметной области. Предметная область (ПО) – это совокупность объектов, процессов и связей между ними ( банк, завод, склад и т. д.).   При     обследовании   ПО   выполняется   семантический   анализ   информации   данной предметной области. Семантика – это смысловая сторона информации. Рассмотрим   некоторые   понятия,   необходимые   для   описания   исследуемой   предметной области. Информационный объект(ИО)  ­   источник информации. он может быть материальным ( цех, склад,  документ …) и нематериальным  ( факты, события,  процессы,  явления...). Информационный   объект   –   это   любая   реальная   или   абстрактная   сущность,   о   которой накапливается информация.  Сведения об объектах могут поступать из различных сообщений и документов. Каждый объект характеризуется набором атрибутов. Атрибуты – это свойства, которыми обладает данный объект. Атрибут является простейшей неделимой единицей информации, которая отражает   количественную   или   качественную   характеристику   объекта.   Сведения   об атрибутах   получают   на   этапе   предпроектного   обследования.   Здесь   же   учитываются ограничения   и   допущения.   Состав   атрибутов   представляет   собой   структуру информационного объекта. Атрибуты, которые однозначно определяют каждый экземпляр объекта, являются  ключевыми. По значению ключа можно отыскать нужный экземпляр объекта. Другие атрибуты объекта называются неключевыми или описательными. Ключ и описательные   атрибуты   находятся   в   функциональной   зависимости.   Ключ   может   быть простым  и  составным.   Например   в   объекте   «студент»   будет   составной   ключ: номер_группы + номер_студента Информация   в   реляционных   базах   данных     хранится   в   нескольких   взаимосвязанных двумерных таблицах, в каждой из которых находится информация об одном объекте.  Между всеми объектами, выявленными в предпроектном проектировании, надо установить типы связей. Связи могут быть «один – к ­ одному» ( 1:1), «один – ко ­ многим» ( 1:N), «многие – ко ­ многим» (N:N). Связь «один – к ­ одному» имеет место, когда каждому экземпляру одного объектного множества соответствует только один экземпляр другого объектного множества. человек паспорт 1:1 Связь «один – ко ­ многим» имеет место, когда каждому экземпляру одного объектного множества соответствует несколько экземпляров другого объектного множества. классификация товар 1:N Этот тип связи наиболее часто используется в концептуальной модели. Связь «один – ко ­ многим» имеет место, когда каждому экземпляру одного объектного множества   соответствует   несколько   экземпляров   другого   объектного   множества.   И наоборот, каждому экземпляру второго объектного множества соответствует несколько экземпляров первого объектного множества. 2 поставщик товар N:N На основании выявленных связей строится концептуальная модель. 3 Пример разработки информационной системы 1. Анализ предметной области. 1.1. Описание предметной области и функции решаемых задач. В   качестве   примера   рассмотрим   упрощённый   вариант   построения   информационной системы для работы туристического агентства. Предметной   областью   автоматизации   являются   должностные   функции   менеджера   по продажам путёвок. Сотрудники оформляют тур. путёвки и путёвки в санаторий для клиентов.  1.2 Перечень входных данных. Входную   информацию   делят   на   условно­постоянную,   сохраняющую   свои   значения   на длительный период времени, и, на постоянно меняющуюся оперативно­учётную. В результате обследования предметной области определены входные данные, необходимые для решения комплекса задач: Тур.путёвки, путёвки в санаторий, информация о клиентах, информация о странах. Поэтому   при   разработке   базы   данных,   необходимо   создать   формы   для   ввода   этой информации. Входная информация может быть представлена следующими документами:  Клиенты Код клиента … … ФИО …  Тур. Путёвка Таблица 1. Входные данные по клиенту Телефон Адрес … Таблица 2. Входные данные по путёвкам Номер путёвки … Начало тура … Продолжи тельность … Стоимость Название тура … Примечание: по возможности входная информация представляется в графическом виде  1.3. Перечень выходных данных. Выходная информация представляется в виде отчётов.  Туры по странам;  Самый дешёвый тур;  Неоплаченные путёвки; Выходную информацию представим в виде отчётных форм: не оплаченные путёвки Клиенты Смирнов Д.А. № тура Золотой песок Оплата Не оплачено Стоимость 500 4 … … … … самый дешёвый тур Страна Начало  Продолжитель №  тура ность тура тура Название  тура Транспорт Стои Колич мост ство 1.4. Ограничения предметной области. По рассматриваемой предметной области введём некоторые ограничения:  В таблице «путёвки» значение поля «стоимость» должно быть больше нуля.  В таблице «путёвки» значение поля «количество» должно быть больше нуля.  В   таблице   «путёвки   в   санаторий»   значение   поля   «дата   окончания   пребывания» должна вычисляться как сумма «даты начала» и «продолжительности». 1.5. Взаимодействие с другими программами. Представленная информационная система должна выводить отчёты в текстовый редактор MS Word. Прайс­лист о путёвках может выводится в MS Excel. 2. Постановка задачи Разрабатываемая   информационная   система   предназначена   для   структурированного хранения данных и вывода информации о турах по странам, путёвках туров, клиентах Разрабатываемая информационная система должна выполнять следующие функции:  Добавление информации о новом туре.  Добавление путёвки к туру.  Оформление и возврат путёвок.  Осуществлять поиск тура по стране и виду транспорта  Просмотр реализованных путёвок по санаториям.  Оформление путёвки в санаторий.  Определять туры с просроченной датой.  Путёвки в санаторий с истекшей датой заезда.  Показать самый популярный тур.  Показать  самый дешёвый тур 3. Проектирование концептуальной модели 3.1 Выделение информационных объектов. Одним из первых объектов предметной области можно выделить «тур путёвки» и «путёвки в   санаторий».   Далее   определяем,   кто   оформляет   путёвки,   для   этого   вводим   объект «сотрудник». Путёвки покупаются клиентами ( объект «клиент»).Тур путёвки продаются в разные   страны,   отсюда     объект   «страна».   Переезд   в   туристических   путёвках осуществляется транспортом, поэтому возникает необходимость в объекте «транспорт». 5 Клиентам необходимо представлять список санаториев, куда он может поехать (объект «санаторий» ). В санаториях выполняют лечение заболеваний (объект «заболевание»). Объект  «оформление  путёвки»  является сложным,  так как  включает  в себя несколько объектов: «клиенты», «путёвки», «сотрудники» 3.2 Определение атрибутов объектов. Рассмотрим атрибуты перечисленных объектов. 6 Таблица 3. Атрибуты объектов Объект Атрибуты объектов тур путёвки Оформление путёвки Санаторий Заболевания Путёвки санаторий   в   Начало   тура,   Окончание   тура, №   тура,   Стоимость, Кол­во путевок № путёвки,  № тура, Оплата санаторий, адрес, город заболевание код   поступления, прибытия, путёвок   дата   кол­во   санаторий,   продолжительность, Ключевой атрибут № тура № путёвки санаторий заболевание код поступления Необходимо   проанализировать   каждый   атрибут   на   наличие   взаимосвязей   с   другими реквизитами   объекта.   Реквизит   приобретает   смысл,   только   тогда,   когда   он   связан   с другими атрибутами, обладающими смысловым единством. 3.3 Определение отношений и мощности отношений между объектами. Рассмотрим взаимосвязи между объектами и мощности отношение и построим матрицу отношений. Страны ­>Путёвки. «Страны» главный объект, а «путёвки» подчинённый объект. Тип связи «один ко многим». Так как в одну страну может быть несколько путёвок. Связь между этими объектами осуществляет атрибут «код_страны» Страны Путёвки 1:N Транспорт  ­>Путёвки. «Транспорт» главный объект, а «путёвки» подчинённый объект. Тип связи   «один   ко   многим».   Один   транспорт   может   использоваться   для   переезда   по нескольким   путёвкам.   Связь   между   этими   объектами   осуществляет   атрибут «код_транспорта» Транспорт Путёвки 1:N ТурПутёвки ­>ОформлениеПутёвки. «ТурПутёвки» главный объект, а «оформление путёвки» подчинённый   объект.   Тип   связи   «один   ко   многим».   Один   тур   может   быть   оформлен несколько раз. Связь между этими объектами осуществляет атрибут «номер_тура» ТурПутёвки ОформлениеПутёвки 1:N И так описываются все отношения предметной области. Составим матрицу смежности для рассматриваемой предметной области. По строкам и столбцам записываем выделенные объекты предметной области. Если между объектами существуют   отношения,   то   на   пересечении   строки   и   столбца   ставится   мощность отношения. Если объекты не взаимодействуют, то указывается 0. 7 Таблица 4. Матрица смежности. ы н а р т С т р о п с н а р Т Страны Транспорт Путёвки … 0 0 0 0 0 0 е и н е л м р о ф О и к в ё т у п 0 0 1:N . . … и к в ё т у П 1:N 1:N 0 3.4 Построение схемы концептуальной модели. На основе полученных объектов, атрибутов объектов и отношений между ними, можно построить концептуальную модель. Часть 1 Оформление путёвки Код страны Страны наименование  страны Код трансп Транспорт Вид трансп  страны Название тура Код клиента ФИО Начало тура Клиенты Путёвки Продол тура № тура Сотрудники Код сотруд ФИО № путёвки оплата страны Рис 1. Концептуальная модель часть1 8 Оформление путёвки в санаторий Заболевание Код клиента ФИО Код сотруд Заболевание Клиенты Сотрудники ФИО Путёвки в санаторий Код поступ Дата приб санаторий оплата страны № путёвки Санаторий адрес Санаторий Рис 1. Концептуальная модель часть2 9 4.   Разработка логической структуры базы данных. Логическая структура реляционной базы данных определяется совокупностью логически связанных реляционных таблиц.  Логические связи соответствуют структурным связям между объектами в концептуальной модели, каждый объект в логической модели отображается соответствующей реляционной таблицей. Связи   между   таблицами   осуществляются   посредством   общих   атрибутов.   Логическая структура реляционной базы данных имеет вид: Страна Транспорт К о д _ с т р а н ы н а и м е н о в а н и е К о д _ с т р а н ы т а К о д _ т р а н с п о р В и д   т р а н с п о р т а Путёвки №   т у р а Н а з в   т у р а Н а ч а л о   т у р а П р о д   т у р а т а К о д _ т р а н с п о р ОформлениеПутёвки К о д _ к л и е н т а   № п у т ё в к и К о д _ с о т р у д Н а ч а л о   т у р а П р о д   т у р а №   т у р а О п л а т а Рис 3. Логическая структура реляционной базы 5. Реляционная модель. Вначале раздела приводится теоретическая информация о реляционной модели. Реляционная модель проектируемой базы данных: Клиенты (Код клиента, ФИО) 10 Сотрудники ( Код сотрудника, ФИО, код фирмы) Внешний ключ: код фирмы Страны ( Код страны, название страны) Транспорт (код транспорта, вид транспорта) Путевки (№ тура, Код страны, Начало тура, Окончание тура, Код транспорта, Стоимость, Кол­во путевок) Внешний ключ: Код транспорта Внешний ключ: Код страны Оформление путёвки (№ путёвки, Код клиента, Код сотрудника, № тура, Оплата) Внешний ключ: Код клиента Внешний ключ: Код сотрудника Санаторий (санаторий, адрес, город) Заболевания (заболевание) Заболнвание_и_санаторий (санаторий, заболевание) Путёвки в санаторий  (код поступления, санаторий, дата прибытия, продолжительность, кол­во путёвок) Внешний ключ: санаторий Оформление   путёвки   в   санаторий  (№   путёвки,   код   клиента,     код   сотрудника,   код поступления, оплата) Внешний ключ: код клиента Внешний ключ: код сотрудника Внешний ключ: код поступления 6. Определение типов данных в заданном формате Таблица «Клиенты» Содержит информацию о клиентах, приобретавших путёвки. Таблица 5 Структура таблицы данных «Клиент» Тип поля Размер поля Обязательное поле Ключевое поле Числовой Текстовое Длинное целое 50 Да Нет Да Нет Наименование поля Код клиента ФИО Таблица «Сотрудники» Содержит информацию о сотрудниках, работающих на фирме. Наименование Тип поля Размер поля Обязательное поле Ключевое поле Таблица 6 Структура таблицы данных «Сотрудники» 11 поля Код сотрудника Числовой ФИО Текстовое Длинное целое 50 Да Нет Да Нет 12 7. Создание глобальной схемы связей. Поддержка целостности данных. Раздел содержит теоретические сведения: о глобальной схеме данных, о поддержании ссылочной целостности, о добавлении данных в подчинённую таблицу. Рис 4. Схема данных. 8. Запросы. Структура и назначение. SQL – запрос Вначале раздела приводятся теоретические сведения о запросах, типах запросов, конструкторе запросов. Далее описываются все запросы информационной системы. Запрос «Количество путёвок клиента» Запрос отображает информацию о количестве путёвок, приобретённых каждым клиентом Запрос создан на основе таблиц «Оформление путёвки», «Клиенты» 13 Рис 5  Структура запроса «Количество путёвок клиента» Запрос в режиме SQL: SELECT   клиенты.ФИО,   Count([оформление   путевки].[№   тура])   AS [Count­№ тура] FROM   клиенты   INNER   JOIN   [оформление   путевки]   ON   клиенты.[Код клиента] = [оформление путевки].[Код клиента] GROUP BY клиенты.ФИО; Запрос отображает информацию о клиентах, не оплативших путёвки. Запрос построен на основе таблиц «Оформление путёвки», «путёвки»  Запрос «Неоплаченные туры» Рис 6  Структура запроса «Неоплаченные туры» Запрос в режиме SQL SELECT [оформление путевки].[Код клиента], [оформление путевки]. [№ тура], [оформление путевки].Оплата, путёвки.[Название тура],  путёвки.Стоимость FROM путёвки INNER JOIN [оформление путевки] ON путёвки.[№ тура] = [оформление путевки].[№ тура] WHERE ((([оформление путевки].Оплата)="Не оплачено")); 9. Проектирование форм. Структура и назначение существующих форм. 14 Access предоставляет широкие возможности для конструирования форм – графического и диалогового интерфейса пользователя. Формы являются основой разработки диалоговых приложений. Приведите теоретические сведения о разработке форм с помощью конструктора и мастера. Рассмотрим формы, спроектированные в данной базе. Форма «Клиенты» Форма   создана   для   просмотра   клиентов   и   приобретённых   ими   путёвок.   Также   можно оформит новую путёвку для клиента, нажав кнопку «Оформить путёвку»  Данная форма состоит из основной части, где отображаются поля из таблицы «клиенты» и подчинённой, где отображаются поля из таблицы «оформление путёвок» Для перемещения по записям клиентов используются кнопки Рис 7.  Структура подчинённой формы Форма «Оформление путёвки» Форма создана для формирования новой записи о приобретении клиентом путёвки. Рис 8  Структура формы «Оформление путёвки» Поле 1­ «№ путёвки» заполняется автоматически на основе запроса «максимальный номер путёвки_2». Поле не редактируется пользователем. Поле     2   ­   поле   со   списком   «ФИО»   формируется   на   основе   таблицы   «клиенты».   Оно позволяет выбрать фамилию клиента, для которого оформляется путёвка. Поле 3 ­ поле со списком  «Название тура» формируется на основе запроса «с количеством больше 0». Оно позволяет выбирать туры, для которых количество путёвок больше 0. 15 Поле   4­ поле со списком «ФИО» формируется на основе таблицы «сотрудники». Оно позволяет выбрать фамилию сотрудника, который оформляет путёвку Поле 5 со списком «оплата» формируется на основе на основе фиксированного набора значений: Оплачен, НЕ оплачено. В   примечании   формы   находится   кнопка,   которая   запускает   макрос   «Уменьшить количество путёвок». После нажатия на кнопку пользователю будет предложено изменить данные. В случае согласия добавляется новая запись в таблицу «оформление путёвок» и уменьшается количество путёвок в туре на 1. Форма «Путёвки» Форма создана только для просмотра туров. Форма содержит следующие поля: Рис 9 Структура формы «Путёвки» В поле 1 выбирается из списка страна. Поле 2 предназначено для ввода даты начала тура. Поле 3 предназначено для ввода количества дней тура. Поле 4 предназначено для ввода номера тура. Поле 5 предназначено для ввода названия тура. В поле 6 выбирается из списка транспорт тура. Поле 5 предназначено для ввода стоимости тура. Поле 8 предназначено для ввода количества путёвок. Кнопки 9, 10 выполняют движение по записям. Кнопка 11 позволяет просмотреть отчёт  ‘Туры’ Кнопка 12 предназначена для закрытия формы. 10. Структура отчетов. Средства   разработки   отчётов   в  Access  предназначены   для   создания   макета   отчёта,   по которому может быть осуществлён вывод данных в виде выходного печатного документа. Эти   средства   позволяют   создавать   отчёт   сложной   структуры,   обеспечивающий   вывод взаимосвязанных   данных   из   многих   таблиц,   запросов,   их   группировку,   вычисление итоговых данных. Приведите теоретические сведения о отчётах. При помощи мастера  созданы следующие отчеты. Отчёт «Не оплаченные путёвки» 16 Отчёт создан на основе запроса «Не оплаченные туры». Он отображает информацию о путевках,   которые   не   были   оплачены   клиентами.   Отчёт   содержит   разделы:   заголовок отчёта, верхний колонтитул, нижний колонтитул, область данных. Верхний колонтитул содержит надписи полей: клиенты, № тура, оплата, стоимость. Область данных содержит поля запроса: код клиента, № тура, оплата, стоимость. Нижний колонтитул содержит функцию для определения даты и номера страницы Рис 10  Вид отчёта в конструкторе «неоплаченные путёвки» 11. Макросы. Назначение и алгоритм работы. Макрос представляет  набор макрокоманд,  который создается  для автоматизации  часто выполняемых задач. Группа макросов позволяет выполнить несколько задач одновременно. Макросы могут быть полезны для автоматизации часто выполняемых задач. Приведите теоретические сведения о макросах. Макрос «Уменьшить количество путёвок» Макрос выполняет следующие действия:    добавляет   запись   в   таблицу   «оформление   путёвок»   ­   Открыть   запрос «оформить путёвку» уменьшает количество путёвок для заданного тура на 1 ­ Открыть запрос «уменьшить кол путёвок» закрывает форму «оформление путёвок» Макрос с условием. Пользователю выводится сообщение, которое просит подтверждение об изменении  данных. Если  пользователь  выбрал «Да», то выполняются  перечисленные действия. 17 Рис 11  Вид макроса «уменьшить кол путёвок» в конструкторе Макрос «условие тура» Макрос с условием.  Если количество записей тура (занесено в поле формы «количество стран по запросу»), соответствующих параметрам поиска больше 0, то    запускается форма «туры страны и транспорта»  и закрывается форма «количество стран по запросу» Если количество записей тура равно 0, то    выводится сообщение пользователю; и закрывается форма «количество стран по запросу» Рис 12  Вид макроса «условие тура» в конструкторе 12. Структура главной кнопочной формы. Распределение функций информационной системы по страницам главной кнопочной формы. Рис 13 Первая страница главной кнопочной формы 18 1. Сведения о турах (кнопка ‘Информация о турах’) 1.1 Новый тур. 1.2 Добавить путёвку к туру. 1.3 Оформление и возврат путёвок 1.4 Туры в страны. 1.5 Поиск тура по стране и виду транспорта. 1.6 Туры и проданные путёвки. 2. Санаторно­курортное лечение (кнопка ‘Отдых в санаториях’). 2.1 Заболевания. 2.2 Санатории. 2.3 Лечение заболеваний в санаториях. 2.4 Путёвки в санатории. 2.5 Просмотр реализованных путёвок по санаториям. 2.6 Оформление путёвки в санаторий. 3. Информация для клиентов по тур. путёвкам. ( кнопка ‘Клиенты’). 3.1 Клиенты и туры. 3.2 Неоплаченные путёвки. 3.3 Список клиентов. 4. Сотрудники. (кнопка ‘Сотрудники’). 4.1 Список сотрудников. 5. Статистика (кнопка ‘Статистика’). 5.1 Туры с просроченной датой. 5.2 Путёвки в санаторий с истекшей датой заезда. 5.3 Самый популярный тур. 5.4 Самый дешёвый тур 5.5 Наиболее частые клиенты 13. Руководство пользователя. Разработанная информационная система предназначена для структурированного хранения данных и вывода информации о турах по странам, путёвках туров, клиентах.  Для работы с представленной базой данных необходимы: 1. Процессор не ниже Pentium 133Mhz. 2. Операционная система Windows. 3. Microsoft Access.  Описание последовательности действий по реализации функций системы  Добавить новый тур  Откройте   главную   кнопочную   форму.   Выберите   кнопку  «Информация   о  турах».   Вы перейдёте на следующую страницу ‘Сведения о турах’ Щёлкните на кнопке «Новый тур» На экран отображается форма.  После   ввода   информации,   нажмите   на   кнопке   с   изображением   дискеты.   Запись будет   сохранена.   При   выборе   страны   и   вида   транспорта   используйте раскрывающийся список.  Добавить путёвки к уже существующему туру. 19 Откройте   главную   кнопочную   форму.   Выберите   кнопку  «Информация   о  турах».   Вы перейдёте на следующую страницу ‘Сведения о турах’ Щёлкните на кнопке «Добавить путёвки к туру» На экране отображается форма Рис 14 Форма «Добавить путёвки к туру» Поле 1 – позволяет выбрать наименование тура Поле 2 – предназначено для ввода количества путёвок. После заполнения полей нажмите на кнопке «Добавить путёвки к туру»  Далее описываются все страницы и кнопки Главной кнопочной формы. Проделанная   работа   позволяет   любому   пользователю   хранить   большие   объёмы информации, обрабатывать их, сортировать, делать выборки по определённым критериям.  14. Заключение. Разработанная база данных может быть использована в работе туристического бюро. 16.1 Экранные формы проекта. 16.Графическое приложение. Форма «Добавить путёвки к туру» 20 Форма «Туры в страны» 21 16.2 Отчёты Лечение заболеваний в санаториях заболевания санаторий дата  продолжи количе стоимос прибыти тельность ство ть аллергия глаукома дерматология желудочно­кишечного  тракта желчекаменная болезнь катаракта ЛОР ­ органы Русь 20.06.2006 20 9 20000 Пикет 20.06.2006 20 90 10000 Жемчужина 20.07.2006 10 4 0 им. 30­летия Победы 01.07.2007 Центральный военный  санаторий 15.07.2006 ВИКТОРИЯ 20.06.2006 Эльбрус 15.07.2006 Жемчужина 20.07.2006 21 21 21 21 10 10 10 18 25 4 22000 21000 25000 19000 0 Пикет 20.06.2006 20 90 10000 Эльбрус 15.07.2006 21 25 19000 6 июня 2006 г. Страница 1 из 3 22 Литература. 23 1. Хансен   Г.,   Хансен   Дж.   Базы   данных:   разработка   и   управление.­М.:   ЗАО "Издательство БИНОМ", 1999, 300 с. 2. Дейт, К., Дж. Введение в системы баз данных. ­СПб.: Издательский дом "Вильяме", 3. Горев А., Ахаян Р., Макашарипов С. Эффективная работа с СУБД­СПб.:Питер, 4. Мартин   Дж.   Организация   баз   данных   в   вычислительных   системах.­Издательство 5. Киреева   Е.В.,   Кусков   А.Н.   Реляционная   алгебра   и   реляционное   исчисление. 1999, 520 с. 1997, 440 с. "Мир", 1980, 600 с. Уч.пособие. 2000г., 40 с. 6. Киреева Е.В. Дореляционные модели данных. Уч.пособие. 2000 г. 35 с. 7. Киреева Е.В., Кусков А.Н. Реляционная модель. Нормализация. Уч.пособие. 2000 г. 8. Дейт, К., Дж. Введение в системы баз данных. ­СПб.: Издательский дом "Вильяме", 42 с. 1999. 350 с. 9. Киреева Е.В. Физическая организация баз данных. Уч.пособие. 2001г. 10. Надеждина   О.В.   методические   указания   для   студентов   по   выполнению 11. Надеждина   О.В.   методические   указания   для   студентов   по   выполнению 12. Надеждина О.В методические указания для студентов по выполнению практических 13. Старкова А.Ю. методические указания для студентов по выполнению практических 14. Старкова А.Ю. методические указания для студентов по выполнению практических практических занятий. Часть1. 2008. практических занятий. Часть2. 2008. занятий. ЧастьЗ. 2008. занятий. Часть4. 2008. занятий. Частьэ. 2008.