Базы данных
учебно-методическое пособие по информатике и икт (11 класс)

Базы данных

Первое занятие: Основные понятия баз данных. Виды баз данных.

База данных (БД) – организованная совокупность данных и связей между ними, предназначенная для длительного хранения во внешней памяти ЭВМ, постоянного обновлении и использования.

Разновидности структуры БД

Иерархические БД: - в которых существует упорядоченность объектов по уровням. Объекты более высокого уровня могут включать в себя объекты более низкого уровня.

Связь между объектами в такой организации данных называется «один ко многим».

Пример: иерархическую БД образуют папки Windows. Рабочий стол (верхний уровень) → Мой компьютер, Корзина, Сетевое окружение (второй уровень) → Диск А, Диск С, Диск D (третий уровень), и т. д.

В общем виде структура таких БД выглядит так:

Схема такой организации данных на примере школы:

Задание: Приведите пример иерархической структуры данных, и перечислите объекты нескольких уровней.

Сетевые БД: - в которых не накладывается никаких ограничений на связи между объектами, хотя объекты разных уровней (предки-потомки) есть.  

Связь между объектами в такой организации данных называется «многие  ко многим».

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

Схема такой организации данных на примере школы:

Задание: Приведите пример сетевой БД. Нарисуйте полностью или фрагмент структуры БД для Вашего примера.

Наиболее распространенным типом БД являются табличная или реляционная БД: - с табличной формой организации данных (реляция – отношение).

Такой вид представления можно организовать везде, где данные связаны отношением: классный школьный журнал успеваемости, телефонный справочник, …

Связь между объектами в такой организации данных называется «один  к одному».

Второе занятие: Структура табличных баз данных.

Структурными составляющими таблицы являются записи и поля:

                        Поле 1                Поле 2                Поле 3                ……

Каждая запись содержит информацию об отдельном объекте системы: одной книге в библиотеке, об одном сотруднике предприятия т.п.

Каждое поле это определенная характеристика (свойство, атрибут) объектов: название книги, автор книги, фамилия сотрудника, год рождения т.п.

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

В реляционных БД существует много типов полей. Вот основные из них:

Текстовой

Числовой

Дата/Время

Логический

А также:

Счетчик

Денежный

Объект OLE

Гиперссылка

и др.

Фрагмент табличной БД:

Описание полей:

Задание: Составьте в тетради табличную БД «Наш класс». Используйте не менее 5-ти полей: ФИО, год рождения и т. д. Опишите поля как в приведенном выше примере.

Третье занятие: Знакомство с СУБД MS Access.

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

Самые известные СУБД: FoxPro, Paradox др., чаще они ориентированы на программистов.

СУБД MS Access ориентирована на пользователя, не обязательно знакомого с программированием.

Лабораторная работа: «Знакомство с СУБД MS Access»

Цель: Познакомиться с СУБД MS Access. Познакомиться табличными базами и типами данных полей. Познакомиться с представлением данных в виде форм на примере учебной базы «Борей».

1. Запустите приложение MS Access. (Пуск→Программы→ MS Access)
2. Откройте учебную базу данных «Борей». (Файл→Открыть… , выберите в «Мои документы» файл «Борей.mdb»)
3. Выберите в объектах таблицы и просмотрите имеющиеся в БД таблицы: «Доставка», «Заказы», «Клиенты» и др. (для просмотра таблицы нужно два раза щелкнуть левой кнопкой мыши на названии таблицы)
4. Откройте поочередно таблицы в режиме конструктора и посмотрите типы данных полей общие свойства полей (щелкнуть один на названии таблицы правой кнопкой мыши и из выпавшего меню выбрать конструктор, после этого таблица откроется в режиме конструктора). Нужно найти в таблицах все основные типы  полей. Закройте таблицы (крестик в правом верхнем углу).
5. Выберите в объектах пункт «Формы». Просмотрите формы имеющиеся в данной БД. (аналогично просмотру таблиц)
6. Просмотрите внимательно форму «Сотрудники». Пролистайте реестр сотрудников. Закройте форму.
7. Откройте таблицу «Сотрудники» и внимательно просмотрите. Какие выводы Вы можете сделать.
8. Закройте приложение MS Access (не вносите нечаянно изменений в базу!)

