Моделирование. Шарики.

В базе данных необходимо хранить информацию о шариках (номер, диаметр, цвет). Номер шарика должен быть уникальным. В качестве первичного ключа использовать номер шарика запрещено. Редактирование номера шарика запрещено. Номер шарика должен формироваться минимальным из незанятых других шариков. Шарики могут быть связанны между собой веревочкой. Между любыми двумя шариками не может быть две веревочки. Шарик НЕ может быть связан сам с собой. Шарики не могут быть связаны, если целая часть от деления суммы номеров шариков на остаток от деления номеров кратно 3.

Логическая модель

Физическая модель

Задачи:
1. Необходимо написать скрипт, создающий требуемые таблицы, ограничения целостности, указать на ошибки в задании, если таковые есть. Если что-то реализовать средствами СУБД невозможно объяснить почему, предложить варианты реализации.

CREATE TABLE BALL
( BALL_NUMBER INTEGER,
  BALL_DIAMETER INTEGER,
  BALL_COLOUR VARCHAR2(16)
) TABLESPACE MIRAVILD_DATA;
 
COMMENT ON TABLE BALL IS 'Шарик';
COMMENT ON COLUMN BALL.BALL_NUMBER IS 'Номер шарика';
COMMENT ON COLUMN BALL.BALL_DIAMETER IS 'Диаметр шарика';
COMMENT ON COLUMN BALL.BALL_COLOUR IS 'Цвет шарика';

CREATE UNIQUE INDEX U_IDX_BALL ON BALL (BALL_NUMBER) TABLESPACE MIRAVILD_INDEX;

ALTER TABLE BALL ADD CONSTRAINT PK_BALL PRIMARY KEY (BALL_NUMBER) 
USING INDEX U_IDX_BALL ENABLE VALIDATE;

-- Номер шарика положительное целое число
ALTER TABLE BALL ADD CONSTRAINT CC_BALL_NUMBER CHECK (BALL_NUMBER > 0);
/

CREATE TABLE ROPE 
(BALL_FIRST INTEGER,
 BALL_SECOND INTEGER
) TABLESPACE MIRAVILD_DATA;
 
COMMENT ON TABLE ROPE IS 'Верёвочка';
COMMENT ON COLUMN ROPE.BALL_FIRST IS 'Номер первого шарика';
COMMENT ON COLUMN ROPE.BALL_SECOND IS 'Номер второго шарика';

ALTER TABLE ROPE ADD CONSTRAINT FK_BALL_FIRST FOREIGN KEY (BALL_FIRST) 
REFERENCES BALL (BALL_NUMBER);

ALTER TABLE ROPE ADD CONSTRAINT FK_BALL_SECOND FOREIGN KEY (BALL_SECOND) 
REFERENCES BALL (BALL_NUMBER);

CREATE UNIQUE INDEX U_IDX_ROPE ON ROPE (BALL_FIRST, BALL_SECOND) TABLESPACE MIRAVILD_INDEX;

ALTER TABLE ROPE ADD CONSTRAINT PK_ROPE PRIMARY KEY (BALL_FIRST, BALL_SECOND) 
USING INDEX U_IDX_ROPE ENABLE VALIDATE;

-- Шарики не могут быть связаны, если целая часть от деления суммы номеров шариков на остаток от деления номеров кратно 3
ALTER TABLE ROPE ADD CONSTRAINT CC_BALL_FIRST CHECK (MOD((BALL_FIRST + BALL_SECOND), BALL_FIRST) <> 3);

ALTER TABLE ROPE ADD CONSTRAINT CC_BALL_SECOND CHECK (MOD((BALL_FIRST + BALL_SECOND), BALL_SECOND) <> 3);

ALTER TABLE ROPE ADD CONSTRAINT CC_ROPE CHECK (BALL_FIRST <> BALL_SECOND); -- Шарик НЕ может быть связан сам с собой
/

-- Между любыми двумя шариками не может быть две веревочки

CREATE OR REPLACE TRIGGER trg_rope
  BEFORE INSERT ON rope
  FOR EACH ROW
DECLARE
  l_cnt PLS_INTEGER;
BEGIN
  SELECT COUNT(*)
    INTO l_cnt
    FROM rope r
   WHERE r.ball_first = :new.ball_second
     AND r.ball_second = :new.ball_first;

  IF l_cnt = 1 THEN
    raise_application_error(-20100,
                            'Между любыми двумя шариками не может быть две веревочки');
  END IF;

END trg_rope;
/

ВАЖНО! На большом количестве шариков, порядка 10 000, триггерная процедура станет узким местом, в частности запрос к таблице ROPE:

  SELECT COUNT(*)
    INTO l_cnt
    FROM rope r
   WHERE r.ball_first = :new.ball_second
     AND r.ball_second = :new.ball_first;
/

2. Написать скрипт для наполнения тестовыми данными.
Заполняем таблицу BALL

DECLARE
  i INTEGER := 0;
BEGIN
  <<ball>>
  WHILE i < 100
  LOOP
    i := i + 1;
    INSERT INTO ball b
      (b.ball_number, b.ball_diameter, b.ball_colour)
    VALUES
      (i, i + 1, 'RED');
  END LOOP ball;
END;
/

COMMIT WORK;
/

Заполняем таблицу ROPE

BEGIN
  <<ball_first>>
  FOR i IN (SELECT b.ball_number FROM ball b)
  LOOP
    <<ball_second>>
    FOR j IN (SELECT b.ball_number FROM ball b)
    LOOP
      BEGIN
        INSERT INTO rope r
          (r.ball_first, r.ball_second)
        VALUES
          (i.ball_number, j.ball_number);
      EXCEPTION
        WHEN OTHERS THEN
          sys.dbms_output.put_line('sqlcode/' || SQLCODE || '/sqlerrm/' ||
                                   SQLERRM);
      END;
    END LOOP ball_second;
  END LOOP ball_first;
END;
/

COMMIT WORK;
/

3. Написать запрос, возвращающий номера шариков, не связанных веревочкой.

SELECT b.ball_number
  FROM ball b
 WHERE NOT EXISTS (SELECT 1 FROM rope r WHERE r.ball_first = b.ball_number)
   AND NOT EXISTS
 (SELECT 1 FROM rope r WHERE r.ball_second = b.ball_number);
/

SELECT b.ball_number
  FROM ball b
MINUS
SELECT r1.ball_first
  FROM rope r1
MINUS
SELECT r2.ball_second
  FROM rope r2;
/

4. Написать два варианта запроса, возвращающих шарики, имеющих три и более связей. Продемонстрировать планы запросов для обоих вариантов, к планам запроса дать комментарии. Предложения для повышения скорости выполнения.
Первый вариант запроса:

SELECT r.ball_number, SUM(r.cnt_rope) AS sum_rope
  FROM (SELECT r.ball_first AS ball_number, COUNT(*) AS cnt_rope
          FROM rope r
         GROUP BY r.ball_first
        UNION ALL
        SELECT r.ball_second, COUNT(*) AS cnt_second
          FROM rope r
         GROUP BY r.ball_second) r
 GROUP BY r.ball_number
 HAVING SUM(r.cnt_rope) >= 3
 ORDER BY sum_rope DESC;
/

Предполгаемый план запроса полученный в IDE PL/SQL Developer:

План выполнения запроса

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

INDEX FAST FULL SCAN — при таком методе читаются все индексные блоки в сегменте. Выбираются среди них листовые блоки, из которых уже берутся значения индекса. Так как индексные блоки в сегменте размещены неупорядоченно, поэтому и результат выборки не может быть использован как отсортированная последовательность. Но есть существенный плюс в таком методе выборки. Из сегмента читаются сразу несколько смежных блоков за один заход (multiblock reads). Индекс читается несколькими параллельными процессами. Это позволяет достаточно быстро получить данные с диска. FAST FULL SCAN позволяет добраться к данным, находящимся в индексе, без доступа к таблице.

