Базы данных и системы управления базами данных

Базы данных и системы управления базами данных

База данных – это организационная структура, предназначенная для хранения информации.

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

Работа с СУБД MS Access

Целостность данных — это набор правил, гарантирующих, что Access будет работать только с непротиворечивыми данными и разрешёнными операциями.

Активизировать команду Сервис Схема данных в диалоговом окне Схема данных:

1. Щёлкнуть по кнопке Добавить таблицу.
2. В диалоговом окне из списков выбрать таблицы, между которыми создаются связи. Закрыть окно Добавление таблицы. Искомые таблицы появятся в окне Схема данных.
3. Выделить в 1-й таблице ключевое поле и с помощью мыши перетащить его на одноименное поле 2-й таблицы. При отпускании кнопки мыши откроется диалоговое окно Связи.
4. Установить флаг Обеспечение целостности данных.
5. Щёлкнуть по кнопке Создать. Появится связь 1:1.

Предположим, что требуется установить связь между таблицами «Кафедра» и «Преподаватель» через поле ККАФ (код кафедры). В таблице «Кафедра» это поле является уникальным ключом , а в таблице «Преподаватель» — внешним ключом. Если схема данных создается заново, то при нажатии на кнопку «Схема данных» поверх окна схемы данных появится окно «Добавление таблицы«. В этом окне следует выделить требуемые таблицы и нажать «Добавить«( рис. 6.5)

В результате в окно схемы данных будут добавлены графические образы двух таблиц:

Необходимо перетащить мышью поле ККАФ таблица «Кафедра» на поле ККАФ таблицы «Преподаватель«. В открывшемся окне «Изменение связей» следует установить флажок «Обеспечение целостности данных«. В этом случае Access будет выдавать предупреждающие сообщения о неправильном вводе данных, если, например, в поле ККАФ подчиненной таблицы «Преподаватель» будет введено значение, отсутствующее в поле ККАФ базовой таблицы «Кафедра» ( рис. 6.6).

Обратите внимание, что Access автоматически определил тип связи как «один-ко-многим» ( рис. 6.7).

Можно также установить флажки «каскадное обновление связей» и «каскадное удаление связей«. В этом случае Access автоматически скорректирует (удалит) записи в подчиненных таблицах , если будут изменены записи в базовой таблице.

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

Для установления связей по составному ключу необходимо в окне «Изменение связей» в полях «Таблица/Запрос» и «Связанная таблица/запрос» вручную выбрать из списков пары связываемых полей. На рис. 6.9 показан пример связи по составному ключу .

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

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

Связь между таблицами позволяет:

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

Установить флаг Обеспечение целостности данных.

Установить дополнительно флаги Каскадное обновление связанных полей и Каскадное удаление связанных записей.

Работа с запросами

Запрос — это отбор записей в разнообразных формах, в соответствии с выбранными условиями.

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

Виды запросов

• На выборку;
• Запрос с параметром (критерий задаёт сам пользователь)
• Итоговые запросы (производят вычисления по заданному полю и выдают результат);
• Запросы на изменение (позволяют автоматизировать — заполнение полей таблиц);
• Перекрёстные запросы (позволяют создавать результирующие таблицы на основе результатов расчётов, полученных при анализе группы таблиц)
• Специфические запросы — запросы к серверу БД, написанные на языке запросов SQL

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

Запросы и фильтры

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

Основные отличия запросов и фильтров заключаются в следующем.

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

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

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

Работа с формами

• Вводить данные в таблицы БД без непосредственного доступа к самим таблицам;
• Выводить результаты работы запросов в виде красиво оформленных форм.

Существует два вида формирования структуры форм:

• На основе таблицы;
• На основе запроса.
• Возможен и комбинированный (творческий) подход.

Работа с отчётами

Отчёты служат для форматированного вывода данных на печатающее устройство .

Здесь существуют средства ручного, автоматического и автоматизированного проектирования.

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

Мастер отчётов работает в шесть этапов.

1. выбор таблицы или запросов, на которых отчёт базируется; выбор полей, отражаемых в отчёте;
2. выбор полей группировки (уровней и интервалов группировки);

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

Краткие итоги

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

Функции и возможности

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

Работа с таблицами

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

Работа с таблицами

Импорт таблиц

Access позволяет импортировать разные типы таблиц. Здесь есть возможность импортировать документы Excel для их последующей конвертации в базу данных. Программа также работает с документами Word. Разумеется, что в Acces можно открывать для редактирования и конвертации базы данных MySQL, Firebird, SQLite и многих других.

Работа с графиками и диаграммами

С помощью графиков и диаграмм можно отображать разные виды информации в понятном формате. В Acces присутствует множество различных графиков и диаграмм: пользователь может использовать финансовые, точечные, номинальные, объемные графики. Удобно также то, что инструменты для работы с этими видами объектов аналогичны тем, которые используются в Word и Excel.

Работа с формулами

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

Создание форм

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

Экспорт данных

Готовый документ можно быстро экспортировать в другие форматы, а проект сохранить не только в формате базы данных, но и как документ Excel, RTF, HTML, PDF, XML. Также с помощью инструмента экспорта любой проект можно сохранить в виде текстового документа.

Верхний колонтитул

Представляет собой приблизительно то же самое что и в MS Word. Однако в Access его используют для создания различных наименований столбцов с данными. Кроме того, колонтитулы как верхний, так и нижний можно использовать для добавление номеров страниц. По умолчания в Microsoft Access создается одновременно два колонтитула – верхний и нижний. Чтобы создать только один нужно перейти в раздел «Вид», затем нажать на кнопку колонтитулы. Для удаления одного из них из рабочей области нужно воспользоваться свойствами. В параметр «Высота колонтитула» нужно вписать значение «ноль».

Знакомство с Microsoft Access.

Microsoft Access – это интерактивная реляционная СУБД (relational database management system – RDBMS) для WINDOWS.

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

Access также предоставляет в распоряжение пользователя механизмы работы с базами данных различных форматов. К примеру, можно прямо обращаться к базам данных dBASE, Paradox или Btrieve без конвертирования их в формат, используемый Access.

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

СУБД – система управления базами данных.

Функции СУБД

1. Определения данных

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

2. Обработка данных

СУБД должна уметь обрабатывать запросы пользователя на выборку, изменение или удаление соответствующих данных в базе данных или на добавление новых данных в базу данных.

3. Безопасность и целостность данных

СУБД должна контролировать пользовательские запросы и пресекать попытки нарушения правил безопасности и целостности, определенных АБД.

4. Восстановление данных и дублирование

Восстановление и дублирование данных осуществляется СУБД или администратором.

5. Введение словаря данных

Словарь данных – это исчерпывающий набор таблиц или файлов, представляющий собой каталог всех описаний данных.

6. Производительность

СУБД должна выполнять все указанные функции с максимально возможной эффективностью.

Характеристика СУБД Microsoft Access

СУБД MS Access похожа по своему назначению и возможностям на программу электронных таблиц MS Excel. Однако между ними существуют принципиальные и существенные различия:

• таблица в MS Access отличается от таблицы MS Excel тем, что в ней для каждого поля записи определен тип данных, т.е. нельзя в разные строки столбца таблицы заносить разнотипные данные;
• MS Access позволяет не только вводить данные в таблицы, но и контролировать правильность вводимых данных. Для этого устанавливают правила проверки на уровне таблиц;
• с таблицами MS Excel удобно работать, если они содержат ограниченное количество строк. Таблицы БД могут включать в себя огромное количество записей. СУБД обеспечивает удобные способы извлечения из этого множества необходимой информации;
• MS Access позволяет хранить все данные в одном файле и осуществлять доступ к этим данным постранично;
• создание связей между таблицами в MS Access позволяет совместно использовать данные из разных таблиц. При этом для пользователя они будут представляться одной таблицей;
• взаимосвязи между таблицами MS Access позволяют избежать дублирования данных, сэкономить память компьютера, а также увеличить скорость обработки информации. Для этого таблицы, содержащие повторяющиеся данные, разбивают на несколько связанных таблиц;
• MS Excel позволяет работать нескольким пользователям с одним документом, однако эти возможности ограничены. MS Access может поддерживать одновременную работу 50 – ти пользователей;
• система защиты от несанкционированного доступа MS Access позволяет каждому пользователю или группе пользователей работать с теми объектами, на которые выданы права администратором системы. Возможен запрет на использование некоторых команд меню, открытие определенных форм, изменение данных в таблицах или формах.

Объекты СУБД MS Access

Таблицы

Таблицы базы данных могут иметь различное назначение:

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

Таблицы переменной информации – это таблицы, информация об объектах в которых постоянно дополняется или изменяется пользователем.

Запросы

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

– на выборку (поиск (выбора) информации в конкретной таблице).

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

– добавление (предназначены для автоматического добавления записей в таблицы).

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

– создание таблиц (создание новых таблиц на основе уже имеющихся).

Формы

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

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

Отчеты

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

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

Страницы

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

Модули

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

Макрос

Макрос – программа, состоящая из последовательности макрокоманд. Макрокоманда – это инструкция, ориентированная на выполнение определенного действия над объектами Access и их элементами.

Написание кода на C# для выполнения запросов к MS Access

Добавим в класс формы строковое поле string – строку подключения к БД. В строке подключения в значении параметра Data Source указывается путь к файлу с базой данных.

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

Далее добавим поле класса типа OleDbConnection. Экземпляр данного класса понадобится для установления соединения с базой данных MS Access.

В конструкторе класса создадим экземпляр OleDbConnection и установим соединение с БД. Т.е. при запуске программы будет устанавливаться соединение с Access.

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

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

При нажатии кнопки SELECT2 будет выполняться запрос на получение многострочных данных из нескольких столбцов (по сути таблицы). Данные выводятся в ListBox.

Далее запрос вставки данных (INSERT) в MS Access на C#:

Метод ExecuteNonQuery() класса OleDbCommand выполняет запрос и возвращает целое число типа int – количество строк затронутых выполняемым запросом. Данный метод подходит для запросов вставки INSERT, обновления UPDATE и удаления DELETE. Т.е. для тех, которые не возвращают данные.

Запрос обновления данных UPDATE:

Пример SQL-запроса удаления данных (DELETE) из БД Access с помощью языка C#:

Скачать исходник программы из данного урока можно, нажав на кнопку ниже. Программа написана в среде разработки Visual Studio.

Более подробное объяснение темы выполнения запросов из программы на C# к базе данных Microsoft Access доступно в видеоуроке, представленном ниже.

1. Функции SQL для обработки текста

Реализация SQL в СУБД Access имеет следующие функции для обработки текста:

Переведем названия товаров в верхний регистр с помощью функции UCase():

SELECT Product, UCase(Product) AS Product_UCase FROM Sumproduct

Выделим первые три символа в тексте с помощью функции LEFT():

SELECT Product, LEFT(Product, 3) AS Product_LEFT FROM Sumproduct

Виды запросов

Microsoft Access поддерживает множество типов. Описание главных категорий:

1. Select позволяет получать записи или сводки (итоги).
2. Создание таблиц. Аналогично к Select, но результаты помещаются в таблицу, которая создается автоматически.
3. Добавление. Аналогично первым двум, но результаты добавляются в существующую таблицу.
4. Update изменяет (обновляет) данные в записях.
5. Delete.

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

Запросы на примере Select

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

1. Перейти во вкладку «Создать» и нажать на кнопку «Конструктор запросов».
2. Выбрать тип.
3. Выбрать таблицу и поля. Основным шагом является указание таблицы или таблиц для использования и полей для отображения. Необходимо выбрать таблицу из списка. Выбранная таблица размещается в верхней части окна. Оттуда можно выбрать поля, нужно дважды нажать по ним или перетащить их в нижнюю часть окна. Важно убедиться, что опция «Вывод на экран» активна для требуемых ячеек.
4. Применить сортировку и изменить порядок полей. Как только ячейки помещены в сетку QBE (нижняя часть экрана), можно изменить их последовательность. Для этого нужно щелкнуть на столбец и перетащить его в нужное место. Чтобы отсортировать результаты, достаточно выбрать критерий в меню «Сортировка».
5. Изменить название. Данные могут храниться под названиями, которые нелегко понять пользователям. Используя выражения, можно изменить имя, которое видит пользователь. Например, имя «Поле 1» можно изменить на «ID Клиента», поместив новое имя с последующим двоеточием и исходным именем в ячейку поля QBE: «ID Клиента: [Поле 1]».
6. Установить свойства. В правой части экрана есть окно свойств. Для отображения свойств запроса нужно временно отключить флажок в «Вывод на экран».
7. У каждой области также есть свойства, которые можно установить. Для отображения свойств, нужно вернуть флажок и нажать на требуемое поле. В зависимости от типа данных доступны разные свойства. Наиболее важные свойства для числовых типов и типа даты. В свойствах можно указать, как ячейки будут форматироваться при выполнении.
8. Просмотреть результаты и эквивалент SQL. Когда запрос завершен, можно просмотреть его результаты, переключившись из режима конструктора в «Режим таблицы». Также можно просмотреть в виде кода SQL. Таким образом, можно редактировать синтаксис SQL напрямую, просматривать результаты и переключаться в режим конструктора.
9. Нажать кнопку «Выполнить» и просмотреть результат.

Настройка условий и их типы

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

Чтобы еще больше сузить область, можно ввести критерии для нескольких полей одновременно. При вводе критериев в одной строке выполняется операция «И» между полями. То есть извлекаются строки, которые соответствуют критериям в поле 1 и в поле 2 и т. д. Если условия размещены в разных строках, выполняется операция «ИЛИ».

• самым простым условием является точное соответствие. Стоит ввести желаемую величину в разделе «Условие отбора»;
• <>, <, >, BETWEEN, AND. Можно получить записи, в которых значение поле не равно определенной величине, используя символ «<>». Точно так же используются знаки >, <, >= или <= для диапазонов. Чтобы выбрать записи с величиной между двумя значениями, используются операторы BETWEEN и AND;
• нулевые ячейки. Чтобы выбрать записи со значениями Null, нужно ввести Null. Но важно помнить, что для текстовых полей строки нулевой длины («») не являются пустыми;
• OR и IN. Чтобы выбрать записи, в которых поле может иметь одно из нескольких величин, следует использовать оператор OR. Можно написать: «1000 OR 2000». В качестве альтернативы команда IN выполняет ту же функцию: «IN (1000, 2000)». Второй синтаксис проще использовать, если существует много значений.

Поиск по шаблону. Иногда нужно найти конкретную букву или цифру. В сочетании с командой LIKE подстановочные знаки позволяют указывать такие условия. Наиболее часто используемые знаки: «?» – один символ; «*» – любое количество символов, «#» – однозначный символ, «[..]» – список символов, «[! ..]» – нет в списке символов.

Использование параметров

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

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

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

В следующем примере переменная [Enter State Name:] является критерием в поле [State], а [Enter Minimum Age:] является параметром в поле [Age]. Когда этот запрос выполняется, пользователю предлагается указать желаемое состояние и минимальный возраст, и извлекаются записи, соответствующие этим критериям.

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

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

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