Обучение базам данных и СУБД с использованием SQL-ориентированных заданий в профильных классах

Разделы: Информатика

Содержание обучениябазам данных и СУБД состоит из двух частей: инвариантной и вариативной. Инвариантная часть содержания обучения может быть ориентирована на формирование у учащихся знаний в области основных понятий баз данных и СУБД, работы в среде СУБД с графическим интерфейсом (на примере MS Access). В вариативной части содержания обучения базам данных и СУБД могут рассматриваться вопросы проектирования, структурирования данных реляционных баз данных и запросов на языке SQL к базам данных, программирование в среде СУБД, построенной на командном интерфейсе и т.д. (на примере консоли СУБД Access). Результатом  может стать в достаточной мере функциональная база данных с прикладным содержанием. При этом реализация вариативной части обучения предполагает существование условий профильной дифференциации обучения учащихся.

В качестве примера реализации вариативной части элективного курса «Обучение базам данных и СУБД с использованием SQL-ориентированных заданий в профильных классах» хотелось бы привести конспект первого двухчасового урока по данной теме.

Тема урока: язык SQL. Синтаксис языка SQL в запросах на извлечение данных.

Цель урока: познакомить учащихся с понятиями  SQL, режимами отображения запросов, разобрать примеры. Познакомить учащихся с оператором SELECT, использованием нескольких источников (таблиц), группировкой, сортировкой. 

Тип занятия: комбинированный (повторение изученного материала, ознакомление  с новым материалом, закрепление изученного материала, контроль знаний).

Вид занятия: смешанный.

Методы: лекция с элементами беседы, практическая работа.

Время: 90 минут (2 урока).

Программные средства: ОС  MS Windows XP, СУБД MS Access 2003, Power Point,  учебная база данных «Подписка на журналы».

Наглядные средства: презентация в Power Point, порядок выполнения   практической работы, задания для самостоятельной работы, распечатка синтаксиса в запросах на извлечение данных.

ХОД УРОКА

1. Организационный момент

Тема урока. Язык SQL. Синтаксис языка SQL в запросах на извлечение данных.

Цель урока: познакомиться с понятиями  SQL, режимами отображения запросов, разобрать примеры. Познакомиться с оператором SELECT, использованием нескольких источников (таблиц), группировкой, сортировкой. 

2. Актуализация опорных знаний

Одним из наиболее естественных для пользователей способов представления дан­ных является двумерная таблица.
Какая модель БД основана на работе с двумерными таблицами? – реляционная модель.
Доказано, что сетевое и древовидное представление данных может быть сведено к двумерным таблицам.
Вспомним, что называется запросом? – отбор данных на основании заданных условий. Условия отбора записей создаются с использованием операторов сравнения (=, >, <  и т.д.)
Запросы бывают простыми и сложными. Какие запросы называются простыми, а какие сложными? – простой запрос содержит одно условие, а сложный запрос содержит несколько условий для различных полей и таблиц.
Базы данных могут содержать сотни и тысячи записей. Как называется упорядочение записей? – сортировкой.
Какие виды сортировок вы знаете? – сортировка производится по значениям одного из полей базы данных по возрастанию или убыванию.

3. Объяснение нового материала

Одним из языков, появившихся в результате разработки реляционной модели данных, является язык SQL.Аббревиатура SQL означает Structured Query Language (структурированный язык запросов). Стандарт SQL был разработан Американским национальным институтом стандартов (ANSI) в 1986 году, а годом позже Международная организация стандартов (ISO) приняла его в качестве международного. Синтаксис SQL разрабатывался для удобства формирования за­просов «близко к естественному английскому». Предполагалось, что его смогут использовать рядовые пользователи баз данных.
В настоящее время пользователи обычно общаются с БД через более удоб­ный интерфейс клиентских приложений, но взаимодействие между приложени­ем и СУБД часто происходит с использованием SQL.
В зависимости от того где находится база данных относительно сервера, используется внешний или внутренний SQL. При этом синтаксисы SQL внешней и внутренней СУБД различаются, например, для работы в сети можно использовать Microsoft SQL Server, а в качестве  «внутреннего» SQL – использовать SQL Microsoft Access.
В состав SQL входят только команды определения и манипулирования данными и отсутствуют какие-либо команды управления ходом вычислений. Другими словами, в этом языке нет команд типа IF ... THEN ... ELSE, DO ... WHILE, GO TO и др. Подобные задачи должны решаться с помощью языков программирования или управления заданиями.

Язык SQL может использоваться двумя способами:

  • интерактивная работа, заключающаяся во вводе пользователем отдельных SQL-операторов;
  • внедрение SQL-операторов в запросы, созданные в режиме конструктора.

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

Как и большинство современных языков. SQL поддерживает свободный формат записи операторов (не связанный с фиксированными позициями экрана). Структу­ра команд задается набором ключевых слов, представляющих собой обычные слова английского языка: CREATE TABLE («создать таблицу»), INSERT («вставить»), SELECT («выбрать») и т. д.
Язык SQL является первым (и пока единственным) стандартным языком работы с базами данных, который получил достаточно широкое распространение. Практически все крупнейшие разработчики СУБД в настоящее время создают свои продукты именно с использо­ванием языка SQL.
В Microsoft Access при обращении к БД также применяется язык SQL.  Любой запрос, построенный с помо­щью мастера или конструктора, имеет соответствующее представление на язы­ке SQL. Конструктор – лишь визуальное средство для создания запросов. В Access имеется возможность редактировать запросы непосредственно в режиме SQL. Причем, не всякий составленный на SQL запрос, может быть отображен в режиме конструктора – SQL имеет более широкие возможности, чем визуаль­ный конструктор.
Существует три различных режима отображения запроса – конструктор, таблица и SQL <Рисунок1>. При освоении языка SQL бывает по­лезно составить запрос с помощью конструктора, а затем просмотреть или мо­дифицировать его в режиме SQL.
Для переключения режимов отображения запросов используется кнопка «Вид» панели инструментов <Рисунок2>.

Синтаксис языка в простых запросах

1. SELECT <список полей>

2. FROM <список источников> – для построения запросов на извлечение данных, где «список полей» – имена полей (столбцов), которые следует извлечь из источников (таблиц) и поместить в результирующий набор.

3. FROM <список источников> … JOIN <источник> ON<условие> –ограничение на объединяемые записи в выражении FROM

а) INNER JOIN <источник> ON <условие> – в результат включаются только те записи из обеих таблиц, которые связаны между собой, автоматически определяется при создании запроса в режиме конструктора.
б) LEFT JOIN <источник> ON <условие> – в результат включаются все записи из первой таблицы. Если для них нет связанных записей во второй таблице, соответствующие поля результата будут пустыми.
в) RIGHT JOIN <источник> ON <условие> – операция, зеркально сим­метричная левому объединению. Включаются все записи из второй таблицы и связанные с ними записи из первой.
Предложение ON <условие> определяет связь между полями объединяемых таблиц. Результат содержит только комбинации записей, для которых выполняется заданное условие.

4. SELECT <сп.полей> COUNT <имя таблицы.имя поля> AS <имя поля> – вычисляет количество строк возвращаемое запросом. Ключевое слово AS указывает имя  для столбца. Если имя не задано явно, используется соответствующее ему имя поля исходной таблицы. Если же столбец формируется с помощью некоторого выражения, Access при­сваивает ему имя самостоятельно.

5. GROUP BY <список источников> – группировка, могут быть перечислены несколько полей че­рез запятую. Если группировка производится по нескольким полям, объеди­няться в группу будут строки, для которых попарно равны значения всех груп­пируемых полей. Для всех полей, перечисленных в предложении SELECT, но не вошедших в предложение GROUP BY, должны быть определены групповые операции.

6. ORDER BY <имя поля> ASC [DESC] – сортировка, в предложе­нии можно перечислять несколько полей через запятую. Сортировка будет выполняться сначала по первому полю, затем по второму (если совпадают значе­ния первого поля) и т.п., направление сортировки – ASC (по возрас­танию), DESC (по убыванию). Если направление не указано, подразумева­ется значение ASC.

Формат записи операторов основной части синтаксиса языка SQL выдается в распечатанном виде.

4. Практическая работа

Тема, цели и ход работы представлены учащимся в распечатанном виде.

