Базы данных как аппарат моделирования.

Курс за третий семестр.

 

Введение в СУБД.

Базы данных как аппарат моделирования.

состояние(объект)®статика

процедура®динамика объекта

тип данных

 

Базы данных

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

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

Люди Дома

фамилия: string(30) город: string(30)

возраст: 1..150 улица: string(30)

номер: integer

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

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

Замечание. Как всегда в программировании СУБД мы имеем дело с именованными аналогами классических понятий с явной и фиксированной системой обозначений.

Классический язык описания предикатов отношений – язык математической логики. Популярен более бедный, но более наглядный язык диаграмм – язык «сущность-связь», или язык ER-диаграмм (Entity Relation – сущность-связь).

	Люди		Дом

живёт

связь

Сущность – люди.

Имя связи – живёт.

Живёт (предикат): люди´дома®Boolean

Выделение на основе экспертной информации сущностей и их атрибутов, а также связей отношений между ними составляет первичную логическую основу, структуру БД. Адекватной задачей предметной области и оптимальной в смысле компактности хранения и скорости доступа к данным структуры БД называется её проектированием. Стандартная физическая реализация БД – файлы, то есть медленные наборы данных большой ёмкости на внешних, чаще всего механических, носителях. Связь БД с такими носителями во многом определяет специфику их эволюции.

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

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

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

 

Классификация бинарных отношений

RÍX´Y

!) R – отношение «один ко многим», если

"xÎX, "y,y^| ÎR (<x,y>ÎR, <x,y^|>ÎR ® y=y^|)

В этом случае таблица y называется родительской, а x – дочерней.

2) R – отношение «один ко многим», если оно отношение «один ко многим», но наоборот: для каждого y существует не более одного соответствующего ему x.

"yÎY, "x,x^|ÎR (<x,y>ÎR, <x^|,y>ÎR ® x=x^| )

Все оставшиеся отношения называются «многие ко многим». Наиболее популярным видом отношений СУБД является «один к одному».

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

Замечание: Как правило. В приведённых определениях не требовалась всюду определённость функций. Отношение «быть подмножеством» - пример отношения «один к одному». В СУБД отношения используют также в тех случаях, когда лишь наибольшая часть записей одной таблицы взаимно-однозначно соответствует другой. Более точная количественная классификация отношений предполагает подсчёт количества записей, приходящееся на связанные с ними записи. В силу вычислительной сложности он (количественный анализ) используется редко.

 

Реляционные базы данных

Как реализовать БД?

Таблицы БД – с ними проблем нет – реализуются файлами записей (file of record).

Проблема: как реализовать отношения?

Кажется, совершенно естественно реализовать отношения программно, снабдив СУБД механизмом навигации – внутренними алгоритмами прохода по связи. Например, от родителей к детям. Именно такой навигационный подход использовался в ранних иерархических БД (название происходит от топологии (вида) отношений). В иерархических БД реализуются древовидные отношения, в них каждый потомок имеет одного предка. В сетевой топологии разрешены произвольные отношения, но не разрешены отношения «многие ко многим», но разрешено множественное наследование. Преимуществом навигационного подхода является высокая скорость при выполнении запроса, недостатком – узкий круг разрешённых запросов.

Очевидно, что кроме выделенных, БД всегда содержат информацию о множестве других отношений.

Пример: отношение однофамильцев или быть старше в примере с жителями.

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

Идея реализации отношений традиционных БД проста и изящна: заменить рассмотрения отношений между таблицами простыми отношениями между значениями скалярных типов.

Что может быть проще равенства?

<r₁,r₂>ÎR E(r₁)=E(r₂)

Пусть PK – некоторое выражение над полями таблицы PT. Назовём это выражение первичным ключом (primary key), если оно различает каждую пару записей PT.

"r₁,r₂ÎPT PK(r₁)¹PK(r₂) (r₁¹r₂)

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

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

