Автоматизированная база данных с использованием MS Access

Содержание

 

 

 

 

 

 

 

 

 

ВВЕДЕНИЕ

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

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

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

В данном курсовом проекте рассмотрены основные понятия баз данных: структура простейшей базы данных, компоненты базы данных Microsoft Access. Представлен электронный вариант базы данных «ДЮСШ», в которой хранятся данные о обучающихся и преподавателях которые их обучают. Разработка проекта проводилась в приложении  Microsoft Access, предназначенный для управления, проектирования и разработки баз данных.

Цель: разработать автоматизированную базу данных с использованием MS Access для решения задач заказчика.

 

 

 

 

 

Задачи:

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1. ОРГАНИЗАЦИОННО-ЭКОНОМИЧЕСКАЯ СУЩНОСТЬ ЗАДАЧИ

1.1.  Постановка  задачи

 

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

Основные требования заказчика к базе данных:

• Красивый и понятный для пользователя интерфейс.
• Хранение информации  связанные с деятельностью заказчика.
• Быстрый и удобный доступ к необходимой информации.
• Выходные документы соответствующие принятым стандартам

 

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

 

1.2. Структура предприятия  заказчика

 

Предприятие заказчика можно представить следующей схемой (рис. 1):

 

 

Рисунок 1.

Преподаватели:

• Состоит из списка преподавателей
• Содержит информацию: Номер паспорта, ФИО, дата рождения, должность, стаж, оклад.

Обучающиеся:

• Состоит из списка обучающихся,
• Содержит информацию: Номер полиса,  ФИО, дата рождения, достижения.

Залы:

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

Договор:

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

Кружки:

• Состоит из списка кружков
• Содержит информацию: код кружка, вид деятельности, цена.

Олимпиада:

• Состоит из списка соревнований
• Содержит информацию: код соревнования, вид соревнования, вид кружка, дата проведения, призовой фонд.

Инвентарь:

• Состоит из списка инвентаря
• Содержит информацию: код инвентаря, название инвентаря, стоимость

ДЮСШ:

• Состоит из списка домов
• Содержит информацию: номер дома, площадь дома, этажи, количество залов.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1.3. Информационные  процессы

 

Деятельность организации можно представить так:

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

 

1.4. Формы входных  документов

 

Информация рассматриваемой предметной области отображается документами приведенными ниже.

Договор на обучение

Номер договора _____________________________________________.

Название кружка ____________________________________________.

Местонахождение – номер ДЮСШ ______________.

Срок действия договора ________________________________________.

ФИО ребенка ________________________________________________.

Дата рождения ____________________.

Дата заключения договора ____________________________________.

Подпись ________.

 

 

 

 

 

 

1.5. Перечень задач, предлагаемых заказчиком для  исполнения

 

Заказчик поставил перед нами следующие задачи:

1. Предоставить возможность вывода обучающихся, занимающихся в указанном кружке
2. Предоставить возможность вывода обучающихся, занявших указанное место в соревнованиях
3. Возможность вывода учеников у определенного преподавателя
4. Вывод информации о зарплатах преподавателей с учетом премий
5. Возможность изменения фамилии преподавателя
6. Возможность поиска обучающегося по фамилии
7. Предоставить возможность вывода информации о доходах с кружков
8. Просмотр информации об обучающихся, посещающих указанный кружок
9. Вывод информации об инвентаре, необходимом для указанного кружка
10. Вычисление затрат на инвентарь
11. Предоставить возможность вывода кружка с максимальной стоимостью
12. Вывод списка договоров, у которых истек срок действия.
13. Вывод списка обучающихся, посещающих несколько кружков и общая сумма оплаты за кружки.
14. Предоставление скидки обучающимся, у которых сумма оплаты за кружки превышает Х рублей

 

2. ИНФОРМАЦИОННО-ЛОГИЧЕСКАЯ  МОДЕЛЬ ПРЕДМЕТНОЙ ОБЛАСТИ

2.1. ER-Диаграмма

Для проектирования информационно-логической модели существуют различные средства, в том числе и компьютерные. Воспользуемся ER-диаграммой (ER – Entity Relation – Сущность - Связь)

Сущность - это класс однотипных объектов, информация о которых должна быть учтена в модели. Каждая сущность должна иметь наименование, выраженное существительным в единственном числе.

Атрибут сущности - это именованная характеристика, являющаяся некоторым свойством сущности.

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

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

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

• Один к одному (1:1);
• Один ко многим (1:М);
• Многие ко многим (М:М).

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

Рассмотрим следующие сущности: ПРЕПОДАВАТЕЛИ, ОБУЧАЮЩИЕСЯ, КРУЖКИ, ВИД СОРЕВНОВАНИЯ, ОЛИМПИАДА, ИНВЕНТАРЬ и определим связи между ними.

 

У каждого обучающегося может быть несколько преподавателей, и каждый преподаватель может обучать нескольких обучающихся. Поэтому связь между сущностями ПРЕПОДАВАТЕЛИ И ОБУЧАЮЩИЕСЯ (М:М).

 

Обучающийся может посещать несколько видов кружков, и один кружок могут посещать несколько обучающихся. Поэтому связь между сущностями ОБУЧАЮЩИЕСЯ И КРУЖКИ (М:М)

 

Обучающийся может участвовать в нескольких видах соревнования, и в одном виде соревнования участвуют несколько обучающихся. Поэтому связь между сущностями ОБУЧАЮЩИЕСЯ И ВИД СОРЕВНОВАНИЯ (М:М).

 

В одной олимпиаде может быть несколько видов соревнований, а для одного вида соревнования может быть только одна олимпиада. Поэтому связь между сущностями ВИД СОРЕВНОВАНИЯ И ОЛИМПИАДА (1:М).

 

Несколько видов инвентаря может быть для одного кружка, и для нескольких кружков может быть необходим один инвентарь. Поэтому связь между сущностями КРУЖКИ И ИНВЕНТАРЬ (М:М).

 

 

 

 

 

 

 

Итак, получили следующую ER-диаграмму:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Рисунок 2.1 ER-диаграмма

 

На данной схеме явно просматриваются все связи между сущностями, сами сущности и их атрибуты.

 

 

 

 

 

 

 

 

 

 

2.2.  Реляционная  модель

Что такое реляционная модель? Достаточно точно ее характеризует следующее определение:  реляционная модель - это способ рассмотрения данных, т.е. предписание для способа представления данных (посредством таблиц) и для способа работы с таким представлением (посредством операторов). Или, точнее, реляционная модель связана с тремя аспектами данных: структурой, целостностью и обработкой данных.

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

1. Каждый элемент таблицы – один элемент данных.
2. Все столбцы в таблице однородные, т.е. все элементы в столбце имеют одинаковые характеристики  (тип и длину).
3. Каждое столбец имеет уникальное имя.
4. Порядок столбцов не важен.
5. Отсутствуют одинаковые записи.
6. Порядок записей в таблице может быть произвольным.

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

Реляционные модели (в  MS Access) не позволяют определять прямую связь «многие ко многим» между  двумя таблицами. Поэтому нужно разделить связь «многие ко многим» на две связи «один ко многим», т.е. построить дополнительную таблицу связи.

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

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

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

Таблицы «Преподаватели» и «Обучающиеся» имеют связь (М: М), так как в реляционной модели мы не можем определять прямую связь «многие ко многим» между  двумя таблицами, построим дополнительную таблицу связи, которую назовем ДОГОВОР. Соотнося атрибуты в сущностях ПРЕПОДАВАТЕЛИ и ОБУЧАЮЩИЕСЯ с соответствующими  атрибутами,  в сущности, ДОГОВОР через две связи один ко многим (1:М), получаем необходимую нам связь многие ко многим (М: М). Для объединения таблиц нам требуется добавить внешние ключи (НомПасп и НомПол) в таблицу «Договор».

 

Таблицы «Обучающиеся» и «Кружки» имеют связь (М: М), соотнося атрибуты в сущностях ОБУЧАЮЩИЕСЯ и КРУЖКИ с соответствующими атрибутами, в сущности, ДОГОВОР, мы так же можем объединить их через две связи один ко многим (1:М), получаем необходимую нам связь многие ко многим (М: М). Для объединения таблиц нам требуется добавить внешний ключ (КодКр) в таблицу «Договор».

 