Задание: Сформулируйте и запишите вывод к пункту 7 лабораторной работы.

Четвертое занятие: Ввод и редактирование данных в базу.

Лабораторная работа: «Создание таблиц данных в MS Access»

Цель: Научиться создавать табличные БД, вводить и редактировать в них данные, сохранять изменения.

Выполните указанные задания из приведенных ниже. Для этого:

1. Запустите приложение MS Access.
2. Создайте новую БД: Файл→Создать; в появившееся области задач выберите пункт «Новая база данных»; в открывшемся окне «Мои документы» дайте название новой БД по смыслу выполняемого задания.
3. Создавайте таблицу путем ввода данных, выбрав соответствующий пункт меню.
4. Название полей можно изменить с помощью двойного щелчка левой кнопки мыши. Необходимая ширина задается перетаскиванием края поля мышью.  

Удаление лишних полей можно произвести с использованием выпадающего меню по щелчку правой кнопки мыши (вверху на поле) и выбора пункта «удалить столбец».

Удаление лишних записей можно произвести с использованием выпадающего меню по щелчку правой кнопки мыши (слева на записи) и выбора пункта «удалить запись».  

Варианты заданий:

На «3»

Составьте БД «Автомобилисты»

Лишние поля и записи можно не удалять.

На «4»

Вариант 1

1

Преобразовать приведенную ниже информацию к табличному виду:

Оля, Петя, 13, пение, 14, баскетбол, Вася, Катя, 13, хоккей, баскетбол, футбол, 15, 11, Коля, 11, танцы, Сережа.

Вариант 2

2

Преобразовать приведенную ниже информацию к табличному виду:

+18, Москва, северный, Пермь, дождь, дождь, южный, +20, +15, Санкт-Петербург, южный, без осадков, без осадков, Екатеринбург, +17, восточный.

На «5»

Составьте БД по ее структуре, и заполните 10 записей:

Пятое занятие: Простые запросы

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

Примеры: сортировка данных, выборка по некоторым условиям, обновление и добавление данных.

Запрос – это команда на выполнение определенного вида манипулирования с данными.

Чтобы составить запрос, необходимо знать правила и способы их составления. В частности в MS Access (как и в других БД) есть  язык запросов SQL и конструктор запросов.  Язык запросов SQL ориентирован на подготовленных специалистов, а конструктор запросов на пользователей.

Запросы можно разделить на простые и составные.  

К простым запросам можно отнести запросы на сравнение полей, поиск по шаблону и т.д.

Если запрос ведется по какому-то одному признаку для каждой записи – такой запрос называется простым.

Лабораторная работа: «Формирование простых запросов»

Цель: Научится составлять простые запросы к готовой БД. Научиться выводить нужную информацию по запросу. Научиться сохранять/удалять  запросы.

1. Загрузите БД «Автомобили» приведенную ниже.
2. Создайте запрос с помощью конструктора запросов: владелец = «Кушниренко», для этого в режиме конструктора добавьте таблицу «Автомобили» в предложенном Вам меню,  выберите поле «Владелец» и внизу в условии отбора напишите «Кушниренко». Далее, выберите нужные поля для вывода на экран приведя запрос к такому виду:

Для выполнения готового запроса выполните команду Запрос→Запуск в главном меню MS Access.

3. Закройте окно запроса с сохранением под именем «Владелец».
4. Аналогично создайте запрос: цвет = «синий», и приведите его путем вывода нужных полей на экран к виду:

Сохраните запрос под именем «Цвет».

5. Создайте запрос: «выпуск = 1998», и приведите к виду:

Сохраните запрос под именем «выпуск»

6. Создайте запрос: «выпуск <1995», и приведите к виду:

7. Создайте запрос: «1989 <=выпуск <=1995»

Шестое занятие: Составные запросы

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

