ЛЕКЦИЯ 3 ВВЕДЕНИЕ В БД ОСНОВЫ СУБД ACCESS

ЛЕКЦИЯ 3 ВВЕДЕНИЕ В БД. ОСНОВЫ СУБД ACCESS. 1 Составитель: доц. Космачева И. М.

ЯЗЫКОВЫЕ СРЕДСТВА БНД и БД • Обеспечивают интерфейс пользователей разных категорий с банком данных. • В настоящее время используются языковые средства - табличный язык запросов QBE, структурированный язык запросов SQL. • В языке QBE (Query-by-Example - язык запросов по образцу) используется визуальный подход для организации доступа к информации в базе данных, основанный на применении шаблонов запросов. 2

ЯЗЫК SQL Structured Query Language (SQL)— это непроцедурный язык, используемый для формулировки запросов к данным в большинстве современных СУБД и являющийся индустриальным стандартом (ANSI, ISO). Непроцедурность языка означает, что на нем можно указать, что нужно сделать с базой данных, но нельзя описать алгоритм этого процесса. Существует много диалектов. Основу языка SQL составляют операторы.

Запросы к БД Расписание Москва - Киев на вечернее время Выбрать Номер_рейса, Дни_недели, Время_вылета из таблицы Расписание, где Пункт_отправления= «Москва» и Пункт_назначения = «Киев» и время вылета>=17 Количество рейсов Москва - Минск Выбрать Количество (Номер_рейса) из таблицы Расписание, где Пункт_отправления= «Москва» и Пункт_назначения = «Минск» 4

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ (DATA DEFINITION LANGUAGE DDL): CREATE создает объект БД; ALTER изменяет объект; DROP удаляет объект;

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДОСТУПА К ДАННЫМ (DATA CONTROL LANGUAGE DCL): GRANT предоставляет пользователю (группе) разрешения на определенные операции с объектом; REVOKE отзывает ранее выданные разрешения;

ОПЕРАТОРЫ УПРАВЛЕНИЯ ТРАНЗАКЦИЯМИ (TRANSACTION CONTROL LANGUAGE, TCL) применяет транзакцию; ROLLBACK откатывает все изменения, сделанные в контексте текущей транзакции; SAVEPOINT делит транзакцию на более мелкие участки, применяется для отметки логических точек разрыва в границах транзакции; SET TRANSACTION начинает транзакцию и определяет ее поведение. COMMIT

ОПЕРАТОРЫ МАНИПУЛЯЦИИ ДАННЫХ (DATA MANIPULATION LANGUAGE DML): SELECT считывает данные, удовлетворяющие заданным условиям; INSERT добавляет новые данные; UPDATE изменяет существующие данные; DELETE удаляет данные;

УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ 9 Транзакция – это последовательность операций над БД, рассматриваемых СУБД как единое целое. Понятие транзакции необходимо для поддержания логической целостности БД (например, необходимость объединения элементарных операций над файлами). Поддержание механизма транзакций – необходимое условие как для однопользовательских СУБД, так и многопользовательских СУБД. То свойство, что каждая транзакция начинается при целостном состоянии БД и оставляет это состояние целостным после своего завершения. При соответствующем механизме управления транзакциями пользователь может почувствовать себя единственным пользователем СУБД.

ПОРЯДОК ВЫПОЛНЕНИЯ ОПЕРАТОРАSELECT 10

ЭТАПЫ ОБРАБОТКИ ЗАПРОСА 1. Синтаксический разбор (parsing). Проверяется синтаксис запроса, выполняется проверка всех объектов БД, на которые ссылается запрос ( существование всех столбцов, на которые ссылается запрос, и определяются их идентификаторы). 2. Компиляция запроса (query compilation). 3. Оптимизация запроса (query optimization). Оптимизатор запросов исследует несколько стратегий обращения к данным, выполняет анализ запроса, в процессе которого он отыскивает аргументы поиска и операции соединения. Затем оптимизатор решает, какие индексы использовать. Если запрос содержит операции соединения, оптимизатор выбирает порядок выполнения соединений и методы их обработки. 4. Выполнение запроса (query execution). Для некоторых инструкций можно избежать этапов синтаксического разбора и оптимизации, если компоненту Database Engine известно, что существует только один выполнимый план. Такая оптимизация называется тривиальной оптимизацией плана. В качестве примера запроса, для которого применима тривиальная оптимизация плана, можно назвать простую форму инструкции INSERT.

13

14

БД «КРЕДИТНАЯ ИСТОРИЯ 1. Сущности: Договор; Физическое лицо; Юридическое лицо; Погашение; Кредитная история; 15

БД «КРЕДИТНАЯ ИСТОРИЯ «Физическое лицо» : • • ИНН физического лица; Фамилия; Имя; Отчество; Номер, серия паспорта; Регистрация; Доход; 16

БД «КРЕДИТНАЯ ИСТОРИЯ «ДОГОВОР» : Номер; ИНН; Сумма; Дата заключения договора; • Срок кредита; • Процентная ставка; • Вид клиента; • • 17

БД «КРЕДИТНАЯ ИСТОРИЯ» : • • • ИНН; Сумма кредита; Дата погашения; Задолженность; Название банка; Вид клиента; 18

БД «КРЕДИТНАЯ ИСТОРИЯ «ПОГАШЕНИЕ» • Номер договора; • Дата погашения; • Сумма; 19

БД «КРЕДИТНАЯ ИСТОРИЯ Связи сущностей между сущностями «Договор» и «Погашение» : Слева направо: «Договор может иметь несколько погашений» ; Справа налево: «Каждое погашение должно принадлежать одному договору» ; Тип связи: один - ко многим. 20

СОЗДАНИЕ БД В ACCESS 21

ТЕХНОЛОГИИ ODBC И OLE DB 2 2 Технологии разрабатываются компанией Microsoft для изоляции клиента от особенностей реализации источника данных, предоставив ему стандартный интерфейс доступа. Разработчикам легче реализовать один стандартный интерфейс, чем поддерживать множество БД с различным API (Application Programming Interface) — это интерфейс создания приложений. ODBC - Open Database Connectivity ("Открытый интерфейс взаимодействия с базой данных"). Достоинство технологии - кросс-платформенность, что позволяет использовать ODBC драйвер на Unix. Обращение к БД происходит по имени зарегистрированного источника данных DSN (Data Source Name), перед использованием источник данных необходимо зарегистрировать на компьютере пользователя. Зарегистрировать новое подключение можно через "Администратор источника данных" (Control Panel>Administration->Источники данных ODBC).

ТЕХНОЛОГИИ ODBC И OLE DB 2 3 Технология OLE DB (или OLEDB) - Object Linking and Embedding, Database появилась позже ODBC. OLE DB провайдеры могут работать не только с реляционными БД , но и с любой другой информацией которую можно представить в табличном виде ( Jet OLE DB провайдер, позволяет извлекать данные из Excel и Outlook). Для подключения через OLE DB не требуется регистрация источника данных в системе, как это принято в ODBC. Вся информация хранится либо в файлах с расширением udl, либо указывается непосредственно в строке подключения. Для редактирования подключения OLE DB с расширением udl ассоциирован редактор связей с данными Data Link.

24

25

26

27

28

29

30

31

32

ТИПЫ ОБЪЕКТОВ ВACCESS Таблица – объект базы данных, в котором данные хранятся в виде записей (строк) и полей (столбцов). Запрос – объект базы данных, позволяющий осуществлять поиск и вывод данных, хранящихся в таблицах, удовлетворяющих заданным условиям (в том числе из нескольких таблиц). С помощью запроса можно модифицировать и удалять записи таблиц, а также выполнять различные вычисления. Форма – объект базы данных, являющийся элементом пользовательского интерфейса, предназначенный для просмотра, ввода и модификации данных в одной или более таблицах. Отчет – объект базы данных, предназначенный для анализа и вывода на печать данных, организованных и отформатированных в соответствии с требованиями пользователя. 33 Макрос – макрокоманда или набор макрокоманд, используемый для автоматизации задач.

СОЗДАНИЕ ТАБЛИЦ 34

СОЗДАНИЕ ТАБЛИЦ 35

ОГРАНИЧЕНИЯ ЦЕЛОСТНОСТИ База данных поддерживает следующие виды ограничений: 1) PRIMARY KEY 2) UNIQUE 3) FOREIGN KEY 4) CHECK, 5) NOT NULL 36

ТИПЫ ДАННЫХ 37

ВВОД ДАННЫХ Маски управляют элементом ввода. Будучи пустым, шаблон маски отображает символы-заполнители, на место которых должны попасть значения. Маски помогают понять смысл данных. Гораздо легче читать множество значений, представленных определенным образом. Маски устраняют путаницу. Одни и те же данные многих типов можно представить несколькими способами. Маски предупреждают ошибки. Они отбрасывают символы, не соответствующие шаблону. Если вы пользуетесь маской для ввода кода диагоза, то не сможете ввести буквы. 38

ЗАДАНИЕ СВЯЗЕЙ МЕЖДУ ТАБЛИЦАМИ 39

ОГРАНИЧЕНИЯ ЦЕЛОСТНОСТИ 40

ТИПЫ СВЯЗЕЙ Один к одному: каждой записи первой сущности соответствует только одна запись второй сущности, а каждой записи второй сущности – только одна запись первой сущности. Пример - пациент, у которого имеется 1 полис МС. Один ко многим: каждой записи первой сущности могут соответствовать несколько записей второй сущности, однако каждой записи второй сущности соответствует только одна запись первой сущности. Пример – один доктор, у которого наблюдается несколько пациентов. Многие ко многим: каждой записи первой сущности могут соответствовать несколько записей второй сущности, а каждой записи второй сущности соответствуют несколько записей первой сущности. Пример – разные пациенты наблюдаются у разных врачей. 41

ТИПЫ ДАННЫХ В СВЯЗЯХ И СОЕДИНЕНИЯХ 42

ТИПЫ ДАННЫХ В СВЯЗЯХ И СОЕДИНЕНИЯХ 43

ТИПЫ ДАННЫХ В СВЯЗЯХ И СОЕДИНЕНИЯХ Соединение — это операция, объединяющая данные из двух источников на основе значений указанного общего поля в источниках. Соединение может быть внутреннее соединение, левое внешнее соединение или правое внешнее соединение. При создании связи между таблицами или добавлении соединения в запрос связываемые поля должны иметь одинаковые или совместимые типы данных. Невозможно создать соединение между числовым и текстовым полями, даже если значения в них совпадают. ! Тип данных "Счетчик" совместим с полями числового типа, если для свойства Размер поля последних задано значение Длинное целое. Для поля, используемого в связи между таблицами, невозможно изменить тип данных или свойство Размер поля. Чтобы изменить значение свойства Размер поля, можно временно удалить связь. Если изменить тип данных поля, для воссоздания связи придется изменить и тип данных связанного поля. 44

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

ТИПЫ ОБЪЕКТОВ ВACCESS 46

ЗАПРОСЫ Запросы можно создавать с помощью Мастера запросов (Query Wizard) или Конструктора запросов (Query Design). 1. Запросы на выборку возвращают данные из одной или нескольких таблиц и отображают их в виде таблицы, записи в которой можно обновлять (с некоторыми ограничениями). Запросы на выборку можно также использовать для группировки записей и вычисления сумм, средних значений, подсчета записей и нахождения других типов итоговых значений. 2. Запросы с параметрами - это запрос, при выполнении отображающий в собственном диалоговом окне приглашение ввести данные, например условие для возвращения записей или значение, которое требуется вставить в поле. 47

ЗАПРОСЫ 3. Перекрестные запросы - запросы, которые используются для расчетов и представления данных в структуре, облегчающей их анализ. Перекрестный запрос подсчитывает сумму, среднее, число значений или выполняет другие статистические расчеты, после чего результаты группируются в виде таблицы по двум наборам данных, один из которых определяет заголовки столбцов, а другой заголовки строк. 4. Запросы на изменение - запрос, который за одну операцию изменяет или перемещает несколько записей. Существует четыре типа запросов на изменение. 4. 1. На удаление записи 4. 2. На обновление записи. 4. 3. На добавление записей. 4. 4. На создание. 4. 5. На объединение – позволяет объединить данных из двух 48 таблиц с аналогичными структурами.

ЗАПРОСЫ НА ВЫБОРКУ (ЧАСЫ<=100 AND ЛЕК<>0) OR (ЧАСЫ>150 AND 49 ЧС<3)

50

ЗАПРОСЫ НА ВЫБОРКУ ? – один произвольный символ * - любое количество неизвестных символ Выбрать записи со значением в поле ФИО - Боярская или Макова (инициалы Маковой неизвестны). Записи о заданном студенте появятся в результирующей таблице запроса только в том случае, если запись об этом 51 студенте содержится в таблице СТУДЕНТ, а в таблице УСПЕВАЕМОСТЬ имеются записи, связанные с записью о студенте.

ЗАПРОСЫ НА ВЫБОРКУ < >[ИЗУЧЕНИЕ] ! [ЧАСЫ]*[ЧС] Замечание. Указывать таблицу ИЗУЧЕНИЕ для поля ЧАСЫ обязательно, потому что поле с таким же именем имеется и в таблице ПРЕДМЕТ 52

ЗАПРОС С ВЫЧИСЛЯЕМЫМ ПОЛЕМ И УСЛОВИЕМ ОТБОРА ПО ЕГО ЗНАЧЕНИЮ Вычисляемое поле в пустой ячейке строки [ЧАСЫ] - [ПР] - [ЛЕК] 53 Для отбора записей с ненулевым значением разности в вычисляемом поле в строку Условие отбора введем <>0

ЗАПРОС С ГРУППОВОЙ ОПЕРАЦИЕЙ ПОДСЧЕТА ЧИСЛА СТУДЕНТОВ В ГРУППЕ В Access предусмотрено девять статистических функций: Sum - сумма значений некоторого поля для группы Avg - среднее от всех значений поля в группе Max, Min - максимальное, минимальное значение поля в группе Count - число значений поля в группе без учета пустых значений Stdev - среднеквадратичное отклонение от среднего значения поля в группе Var - дисперсия значений поля в группе First и Last - значение поля из первой или последней записи в группе 54

ЗАПРОС С ГРУППОВЫМИ ОПЕРАЦИЯМИ И ПОЛЕМ, ВВЕДЕННЫМ ДЛЯ ОПРЕДЕЛЕНИЯ УСЛОВИЙ ОТБОРА ЗАПИСЕЙ Число студентов и средний ПБАЛЛ группы для студентов, средний балл которых выше 4, 5 55

ЗАПРОСЫ С ПАРАМЕТРОМ В условиях отбора полей ФИО и НП укажем запрос параметра, его значение запрашиваться во время выполнения запроса. 56

КОНСТРУИРОВАНИЕ ЗАПРОСА НА СОЗДАНИЕ ТАБЛИЦЫ Этот вид запроса основан на запросе на выборку, но, в отличие от него, сохраняет таблицу с результатами запроса. 57

КОНСТРУИРОВАНИЕ ЗАПРОСА НА ОБНОВЛЕНИЕ 58

ПЕРЕКРЕСТНЫЙ ЗАПРОС 59

ПЕРЕКРЕСТНЫЙ ЗАПРОС 60

ПОСТРОИТЕЛЬ ВЫРАЖЕНИЙ Для формирования сложного выражения в вычисляемом поле целесообразно использовать построитель выражений. 61

ПОСТРОИТЕЛЬ ВЫРАЖЕНИЙ 62

ФУНКЦИИ В ACCESS • Функции могут быть использованы при построении запроса не только в строке «Поле» конструктора, но и в строке «Условие отбора» • Также функции могут быть использованы при построении форм и отчетов 63

ФУНКЦИИ В ACCESS 64

ФУНКЦИИ В ACCESS 65

ОКНО КОНСТРУКТОРА ОТЧЕТОВ ПРИ РАЗРАБОТКЕ ОТЧЕТА 66

ОСНОВНЫЕ КОМПОНЕНТЫ СИСТЕМЫ ЗАЩИТЫ БАЗ ДАННЫХ Разграничение Резервное копирование Шифрование Аудит доступа данных доступа к данным 67

ЗАЩИТА ДАННЫХ ВБД Для защиты данных предоставьте доступ к файлу БД на уровне пользователей только надежным пользователям, используя разрешения файловой системы Windows. В Access 2010 не поддерживается защита на уровне пользователя для баз данных, созданных в новом формате (ACCDB и ACCDE-файлы). При открытии базы данных из более ранней версии Access 2010, имеющей защиту на уровне пользователя, в Access 2010 эти параметры будут продолжать действовать. 68

