Файл: Лабораторные работы по курсу Базы данных Введение.pdf

Для организации коллективного доступа в СУБД применяется механизм
блокировок. Суть блокировки состоит в том, что на время выполнения какой-либо операции в БД доступ к используемому объекту со стороны других потребителей временно запрещается или ограничивается. Например, при копировании таблицы она блокируется от изменения, хотя и разрешено просматривать ее содержимое.
Выделим четыре вида блокировок, перечисленных в порядке убывания строгости ограничений на возможные действия:
• полная блокировка;
• блокировка от записи;
• предохраняющая блокировка от записи;
• предохраняющая полная блокировка.
Полная блокировка. Означает полное завершение всяких операций над основными объектами (таблицами, отчетами и экранными формами). Этот вид блокировок обычно применяется при изменении структуры таблицы.
Блокировка от записи. Накладывается в случаях, когда можно использовать таблицу, но без изменения ее структуры или содержимого.
Такая блокировка применяется, например, при выполнении операции слияния данных из двух таблиц.
Предохраняющая блокировка от записи. Предохраняет объект от наложения на него со стороны других операций полной блокировки, либо блокировки от записи. Этот вид блокировки позволяет тому, кто раньше
«захватил» объект, успешно завершить модификацию объекта.
Предохраняющая блокировка о записи совместима с аналогичной блокировкой (предохраняющей блокировкой от записи), а также с предохраняющей полной блокировкой.
Примером необходимости использования этой блокировки является режим совместного редактирования таблицы несколькими пользователями.
Предохраняющая полная блокировка. Предохраняет объект от наложения на него со стороны других операций только полной блокировки.
Обеспечивает максимальный уровень совместного использования объектов.
Такая блокировка может использоваться, например, для обеспечения одновременного просмотра несколькими пользователями одной таблицы. В группе пользователей, работающих с одной таблицей, эта блокировка не позволит никому изменить структуру общей таблицы.
Что касается БД созданных в среде MS Access, после открытия файла сразу создается файл блокировки (название такое же, как и у исходного, но расширение ldb). По умолчанию, на БД накладывается предохраняющая полная блокировка, т.е. каждый из пользователей может просматривать и изменять данные в таблицах, но не может изменять структуру существующих таблиц, форм, отчетов пока БД пользуется два и более человек. Ограничения на изменения структуры БД снимаются, как только пользователю становится

доступна полная блокировка (монопольный доступ), т.е. как только остальные пользователи заканчивают работу с БД.
Рассмотрим ситуацию, потенциально приводящую к конфликту:
1. Пользователь А и Б открыл базу;
2. Пользователь А начал редактирование 2го поля в 3ем кортеже отношения R1;
3. Пользователь Б начал редактирование 2го поля в 3ем кортеже отношения R1;
4. Пользователь А закончил редактирование;
5. Пользователь Б закончил редактирование.
В результате, изменения вносимые пользователем А просуществовали до завершения редактирования пользователем Б. Более того, пользователь Б затер изменения пользователя А даже не подозревая об этом. Потенциально такая ситуация может приводить к нарушению логики функционирования приложения, использующего БД. Поэтому в MS Access при возникновении рассмотренной ситуации, когда пользователь Б пытается закончить редактирование (п. 5), ему выдается предупреждение о том, что запись уже была изменена, а также предлагается выбрать один из вариантов: закончить редактирование, скопировать внесенные им изменения в буфер и просмотреть измененные (пользователем А) значения полей, отменить редактирование.
В этом случае ответственность за правильное внесение данных перекладывается на пользователей БД и прикладных программных средств, использующих БД.
Кроме того, имеется возможность указать степень детализации блокировки, которую использует Microsoft Access в общей базе данных. Если применяется блокировка на уровне страниц, Microsoft Access блокирует страницу с размером 4K (область памяти, в которой находится запись). При этом изменение записи может привести к блокировке других записей на этой странице. Однако блокировка на уровне страниц обычно обеспечивает более высокое быстродействие.
Структура языка SQL
Все операторы языка SQL можно условно разделить на три группы операторов. Оператор языка запросов – SELECT (был рассмотрен во 2ой лабораторной), операторы языка манипуляции данными (Insert, Update,
Delete) и операторы языка определения данных (Create, Drop, Alter).
Запросы DML (ЯМД)
К запросам языка манипуляции данными (Data Manipulation Language) относятся запросы на добавление, удаление и модификацию кортежей.
Добавление кортежа производится командой:
INSERT INTO имя_таблицы [(<список столбцов>)] VALUES (<список
значений>)

