Министерство образования Республики Беларусь БЕЛОРУССКИЙ НАЦИОНАЛЬНЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ Кафедра «Экономика и управление научными исследованиями, проектированием и производством» СЕТЕВЫЕ ТЕХНОЛОГИИ И БАЗЫ ДАННЫХ Лабораторный практикум Ч а с т ь 1 СУБД MS ACCESS М и н с к Б Н Т У 2 0 1 1 Министерство образования Республики Беларусь БЕЛОРУССКИЙ НАЦИОНАЛЬНЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ Кафедра «Экономика и управление научными исследованиями, проектированием и производством» СЕТЕВЫЕ ТЕХНОЛОГИИ И БАЗЫ ДАННЫХ Лабораторный практикум В 3 частях Ч а с т ь 1 СУБД MS ACCESS М и н с к Б Н Т У 2 0 1 1 УДК 004.65 (076.5) ББК 32.97я7 С 33 С о с т а в и т е л ь А.Г. Ляхевич Р е ц е н з е н т ы : А.А. Дудкин, П.В. Мелюшин С 33 Сетевые технологии и базы данных: лабораторный практикум: в 3 ч. / сост. А.Г. Ляхевич. – Минск: БНТУ, 2011. – Ч. 1: СУБД MS ACCESS. – 45 с. Практикум предназначен для студентов специальности 1-27 01 01 «Экономика и организация производства» направления 1-27 01 01-08 «Приборостроение» и специальности 1-26 02 02 «Менеджмент» направления 1-26 02 02-03 «Инновационный менеджмент». Первая часть практикума знакомит студентов с основами работы с СУБД MS ACCESS и языком SQL. ISBN 978-985-525-529-2 (Ч. 1) ISBN 978-985-525-530-8 © БНТУ, 2011 3 Лабораторная работа № 1 ОСНОВНЫЕ ПРИЕМЫ РАБОТЫ С MS ACCESS. СОЗДАНИЕ ТАБЛИЦ И СХЕМЫ ДАННЫХ Цели работы: • изучение интерфейса СУБД MS Access; • получение навыков создания базы данных в СУБД MS Access; • получение навыков создания и изменения таблиц базы данных; • получение навыков создания связей «первичный – внеш- ний ключ» между таблицами реляционной базы данных. Теоретический материал Создание таблицы возможно одним из следующих способов: • режим таблицы удобнее всего. Создание таблицы осу- ществляется путем ввода данных в пустую таблицу. После со- хранения таблицы остаются только те столбцы, которые были переименованы или в которые были введены данные (рис. 1.1); Рис. 1.1. Создание таблиц в режиме таблицы • режим конструктора предназначен для создания таб- лицы при помощи графического интерфейса. Приводится спи- сок столбцов таблицы: указывается имя поля и тип данных, длина поля, является ли поле первичным ключом таблицы, до- пустимо ли указание в поле пустых значений и т. д. (рис. 1.2). 4 Тип данных «Поле объекта OLE» позволяет хранить в базе данных объекты, обрабатываемые другими приложениями, например, документы Word, Excel, изображения, видеофайлы и т. п. Для добавления объекта необходимо переключиться в режим отображения данных таблицы и в контекстном меню выбрать пункт «Добавить объект» (рис. 1.3). Рис. 1.2. Создание таблиц в режиме конструктора Рис. 1.3. Добавление объекта OLE 5 • мастер таблиц – это создание таблицы при помощи ма- стера на основе шаблонов других таблиц; • импорт таблиц – импорт таблиц из других файлов Access или файлов других форматов (MS Excel, СУБД Visual FoxPro и др.). При импорте в текущей базе данных просто со- здается копия соответствующих таблиц, а связь с ними отсут- ствует. Для выполнения импорта используется команда меню «Файл/Внешние данные/Импорт». Импорт таблицы Excel можно осуществить и просто вставив ее через контекстное меню (вызывается на белом фоне в списке таблиц); • связь с таблицами – позволяет выполнить те же действия, что и «Импорт таблиц» с той разницей, что сама таблица факти- чески останется в другой базе данных, а в текущей БД Access бу- дет просто ссылка на эту таблицу. Используя «Связь с таблица- ми», можно превратить MS Access в клиентскую программу для доступа к СУБД MS SQL Server, Oracle или другой базе данных, имеющей драйвер ODBC (меню «Файл/Внешние данные/Связь с таблицами/Тип файлов/Базы данных ODBC/кнопка New»). При создании таблицы в режиме конструктора или при по- следующем редактировании таблицы можно создать столбец подстановок. Столбец подстановок – это столбец, в который значения вводятся не вручную, а из раскрывающегося списка. В качестве источника данных списка может выступать стол- бец другой таблицы или фиксированный набор значений поль- зователя. Создание столбца подстановок: открыть таблицу в режиме конструктора, выбрать нужный столбец и вкладку «Подстановка». Указать «Тип элемента управления» – «Поле со списком», указать «Тип источника строк» – «Таблица или запрос», рядом с полем «Источник строк» нажать кнопку с тремя точками, сформировать запрос в появившемся диалого- вом окне «Инструкция SQL:построитель запросов», закрыть его, в ответ на запрос сохранения инструкции SQL указать «Да» (рис. 1.4). Для ввода фиксированного набора значений в поле «Тип источника строк» указывается «Список значений», 6 а сами значения вводятся в поле «Источник строк», разделяя разные значения точкой с запятой. После вставки столбца подстановок в него можно будет вводить значения вручную или выбирать их из раскрывающегося списка. Для запрета ручного ввода значений, не совпадающих со списком, необхо- димо открыть таблицу в режиме конструктора, выбрать стол- бец подстановки и на вкладке «Подстановка» в поле «Огра- ничиться списком» указать «Да». Рис. 1.4. Создание столбца подстановок Схема данных позволяет определить связи между таблица- ми, используя понятия «первичный ключ» и «внешний ключ» реляционной модели данных. Она также позволяет установить правила каскадного удаления и обновления строк в связанных таблицах. Для создания схемы данных – «Сервис/Схема дан- 7 ных» – при помощи графического интерфейса установить свя- зи между таблицами, перетянув поле одной таблицы на свя- зываемое поле другой таблицы (рис. 1.5). Рис. 1.5. Схема данных Если установить флажок «Обеспечение целостности дан- ных», то Microsoft Access не позволяет добавлять в связанную таблицу записи, для которых нет соответствующих записей в главной таблице или же изменять (удалять) записи в главной таблице, для которых имеются подчиненные записи в связан- ной таблице. Если дополнительно установить флажок «Кас- кадное обновление» связанных полей, любое изменение зна- чения в ключевом поле главной таблицы приведет к автома- тическому обновлению соответствующих значений во всех связанных записях. Постановка флажка «Каскадное удаление» приведет к автоматическому удалению связанных записей в подчиненной таблице при удалении записи в главной таблице. Создавая связи, необходимо помнить о реляционной модели данных: связи устанавливаются не между любыми полями, а 8 только между ключевым полем таблицы и внешним ключом в другой таблице (некоторым полем во второй таблице, содер- жащим те же значения, что и ключевое поле первой таблицы, не обязательно все значения). Тип создаваемой в схеме данных связи зависит от полей, для которых определяется связь: 1) отношение «один-ко-многим» создается в том случае, когда только одно из полей является ключевым или имеет уникальный индекс; 2) отношение «один-к-одному» создается в том случае, ко- гда оба связываемых поля являются ключевыми или имеют уникальные индексы. Задания для выполнения Задание 1. Создать в MS Access таблицы следующего со- держания (табл. 1.1–1.3): Таблица 1.1 Склад Изделие Цена Наличие Поставщик Приме- чание F117A 10000000 1 д. Гадюкино – F14 1000000 20 корпорация Мак- доннел Дуглас – F15 3000000 15 корпорация Мак- доннел Дуглас – F16 2000000 10 корпорация Мак- доннел Дуглас – Палочки для воды 100 5 – – Палочки для еды 1 10000 Бьенхоа – 9 Первичным ключом таблицы является столбец «Изделие». Не допускается вводить в столбцы «Наличие» отрицательные значения (выводится предупреждающее сообщение). Таблица 1.2 Заказ № заказа Заказчик Изделие Количество 1 ВВС США F14 1 2 Ильич Рамирес Санчес F117A 2 3 ВВС США F14 130 4 ВВС Ирана F16 1 5 Петрович Палочки для воды 15 6 Ричард Роу Палочки для еды 1000 Первичным ключом таблицы является столбец «№ заказа». Столбец «Изделие» является столбцом подстановки, данные для которого берутся из столбца «Изделие» таблицы «Склад» (запрещается вводить в столбец значения, не перечисленные в столбце «Изделие» таблицы «Склад»). В столбец «Количе- ство» запрещается вводить отрицательные значения. Таблица 1.3 Оплата № заказа Форма расчетов Оплата 1 Безналичный перевод 1000000 2 Наличные 0 3 Безналичный перевод 10000000 4 Казначейские облигации США 2000000 5 Бартер 1500 10 Первичным ключом таблицы является столбец «№ заказа». Столбец «Форма оплаты» является столбцом подстановки, данные для которого представляют список фиксированных значений (примеры всех значений приведены в таблице). В столбец «Форма оплаты» разрешается вводить и значения, не указанные в списке. Задание 2. Выполнить следующие изменения структуры таблицы «Склад»: • удалить из таблицы «Склад» столбец «Примечание»; • добавить в таблицу «Склад» поле «Документация», име- ющее тип данных «Поле объекта OLE». Используя меню «Вставить/Объект», вставить по строчкам этого столбца доку- менты Word и Excel. Должны быть вставлены как документы уже имеющиеся на диске (со связью и без установления связи с документом на диске), так и заново созданные документы; • установить для столбца «Цена» таблицы «Склад» значе- ние по умолчанию 0.25, а также запрет на ввод отрицательных чисел с выводом соответствующего предупреждения при не- правильном вводе данных. Задание 3. При помощи меню «Сервис/Схема данных» установить связь между таблицами: • столбец «Заказ» таблицы «Изделие» является внешним ключом для таблицы «Склад». При попытке удалить изделие, по которому существует заказ, удаление изделия блокируется; • столбец «№ заказа» таблицы «Оплата» является внеш- ним ключом для таблицы «Заказ». При обновлении или уда- лении записей в таблице «Заказ» соответствующие изменения происходят в таблице «Оплата». 11 Лабораторная работа № 2 ОСНОВНЫЕ ПРИЕМЫ РАБОТЫ С MS ACCESS. СОЗДАНИЕ ЗАПРОСОВ, ИСПОЛЬЗОВАНИЕ ЯЗЫКА SQL В ЗАПРОСАХ Цели работы: • получение навыков создания запросов в СУБД MS Access; • получение навыков использования языка SQL в реляци- онной базе данных. Теоретический материал Наиболее гибким вариантом является создание запроса в режиме конструктора: задается имя таблицы, имена отбирае- мых столбцов, условия отбора, сортировки и вывода на экран. Условия, находящиеся в одной строке конструктора, объеди- няются логическим оператором «и», а в разных строках – опе- ратором «или» (рис. 2.1). Если флажок «Вывод на экран» снят, то сам столбец на экран не выводится, но условие, записанное в нем, продолжает действовать. Возможно создание вычисля- емых столбцов в формате «Название столбца: Вычисления». Названия столбцов заключаются в квадратные скобки, если в названии содержится хотя бы один пробел. Не следует выно- сить на панель запроса более одной таблицы, если вы не соби- раетесь объединять их. Таблицы объединяются только по по- лям с совпадающими данными. Для успешного объединения рекомендуется непосредственно указать эти столбцы, протя- нув мышью связь от одного столбца к другому. Используя команду меню «Вставка/Запрос», можно также создать запрос в режиме мастера запросов по одному из сле- дующих вариантов: • простой запрос позволяет указать таблицы и имена столбцов, включаемые в результаты запроса; • перекрестный запрос напоминает сводную таблицу Excel (необходимо указать строки, столбцы, данные и функцию об- 12 работки данных). Для создания перекрестного запроса на осно- вании нескольких таблиц надо сначала создать простой запрос, содержащий все необходимые столбцы из этих таблиц; • повторяющиеся записи выводит повторяющиеся записи из таблицы: указывается столбец, по которому надо искать повторы, а также другие столбцы. Если дополнительные столбцы не указаны, то выводится количество повторов; • записи без подчиненных – поиск записей, не имеющих связей с записями в другой таблице (например, клиенты, не имеющие заказов). Рис. 2.1. Запрос в режиме конструктора Помимо создания запроса одним из вышеперечисленных способов можно задать запрос и непосредственно, в виде ин- струкций SQL. SQL (Structured Query Language – структури- рованный язык запросов) – это язык, предназначенный для выборки и обработки информации, содержащейся в реляци- онной базе данных. Для перехода из режима конструктора за- проса в режим SQL необходимо воспользоваться командой меню «Вид/Режим SQL». Запросы на выборку позволяют получить информацию из одной или нескольких таблиц и оформляются ключевым сло- 13 вом SELECT. Поскольку инструкции SQL могут читаться как обычные предложения английского языка, то дальнейшее по- яснение инструкций будет даваться путем перевода соответ- ствующих ключевых слов на русский язык. В большинстве случаев это достаточно для понимания инструкции. Простой запрос на выборку SELECT DISTINCT столбец1, (столбец2+столбец3)*5 FROM Таблица1 WHERE (столбец1>10) OR (столбец4 LIKE 'М_нск%') AND NOT (столбец3 IN 10,20,30) OR (столбец5 IS NULL) OR (столбец6 BETWEEN 100 AND 200) OR ((столбец3 - столбец2) IS UNKNOWN) OR (столбец7 IS FALSE) ORDER BY столбец1, столбец2 ASC Русский перевод ключевых слов инструкции: ОТОБРАТЬ ОТЛИЧАЮЩИЕСЯ_ЗНАЧЕНИЯ столбец1, (столбец2+столбец3)*5 ИЗ Таблица1 ГДЕ (столбец1>10) ИЛИ (столбец4 ПОХОЖ _НА 'М_нск%') И НЕ_ВЕРНО_ЧТО (столбец3 СРЕДИ_ЗНАЧЕНИЙ 10,20,30) ИЛИ (столбец5 НЕ_ОПРЕДЕЛЕН) ИЛИ (столбец6 МЕЖДУ 100 И 200) ИЛИ ((столбец3- столбец2) РЕЗУЛЬТАТ_НЕ_ОПРЕДЕЛЕН) ИЛИ (столбец7 СОДЕРЖИТ_ЗНАЧЕНИЕ ЛОЖЬ) УПОРЯДОЧИТЬ ПО столбец1, столбец2 ПО_ВОЗРАС- ТАНИЮ В шаблоне LIKE подчеркивание означает любой одиноч- ный символ, а «%» – произвольное число любых символов (в MS Access вместо «_» используют «?», а вместо «%» исполь- зуют «*»). Так, под шаблон «М_нск%'» подходят слова 14 «Минск», «Менский» и т. п. Результат вычислений может быть неопределен (unknown), если хотя бы один столбец содержит неопределенное значение (null). Сортировка по двум столбцам выполняется следующим образом: сначала по одному столбцу, а в рамках повторяющихся значений первого столбца – по вто- рому столбцу. Так можно отсортировать сотрудников по долж- ностям, а в рамках должности бухгалтера – по стажу. Сорти- ровка по возрастанию может не указываться (сортируется по умолчанию), либо задаваться ключевым словом ASC. При сор- тировке по убыванию вместо ASC указывается DESC. Многотабличные запросы. Информация выбирается из двух или более таблиц, имеющих общее поле, т. е. в каждой из таблиц имеется столбец, содержащий такие же значения (не обязательно все), как и в другой таблице. На рис. 2.2 показан пример многотабличного запроса, а ниже указана соответ- ствующая инструкция. Таблица1 Таблица2 Результат объединения Фио Заказ Фио Оплата Фио Заказ Оплата Иванов 1000 Иванов 500  Иванов 1000 500 Петров 2000 Сидоров 3000 Сидоров 3000 3000 Сидоров 3000 Чен 300 Рис. 2.2. Внутреннее объединение таблиц SELECT Таблица1.Фио, Заказ, Оплата FROM Таблица1 INNER JOIN Таблица2 ON Таблица1.Фио= Таблица2.Фио Русский перевод ключевых слов инструкции: ОТОБРАТЬ Таблица1.Фио, Заказ, Оплата ИЗ Таблица1 ПРОВЕДЯ_ВНУТРЕННЕЕ ОБЪЕДИНЕНИЕ_С Таблица2 ПО Таблица1.Фио= Таблица2.Фио При внутреннем объединении остаются только строки со зна- чениями, совпадающими в обеих таблицах. При этом часть дан- ных «теряется» (например, строки «Чен» и «Петров»). Если по 15 логике запроса этого необходимо избежать, то можно восполь- зоваться «полным», «левым» или «правым» внешним объедине- нием. Инструкция INNER JOIN заменяется соответственно на FULL OUTER JOIN (полное), LEFT OUTER JOIN (левое) или RIGHT OUTER JOIN (правое объединение). В случае полного внешнего объединения (рис. 2.3) в результирующей таблице со- храняются все строки, а отсутствующие данные заполняются значениями NULL (неопределенное значение). В случае левого внешнего объединения (рис. 2.4) сохраняются значения только первой таблицы в запросе (находится слева от слова JOIN), а не- совпадающие строки второй таблицы удаляются. В случае пра- вого внешнего объединения сохраняются значения только вто- рой таблицы в запросе (находится справа от слова JOIN). Таблица1 Таблица2 Результат объединения Фио Заказ Фио Оплата Фио Заказ Оплата Иванов 1000 Иванов 500  Иванов 1000 500 Петров 2000 Сидоров 3000 Сидоров 3000 3000 Сидоров 3000 Чен 300 Петров 2000 NULL SELECT Таблица1.Фио, Заказ, Оплата FROM Таблица1 FULL OUTER JOIN Таблица2 ON Таблица1.Фио= Таблица2. Фио Чен NULL 300 Рис. 2.3. Полное внешнее объединение таблиц Таблица1 Таблица2 Результат объединения Фио Заказ Фио Оплата Фио Заказ Оплата Иванов 1000 Иванов 500  Иванов 1000 500 Петров 2000 Сидоров 3000 Сидоров 3000 3000 Сидоров 3000 Чен 300 Петров 2000 NULL SELECT Таблица1.Фио, Заказ, Оплата FROM Таблица1 LEFT OUTER JOIN Таблица2 ON Таблица1.Фио = Таблица2. Фио Рис. 2.4. Левое внешнее объединение таблиц 16 Объединение результатов запросов. Несколько однотип- ных запросов могут быть объединены в общий список при помощи инструкции UNION. Объединяемые столбцы должны иметь одинаковый тип данных (но не обязательно логически однородное содержимое). Инструкция DISTINCT для удале- ния повторяющихся строк должна присутствовать только в последнем из объединяемых запросов. SELECT Фио, Заказ, Оплата FROM Таблица1 WHERE Заказ>0 UNION SELECT DISTINCT Фио, Оплата FROM Таблица2 WHERE Оплата>300 Русский перевод ключевых слов инструкции: ОТОБРАТЬ Фио, Заказ ИЗ Таблица1 ГДЕ Заказ>0 ОБЪЕДИНИВ_РЕЗУЛЬТАТЫ_С ОТОБРАТЬ ОТЛИЧАЮЩИЕСЯ_ЗНАЧЕНИЯ Фио, Оплата ИЗ Таблица2 ГДЕ Оплата>300 Итоговые запросы (запрос с группировкой). Позволяют сгруппировать данные по какому-либо столбцу и рассчитать итоговые значения по каждой группе. Ниже приведен пример инструкции, в котором данные сначала группируются по столбцу отдела, а в рамках отдела – по должностям. Затем по каждой группе считается число сотрудников (фамилий), сред- няя зарплата и т. п. Условие WHERE накладывается на данные ДО группировки, а условие HAVING – ПОСЛЕ группировки. Если не выполнить группировку (не указать GROUP BY), то среднее значение, минимум, максимум и прочие функции бу- дут рассчитаны в целом по столбцу. SELECT Отдел, Должность, COUNT(Фио), AVG(Зарплата), MAX(Зарплата), MIN(Зарплата), SUM(Продажи), COUNT (DISTINCT Фи- лиалы) 17 FROM Таблица1 WHERE столбецГород='Минск' GROUP BY Отдел, Должность HAVING COUNT (Фио) > 2 ORDER BY Отдел DESC Русский перевод ключевых слов инструкции: ОТОБРАТЬ Отдел, Должность, ЧИСЛО_СТРОК(Фио), СРЕДНЯЯ (Зарплата), МАКСИМУМ (Зарплата), МИНИМУМ(Зарплата), СУММА (Продажи), ЧИСЛО_СТРОК (ОТЛИЧАЮЩИЕСЯ_ЗНАЧЕНИЯ Филиалы) ИЗ Таблица1 ГДЕ столбецГород='Минск' СГРУППИРОВАТЬ ПО Отдел, Должность ИМЕЮЩИЕ_ПОСЛЕ_ГРУППИРОВКИ ЧИСЛО_СТРОК(Фио) > 2 УПОРЯДОЧИТЬ ПО Отдел ПО_УБЫВАНИЮ Подчиненные запросы (вложенные запросы). Позволяют создать несколько уровней вложенности запросов. В обычных запросах условия отбора строк WHERE и HAVING выглядят примерно так: WHERE столбец = значение. В подчиненных запросах эта схема модифицируется сле- дующим образом: WHERE столбец = (результаты еще одного вложенного запроса). Таким образом, в условиях вместо констант используются результаты других запросов, причем число уровней вложенно- сти запросов может быть достаточно велико. По этой причине в таких запросах во избежание путаницы с одинаковыми назва- ниями столбцов целесообразно использовать полные имена столбцов в формате «ИмяТаблицы.ИмяСтолбца». Ниже приве- ден пример инструкции, отбирающей из списка сотрудников из отдела маркетинга фамилию работника, зарплата которого больше, чем у любого из работников филиала предприятия. 18 SELECT Фио FROM таблицаСотрудники WHERE Зарплата > ALL (SELECT таблицаФилиал.Зарплата FROM таблицаФилиал WHERE отдел = 'маркетинг' ) Русский перевод ключевых слов инструкции: ОТОБРАТЬ Фио ИЗ таблицаСотрудники ГДЕ Зарплата > ВСЕХ (ОТОБРАТЬ таблицаФилиал.Зарплата ИЗ таблицаФилиал ГДЕ отдел = 'маркетинг' ) Вместо ключевого слова ALL (все) в обработке результатов подчиненных запросов могут также использоваться ключевые слова ANY (кто-либо), IN (среди значений), NOT EXISTS (не существует), MAX (максимум), MIN (минимум) и другие стандартные функции. Задания для выполнения Задание 1. Создать запросы в конструкторе MS Access. Приведенные ниже задания выполняются в следующем по- рядке: запрос создается при помощи конструктора запросов MS Access на основании таблиц, созданных в лабораторной работе № 1. Затем отдельно (в блокноте) студентом записыва- ется SQL-инструкция, соответствующая данному запросу. По- сле чего конструктор MS Access переводится в режим про- смотра SQL-инструкции (команда меню «Вид/Режим SQL») и полученная SQL-инструкция сопоставляется с инструкцией, написанной студентом. Задания для выполнения: • создать запрос «Наличие изделий». В запрос включить столбцы «Изделие» и «Наличие» из таблицы «Склад»; • создать запрос «Заказчики F14». В запрос включить за- казчиков «F14», отсортировав их по убыванию; 19 • создать запрос «Мелкие заказчики», содержащий столбцы «Заказчик» и «Количество» из таблицы «Заказ» и включающий заказчиков, у которых количество заказанных изделий нахо- дится в пределах от 1 до 10 или заказанное изделие начинается на «Палочки». Заказчики в таблице не должны повторяться; • создать запрос «Форма заказа», содержащий столбцы «№ заказа», «Заказчик», «Изделие», «Количество» из таблицы «Заказ», столбец «Цена» из таблицы «Склад» и вычисляемый столбец «Сумма заказа» (=Цена*Количество). Отсортировать результаты запроса по полю «№ заказа» по возрастанию; • создать запрос «Закупить», включающий изделия, для которых в соответствии с имеющимися заказами необходимо произвести дополнительные закупки. В запрос включить столбец «Изделие» и «Закупить» (в столбце рассчитывается необходимый объем закупок). Задание 2. Создать запросы при помощи инструкций SQL. При выполнении данного задания инструкции необходимо непосредственно вводить в режиме SQL конструктора запро- сов (меню «Вид/Режим SQL»): • написать инструкцию SQL и создать запрос «Заказ- оплата», включающий в себя столбцы «№ заказа», «Заказчик», «Сумма заказа» из запроса «Форма заказа», и столбец «Опла- та» из таблицы «Оплата». В результирующем запросе не должны быть потеряны заказчики, от которых не поступила оплата (использовать левое внешнее объединение); • написать инструкцию SQL и создать запрос «Контакты», представляющий общий список заказчиков (таблица «Заказ») и поставщиков (таблица «Склад»), имевших дело с данным предприятием. Заказчики и поставщики должны быть пере- числены в один столбец и не должны повторяться; • написать инструкцию SQL и создать запрос «Максималь- ная партия», подсчитывающий максимальную партию (столбец 20 «Количество» таблицы «Заказы») по заказам, у которых назва- ние изделия не начинается со слова «Палочки»; • написать инструкцию SQL и создать запрос «Средняя партия по крупным заказам». В запрос включить только изде- лия, для которых средняя партия превышает 1 и название из- делия не начинается со слова «Палочки»; • написать инструкцию SQL и создать запрос «Изделия без спроса», перечисляющий все изделия из таблицы «Склад», на которые отсутствуют заказы в таблице «Заказ». Изделия не должны повторяться; • написать инструкцию SQL и создать запрос «Партия больше средней», включающий столбцы «Изделие» и «Коли- чество». В результаты запроса должны попасть только те из- делия, по которым партия (столбец «Количество») превышает средний размер партии по всем изделиям (без учета изделий, начинающихся на «Палочки», при определении среднего зна- чения), и название самого изделия не начинается со слова «Палочки». 21 Лабораторная работа № 3 ИСПОЛЬЗОВАНИЕ SQL В MS ACCESS. СОЗДАНИЕ ТАБЛИЦ И ИЗМЕНЕНИЕ ДАННЫХ ПРИ ПОМОЩИ SQL Цели работы: • получение навыков использования языка SQL в СУБД MS Access; • изучение возможностей использования языка SQL для создания таблиц и изменения данных. Теоретический материал Язык SQL позволяет не только строить запросы к базе дан- ных, но и изменять хранящуюся в ней информацию и даже структуру базы данных. Рассмотрим примеры соответствую- щих инструкций SQL. Однострочная инструкция добавления данных INSERT INTO Таблица1 (столбец1, столбец3) VALUES ('Иванов', 300000) Русский перевод ключевых слов инструкции: ВСТАВИТЬ В Таблица1 (столбец1, столбец3) ЗНАЧЕНИЯ ('Иванов', 300000) Если при вставке строки данные указаны для всех столбцов по порядку, то инструкцию можно записать в сокращенном варианте: INSERT INTO Таблица1 ('Иванов', NULL, 300000) Многострочная инструкция добавления данных вставля- ет в таблицу строки, полученные в результате запроса. INSERT INTO Таблица1 (столбец1, столбец3) SELECT Фио, Стипендия FROM Таблица2 WHERE курс>2 22 Русский перевод ключевых слов инструкции: ВСТАВИТЬ В Таблица1 (столбец1, столбец3) ОТОБРАТЬ Фио, Стипендия ИЗ Таблица2 ГДЕ курс>2 Удаление строк DELETE FROM Таблица1 WHERE Фио='Иванов' Русский перевод ключевых слов инструкции: УДАЛИТЬ_СТРОКИ ИЗ Таблица1 ГДЕ Фио='Иванов' Если не указать критерий отбора WHERE, то из таблицы будут удалены все строки. Инструкция удаляет строки, но не удаляет саму таблицу. Обновление данных UPDATE Таблица1 SET столбец1='Петров', столбец3=столбец3+100000 WHERE столбец3<200000 Русский перевод ключевых слов инструкции: ОБНОВИТЬ Таблица1 УСТАНОВИТЬ столбец1='Петров', столбец3=столбец3+100000 ГДЕ столбец3<200000 Создание таблиц осуществляется инструкцией CREATE TABLE. Необходимо помнить, что связь создается не между любыми столбцами, а только между первичным ключом табли- цы и внешним ключом. Ниже приведен пример инструкции, создающей таблицу1 со связями с другими таблицами, приве- денными на рис. 3.1. 23 Таблица2 Таблица1 Фио Стобец1 … Столбец2 Столбец3 Таблица3 Столбец4 Город Телефон … Рис. 3.1. Схема данных создаваемой таблицы CREATE TABLE Таблица1 ( Столбец1 INTEGER, Столбец2 VARCHAR(30), Столбец3 VARCHAR(30), Столбец4 VARCHAR(20), CONSTRAINT условие1 PRIMARY KEY (Столбец1), CONSTRAINT условие2 FOREIGN KEY (Столбец2) REFERENCES Таблица2 ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT условие3 FOREIGN KEY (Столбец3, Столбец4) REFERENCES Таблица3 ON DELETE SET DEFAULT ON UPDATE SET NULL, CONSTRAINT условие4 CHECK (Столбец3 IN 'Минск', 'Пинск') Русский перевод ключевых слов инструкции: СОЗДАТЬ ТАБЛИЦУ Таблица1 ( Столбец1 ЦЕЛОЕ, Столбец2 СТРОКА_СИМВОЛОВ(30) Столбец3 СТРОКА_СИМВОЛОВ(30), Столбец4 СТРО- КА_СИМВОЛОВ(20), ОГРАНИЧЕНИЕ условие1 ПЕРВИЧНЫЙ КЛЮЧ (Столбец1), 24 ОГРАНИЧЕНИЕ условие2 ВНЕШНИЙ КЛЮЧ (Столбец2) ССЫЛАЕТСЯ_НА Таблица2 ПРИ УДАЛЕНИИ КАСКАДНОЕ_УДАЛЕНИЕ ПРИ ОБНОВЛЕНИИ ОБЕСПЕЧИТЬ_ЦЕЛОСТНОСТЬ, ОГРАНИЧЕНИЕ условие3 ВНЕШНИЙ КЛЮЧ (Столбец3, Столбец4) ССЫЛАЕТСЯ_НА Таблица3 ПРИ УДАЛЕНИИ УСТАНОВИТЬ ЗНАЧЕНИЕ_ПО_- УМОЛЧАНИЮ ПРИ ОБНОВЛЕНИИ УСТАНОВИТЬ НЕОПРЕДЕЛЕН- НОЕ_ЗНАЧЕНИЕ, ОГРАНИЧЕНИЕ условие4 ПРОВЕРЯТЬ_ПРИ_ВВОДЕ_ДАННЫХ (Столбец3 СРЕДИ_ЗНАЧЕНИЙ 'Минск', 'Пинск') ) Удаление таблиц DROP TABLE Таблица1 CASCADE или RESTRICT Русский перевод ключевых слов инструкции: УДАЛИТЬ ТАБЛИЦУ Таблица1 С_КАСКАДНЫМ_УДАЛЕНИЕМ_СВЯЗАННЫХ_ЗАПИСЕ Й_В_ДРУГИХ_ТАБЛИЦАХ или С_ЗАПРЕТОМ_УДАЛЕНИЯ_ПРИ_НАЛИЧАЕ_СВЯЗАНН ЫХ_ЗАПИСЕЙ Добавление столбцов таблиц ALTER TABLE Таблица1 ADD COLUMN Столбец5 INTEGER Русский перевод ключевых слов инструкции: ИЗМЕНИТЬ ТАБЛИЦУ Таблица1 ДОБАВИТЬ СТОЛБЕЦ Столбец5 ЦЕЛОЕ_ЧИСЛО 25 Удаление столбцов таблиц ALTER TABLE Таблица1 DROP Столбец5 CASCADE или RESTRICT Русский перевод ключевых слов инструкции: ИЗМЕНИТЬ ТАБЛИЦУ Таблица1 УДАЛИТЬ Столбец5 С_КАСКАДНЫМ_УДАЛЕНИЕМ_СВЯЗАННЫХ_ЗАПИС ЕЙ_В_ДРУГИХ_ТАБЛИЦАХ или С_ЗАПРЕТОМ_УДАЛЕНИЯ_ПРИ_НАЛИЧАЕ_СВЯЗАНН ЫХ_ЗАПИСЕЙ Задания для выполнения Создать запросы при помощи инструкций SQL на основа- нии таблиц и запросов, созданных в лабораторных работах № 1 и № 2. При выполнении данного задания инструкции необходимо непосредственно вводить в режиме SQL кон- структора запросов (меню «Вид/Режим SQL»): • написать инструкцию SQL и создать запрос, вносящий в таблицу «Склад» информацию о закупке десяти дополнитель- ных палочек для воды; • написать инструкцию SQL и создать запрос, добавляю- щий в таблицу «Склад» информацию о поступлении нового изделия «M21» в количестве 25 штук по цене 500 (USD), по- ставщик не известен; • написать инструкцию SQL и создать запрос, удаляющий из таблицы «Склад» запись об изделии «M21», сделанную в предыдущем задании; • написать инструкцию SQL и создать запрос, создающий таблицу «Резерв», состоящую из столбцов «Изделие» (строка символов переменной длины до 50 символов, является первич- 26 ным ключом таблицы), «Наличие» (целое число), «Примеча- ние» (строка символов переменной длины до 100 символов); • написать инструкцию SQL и создать запрос, вставляющий в созданную таблицу «Резерв» данные из таблицы «Склад» по всем изделиям, начинающимся с «F»; • написать инструкцию SQL и создать запрос, создающий таблицы «Склад1» и «Заказ1», по структуре аналогичные табли- цам «Склад» и «Заказ». Столбец «Изделие» из таблицы «Заказ1» является внешним ключом для таблицы «Склад1». Если на изде- лие существуют заказы в таблице «Заказ1», то попытки удалить его из таблицы «Склад1» блокируются. Лабораторная работа № 4 ОСНОВНЫЕ ПРИЕМЫ РАБОТЫ С MS ACCESS. СОЗДАНИЕ ФОРМ, ОТЧЕТОВ И МАКРОСОВ Цели работы: • получение навыков создания форм в СУБД MS Access; • получение навыков создания отчетов в СУБД MS Access; • получение навыков использования макросов в СУБД MS Access Теоретический материал Наиболее гибким инструментом является создание форм в режиме конструктора, однако создание форм может осу- ществляться и при помощи мастера. Мастер форм позволяет автоматически создать форму по указанной таблице, на осно- вании которой строится форма. Возможны следующие вари- анты форм: в один столбец (одновременно на экране отобража- ется только одна запись, слева – название поля, справа – дан- ные), ленточная (отображается несколько записей в виде таблицы, сверху названия столбцов), табличная (аналогична ли- 27 сту Excel), выровненная. При помощи команды меню «Встав- ка/Форма» или кнопки «Создать» в окне базы данных (рис. 4.1) возможно также быстрое создание формы по всем полям таб- лицы с использованием автоформ. Рис. 4.1. Режимы создания новой формы При создании формы в режиме конструктора в форме вы- деляется несколько областей (рис. 4.2 и рис. 4.3): • заголовок/примечание (меню «Вид/Заголовок», «Вид/ Примечание формы»); • колонтитулы (меню «Вид/Колонтитулы»); • область данных. Рис. 4.2. Ленточная форма в режиме просмотра 28 Рис. 4.3. Ленточная форма в режиме конструктора В случае создания ленточной формы поля, размещенные в области данных, выводятся на экран столько раз, сколько при- сутствует записей в форме. Поля, размещенные в заголовке и примечании, выводятся на экран однократно. Таким образом, если необходимо создать поле с подсчетом среднего значения по каждой строке формы, его необходимо размещать в обла- сти данных, а если необходимо создать поле с подсчетом среднего значения по всему столбцу – в области примечания. В заголовке чаще всего создаются надписи, формирующие шапку с названиями столбцов. Поля и надписи в форме создаются с помощью панели эле- ментов. Кнопка «Надпись» вставляет обычный текст, кнопка «Поле» – поле данных или вычислений, кнопка «Кнопка» создает кнопки формы. Если при создании но- вой кнопки включен режим мастера , то пользователю предлагается выбрать действия и внешний вид кнопки из стандартного списка. Если режим мастера не включен (отжат переключатель ), то создается пустая кнопка, не произво- дящая никаких действий. Впоследствии для кнопки правым щелчком мыши можно вызвать контекстное меню «Свой- ства/вкладка События» и в полях «Нажатие кнопки», «Двой- ное нажатие кнопки» и т. п. указать макрос или программу VBA, которые будут выполняться при нажатии на кнопку. Аналогично обработку событий можно описать и для любых 29 других элементов формы. Так, через контекстное меню «Свойства/ вкладка События/двойное нажатие кнопки» можно задать срабатывание программы при двойном щелчке по полю данных или изображению (рисунки вставляются с использо- ванием кнопки на панели элементов). Задание или смена источника данных формы осуществля- ется также в меню «Свойства» (выделить всю форму целиком и нажать F4 либо вызвать свойства любого элемента формы и в верхней части диалога свойств в выпадающем списке вы- брать «Форма») на вкладке «Данные/Источник записей». Для облегчения создания полей может быть вызван построитель выражений: выделить поле, контекстное меню «Свой- ства/Данные/Данные», нажать кнопку рядом с полем «Данные». Построитель позволяет двойным щелчком зано- сить в поле название других полей формы, названия таблиц и т. п. Также построитель позволяет создавать вычисляемые по- ля с использованием встроенных функций Access (рис. 4.4). Пример создания вычисляемого поля приведен в табл. 4.1. Рис. 4.4. Построитель выражений 30 Таблица 4.1 Создание вычисляемых полей Вычисляемое поле Описание =Sum([Количество]*[Цена]) Сумма произведений столбцов «Количество» и «Цена» по всем строкам формы =Avg([СтоимостьДоставки]) Среднее значение столбца «Сто- имостьДоставки» =Count([КодЗаказа]) Количество записей в столбце «КодЗаказа» =DSum("[Столбец1]", "[Табли- ца1]", "[Столбец3] = 'Минск' ") Использует функцию по под- множеству. Суммируются толь- ко те значения столбца «Стол- бец1» из таблицы «Таблица1», для которых в той же строке в столбце «Столбец3» содержится значение «Минск» Внешний вид интерфейса формы (например, отображение кнопок оконного меню) настраивается на вкладке «Макет» свойств формы. Пользовательское контекстное меню в форме создается следующим образом: • выбрать меню «Сервис/Настройка/вкладка Панели инстру- ментов/кнопка Создать/указать имя панели меню/кнопка ОK»; • с вкладки «Команды» перетащить на созданную панель необходимые кнопки; • перейти на вкладку «Панели инструментов», выбрать со- зданную панель, нажать кнопку «Свойства», изменить тип на «Контекстное меню», нажать «ОK/Закрыть»; • открыть форму в режиме конструктора, выделить всю форму, нажать F4 (свойства формы), на вкладке «Другие» в пункте «Контекстное меню» указать использование в форме созданного контекстного меню. 31 Для автоматического запуска формы при открытии базы данных используется меню «Сервис/Параметры запуска». Создание отчетов Единственное отличие отчета от формы заключается в том, что отчет предполагает выведение информации на бумагу. В остальном создание отчетов аналогично созданию форм. В режиме конструктора выделяются те же области: заголовок отчета, верхний колонтитул, область данных, нижний колон- титул, примечание отчета (рис. 4.5). Рис. 4.5. Отчет в режиме конструктора Колонтитулы отображаются на каждой странице отчета. Примечание и заголовок – в начале и конце отчета. Аналогич- ным образом создаются вычисляемые поля и надписи. Ис- пользуя меню «Вид/Сортировка и группировка», можно созда- вать отчеты с группировкой, например, объем продаж товара, сгруппированный по регионам продаж и т. п. Также возмож- но создание отчетов и диаграмм с использованием мастера отчетов. 32 Создание макросов Макросы позволяют выполнить линейную последователь- ность стандартных команд Access. Задается сама команда и её параметры. На рис. 4.6 приведен пример выполнения ин- струкции SQL при помощи макроса. Рис. 4.6. Выполнение инструкции SQL при помощи макроса Задания для выполнения Задания выполняются на основании таблиц и запросов, со- зданных в лабораторных работах № 1–3. Задание 1. Создать на основании таблицы «Склад» ленточ- ную форму и отчет. Предусмотреть в примечании формы/- отчета подсчет средней цены по изделиям, находящимся на складе. Задание 2. Создать ленточную форму «Форма заказа» на основании созданного ранее запроса «Форма заказа». В приме- чании формы предусмотреть поле «Суммарный объем продаж F14», в котором будет рассчитана общая сумма заказа по всем закупкам «F14» (использовать функции по подмножеству). Задание 3. В форме «Форма заказа» создать: кнопку закры- тия формы; кнопку просмотра отчета, аналогичного форме (предварительно необходимо создать соответствующий от- 33 чет); кнопку печати отчета, аналогичного форме; кнопку за- крытия Access. При помощи меню «Вид/Программа» ознако- миться с текстом VBA-программ, соответствующих кнопкам. Задание 4. Создать собственное контекстное меню формы «Форма заказа». Отключить отображение стандартных кнопок оконного меню (крестик на закрытие и т. д) в форме. Задание 5. Установить параметры запуска базы данных так, чтобы при ее загрузке стандартное окно базы данных не загружалось, а вместо этого отображалась любая из созданных ранее форм. Проверить действие команды, перезагрузив базу данных. Предотвратить использование параметров запуска, удерживая клавишу при загрузке базы данных, и от- менить эти параметры. Задание 6. Создать отчет по запросу «Заказ-Оплата», сгруп- пировав данные по заказчикам (меню «Вид/Сортировка и группировка») и поместив в заголовке группы надпись «За- казчик», а в примечании группы – среднюю сумму заказа по каждому заказчику. Задание 7. Создать макрос, состоящий из следующих дей- ствий: 1) выполнение инструкции SQL вида: CREATE TABLE Кредит (ФИО VARCHAR(30) NOT NULL, Лимит NUMERIC, constraint огр1 primary key (ФИО), constraint огр2 UNIQUE (ФИО)); 2) вывод созданной таблицы «Кредит» на экран. Перейти на вкладку «Макросы», выделить созданный мак- рос и, воспользовавшись меню «Сервис/Макрос/Преобразо- вать макросы», преобразовать созданный макрос в модуль программы на VBA. Ознакомиться с содержимым полученно- го модуля на вкладке «Модули». 34 Воспользовавшись командой меню «Сервис/Настройка/ вкладка Команды/ Категория: Все макросы», создать в меню и на панели управления кнопку, выполняющую данный макрос. В любой форме создать кнопку, выполняющую созданный макрос. Проверить работоспособность кнопки и проверить, что отображается в свойствах кнопки в разделе «События/ Нажатие кнопки». Воспользовавшись меню «Вид/Программа», перейти к просмотру VBA-программ формы и убедиться, что там отсутствует программа, соответствующая созданному макросу. В режиме конструктора формы выполнить команду «Сервис/Макрос/Преобразовать макросы формы», затем зано- во перейти к просмотру VBA-программ формы и найти там преобразованную VBA-программу, соответствующую макросу. Лабораторная работа № 5 СИСТЕМА БЕЗОПАСНОСТИ MS ACCESS Цели работы: • изучение системы безопасности и разграничения доступа в MS Access; • получение навыков создания защищенной базы данных. Теоретический материал Access обеспечивает два способа защиты базы данных: установка пароля и защита на уровне пользователей. Кроме того, можно предотвратить изменения структуры форм, отче- тов и модулей, сохранив базу данных как файл MDE (меню «Сервис/Служебные программы/Создать MDE-файл»). Для установки пароля откройте файл базы данных моно- польно (в однопользовательском режиме), выбрав в правом нижнем углу диалогового окна вместо кнопки «Открыть» вариант «Монопольно». Выполните команду 35 меню «Сервис/Защита/Задать пароль базы данных». Введите и подтвердите пароль (с учетом регистра). Защита на уровне пользователей позволяет установить раз- ные права доступа на различные объекты базы данных для раз- личных пользователей. При запуске Access пользователь ука- зывает свое имя и пароль. Если данный пользователь есть в списке и пароль введен правильно, то пользователь подключа- ется к Access под своим именем и как участник своей группы. Группы пользователей: Admins (администраторы), Users (поль- зователи) или любые другие определяемые вручную группы. Каждая группа имеет свой определяемый набор прав. Напри- мер, члены группы Users могут иметь разрешения на просмотр, ввод или изменение данных в таблицы, но им не будет разре- шено изменять структуру таблиц. Группа Users может быть до- пущена только к просмотру данных в таблице «Заказы» и не иметь доступа к таблице «Платежная ведомость». Члены груп- пы Admins имеют все разрешения на доступ ко всем объектам базы данных. Кроме того, каждый пользователь может иметь свой индивидуальный набор прав. Права пользователя сумми- руются с правами группы, в которую он входит. Владелец базы данных или отдельного объекта базы дан- ных – это тот пользователь, который был подключен к Access при создании базы данных или отдельного объекта. Владелец базы данных всегда может открыть базу данных и получить все разрешения на объекты для которых он является владельцем. Создание нового пользователя: «Сервис/Защита/Пользова- тели и группы/вкладка Пользователи/кнопка Создать/ввести имя нового пользователя и его код». Код пользователя – это не пароль. Фактически, к базе данных имеет доступ не имя поль- зователя, а комбинация «имя+уникальный код». Для повтор- ного создания идентичного пользователя необходимо указать не только правильное имя, но и правильный код. Используя кнопки «Добавить», «Удалить», можно переместить в окно «Участие в группе» те группы, в которых будет участвовать 36 пользователь. Группа Admins дает пользователю права адми- нистратора, Users – права рядового пользователя. Кнопка «Удалить» удаляет пользователя. Создание новой группы: «Сервис/Защита/Пользователи и группы/вкладка «Группы»/кнопка «Создать»/ввести название группы и ее уникальный код». Изменение пароля пользователя: «Сервис/Защита/Поль- зователи и группы/вкладка «Изменение пароля»/указать старый пароль, новый пароль и подтверждение пароля». Можно изме- нить только пароль того пользователя, от имени которого было произведено подключение к Access. Для того, чтобы изменить пароль любого пользователя, необходимо выйти из Access и снова подключиться к нему от имени этого пользователя. Поль- зователь, обладающий правами администратора, может отме- нить пароль любого пользователя, отображаемого на вкладке в окне «Пользователь имя:», нажав кнопку «Снять пароль». Подключение к Access от имени определенного пользо- вателя: можно подключиться из командной строки (Access.exe/ /User <имя >/Pwd <пароль>). Если в командной строке пароль не указан, то Access выведет диалоговое окно, где попросит его указать. Можно также воспользоваться следующей техни- кой: Access по умолчанию пытается подключить всех пользо- вателей как Admin. Достаточно установить пароль для поль- зователя Admin и при стандартном запуске Access всегда бу- дет выводить диалоговое окно с запросом на ввод имени пользователя и его пароля. Чтобы отменить вывод этого за- проса, администратору достаточно снять пароль пользователю Admin кнопкой «Снять пароль». Установка защиты на уровне пользователей: создать список групп и имен пользователей. Запустить Access без от- крытия базы данных как пользователь Admin или любой другой с правами администратора. Открыть защищаемую базу данных. В меню «Сервис/Разрешения/вкладка Разрешения/поле Тип объ- екта/выбрать База данных/выбрать имя пользователя или 37 название группы для которой устанавливаются разрешения», флажками отметить, что разрешено данному пользователю или группе (разрешения отдельных пользователей и разрешения групп, в которых они участвуют, суммируются). На вкладке «Разрешения» в поле «Тип объекта» выбрать остальные объек- ты базы данных «Таблицы», «Запросы», «Формы», «Отчеты», «Макросы», «Модули» и установить разрешения для них. При этом можно определять набор разрешений как для уже суще- ствующих объектов, так и для вновь создаваемых. Удаление защиты на уровне пользователей: подключиться к Access как администратор. Загрузить защищенную базу дан- ных. Предоставить группе Users разрешения на все объекты ба- зы данных. Возвратить права владельца базы данных и ее объ- ектов стандартной учетной записи пользователя Admin. Выйти из Access и снова подключиться к системе под именем Admin, создать пустую базу данных, импортировать все объекты из защищенной базы данных в новую. Снять пароль пользовате- лю Admin. Смена владельца отдельных объектов базы данных: ме- ню «Сервис/Защита/Пользователи и группы/вкладка Смена владельца». Выбрать тип объектов в списке (таблица, запрос, форма, отчет и т. д.). Выделить один или несколько объектов, для которых требуется произвести смену владельца. В списке «Новый владелец» выбрать имя пользователя или группы, ко- торым передаются права владельца объекта. Нажать кнопку «Сменить владельца». Смена владельца всей базы данных: подключиться к Access от имени пользователя, который станет новым владельцем базы данных. Создать новую пустую базу данных. Импортировать в нее все объекты из исходной базы данных. Для импорта пользо- ватель должен иметь разрешение «Открытие/запуск» для базы данных и «Чтение макета/Чтение данных» на ее объекты. Создание нового файла рабочей группы: в файле рабочей группы содержится перечень имен пользователей и групп. Ме- 38 ню «Сервис/Защита/Администратор рабочих групп». В диало- говом окне нажать кнопку «Создать» и ввести свое имя и назва- ние организации. В поле «Код рабочей группы» ввести до 20 цифр/букв и нажать кнопку «OK». Ввести имя нового файла ра- бочей группы, указать, куда сохранить и нажать кнопку «OK». Присоединение к существующему файлу рабочей группы: меню «Сервис/Защита/Администратор рабочих групп». В диа- логовом окне нажать кнопку «Связать». Ввести путь и имя файла рабочей группы или нажать кнопку «Обзор». При сле- дующем запуске Microsoft Access будет использован новый файл рабочей группы. Можно также указать файл рабочей группы при запуске Access с параметром командной строки Access.exe /wrkgrp <путь и имя файла рабочей группы>. Устранение слабых мест защиты на уровне пользова- телей: проблему представляют стандартный пользователь Admin и стандартный код групп Admins/Users при использо- вании стандартного файла рабочей группы. Учетные записи Admin одинаковы для всех экземпляров Microsoft Access. По этой причине Admin не должен быть вла- дельцем защищенной базы данных или ее объектов. Пользо- ватель Admin не должен иметь права администратора или входить в группу администраторов. В противном случае мож- но легко создать файл рабочей группы заново (создав тем са- мым стандартного пользователя Admin без пароля) и получить полный доступ к защищенной базе данных. То же относится и к группе Admins, для обеспечения уни- кальности которой требуется создать файл рабочей группы с уникальным кодом (при установке Access «по умолчанию» это не сделано). Последовательность действий для создания защищенной ба- зы такова: создать файл рабочей группы с уникальным кодом; создать уникальную учетную запись администратора (не Admin); подключиться к Access от имени уникального администратора; создать пустую базу данных, проимпортировать в нее объекты 39 из старой базы данных. Удалить пользователя Admin из группы Admins. Отобрать у группы Users, в которой продолжает нахо- диться Admin, все права на доступ к базе данных. Шифрование и дешифрование базы данных: при шифро- вании базы данных ее файл сжимается и делается недоступным для чтения с помощью сторонних программ или текстовых ре- дакторов (в Access база открывается обычным образом). Это предотвращает извлечение информации из базы данных в обход механизмов безопасности Access. Дешифрование базы данных отменяет результаты операции шифрования. Порядок шифро- вания: запустить Access без открытия базы данных (невозможно зашифровать или дешифровать открытую базу данных); меню «Сервис/Защита/Шифровать или Дешифровать/указать имя ба- зы данных, указать имя зашифрованной базы данных». Задания для выполнения Использовать базу данных, созданную в лабораторных ра- ботах № 1–4. Задание 1. Установить пароль на базу данных, проверить его действие и снять пароль. Задание 2. Выполнить шифрование базы данных, присвоив зашифрованной базе новое имя. Не открывая зашифрованную базу данных, запустить текстовый редактор WordPad («кнопка Пуск/Выполнить/набрать «WordPad»/ОK»), загрузить в него сначала незашифрованную базу данных, просмотреть ее, а за- тем загрузить в редактор зашифрованную базу данных и срав- нить ее с незашифрованной. Дешифровать зашифрованную ба- зу данных. Задание 3. Установить защиту на уровне пользователей: • создать новую группу пользователей «Работники». Со- здать нового пользователя «Директор» с правами администра- тора (участвует в группах Admins, Users и «Работники»). Со- 40 здать нового пользователя «Иванов» с правами работника предприятия и пользователя (участвует в группах Users и «Ра- ботники»). Создать пользователя «Новичок» с правами работ- ника предприятия и пользователя. Создать пользователя «Гость» с правами пользователя (участвует только в группе Users). При создании пользователя «Гость» его индивидуаль- ный «Код» задать в виде «Гость1234»; • установить пароль «123456789» для пользователя Admin. Перезапустить Access. Войти в Access от имени пользователя «Директор» и установить ему пароль. Войти в Access от име- ни пользователей «Иванов» и «Новичок» и установить им па- роли, для «Гость» пароль не устанавливать; • войти в Access от имени «Директора». Открыть базу данных, созданную в предыдущих лабораторных. Запретить группе Users открывать базу данных. Разрешить группе «Ра- ботники» только просматривать существующие таблицы, за- просы, формы и отчеты, а пользователю «Иванов» – еще и из- менять введенные данные, но не изменять саму структуру базы данных как для уже существующих, так и для вновь создавае- мых таблиц, запросов, форм и отчетов. Разрешить пользовате- лю «Гость» открывать базу данных, но разрешить ему только просмотр формы «Форма заказа». Создать свою пустую базу данных с именем «Конфиденциально» и запретить пользова- телю «Гость» открывать эту базу данных (все остальные поль- зователи, входящие в группу «Работники», могут ее откры- вать). Для всех текущих или вновь создаваемых пользовате- лей, кроме «Директора», запретить устанавливать или изме- нять пароль на базу данных; • войти в Access последовательно как «Гость», «Новичок», «Иванов» и проверить действие ограничений на уже суще- ствующие таблицы. При подключении каждого из пользова- телей создавать произвольные новые таблицы, назвав их соот- ветственно «Гость», «Новичок», «Иванов». Обратить внима- ние на то, что пользователь, создавший новую таблицу (запрос, 41 форму, отчет), является ее владельцем, а значит обладает лю- быми правами на нее, не зависимо от переданных ему адми- нистратором разрешений. В то же время другие пользователи относительно этой таблицы обладают лишь теми правами, ко- торые им определил администратор для вновь создаваемых таблиц. Проверить это утверждение, попытавшись изменить структуру таблицы «Иванов», подключившись как «Нови- чок», а затем структуру таблицы «Гость», подключившись как «Гость». По вновь созданным таблицам передать права вла- дельца «Директору»; • войти в Access как «Директор», удалить пользователя «Гость» и снять пароль с пользователя «Новичок». Попытать- ся подключиться к Access как «Гость», подключиться как «Но- вичок» и заново задать для него пароль. Подключиться как «Ди- ректор» и заново создать пользователя «Гость» (входит только в группу Users), но с индивидуальным кодом «1234» (код не сов- падает с кодом прежнего пользователя «Гость»). Подключиться как «Гость» и попытаться открыть базу данных. Подключиться как «Директор», удалить и заново создать пользователя «Гость» с индивидуальным кодом «Гость1234» (код совпадает с кодом прежнего пользователя «Гость»). Подключиться как «Гость» и открыть базу. Объяснить причины неудачи и успеха. Задание 4. Изучить слабые места защиты на уровне поль- зователей: • запустить администратора рабочих групп, определить используемый сейчас стандартный файл рабочей группы, название организации и имя пользователя. В своем каталоге под именем «Взломщик» создать пустую копию файла рабо- чей группы, введя такое же «Имя пользователя» и «Название организации» («Код» не вводить). Связать этот файл с Access (кнопка «Связать»). Подключиться к Access с файлом рабочей группы «Взломщик» как пользователь Admin без пароля, от- крыть и изменить базу данных, заново связать Access со ста- рым файлом рабочей группы; 42 • устранить слабые места защиты. Создать новый файл ра- бочей группы с уникальным кодом и именем «Защищенный». Запустить Access с файлом рабочей группы «Защищенный», подключившись как пользователь Admin. Заново создать группы пользователей и имена пользователей, как это было сделано в предыдущем задании (пользователей «Новичок» и «Гость» можно не создавать). Создать пустую базу данных с именем «Временная» и импортировать в нее все таблицы, за- просы, формы и отчеты из старой базы данных. Удалить поль- зователя Admin из группы Admins и установить ему пароль. Запустить Access с файлом рабочей группы «Защищенный». Подключившись как «Директор», создать пустую базу данных с именем «Нет лазеек» и импортировать в нее все таблицы, запросы, формы и отчеты из базы данных «Временная» (Вни- мание! Очень важно, чтобы при создании базы данных «Нет лазеек» был активен пользователь «Директор», т. к. если бу- дет активен Admin, то он станет владельцем базы данных, а значит сможет открывать и изменять любые ее объекты, что сделает защиту бессмысленной). Заново определить все разре- шения на доступ к базе данных «Нет лазеек» для пользователей и групп пользователей так, как это было сделано в предыдущем задании (Внимание! Важно отобрать у группы Users все права на доступ к базе данных, т. к. в этой группе все еще продолжает находиться пользователь Admin, а удалить его из этой группы невозможно). Удалить старую базу данных и базу данных «Временная» без использования корзины; • подключиться к Access с файлом рабочей группы «Взлом- щик» как пользователь Admin без пароля и попытаться от- крыть базу данных «Нет лазеек». Подключиться к Access с файлом рабочей группы «Защищенный» как пользователь «Директор» и открыть базу данных «Нет лазеек». Объяснить причины неудачи и успеха. 43 ЛИТЕРАТУРА 1. Вейскас, Дж. Microsoft Office Access 2003 / Дж. Вейскас; пер. В. Ахмадуллина, А. Маркова, А. Раздобарина. – СПб.: Питер, 2005. – 1167 с. 2. Грофф, Дж. Энциклопедия SQL / Дж. Грофф, Р.П. Вайн- берг. – 3-е изд. – СПб.: Питер; Киев: BHV, 2004. – 895 с. 3. Официальный учебный курс Microsoft: Microsoft Office Access 2003 / пер. Л. Сазонова. – М.: ЭКОМ, БИНОМ. Лабо- ратория знаний, 2006. – 526 с. 44 СОДЕРЖАНИЕ Лабораторная работа № 1 ОСНОВНЫЕ ПРИЕМЫ РАБОТЫ С MS ACCESS. СОЗДАНИЕ ТАБЛИЦ И СХЕМЫ ДАННЫХ ................................................................... 3 Лабораторная работа № 2 ОСНОВНЫЕ ПРИЕМЫ РАБОТЫ С MS ACCESS. СОЗДАНИЕ ЗАПРОСОВ, ИСПОЛЬЗОВАНИЕ ЯЗЫКА SQL В ЗАПРОСАХ .................. 11 Лабораторная работа № 3 ИСПОЛЬЗОВАНИЕ SQL В MS ACCESS. СОЗДАНИЕ ТАБЛИЦ И ИЗМЕНЕНИЕ ДАННЫХ ПРИ ПОМОЩИ SQL .................. 21 Лабораторная работа № 4 ОСНОВНЫЕ ПРИЕМЫ РАБОТЫ С MS ACCESS. СОЗДАНИЕ ФОРМ, ОТЧЕТОВ И МАКРОСОВ .................... 26 Лабораторная работа № 5 СИСТЕМА БЕЗОПАСНОСТИ MS ACCESS ........................... 34 ЛИТЕРАТУРА ............................................................................ 43 45 Учебное издание СЕТЕВЫЕ ТЕХНОЛОГИИ И БАЗЫ ДАННЫХ Лабораторный практикум В 3 частях Ч а с т ь 1 СУБД MS ACCESS С о с т а в и т е л ь ЛЯХЕВИЧ Александр Генрихович Редактор Т.А. Подолякова Компьютерная верстка Д.К. Измайлович Подписано в печать 04.07.2011. Формат 60×841/16. Бумага офсетная. Отпечатано на ризографе. Гарнитура Таймс. Усл. печ. л. 2,62. Уч.-изд. л. 2,05. Тираж 100. Заказ 1179. Издатель и полиграфическое исполнение: Белорусский национальный технический университет. ЛИ № 02330/0494349 от 16.03.2009. Проспект Независимости, 65. 220013, Минск.