База данных "Телефонный справочник"

РОССИЙСКИЙ  ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА

ПЕНЗЕНСКИЙ  ФИЛИАЛ

Кафедра «Информационные системы»

 

 

 

 

 

 

Курсовой проект

по дисциплине: «Управление Данными»

на тему: «Телефонный справочник»

 

 

 

Выполнил: студент гр. 10и1 Тарасов С. Г.

Проверил: преподаватель Корниенко К. А.

 

 

 

Пенза 2013 г.

Утверждаю

Зав. кафедры 

____________

Дисциплина - Управление данными.

Задание на курсовое проектирование.

 

 

Выдано студенту гр.______10и1___                Ф.И.О._Тарасов С. Г.

Вариант задания №______33______           Дата выдачи_________________

Руководитель ___________________          Студент_____________________

 

Разработать базу данных «Телефонный справочник», содержащую следующие атрибуты: ФИО, телефон, город, время разговора, ставка, льготы, адрес, переговоры, дата разговора.

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

Далее следует провести нормализацию концептуальной схемы до второй или  третьей нормальной формы и на их основе создать проект БД в среде Access и провести его отладку и  тестирование.

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

Содержание

Введение.………………………………………………………………….………...4

1 Общие понятия и определения…………………………………………….…….5

2 Анализ заданной предметной  области, потребности различных  категорий пользователей разрабатываемой  БД……………………………………..………..8

3 Разработка и описание концептуальной схемы и подсхем БД ..…..………….9

4 ER-Win ………………………………………………………….………….……14

5  Преобразование схемы и подсхем в реляционную БД………..……..………19

6 Нормализация реляционной БД…………………...………………….…..……21

1НФ…………………………………………………………………………….…..21

2НФ…………………………………………………………………………….…..21

3НФ…………………...………………………………………………………..…..22 7 Разработка запросов в SQL………….……………………………………….…23

8 Объединение компонентов базы данных в единое приложение…………….27

Заключение  ………………………………………………………………….…….29

Список литературы ………………………………………………………….……30

 

 

 

 

 

 

 

 

Введение

  Данная курсовая работа выполнена в СУБД  Microsoft Access, входящее в прикладной пакет Microsoft Office. Созданная программа обладает рядом достоинств :

1. Легкий способ ввода или удаления.
2. Удобный дизайн программы с понятным интерфейсом и оригинальными кнопочными формами
3. Возможность просмотра или вывода на печать отчётов, наглядно демонстрирующих информацию по основным категориям
4. Наличие подробного описания предназначения и работы программы
5. Автоматизация необходимых расчетов.

 

1. Общие понятия и определения

 

Существует несколько моделей  данных, полагаемых в основу информационных систем. Наиболее часто используются следующие три: иерархическая, сетевая и реляционная. Недавно появился четвертый тип: объектно-ориентированные системы управления базами данных (ООСУБД), которые соединяют традиционную технологию проектирования баз данных с объектной моделью. Реляционная модель весьма популярна, обладает рядом достоинств и может сочетаться с объектно-ориентированным подходом. Именно это сочетание присутствует в Microsoft Access, ведь Microsoft Access является реляционной СУБД. К тому же, многие существующие информационные системы построены на основе реляционной модели.

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

Информационная система (information system) — это приложение, предназначенное для хранения и обработки данных. Основой информационной системы является база данных с информацией, хранящейся в одной или нескольких связанных таблицах.

База данных {data base) представляет собой совокупность связанных таблиц (в предельном случае - одну таблицу), предназначенных для хранения определенной информации. Термином "база данных" часто называют приложение, использующее базу данных и обладающее интерфейсом просмотра и правки, а также средствами   обработки хранящейся в базе данных информации. Однако такое приложение лучше называть информационной системой.

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

Реляционная модель (relational model). Основными элементами реляционной модели являются таблицы, представляющие сущности, в которых столбцы представляют атрибуты сущностей, а строки описывают экземпляры сущностей. Модель данных также подразумевает наличие операторов для генерации новых таблиц на основе существующих (называемых запросами (query)), именно таким способом' пользователи могут манипулировать данными и получать необходимую информацию.

Сущность (entity) —множество однотипных объектов, называемых экземплярами (instance). Каждый экземпляр характеризуется набором свойств, называемых атрибутами сущности (attribute). Каждый экземпляр индивидуален и отличается от всех остальных экземпляров во множестве.