Тема: синтаксис языка SQL в простых запросах на извлечение данных.

Цель: познакомится  с оператором SELECT, использованием нескольких источников (таблиц), группировкой, сортировкой.

Ход работы

Работа выполняется с базой данных «Подписка на журналы». Структура, схема, содержание БД представлены в порядке выполнения практической работы <Рисунок3>. Данную БД предлагается создать в режимах конструктора и таблицы, а далее в режиме SQL создать запросы к базе данных.

№ п/п Постановка задачи Запрос в режиме SQL
Простейшая форма оператора SELECT
1 Вывести следующие данные обо всех клиентах: «Фамилия», «Имя», «Отчество». SELECT Фамилия, Имя, Отчество
FROM Клиент
2 Вывести все данные обо всех клиентах («*» – означает «все поля»). SELECT *
FROM Клиент
Использование нескольких источников (таблиц) в операторе SELECT
(предложения INNER JOIN, LEFT JOIN, RIGHT JOIN)
3 Выведите для всех клиентов сведения «Фамилия» и «КодЖурнала». SELECT Клиент.Фамилия, Подписка.КодЖурнала
FROM Клиент, Подписка
4 Ограничьте результат в предыдущей задаче только комбинациями тех записей, которые содержат одинаковые значения в полях «КодКлиента» таблиц «Клиент» и «Подписка» SELECT Клиент.Фамилия, Подписка.КодЖурнала
FROM Клиент INNER JOIN Подписка
ON Клиент.КодКлиента = Подписка.КодКлиента
5 Выведите данные «КодЖурнала» из таблицы «Подписка» для всех клиентов. SELECT Клиент.Фамилия, Подписка.КодЖурнала
FROM Клиент LEFT JOIN Подписка
ON Клиент.КодКлиента = Подписка.КодКлиента
Группировка, сортировка, имена столбцов в операторе SELECT (предложения COUNT, GROUP BY, AS, ORDER BY)
6 Предыдущий запрос изменить так, чтобы фамилия каждого клиента выводилась один раз, а рядом выводилось количество журналов, выписанных этим клиентом. SELECT Клиент.Фамилия, COUNT(Подписка.КодЖурнала)
FROM Клиент LEFT JOIN Подписка
ON Клиент.КодКлиента = Подписка.КодКлиента
GROUP BY Клиент.Фамилия
7 Надо задать осмысленное название второму полю – «Количество», указать способ сортировки результирующего набора по убыванию. Вывести данные для всех подписчиков. SELECT Клиент.Фамилия, COUNT(Подписка.КодЖурнала) AS Количество
FROM Клиент LEFT JOIN Подписка
ON Клиент.КодКлиента = Подписка.КодКлиента
GROUP ВY Клиент.Фамилия
ORDER ВY Клиент.Фамилия ASC

Результаты выполнения запросов в режиме таблицы MS Access представлены в порядке выполнения практической работы <Рисунок4>.

5. Самостоятельная работа

Задания для самостоятельного выполнения

  • Составьте «Запрос8», где выводятся все данные обо всех журналах.
  • Создайте запрос, в котором содержатся следующие данные обо всех журналах: «НазваниеЖурнала», «НомерЖурнала», «ГодЖурнала».
  • Выведите данные «КодКлиента» для всех журналов.
  • Предыдущий запрос изменить так, чтобы название журнала выводилось один раз и в соседнем поле выводилось количество подписчиков.
  • Название второго столбца сформировано автоматически. Надо задать осмысленное название «Количество». Следует  указать способ сортировки результирующего набора по убыванию. Вывести данные для всех журналов.

6. Подведение итога урока

Учащиеся должны ответить на следующие вопросы:

  • Какие режимы представления запроса вы знаете?
  • Как расшифровывается аббревиатура SQL?
  • Какой используется оператор для создания запроса на извлечение данных из БД в режиме SQL?
  • Что обозначает символ «*» в операторе SELECT?
  • С помощью каких служебных слов выполняется внутреннее объединение? Левое внешнее объединение? Правое внешнее объединение?
  • С помощью каких служебных слов выполняется сортировка полей? Направления сортировки?
  • Какие служебные слова позволяют группировать поля в запросе?

7.  Домашнее задание

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