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

Лекция 5

Иерархические и сетевые модели данных

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

Модель данных – это некоторая абстракция, которая позволяет пользователям и разработчикам трактовать их уже как сведения, содержащие данные и взаимосвязь между ними. Любая база данных состоит из элементов данных (реквизитов) и связей между ними. Значит, чтобы организовать данные в базу, предварительно должна быть разработана схема, позволяющая изобразить связи между элементами данных. Такую схему принято называть моделью данных. Т

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

Иерархическая модель данных

Иерархическая (древовидная) модель появилась впервые в структурах данных языка Кобол. На первом уровне иерархии находится только одна вершина, называемая корнем дерева. Каждая вершина имеет связи с вершинами-потомками и/или предками, связи между вершинами одного уровня отсутствуют. Основными понятиями иерархической структуры считают:

Узел (элемент) – совокупность атрибутов данных, описывающих некоторый объект (на схеме это вершины графа).

Уровень.

Связь.

Традиционно доступ к информации возможен только по вертикальной схеме, начиная с корня. (Современные средства обработки данных – реализациb XML, XPath– горизонтальное перемещение по структуре данных).

Пример иерархической организации данных – организация файлов и папок в файловой системе ПК. Имя диска – корневая вершина, папки – вершины, имеющие потомков(вложенные папки и файлы), файлы – листья, т.е. висячие вершины, не имеющие потомков.

Достоинства: эффективное использование памяти и неплохие показатели временных затрат на выполнение операций; пригодны для формирования БД с теми данными, которые сами по себе имеют иерархическую структуру.

Недостатки: громоздкость; сложность физической реализации для больших древовидных структур.

Организация данных в СУБД иерархического типа определяется в терминах:

элемент(атрибут) – наименьшая единица данных, имеет уникальное имя (также называемое полем), по имени обращаются к элементу при обработке;

запись (группа) – именованная совокупность атрибутов, именно записи изменяются, добавляются и удаляются,

групповое отношение – иерархическое отношение («родитель – потомок») между записями двух типов.

Пример:

Рассмотрим следующую модель данных: предприятие состоит из отделов, отделы — из сотрудников. Сотрудник может работать только в одном отделе.

Для информационной системы управления персоналом создадим групповое отношение (рис. 1.а):

родительская записи - ОТДЕЛ (НАИМЕНОВАНИЕ_ОТДЕЛА, ЧИСЛО_РАБОТНИКОВ),

дочерняя запись - СОТРУДНИК (ФАМИЛИЯ, ДОЛЖНОСТЬ, ОКЛАД). (Для простоты полагается, что имеются только две дочерние записи).

Для автоматизации учета контрактов с заказчиками создана еще одной иерархической структуры: заказчик — контракты — сотрудники, задействованные в работе над контрактом. Это дерево будет включать записи (рис. 1.б)

ЗАКАЗЧИК(НАИМЕНОВАНИЕ_ЗАКАЗЧИКА, АДРЕС),

КОНТРАКТ(НОМЕР, ДАТА,СУММА),

ИСПОЛНИТЕЛЬ (ФАМИЛИЯ, ДОЛЖНОСТЬ, НАИМЕНОВАНИЕ_ОТДЕЛА)

Видно, что в схеме есть частичное дублирование информация между записями СОТРУДНИК и ИСПОЛНИТЕЛЬ (такие записи называют парными), причем в иерархической модели данных не предусмотрена поддержка соответствия между парными записями.

Иерархическая модель реализует отношение между родительской и дочерней записью по схеме «один-ко-многим» (одной родительской записи соответствует любое число дочерних). Допустим, что исполнитель может принимать участие более чем в одном контракте (т.е. возникает связь типа «многие-ко-многим»). В этом случае в базу данных необходимо ввести еще одно групповое отношение, в котором ИСПОЛНИТЕЛЬ будет являться исходной записью, а КОНТРАКТ — дочерней (рис. 1.в). Таким образом, мы опять вынуждены дублировать информацию.

Рисунок 1- иерархическая модель

Из этого примера виден один из основных недостатков и иерархических БД – большое количество дублирования информации.

Операции над данными, определенные в иерархической модели:

ДОБАВИТЬ в базу данных новую запись. Для корневой записи обязательно формирование значения ключа.

ИЗМЕНИТЬ значение данных предварительно извлеченной записи. Ключевые данные не должны подвергаться изменениям.

УДАЛИТЬ некоторую запись и все подчиненные ей записи.

ИЗВЛЕЧЬ:

- извлечь корневую запись по ключевому значению, допускается также последовательный просмотр корневых записей

- извлечь следующую запись (следующая запись извлекается в порядке левостороннего обхода дерева)

Сетевая модель данных

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

Основное различие этих моделей состоит в том, что в сетевой модели запись может быть членом нескольких групповых отношений. Согласно этой модели

а) каждое групповое отношение именуется;

б) имя задает тип отношения, тип определяет свойства общие для всех экземпляров данного типа.

в) Экземпляр группового отношения представляется записью-владельцем и множеством (возможно пустым) подчиненных записей. При этом имеется следующее ограничение: экземпляр записи не может быть членом двух экземпляров групповых отношений одного типа.

ПРИМЕР

а) имя отношения – «ОТДЕЛ-СОТРУДНИК»,

б) запись владелец (родительская запись) содержит

- имя отдела,

- число сотрудников,

подчиненные записи содержат

- ФИО

- Должность

- Оклад

в) Экземпляр группового отношения

ОТДЕЛ : «Отдел технической поддержки»

2 (2 – число сотрудников в отделе).

СОТРУДНИК: Иванов А.А.

Инженер

12 000 р.

Данный сотрудник работает только в одном подразделении.

Иерархическая структура преобразовывается в сетевую следующим образом:

1)деревья 1.a и 1.b заменяются единой сетевой структурой, в которой запись СОТРУДНИК входит в два групповых отношения;

2)для отображения связи «многие-ко-многим» вводится запись СОТРУДНИК_КОНТРАКТ, которая не имеет ключевых полей и служит для связи записей КОНТРАКТ и СОТРУДНИК (при этом в записи может храниться и полезная информация, например, зарплата данного сотрудника по данному контракту.)

Рисунок 3 – сетевая модель

Групповое отношение характеризуется следующими признаками, которые должен назначить(определить) разработчик:

способ упорядочивания подчиненных записей

режим включения подчиненных записей

режим исключения подчиненных записей

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

произвольный,

хронологический /очередь/,

обратный хронологический /стек/,

сортированный.

Режим включения подчиненных записей:

автоматический – при занесении записи в БД запись сразу же закрепляется за неким владельцем;

ручной - позволяет запомнить в БД подчиненную запись и не включать ее немедленно в экземпляр группового отношения. Эта операция позже инициируется пользователем).

 

Режим исключения Принято выделять три подчинения записей в групповых отношениях:

Фиксированное подчинение, т.е подчиненная запись жестко связана с записью владельцем. При удалении записи-владельца все подчиненные записи автоматически тоже удаляются. В рассмотренном выше примере фиксированное членство предполагает групповое отношение между записями "КОНТРАКТ" и "ЗАКАЗЧИК", так как контракт не может существовать без заказчика.

Обязательное подчинение. Допускается переключение подчиненной записи на другого владельца, но невозможно ее существование без владельца. Таким отношением связаны записи "СОТРУДНИК" и "ОТДЕЛ". Сотрудник не существует вне какого-либо отдела, но может перейти в другой отдел.

Необязательное подчинение. Можно исключить запись из группового отношения, но сохранить ее в базе данных не прикрепляя к другому владельцу. При удалении записи-владельца ее подчиненные записи - необязательные члены сохраняются в базе, не участвуя более в групповом отношении такого типа. Примером такого группового отношения может служить связь между "СОТРУДНИКИ" и "КОНТРАКТ". После удаления записи «КОНТРАКТ», записи "СОТРУДНИК" могут остаться в базе.

Операции над данными.

ДОБАВИТЬ - внести запись в БД и, в зависимости от режима включения, либо включить ее в групповое отношение, где она объявлена подчиненной, либо не включать ни в какое групповое отношение.

ВКЛЮЧИТЬ В ГРУППОВОЕ ОТНОШЕНИЕ - связать существующую подчиненную запись с записью-владельцем.

ПЕРЕКЛЮЧИТЬ - связать существующую подчиненную запись с другой записью-владельцем в том же групповом отношении.

ОБНОВИТЬ - изменить значение элементов записи.

ИЗВЛЕЧЬ - извлечь записи последовательно по значению ключа и используя групповые отношения

УДАЛИТЬ - убрать из БД запись. Если эта запись является владельцем группового отношения, то анализируется класс членства подчиненных записей. Обязательные члены должны быть предварительно исключены из группового отношения, фиксированные удалены вместе с владельцем, необязательные останутся в БД.

ИСКЛЮЧИТЬ ИЗ ГРУППОВОГО ОТНОШЕНИЯ - разорвать связь между записью-владельцем и записью-членом.

Ограничения целостности.

Как и в иерархической модели обеспечивается только поддержание целостности по ссылкам (владелец отношения - член отношения).

Реляционная модель данных

Предметная область (ПО) замкнутая, в пределах поставленной задачи, система объектов.

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

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

Создание информационных систем (ИС) – по сути дела есть решение задач посредством СУБД.

По сферам применения различают два основных класса ИС: информационно-поисковые системы (ИПС) и системы обработки данных (СОД).

Для построения ИС необходимо:

• определить предметную область;

• определить участников событий, их интересы(потенциальные пользователи ИС);

При проектировании ИС взгляды отдельных пользователей на предметную область называют локальными пользовательскими представлениями (ЛПП).

Сведение этих взглядов в единую систему, выявление общих задач - один из основных при построении ИС. Завершение этапа приведет к формированию глобального пользовательского представления (ГПП), т.е. будет отражать точку зрения администратора БД.

Процесс проектирования ИС (точнее, БД - важной части ИС) состоит из шагов:

сбор данных;

составление частных ЛПП;

унификация пересекающихся эпизодов;

составления ГПП;

формирование модели предметной области (инфологическое проектирование);

составление схемы с учетом используемого СУБД (концептуальное проектирование);

физическое проектирование.

Рисунок 3 – Проектирование ИС

Инфологическая модель - модель без ориентации на конкретные программные или технические средства. Какими средствами можно составить инфологическое описание предметной области? На этот вопрос нет однозначного ответа. Часто используется модель, названная «сущность-связь» (или «объекты-связи»).

Модель «сущность-связь» представляет объекты предметной области и отношения между ними в терминах: сущность, атрибут, связь.

Сущность - представление (абстракция) реально существующего объекта, процесса или явления. Наименование сущности уникально во всей модели.

Тип сущности - определяет набор однородных объектов.

Экземпляр сущности - конкретный объект из этого набора.

Пример: сущность «Ученик» определяет информацию об учениках.

Конкретный ученик Иванов Иван – экземпляр сущности «Ученик».

Все ученики – тип сущности.

Атрибут - свойство сущности (объекта). Его имя должно быть уникально в рамках одной сущности.

Экземпляр атрибута - конкретное значение свойства данного атрибута.

Пример: сущность «Ученик» определяется атрибутами: «Фамилия ученика», «Класс». Для каждого конкретного ученика (экземпляра сущности) следует задать экземпляры атрибутов (конкретные значения). Пусть экземпляр сущности «Ученик» имеет экземпляры атрибутов;

Имя атрибута

Значения атрибута

«Номер ученического билета».

«ФИО»

«Адрес»

«Родители»

«Класс»

«Классный руководитель»

«Специализация класса»

№ХХХХ…

Иванов Василий

г. Такой-то, ул. Такая-то, ХХ-ХХ

ФИО_1, ФИО_2

«8А»

ФИО_3

Название специализации

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

Связь моделирует отношения между объектами предметной области. Наименование связи должно быть уникально во всей модели.. Существует 4 типа связей:

1. «Один-к-одному» - любому экземпляру сущности А соответствует только один экземпляр сущности В, и наоборот.

У любого конкретного ученика может быть только одна характеристика, эта характеристика относится к единственному ученику.

2.  «Один-ко-многим» - любому экземпляру сущности А соответствует 0, 1 или несколько экземпляров сущности В, но любому экземпляру сущности В соответствует только один экземпляр сущности А.

Ученику ставят много оценок; поставленная оценка принадлежит только одному ученику.

3. «Многие-к-одному» - любому экземпляру сущности А соответствует только один экземпляр сущности В, но любому экземпляру сущности В соответствует 0, 1 или несколько экземпляров сущности А.

Преподаватель работает только в одном кабинете, однако рабочий кабинет может быть закреплен за несколькими преподавателями.

4. «Многие-ко-многим» - любому экземпляру сущности А соответствует 0, 1 или несколько экземпляров сущности В, и любому экземпляру сущности В соответствует 0, 1 или несколько экземпляров сущности А.

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

Концептуальное проектирование

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

Реляционная модель данных

Почти все современные системы основаны на реляционной (relational) модели управления базами данных. Название реляционная связано с тем, что каждая запись в такой базе данных содержит информацию, относящуюся только к одному конкретному объекту. В реляционной СУБД все обрабатываемые данные представляются в виде плоских таблиц. В столбцах таблицы находятся атрибуты объектов, а строки - наборы атрибутов отдельных экземпляров объектов.

Инфологическая модель удовлетворяет принципам реляционности. Однако для приведения этой модели к реляционной необходимо выполнить процедуру, называемую нормализацией. Теория нормализации оперирует с пятью нормальными формами. Эти формы предназначены для уменьшения избыточности информации, и каждая следующая нормальная форма должна удовлетворять требованиям предыдущей и дополнительным условиям. На практике четвертая и пятая формы, как правило, не используются.

Введем следующие понятия:

Отношение - абстракция объекта как совокупность его свойств.

Экземпляр отношения - совокупность значений свойств конкретного объекта.

Первичный ключ - идентифицирующая совокупность атрибутов, его значения не могут обновляться

Простой атрибут - атрибут, значения которого неделимы.

Отношение находится в первой нормальной форме, если все его атрибуты простые.

При приведении отношения «Ученик» к первой нормальной форме следует атрибут «Родители» разбить на два атрибута «ФИО_1», «ФИО_2», что приводит к увеличению числа свойств объекта.

Отношение находится во второй нормальной форме, если оно находится в первой нормальной форме и значения неключевых атрибутов однозначно определяются значением первичного ключа. Если первичный ключ есть номер билета, то он однозначно определяет конкретного ученика, т.е. все его свойства.

Отношение находится в третьей нормальной форме, если оно находится во второй нормальной форме и все неключевые атрибуты не зависят друг от друга. Свойства «Специализация класса» и «Классный руководитель» зависят и однозначно определяются свойством «Класс». Вводим новое отношение, удаляем свойства «Специализация класса» и «Классный руководитель» из отношения «Ученик».

Отношение «Ученик»

Отношение «Класс»

«Номер ученического билета».

«ФИО»

«Адрес»

«Родители»

«Класс»

«№класса»

«Классный руководитель»

«Специализация класса»

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

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

Рассмотренный выше пример с сотрудниками и контрактами, применительно к реляционной модели будет иметь вид:

Рис.4. База данных о подразделениях и сотрудниках предприятия.

Номер_отдела – первичный ключ в таблице OТДЕЛ,

– внешний ключ в таблице СОТРУДНИК.

Табельный номер – первичный ключ в таблице СОТРУДНИК,

– внешний ключ в таблице ИСПОЛНИТЕЛИ.

Имя_заказчика – первичный ключ в таблице ЗАКАЗЧИК,

– внешний ключ в таблице КОНТРАКТ.

Номер_контракта – первичный ключ в таблице КОНТРАКТ,

– внешний ключ в таблице ИСПОЛНИТЕЛИ.

Таблица ИСПОЛНИТЕЛИ первичных ключей не имеет.

Ограничения целостности

Целостность данных - это механизм поддержания соответствия базы данных предметной области. В реляционной модели данных определены два базовых требования обеспечения целостности:

целостность ссылок

целостность сущностей.

Целостность сущностей.

Объект реального мира представляется в реляционной базе данных как кортеж некоторого отношения(запись в таблице). Требование целостности сущностей заключается в следующем:

каждый кортеж любого отношения должен отличатся от любого другого кортежа этого отношения ( При реляционном подходe отношение – таблица, кортеж – запись в таблице. Другими словами, записи должны обладать уникальными первичными ключами).

Вполне очевидно, что если данное требование не соблюдается, то в базе данных может хранится противоречивая информация об одном и том же объекте(объект однозначно определяется первичным ключом, в таблице не одна запись с таким ключом, т.е. информация об объекте противоречива). 

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

при добавлении записей в таблицу проверяется уникальность их первичных ключей,

не позволяется изменение значений атрибутов, входящих в первичный ключ, при обновлении записей.

Целостность ссылок

Требование целостности по ссылкам состоит в следующем:

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

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

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

Лекция 6

Основы языка SQL

Язык SQL (Structured Query Language - структурированный язык запросов) представляет собой стандартный высокоуровневый язык описания данных и манипулирования ими в системах управления базами данных (СУБД), построенных на основе реляционной модели данных. Единственной структурой представления данных (как прикладных, так и системных) в реляционной базе данных (БД) является двумерная таблиц.

Основными операциями над таблицами являются:

Проекция - построение новой таблицы из исходной путем включения в нее избранных столбцов исходной таблицы.

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

Объединение - построение новой таблицы из 2-ух или более исходных путем включения в нее всех строк исходных таблиц (при условии, конечно, что они подобны).

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

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

Функционально команды языка можно разделить на группы:

манипулирование данными (Data Manipulation Language, DML)

определение данных (Data Definition Language, DDL)

управление данными (Data Control Language, DCL).

Язык манипулирования данными состоит из 4 основных команд:

SELECT

(выбрать)

INSERT

(вставить)

UPDATE

(обновить)

DELETE

(удалить)

Язык определения данных используется для создания и изменения базы данных и ее составных частей - таблиц, индексов, представлений, триггеров и хранимых процедур. Основными его командами являются:

CREATE DATABASE

(создать базу данных)

CREATE TABLE

(создать таблицу)

CREATE VIEW

(создать представление)

CREATE PROCEDURE

(создать сохраненную процедуру)

ALTER DATABASE

(модифицировать базу данных)

ALTER TABLE

(модифицировать таблицу)

ALTER VIEW

(модифицировать представление)

ALTER PROCEDURE

(модифицировать сохраненную процедуру)

DROP DATABASE

(удалить базу данных)

DROP TABLE

(удалить таблицу)

DROP VIEW

(удалить представление)

DROP PROCEDURE

(удалить сохраненную процедуру)

И др. команды.

Язык управления данными используется для управления правами доступа к данным и выполнением процедур в многопользовательской среде. Более точно его можно назвать "язык управления доступом". Он состоит из двух основных команд:

GRANT

(дать права)

REVOKE

(забрать права)

Запросы к базе данных

В основном используются несколько sql-запросов.

Чаще всего используетя SELECT - выборка данных, вариантов этого запроса существует довольно много. Реже используются INSERT -вставка, UPDATE - обновление, DELETE -удаление записей из базы данных..

1. Запрос Select

Синтаксис SQL-запроса SELECT:

SELECT <поле / поля через запятую>

FROM <имя таблицы>

WHERE <условие / условия>

ORDER BY <поле/поля> ASC / DESC

LIMIT <начальная строка>, <количество строк>;

Примеры