Таблица (table) - множество ячеек с данными, образующих строки и столбцы прямоугольной таблицы. Таблица реализует сущность в понятии реляционной модели данных. Строки таблицы представляют экземпляры сущности и называются записями (records). Столбцы таблицы представляют атрибуты сущности и называются полями (fields).

Атрибут* (attribute) представляет собой определенное свойство (характеристику) данной сущности. Рекомендуется в качестве атрибутов выделять атомарные свойства сущности.

Поле таблицы (table field) - столбец в прямоугольной таблице. Поле таблицы реализует атрибут в понятии реляционной модели, при этом данные, хранятся в ячейках одного столбца, должны принадлежать одному домену. Домен определяет набор допустимых значений и операций над данными. То есть данные в ячейках одного столбца должны быть одного типа.

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

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

Ключевое поле (key field) — поле, представляющее первичный ключ или являющееся частью составного первичного ключа.

Альтернативный  ключ (alternative key) — обычные поля или комбинации атрибутов, отличающиеся от первичного ключа сущности, но также претендующие на эту роль.

Связь (relationship) - это логическое отношение между сущностями, выражающее некоторое ограничение или правило. В реляционной модели вводится понятие реляционной связи (relation) — это связь между записями, основанная на совпадении (или ином предикате) значений атрибутов, по которым устанавливается связь.

Основные  объекты Access - таблицы, формы, запросы, отчеты, макросы, модули. Таблица является основой БД, в ней хранится вся информация.

Процесс создания отдельной таблицы в составе  БД состоит из следующих этапов:

1) Создание структуры таблицы  (задание имен и типов полей,  задание ключевого поля);

2) Ввод данных в таблицу. Установить  связи по общим полям методом  ДД перетаскивая их от главной  таблицы к связанной 

3) Сохранить схему данных, закрыть  окно.                                                 

2. Анализ заданной предметной области, потребности различных категорий пользователей разрабатываемой БД

 

         База данных «Телефонный справочник» предназначена, прежде всего, для отображения данных об абонентах телефонной сети. С помощью данной базы данных можно получать различную информацию об абонентах, такие как город проживания, телефон, код клиента, ФИО клиента, время разговора, переговоры с конкретным пунктом, ставку, льготы и сумма оплаты. С помощью удобно оформленных запросов можно получить всю информацию о клиенте введя лишь его фамилию или телефон. Также база данных предоставляет подробный отчет об использовании сети абонентами.

 

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

 

 

 

 

 

 

 

3. Разработка и описание концептуальной схемы и подсхем БД

 

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

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

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

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

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

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

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

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

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

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

Схема базы данных

 

 

Рис. 1 – Схема  БД.

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

Первый тип - связь “один к  одному”, т. е. одной записи при этой связи в главной таблице должна соответствовать одна запись в подчиненной  таблице. Такие БД используются довольно редко. С помощью таких связей выделяют отдельно редко используемую информацию.

Второй тип - связь “один ко многим”, наиболее часто используется это  отношение. В данном случае одной записи главной таблице могут соответствовать несколько записей подчиненной таблицы. Различают две разновидности связи “один ко многим”. В первом случае предъявляются жесткие требования на обязательное наличие записей во вторичной таблице. Во втором случае такие требования отсутствуют.

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

Подсхемы  базы данных

 

 

Рис. 2 - Подсхема «Клиенты».

 

Рис. 3 - Подсхема «Оплатить».

 

Рис. 4 - Подсхема «Учет переговоров».

 

 

Рис. 5 - Подсхема «Льготы».

4. Создание ER-модели в среде ER-Win

 

Запустить ERWin и выбрать режим создания новой модели (Create a new model) или открытия существующей (Open an existing file).

Выбрать тип новой модели (New model type): логическая, физическая или модель, которая допускает преобразование одной в другую. Поскольку нужно спроектировать БД, следует выбрать логико-физическую модель.

Для построения ER-моделей используется инструментальная панель, состав которой зависит от выбранного стандарта представления моделей и типа модели: логическая или физическая. В данной работе рекомендуется использовать стандарт IDEF1X (Integration DEFinition for information modeling), в котором используются представленные в таблице обозначения.