Используется, когда индекс содержит все колонки, необходимые для запроса. А также одна колонна в индексном ключе должна иметь NOT NULL ограничение.

Второй вариант запроса:

SELECT r.ball_number, SUM(r.cnt_rope) AS sum_rope
  FROM (SELECT r.ball_first as ball_number,
               COUNT(*) over(PARTITION BY r.ball_first ORDER BY r.ball_first) AS cnt_rope
          FROM rope r
        UNION
        SELECT r.ball_second,
               COUNT(*) over(PARTITION BY r.ball_second ORDER BY r.ball_second)
          FROM rope r) r
 GROUP BY r.ball_number
HAVING SUM(r.cnt_rope) >= 3;
/

Предполгаемый план запроса полученный в IDE PL/SQL Developer:

План выполнения запроса

Здесь требуется больше ресурсов оптимизатору за счёт дополнительной операции SORT UNIQUE, обусловленной оператором UNION в запросе.
5. Написать запрос возвращающий номер(а) шарика(ов), имеющих максимальное количество связей.

SELECT r.ball_number
  FROM (SELECT r.ball_number,
               r.sum_rope,
               dense_rank() over(ORDER BY r.sum_rope DESC) AS dense_rank
          FROM (SELECT r.ball_number, SUM(r.cnt_rope) AS sum_rope
                  FROM (SELECT r.ball_first AS ball_number,
                               COUNT(*) AS cnt_rope
                          FROM rope r
                          JOIN ball b
                            ON b.ball_diameter = r.ball_first
                         GROUP BY r.ball_first
                        UNION ALL
                        SELECT r.ball_second, COUNT(*) AS cnt_rope
                          FROM rope r
                         GROUP BY r.ball_second) r
                 GROUP BY r.ball_number) r) r
 WHERE r.dense_rank = 1;
/

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

WITH avg_ball AS
 ( -- Средний размер привязанных шариков
  SELECT r.ball_number, SUM(r.sum_ball) / SUM(cnt_rope) AS avg_ball
    FROM (SELECT r.ball_first AS ball_number,
                  SUM(b.ball_diameter) AS sum_ball,
                  COUNT(*) AS cnt_rope
             FROM rope r
             JOIN ball b
               ON b.ball_number = r.ball_second
            GROUP BY r.ball_first
           UNION ALL
           SELECT r.ball_second,
                  SUM(b.ball_diameter) AS sum_ball,
                  COUNT(*) AS cnt_rope
             FROM rope r
             JOIN ball b
               ON b.ball_number = r.ball_first
            GROUP BY r.ball_second) r
   GROUP BY r.ball_number)
SELECT r.ball_number
  FROM (SELECT r.ball_number,
               r.sum_rope,
               dense_rank() over(ORDER BY r.sum_rope DESC) AS dense_rank
          FROM (SELECT r.ball_number, SUM(r.cnt_rope) AS sum_rope
                  FROM (SELECT r.ball_first AS ball_number,
                               COUNT(*) AS cnt_rope
                          FROM rope r
                         GROUP BY r.ball_first
                        UNION ALL
                        SELECT r.ball_second, COUNT(*) AS cnt_rope
                          FROM rope r
                         GROUP BY r.ball_second) r
                 GROUP BY r.ball_number) r) r
  JOIN avg_ball a
    ON a.ball_number = r.ball_number
   AND a.avg_ball > 7
 WHERE r.dense_rank = 1;
/

P.S.
Запросы написать по возможности в стандарте SQL92. (Можно написать вариант в стандарте SQL92 и вариант использования расширений). При написании функций (если требуется) написать на PL/pgSQL или на PL/SQL.

Конфигурация СУБД — Oracle Database 11g Express Edition Release 11.2.0.2.0
IDE — PL/SQL Developer Version 11.0.6.1776

Tags: Oracle, PL/SQL, SQL, SQL92

You must be logged in to post a comment.

Miravild

Моделирование. Шарики.

Leave a Reply