Создание запросов в конструкторе
Для создания запроса в конструкторе выполните команду
Создание → Запросы→ Конструктор запросов
Перед вами появился бланк запросов. В окне Добавление таблицы выберите таблицу Недвижимость, нажмите клавишу Добавитьи закройте это окно.
А теперь в строке Поле в каждой колонке выберите нужные поля, например, следующим образом:
Замечание. Чем больше полей вы укажите в строке Поле, тем более информативным будет результат поиска.
В нашем примере в каждом запросе нужно указывать, по крайней мере, основные характеристики квартиры: метро, количество комнат, общая площадь, цена.
Давайте создадим запрос, задав самое простое условие. Допустим, нам нужно найти все квартиры вблизи станции метро Университет. Чтобы получить такую информацию нужно в строке Условие отбора в поле Метро указать Университет:
Нажмите на панели Конструктор Работа с запросами кнопку Выполнить (с красным восклицательным знаком) для выполнения запроса.
Результат запроса появится на экране в следующем виде:
Для создания следующего запроса необязательно создавать новый запрос, можно на панели Главная нажать кнопку Режим, войти в режим конструктора уже созданного запроса и внести необходимые изменения.
Допустим, теперь мы хотим найти квартиры вблизи станций метро Университет или Проспект Вернадского. Наш запрос будет выглядеть следующим образом:
В результате поиска мы получим квартиры рядом с метро Университет и рядом с метро Проспект Вернадского:
Чтобы указать три станции метро, нужно в строке Условие отбора записать запрос следующим образом: “Университет” Or “Проспект Вернадского” Or “Юго-Западная”.
Несколько значений в условии можно указать другим способом. Например, нам нужно вывести на экран все квартиры в панельных, блочных, кирпичных и монолитных домах. В этом случае можно записать запрос следующим образом:
Чтобы найти квартиры, находящиеся рядом со станциями метро, названия которых начинаются на какую-нибудь букву, например, букву «П», нужно использовать конструкцию like П*. Звёздочка означает: любое количество любых символов. Запишите такое условие в поле Метро в строке Условие отбора.
Результат поиска будет иметь вид:
Для того , чтобы указать условие для логического поля , нужно в соответствующей колонке указать Да или Нет, можно написать Yes или No, Истина или Ложь, а также True или False.
Далее рассмотрим создание запросов с условиями по числовым полям.
Допустим, мы хотим получить информацию о квартирах, в которых более двух комнат. Условие по числовому полю Количество комнат будет выглядеть так:
Давайте усложним задачу. Допустим, мы хотим посмотреть квартиры в каком-либо интервале цен, (в зависимости от той суммы, которой располагает клиент). Чтобы записать такое условие, нужно в строке Условие отбора записать:
>=4500000 AND <= 6500000
Допустим, мы хотим посмотреть самые дешёвые квартиры (меньше 5000000) и самые дорогие (больше 10000000). Запрос запишем так:
Этот же запрос можно записать в строке Условие отбора следующим образом:
<5000000 OR >10000000
Если мы делаем запрос по одному полю, то безразлично как мы записываем условие отбора для нескольких текстовых значений, в одной строке, используя логическое сложение OR или в нескольких строках – Условие отбора, или, и т.д.
Если мы делаем запрос по нескольким полям, то в каждом отдельном случае нужно использовать тот вариант, который соответствует поставленной цели.
Например, если нужно найти трёхкомнатные квартиры в панельных, блочных и кирпичных домах, то лучше записать запрос следующим образом:
Если вы запишите запрос, как показано ниже:
В этом случае вы получите 3-комнатные квартиры в панельных домах, а в блочных и кирпичных все квартиры.
Если же вам нужно одним запросом найти 3-комнатные квартиры на Соколе, 2-комнатные на Войковской и 1-комнатные на Речном вокзале, то этот запрос можно записать так:
Сортировка
В конструкторе запросов можно отсортировать записи по возрастанию или убыванию значений какого-либо поля (по алфавиту или в порядке обратном алфавитному для текстовых полей). Например, мы хотим отсортировать записи по полю Метро по алфавиту. По полю Метро в строке Сортировка выберем из выпадающего списка По возрастанию.
Можно провести сортировку по нескольким полям. Например, сначала отсортировать таблицу по полю Метро по алфавиту, а затем по полю Количество комнат по убыванию. Запрос будет иметь такой вид:
Замечание. При сортировке таблицы по нескольким полям нужно указывать в бланке запроса поля по порядку слева направо. Сначала указывается первое поле, правее второе и т.д.
Замечание. При изменении содержимого таблицы результаты запросов будут меняться.
Самостоятельная работа
Замечание. Перед началом работы просмотрите все запросы, которые вам предстоит построить ивведите в таблицу те записи, которые должны быть найдены по этим запросам, чтобы результаты запросов не были пустые.
I. Создайте в конструкторе запросы к базе данных Недвижимость.
Замечание. При создании запросов нужно выбирать такое количество полей, чтобы получить максимальную информацию о квартирах.
1. Все 1-комнатные квартиры рядом с метро Университет с телефоном.
2. Все квартиры стоимостью не более 5500000 рублей рядом с метро Ясенево.
3. Все квартиры рядом с метро Маяковская или Тверская стоимостью не более 7500000 рублей, этаж – кроме первого.
4. Все квартиры общей площадью от 45 до 60 кв. метров, в кирпичном доме.
5. Все 3-комнатные квартиры в новостройках с отделкой.
6. Все 1-комнатные или 2-комнатные квартиры в новостройках, без отделки, стоимостью от 5000000 рублей до 6500000 рублей.
7. Отсортируйте базу данных по трём ключам: станции метро - по алфавиту, количество комнат - по возрастанию в пределах каждой станции метро, стоимость квартиры – по возрастанию в пределах каждого количества комнат.
8. Всю информацию по коттеджам и деревянным домам, отсортируйте по стоимости в порядке возрастания.
9. Все квартиры рядом с метро Университет или Проспект Вернадского, в монолитных домах с телефоном, с балконом, этаж - не выше 5-го, стоимость в интервале от 6500000 до 8000000 рублей.
10. Все 4-комнатные квартиры в панельных или блочных домах с лифтом, отсортируйте по возрастанию стоимости.
11. Все квартиры рядом с метро Коньково, не менее 3-х комнат, жилая площадь - в интервале от 50 до 70 кв. метров, стоимость - в интервале от 6000000 до 7500000 рублей.
12. Сделайте запрос на квартиру, в какой бы Вы хотели жить сами.
II. Для построенной таблицы Недвижимость создайте в мастередве формы.
III. Результаты двух запросов оформите в виде отчётов, используя мастер.
Замечание. Для того чтобы заголовки в отчётах отражали смысл запроса, при сохранении запросов старайтесь давать им соответствующие названия.
ПЕРЕХОД НА Оглавление
Семинар №3
Нормализация таблиц. Первая нормальная форма (1НФ). Вторая нормальная форма (2НФ). Третья нормальная форма (3НФ). Внешний ключ. Родительская и дочерняя таблицы. Три вида связей между таблицами. Создание запросов, использующих несколько таблиц
Мы уже говорили на первом семинаре, что при создании базы данных необходимо достаточно глубоко изучить ту область, для которой создаётся база данных. Нужно определить те характеристики объектов, которые должны будут отражены в таблицах и описаны с помощью данных в полях записей. Каждая запись описывает один объект с помощью данных, последовательно занесённых в соответствующие поля записи. Данные одного поля – это одна из характеристик объекта. При построении таблиц нужно предварительно изучить круг запросов, которые в дальнейшем придётся осуществлять к этой базе данных.
При построении баз данных нужно стремиться к тому, чтобы она не была громоздкой, так как для баз данных, имеющих большой объём, запросы исполняются очень медленно, да и обслуживание таких баз данных оказывается трудоёмким. Порой вместо того, чтобы произвести изменение в одной записи, приходится изменять данные в нескольких десятках (или тысячах) записей.
Для того чтобы избежать этих трудностей нужно при создании таблиц базы данных учесть ряд правил, которые заложены в процесс нормализациитаблиц. Нормализация – это процесс последовательной пошаговой модификации таблиц базы данных, имеющий целью сокращение избыточности и несогласованности, а так же для приведения таблиц к так называемой третьей нормальной форме (3НФ).
Далее мы рассмотрим процесс нормализации на примере таблиц базы данных «Мир книги», в которой содержится информация о работе интернет-магазина, торгующего книгами. В этой базе данных представлена информация о книгах, клиентах, сотрудниках, отделах, продажах и т.д.[1]
При разработке базы данных рассматривают три основные формы 1НФ, 2НФ, 3НФ.
Первая нормальная форма (1НФ) требует, чтобы каждое значение поля таблицы базы данных было неделимо и не содержало повторяющихся групп. Что это значит?
Неделимость означает, что содержимое поля не должно делиться на более мелкие значения.
Пример 1
Таблица Клиенты может быть преобразована в 1НФ следующим образом:
| Код клиента | ФИО |
| Иванов Пётр Иванович | |
| Петров Павел Сергеевич |
| Код клиента | Фамилия | Имя | Отчество |
| Иванов | Пётр | Иванович | |
| Петров | Павел | Сергеевич |
Это преобразование нужно сделать в том случае, когда необходимо рассматривать имена, отчества и фамилии как самостоятельные характеристики клиентов. Если же в дальнейшей работе не предполагается работа с отдельными характеристиками, например, с фамилиями, то можно считать, что исходная таблица уже находится в 1НФ.
Пример 2
То же самое можно сказать о преобразовании таблицы в следующем примере. Если в дальнейшем не предполагается работа с различными книгами одного автора, то можно считать, что исходная таблица Книгиуже находится в 1НФ. Если же есть необходимость рассматривать отдельно ФИО автора книги и название книги, то таблица Книгиможет быть переведена в 1НФ следующим образом:
| Код книги | Книга |
| Пушкин А.С. «Сказки» | |
| Лермонтов М.Ю. «Стихи» |
| Код книги | Автор | Название |
| Пушкин А.С. | Сказки | |
| Лермонтов М.Ю. | Стихи |
Повторяющимися называются поля, содержащие одинаковые по смыслу значения.
Пример 3
Таблица Отделы может быть переведена в 1НФ следующим образом:
| Код отдела | Отдел | Продавец 1 | Продавец 2 | Консультант 1 |
| Наука | Окунева Л.О. | Орлова М.И. | Медведева Р.И. | |
| Учебная литература | Соловьёва Н.А. | Петухова Н.Н. | Волкова Н.П. |
| Код отдела | Отдел | Должность | ФИО |
| Наука | Продавец | Окунева Л.О. | |
| Наука | Продавец | Орлова М.И. | |
| Наука | Консультант | Медведева Р.И. | |
| Учебная литература | Продавец | Соловьёва Н.А. | |
| Учебная литература | Продавец | Петухова Н.Н. | |
| Учебная литература | Консультант | Волкова Н.П. |
Если в отделах потребуется добавить несколько сотрудников, имеющих такие же или другие должности, то во втором случае не нужно будет переделывать таблицу.
Вторая нормальная форма (2НФ) требует, чтобы все поля таблицы зависели от первичного ключа целиком. То есть первичный ключ pk должен однозначно определять запись и не должен быть избыточным. Те поля, которые зависят от части первичного ключа должны быть выделены в отдельную таблицу.
Пример 4
Таблица, представленная ниже, содержит отделы с указанием сотрудников, работающих в каждом из них.
Отделы
| Код отдела | Код сотрудника | Отдел | Сотрудник | Должность |
| Учебная литература | Волкова Н.П. | Консультант | ||
| Учебная литература | Соловьёва Н.А. | Продавец | ||
| Культура | Орлова Д.Н. | Продавец | ||
| Наука | Медведева Р.И. | Консультант | ||
| Иностранная литература | Медведева Р.И. | Консультант |
Первичный ключ в этой таблице состоит из двух полей: Код отдела и Код сотрудника. Эта таблица может быть преобразована во вторую нормальную форму (2НФ) в результате создания двух таблиц Отделыи Сотрудники.
Отделы
| Код отдела | Отдел |
| Иностранная литература | |
| Культура | |
| Наука | |
| Учебная литература |
Сотрудники
| Код сотрудника | ФИО | Должность |
| Соловьёва Н.А. | Продавец | |
| Орлова Д.Н. | Продавец | |
| Медведева Р.И. | Консультант | |
| Волкова Н.П. | Консультант |
Замечание. Если таблица находится в первой нормальной форме (1НФ), и первичным ключом является одно поле, то таблица уже находится во второй нормальной форме (2НФ).
Третья нормальная форма (3НФ) требует, чтобы значение одного поля не зависело от значения другого поля, не являющегося первичным ключом.
Пример 5
Продажа
| Номер заказа | Код клиента | Дата | Автор | Название | Цена | Количество |
| 10.12.2014 | Пушкин А.С. | Сказки | ||||
| 15.05.2015 | Лермонтов М.Ю. | Стихи | ||||
| … | … | … | … | … | … |
Цена является лишним информационным элементом в таблице, так как зависит от полей Автор и Название. Декомпозиция на две таблицы преобразует таблицу Продажа в таблицы Продажа и Книги, находящиеся в 3НФ.
Продажа
| Номер заказа | Код клиента | Дата | Код книги | Количество |
| 10.12.2014 | ||||
| 15.05.2015 | ||||
| … | … | … | … |
Книги
| Код книги | Автор | Название | Цена |
| Пушкин А.С. | Сказки | ||
| Лермонтов М.Ю. | Стихи | ||
| … | … | … |
Таким образом, нормализация базы данных даёт следующие преимущества:
· Улучшает организацию базы данных.
· Сокращает избыточность данных.
· Достигается непротиворечивость информации внутри базы данных.
На предыдущих семинарах мы научились работать с базами данных, состоящих из одной таблицы. В реальной жизни обычно мы имеем дело с несколькими таблицами, так как вести поиск в таблице, содержащей большое количество столбцов, неудобно. При работе с базой данных, содержащей несколько таблиц, по мере необходимости происходит обращение к той или иной таблице, но часто приходится осуществлять запросы, для выполнения которых нужно одновременно производить поиск в нескольких таблицах. Одновременно вести поиск можно только в том случае, когда таблицы связаны между собой.
Так как разные таблицы содержат информацию о разных типах объектов, то должен быть способ осмысленного перемещения от одной таблицы к другой. Таким механизмом является внешний ключ, который применяется для соединения одной таблицы с другой. Это соединение позволяет строкам одной таблицы иметь соответствующие строки в другой таблице. Таблица, которая содержит внешний ключ, называется дочерней, а таблица, на которую внешний ключ указывает – родительской. Внешний ключ устанавливает прямую взаимосвязь с первичным ключом родительской таблицы. Поэтому внешний ключ может принимать только те значения, которые уже принял первичный ключ в родительской таблице. Это ограничение называется ограничением целостности по ссылке. В отличие от первичного ключа значениями внешнего ключа могут быть значения NULL (неопределённые значения).
Итак, давайте дадим определение внешнего ключа. Допустим, мы имеем две таблицы. В первой (родительской) таблице определён первичный ключ. Внешний ключ (foreign key, обозначается fk) – это столбец (или группа столбцов) второй (дочерней) таблицы, любое значение которого должно обязательно совпадать с одним из значений первичного ключа первой (родительской) таблицы или быть неопределённым.
Пример 6
 |
Клиенты
| Код клиента | Фамилия | Имя | Отчество | Телефон | Адрес | Профессия | Тематика |
| Иванов | Пётр | Иванович | Ленинский пр., д.55, кВ.77 | Экономист | Изобразительное искусство |
 |
Продажа
| Номер заказа | Код клиента | Дата | Код книги | Количество |
| … | … | … | … | … |
| 10.12.2014 |
Таким образом, столбец Код клиента в таблице Продажа является внешним ключом.
Внешний ключ и соответствующий ему первичный ключ должны быть одного типа данных, например множество целых чисел. Первичный и внешний ключи необходимы для установления связей между таблицами их содержащими.
Существует три вида связи между таблицами базы данных:
· «один-к-одному»,
· «один-ко-многим»,
· «многие-ко-многим».
Связь «один-к-одному» - это такая связь между таблицами, когда одной записи родительской таблицы соответствует одна запись дочерней таблицы.
Пример 7
Клиенты
| Код клиента | Фамилия | Имя | Отчество | Адрес | Телефон |
| Иванов | Пётр | Иванович | Ленинский пр., д.25,кв.77 |  94951234567 | |
| Петров | Павел | Сергеевич | Ломоносовский пр., д.3, кВ.7 |  84999876543 | |
| … |
Паспортные данные
| Код клиента | Серия | № | Дата выдачи | Кем выдан |
| 5.09.2009 | ОВД «Дорогомилово» | |||
| 7.09.2011 | ОВД «Хорошевский» | |||
| … |
Паспортные данные используются очень редко, и имеет смысл их хранить в отдельной таблице, а обращаться к ней по необходимости. Сколько клиентов в таблице Клиенты, столько же записей содержится в таблице Паспортные данные. Каждой записи в таблице Клиентысоответствует одна запись в таблице Паспортные данные. Эта запись содержит серию, номер, дату выдачи паспорта, название организации, выдавшей паспорт клиента, зарегистрированного в таблице Клиенты под тем же номером. Это, по существу, одна, разделённая на две, таблица. Одной из причин, по которой создают связь «один-к-одному», является желание не создавать слишком громоздких таблиц. Следует заметить, что связь поддерживается с помощью первичного ключа.
Связь «один-ко-многим» имеет место, когда одной записи родительской таблицы может соответствовать несколько записей дочерней таблицы. Связь между таблицами Клиентыи Продажаявляется видом связи «один-ко-многим». В родительской таблице Клиентыполе Код клиента является первичным ключом, а в дочерней таблице Продажаполе Код клиента является внешним ключом. Одной записи родительской таблицы Клиентыможет соответствовать несколько записей дочерней таблицы Продажа, так как каждый клиент может совершить несколько покупок. Не исключено, что в дочерней таблице может и не быть записей, соответствующих записи в родительской таблице. Например, клиент зарегистрировался, но ещё не купил ни одной книги.
Связь «многие-ко-многим» имеет место, когда:
1. Записи в родительской таблице могут соответствовать несколько записей в дочерней таблице.
2. Записи в дочерней таблице могут соответствовать несколько записей в родительской таблице.
Пример 8
В таблице Отделы представлена информация о том, какие сотрудники (Код сотрудника) работают в каком отделе. Таблица Сотрудники содержит поля Код сотрудника, ФИО, Должность, Телефон.
Отделы
| Код отдела | Название отдела | Код сотрудника |
| Иностранная литература |   13 | |
| Культура | ||
| Наука |  13 | |
| Учебная литература |  11 | |
| Учебная литература |
Сотрудники
| Код сотрудника | ФИО | Должность | Телефон |
| Соловьёва Н.А. | Продавец | ||
| Орлова Д.Н. | Продавец | ||
| Медведева Р.И. | Консультант | ||
| Волкова Н.П. | Консультант | ||
Связь между этими таблицами имеет вид «многие-ко-многим». В каждом отделе может работать несколько сотрудников, при этом каждый сотрудник может по мере необходимости работать в нескольких отделах. Например, один день в одном отделе, а в другой день – в другом, или до обеда в одном отделе, после обеда – в другом.
Работа с такими таблицами сводится обычно к созданию третьей связующей таблицы, позволяющей организовать две связи «один – ко многим», например: