Язык SQL. Запросы на выборку данных с GROUP BY.
Логические схемы сущность-связь. Схема данных. Конструктор запросов QBE.
Главный ключ системы
Для выполнения операций над данными необходимо иметь для каждой записи (строки) таблицы уникальный идентификатор, значение которого однозначно определяет только эту запись. Этот идентификатор называют Главный ключ (primary key).Он может состоять из одного или нескольких полей. Например, в TELEF (телефонный справочник, см. пример)- роль ключа выполняет одно поле- Номер телефона, а в SEBEST- 3 поля: Фирма, Прод., Сх.
Главный ключ должен обладать двумя свойствами:
1.Однозначной идентификацией записи.
2.Отсутствием избыточности- никакое поле нельзя удалить из ключа, не нарушая при этом однозначности (первого свойства).
В примере ZAKAZ - главным ключом является номер завода (поскольку бессмысленно иметь иначе).
Главным ключом в таблице KADR «просится» быть Ф.И.О…. (посмотрим далее).
Таким образом, указание главного ключа - это и есть единственный способ отличить один экземпляр объекта от другого.
Вернемся к Ф.И.О.- это не надежный ключ. Более надежным является в пределах предприятия - табельный номер; в пределах страны - номер и серия паспорта или просто один номер (как в США- social secuirity number).
Слово «главный» предполагает и наличие неглавного или простого (вторичного) ключа. Этот термин возникает в операции, подразумевающей просмотр по какому-либо полю. Например, по полю «Катег» в примере с телефонным справочником. Т.е. при этом «Катег»- это простой ключ и его значение может быть неуникальным.
Один из выводов по ключам: главный ключ - только один, а простых ключей может быть множество.
Простые ключи используются при так называемом индексировании (об этом далее).
Это важные теоретические положения, но на практике могут быть и дубликаты главных ключей, и файлы (таблицы) без понятия главного ключа. Есть способы мириться с этими отступлениями. Например, в первом случае пользователю предоставляется возможность исправить дублирование или оставить это системе (однако не во всех системах это так).
Модели «сущность-связь»
Основная статья: ER-модель данных
Модель «сущность-связь» (англ. “Entity-Relationship model”), или ER-модель, предложенная П. Ченом[1] в 1976 г., является наиболее известным представителем класса семантических (концептуальных, инфологических) моделей предметной области. ER-модель обычно представляется в графической форме, с использованием оригинальной нотации П. Чена, называемой ER-диаграмма, либо с использованием других графических нотаций (Crow's Foot, Information Engineering и др.).
Основные преимущества ER-моделей:
· наглядность;
· модели позволяют проектировать базы данных с большим количеством объектов и атрибутов;
· ER-модели реализованы во многих системах автоматизированного проектирования баз данных (например, ERWin).
Основные элементы ER-моделей:
· объекты (сущности);
· атрибуты объектов;
· связи между объектами.
Сущность — объект предметной области, имеющий атрибуты.
Связь между сущностями характеризуется:
· типом связи (1:1, 1:N, N:М);
· классом принадлежности. Класс может быть обязательным и необязательным. Если каждый экземпляр сущности участвует в связи, то класс принадлежности — обязательный, иначе — необязательный.
Схема данных
Создание схемы данных позволяет упростить конструирование многотаблиц, форм, запросов, отчетов, а также обеспечить поддержание целостности взаимосвязанных данных при корректировке таблиц. Схема данных наглядно отображает таблицы и связи между ними, а также обеспечивает использование связей при обработке данных и целостность БД.
Схема данных задает структуру БД. Она является графическим образом БД. Схема данных базы графически отображается в своем окне, таблицы представлены списками полей, а связи - линиями между полями разных таблиц. Схема данных ориентирована на работу с таблицами, отвечающими требованиям нормализации, между которыми установлены связи 1:М и 1:1 с обеспечением целостности БД. Поэтому схема данных отроится в соответствии с информационно-логической моделью.
При построении схемы данных Access автоматически определяет по выбранному полю связи тип связи между таблицами. Если поле, по которому нужно установить связь, является уникальным ключом, как в главной таблице, так и в подчиненной Access устанавливает связь «один – к – одному». Если поле связи является уникальным ключом в главной таблице, а в подчиненной таблице является неключевым или входит в составной ключ, Access устанавливает связь «один ко многим» от главной таблице к подчиненной.
Связи - объединения. При выборе в качестве поля связи в главной таблице неключевого поля Access сообщает, что тип отношения не может быть определен. В этом случае между таблицами возможно установление только связи - объединения. Связь - объединение обеспечивает объединение записей двух таблиц, имеющих одинаковые значения в поле связи. Причем производится объединение каждой записи из одной таблицы с каждой записью из другой таблицы при условии равенства значений в поле связи. Результатом объединения записей, который определяется выбором одного из трех способов, может быть:
- объединение только тех записей, в которых связанные поля обеих таблиц совпадают;
- объединение тех записей, в которых связанные поля обеих таблиц совпадают, а также объединение всех записей из первой таблицы, для которых нет связанных во второй, с пустой записью второй таблицы;
- объединение тех записей, в которых связанные поля обеих таблиц совпадают, а также объединение всех записей из второй таблицы, для которых нет связанных в первой, с пустой записью первой таблицы.
Создание схемы данных начинается в окне БД с выполнения команды Сервис / Схема данных или нажатием соответствующей кнопки. После этого можно выбрать таблицы, включаемые в схему данных, и приступить к определению связей между ними. Устанавливая связи между парой таблиц в схеме данных, надо выделить в главной таблице уникальное ключевое поле, по которому устанавливается связь, и протащить курсор мыши в соответствующее поле подчиненной таблицы.
QBE-запросы (Query By Example) — запросы, строящиеся с помощью конструктора запросов, представляющего собой графический инструмент для создания запросов по образцу.
Данный метод отбора данных впервые предложен Моше Злуфом (англ. Moshé M. Zloof), сотрудником исследовательского центра IBM в 1970 году.
Эксплуатационным преимуществом поиска QBE является то, что для формирования запроса не требуется использовать специализированный язык запросов, синтаксис которого может быть сложен и недоступен конечному пользователю. Пользователю выводится окно, в котором указаны все поля данных, встречающиеся в каждой записи данных; введение информации в конкретное поисковое поле ограничит поиск совпадением (полным или частичным, в зависимости от договорённости реализации) по данному полю. Проверка условий осуществляется только по заполненным условиям на поля, а поля, условия на которые указаны не будут, могут соответствовать чему угодно. Многие практические реализации QBE допускают также не только конъюнктивное соединение условий в заполненных полях, но и другие варианты соединения условий (например, дизъюнкцию, отрицание, существование или несуществование связанных записей и другие).
24.Язык SQL. Запросы на выборку данных.
SQL (произносится “SEQUEL”)- структурированный язык запросов (Structured Query Language).
Базы данных имеют тенденцию к постоянной интеграции, что привело к необходимости разработки стандартного языка запросов, пригодного для использования на множестве компьютерных платформ.
SQL изначально не являлся языком программирования, поскольку на нем пишутся не программы, а запросы к БД. Т.е. SQL- декларативный язык. Это означает, что с его помощью можно сформулировать ЧТО необходимо получить, но нельзя указать КАК это сделать. Операторы типа if, for, do, while и т.п. – отсутствуют.
Запрос на языке SQL состоит из одной или нескольких команд SQL, следующих одна за другой и разделенных точкой с запятой. Наиболее важные команды:
| SELECT | Выбрать | Вsбрать данные из БД |
| INSERT | Добавить | Добавить данные в БД |
| UPDATE | Обновить | Обновить данные в БД |
| DELETE | Удалить | Удалить данные из БД |
| GRANT | Разрешить | Предоставить привилегию пользователю |
| REVOKE | Отменить | Отменить привилегию пользователя |
| COMMIT | ЗАФИКСИРОВАТЬ | Зафиксировать текущую транзакцию |
 ROLLBACK
| ПРЕРВАТЬ | Прервать текущую транзакцию |
Инструкция SELECT
Инструкция select извлекает информацию из базы данных и возвращает ее в виде таблицы результатов запроса. В кратком введении в SQL, сделанном в главе 2, уже приводились примеры инструкций select. Ниже показано несколько образцов запросов, извлекающих данные об офисах.
Вывести список офисов с их плановыми и фактическими объемами продаж.
SELECT CITY, TARGET, SALES FROM OFFICES
CITY TARGET SALES
Denver $300,000.00 $186,042.00
New York $575,000.00 $692,637.00
Chicago $800,000.00 $735,042.00
Atlanta $350,000.00 $367,911.00
Los Angeles $725,000.00 $835,915.00
Вывести список офисов, расположенных в восточном регионе, с их плановыми и фактическими объемами продаж.
SELECT CITY, TARGET, SALES FROM OFFICES WHERE REGION = ‘Eastern’
CITY TARGET SALES
New York $575,000.00 $692,637.00
Предложение GROUP BY позволяет определить подлинность значений отдельного поля в терминах другого поля и применять функции агрегирования к полученному подмножеству. Это дает возможность комбинировать поля и агрегатные функции в одном SELECT. Например, предположим, что нужно найти наибольший заказ из тех, что получил каждый из продавцов.
SELECT snum, MAX (amt)
FROM Orders
GROUP BY snum
При этом результат- несколько строк.
GROUP BY применяет агрегированные функции отдельно к каждой из серии групп, которые определяются общим значением поля (одно и тоже snum). Справа- МАХ значение на группу (с номером 1001).
Возможно, применить GROUP BY к нескольким полям:
SELECT snum, odate, MAX (amt)
FROM Orders
GROUP BY snum, odate;
Запрос дает наибольший заказ сделанный каждому продавцу на каждую дату.