В составных запросах используются служебные слова «и» и «или».  Служебное слово «и», соединяющее два простых запроса (а может и более), показывает те записи, которые удовлетворяют каждому из этих запросов.

Служебное слово «не» используется для выбора записей, не удовлетворяющих заданным условиям.

В MS Access используется английская мнемоника т. е.:

And - "и"

Or – «или»

Not – «не»

Лабораторная работа: «Формирование составных запросов»

Цель: Научиться составлять составные запросы. Отработать вывод на экран заданных полей. Научиться применять поиск по шаблону.

1. Загрузите БД «Автомобили».
2. Составьте запрос «марка = «Волга»  и цвет = «черный»». Для этого выберите необходимые поля и в условии отбора под полем «марка» укажите «Волга», а под полем «цвет» - «черный». Такое условие отбора будет соответствовать союзу «и». Приведите запрос к виду:

3. Аналогично создайте запрос « марка = «Форд» и выпуск > 1990». Не забывайте – чем больше год, тем младше автомобиль! Приведите запрос к виду:

4. Создайте запрос «выпуск < 1995 и регистрация > 1995», отвечающий на запрос какие автомобили были выпущены до 1995 года, а зарегистрированы после 1995 года. Вид запроса должен быть таким:

Чтобы организовать ответ на запрос «Владельцы автомобилей, фамилия которых начинается на букву «К»», в MS Access предусмотрена функция «like». Применение этой функции в запросах называется поиском по шаблону.

5. Составьте зарос: «владелец = «К». Для этого в условие отбора под полем «Владелец» напишите: like «К*», что означает поиск всех владельцев в столбце (поле), фамилия которых начинается на букву «К». Приведите запрос к виду:

6. Составьте зарос: « номер = «*3*» и номер = «*7*»», отвечающий на вопрос «В каких номерах есть цифра «3» и «7» одновременно?». Для создания такого запроса введите в условие отбора под полем «Номер» строку: Like "*3*" And Like "*7*” . Запрос приведите к виду:

7. Составьте запрос « номер = «*0*» и владелец «К*»», отвечающий на вопрос «У каких владельцев с фамилией на «К», в номере есть цифра «0»?» Вид этого запроса:

Седьмое занятие: Практическая самостоятельная работа на создание БД, и составление запросов к ней.

К занятию должна быть подготовлена соответствующая информация в зависимости от выбора уровня сложности

На «3»

Составьте в MS Access БД «СТРАНЫ МИРА» приведенную ниже:

Ответьте на вопросы:

1. Количество полей в БД
2. Количество записей в БД
3. Перечислите типы полей в БД
4. Какова максимальная ширина каждого поля
5. Какое поле можно считать основным
6. Заполните таблицу структуры данной БД в тетради:

Создайте запросы к данной БД:

1. «Страны Европы»
2. «Страны с населением более 10 млн. чел.»
3. «Страны с населением Европы менее 10 млн. чел.»

На «4»

По заданной структуре БД подготовить не менее 15 записей из справочных или других источников, и заполнить БД

БД «Крупнейшие реки мира»

Выполните следующие операции и запросы с БД

1. Упорядочите БД по алфавиту в поле «Реки»
2. Упорядочите БД по убыванию длины реки
3. запрос «Пять рек с максимальной площадью бассейна»
4. запрос «Самая длинная река на букву «А»»
5. запрос «Река с самым малым средним расходом воды в Азии»
6. запрос «Все реки Азии в которых есть две «А», длина более 1000 км., и расходом воды более 1500 куб. м./с.»

 На «5»

Спроектируйте БД «Домашняя Видеотека» и запишите структуру проекта в виде таблицы в тетрадь. В данной БД должно быть не менее 8 полей среди которых «номер», «фильм», «страна», «время», «дата», «жанр», «дата» и др., и 20-ти записей. (Пример структуры БД см. в примере на «4»)

Создайте структуру БД в режиме конструктора

Занесите данные в  БД в режиме таблиц (Данные должны быть реальными!)

Выполните следующие операции с БД и создайте запросы:

1. Упорядочите БД по алфавиту в поле «Фильм»
2. Упорядочите БД по возрастанию длительности фильмов
3. создайте запрос «Три фильма менее 1 ч. 10 мин по длительности»
4. создайте запрос «Комедии производства США»
5. создайте запрос «Фильмы России ранее 2000 года»
6. создайте запрос «Комедии и боевики США и России где встречается буквы «А» и «И», длительностью более часа»
7. Составьте самостоятельно два составных запроса с использованием оставшихся полей.

  Восьмое занятие: Некоторые определения и понятия  реляционных БД

(теория)

При проектировании и создании БД с большими объемами информации, с данными которые часто повторяются, удобней весь объем разбивать на отдельные смысловые таблицы.

Таблица в которой содержится вся собранная информация для данной БД называется плоской таблицей.

Эта же БД, но разбитая на отдельные по смыслу таблицы, называется реляционной БД.

Отдельные таблица в РБД называется отношение (от анг. «relation»-отношение, отсюда и название БД).

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

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

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

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

MS Access всегда «предлагает» задать ключевое поле таблицы. По умолчанию это будет счетчик. Во многих случаях это бывает удобно.

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

Связи бывают «один к одному», «один ко многим»… (см. первое занятие).

Связь «один к одному» осуществляется через общий главный ключ. При этом один экземпляр записи одной таблицы тесно связан с одним экземпляром записи другой таблицы. На примере первого занятия это связь между объектами одного уровня иерархической  БД.

Связь «один ко многим» осуществляется через главный ключ в одной таблице и одноименное поле другой таблицы (такое поле называется внешним ключом). При этом одна запись первой таблицы связана со многими  записями другой таблицы. На примере первого занятия это связь между объектами соседних уровней иерархической  БД.

Задание: Приведите примеры связей «один к одному» и «один ко многим» на примере иерархической БД.

Из определений выше видно что связь устанавливается между парой таблиц. Одна из них является  главной другая подчиненной.

Если связь «один ко многим» то главной является первая таблица. В остальных видах связей главная таблица определяется пользователем.

Все связи между таблицами готовой РБД в MS Access можно посмотреть:

Сервис  → Схема данных…

Чтобы на схеме данных MS Access отображался тип связи, нужно установить в свойствах связи обеспечение целостности данных. После этого MS Access будет проверять изменения при редактировании данных и предупреждать об этом пользователи.

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

Практическая работа «Просмотр организации данных в готовой РБД»

1. Откройте учебную БД «Борей»
2.  Просмотрите связи между таблицами. Убедитесь, что существуют связи «один к одному» и «один ко многим». Найдите связи между таблицами через внешний ключ.
3. Просмотрите ключевые поля таблиц, (для этого открывайте их поочередно в режиме конструктора). В какой таблице в качестве ключа задано несколько полей?
4. Посмотрите, например, как организована связь между таблицами «Заказы» и «Доставка». В данной связи используется идентификатор (ID) в виде числового кода в таблице «Доставка» (в данном случае он совпадает со счетчиком). Использование   идентификаторов позволяет всю информацию об экземпляре записи вводить по короткому коду. Убедитесь, что почти  все таблицы связываются через код-идентификатор.
5. Просмотрите запросы на выборку. Найдите запросы на выборку организованные с помощью связей между таблицами. Какие еще типы запросов встречаются в данной РБД.

Девятое занятие: Преобразование плоской таблицы в РБД

Лабораторная работа «Преобразование плоской таблицы в РБД»

Обязательная часть

Цель: Убедится в эффективности проектирования РБД. Увидеть преимущества  в компактности, логичности и обозреваемости этого способа хранения и обработки больших объемов информации.

Имеется плоская таблица с информацией о посещении курсов студентами:

1. Выделите из данной таблицы информацию о курсах в отдельную таблицу:

Подготовьте эту таблицу в MS Access, задав ключевым поле «КодКурсы»

2. Выделите информацию о посещаемости в следующую таблицу:

Подготовьте эту таблицу в MS Access. Ключ не задавайте.

3. Установите связь в схеме данных

