Базы данных. (5-й семестр вмк)

Скачать 0.73 Mb.

Название	Базы данных. (5-й семестр вмк)
страница	2/6
Тип	Документы

rykovodstvo.ru > Руководство эксплуатация > Документы

1 2 3 4 5 6

Межтабличные связи и ключи. Межтабличную связь можно объявить только для пары таблиц (бинарную) и только типа (1:N) – (Родительская таблица : Дочерняя таблица).

Связь определяется с помощью пары ключей: родительский ключ (REFERENCES) – один из уникальных ключей родительской таблицы (чаще всего, это ее первичный ключ), внешний ключ (FOREIGN KEY) – ключ дочерней таблицы (как правило, это неуникальный ключ).

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

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

Еще раз отметим, что родительский ключ уникальный, поэтому получаем связь типа 1:N, т.к. в родительской таблице не более одной строки с заданным значением такого ключа.

Связь объявляется при дочерней таблице

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

Ограничения целостности базы данных. Ограничения целостности (Integrity Constraint) – это условия правильности данных. СУБД не допускает внесения изменений в данные таблиц, при которых нарушаются объявленные требования к правильности данных.

Ограничения целостности можно классифицировать.

Внутритабличные горизонтальные. Каждая строка таблицы (или конкретное поле каждой строки) должна удовлетворять объявленному условию: заданному обычным логическим выражением (CHECK) или специальными, например NOT NULL – поле должно иметь непустое значение ^⁽*).
Внутритабличные вертикальные. Набор значений в конкретном списке колонок таблицы (конкретного списка полей по всем строкам) должен удовлетворять условию неповторяемости - UNIQUE, PRIMARY (влечет UNIQUE);
Межтабличные (ограничения ссылочной целостности). Для каждой строки дочерней таблицы межтабличной связи, если значение ее внешнего ключа не NULL, то должна существовать строка в соответствующей родительской таблице с таким же значением родительского ключа.

Стратегии поддержания ссылочной целостности. Существуют две основные стратегии поддержания ссылочной целостности.

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

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

Средства описания баз данных (DDL) в SQL.

CREATE TABLE ИмяТаблицы

(Колонка, ... [, ОграничениеТаблицы, ...])

Колонка = ИмяКолонки ТипКолонки

[DEFAULT Константа] [ОграничениеКолонки ...]

ОграничениеКолонки =

NOT NULL | UNIQUE | PRIMARY KEY | CHECK(Условие)

| REFERENCES ИмяРодительскойТаблицы

[(ИмяЕеКолонки)] [СтратегияПоддержки]

ОграничениеТаблицы =

CHECK(Условие)

| UNIQUE (ИмяКолонки, ...)

| PRIMARY KEY (ИмяКолонки, ...)

| FOREIGN KEY (ИмяКолонки, ...)

REFERENCES ИмяРодительскойТаблицы

[(ИмяЕеКолонки, ...)] [СтратегияПоддержки]

СтратегияПоддержки =

ON DELETE CASCADE

| ON UPDATE CASCADE

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

Метасимволы: [] – «может отсутствовать», | - «один из вариантов», ... – «допустимо повторение через предыдущий символ».

ОграничениеКолонки UNIQUE | PRIMARY KEY | REFERENCES используются в случае несоставного ключа, а иначе эти ограничения описываются как ОграничениеТаблицы.
CHECK(Условие) используется как ОграничениеТаблицы, если Условие зависит более чем от одного поля.
Если в ограничении ссылочной целостности не указан родительский ключ, то по умолчанию родительским считается первичный ключ (PRIMARY KEY) родительской таблицы.
Если в ограничении ссылочной целостности не указана СтратегияПоддержки, то по умолчанию действует стратегия RESTRICT (ОГРАНИЧИТЬ).

ПРИМЕР.

/* Table: PSTS, ПОСТАВЩИКИ */

CREATE TABLE PSTS

