Содержание Введение. 2 1. Постановка задачи. 4 2. Нормализация базы данных 5 3. Описание ограничений целостности 7 4. Построение таблиц. 10 4.1 Создание таблиц 10 4.2 Связи между таблицами 14 5. Работа с запросами 16 6. Отчеты. 19 7. Макросы 21 8. Принцип работы 23 Заключение 26 Список литературы 27 Введение. В настоящее время среди разработчиков базы данных (БД) большой популярностью пользуется настольная реляционная СУБД Access, входящая в состав пакета Microsoft Office. Дружественный интерфейс и простота настройки, эффективные средства создания таблиц, форм, запросов, интеграция с другими приложениями пакета, средства организации работы с базами данных и защита информации - вот далеко не полный перечень достоинств этого приложения. Основные функции СУБД – это описание структуры базы данных, обработка данных и управление данными. База данных – это совокупность сведений о реальных объектах, процессах, событиях или явлениях, относящихся к определённой теме или задаче, организованная таким образом, чтобы обеспечить удобное представление этой совокупности, как в целом, так и любой её части. Реляционная база данных представляет собой множество взаимосвязанных таблиц, каждая из которых содержит информацию об объектах определённого типа. Каждая строка таблицы содержит данные об одном объекте (например, клиенте, автомобиле, документе), а столбцы таблицы содержат различные характеристики этих объектов – атрибуты (например, наименования и адреса клиентов, марки и цены автомобилей). Строки таблицы называются записями, все записи имеют одинаковую структуру – они состоят из полей, в которых хранятся атрибуты объекта. Каждое поле в записи содержит одну характеристику объекта и имеет строго определённый тип данных (например, текстовая строка, число, дата). Все записи имеют одни и те же поля, только в них содержатся разные значения атрибутов. Любая СУБД позволяет выполнять четыре простейшие операции с данными: добавить в таблицу одну или несколько записей; удалить из таблицы одну или несколько записей; обновить значения некоторых полей в одной или нескольких записях; найти одну или несколько записей, удовлетворяющих заданному условию. Для выполнения этих операций используется механизм запросов. Результатом выполнения запросов является либо отобранное по определённым критериям множество записей, либо изменение в таблицах. Запросы к базе данных формулируются на специально созданном для этого языке, который так и называется язык структурированных запросов (Structured Query Language – SQL). 1. Постановка задачи. В данной работе в качестве предметной области рассматривается, банк с вкладчиками, которая организует документооборот входящей, исходящей, внутренней документации, к которой относятся сведения о счетах, вкладчиках. Ограничения и требования, предъявляемые к базе данных. Данная база данных решает следующие задачи: учёт вкладов, выдача данных о состоянии счета. Реализует запросы по группировке сортировке и расчету времени хранения. Осуществляет поиск товаров по дате. Технические средства: ПЭВМ типа IBC PC. Применяемая операционная система: WINDOWS XP. Применяемая СУБД: АССЕSS XP .Время поиска и выдачи информации: 3 сек. или менее в зависимости от мощности компьютера. Требования к целостности данных: данные в базе данных в любой момент времени должны быть правильными и непротиворечивыми. 2. Нормализация базы данных Процесс проектирования баз данных заключается в последовательном переводе отношений из первой нормальной формы в нормальные формы более высокого порядка по определенным правилам. Каждая следующая нормальная форма ограничивает определенный тип функциональной зависимости, устраняет соответствующие аномалии при работе с отношениями и сохраняет свойства предшествующих форм. Процесс построения реляционных баз данных на основе нормальных форм предполагает удаление из исходного отношения, следующие меж атрибутивные зависимости: Частичной зависимости атрибутов от ключа (уровень второй нормальной формы); Транзитивность зависимостей не ключевых атрибутов от ключа (удовлетворяет 3-ей нормальной форме); Зависимости ключей от не ключевых атрибутов (удовлетворяет нормальной форме Байеса-Кодда) альтернативой этого подхода является метод ER-диаграмм (метод сущность-связь), которой применяется для проектирования больших баз данных и на нем реализованы средства проектирования баз данных. Основное правило при создании таблиц сущностей – это каждой сущности желательно сопоставить отдельную таблицу. Поля таблиц сущностей могут быть ключевыми или не ключевыми. Введение ключей позволяет обеспечить уникальность значений в записи, ускорить обработку записи и выполнить обработку. Если в таблице есть значительное повторение по нескольким полям и их объем существенен, то лучше их выделить в отдельную таблицу. Новую сущность легко добавить и изменить, но при удалении следует уничтожить все ссылки на нее из таблиц связей, в противном случае возникает некорректность. В данной контрольной работе была проведена нормализация базы данных и достигнута нормальная форма Байеса-Кодда, то есть были устранены функциональные зависимости и исключена явная избыточность в таблицах. Также удалось избавиться от транзитивных зависимостей. Таблица находится в НФБК, если и только если любая функциональная зависимость между его полями сводится к полной функциональной зависимости от возможного ключа. В результате процесса нормализации исходной таблицы была получена НФБК: 3. Описание ограничений целостности Поля базы данных не просто определяют структуру базы – они еще определяют групповые свойства данных, записываемых в ячейки, принадлежащие каждому из полей. Ниже перечислены основные свойства полей таблиц баз данных на примере СУБД Microsoft Access. Имя поля – определяет, как следует обращаться к данным этого поля при автоматических операциях с базой (по умолчанию имена полей используются в качестве заголовков столбцов таблиц). Тип поля – определяет тип данных, которые могут содержаться в данном поле. Размер поля – определяет предельную длину (в символах) данных, которые могут размещаться в данном поле. Формат поля – определяет способ форматирования данных в ячейках, принадлежащих полю. Маска ввода – определяет форму, в которой вводятся данные а поле (средство автоматизации ввода данных). Подпись – определяет заголовок столбца таблицы для данного поля (если подпись не указана, то в качестве заголовка столбца используется свойство Имя поля). Значение по умолчанию – то значение, которое вводится в ячейки поля автоматически (средство автоматизации ввода данных). Условие на значение – ограничение, используемое для проверки правильности ввода данных (средство автоматизации ввода, которое используется, как правило, для данных, имеющих числовой тип, денежный тип или тип даты). Сообщение об ошибке – текстовое сообщение, которое выдается автоматически при попытке ввода в поле ошибочных данных. Обязательное поле – свойство, определяющее обязательность заполнения данного поля при наполнении базы. Пустые строки – свойство, разрешающее ввод пустых строковых данных (от свойства Обязательное поле отличается тем, что относится не ко всем типам данных, а лишь к некоторым, например к текстовым). Индексированное поле – если поле обладает этим свойством, все операции, связанные с поиском или сортировкой записей по значению, хранящемуся в данном поле, существенно ускоряются. Кроме того, для индексированных полей можно сделать так, что значение в записях будут проверяться по этому полю на наличие повторов, что позволяет автоматически исключить дублирование данных. Поскольку в разных полях могут содержаться данные разного типа, то и свойства у полей могут различаться в зависимости от типа данных. Так, например, список вышеуказанных свойств полей относится в основном к полям текстового типа. Поля других типов могут иметь или не иметь эти свойства, но могут добавлять к ним и свои. Например, для данных, представляющих действительные числа, важным свойством является количество знаков после десятичной запятой. С другой стороны, для полей, используемых для хранения рисунков, звукозаписей, видео клипов и других объектов OLE, большинство вышеуказанных свойств не имеют смысла. Информация об объектах предметной области сведена в таблицу 2 Таблица 2. Название объекта (Название таблицы базы данных) Обозначение объекта Свойства (Описание поля) Ограничения (Тип поля)
Вкладчики Код Ключевое поле Счетчик
ФИО ФИО клиента Текстовый (55 символов)
Адрес Адрес клиента Текстовый (255 символов)
Номер паспорта Номер паспорта клиента Текстовый (55 символов)
Телефон Телефон клиента Длинное целое
Название объекта (Название таблицы базы данных) Обозначение объекта Свойства (Описание поля) Ограничения (Тип поля)
История Код Ключевое поле Счетчик
Вкладчик Связь с таблицей вкладчик Длинное целое
Операция Связь с таблицей операция Длинное целое
Дата Дата операции Дата/Время
Сумма Сумма Денежный
Счет Связь с таблицей счет Длинное целое
Счет Код Ключевое поле Счетчик
КОД_П Связь с таблицей поставщик Длинное целое
Счет Идентификатор счета Текстовый (55 символов)
Баланс Сумма на счету Денежный
Дата Дата создания Дата/Время
Тип Связь с таблицей тип счета Длинное целое
Операции Код Ключевое поле Счетчик
Тип операции Тип операции Текстовый (55 символов)
Тип счета Код Ключевое поле Счетчик
Тип счета Тип счета Текстовый (55 символов)
4. Построение таблиц. 4.1 Создание таблиц Для создания базы была созданы пять таблиц «Вкладчики», «История», «Операции», «Счет» и «Тип счетов» с соответствующими полями заданными в условии задания. Для каждого из поля был выбран соответствующий тип переменных. Таблица создавалась с помощью конструктора. В режиме Таблицы осуществляется работа с данными, находящимися в таблице: просмотр, редактирование, добавление, сортировка и т. п. В режиме Конструктора создается или модифицируется структура таблицы, т. е. задаются имена полей таблицы и их типы, поля описываются, задаются их свойства. В режимах Сводной таблицы и Сводной диаграммы удобно выполнять анализ данных, динамически изменяя способы их представления. Существует также дополнительный режим — режим Предварительного просмотра, который позволяет увидеть расположение данных на листе перед осуществлением печати таблицы. В данной работе мы остановимся на двух важнейших аспектах работы с таблицами: описание структуры таблиц и установка связи между ними; работа с данными в таблицах: ввод, просмотр, изменение, поиск и т. д. Дважды щелкнуть левой кнопкой мыши на ярлыке Создание таблицы с режиме конструктора. Есть другой способ: нажать на кнопку Создать в верхней части окна базы данных, выбрать из списка в окне Новая таблица элемент Конструктор и нажать кнопку ОК. В окне Конструктора таблиц в столбец Имя поля вводить имена полей создаваемой таблицы. В столбце Тип данных для каждого поля таблицы выбрать из раскрывающегося списка тип данных, содержащие в этом поле. В столбце Описание можно вводить описание данного поля (не обязательно). В нижней части окна Конструктора таблиц на вкладках Общие и Подстановка ввести свойства каждого поля или оставить значения свойств, установленные по умолчанию. После описания всех полей будущей таблицы нажать кнопку Закрыть (в верхнем правом углу окна таблицы). На вопрос Сохранить изменения макета или структуры таблицы ( нажать кнопку Да. В окне Сохранить как в поле Имя таблицы ввести имя создаваемой таблицы и нажать кнопку ОК. Вопрос Создать ключевое поле сейчас? нажмите кнопку Да если ключевое поле необходимо, или кнопку Нет если такого не требуется.
Рис. 1. Конструктор таблиц. Каждая строка таблицы должна быть уникальна. Для обеспечения уникальности строк используются ключи – поля, значения которых не могут повторяться ни в одной записи базы данных. Свойство Формат поля указывает формат отображения данных из поля в режиме Таблицы. Для определения формата полей текстового типа используются специальные символы форматирования. Для числовых полей значение формата можно выбрать из раскрывающегося списка. С помощью свойства Маска ввода указывается маска, позволяющая автоматизировать проверку ввода символов в поле. Она применяется к таким полям, как номер телефона, дата и т. д. Для большинства типов полей определено свойство Значение по умолчанию. В этом свойстве указывается значение, автоматически добавляемое в поле для каждой новой записи, если это значение не введено пользователем. Условие на значение — свойство определяет условие (ограничение), накладываемое на вводимые в это поле данные. При несоответствии вводимых данных указанному условию выдается сообщение об ошибке. Сообщение об ошибке — свойство определяет то сообщение, которое будет выдаваться пользователю, если при вводе данных не соблюдается условие, указанное в свойстве Условие на значение. Результирующие таблицы с данными показаны ниже на рисунках.
Рис. 2. Таблица вкладчики.
Рис. 3. Таблица история.
Рис. 4. Таблица операция.
Рис. 5. Таблица счет.
Рис. 6. Таблица тип счетов. Ввод и редактирование записей осуществлялся непосредственно в созданной таблице. Для удобного поиска и отображения необходимых записей использовались фильтры и сортировки. Настройка фильтра и параметры сортировки с помощью кнопок на панели инструментов:
Рис. 6. Кнопки управления фильтром и сортировкой. 4.2 Связи между таблицами Связи в реляционных базах данных определяются по совпадению значений полей в разных таблицах. Например, клиенты и заказы связаны отношением "один-ко-многим", т. к. одной записи в таблице, содержащей сведения о клиентах, может соответствовать несколько записей в таблице заказов этих клиентов. Если же рассмотреть отношение между преподавателями и курсами лекций, которые они читают, это будет отношение "многие-ко-многим", т. к. один преподаватель может читать несколько курсов, но и один курс может читаться несколькими преподавателями. И последний тип связей между таблицами — это отношение "один-к-одному". Такой тип отношений встречается гораздо реже. Как правило, это бывает в двух случаях: запись имеет большое количество полей, и тогда данные об одном типе объектов разносятся по двум связанным таблицам, или нужно определить дополнительные атрибуты для некоторого количества записей в таблице, тогда создается отдельная таблица для этих дополнительных атрибутов, которая связывается отношением "один-к-одному" с основной таблицей.
Рис. 6. Схема базы данных. В Microsoft Access обеспечивается возможность автоматической проверки целостности данных в связанных полях. Целостность данных означает систему правил, используемых для поддержания связей между записями в связанных таблицах, а также для обеспечения защиты от случайного удаления или изменения связанных данных. Установить проверку целостности данных можно, если выполнены следующие условия: связанное поле главной таблицы является ключевым полем или имеет уникальный индекс; связанные поля имеют один тип данных. Здесь существует два исключения. Поле счетчика может быть связано с числовым полем, если в последнем поле в свойстве Размер поля (FieldSize) указано значение Длинное целое (Long Integer), или в обоих полях свойство Размер поля (FieldSize) имеет значение Код репликации (Replication ID); обе таблицы принадлежат одной базе данных Microsoft Access. Для обеспечения целостности данных в нашей базе данных соблюдены все выше сказанные требования и в параметрах созданных связях установлено «Обеспечение целостности данных». 5. Работа с запросами Создание запросов интересно не только тем, что мы можем в виде одной таблицы представить данные из нескольких связанных таблиц и отобрать нужные записи из этих таблиц. Создавать столбцы в запросе, которые являются результатом вычислений над значениями других столбцов. Такие столбцы называются вычисляемыми. Это существенно расширяет возможности запросов. Все запросы создавались с помощью конструктора. Метод создания запроса описан ниже. Выбираем Создание запроса в режиме конструктора. В поле Добавление таблицы добавляем по необходимости таблицу Товар, Склад, Получатели и Поставщики. После добавления всех необходимых таблиц нажимаем кнопку Закрыть в окне Добавление таблицы. Все выбранные таблицы оказываются помещенными на верхней панели окна Конструктора запросов. Затем указываем, какие поля из базовых таблиц будут отображаться в запросе. Включать в запрос можно поля из любой таблицы. Первый запрос «Выборка операций по вложениям» для его построения выбраны следующие поля: ФИО из таблицы вкладчики, Дата из таблицы история, Сумма из таблицы история, Счет из таблицы счет, Операция из таблицы операция. В запросе используется выборка согласно условию отбора по полю Операция равна 5.
Рис. 7. Конструктор запросов. Второй запрос «Поиск клиента» для его построения выбраны следующие поля: ФИО из таблицы вкладчики, Номер паспорта из таблицы вкладчики, Адрес из таблицы вкладчики, Телефон из таблицы вкладчики. В запросе используется выборка по вводимому параметру. Условие отбора по полю ФИО имеет вид: Like "*"+[Введите фамилию]+"*"
Рис. 8. Конструктор запросов. Третий запрос «Процентные отчисления за операции» для его построения выбраны следующие поля: ФИО из таблицы вкладчики, Номер паспорта из таблицы вкладчики, Адрес из таблицы вкладчики, Телефон из таблицы вкладчики, Сумма из таблицы история, Операция из таблицы история. В запросе используется группировка и добавлено вычисляемое поле «Процент», которое имеет формулу: Процент: [Sum-Сумма]*0,15. В запросе используется условие отбора по полю Операция, условие отбора: 3
Рис. 9. Конструктор запросов. Четвертый запрос «Приход денежных средств» для его построения выбраны следующие поля: Дата из таблицы история, Сумма из таблицы история, Операция из таблицы история. В запросе используется фильтр записей с условие отбора по полю Операция равна 5.
Рис. 10. Конструктор запросов.
Рис. 11. Конструктор запросов. 6. Отчеты. Отчеты и формы Access имеют много общего. Однако, в отличие от форм, отчеты не предназначены для ввода и правки данных в таблицах. Они позволяют лишь просматривать и печатать данные. В отчете невозможно изменить исходные данные с помощью элементов управления, как это можно сделать с помощью форм. Хотя в отчетах можно использовать такие же элементы управления для указания состояния переключателей, флажков и списков. Выбрать на панели Объекты кнопку Отчёты. Выполнить команду: Создание отчёта с помощью мастера. В окне Новый отчёт выбрать запрос «Процентные отчисления за операции». Нужно определить, какие поля включить в отчет. Из таблицы «Процентные отчисления за операции» в отчете будут использоваться следующие поля: Группа товаров, Остаток на начало месяца, Количество получения товаров, Затраты, Количество товаров по спискам, Остаток на конец месяца. С помощью кнопки ">" (или дважды щелкнув по элементу списка) поочередно переместите эти поля из списка Доступные поля в список Выбранные поля. В отчете поля появляются слева направо, в соответствии с последовательностью их расположения в списке Выбранные поля. Выбрать макет для отчёта: табличный. Выбрать стиль. Задать имя отчёта: «Процентные отчисления за операции». Для более приятного и стилизированного отображения применяется мастер отчетов. Созданный отчет с помощью мастера придает выводимой информации более приемлемый вид.
Рис. 12. Отчет «Процентные отчисления за операции».
Рис. 13. Отчет «Вкладчики». 7. Макросы Основное назначение макросов — это создание удобного интерфейса приложения: чтобы формы и отчеты открывались при нажатии кнопок в форме или на панели инструментов или же привычным выбором команды меню; чтобы при открытии приложения пользователь видел на экране не окно База данных, наполненное множеством таблиц, запросов, форм и отчетов, а некую понятную форму, с помощью которой можно было бы сразу производить желаемые действия и т. д.
Рис. 14. Конструктор макросов. Открыть в окне База данных щелкнув левой кнопкой мыши по ярлыку Макросы, и нажмите кнопку Создать в верхней части окна. В поле Макрокоманда выбрать Открыть запрос. В поле Имя запроса выбрать Процентные отчисления за операции и закрыть. Ввести в окне сохранение имя макроса Макрос1. С помощью макросов можно создать удобные меню и панели инструментов для приложения. Для вывода информации запроса «Пороцентные отчисления за операции» использовался макрос с названием Макрос1, который преобразует запрос в электронную таблицу. Второй макрос осуществляет одновременный вывод таблицы создаваемой запросом и выполнение самого запроса. Третий запрос осуществляет печать одной из форм.
Рис. 15. Конструктор макросов.
Рис. 16. Конструктор макросов.
Рис. 17. Конструктор макросов. 8. Принцип работы Данная программа выполняет следующие функции: управление с помощью меню (кнопочная форма); ввод данных в базу данных с помощью форм; коррекция данных; реализация запросов к базе данных; формирование и вывод выходных документов на экран, печать. При загрузке файла База данных База данных.mdb, автоматически открывается главная форма: «Главная». Чтобы реализовать автозапуск главной формы необходимо в главном меню СУБД ACCESS XP выбрать «Сервис» и запустить вкладку «Параметры запуска». В разделе «вывод формы» необходимо указать главную форму из списка всех предложенных форм и нажать кнопку ОК. Выбор того или иного действия осуществляется левой клавишей мыши.
Рис. 18. Главная форма. Управление базой данных с помощью меню осуществляется созданием кнопочной формы. Для этого создается Главная форма, из которой доступны все основные функции программы. Доступ к функциям осуществляется с помощью экранных кнопок. С каждой кнопкой связывается процедура обработки события (Кнопка N_Click ()) или макрос, который вызывает заданную форму, отчет, выполняет расчеты и т.п. Главная кнопочная форма показана на рисунке 2. Макрос кнопки Private Sub Кнопка1_Click() ‘ процедура обработки On Error GoTo Err_Кнопка1_Click ‘ события
Dim stDocName As String ‘Название документа Dim stLinkCriteria As String stDocName = «Поставщики» DoCmd.OpenForm stDocName, , , stLinkCriteria ‘ открыть документ Exit_Кнопка1_Click: Exit Sub Err_Кнопка1_Click: ‘обработка ошибки открытия MsgBox Err.Description Resume Exit_Кнопка1_Click
End Sub При нажатии на какую либо кнопку главного меню происходит переход на подчиненные формы. Например, после нажатия на кнопку Клиенты откроется форма, представленная на рисунке 19.
Рис. 19. Форма «Клиенты». Таблица, по которой сделана форма, представлена на рисунке 20
Рис. 18.. Таблица «Вкладчики». Заключение Использование баз данных и информационных систем становится неотъемлемой составляющей деловой деятельности современного человека и функционирования преуспевающих организаций. В связи с этим большую актуальность приобретает освоение принципа построения и эффективного применения соответствующих технологий и программных продуктов. Разработанная в работе БД для решения задач документооборота в банке, позволяет автоматизировать весь документооборот, что позволяет разгрузить весь отчетный документооборот, и сократить ошибки в документации. При появлении новых производственных задач разработчик может в кратчайшие сроки реализовать их в БД. Список литературы Михеева В.Д., Харитонова И.А. Microsoft Access 2000. – БХВ – Изд. «Санкт-Петербург», 2000 Диго С.М. Проектирование и использование баз данных: Учебник. – М.: Финансы и статистика, 1995. Джим Бойс « Использование Microsoft Office97» С.В. Симонович «Информатика. Базовый курс» Ю.Бекаревич « MS Access 2000»