Прежде  всего следует разместить диаграммы  блоки сущностей. Выбрать блок сущности (Entity) на панели главного меню с помощью щелчка левой клавишей и разместить в поле проектирования модели (переместить курсор и щелкнуть левой клавишей); в результате в поле проектирования будет размещен блок сущности с именем по умолчанию «E/1».

С помощью  пункта главного меню «Model / Entities» или  пункта всплывающего меню (по щелчку правой клавишей в поле блока сущности) «Entity Properties» («Свойства сущности») открыть  окно для редактирования свойств  сущности: название (Name), определение (Definition), примечания (Note, Note2, Note3), определяемые пользователем свойства (UDP – User definition properties) и др.

С помощью  пункта «Object Font & Color» всплывающего меню установить параметры шрифта названия сущности и цвет заполнения поля блока.

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

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

Когда добавляется  объект в окно диаграммы, ERWin автоматически  назначает шрифт по умолчанию. Для  изменения параметров шрифтов, задаваемых по умолчанию, используется диалоговое окно «Default Fonts & Colors», которое вызывается из всплывающего меню (после щелчка правой клавишей на поле диаграммы).

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

С помощью  пункта «Attributes …» всплывающего меню и соответствующих форм ввести данные об атрибутах выбранной сущности:

нажать кнопку «New»;

заполнить поля «Attribute Name» и «Column Name» (не обязательно) формы «New Attribute», предварительно выбрав тип данных для атрибута; названия атрибутов можно задавать с помощью шрифтов типа «Кириллица» или «Латиница» (при этом следует учитывать возможности СУБД, которые будут использоваться для сопровождения БД; при использовании СУБД типа Access можно использовать имена сущностей и атрибутов на русском языке);

Рис. 6 – Логическая модель в ERWIN.

Рис. 7 – Физическая модель в ERWIN.

 

Генерация файлов БД

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

Выбрать сервер или СУБД, которая будет  использоваться для работы с создаваемой  БД через пункты главного меню «Database / Choose Database» и окно «Target server» («целевой сервер») с переключателями.

 

Рис. 8 – окно «Target server».

С помощью меню «Database / Database Connection»  открыть окно для ввода параметров связи ER-модели с БД следует ввести имя пользователя «admin», затем выбрать  с помощью клавиши «Browse» и  диалогового окна путь и имя файла  БД, нажать клавишу «Connect» (будет  выполнена связь ER-модели с файлом БД).

Рис 9 – Диалоговое окно «Access Connection».

С помощью меню «Tools / Forward Engineer/Schema Generation» открыть окно для проверки и изменения параметров генерации  файлов БД

 

Рис 10 – Окно «Main Subject Area».

 

Нажать  клавишу «Generate»; в окне будут выведены операторы, выполненные при генерации файлов БД.

 

 

Рис. 11 – Диалоговое окно «Generate Database Schema».

В случае успешного окончания процесса генерации  файлов БД («Schema Generation Complete» - «Генерация схемы выполнена») в ранее созданной БД будут находиться соответствующие таблицы и схема БД.

 

 

 

 

 

 

 

5. Преобразование схемы и подсхем в реляционную БД

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

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

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

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

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

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

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

 

Создать структуру  таблицы  «Клиенты»,

• В окне БД CL закладку «Таблицы», CL кнопку «Создать»
• CL пункт «Конструктор», CL кнопку «ОК»
• В появившемся окне в столбце «Имя поля» ввести имена полей
• В столбце «Тип данных» CL и выбрать нужный тип.
• Задать ключевое поле - RCL на имени поля, в меню CL пункт «Ключевое поле» (в левой части строки появится изображение ключа)
• Сохранить структуру  таблицы - CL кнопку «Сохранить» на панели инструментов, ввести имя таблицы, CL кнопку «ОК»

           Рис. 12 – Создание таблицы «Клиенты» в режиме конструктора.

Создать схему  данных (указать связи)

• Нажать кнопку Схема Данных
• RCL, выбрать пункт «Показать таблицу»,

выделить  таблицы, нажать кнопку Добавить, нажать Закрыть 

• Установить связи по общим полям методом ДД перетаскивая их от главной таблицы Предприятие к связанной.
• Сохранить схему данных, закрыть окно.

 

