Базы данных

Изменение данных

Операторы

Содержание

Типы изменений

  • Операции БД
    • Вставка (insert)
    • Выборка (select)
    • Обновление (update)
    • Удаление (delete)
  • Обобщение
    • Создание (create)
    • Чтение (read)
    • Обновление (update)
    • Удаление (delete)

Вставка

  • Синтаксис
    • insert into таблица [(атрибуты)]
      values (значения) [, ещё строки]
      
    • insert into таблица [(атрибуты)]
      запрос
      
  • Примеры
    • insert into Courses (Name, Lecturer) values
      ('БД', 'Корнеев Г.'),
      ('ТеорИнф', 'Трифонов П.')
      
    • insert into Points
      select SId, CId, 0 from Students, Courses
      

Обновление

  • Синтаксис
    • update таблица set значения [where условие]
      
  • Значение
    • атрибут = выражение
      
  • Примеры
    • update Students set GroupId = 'M3437'
      where LastName = 'Иванов'
      
    • update Students set
      PassNo = '1234', PassSeries = '567890'
      where SId = 1
      
    • update Points set points = points + 1 where CId in
      (select CId from Courses where Name = 'СУБД')
      

Удаление

  • Синтаксис
    • delete from таблица [where условие]
      
  • Примеры
    • delete from Points where CId
      in (select CId from Courses where Name = 'СУБД')
      
  • Truncate
    • Синтаксис
      truncate таблица -- DDL
      
    • Пример
      truncate Points
      

Слияние

  • Синтаксис
    • merge into таблица [[as] имя1]
      using данные [[as] имя2]
      on условие
      when matched [and ...] then действие1
      when not matched [and ...] then действие2
      
  • Пример обновления данных
    • merge into Points
      using (select * from Courses c, Students s) r
      on Points.CId = r.CId and Points.SId = r.SId
      when matched then update set points = points + 1
      when not matched then insert (SId, CId, points)
          values (r.SId, r.CId, 1)
      

Корректность и целостность (1)

  • Корректность
    • Соответствие содержимого БД «реальному миру»
    • Не может быть проверена
  • Целостность
    • Непротиворечивость содержимого БД
    • Может быть описана набором правил и проверена

Корректность и целостность (2)

  • Корректность
    • Оценка каждого студента соответствует полученной на экзамене
    • Студент слушает курс баз данных
  • Целостность
    • У студента есть оценки только по предметам, которые слушала его группа
    • Студент посетил столько занятий курса БД, сколько было проведено

Проверка целостности

  • Когда
    • По завершении оператора
    • По завершении транзакции (отложенная)
  • При неудаче
    • Все изменения отменяются

Ограничение типа

  • Множество значений, допустимых для типа
  • Примеры
    • Дата
      Date
      
    • Имя
      create type Name as object (
          FirstName varchar(100),
          LastName varchar(100)
      )
      
    • Баллы
      create type Points as object
      (points as int between 0 and 100)
      

Ограничение атрибута

  • Множество значений, допустимых для атрибута
    • Является следствием его типа
  • Примеры
    • Дата:
      Enroll Date not null
      
    • Имя:
      SName Name
      
    • Баллы:
      CoursePoints Points
      

Ограничение отношения

  • Множество значений
    • Конъюнкция предикатов
  • Типы предикатов
    • Ключи
    • Внешние ключи
    • Проверяемые условия
  • Примеры
    • primary key (SId, CId)
      
    • unique (PassNo, PassSeries)
      
    • foreign key (SId) references Students(SId)
      
    • check (Points between 0 and 100)
      

Проверяемые условия

  • Проверяются для каждого кортежа
    • Неявный ∀
    • ⇒ не проверяются для пустых отношений
    • Оптимизация в реализациях
  • Примеры
    • SId in (select SId from Students)
      
    • ExpulsionDate is not null or
      not exists (select * from Points p
      where p.SId = SId and p.CId = CId and Point < 60)
      

Ограничение базы данных

  • Множество значений, которые может принимать база данных
    • Предикаты таблиц
    • Предикаты утверждений
  • Предикаты утверждений
    • Проверяются и для пустых отношений
    • create assertion NonEmptyGroup check (not exists
          (select * from Group g where not exists
              (select * from Students s where s.GId = g.GId)))
      

Компенсирующие действия

  • Удаление курса
  • Что делать с оценками по нему?
    • Удалить все оценки по курсу
    • Запретить удаление курса
  • В общем случае
    • Каскадирование изменений
    • Ограничение изменений

Типы компенсирующих действий

  • Опасные действия
    • Удаление (delete)
    • Обновление (update)
  • Производимый эффект
    • Бездействие (no action)
    • Каскадирование (cascade)
    • Ограничение (restrict)
    • Обнуление (set null)
    • Установка значения по умолчанию (set default)

Объявление компенсир. действий

  • Синтаксис
    • foreign key (столбцы)
      references таблица [(столбцы)]
      [on delete действие]
      [on update действие]
      
  • Примеры
    • foreign key (CId) references Courses
      on delete cascade on update cascade
    • foreign key (SId) references Students
      on delete restrict on update cascade
      

Триггеры

Содержание

Триггеры

  • Действия, выполняемые при изменении данных
    • Целостность данных
    • Любые действия
  • Применение триггеров
    • Уведомление пользователей
    • Обновления денормализованных данных
    • Аудит изменений
    • Компенсирующие действия

Объявление триггеров

  • Синтаксис
    • create trigger имя on объект
      {before | after | instead of} {insert | update | delete}
      [объявление ссылок]
      [for each {row | statement}]
      действие
      
  • Объявление ссылок
    • referencing {new|old} {row|table} имя
      

Пример применения триггера

  • Каскадирование изменений
    • create trigger CascadeCourses
      on Courses after delete
      referencing old row c for each row
      delete from points p where p.CId = c.CId
      
  • Аудит изменений
    • create trigger AuditPoints
      on Points after insert, update
      referencing new row p
      for each row set p.UpdateTime = current_timestamp()
      

Представление

  • Именованный запрос
  • Применение представлений
    • Макросы
    • Сокрытие данных
    • Независимость от данных

Объявление представлений

  • Синтаксис
    • create view имя(столбцы) as запрос
      
    • drop view имя
      
  • Примеры
    • create view AveragePoints(SId, AvgPoints) as
      select SId, avg(Points) from Points
      
    • create view StudentCourse(FN, LN, N, P) as
      select s.FirstName, s.LastName, c.Name, p.Points
      from Students s natural join Points p natural join
      Courses c
      

Обновление представлений

  • Зачем обновлять представления?
    • Для обеспечения независимости от данных
    • Для сокрытия данных
  • Действия при обновлении
    • Как если бы представление было таблицей
    • Взаимообратность вставки и удаления
  • Обновление
    • Удаление (без проверки целостности)
    • Вставка

Унарные операции

  • $\sigma_P(R)$
    • Кортеж должен удовлетворять $P$
    • Вставить кортеж в $R$
    • Удалить кортеж из $R$
  • $\pi_A(R)$
    • Вставить кортеж в $R$ со значениями по умолчанию
    • Удалить все соответствующие кортежи из $R$
  • $\rho_{a=b}(R)$
    • Вставить кортеж в $R$
    • Удалить кортеж из $R$

Множественные операции

  • $R_1 \cup R_2$
    • Вставка
      • Если кортеж удовлетворяет предикату $R_1$ ⇒ вставить в $R_1$
      • Если кортеж удовлетворяет предикату $R_2$ ⇒ вставить в $R_2$
    • Удалить из $R_1$ и $R_2$
  • $R_1 \cap R_2$
    • Вставить в $R_1$ и $R_2$
    • Удалить из $R_1$ и $R_2$
  • $R_1 - R_2$
    • Вставить в $R_1$
    • Удалить из $R_1$

Соединения

  • $R_1 ⋈ R_2$
    • Вставить в $R_1$ соответствующую часть кортежа Вставить в $R_2$ соответствующую часть кортежа
    • Удалить из $R_1$ соответствующую часть кортежа Удалить из $R_2$ соответствующую часть кортежа
  • Базируется на ограничениях целостности
    • Один-к-одному
    • Один-ко-многим
    • Многие-ко-многим

Обновление и SQL

  • Унарные операции
    • Обновляемые
  • Множественные операции
    • Необновляемые
  • Соединения
    • Один-к-одному – обновляемые
    • Один-ко-многим – обновляемые «многие»
    • Многие-ко-многим – необновляемые

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

  • «Слепок» данных на определенный момент времени
    • Обычно хранятся физически
    • Виртуальное удаление
  • Преимущества
    • Быстрота выборки
    • Возможность «фиксации»
  • Недостатки
    • Необходимость обновления
    • Устаревание данных
    • Занимают место

Объявление матер. представлений

  • Синтаксис
    • create materialized view имя
      [
          refresh [{fast|complete}] [on {demand|commit}]
          [start with время] [next время]
      ]
      as запрос
      
    • refresh materialized view имя
      
  • Пример
    • create materialized view AveragePoints
      refresh next dateadd(day, current_timestamp(), 1)
      as select SId, avg(Points) from Points group by SId
      

Схемы управления доступом

  • Избирательная (дискреционная)
    • Списки доступа
  • Мандатная
    • Классификационный уровень

Дискреционная схема

  • Объекты
    • Права на них
    • Отдельные права никак не связаны
  • Пользователи
    • Права пользователей
  • Группы пользователей
    • Права групп

Мандатная схема

  • Классификационные уровни
    • «Матрешка»
    • Уровень пользователя
    • Уровень ресурса
  • Чтение
    • Уровень ресурса < уровня пользователя
  • Изменение
    • Уровень ресурса = уровню пользователя
  • Добавление (не всегда)
    • Уровень ресурса > уровня пользователя

Управление пользователями

  • Синтаксис (не SQL)
    • create user имя [password 'пароль']
      
    • alter user имя [password 'пароль']
      
    • drop user имя
      
  • Примеры
    • create user kgeorgiy;
      
    • alter user kgeorgiy password '***';
      
    • drop user kgeorgiy;
      

Управление группами

  • Синтаксис (не SQL)
    • create group имя
      
    • alter group имя {add|drop} user пользователь
      
    • drop group имя
      
  • Примеры
    • create group lecturers;
      
    • alter group lecturers add user kgeorgiy;
      
    • drop group lecturers;
      

Добавление прав

  • Синтаксис
    • grant {select|insert|update|delete|create
               |execute|trigger|usage|…|all privileges}
      on {table|database|view|procedure|function|…} имя
      to {пользователь|group группа|public}
      [with grant option]
      
  • Примеры
    • grant all privileges on Students
        to group Deans with grant option;
      
    • grant select on Students to public;
      

Удаление прав

  • Синтаксис
    • revoke [grant option for]
      {select|insert|update|delete|create
         |execute|trigger|usage|…|all privileges}
      on {table|database|view|procedure|function|…} имя
      from {пользователь|group группа|public}
      [cascade|restrict]
      
  • Примеры
    • revoke insert on Students
          from group Deans cascade;
      
    • revoke update on Students from public;
      

Владелец объекта

  • Имеет все права на объект
    • Создатель
    • Не может ограничить свои права
  • Изменение владельца
    • alter {table|schema|database|…}
      owner to пользователь
      

Фильтрованная таблица

  • Представление, содержащее не все кортежи
    • Может быть изменяемым
  • Пример
    • create view FITPStudents as
      select * from Students
      where GId in
      (select GId from Group where FId = 'ИТиП')
      
    • grant all privileges on FITPStudents to FITPDean;
      

Спроецированная таблица

  • Представление, содержащее не все атрибуты
    • Может быть изменяемым
  • Пример
    • create view PublicStudents as
      select SId, FirstName, LastName
         /* No passport info, etc */
      from Students
      
    • grant select on PublicStudents to public;
      

Агрегированная таблица

  • Представление, содержащее агрегированные данные
    • Подвержено дифференциальным атакам
  • Пример
    • create view StudentPoints as
      select SId, avg(points)
      from Students natural join Marks
      
    • grant select on StudentPoints to public;
      

Литература

Содержание

Основная литература