Убедитесь что тип этой связи «один ко многим» (для этого выделите эту связь, откройте правой кнопкой мыши меню и в нем пункт «изменить связь»).

4. Установите в свойствах связи флажок «обеспечение целостности данных». Что произошло? Объясните смысл появившихся значков на концах «веревки».

5. Ответьте на вопросы:

Какая таблица является главной?

Какая таблица является подчиненной?

Какое поле являются ключом?

Какое поле являются внешним ключом?

6. Создайте запрос «Кто что посещает». Запрос создавайте в режиме конструктора, добавив на бланк QBE (от Query By Example – запрос по образцу, так называется режим конструктора создания запросов) обе таблицы и установив связь аналогично как в схеме данных. Пометьте поля для вывода на экран.

7. Сохраните запрос, запустите, проанализируйте результат.

Выводы:

• Преобразованная БД стала компактней, потому что  отпала необходимость вводить повторяющиеся данные (повторяющиеся записи)
• БД стала выглядеть более логично
• Обновление данных (изменение) в такой БД значительно упрощается из-за использования идентификаторов записей
• При больших информационных объемах, реляционное построение БД имеет значительные преимущества

Дополнительная часть

8. Усовершенствуйте далее эту БД собрав в отдельную таблицу фамилии и имена студентов:

9. А таблицу «Регистрация» измените, поместив в нее лишь идентификаторы курсов и фамилий (ключи задавать не нужно):

В этой таблице указано кто из студентов какой курс посещает с помощью идентификаторов. Оцените, насколько это компактно и избавляет от необходимости вводить записи полностью.

10. Измените схему данных следующим образом:

Таблица «Регистрация» связана с остальными через внешние ключи.

11. Создайте запрос на бланке QBE, установив такие же связи между таблицами, как и в схеме данных и отметив необходимые поля для вывода. Посмотрите результат и объясните его.

Десятое занятие: Практическая самостоятельная работа на создание РБД, и составление запросов к ней.

На «3»

Имеется таблица телефонных переговоров.

1. Проанализируйте данные и разбейте данную таблицу на две – таблица «Разговоры» с полями: Номер, Дата, Город, Продолжительность, и таблицу «Телефоны» с полями: Номер, Владелец, Адрес.
2. Организуйте связь между этими таблицами в схеме данных (по номеру).
3. Создайте запрос «Кто куда, когда и сколько звонил» на бланке QBE.
4. Создайте запрос отвечающий на вопрос «Кто звонил в Женеву и сколько времени разговаривал?»
5. Придумайте и создайте подобные запросы.

На «4»

Имеется таблица расписания занятий (см. ниже).

1. Проанализируйте данные и разбейте их на следующие таблицы:

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

Установите связи в схеме данных через эту таблицу.

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

4. Создайте запросы отвечающие на вопросы: «Кто что преподает?», «Кто преподает химию?», «Кто преподает физику?», и т. д.

На «5»

1. Создайте РБД «Наша школа». РБД должна состоять не менее чем из трех таблиц, и отвечать на запросы «Кто в каком классе и какой предмет преподает», «Классный руководитель класса», «Кто ведет физику», и т. д.

Примерная схема построения такой РБД может выглядеть так:

Таблицы:

Схема данных и ключевые поля (обеспечьте сохранение целостности данных!, это позволит избегать ошибок при вводе данных и изменении данных в таблицах):

2. Создайте запросы (не менее пяти), подобные тем, которые перечислены выше.

Примечание: Вносите в таблицы только реальные данные нашей школы. Примеры можно посмотреть в заготовленных БД «Школа», и  «Модель школы»

Используемая литература:

1. Кушниренко и др. «Информатика 7-9»
2. Кузнецов «Информатика 8-9»
3. Семакин и др. «Информатика. Базовый курс»
4. Семакин, Хеннер, «Информатика 11»
5. Семакин и др. «Задачник ч. 2»
6. Еженедельная газета «Информатика», №27, 28 2000 г, №34-46 2004 г, и др.
7. Майкл Хэлворсон, Майкл Янг, MS Office.