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

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

• Введение в проектирование баз данных;
• Введение в SQL;
• Немного про JOIN-ы;
• Обзор INFORMATION_SCHEMA;
• Иерархические структуры в базах данных.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Инфологическое проектирование

Концептуальное (инфологическое) проектирование

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

• сущности
• атрибуты
• связи

Логическое проектирование

Логическое (даталогическое) проектирование

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

• записи
• элементы данных
• связи между записями

Физическое проектирование

Физическое проектирование

Создание схемы базы данных для конкретной СУБД.

• группирование данных
• индексы
• методы доступа

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

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

Концептуальное проектирование

Выявление сущностей и связей

Структура таблицы тем

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

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

Справочник пользователей

Нормальная форма

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Реляционная часть

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

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();

information_schema

• attributes
• character_sets
• collations
• columns
• domain_constraints
• domains
• referential_constraints
• role_column_grants
• role_routine_grants
• role_table_grants
• role_usage_grants
• routines
• sequences
• sql_features
• sql_implementation_info
• table_constraints
• table_privileges
• tables
• triggers
• view_column_usage
• view_routine_usage
• views
• view_table_usage
• ...

information_schema.tables

• table_catalog
• table_schema
• table_name
• table_type
• self_referencing_column_name
• reference_generation
• user_defined_type_catalog
• user_defined_type_schema
• user_defined_type_name
• is_insertable_into
• is_typed
• commit_action

information_schema.columns

• table_catalog
• table_schema
• table_name
• column_name
• ordinal_position
• column_default
• is_nullable
• data_type
• character_maximum_length
• character_octet_length
• numeric_precision
• numeric_precision_radix
• numeric_scale
• datetime_precision
• interval_type
• interval_precision
• character_set_name
• collation_name
• domain_name
• is_self_referencing
• is_identity
• is_generated
• is_updatable
• ...

SELECT

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE }
        [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

FROM. Типы таблиц

• Постоянные таблицы
• Временные таблицы
• Виртуальные таблицы (представления)

WHERE. Фильтрация

• =
• !=, <>
• >, <, >=, <=
• OR, AND, NOT
• a BETWEEN x AND y
• a IN (x, y, z...)
• a LIKE p (_, %)

SELECT *
FROM persons
WHERE department_id IN (
    SELECT id FROM departments WHERE company_id = 42
)

JOIN-ы

CASE

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

NULLIF(value1, value2)

GREATEST(value [, ...])

LEAST(value [, ...])

SELECT a,
   CASE WHEN a=1 THEN 'one'
        WHEN a=2 THEN 'two'
        ELSE 'other'
   END
FROM test;

GROUP BY. Формирование групп

• Группировка по одному столбцу
• Группировка по нескольким столбцам
• Группировка посредством выражений

GROUP BY. Формирование групп

SELECT * FROM employee;

GROUP BY. Формирование групп

SELECT grade, department, COUNT(*)
FROM employee
GROUP BY grade, department
ORDER BY grade, department;

Агрегаторы

aggregate_name (
    [ ALL | DISTINCT ] expression [ , ... ]
    [ order_by_clause ]
) [ FILTER ( WHERE filter_clause ) ]

SELECT
  string_agg(name, ',' ORDER BY name),
  avg(age),
  count(*) FILTER (WHERE age BETWEEN 13 AND 19) AS teens
FROM persons;

Агрегаторы

Агрегаторы

Агрегаторы

HAVING. Фильтрация после группировки

SELECT grade, department, COUNT(*)
FROM employee
GROUP BY grade, department
HAVING COUNT(*) > 3;

SELECT * FROM (
    SELECT grade, department, COUNT(*) as employee_cnt
    FROM employee
    GROUP BY grade, department
) a
WHERE employee_cnt > 3;

ORDER BY. Сортировка

SELECT select_list
FROM table_expression
ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
      [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

SELECT a, b FROM table1 ORDER BY a + b, c;

SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;

# SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;
ERROR:  column "sum" does not exist
LINE 1: SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;
                                                    ^

LIMIT. Разбивка на страницы

ISO SQL:2003 - Window Functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY age ASC) AS rownumber,
    person_id, person_name, age
  FROM person
) AS foo
WHERE rownumber <= 10;

SELECT * FROM (
  SELECT
    RANK() OVER (ORDER BY age ASC) AS ranking,
    person_id, person_name, age
  FROM person
) AS foo
WHERE ranking <= 10;

LIMIT. Разбивка на страницы

Синтаксис MySQL:

SELECT
  person_id, person_name, age
FROM person
LIMIT 10
OFFSET 20;

Синтаксис DB2:

SELECT
  person_id, person_name, age
FROM person
FETCH FIRST 10 ROWS ONLY;

WITH

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ),
     top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

WITH RECURSIVE

WITH RECURSIVE recursetree (id, parent_id, level, name) AS (
    SELECT id, parent_id, 0, name FROM tree
    WHERE parent_id = 0
  UNION ALL
    SELECT t.id, t.parent_id, level+1, t.name
    FROM tree t
    JOIN recursetree rt ON rt.id = t.parent_id
  )
SELECT * FROM recursetree;

WITH RECURSIVE

WITH RECURSIVE recursetree (id, path, name) AS (
    SELECT id, array_append('{}'::int[], id), name FROM tree
    WHERE parent_id = 0
  UNION ALL
    SELECT t.id, array_append(path, t.id), t.name
    FROM tree t
    JOIN recursetree rt ON rt.id = t.parent_id
  )
SELECT id, array_to_string(path, '.') as path, name FROM recursetree
ORDER BY path;

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';

Список смежных вершин (Adjacency List)

Список смежных вершин (Adjacency List)

Вложенное множество (Nested Set)

Вложенное множество (Nested Set)

Материализованный путь (Materialized Path)

Материализованный путь (Materialized Path)

Комбинированный подход

По сути вопроса следует отметить, что скомбинировать приведенные методы можно лишь в двух направлениях:

• Списки смежности + материализованный путь (Adjacency List + Materialized Path)
• Списки смежности + вложенные множества (Adjacency List + Nested Set)

Комбинировать же Nested Set и Materialized Path особого смысла не имеет, т.к. существенного выигрыша ни в чтении, ни в записи вы не получите.

Adjacency List + Materialized Path

Для AL при использовании с MP:

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

Для MP при использовании с AL:

• Улучшаются операции выборки наследников заданного узла;
• Улучшаются операции выборки родителей заданного узла.

Adjacency List + Nested Set

Для связки AL+NS взаимовыгодность не столь очевидна.

В первую очередь это объясняется тем, что недостатки от проблем изменения узлов дерева в модели NS напрочь убивают в этой сфере все достоинства AL.

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

Но ведь и это качественное улучшение, хотя и не такое очевидное.