Рис. 13 – Схема данных.

 

 

 

6 Нормализация реляционной БД

Нормализация - это такой способ описания данных, который:

понятен пользователю, не имеющему особых навыков в программировании;

позволяет подсоединять новые элементы данных, записи, связи без изменения  существующих подсхем и, следовательно, прикладных программ;

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

Сам процесс нормализации проводится по следующей схеме:

1 шаг. Приведение таблиц к  первой нормальной форме.

2 шаг. Приведение ко второй  нормальной форме.

3 шаг. Приведение к третьей  нормальной форме.

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

 

 Заданную  таблицу “Телефонный справочник” нужно привести к 1, 2, 3 нормальной форме.

1-я  Нормальная форма

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

2-я  Нормальная форма.

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

  Выделим первичный ключ – это поля “Код клиента”. С помощью проверки мы получили что данный ключ идентичен и все поля зависят от него т.о. 2НФ выполнена.

3-я  Нормальная форма.

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

 

 

 

 

 

 

 

 

 

 

 

 

 

7 Разработка запросов в SQL

 

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

Для подготовки запросов в различных СУБД чаще всего  используются два основных языка:

Язык QBE (Query By Example) – язык запросов по образцу,

Язык SQL (Structured Query Language) –  структурированный язык запросов.

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

Теоретической основой языка QBE является реляционное  исчисление с переменными - доменами. Язык позволяет задавать сложные  запросы к БД заполнением запросной  формы, имеющей вид таблицы, имена  и названия полей которой совпадают  с именами и названиями полей  соответствующих исходных таблиц. Наглядными являются запросные формы в Access.

Язык SQL предназначен для выполнения операций над таблицами (создание, удаление, изменение структуры) и над данными  таблиц (выборка, изменение, добавление, удаление), и некоторых сопутствующих  операций. SQL является непроцедурным  языком и не содержит операторов управления, организации подпрограмм, ввода-вывода и т.п. В связи с этим SQL обычно встраивается в СУБД (например, СУБД ACCESS, FoxPro СУБД.).

В современных  СУБД с интерактивным интерфейсом  можно создавать запросы, как  было сказано, например, с помощью  языка QBE. Однако применение SQL часто  позволяет повысить эффективность  обработки данных. Так, при подготовке запроса в ACCESS можно перейти из окна Конструктора запросов (формулирование запроса по образцу на языке QBE) в  окно с эквивалентным оператором SQL. Новый запрос можно создать  путем редактирования уже имеющегося запроса или программированием  нового.

Язык SQL не обладает функциями полноценного языка программирования, а ориентирован на доступ к данным, поэтому его  включают в состав средств разработки программ. В этом случае его называют встроенным SQL.

При построении запроса в окне конструктора система Access работает в фоновом режиме, записывая  эквивалентные инструкции SQL. Для  просмотра программы SQL в меню Вид выберите команду Режим SQL.

 

Рис. 14 – Запрос по фамилии

Код SQL:

SELECT Клиенты.[Код  клиента], Клиенты.Фамилия, Клиенты.Имя,  Клиенты.Отчество, Клиенты.Телефон,  Клиенты.Город, Клиенты.Адрес

FROM Клиенты

WHERE (((Клиенты.Фамилия)=[Введите  искомую фамилию]));

 

Рис. 15 – Запрос по телефону.

Код SQL:

SELECT Клиенты.[Код  клиента], Клиенты.Фамилия, Клиенты.Имя,  Клиенты.Отчество, Клиенты.Город, Клиенты.Телефон,  Клиенты.Адрес

FROM Клиенты

WHERE (((Клиенты.Телефон)=[Введите  искомый телефон]));

 

 

 

 

 

 

8. Объединение компонентов базы данных в единое приложение

 

Войдите в режим создания формы конструктором  без указания источника данных.

На панели элементов отключите кнопку Мастера.

На панели элементов выберите элемент Кнопка и нарисуйте на форме в области  данных кнопки.

Для каждой кнопки вызовите контекстное меню. Выберите пункт Свойства. Перейдите  на вкладку Макет и в строке Подпись задайте название кнопки в соответствии с проектом.

Перейдите в  режим формы и просмотрите  созданный проект формы. Если он Вас устраивает, то сохраните форму.

 

 

Рис. 16 - Форма абоненты.