Главная страница
Навигация по странице:

  • 9.3 Проверка значений, помещаемых в представление

  • 9.4 Предикаты и исключенные поля

  • 9.5 Проверка представлений, которые базируются на других представлениях

  • 9.6 Создание представления в базе данных

  • 9.7 Модификация представления «Pokupka» из базы данных

  • 9.8 Удаление представления «Pokupka» из базы данных

  • 9.9 Контрольное задание Создать не менее трех представлений в базе данных согласно выбранному вами варианту. 9.10 Контрольные вопросы

  • 10 Лабораторная работа № 10. Индексы Цель работы

  • 10.1 Архитектура индексов

  • 10.2 Последовательность столбцов в составном индексе

  • 10.3 Производительность

  • 10.6 Использование индекса и просмотр его свойств. Просмотр свойств индекса в базе данных

  • 10.7 Исполнение запросов и просмотр плана исполнения

  • Лабораторный практикум. Лабораторная работа Обследование предметной области Построение модели ide создание контекстной диаграммы Создание диаграммы декомпозиции Создание диаграммы


    Скачать 5.73 Mb.
    НазваниеЛабораторная работа Обследование предметной области Построение модели ide создание контекстной диаграммы Создание диаграммы декомпозиции Создание диаграммы
    АнкорЛабораторный практикум
    Дата25.12.2022
    Размер5.73 Mb.
    Формат файлаpdf
    Имя файлаЛабораторный практикум.pdf
    ТипПрактикум
    #863633
    страница8 из 12
    1   ...   4   5   6   7   8   9   10   11   12
    в своем определении.
    6. Оно не должно использовать подзапросы (это – ограничение, которое не предписано для некоторых реализаций).
    7. Оно может быть использовано в другом представлении, но это представление должно также быть модифицируемыми.
    8. Оно не должно использовать константы, строки, или выражения значений (например, «comm*100») среди выбранных полей вывода.
    9. Для , оно должно содержать любые поля основной таблицы, которые имеют ограничение , если другое ограничение по умолчанию не определено.
    9.3 Проверка значений, помещаемых в представление
    Вы можете вводить значения, которые «проглатываются» (swallowed) в базовой таблице. Рассмотрим модифицируемое представление:
    CREATE VIEW Highratings
    AS SELECT cnum, rating
    FROM Customers WHERE rating = 300
    Оно просто ограничивает ваш доступ к определенным строкам и столбцам в таблице. Предположим, что вы вставляете () следующую строку:
    INSERT INTO Highratings VALUES (2018, 200)
    Это допустимая команда в этом представлении. Строка будет вставлена, с помощью представления «Highratings», в таблицу заказчиков.

    146
    Однако когда она появится там, она исчезнет из представления, поскольку значение оценки не равно 300.
    Значение 200 может быть просто напечатано, но теперь строка находится уже в таблице заказчиков, где вы не можете даже увидеть ее. Пользователь не сможет понять, почему введя строку, он не может ее увидеть, и будет неспособен при этом удалить ее.
    Вы можете быть гарантированы от модификаций такого типа с помощью включения (с опцией проверки) в определение представления. Мы можем использовать в определении представления «Highratmgs».
    CREATE VIEW Highratings
    AS SELECT cnum, rating
    FROM Customers
    WHERE rating = 300
    WITH CHECK OPTION
    Вышеупомянутая вставка будет отклонена.
    производит действие все_или_ничего (all-or-
    nothing). Вы помещаете его в определение представления, а не в команду DML, так что или все команды модификации в представлении будут проверяться, или ни одна не будет проверена. Обычно вы хотите использовать опцию проверки, используя ее в определении представления, что может быть удобно.
    В общем, вы должны использовать эту опцию, если у вас нет причины разрешать представлению помещать в таблицу значения, которые он сам не может содержать.
    9.4 Предикаты и исключенные поля
    Похожая проблема включает в себя вставку строк в представление с предикатом, базирующемся не на всех полях таблицы. Например, может показаться разумным создать представление «Londonstaff»:

    147
    CREATE VIEW Londonstaff
    AS SELECT snum, sname, comm
    FROM Salespeople WHERE city = 'London'
    В конце концов, зачем включать значение «city», если все значения «city» будут одинаковыми.
    Так как мы не можем указать значение «city» как значение по умолчанию, этим значением, вероятно, будет , и оно будет введено в поле
    ( используется, если другое значение по умолчанию не было определено). Так как в этом случае поле не будет равняться значению
    «London», вставляемая строка будет исключена из представления. Это будет верным для любой строки, которую вы попробуете вставить в просмотр
    «Londonstaff». Все они должны быть введены с помощью представления
    «Londonstaff» в таблицу продавцов, и затем исключены из самого представления
    (если определением по умолчанию был не London, то это особый случай).
    Пользователь не сможет вводить строки в это представление, хотя все еще неизвестно, может ли он вводить строки в базовую таблицу. Даже если мы добавим в определение представления
    CREATE VIEW Londonstate
    AS SELECT snum, sname, comm
    FROM Salespeople
    WHERE city = 'London'
    WITH CHECK OPTION проблема не обязательно будет решена. В результате этого мы получим представление, которое мы могли бы модифицировать или из которого мы могли бы удалять, но не вставлять в него. В некоторых случаях, это может быть хорошо; хотя, возможно, нет смысла пользователям, имеющим доступ к этому представлению, иметь возможность добавлять строки.
    Даже если это не всегда может обеспечить Вас полезной информацией, полезно включать в ваше представление все поля, на которые имеется ссылка в предикате. Если вы не хотите видеть эти поля в вашем выводе, вы всегда сможете

    148 исключить их из запроса в представлении, в противоположность запросу внутри представления. Другими словами, вы могли бы определить представление
    «Londonstaff»> подобно этому:
    CREATE VIEW Londonstaff
    AS SELECT *
    FROM Salespeople
    WHERE city = 'London'
    WITH CHECK OPTION
    Эта команда заполнит представление одинаковыми значениями в поле
    , которые вы можете просто исключить из вывода с помощью запроса, в котором указаны только те поля, которые вы хотите видеть:
    SELECT snum, sname, comm
    FROM Londonstaff
    9.5 Проверка представлений, которые базируются на других
    представлениях
    Еще одно надо упомянуть относительно предложения OPTION> в : оно не делает каскадного изменения. Оно применяется только в представлениях, в которых определено, но не в представлениях, основанных на этом представлении. Например, в предыдущем примере:
    CREATE VIEW Highratings
    AS SELECT cnum, rating
    FROM Customers
    WHERE rating = 300
    WITH CHECK OPTION
    Попытка вставить или модифицировать значение оценки не равное 300 потерпит неудачу. Однако мы можем создать второе представление (с идентичным содержанием) основанное на первом:
    CREATE VIEW Myratings

    149
    AS SELECT *
    FROM Highratings
    Теперь мы можем модифицировать оценки, не равные 300:
    UPDATE Myratings
    SET rating = 200
    WHERE cnum = 2004
    Эта команда, выполняемая так, как если бы она выполнялась как первое представление, будет допустима. Предложение просто гарантирует, что любая модификация в представлении произведет значения, которые удовлетворяют предикату этого представления.
    Модификация других представлений, базирующихся на первом текущем, является все еще допустимой, если эти представления не защищены предложениями внутри этих представлений. Даже если такие предложения установлены, они проверяют только те предикаты представлений, в которых они содержатся. Так, например, даже если представление «Myratings» создавалось следующим образом
    CREATE VIEW Myratings
    AS SELECT *
    FROM Highratings
    WITH CHECK OPTION, проблема не будет решена. Предложение будет исследовать только предикат представления «Myratings». Пока у «Myratings», фактически, не имеется никакого предиката, ничего не будет делать. Если используется предикат, то он будет проверяться всякий раз, когда представление «Myratings» будет модифицироваться, но предикат
    «Highratings» все равно будет проигнорирован.

    150
    9.6 Создание представления в базе данных
    1. Откройте и подключитесь к локальному серверу.
    2. На панели в окне введите и исполните следующий код
    Transact-SQL:
    USE Northwind
    GO
    CREATE VIEW Pokupka
    AS SELECT ФИО_клиента, Количество_проданного_товара, Дата
    FROM
    Клиент,
    Продажа
    WHERE
    Клиент.Код_клиента
    =
    Продажа.Код_клиента
    Этот оператор создает в базе данных представление
    «Pokupka». Оператор содержит запрос из определения .
    9.7 Модификация представления «Pokupka» из базы данных

    1. На панели в окне введите и исполните следующий код
    Transact-SQL:

    151
    USE Northwind
    GO
    ALTER VIEW Pokupka
    AS SELECT ФИО_клиента, Название_товара,
    Количество_проданного_товара, Дата
    FROM Клиент, Продажа, Товар
    WHERE Клиент.Код_клиента = Продажа.Код_клиента and
    Товар.Код_товара = Продажа.Код_товара
    2. Исполните оператор Transact-SQL. На вкладке панели
    выводится сообщение об успешном завершении команды.
    3. В дереве объектов в окне найдите представление
    «Pokupka» и раскройте узел . Обратите внимание на столбец
    <Название_товара>, добавленный к списку столбцов.
    4. Закройте .
    9.8 Удаление представления «Pokupka» из базы данных
    1. На панели в окне введите и исполните следующий код
    Transact-SQL:
    USE Northwind
    GO
    DROP VIEW Pokupka
    Этот оператор удаляет представление «Pokupka» из базы данных
    .
    2. Исполните оператор Transact-SQL.
    На вкладке панели выводится сообщение об успешном завершении команды.
    3. В дереве объектов в окне найдите узел для базы данных . Обратите внимание, что представление «Pokupka» больше не показано.

    152
    9.9 Контрольное задание
    Создать не менее трех представлений в базе данных согласно выбранному вами варианту.
    9.10 Контрольные вопросы
    1. Сформулируйте определение представления.
    2. Каково назначение представлений?
    3. Как создать представление?
    4. Что такое модифицируемое представление?
    5. Каким образом модифицировать представление?

    153
    10 Лабораторная работа № 10. Индексы
    Цель работы: используя язык T-SQL, научиться создавать индексы.
    Используемое программное обеспечение: Microsoft SQL Server 2017.
    Индекс – объект базы данных, создаваемый с целью повышения производительности поиска данных. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра таблицы строка за строкой может занимать много времени. Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы и, таким образом, позволяет искать строки, удовлетворяющие критерию поиска. Ускорение работы с использованием индексов достигается в первую очередь за счѐт того, что индекс имеет структуру, оптимизированную под поиск – например, сбалансированного дерева.
    Некоторые системы управления базами данных расширяют возможности индексов введением возможности создания индексов по столбцам представлений или индексов по выражениям. Например, индекс может быть создан по выражению и соответственно будет хранить ссылки, ключом к которым будет значение поля в верхнем регистре.
    Кроме того, индексы могут быть объявлены как уникальные и как не уникальные. Уникальный индекс реализует ограничение целостности на таблице, исключая возможность вставки повторяющихся значений.
    10.1 Архитектура индексов
    Существует два типа индексов: кластерные и некластерные. При наличии кластерного индекса строки таблицы упорядочены по значению ключа этого индекса. Если в таблице нет кластерного индекса, таблица называется кучей.
    Некластерный индекс, созданный для такой таблицы, содержит только указатели на записи таблицы. Кластерный индекс может быть только одним для каждой

    154 таблицы, но каждая таблица может иметь несколько различных некластерных индексов, каждый из которых определяет свой собственный порядок следования записей.
    Индексы могут быть реализованы различными структурами. Наиболее частоупотребимы B*-деревья, B+-деревья, Bдеревья и хеши.
    10.2 Последовательность столбцов в составном индексе
    Последовательность, в которой столбцы представлены в составном индексе, достаточно важна. Дело в том, что получить набор данных по запросу, затрагивающему только первый из проиндексированных столбцов, можно.
    Однако в большинстве систем управления базами данных невозможно или неэффективно получение данных только по второму и далее проиндексированным столбцам (без ограничений на первый столбец).
    Например, представим себе телефонный справочник, отсортированный вначале по городу, затем по фамилии, и затем по имени. Если вы знаете город, вы можете легко найти все телефоны этого города. Однако в таком справочнике будет весьма трудоемко найти все телефоны, записанные на определѐнную фамилию — для этого необходимо посмотреть в секцию каждого города и поискать там нужную фамилию. Некоторые системы управления базами данных выполняют эту работу, остальные же просто не используют такой индекс.
    10.3 Производительность
    Для оптимальной производительности запросов индексы обычно создаются на тех столбцах таблицы, которые часто используются в запросах. Для одной таблицы может быть создано несколько индексов. Однако увеличение числа индексов замедляет операции добавления, обновления, удаления строк таблицы, поскольку при этом приходится обновлять сами индексы. Кроме того, индексы занимают дополнительный объем памяти, поэтому перед созданием

    155 индекса следует убедиться, что планируемый выигрыш в производительности запросов превысит дополнительную затрату ресурсов компьютера на сопровождение индекса.
    10.4 Ограничения
    Индексы полезны для многих приложений, однако на их использование накладываются ограничения. Возьмѐм такой запрос SQL:
    SELECT first_name FROM people WHERE last_name = 'Франкенштейн'
    Для выполнения такого запроса без индекса система управления базами данных должна проверить поле в каждой строке таблицы (этот механизм известен как «полный перебор» или «полный скан таблицы», в плане может отображаться словом ). При использовании индекса сустема управления базами данных просто проходит по Bдереву, пока не найдѐт запись
    «Франкенштейн». Такой проход требует гораздо меньше ресурсов, чем полный перебор таблицы.
    Теперь возьмѐм такой запрос:
    SELECT email_address FROM customers WHERE email_address LIKE
    '%@yahoo.com'
    Этот запрос должен нам найти всех клиентов, у которых email заканчивается на «@yahoo.com», однако даже если по столбцу есть индекс, система управления базами данных всѐ равно будет использовать полный перебор таблицы. Это связано с тем, что индексы строятся в предположении, что слова / символы идут слева направо. Использование символа подстановки в начале условия поиска исключает для системы управления базами данных возможность использования поиска по B-дереву. Эта проблема может быть решена созданием дополнительного индекса по выражению и формированием запроса вида:
    SELECT email_address FROM customers WHERE reserve(email_address)
    LIKE reserve('%@yahoo.com')

    156
    В данном случае символ подстановки окажется в самой правой позиции
    («moc.oohay@%»), что не исключает использование индекса по
    .
    10.5 Редкий индекс
    Редкий индекс в базах данных – это файл с последовательностью пар ключей и указателей. Каждый ключ в редком индексе, в отличие от плотного индекса, ассоциируется с определѐнным указателем на блок в сортированном файле данных. Идея использования индексов пришла, оттого что современные базы данных слишком массивны и не помещаются в основную память. Мы обычно делим данные на блоки и размещаем данные в памяти поблочно. Однако поиск записи в базе данных может занять много времени. С другой стороны, файл индексов или блок индексов намного меньше блока данных и может поместиться в буфере основной памяти, что увеличивает скорость поиска записи.
    Поскольку ключи отсортированы, можно воспользоваться бинарным поиском. В кластерных индексах с дублированными ключами редкий индекс указывает на
    наименьший ключ в каждом блоке.
    10.6 Использование индекса и просмотр его свойств. Просмотр
    свойств индекса в базе данных
    1. Откройте и подключитесь к локальному серверу.
    2. На панели в окне введите и исполните следующий код:
    USE Northwind
    GO sp_helpindex customers
    На вкладке панели появляются пять индексов (рисунок
    1).

    157
    Рисунок 1 – Просмотр индексов таблицы «Customers»
    3. Какой индекс отражает порядок сортировки таблицы «Customers»?
    4. Есть ли в таблице «Customers» составной индекс?
    10.7 Исполнение запросов и просмотр плана исполнения
    1. В щелкните , а затем .
    2. На панели в окне введите и исполните следующий код:
    USE Northwind
    GO
    SELECT *
    FROM customers
    На вкладке панели появляется результирующий набор.
    Обратите внимание, что он упорядочен по значению (рисунок 2).

    158
    Рисунок 2 – Просмотр содержимого таблицы
    3. Щелкните вкладку .
    План исполнения выводится на вкладке панели .
    Обратите внимание, что оптимизатор запросов использовал кластерный индекс
    «PK_Customers». На вкладке «Execution Plan» имя индекса «PK_Customers» обрезано до «РК_Сu...» (рисунок 3).

    159
    Рисунок 3 – Отображение плана исполнения просмотра содержимого таблицы «Customers»
    4. На панели в окне введите и исполните следующий код:
    SELECT city, customerid
    FROM customers
    На вкладке панели появляется результирующий набор.
    Обратите внимание, что он отсортирован по значению (рисунок 4).

    160
    Рисунок 4 – Просмотр содержимого полей и таблицы
    «Customers»
    5. Щелкните вкладку .
    План исполнения показывает, что оптимизатор запросов использовал некластерный индекс «City» (рисунок 5).

    161
    Рисунок 5 – Отображение плана исполнения просмотра содержимого полей и таблицы «Customers»
    6. Почему оптимизатор запросов в этом случае выбрал индекс «City», а не
    «PK_Customers»?
    7. На панели в окне введите и исполните следующий код:
    SELECT companyname, contactname, city, country, phone
    FROM customers
    На вкладке панели появляется результирующий набор.
    Обратите внимание, что он упорядочен по значению столбца .
    Этот порядок сортировки на самом деле соответствует порядку столбца
    , значения которого включают, по крайней мере, первые три символа значений столбца (рисунок 6).

    162
    Рисунок 6 – Просмотр содержимого полей ,
    , , и таблицы «Customers»
    8. Щелкните вкладку .
    План исполнения показывает, что оптимизатор запросов использовал индекс «РК_ Customers». Это произошло, поскольку ни один индекс кроме него не является покрывающим для запроса. В следующем задании вы создадите покрывающий индекс для этого запроса (рисунок 7).
    Рисунок 7 – Отображения плана исполнения просмотра содержимого полей , , , и таблицы
    «Customers»
    Оставьте вкладку активной.

    163
    1   ...   4   5   6   7   8   9   10   11   12


    написать администратору сайта