Таблицы «Вид соревнования» и «Обучающиеся» имеют связь (М: М), так как в реляционной модели мы не можем определять прямую связь «многие ко многим» между  двумя таблицами, построим дополнительную таблицу связи, которую назовем РЕЗУЛЬТАТЫ ОЛИМПИАДЫ. Соотнося атрибуты в сущностях ВИДЫ СОРЕВНОВАНИЯ и ОБУЧАЮЩИЕСЯ с соответствующими  атрибутами,  в сущности, РЕЗУЛЬТАТЫ ОЛИМПИАДЫ через две связи один ко многим (1:М), получаем необходимую нам связь многие ко многим (М: М). Для объединения таблиц нам требуется добавить внешние ключи (КодСоревн и НомПол) в таблицу «Результаты олимпиады».

 

Таблицы «Кружки» и «Инвентарь» имеют связь (М: М), так как в реляционной модели мы не можем определять прямую связь «многие ко многим» между  двумя таблицами, построим дополнительную таблицу связи, которую назовем ИНВЕНТАРИЗАЦИЯ. Соотнося атрибуты в сущностях КРУЖКИ и ИНВЕНТАРЬ с соответствующими  атрибутами,  в сущности, ИНВЕНТАРИЗАЦИЯ через две связи один ко многим (1:М), получаем необходимую нам связь многие ко многим (М: М). Для объединения таблиц нам требуется добавить внешние ключи (КодИнв и КодКр) в таблицу «Инвентаризация».

 

Таблица «Вид соревнования» с таблицей «Олимпиада» имеют связь 1:М (один ко многим), следовательно, требуется добавить внешний ключ в таблицу «Вид соревнования» (КодПровед)

 

Схема данных имеет вид:

Рисунок 2.2 Схема данных

 

 

2.3. Построение нормализованных таблиц из ER-диаграмм.

 

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

В моей предметной области используются следующие таблицы:

Преподаватели, Кружки, Договора.

Ключи в таблицах соответствуют ключам в соответствующих сущностях.

Полагаясь на выше сказанное, получаем таблицы со следующими полями:

Преподаватели(#НомПасп, ФамПр, ИмяПр, ДатаРождПр, Должность, Стаж, Оклад, КодКр)

Кружки(#КодКр, ВидДеят, Цена) 

       Договор(#НомДог, НомПасп, НомПол, КодКр, ДатаЗакл, СрокДейст, ДатаРаст)

 

2.4. Построение  нормализованных таблиц с помощью  приведения к нормальным формам

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

• обеспечить быстрый доступ к данным в таблицах;
• устранения логических ошибок;
• удаления избыточного дублирования данных;
• группирования информации в логически связанных единицах.

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

В логическом проектировании наиболее эффективна структура данных, представленная в виде Нормальных форм (НФ). Существуют несколько видов нормальных форм:

• 1 -я нормальная форма
• 2-я нормальная форма
• 3-я нормальная форма        
• Нормальная форма Бойса - Кодда (НФБК)
• 4-я нормальная форма
• 5-я нормальная форма

Практически используются только первые три. Рассмотрим процесс приведения к 1НФ, 2НФ и 3НФ.

Рассмотрим  универсальную таблицу, в которую включаются все атрибуты.

«ДЮСШ» (НомПол, ФамОб, ИмяОб, НомПасп, ФамПр, ИмяПр, КодКр, ВидДеят, Цена, НомДома, ПлощДома, НомЗал, ПлощЗал, НомДог, КодИнв, НазвИнв, СтоимИнв, Количество, КодСоревн, ПризФонд, КодПровед, Проведение,Место).

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

Ключи (НомПасп, НомДог, КодИнв, КодСоревн).

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

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

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

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

НомПасп à (ФамПр, ИмяПр). – 3НФ

НомПол à (ФамОб, ИмяОб). – 3НФ

НомДог à (НомПасп, НомПол, КодКр). – 3НФ

КодКр à (ВидДеят, Цена). – 3НФ

НомДома à ПлощДома – 3НФ

НомЗал à (ПлощЗал, ВидДеят, НомДома) – 3НФ

КодИнв à (НазвИнв, Количество, СтоимИнв) – 3НФ

КодСоревн à (КодКр, ПризФонд, КодПровед) – 3НФ

КодПровед à (Проведение, КодСоревн) – 3НФ

(КодКр, КодИнв) à Количество – 2НФ

(КодСоревн, НомПол) à (Место, КодПровед) – 2НФ

Путем приведения нашей универсальной таблицы ко 2ой и 3ей нормальным формам мы избавились от всех аномалий.

Можно сделать следующий вывод, что таблицы, построенные по ER–диаграмме и путем нормализации, совпадают.

 

 

 

 

 

 

 

 

 

3. ФИЗИЧЕСКОЕ ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ В СРЕДЕ Microsoft Access

3.1.  Создание  таблиц

Таблицы – это объекты, предназначенные для хранения данных в виде записей (строк) и полей (столбцов). Каждая таблица содержит данные об определенном объекте.

Создание таблицы производится в два этапа:

1. определение структуры таблицы;
2. ввод данных в таблицу (загрузка данных).

В MS Access используются три способа создания таблиц путем ввода данных, с помощью Конструктора таблиц и с помощью Мастера создания таблиц.

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

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

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

После описания всех полей будущей таблицы ее нужно сохранит. Сохранение структуры выполняется следующим способом:

• Нажать кнопку Сохранить на панели инструментов или выбрать команду Файл>Сохранить;
• В появившемся диалоговом окне ввести имя таблицы, нажать ОК;
• Если до сохранения в новой таблице ключевые поля были не определены, то при сохранении таблицы будет выдано сообщение о создании ключевого поля. При  нажатии кнопки ДА будет создано дополнительное поле – ключевое поле счетчика, что не всегда удобно, так как усложняет структуру таблицы (поэтому рекомендуется при создании таблицы указать нужное ключевое поле, выбранное вами), если ключ не нужен и не был задан, то при сохранении отказаться - Нет.

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

 

3.2. Загрузка таблиц

Для ввода данных в таблицу существует несколько способов:

• Автоматический
• Импортировать данные в таблицу из других приложений Microsoft Office,Excel, HTML и др.
• В режиме Таблицы
• С помощью форм

Первоначально ввод данных производили в режиме таблицы. Для этого необходимо открыть таблицу и заполнить её. Для перемещения по ячейкам записей  можно использовать стрелки на клавиатуре или курсор мыши. Для перехода по записям  удобнее всего использовать кнопки, расположенные в нижней части окна таблицы. Там же расположено поле, отображающее номер текущей записи, с которой производится действие. Для перехода сразу в нужную запись  поля с номером записи, вводим номер записи и нажимаем клавишу ENTER. Для добавления записи необходимо нажать кнопку  стрелочка со звездочкой и вводить данные.  Так же можно менять цвет фона, линию сетки и шрифт вводимых данных. Все это можно сделать в пункте меню ФОРМАТ.

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

 

3.3. Ориентировочный расчет требуемой памяти

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

1. Расчет памяти для Таблицы 3.1 Преподаватели

Таблица 3.1 - Преподаватели

Имя Поля	Тип Данных	Память(байт)
НомПасп	Числовой	4
ФамПр	Текстовой	50
ИмяПр	Текстовой	50
Должность	Текстовой	50
Стаж	Числовой	2
ДатаРожд	Дата/время	8
Оклад	Денежный	10
КодКр	Числовой	2
ИТОГО:		176

 

2. Расчет памяти для Таблицы 3.2 Кружки

Таблица 3.2 – Кружки

Имя поля	Тип данных	Память (байт)
КодКр	Числовой	2
ВидДеят	Текстовый	50
Цена	Денежный	10
ИТОГО:		62

 

3. Расчет памяти для Таблицы 3.3 Договор

Таблица 3.3 – Договор

Имя поля	Тип данных	Память (байт)
НомДог	Числовой	5
НомПасп	Числовой	4
НомПол	Числовой	6
КодКр	Числовой	2
ДатаЗаключ	Дата/время	8
СрокДейст	Числовой	2
ДатаРаст	Дата/время	8
ИТОГО:		35

 

4. Расчет памяти для Таблицы 3.4 Обучающиеся

Таблица 3.4 – Обучающиеся

Имя поля	Тип данных	Память (байт)
НомПол	Числовой	6
ФамОб	Текстовый	50
ИмяОб	Текстовый	50
ДатаРожд	Дата/время	8
Достижения	Поле объекта	300
ИТОГО:		364

 

5. Расчет памяти для Таблицы 3.5 Олимпиада

Таблица 3.5 – Олимпиада

Имя поля	Тип данных	Память (байт)
КодСоревн	Числовой	4
КодКр	Числовой	2
ДатаПровед	Дата/время	8
ПризФонд	Денежный	10
КодПровед	Числовой	4
ИТОГО:		28