(

KPST INTEGER DEFAULT 0 NOT NULL PRIMARY KEY,

IMPST VARCHAR(20) DEFAULT '',

ADRPST VARCHAR(30) DEFAULT ''

);

/* Table: DET, ДЕТАЛИ */

CREATE TABLE DET

(

KDET INTEGER DEFAULT 0 NOT NULL PRIMARY KEY,

IMDET VARCHAR(20) DEFAULT '',

CVET VARCHAR(10) DEFAULT ''

);

/* Table: DOG, ДОГОВОРА */

CREATE TABLE DOG

(

KPST INTEGER DEFAULT 0 NOT NULL REFERENCES PSTS,

KDET INTEGER DEFAULT 0 NOT NULL REFERENCES DET,

KOL INTEGER,

DTN DATE,

DTK DATE,

PRIMARY KEY (KPST, KDET), CHECK (DTN

);

/* Table: PST, ПОСТАВКИ */

CREATE TABLE PST

(

KPST INTEGER DEFAULT 0 NOT NULL,

KDET INTEGER DEFAULT 0 NOT NULL,

KOL INTEGER,

DTP DATE CHECK (DTP>'01.01.2000'),

FOREIGN KEY (KPST, KDET) REFERENCES DOG

);

Средства манипулирования данными (DML) в SQL.

Имеется всего четыре, но очень мощных, оператора: SELECT (отбор), INSERT (вставка), DELETE (удаление), UPDATE (обновление).

SELECT (отбор).

SELECT [DISTINCT | ALL] СписокКолонок

FROM ИмяТаблицы [ПсевдонимТаблицы], ...

[WHERE УсловиеОтбора]

[GROUP BY КлючГруппировки]

[HAVING УсловиеОтбораПослеГруппировки]

[ORDER BY КлючУпорядочения]

[UNION [ALL] Select-оператор]

ПсевдонимТаблицы – произвольное имя. Если псевдоним не указан, то по умолчанию он совпадает с ИменемТаблицы.

Семантика оператора SELECT. По исходным FROM-таблицам, назовем их Т1,... строится безымянная выходная таблица.

Строится W1 = декартово произведение FROM-таблиц Т1,... просто присоединением к каждой строке из Т1 каждой строки из Т2, присоединением к результату каждой строки из...
Строится W2 = выборка из W1 по WHERE-УсловиюОтбора
Строки таблицы W2 «пачкуются» в группы. В одну группу входят все строки с одним и тем же значением КлючаГруппировки.

Строится W3 = в W3 формируется точно по одной строке для каждой группы из W2. Способ формирования такой строки по группе определяется СпискомКолонок, детали рассмотрим позже.

Строится W4 = выборка из W3 по HAVING-УсловиюОтбораПослеГруппировки.
Если имеется UNION-фраза, то аналогично строится результат для Select-оператора этой фразы (кстати, в ней могут быть вложенные UNION-фразы).

Строки полученного результата добавляются к W4, поэтому результат UNION-фразы должен быть таблицей, совместимой с W4. Если UNION-фраза не имеет уточнения ALL, то добавляются только строки, хотя бы чем-то отличающиеся по значению от строк, уже имеющихся в W4.

Строки полученной в итоге таблицы W4 упорядочиваются по КлючуУпорядочения. Фраза ORDER BY может присутствовать в операторе не более одного раза, даже при наличии многих UNION-фраз.
Если указано уточнение DISTINCT, из W4 удаляются все (полные) повторения строк (остаются точно по одной из каждой группы одинаковых).
Окончательный результат оператора SELECT строится по итоговому W4 вычеркиванием лишних колонок, не объявленных в СпискеКолонок.

Выражения в SQL.

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

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

Групповые выражения строятся с помощью групповых функций.

SUM([DISTINCT] ПростоеВыражение) имеет значением сумму значений ПростогоВыражения по всем строкам группы.

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

Аналогичный смысл у групповых функций AVG (среднеарифметическое), MAX (максимальное), MIN (минимальное).
COUNT(Аргумент), где

Аргумент = * | DISTINCT ПростоеВыражение

имеет значением для Аргумент = * - количество строк в группе, а для другого вида Аргумента –количество различных значений ПростогоВыражения по всем строкам группы.

ПРИМЕР. Пусть таблица поставок PST сгруппирована по ключу группировки PST.KDET.

COUNT(DISTINCT PST.KPST) – количество разных поставщиков уже поставивших деталь KDET.

COUNT(*) – количество партий этой детали уже полученных от всех поставщиков.

SUM(PST.KOL) – общее количество в штуках детали KDET уже полученное, суммарно по всем поставщикам.

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

Условия. Выражения этого вида играют в SQL особую роль. Это логические выражения, которые используются как УсловияхОтбора (до и после группировки).

Ниже Выражение – простое или групповое выражение.

Выражение Сравнение Выражение

Набор операций Сравнения для базовых типов данных: = < > и т.п.

Выражение Сравнение SelectOne-оператор

SelectOne-оператор должен строить одно колоночную таблицу с одной строкой.

Выражение [NOT] BETWEEN Выражение AND Выражение

E1 BETWEEN E2 AND E3 в терминах языка Паскаль имеет смысл E1 IN [E2..E3], а NOT – отрицание.

Выражение [NOT] IN (Выражение, ...)

E1 IN (E2,...) в терминах языка Паскаль: E1 IN [E2,...].

Выражение [NOT] IN (SelectList-оператор)

SelectList-оператор строит одно колоночную таблицу.

Выражение Сравнение {ALL|SOME} (SelectList-оператор)

E > ALL (SelectList-оператор) имеет смысл: E строго больше всех значений из строк одно колоночной таблицы. SOME ... хотя бы одного значения...

EXISTS (Select-оператор)

Имеет смысл: результат Select-оператора непустая таблица.

Из вышеприведенных условий можно строить более сложные с помощью операций логики высказываний: AND OR NOT.

Теперь вернемся к синтаксису оператора SELECT.

СписокКолонок задается списком простых или групповых выражений, разделенных запятой. При наличии в этом списке хотя бы одного группового выражения, если фраза GROUP BY отсутствует в SELECT-операторе, то имеется в виду полная группировка – результат однострочная таблица (или пустая).

Если надо включить все колонки FROM-таблиц, то качестве элемента СпискаКолонок можно использовать [ПсевдонимТаблицы.]*

Имена колонок результирующей таблицы:

совпадает с именем поля, если оно и использовано в качестве простого выражения в СпискеКолонок,
генерируется системой,
либо может быть объявлено явно: приписать после выражения СпискаКолонок – AS Имя

КлючГруппировки задается списком (через запятую) возможно уточненных имен полей FROM-таблиц.
КлючУпорядочения задается списком (через запятую) имен полей результирующей таблицы или порядковыми номерами этих полей в СпискеКолонок. Для любой колонки можно указать невозрастающий порядок: приписать после элемента КлючаУпорядочения – DESC (по умолчанию имеется в виду неубывающий).

WHERE-УсловиеОтбора не может содержать групповых выражений (что вполне естественно, т.к. оно проверяется до группировки).
HAVING-УсловиеОтбораПослеГруппировки может быть любым выражением вида «условие».

ПРИМЕРЫ.

Решение задачи «о крупных поставках».

SELECT Psts.ImPst, Pst.Kol FROM Pst, Psts

WHERE (Psts.KPst=Pst.KPst) AND

(Pst.KDet=1010) AND (Pst.Kol>1000)

Для каждой детали - количество разных поставщиков уже поставивших эту деталь.

SELECT KDet,COUNT(DISTINCT KPst) AS KolPst

FROM Pst GROUP BY KDet

Для каждой детали - количество партий этой детали уже полученных от всех поставщиков.

SELECT MAX(Det.ImDet), Det.KDet, COUNT(*) AS KolP

FROM Det,Pst WHERE Det.KDet=Pst.KDet

GROUP BY Det.KDet ORDER BY 1

Замечание: MAX пришлось использовать по техническим причинам – InterBase «не любит неключевые поля там».

Договора, по которым есть поставки.

SELECT DISTINCT Dog.* FROM Dog,Pst

WHERE (Dog.KPst=Pst.KPst)AND(Dog.KDet=Pst.KDet)

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

Договора, по которым нет поставок.

SELECT Dog.* FROM Dog

WHERE NOT EXISTS (

SELECT Pst.* FROM Pst

WHERE (Dog.KPst=Pst.KPst)AND(Dog.KDet=Pst.KDet))

Перевыполнения по договорам.

SELECT Dog.KPst,Dog.KDet, SUM(Pst.Kol)-MAX(Dog.Kol)

FROM Dog,Pst

WHERE (Dog.KPst=Pst.KPst)AND(Dog.KDet=Pst.KDet)

GROUP BY Dog.KPst,Dog.KDet

HAVING SUM(Pst.Kol)>Dog.Kol

Замечание: опять пришлось использовать MAX по техническим причинам.

1 2 3 4 5 6

	Аис поз Установка. Обновление версии. Резервное копирование базы данных. Восстановление базы данных. 5		Windows Банк Клиент Инструкция по переходу на версию 1 Для перехода на версию 1 с предыдущих версий системы Банк-Клиент нужно сначала выполнить установку новой версии с созданием пустой...
	Финансовая разведка Тем не менее, криминальный рынок полон объявлений, предлагающих приобрести по сходной цене базы данных мвд, гибдд, сотовых операторов,...		Инструкция по обновлению фк цз Фк цз в администрирование, в авторизации набрать admin, пароль hcadmin. В главном меню выбрать «Операции» – «Резервное копирование...
	Инструкция по обновлению фк рир Фк рир в администрирование, в авторизации набрать admin, пароль hcadmin. В главном меню выбрать «Операции» – «Резервное копирование...		Пояснительная записка Студент Организация данных. Sql дает пользователю возможность изменять структуру представления данных, а также устанавливать отношения между...
	Пояснительная записка Студент Организация данных. Sql дает пользователю возможность изменять структуру представления данных, а также устанавливать отношения между...		Методические указания к курсовой работе по дисциплине Базы данных... Дисциплина «Базы данных» для направления 230700. 62 «Прикладная информатика» подготовки бакалавра
	Методические указания к практическим работам по дисциплине «Базы данных» Методические указания предназначены для проведения практических занятий по дисциплине «Базы данных», для специальности ксиК		Пер с англ. — М. Издательский Архитектура системы баз данных 65 Глава Введение в реляционные базы данных 92
	Инструкция по установке и работе с программой заполнение базы данных В настоящем документе представлена краткая инструкция пользователю по установке и работе с программой Заполнение базы данных студентов...		Лекция Язык sql 46 вставка одного запроса внутрь другого 68 как работает... Эти базы данных создаются и функционируют под управлением специальных программных комплексов, называемых системами управления базами...
	Методические указания для выполнения лабораторных работ и «Базы данных» Лабораторная работа №1 «Организация хранения данных в субд ms access»		3. Заполнение базы данных, модификация данных На предыдущем этапе... ...
	Лабораторная работа №1: Создание баз данных В этой утилите можно выполнить типовые задачи обслуживания баз данных, такие как резервирование и восстановление. Здесь можно настраивать...		Программа дисциплины «Информатика» Е дисциплины «Информатика» учебным планом отводится 200 часов, в том числе лекционные занятия – 34 часов, лабораторные работы – 72...

Базы данных. (5-й семестр вмк)

Похожие: