СУБД. Лекция 2

Введение в проектирование. Введение в SQL.

• Введение в проектирование баз данных;
• Введение в SQL (DDL и DML);
• Обзор INFORMATION_SCHEMA;

Организационные моменты

• Не забывайте отмечаться на лекциях!
• Оставляйте обратную связь по завершению лекции!

Проектирование БД

Проектирование баз данных

Процесс создания схемы базы данных и определения необходимых ограничений целостности.

Основные задачи

• Обеспечение хранения в БД всей необходимой информации.
• Обеспечение возможности получения данных по всем необходимым запросам.
• Сокращение избыточности и дублирования данных.
• Обеспечение целостности данных (правильности их содержания): исключение противоречий в содержании данных, исключение их потери и т.д.

Особенности концептуального проектирования

• Максимум командной работы;
• Привлечение наиболее опытных специалистов;
• Заглядываем в будущее.

Этапы проектирования

Внешнее представление (внешняя схема) данных

Совокупность требований со стороны конкретной функции, выполняемой пользователем.

Концептуальная схема

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

Внутренняя схема

Сама база данных.

Почему проект БД может быть плохим?

1. Избыточность
2. Потенциальная противоречивость
   (аномалии обновления)
3. Аномалии включения
4. Аномалии удаления

Пример проекта

Задача: спроектировать базу для расчета стоимости работ веб-студии

Компания N занимается разработкой полного цикла и поддержкой веб-приложений.

Стоимость часа специалиста зависит от его должности.

Стоимость работ по проекту зависит от того сколько специалистов и по сколько часов работали над данным проектом

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

Пример проекта

Формализация основных бизнес-требований

• веб-студия ведет много проектов
• над каждым проектом работает не менее одного сотрудника
• над разными проектами могут работать разные специалисты
• у сотрудников веб-студии есть должности
• от должности зависит почасовая ставка
• стоимость проекта зависит от того какие специалисты и сколько времени работали над ним

Пример сводной таблицы

1-ая нормальная форма

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

1-ая нормальная форма

Ключи или ключевые атрибуты

Ключ — атрибут или набор атрибутов, который однозначно идентифицирует кортеж в таблице в базе данных.

Первичный ключ — используется для идентификации объекта.

Потенциальный ключ — неиспользуемый, как первичный.

Составной ключ — использует несколько атрибутов.

Естественный ключ — набор атрибутов описываемой записью сущности, уникально её идентифицирующий.

Суррогатный ключ — искусственно сгенерированное поле, никак не связанное с информационным содержанием записи.

Первичный ключ

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

Первичный ключ

Составной суррогатный первичный ключ: < ProjNum, EmpNum >

Функциональная зависимость

Формальное определение:

Пусть дано отношение r со схемой R. A и B — некоторые подмножества множества R, то есть атрибутов отношения r. Множество B функционально зависит от A тогда и только тогда, когда каждое значение множества A связано в точности с одним значением множества B. Обозначается A -> B.

Менее формальное определение:

Если мы знаем, что для некоторого отношения выполняется правило: если кортежи отношения r совпадают по значению атрибута A, то они совпадают и по значению B. Тогда говорится, что B функционально зависит от A или A функционально определяет B.

Нормализация

Выделяем все функциональные зависимости:

1. ProjNum, EmpNum -> ProjName, EmpName, JobType, ChargePerHour, HoursBilled, TotalCharge
2. ProjNum -> ProjName
3. EmpNum -> EmpName, JobType, ChargePerHour
4. JobType -> ChargePerHour

2-ая нормальная форма

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

Простыми словами: не должно быть зависимости от части ключа.

1. ProjNum, EmpNum -> ProjName, EmpName, JobType, ChargePerHour, HoursBilled, TotalCharge
2. ProjNum -> ProjName
3. EmpNum -> EmpName, JobType, ChargePerHour

2-ая нормальная форма

Простыми словами: не должно быть зависимости от части ключа.

Чтобы этого добиться разбиваем отношение с частичными зависимостями на несколько отдельных:

• Project (ProjNum, ProjName)
• Employee (EmpNum, EmpName, JobType, ChargePerHour)
• Assignment (ProjNum, EmpNum, HoursBilled)

2-ая нормальная форма

Employee

3-я нормальная форма

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

Простыми словами: любой столбец таблицы должен зависеть только от ключевого столбца.

1. EmpNum -> EmpName, JobType, ChargePerHour
2. JobType -> ChargePerHour

Определения реляционной алгебры

Детерминант функциональной зависимости - набор атрибутов, который находится слева от стрелки в записи функциональной зависимости.

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

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

Транзитивность

Функциональная зависимость A -> B является транзитивной, если существует такое множество атрибутов C, что для него справедливы нетривиальные функциональные зависимости A -> C и C -> B, при этом отсутствует функциональная зависимость C -> A.

В реляционной алгебре транзитивность отличается от транзитивности в других разделах. Обязательность отсутствия зависимости C -> A не присуща другим разделам.

3-я нормальная форма

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

Чтобы этого добиться выделяем транзитивные зависимости в отдельные отношения

• Project (ProjNum, ProjName)
• Employee (EmpNum, EmpName, JobType)
• Assignment (ProjNum, EmpNum, HoursBilled)
• Job (JobType, ChargePerHour)

3-я нормальная форма

Employee

Нормальная форма Бойса — Кодда (НФБК)

Формальное определение:

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

Менее формальное определение:

Переменная отношения находится в нормальной форме Бойса — Кодда тогда и только тогда, когда детерминанты всех её функциональных зависимостей являются потенциальными ключами.

Нормальная форма Бойса — Кодда (НФБК)

Определение 3НФ не совсем подходит для следующих отношений:

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

Нормальная форма Бойса — Кодда (НФБК)

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

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

Нормальная форма Бойса — Кодда (НФБК), Пример

Отношение имеет два потенциальных ключа - {PNUM, DNUM} и {PNAME, DNUM}.

• PNUM -> PNAME - наименование поставщика зависит от номера поставщика.
• PNAME -> PNUM - номер поставщика зависит от наименования поставщика.
• {PNUM, DNUM} -> VOLUME - поставляемое количество зависит от первого ключа.
• {PNUM, DNUM} -> PNAME - наименование поставщика зависит от первого ключа.
• {PNAME, DNUM} -> VOLUME - поставляемое количество зависит от второго ключа.
• {PNAME, DNUM} -> PNUM - номер поставщика зависит от второго ключ.

Нормальная форма Бойса — Кодда (НФБК), Пример

Нормальные формы. Подытожим.

Нормальные формы. Подытожим.

Мнемоническое правило:

Каждый атрибут атомарен (1НФ) и зависит...

... от ключа (2НФ)

... только от ключа (3НФ)

... и ни от чего кроме ключа (НФБК).

Виды связей между отношениями

• один-к-одному (1:1)
• один-ко-многим (1:M)
• многие-к-одному (M:1)
• многие-ко-многим (M:N)

Типы данных PostgreSQL

• Числовые
• Строковые
• Календарные
• NULL

NULL

SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

SELECT 1 IS NULL, 1 IS NOT NULL, NULL IS NULL, NULL IS NOT NULL;

Числовые

SMALLSERIAL, SERIAL, BIGSERIAL - то же, но с автоинкрементом.

INT1 и беззнаковые есть в расширении https://github.com/petere/pguint

Бинарные

Строки

Дата и время

Время хранится с точностью до микросекунд.

P - кол-во цифр для хранения дробной части секунд.

WITH TIMEZONE - внутри базы хранится UTC.

CREATE TABLE

CREATE [ { TEMPORARY } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

Or:

CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    OF type_name [ (
  { column_name WITH OPTIONS [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE TABLE

column_constraint:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  DEFAULT default_expr |

  CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

CREATE TABLE

table_constraint:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  FOREIGN KEY ( column_name [, ... ] )
    REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] )
    index_parameters [ WHERE ( predicate ) ] |
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Обновление кортежа в родительском отношении (action)

NO ACTION

Не разрешать изменение

RESTRICT

То же, что и NO ACTION, но всегда IMMEDIATE

CASCADE

Изменить каскадно

SET NULL

Установить в NULL

SET DEFAULT

Установить значение по умолчанию

CREATE TABLE (пример)

CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    imdb        varchar(20) NULL,
    CONSTRAINT uniq_imdb UNIQUE(imdb)
);

CREATE TABLE distributors (
     did    integer PRIMARY KEY DEFAULT nextval('serial'),
     name   varchar(40) NOT NULL CHECK (name <> '')
);

ALTER TABLE

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name

action:

ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ ... ]
DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ USING expression ]
ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER [ COLUMN ] column_name DROP DEFAULT
ADD table_constraint [ NOT VALID ]
DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
...

ALTER TABLE (пример)

ALTER TABLE distributors
    ADD COLUMN address varchar(30),
    DROP COLUMN address RESTRICT,
    ALTER COLUMN address TYPE varchar(80),
    ALTER COLUMN name TYPE varchar(100);

ALTER TABLE foo
    ALTER COLUMN foo_timestamp DROP DEFAULT,
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch'
        + foo_timestamp * interval '1 second',
    ALTER COLUMN foo_timestamp SET DEFAULT now();

INSERT

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

conflict_target:

( { index_column_name | ( index_expression ) } ... [, ...] )
[ WHERE index_predicate ]
ON CONSTRAINT constraint_name

conflict_target:

DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
                ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
                ( column_name [, ...] ) = ( sub-SELECT )
              } [, ...]
          [ WHERE condition ]

INSERT

INSERT INTO films (code, title, did, date_prod, kind) VALUES
('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy')
RETURNING *;

INSERT INTO films
SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';

INSERT INTO distributors (id, dname)
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
ON CONFLICT (id) DO UPDATE SET dname = EXCLUDED.dname;

INSERT INTO distributors (id, dname) VALUES (7, 'Redline GmbH')
ON CONFLICT (id) DO NOTHING;

UPDATE

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

UPDATE

UPDATE table1
SET
    col_name1 = col_name2,
    col_name2 = col_name1
WHERE id = 42;

UPDATE weather
  SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
  WHERE city = 'San Francisco' AND date = '2003-07-03'
  RETURNING temp_lo, temp_hi, prcp;

UPDATE weather
  SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
  WHERE city = 'San Francisco' AND date = '2003-07-03'
  RETURNING temp_lo, temp_hi, prcp;

UPDATE

UPDATE accounts SET
  contact_first_name = (
    SELECT first_name FROM salesmen WHERE salesmen.id = accounts.sales_id
  ),
  contact_last_name = (
    SELECT last_name  FROM salesmen WHERE salesmen.id = accounts.sales_id
  );

UPDATE accounts SET (contact_first_name, contact_last_name) =
    (SELECT first_name, last_name FROM salesmen
     WHERE salesmen.id = accounts.sales_id);

UPDATE accounts SET contact_first_name = first_name,
                    contact_last_name = last_name
  FROM salesmen WHERE salesmen.id = accounts.sales_id;

DELETE

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING using_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

DELETE FROM films;

TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
    [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

TRUNCATE TABLE films;

DELETE

DELETE FROM films WHERE kind <> 'Musical';

DELETE FROM tasks WHERE status = 'DONE' RETURNING *;

DELETE FROM films
  WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');

DELETE FROM films USING producers
  WHERE producer_id = producers.id AND producers.name = 'foo';