Лабораторный практикум. Лабораторная работа Обследование предметной области Построение модели ide создание контекстной диаграммы Создание диаграммы декомпозиции Создание диаграммы
Скачать 5.73 Mb.
|
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) Это допустимая команда 146 Однако когда она появится там, она исчезнет из представления, поскольку значение оценки не равно 300. Значение 200 может быть просто напечатано, но теперь строка находится уже в таблице заказчиков, где вы не можете даже увидеть ее. Пользователь не сможет понять, почему введя строку, он не может ее увидеть, и будет неспособен при этом удалить ее. Вы можете быть гарантированы от модификаций такого типа с помощью включения CREATE VIEW Highratings AS SELECT cnum, rating FROM Customers WHERE rating = 300 WITH CHECK OPTION Вышеупомянутая вставка будет отклонена. 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 Проверка представлений, которые базируются на других представлениях Еще одно надо упомянуть относительно предложения 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 Эта команда, выполняемая так, как если бы она выполнялась как первое представление, будет допустима. Предложение Модификация других представлений, базирующихся на первом текущем, является все еще допустимой, если эти представления не защищены предложениями CREATE VIEW Myratings AS SELECT * FROM Highratings WITH CHECK OPTION, проблема не будет решена. Предложение «Highratings» все равно будет проигнорирован. 150 9.6 Создание представления в базе данных 1. Откройте 2. На панели Transact-SQL: USE Northwind GO CREATE VIEW Pokupka AS SELECT ФИО_клиента, Количество_проданного_товара, Дата FROM Клиент, Продажа WHERE Клиент.Код_клиента = Продажа.Код_клиента Этот оператор создает в базе данных «Pokupka». Оператор 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. В дереве объектов в окне 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 = 'Франкенштейн' Для выполнения такого запроса без индекса система управления базами данных должна проверить поле «Франкенштейн». Такой проход требует гораздо меньше ресурсов, чем полный перебор таблицы. Теперь возьмѐм такой запрос: SELECT email_address FROM customers WHERE email_address LIKE '%@yahoo.com' Этот запрос должен нам найти всех клиентов, у которых email заканчивается на «@yahoo.com», однако даже если по столбцу 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 На вкладке Обратите внимание, что он упорядочен по значению 158 Рисунок 2 – Просмотр содержимого таблицы 3. Щелкните вкладку План исполнения выводится на вкладке Обратите внимание, что оптимизатор запросов использовал кластерный индекс «PK_Customers». На вкладке «Execution Plan» имя индекса «PK_Customers» обрезано до «РК_Сu...» (рисунок 3). 159 Рисунок 3 – Отображение плана исполнения просмотра содержимого таблицы «Customers» 4. На панели SELECT city, customerid FROM customers На вкладке Обратите внимание, что он отсортирован по значению 160 Рисунок 4 – Просмотр содержимого полей «Customers» 5. Щелкните вкладку План исполнения показывает, что оптимизатор запросов использовал некластерный индекс «City» (рисунок 5). 161 Рисунок 5 – Отображение плана исполнения просмотра содержимого полей 6. Почему оптимизатор запросов в этом случае выбрал индекс «City», а не «PK_Customers»? 7. На панели SELECT companyname, contactname, city, country, phone FROM customers На вкладке Обратите внимание, что он упорядочен по значению столбца Этот порядок сортировки на самом деле соответствует порядку столбца 162 Рисунок 6 – Просмотр содержимого полей 8. Щелкните вкладку План исполнения показывает, что оптимизатор запросов использовал индекс «РК_ Customers». Это произошло, поскольку ни один индекс кроме него не является покрывающим для запроса. В следующем задании вы создадите покрывающий индекс для этого запроса (рисунок 7). Рисунок 7 – Отображения плана исполнения просмотра содержимого полей «Customers» Оставьте вкладку |