ЗАЩИТА ДАННЫХ ВБД 69

ЗАЩИТА ДАННЫХ ВБД 70

ЗАЩИТА ДАННЫХ ВБД Защиту базы данных обеспечивает добавление пароля, ограничивающего пользователей, которые могут открывать базу данных. Внимание! Если Вы потеряли или забыли пароль, восстановить его будет невозможно, и невозможно открыть базу данных. Нельзя установить пароль базы данных (Монопольный режим), если для базы данных была определена защита на уровне пользователей, а у Вас нет разрешений администратора для базы данных. Пароль базы данных определяется дополнительно к защите на уровне пользователей. 71

ЗАЩИТА НА УРОВНЕ ПОЛЬЗОВАТЕЛЕЙ Защита на уровне пользователей предполагает задание администратором БД определенных разрешений отдельным пользователям и группам пользователей на объекты: таблицы, запросы, формы, отчеты и макросы. Причинами использования защиты на уровне пользователей являются: Защита приложения от повреждения из-за неумышленного изменения пользователями таблиц, запросов, форм, отчетов и макросов, от которых зависит работа приложения; Защита конфиденциальных сведений в БД. 72

ЗАЩИТА НА УРОВНЕ ПОЛЬЗОВАТЕЛЕЙ В Microsoft Access определены две стандартные группы: администраторы – группа «Admins» (учетная запись группы системных администраторов, имеющих полные разрешения на все базы данных, используемые рабочей группой. Программа установки автоматически добавляет в группу «Admins» стандартную учетную запись пользователя «Admin» ) пользователи – группа «Users» (учетная запись группы, в которую включаются все учетные записи пользователей. Microsoft Access автоматически добавляет создаваемые учетные записи пользователей в группу «Users» ), но допускается определение дополнительных групп. Для назначения прав может быть использован мастер защиты. 73

74

ЗАЩИТА НА УРОВНЕ ПОЛЬЗОВАТЕЛЕЙ Изменять разрешения других пользователей на отдельные объекты базы данных могут следующие пользователи: члены группы «Admins» , определенной в файле рабочей группы, который использовался при создании конкретной базы данных; владелец объекта (при активизированной системе защиты это учетная запись пользователя, которая осуществляет управление базой данных или ее объектом. По умолчанию разрешения владельца имеет учетная запись пользователя, создавшего базу данных или ее объект); любой пользователь, получивший на этот объект разрешения администратора. 75

76

СОГЛАШЕНИЯ О ПАРОЛЯХ Имена учетных записей могут иметь длину от 1 до 20 знаков и могут состоять из букв, цифр, пробелов и символов из расширенных наборов, за исключением следующих: знаки " [ ] : | < > + = ; , . ? * пробелы в начале имени; управляющие знаки (с кодами ASCII от 10 до 31). В паролях учитывается регистр знаков Пароль базы данных сохраняется в базе данных, а не в файле рабочей группы. Файл рабочей группы – это файл, который Microsoft Access считывает при запуске, содержащий сведения о пользователях, входящих в рабочую группу. Эти сведения включают имена учетных записей пользователей, их пароли и имена групп, в которые входят пользователи. 77

СОГЛАШЕНИЯ О ПАРОЛЯХ Microsoft Access по умолчанию присваивает учетной записи пользователя «Admin» и любым новым учетным записям пользователя, создаваемым в рабочей группе, пустые пароли. 78

ШИФРОВАНИЕ С ИСПОЛЬЗОВАНИЕМ ПАРОЛЯ БАЗЫ ДАННЫХ Откройте в монопольном режиме базу данных, которую вы хотите зашифровать. Открытие базы данных в монопольном режиме На вкладке Файл выберите команду Открыть. В диалоговом окне Открытие найдите файл, который нужно открыть, и выделите его. Щелкните стрелку рядом с кнопкой Открыть, а затем выберите команду Монопольно. На вкладке Файл выберите пункт Сведения и нажмите кнопку Зашифровать паролем. Откроется диалоговое окно Задание пароля базы данных. Введите пароль в поле Пароль и повторите его в поле 79 Подтверждение.

СПАСИБО ЗА ВНИМАНИЕ. 80