1) SELECT Фамилия FROM users WHERE WHERE (name = 'Мария)'

Это пример операции Ограничения - выбор строк таблицы, удовлетворяющих заданным ограничениям.

2) SELECT id, name, birth_date FROM users ORDER BY birth_date ASC;

Данный запрос выбирает поля id, name и birth_date из таблицы users и сортирует результаты по полю birth_date по возрастанию.

Это пример операции Проекция – выбор избранных столбцов таблицы.

3) SELECT [АВТОР], [НАЗВАНИЕ] FROM [Таблица1], [Таблица2]

WHERE ( [ЖАНР] LIKE '%Социально%' AND [ИД]=[КОД] )

Это пример нельзя назвать операцией Произведение, хотя строка результирующего набора состоит из полей первой и второй таблиц, но а) в строку входят не поля исходных таблиц, б) каждая строка первой таблицы объединяется не всеми строками второй таблицы, а только с определенными. (Квадратные скобки ограничивают русские названия. Это особенность Visual Studio, но не общее правило, в Access такого ограничения нет.)

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

SELECT имя_таблицы1. имя_столбца1,

имя_таблицы1. имя_столбца2,

. . . . .

имя_таблицы2. имя_столбца1,

. . . . .

FROM имя_таблицы1, имя_таблицы2

WHERE имя_таблицы1. имя_столбцаX1 = имя_таблицы2. имя_столбцаY1

AND

имя_таблицы1. имя_столбцаX2 = имя_таблицы2. имя_столбцаY2

. . .

Условия объединения могут быть и другими.

4) SELECT * FROM topics WHERE author IS NOT NULL

SELECT * FROM topics WHERE author IS NULL

SELECT * FROM topics WHERE author BETWEEN 1 AND 3

SELECT * FROM topics WHERE author IN (1,3, 5)

Условие выборки author=1, или author=3, или author=5.

SELECT * FROM topics WHERE author NOT IN (1,3, 5)

SELECT * FROM topics WHERE author LIKE ‘Вел%’

Условие выборки author начинается как ‘Вел’. Метасимвол % обозначает любую последовательность символов.

Символ * означает выборку всех= записей таблицы, операции IS NOT, IS есть операции «равно», «не равно».

2. Построение запросов Select c с помощью оператора IN

Синтаксис запроса:

SELECT имя_столбца FROM имя_таблицы WHERE условие IN

( SELECT имя_столбца FROM имя_таблицы WHERE условие IN

( SELECT имя_столбца FROM имя_таблицы WHERE условие

)

)

Внутренние запросы будут возвращать только один столбец и передавать его внешнему запросу.

SELECT topics_name FROM topics WHERE id_topics IN

( SELECT id_topics FROM posts WHERE id_author IN

(SELECT id_ author FROM topics WHERE topics_name = ‘тема_топика’)

)

Самый внутренний запрос вернет идентификатор автора указанной темы. Средний оператор вернет идентификаторы тем, в которые данный автор посылал сообщения (таблица POST). Запрос верхнего уровня вернет названия этих сообщений(статей).

На практике вложенность выше 3 не рекомендуют из-за сложности восприятия кода и увеличения времени обработки запроса.

3. Объединение запросов Select c с помощью оператора UNION

В запросе на объединение присутствует 2 запроса на выборку SELECT, каждый из которых имеет предложение FROM, а также может иметь предложение WHERE. В предложении SELECT перечисляются поля, содержащие данные для извлечения; в предложении FROM перечисляются таблицы, содержащие эти поля; наконец, в предложении WHERE указаны условия отбора полей. Инструкции на выборку объединяются в запрос на объединение с использованием ключевого слова UNION.

Основной синтаксис SQL для запроса на объединение, включающего два запроса на выборку:

SELECT field_1[, field_2,…]

FROM table_1[, table_2,…]

UNION [ALL]

SELECT field_a[, field_b,...]

FROM table_a[, table_b,…];

Предположим, что есть таблица с именем «Товары» и другая таблица с именем «Услуги». Обе таблицы содержат поля, в которых указаны:

имя товара или услуги,

цена,

наличие гарантии;

исключительные условия предоставления продукта или услуги.

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

SELECT name, price, warranty_available, exclusive_offer

FROM Products

UNION ALL

SELECT name, price, guarantee_available, exclusive_offer

FROM Services;

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

UNION ALL - ключевое слово UNION и дополнительное ключевое слово ALL

Ключевое слово UNION указывает, что результаты выполнения инструкции SELECT, которая предшествует ключевому слову UNION, будут объединены с результатами инструкции SELECT, следующей за ключевым словом UNION.

Если используется ключевое слово ALL, повторяющиеся строки не удаляются из объединенного набора, полученного в результате выполнения запроса на объединение. Это может существенно повысить скорость обработки запроса, поскольку не нужно выполнять проверку результатов на наличие повторов. Ключевое слово ALL рекомендуется использовать в следующих условиях:

результат выполнения запроса на выборку не содержит повторяющихся строк.

Наличие повторяющихся строк не имеет значения.

Нужно просмотреть повторяющиеся строки.

4. Объединение запросов Select c с помощью оператора JOIN

Эти объединения часто называют внешними и внутренними . Синтаксис:

SELECT имя_таблицы1. имя_столбца1,

имя_таблицы1. имя_столбца2,

. . . . .

имя_таблицы2. имя_столбца1,

имя_таблицы2. имя_столбца2,

. . . . .

FROM имя_таблицы1

LEFT OUTER JOIN имя_таблицы2

ON имя_таблицы1. имя_столбцаX1

= имя_таблицы2. имя_столбцаY1

SELECT имя_таблицы1. имя_столбца1,

имя_таблицы1. имя_столбца2,

. . . . .

имя_таблицы2. имя_столбца1,

имя_таблицы2. имя_столбца2,

. . . . .

FROM имя_таблицы2

RIGHT OUTER JOIN имя_таблицы1

ON имя_таблицы2. имя_столбцаY1

= имя_таблицы1. имя_столбцаX1

В первом случае выбираются ВСЕ строки из левой таблицы и связанные с ними строки из правой таблицы. Во втором случае – ВСЕ строки из правой таблицы и связанные с ними строки из левой таблицы. Предложение ON задает условия связывания строк двух таблиц. Наконец имеется оператор FULL OUTER JOIN, который просто объединяет 2 таблицы, но далеко не все СУБД такой запрос поддерживают.

Альтернативой OUTER JOIN является объединение INNER JOIN, синтаксис которого аналогичен предыдущему:

SELECT имя_таблицы1. имя_столбца1,

имя_таблицы1. имя_столбца2,

. . . . .

имя_таблицы2. имя_столбца1,

имя_таблицы2. имя_столбца2,

. . . . .

FROM имя_таблицы1 INNER JOIN имя_таблицы2

ON имя_таблицы1. имя_столбцаX1 = имя_таблицы2. имя_столбцаY1

ПРИМЕР

SELECT Таблица1.АВТОР, Таблица1.ЖАНР,

Таблица2.НАЗВАНИЕ, Таблица2.КОД FROM Таблица1 INNER JOIN Таблица2

ON Таблица1.ИД = Таблица2.КОД

ORDER BY Т аблица1.АВТОР, Таблица2.НАЗВАНИЕ

Выходной набор упорядочен по фамилии автора, для одного и того же автора произведения упорядочены по алфавиту.

5. Запросы Insert, Update, Delete

Запросы используются для записи новых строк в базу данных, обновления существующих, удаления строк из базы данных.

Примеры простых запросов:

INSERT INTO имя_таблицы ( имя_столбца1, имя_столбца2, . . . )

VALUES ( ‘значение1’, ‘значение2’, . . . )

UPDATE имя_таблицы

SET имя_столбца1 = ‘значение1’, имя_столбца2 = ‘значение2’, . . .

WHERE имя_столбцаХ = ‘значениеХ’

DELETE FROM имя_таблицы WHERE имя_столбцаХ = ‘значениеХ’

Условия в операторах могут быть и другими, например:

INSERT INTO [Таблица1] ( [АВТОР], [ЖАНР] ) VALUES ( 'имя', 'название_жанр' )

DELETE FROM [Таблица1] WHERE [ИД] = 40

UPDATE [Таблица1] SET [АВТОР] = 'другое_имя', [ЖАНР] = 'другой_жанр'

WHERE [ИД] =16

В инструкции INSERT можно использовать вложенный запрос SELECT для добавления значений в таблицу из другой таблицы или нескольких таблиц (таблицы должны быть из одной базы данных). Использование вложенного запроса SELECT позволяет также одновременно вставлять более одной строки.

INSERT INTO SalesTab

SELECT SalesID, Name FROM SalesPrev

WHERE TypeSale = 'Market'

6. Индексы базы данных

Предположим при решении какой-либо задачи приходится многократно к таблице, содержащей большое число записей, выполнять запрос типа SELRCT . . . FROM . . . WHERE. . Без какой-либо дополнительной информации и дополнительных действий СУБД должна будет каждый раз сканировать всю таблицу на поиск подходящих записей. С целью ускорения поиска и сортировки данных в любой СУБД используются индексы.

Индекс – средство, которое обеспечивает быстрый доступ к данным в таблице на основе значений одного или нескольких столбцов. Индекс представляет собой упорядоченный список значений и ссылок на те записи, в которых хранятся эти значения. Чтобы найти нужные записи, СУБД сначала ищет требуемое значение в индексе, а затем по ссылкам быстро отбирает соответствующие записи. Индексы бывают двух типов: простые и составные. Простые создаются одному столбцу, составные – по нескольким.

Однако применение индексов приносит не только преимущества, но и недостатки. Главным среди них является тот, что при добавлении, удалении, изменении записей требуется обновлять индекс, что при большом количестве индексов в таблице может замедлять работу. Поэтому индексы обычно рекомендуется создавать только для тех столбцов таблицы, по которым наиболее часто выполняется поиск записей. Во многих СУБД индексы хранятся в отдельных файлах и являются предметом заботы разработчиков СУБД, т. к. при нарушении индекса поиск данных выполняется некорректно. В Microsoft Access индексы хранятся в том же файле базы данных, что и таблицы и другие объекты Access. Индексировать можно любые поля, кроме МЕМО-полей, Гиперссылок и объектов OLE.

Таблицы Access всегда автоматически индексируются по ключевому полю, свойство Индексированное поле для ключа всегда имеет значение ДА (Совпадения не допускаются). В режиме конструктора вызвать окно с таблицей с информацией об индексах данной таблицы.

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

Окно Индексы используется для создания составного индекса. В данном окне нужно:

ввести имена столбцов, по которым будет индексироваться таблица,

указать является ли данное поле ключевым,

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

указать, разрешается или нет , чтобы данное поле было пустым, т.е. не задано.

Рисунок 1 – индексированные поля

7. Запросы в Access

Запрос в Access является объектом, который сохраняется в файле базы данных и может многократно повторяться. Запрос позволяет выбирать данные из таблиц и выполнять над ними ряд операций. В Microsoft Access существует несколько видов запросов:

запросы к серверу, которые используются для выборки данных с сервера;

запросы на автоподстановку, автоматически заполняющие поля для новой записи;

запросы на выборку, выполняющие выборку данных из таблиц;

запросы на изменение, которые дают возможность модифицировать данные в таблицах (в том числе удалять, обновлять и добавлять записи);

запросы на создание таблицы, создающие новую таблицу на основе данных одной или нескольких существующих таблиц,

другие типы запросов.

Виды соединений

Между таблицами в запросах может быть нескольких видов соединений.

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

В случае внешнего соединения независимо от того, имеются ли соответствующие записи во второй таблице, все записи первой попадают в результирующее множество запроса.

Для обозначения этих соединений в Access используются зарезервированные слова INNER JOIN для внутреннего соединения и LEFT JOIN или RIGHT JOIN для внешнего.

Если необходимо связать данные любым отношением, кроме отношения равенства, то такое соединение называют соединением по отношению. Соединение по отношению не отображается в Схеме данных и не выводится в окне Конструктора запросов.

Для связывания данных в одной таблице применяют рекурсивное соединение. Оно создается путем добавления в запрос копии таблицы (в результате чего Access назначает псевдоним для копии) и связывания полей идентичных таблиц.

Пример рекурсивного запроса. Имеется таблица авторов. Расположить авторов в 3 колонки.

SQL-запрос выглядим следующим образом:

SELECT Таблица_1.ИД, Таблица_1.АВТОР,

t.ИД, t.АВТОР,

tt.ИД, tt.АВТОР

FROM Таблица_1 AS t, Таблица_1 AS tt, Таблица_1

WHERE ( ((t.ИД)=[Таблица_1].[ИД]+5)

AND

((tt.ИД)=[Таблица_1].[ИД]+10)

);

Запрос в режиме конструктора и режиме таблицы выглядят как:

Рисунок 2 – запрос в режиме конструтора и в режиме таблицы

Запросы с параметрами

Если запрос содержит конкретные значения дат, названий, имен и т. д., то для того, чтобы повторить запрос с другими значениями в условиях отбора, его нужно открыть в режиме Конструктора, изменить условие и выполнить. Чтобы не делать многократно этих операций, можно создать запрос с параметрами. При выполнении такого запроса выдается диалоговое окно Введите значение параметра (Enter Parameter Value), в котором пользователь может ввести конкретное значение параметра.1

Фильтр

В Microsoft Access существует также понятие фильтра, который в свою очередь является набором условий для выбора подмножества записей и/или их сортировки. Между фильтром и запросом на выборку ними существуют различия, которые нужно понимать, чтобы корректно выбирать между запросом и фильтром:.

Фильтры не позволяют в одной строке отображать данные из нескольких таблиц, т. е. объединять таблицы.

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

Фильтры не могут быть сохранены как отдельный объект в окне базы данных.

Фильтры не позволяют вычислять суммы, средние значения, подсчитывать количество записей и находить другие итоговые значения.

Запрос можно использовать:

для просмотра подмножества записей таблицы без предварительного открытия этой таблицы или формы;

для того чтобы объединить в виде одной таблицы на экране данные из нескольких таблиц;

для просмотра отдельных полей таблицы;

для выполнения вычислений над значениями полей.

Заполнение таблиц, вставка в таблицы

Заполнение таблиц вручную

Вручную скопировать из других источников (например, из таблицы Excel)

Использовать запрос в SQL-режиме для перенесения информации из другой таблицы

INSERT INTO Таблица_3 ( ЖАНР)

SELECT DISTINCT Таблица1.ЖАНР FROM Таблица1;

Ключевое слово DISTINCT в подзапросе SELECT означает выборку только уникальных неповторяющихся записей. Запросы к базе данных формируются с помощью специального средства – Конструктора запросов, предусмотрено два режима формирования запросов: режим SQL, позволяющий создавать запрос непосредственно на языке SQL, и режим конструктора, позволяющий создавать запрос на «кнопочном» уровне. Запрос в режиме SQL имеет вид:

Рисунок 5 – запрос на вставку в режиме SQL

Запрос в режиме конструктора

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

Имеется еще один набор связанных таблиц, схема которого представлена ниже

Пример на вставку новой записи в таблицу «А_Исполнители». В данном случае целесообразно выполнить запрос с параметром, сконструировать запрос можно следующим образом:

Пользоваться таким запросом для связанной (подчиненной) таблицы неудобно. Проще вставить данные непосредственно, щелкнув в соответствующей записи главной таблицы Заказы по значку раскрывающегося списка (‘+’).

При этом внешний ключ в таблицу А_Исполнители вносится автоматически ( = первичному ключи из главной таблицы).

Удаление записей из таблиц

Простой SQL-запрос на удаление выглядит следующим образом:

DELETE FROM Таблица1 WHERE ИД=1.

Аналогичный запрос с параметром в Access имеет вид:

DELETE Таблица1.ИД FROM Таблица1

WHERE (((Таблица1.ИД)= [Введите номер удаляемой записи]));

Этот же запрос в режиме конструктора имеет вид

Если таблицы связаны отношением «один_ко-многим», то нельзя удалить записи из главной таблице, так как во второй останутся записи, которые будут ссылаться на несуществующие записи. Поэтому вначале удаляют записи из подчиненной таблицы, затем из главной. Но Access позволяет одновременно удалить записи из обеих таблиц. Для этого при установке связей между таблицами (или позднее) установить флажок «Каскадное удаление связанных записей». В этом случае записи в подчиненной таблице будут удалены автоматически.

Обновление записей в таблице

Запросы на обновление используются в основном, чтобы внести изменения сразу в некоторое число записей.

В примере с заказами обновить поле СуммаДен во всех записях таблицы А_Исполнители с определенным типом заказа.

Запрос на обновление в режимах конструктора и SQL имеет вид:

UPDATE А_ТипЗаказа INNER JOIN

(А_Заказы INNER JOIN А_Исполнители ON А_Заказы.ИД = А_Исполнители.ИДЗ )

ON А_ТипЗаказа.ИД = А_Заказы.ТипЗаказа

SET А_Исполнители.[Сумма Ден] = А_Исполнители.СуммаПроцент/100*А_ТипЗаказа.Стоимость

WHERE ( (( А_Исполнители.ИДЗ )=[А_Заказы].[ИД] ) AND

((А_Заказы.ТипЗаказа)=[ТипЗаказа].[ИД]) );

Перекрестные запросы

Перекрестный запрос позволяет представить данные в виде таблицы, напоминающей Excel, и выполнить некоторую статистическую обработку данных. Источником для перекрестного запроса является или одна таблица или запрос. Если данные для перекрестного запроса принадлежат разным таблицам, то предварительно необходимо построить запрос, включающий в себя все данные. Например, построим таблицу, строки которой – авторы, столбцы – жанры, ячейка таблицы содержит число произведений данного автора в соответствующем жанре.

Шаг 1. Строим запрос, содержащий всю информацию:

Продолжим построение перекрестного запроса с помощью мастера запросов:

Указать запрос-источник

Выбрать поле, значения которого будут представлять строки в перекрестном запросе, в данном случае поле А_Заказы.ИД.

Выбрать поле, значения которого будут представлять столбцы в перекрестном запросе, в данном случае поле А_ТипЗаказа.Услуга.

Для оставшегося поля (определяет ячейку создаваемой таблицы) выбрать функцию ЧИСЛО(), подсчитывающую число исполнителей данного заказа. Результат перекрестного запроса имеет вид:

← Предыдущая
Страница 1
Следующая →

Файл

Инф_технол_Лек5-6.docx

Инф_технол_Лек5-6.docx
Размер: 770.7 Кб

.

Пожаловаться на материал

Основными понятиями в теории баз данных являются категории «данные» и «модель данных». Организация данных в СУБД. Иерархическая модель.

У нас самая большая информационная база в рунете, поэтому Вы всегда можете найти походите запросы

Искать ещё по теме...

Похожие материалы:

Исследовательский проект: Познавательный интерес подростков

Сравнительная характеристика коровьего и женского молока

Институт холода и биотехнологий. Сравнительная характеристика количественного и качественного состава компонентов коровьего и женского молока: белков, жиров и фосфолипидов, углеводов, минеральных веществ, витаминов и ферментов.

Професійна Українська мова

Державна мова. Українська— мова професійного спілкування, стилі мови, професійне мовлення.

Правил по охране труда в подразделениях федеральной противопожарной службы Государственной противопожарной службы

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

Совокупный спрос и совокупное предложение

Совокупный спрос и его факторы. Построение кривой совокупного спроса. Совокупное предложение. Потенциальный ВВП. Макроэкономическое равновесие. Краткосрочное макроэкономическое равновесие

Сохранить?

Пропустить...

Введите код

Ok