Пусть FK – также некоторое выражение над другой таблицей CT. Говорят, что FK – внешний ключ (foreign key), если все его значения присутствуют среди значений PK.

В случае, когда FK(r₂)=PK(r₁), r₂ÎCT, r₁ÎPT, говорят, что запись r₂ ссылается на запись r₁.

Итак, для реализации отношения «один ко многим» достаточно найти PK и FK такие, что PK(r₁)=FK(r₂).

Откуда берутся ключевые выражения?

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

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

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

 

Нормализация баз данных

Теоретическим фундаментом такой стратегии является понятие нормальных форм таблиц и понимание процесса проектирования как последовательного приведения к нормальной форме. Считается, что на практике достаточно привести к трём нормальным формам.

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

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

Считаем, что поле Y зависит от поля X, если таблица не содержит пары записей с одним значением поля X, но разными значениями поля Y.

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

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

Проиллюстрируем процесс нормализации на примере проектирования БД сделки.

 

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

покупатель фамилия (паспорт)

заказ стоимость

товар стоимость

 

Продавец/заказ: исполняет

Покупатель/заказ: делает

Заказ/товар: состоит

 

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

 

Покупатель/заказ

Продавец/заказ

 

покупатель продавец

 

 

заказ

 

 

заказ/товар ® состоит

 

покупатель – customer

продавец – employee

заказ – order

OrderId	Date	Prod_Id1	Amount	Price1	Prod_Id2

 

Такая структура невозможна при переменном количестве товаров в заказе. Но даже если известно максимальное количество, такая структура явно неэффективна в плане компактности хранения. А формально она противоречит первой нормальной форме.

Замечание. Нормальные формы – лишь ориентир при проектировании. На практике реально запрещается фиксировать в структуре таблицы отношения с переменной кардинальностью.

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

 

OrderId

ODate

Состоит

OrderId

ProdId

Amount

Price

 

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

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

 

N^o

OrderId

ProdId

Amount

Price

 

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

 

Структура SQL

 

Update Restricted

 

Create Index имя список полей

У него нет семантики

В рамках реляционного подхода таблица есть отношения, то есть множество, а в понятии множества порядок перечисления элементов отсутствует, но такой порядок исключительно важен в реализации. Все быстрые алгоритмы поиска (доступа) основаны на некотором упорядочивании компонент файла. Проблема: доступ к разным значениям нуждается в разном порядке компонент. Решение: хранить нужные порядки в разных файлах логически, то есть хранить не сами записи, а ссылки на них. Любая реализация SQL сама создаёт множество индексных файлов, содержащих такие ссылки. В частности, создаются индексы для значений ключевых полей.

 

Create table Customer (ID integer not null primary key, name char(20), city char(20), credit decimal not null check(credit>=0), birthday date check(year(birthday)>1900))

 

Create table Employee (ID integer not null primary key, name char(20), city char(20), comm decimal not null default 0, birthday date)

/*comm – доля выручки с заказа*/

 

Create table Orders (ID integer not null primary key, Cust_Ref integer not null references Customer(ID) //*//, Emp_Ref integer references Employee(ID) //*//, startdate not null, finishdate not null check(startdate<finishdate)

* - имеется первичный ключ по умолчанию

delete of Customer cascades

delete of Employee nulls

update of Customer cascades

 

Create table Item (Order_Ref integer not null, Product_Ref integer not null, amount integer not null default 1, unique(Order_Id, Product_Ref), foreign key (Product_Ref) references Product(ID)

/*amount – количество единиц товара*/

 

Create table Product (ID integer not null primary key, name char(30), price decimal not null check(price>0), type char(10) not null)

 

Команды DML

Добавление insert into (имя таблицы) [(список полей)] values (список значений)

Удаление

Изменение

Пример. insert into Customer (name) values (‘Иванов’)

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

insert into (имя таблицы) (команда select). В указанную таблицу добавляется множество записей, выбранных командой select. Предполагается, что результат выборки согласован по типу с полями таблицы.

delete – удаление записей из таблицы.

update – изменение значений полей.

select – выборка данных.

delete from (имя таблицы) where (условия удаления) – удалить все записи из таблицы, удовлетворяющие данному условию (условиям).

delete from Customer where credit=0.

Замечание. В реальности команда delete, как правило, не удаляет записи физически, а лишь помечает их как удалённые.

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

update Customer set Credit=Credit+100 where city=’Казань’

Как правило, СУБД включают в себя средства восстановления удалённых записей и, собственно, физического удаления помеченных записей.

select [ All/Distinct ] (список выборки) from (список таблиц) [where (условия фильтрации)] [order by (список имён полей или номеров выражений из списка выборки)] [group by (список имён полей, либо номеров выражений из списка выборки)] [having (условия на группу)] [union (другая команда select)]

select Name, Year(date(…))-year(birthday) from Customer

Результат выборки – таблица.

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

Опция as используется для задания пользовательских имён результирующей таблицы. Типы полей определяются по типу выражения. Размерность – тёмное место языка.

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

where city=’Казань’

 

Предикаты в SQL

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

Предикат сравнения по маске: (аргумент) [ not ] like (маска), где маска – произвольная символьная строка, содержащая специальный символ % (символ кратной замены) и _ (символ одиночной замены). Предикат считается истинным, если маску можно превратить в аргумент при подходящей замене знака подчёркивания (_) на одиночный символ и знака % на слово.

where city=’Казань’ and name=’Иван%’

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

(аргумент 1) [ not ] between (аргумент 2) and (аргумент 3)

Предикат истинен, если аргумент1 принадлежит замкнутому интервалу [аргумент 2, аргумент 3] (отрезок).

(аргумент) [ not ] in (список значений) – предикат истинен, если аргумент совпадает с одним из значений списка.

City between ‘Казань’ and ’Москва’

Смотри далее: «Предикаты, использующие выборку».

Опции GROUP BY и HAVING

Опции ORDER BY и UNION

Order by (список полей) [Asc, Desc]

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

Asc ending – по возрастанию (по умолчанию).

Desc ending – по убыванию.

Опция All/Distinct: выбор distinct удаляет из результата выборки полностью совпадающие записи. По умолчанию действует опция all, то есть такое удаление не производится.

Опция Union – объединение выборок.

(команда select) union [all] (команда select)

Обычно интерпретируется как подопция команды select. Легче интерпретировать как операцию над двумя выборками, а именно – объединения двух таблиц, то есть именованных отношений.

Для того, чтобы гарантировать, что результат объединения снова будет таблицей, типы таблиц должны быть согласованы по числу и типу полей (не по именам). Число и типы полей должны совпадать. Размерность – снова тёмное место языка.

Довольно неожиданно применение union убирает по умолчанию все записи-дубликаты из результата. Чтобы избежать этого, используется опция [ all ]. В отличие от этого, в варианте select all/distinct, оставляющем в единичной выборке либо все записи, включая дубликаты (опция [ all ]), либо только различные (опция [ distinct ]). По умолчанию действует опция [ all ].

Замечание. Union запрещается использовать в подзапросах. При использовании опции union, опция order by может использоваться лишь однократно после последней выборки. В этом случае упорядочивается не последняя выборка, а всё объединение.

Пример. Вывести фамилии покупателей и продавцов в лексикографическом порядке.

select name from Customer

Union

select name from Employee

order by 1;

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

Многие версии языка включают опцию [ into ] (преемник выборки), позволяющую сохранять результат либо в реальной таблице (в файле), либо в курсоре (CurSOR).

CurSOR – Current Set Of Record – текущее множество записей во временном файле, доступном в текущем сеансе работы, но исчезающим по завершении работы.

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

 

Вложенные подзапросы

Предикаты – условия на запись, - используемые в опции where (как в командах модификации, так и в самой команде select), в свою очередь могут содержать ссылку на выборку. К таким предикатам относятся:

1) r c all (команда select), где r – выражение над записью, c – знак сравнения.

Предикат истинен, если сравнение выполняется для значения выражения r и всех значений, выбранных командой select. При этом предполагается, что select выдаёт некий список значений list, то есть, формально, таблицу с единственным полем, совместимым по типу с типом выражения r.

» " lÎ list (r c l)

Если выборка пуста, значение предиката true.

2) r c [ any ½ some ] (команда select)

При тех же условиях на select эквивалентно предикату:

$ lÎ list (r c l)

3) r [ not ] in (команда select)

При тех же условиях на select: rÎ list.

4) [ not ] exists (команда select)

В данном случае на select не накладывается никаких ограничений. Предикат истинен, если выборка не пуста: $ r: r Î select

Пример. Молодые покупатели_1.

select name, Id

from Customer

where birthday>(select avg (birthday) from Employee);

 

Молодые покупатели_2.

select name, Id

from Customer

where birthday>(select avg (birthday) from Customer);

Коллизии имён нет, по умолчанию каждое имя поля ссылается на таблицы из ближайшего from. Синтаксис SQL не запрещает именам полей внутренней выборки ссылаться на таблицы внешней выборки. Такие подзапросы называются соотнесёнными.

Молодые покупатели_3.

select name, Id

from Customer, Cust1

where birthday>(select avg (birthday) from Customer, Cust2

where Cust1.city=Cust2.city);

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

Создание представлений

Create view (пользовательское имя представления) as (команда select)

[ with check option ] – с опцией проверки

Также популярны опции local (локальный, ближний), remote (удалённый).

В первом случае соответствующая команда select ссылается только на данные клиента. Удалённое представление ссылается на данные сервера.

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

Представления считаются частью определения БД и хранятся вместе с физическими, или базовыми, таблицами. В реальности, конечно, хранится не виртуальная таблица, но её определение, то есть текст команды select. Основное назначение представления – позволить программисту определять сложные иерархии понятий, базирующиеся на основной реляционной модели, ориентированные на различные группы пользователей.

create view Young_Customers as select (что-то)

create view Young_Kazan_Customers

as select * from Young_Customers

where city=’Казань’;

Customer®Young _Customers®Young_Kazan_Customers

Процесс создания представлений обратен процессу проектирования БД.

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

 

With check option

insert into Казанские_покупатели1 (Id, name) values (5, ‘Сидоров’);

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

Клиент Сервер

Копия структуры

 

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

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

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

 

Транзакции

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

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

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

 

 

Select-запрос

Результаты выборки

Модификации

Примеры.

1) Один клиент читает данные в момент модификации таблиц другим клиентом.

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

 

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

 

 

Обе операции могут осуществляться параллельно, или асинхронно, но важно, чтобы они выполнялись полностью, либо не выполнялись вообще.

Характеристическими свойствами транзакции считают свойство ACID (Atomicity, Consistency, Isolation, Durability) – атомарность, непротиворечивость, изолированность и стойкость (жизнеспособность). Под атомарностью понимают принцип «Всё или ничего» (не навреди). Все вовлечённые в транзакции изменения либо происходят целиком, либо не происходят вообще. Непротиворечивость означает, что изменения в любом случае приводят к корректному состоянию. Под изолированностью понимают то, что транзакции могут быть вложенными, но не пересекаться. Жизнеспособность означает, что подтверждённые результаты транзакции не могут быть отлажены никаким внешними воздействиями.

Это определение требует коренного пересмотра базовых для нас понятий:

1) Понятия состояний: состояние программы теперь не определяет однозначно результат;

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

F(S) – процедура на состояние S.

F(S)=F^½(S,X), где Х – скрытый фактор внешней среды.

$X: F(S)¹F^½(S,X)

(S,X) – состояние вычислительной среды, однозначно определяющее результат.

F(S), если F(S)=F^½(S,X)

Transaction(F,S,X)=

S, в противном случае

 

В SQL транзакция оформляется с помощью команд-скобок:

begin [transaction]

команды SQL (в том числе вложенные транзакции)

end ½ commit [transaction]

Если по каким-либо причинам выполнение команд транзакции невозможно, происходит автоматический откат в состояние на момент её начала. Такой же откат может быть произведён явно с помощью команды RollBack [transaction].

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

Примеры транзакций

База данных. В контексте ситуации люди описываются следующими атрибутами: фамилия (name), паспорт (Id), паспорт матери (mum), паспорт отца (dad), день рождения (birthday), доход (income), город (city), налог (tax, %). Кроме того, в информацию о студентах дополнительно включаются сведения: institute, sex {‘м’,’ж’}.

tax, % 0..100

name char(30)

Id numeric

mum numeric

dad numeric

birthday date

income currency

city char(30)

 

БД включает в себя три таблицы: студенты (Students), отцы (Dads) и матери (Mums).

Задача 1: снизить на 5% налог с родителей, имеющих более трёх детей-студентов, обучающихся в Казани.

Вариант решения:

1) Отобрать в таблице Students ссылки на тех родителей, у которых больше трёх детей-студентов из Казани;

2) Изменить записи об отобранных мамах;

3) Изменить записи об отобранных отцах.

select mum, dad

from Students

group by mum, dad

having count (*)>=3

where city=’Казань’

into cursor temp;

Задача 2: Повысить на 200 рублей стипендию студентов (доход), у которых нет хотя бы одного из родителей, либо совокупный доход родителей не превышает 1529 рублей.

Вариант решения:

1) Подсчитать доход всех родителей и запомнить тех, у которых этот доход не превышает заданной суммы;

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

create view Parents as

select distinct mum, dad

from Students, Mums, Dads

where (Mums. income+ Dads. income<=1529) and (Students. mum= Mums. Id)

and (Students. dad= Dads. Id)

update Students set income=income+200

where (is null (mum) or is null (dad)) and mum in (select mum from Parents)

and dad in (select dad from Parents));

Замечание. Согласно стандарту ANSI в опции where команд модификации запрещается делать запросы к модифицируемым таблицам.

Задача 3: Удалить информацию о студентах данного ВУЗа.

Вариант решения:

1) Запомнить студентов нужного ВУЗа вместе со ссылками на отцов;

2) Запомнить отцов, у которых все дети (если есть) учатся в одном ВУЗе;

3) Удалить информацию об отцах, входящую в первый и второй списки;

4) Запомнить студентов нужного ВУЗа вместе со ссылками на матерей;

5) Запомнить матерей, у которых все дети (если есть) учатся в одном ВУЗе;

6) Удалить информацию о матерях, входящую в первый и второй списки;

7) Удалить информацию о студентах данного ВУЗа.

create view КХТИ1 as

select Id, dad from Students

where institute=’КХТИ’

 

create view Dad1 as

select Id from Dads

where not exists (select Id from Students where institute<>’КХТИ’

and Dads. Id= Students. dad)

delete from Dads

where Id in (select dad from КХТИ1) and Id in (select Id from Dad1)

 

create view КХТИ2 as

select Id, mum from Students

where institute=’КХТИ’

create view Mum1 as

select Id from Mums

where not exists (select Id from Students where institute<>’КХТИ’

and Mums. Id= Students. mum)

delete from Mums

where Id in (select mum from КХТИ2) and Id in (select Id from Mum1)

 

delete from Students

where institute=’КХТИ’;

 

Лекции читал: Бухараев Н.Р.

Набрал: Иванов Нияз (гр. 9109).

Курс за третий семестр.

 

Введение в СУБД.

Базы данных как аппарат моделирования.

состояние(объект)®статика

процедура®динамика объекта

тип данных

 

Базы данных