Введение в базы данных

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

Операторы

Содержание

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

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

Вставка

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

Обновление

  • Синтаксис
    • update таблица set значения [where условие]
      
  • Значение
    • атрибут = выражение
      
  • Примеры
    • update Students set GroupId = '4537'
      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 условие]
      
    • truncate таблица
      
  • Примеры
    • delete from Points where CId =
      in (select CId from Courses where Name = 'СУБД')
      
    • truncate Points
      

Слияние

  • Синтаксис
    • merge into таблица using данные on условие
      when matched then действие1
      when not matched then действие2
      
  • Пример обновления данных
    • merge into Points p
      using (select * from Courses c, Students s) r
      on p.CId = r.CId and p.SId = r.sId
      when matched then update set points = points + 1
      when not matched then insert (SId, CId, p) 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 not null or
      not exists (select * from Points p
      where p.SId = SId, 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 on Course after delete
      referencing old row c for each row
      delete from points p where p.CId = c.CId
      
  • Аудит изменений
    • create trigger on Points after insert, update
      referencing new row p
      for each row set p.time = now()
      

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

Содержание

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

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

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

  • Синтаксис
    • create view имя as запрос
      
    • drop view имя
      
  • Примеры
    • create view AveragePoints as
      select SId, avg(Points) from Points
      
    • create view StudentCourse 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 запрос
      
  • Пример
    • create materialized view AveragePoints
      refresh next dateadd(day, now(), 1)
      as select SId, avg(Points) from Points
      

Управление доступом

Содержание

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

Содержание

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

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

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

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

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

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

Пользователи и группы

Содержание

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

  • Синтаксис (не 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;
      

Data Control Language

Содержание

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

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

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

  • Синтаксис
    • revoke [grant option for]
      {select|insert|update|delete|create
         |execute|trigger|usage|…|all priviledges}
      on {table|database|view|procedure|function|…} имя
      from {пользователь|group группа|public}
      [cascade|restrict]
      
  • Примеры
    • revoke insert on Students from group Deans;
      
    • 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 = 'FITP')
      
    • grant all priviledges on FITPStudents to FITPDean;
      

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

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

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

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

Литература

Содержание

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

  • Дейт К. Введение в системы баз данных (главы 9, 10, 17)
  • Уидом Д., Ульман Д. Основы реляционных баз данных (глава 6)