Список столбцов и список значений указываются через запятую, а значения добавляются в соответствующие столбцы. Если необходимо добавить кортеж целиком (т.е. значения есть для всех полей и их порядок совпадает с порядком полей в отношении), то описание списка столбцов можно опустить.
Пример 3.1:
Три следующих запроса будут верно исполнены для отношения R1 из лабораторной работы №2:
INSERT INTO R1(ФИО, Дисциплина, Оценка) VALUES («Попова»,
«БД», 3);
INSERT INTO R1 VALUES («Попова», «Моделирование», 3);
INSERT INTO R1(ФИО, Дисциплина) VALUES («Бурковский»,
«Сети ЭВМ»);
Оператор удаления данных DELETE позволяет удалить одну или несколько строк из таблицы в соответствии с условиями, которые задаются для удаляемых строк. Синтаксис оператора DELETE следующий:
DELETE FROM <имя_таблицы> [WHERE <условия_отбора>]
Если условия отбора не задаются, то из таблицы удаляются все строки.
Операция обновления данных UPDATE требуется тогда, когда происходят изменения данных, которые надо отразить в базе данных.
Запрос на обновление может изменить сразу целую группу записей.
Этот запрос состоит из трех частей:
• Предложение UPDATE, которое указывает на обновляемую таблицу;
• Предложение SET, задающее данные для обновления;
• Необязательный критерий WHERE, ограничивающий число записей, на которые воздействует запрос на обновление.
Пример 3.2:
Изменить на 3 оценку по дисциплине «БД» у студента Миронова в таблице R1 (из лабораторной №2):
UPDATE R1 SET R1.Оценка = 3
WHERE R1.ФИО = «Миронов» AND R1.Дисциплина = «БД»;
Запросы SDL (ЯОД)
Команды языка определения схемы данных (Schema Definition Language
– SDL) представляют собой инструкции SQL, которые позволяют создавать и модифицировать элементы структуры базы данных. Например, используя
SDL, можно создавать, удалять таблицы и изменять их структуру, создавать и удалять индексы.

Создание таблицы. Оператор создания таблицы имеет следующий вид:
CREATE TABLE <имя таблицы>
(<имя столбца> <тип данных> [NOT NULL]
[,<имя столбца> <тип данных> [NOT NULL]]…)
Обязательными операндами оператора являются имя создаваемой таблицы и имя хотя бы одного столбца (поля) с указанием типа данных, хранимых в этом столбце.
При создании таблицы для отдельных полей могут указываться некоторые дополнительные правила контроля вводимых в них значений.
Например, конструкция NOT NULL (не пустое) служит для определения обязательного поля.
В табл. 3.1 перечислены типы данных, которые можно использовать при создании таблиц, используя Microsoft Jet SDL и предложение CREATE
(СУБД Access).
Таблица 3.1. Типы данных полей, доступных в Access
Тип данных SQL
тип
Счетчик
COUNTER
Текстовый
TEXT
Memo
LONGTEXT
Денежный
CURRENCY
Дата/время
DATETIME
Числовой (одинарное с плавающей точкой)
SINGLE
Числовой
(двойное с плавающей точкой)
DOUBLE
Числовой (целое)
INTEGER
Числовой (длинное целое)
LONG
Числовой (байт)
BYTE
С помощью конструкции CONSTRAINT можно задать первичный ключ таблицы.
Пример 3.3:
Создание таблицы TABL1:
CREATE TABLE TABL1
( [FIL1] COUNTER, [FIL2] TEXT (10),
[FIL3] CURRENCY, [FIL4] DATETIME,
[FIL5] BYTE, [FIL6] INTEGER,
[FIL7] SINGLE, [FIL8] LONG,
[FIL9] DOUBLE,
CONSTRAINT PrimaryKey PRIMARY KEY ([FIL1]) );
В примере 3.3 поле FIL1 объявлено ключевым, для данного поля создан индекс с именем PrimaryKey.

Похожим образом задается внешний ключ:
Пример 3.4:
Создание таблицы TABL2:
CREATE TABLE TABL2
([FIL1] INTEGER, [FIL2] TEXT (10) NOT NULL, [FIL3] CURRENCY,
[FIL4] LONGTEXT,
CONSTRAINT PrimaryKey PRIMARY KEY ([FIL1],[FIL2]),
CONSTRAINT ForeignKey FOREIGN KEY ([FIL1])
REFERENCES TABL1 ([FIL1]));
В данной таблице (пример 3.4) поле FIL1 объявлено внешним ключом.
Между таблицами TABL1 и TABL2 устанавливается связь «один-ко- многим» по полю FIL1.
Для удаления таблиц служит инструкция
DROP TABLE <имя таблицы>
Для модификация структуры таблицы (добавление, удаление полей, изменения типов полей) используется оператор ALTER TABLE изменения структуры таблицы имеет следующий вид:
ALTER TABLE <имя таблицы>MODIFY | ADD | DROP <имя поля>
[<тип данных>]
Создание индексов
Помимо создания индексов в процессе формирования таблицы (с помощью предложения CONSTRAINT), можно также создавать индексы уже после того, как таблица сформирована:
CREATE [UNIQUE] INDEX <имя индекса>ON <имя таблицы>
(<имя столбца> [ASC | DESC]
[, <имя столбца> [ASC | DESC]…)
Этот оператор позволяет создать индекс для одного или нескольких столбцов заданной таблицы с целью ускорения выполнения запросных и поисковых операций с таблицей. Для одной таблицы можно создать несколько индексов.
Для удаления индексов служит инструкция
DROP INDEX <имя индекса> ON<имя таблицы>
Основы работы с MS Access
Создание макросов в среде MS Access
Макросом называют набор из одной или более макрокоманд (замкнутая инструкция), выполняющих определенные операции, такие как открытие форм или печать отчетов. Макросы могут быть полезны для автоматизации часто выполняемых задач.
Для того, чтобы создать макрос необходимо открыть объекты
«Макросы» и нажать кнопку «Создать» (рис. 3.1).

Рисунок 3.1 – Создание макроса в MS Access
На экране появится окно – конструктор макроса, в котором последовательно (по строкам) можно выбрать какие макрокоманды необходимо выполнить (настроить команды можно внизу окна), как это показано на рисунке 3.2.
Рисунок 3.2 – Описание макрокоманд макроса в MS Access
После описания всех макрокоманд макрос следует сохранить. Теперь в любой момент для выполнения макроса, необходимо обратиться по его имени.
Создание форм и отчетов в среде MS Access
СУБД MS Access предоставляет программисту инструментарий для создания форм и отчетов (для пользователя). Соответствующие объекты можно найти среди объектов БД. Доступно как создание форм (отчетов) по определенным таблицам (запросам), так и самостоятельное создание в режиме дизайнера.
Встроенный язык Visual Basic for Application
СУБД MS Access предоставляет возможность описания процедур на языке высокого уровня Visual Basic for Application (VBA). Этот язык встроен во все программные средства, относящиеся к MS Office, и он позволяет работать с объектами БД через выполнение SQL-запросов. Язык VBA является родственником VB и Basic. Также этот язык является процедурным,

поддерживает деление на модули, поддерживает дизайнер форм.
Обеспечивает обработку исключительных ситуаций и выполнение транзакций. Процедуры и функции, описанные в области видимости public, могут также быть использованы при построении SQL-запросов, подобно встроенным функциям СУБД.
Создать модули можно на соответствующей странице объектов MS
Access.
Ниже на примере 3.5 представлена процедура, которая используется для выполнения SQL-запросов к БД. Более подробно о синтаксисе языка можно узнать из автономной справки MS Access-а.
Пример 3.5:
Функция добавления студента в группу для отношения R2 (л/р №2).
Если студент уже есть – группа обновляется, иначе – создается новая запись:
Public Function foo3_5(gr_student As String, fio_student As String)
' объявляем переменные
Dim strSQL As String
Dim rstSQL As ADODB.Recordset
Set rstSQL = New ADODB.Recordset
' ищем записи о студенте
strSQL = "select * from R2 where fio like """ & fio_student & """"
' выполнить запрос
rstSQL.Open strSQL, CurrentProject.Connection
If rstSQL.EOF Then
' если ни одной строки не найдено, то добавить новую
strSQL = "insert into R2 (gr,FIO) values(""" & gr_student &
""",""" & fio_student & """)"
Else
' если найдена, то обновляем номер группы
strSQL = "update R2 set gr=""" & gr_student & """ where fio like
""" & fio_student & """ and gr not like """ & gr_student & """"
End If
rstSQL.Close
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL ' выполнить запрос
DoCmd.SetWarnings True
End Function;
“” (дважды двойные кавычки) – используется для вставки в строку “, т.е. вторые двойные кавычки используются как символ экранирования
(например, как в C \\). Комментарии начинаются с символа ‘ – одинарной кавычки. В функции формируется запрос, выполняется и проверяется результат выполнения – если нет полученных кортежей, значит необходимо добавить новую запись «Группа», «Студент», иначе обновить записи для этого студента о его принадлежности к группе.

Задание на лабораторную работу №3
Обязательная часть
1. Создать базу данных по любой предметной области (желательно по курсовой работе), которая должна минимум содержать таблицу, состоящую минимум из 6 полей, где обязательно должно присутствовать поле типа date.
Для создания таблиц БД использовать скриптовый файл или
макрокоманду, содержащую набор SQL-команд из части языка SDL;
2. Реализовать процедуры Добавления, Удаления, Поиска и
Изменения, с помощью SQL;
3. Организовать оконный интерфейс для функций, созданных на предыдущем этапе (добавления, удаления, поиска и изменения);
4. Поиск должен осуществляться с использованием индексов, т.е. поля, по которым осуществляется поиск, должны быть проиндексированы. Для создания индексов использовать CREATE INDEX.
Выполнение обязательных пунктов = 70%
Бонус (+ 15%): Для получения дополнительных баллов реализовать кодовые поля в основной таблице и справочник(и) для расшифровки этих полей (подобно базе allauto.mdb).
Бонус (+ 15%): Организовать механизм авторизации – вход в БД по паролю для нескольких пользователей (статья справки «Пароли (MDB)»).
Прием работы
Прием происходит при наличии оформленного отчета и работающей БД, созданной в среде MS Access.
Вопросы
1. На какие части можно разделить язык SQL, какие команды им соответствуют?
2. Что такое механизм блокировки, какой бывает механизм блокировки?
3. Какие существуют варианты для создания индекса у поля в таблице?
4. Для чего используются индексы?
5. Как обновить несколько полей для нескольких кортежей таблицы одним запросом?
6. Что определяет ключевое слово Constraint?
7. Что такое VBA?
8. Можно ли выполнить добавление данных без указания названия полей, в которые добавляются значения? (почему нельзя или как можно)
9. Для чего необходима блокировка, какие конфликтные ситуации могут возникать при отсутствии блокировок, какие пути выхода из этих ситуаций?
10. Чем отличается блокировка на уровне записей от блокировки на уровне страниц?

Лабораторная работа №4
Тема: основные функции СУБД, журнализация изменений в базе данных.
Цель: моделирования механизмов протоколирования и отката команд.
Навыки и умения: реализация алгоритма журнализации, написание клиентского интерфейса, программирование на VBA.
Теоретический базис
Основные функции СУБД
К основным функциям СУБД можно отнести следующие:
1. Поддержка языков БД
Как вы уже знаете из предыдущих лабораторных работ, для взаимодействия с БД используются специальные языки: язык определения данных, язык манипулирования данными и язык запросов. В современных
СУБД все эти три языка объединены в единый интегрированный язык – SQL.
Важнейшая функция СУБД – поддержание этих трех языков (т.е. SQL).
2. Управление транзакциями
Транзакция – это последовательность операций над
БД, рассматриваемых СУБД как единое целое. Либо транзакция успешно выполняется, и СУБД фиксирует (COMMIT) изменения БД, произведенные этой транзакцией, во внешней памяти, либо ни одно из этих изменений никак не отражается на состоянии БД (ROLLBACK). Более подробно механизм транзакций будет рассмотрен в следующих лабораторных работах.
3. Управление данными во внешней памяти.
4. Управление буферами оперативной памяти.
5. Обеспечение целостности и безопасности базы данных.
Целостность базы данных – свойство базы данных, при наличии, которого база данных содержит полную и непротиворечивую информацию, необходимую и достаточную для корректного функционирования приложений. Поддержание целостности базы данных включает проверку целостности и ее восстановление в случае обнаружения противоречий в базе данных. Целостное состояние базы данных описывается с помощью ограничений целостности в виде условий, которым должны удовлетворять хранимые в базе данные. Примером таких условий может служить ограничение диапазонов возможных значений атрибутов объектов, сведения о которых хранятся в базе данных, или отсутствие повторяющихся записей в таблицах реляционных баз данных.
6. Журнализация
Одним из основных требований к СУБД является надежность хранения данных во внешней памяти. Под надежностью хранения понимается то, что
СУБД должна быть в состоянии восстановить последнее согласованное состояние БД после любого аппаратного или программного сбоя. Обычно