Описание всех лабораторных работ (1127020), страница 2
Текст из файла (страница 2)
Что такое IDENTITY, UNIQUE, прочие constraint 2. Вопрос: Что такое каскадное удаление и обновление данных? Ответ: используем: REFERENCES referenced_table_name ( ref_column) ON DELETE CASCADE ON UPDATE CASCADE Более подробно: Табличные ограничения (constraint) ● 4 лаба. Манипулирование данными ●●●●Для сдачи нужно знать всю теорию из лабы 1 (Работа в среде интерактивного SQL) Запросы на удаление и изменение в базе данных тоже должны быть сложными. В качестве доп.вопросов вам предложат написать новый запрос и/или исправить один из старых. IF поле = NULL > минус балл. ТОЛЬКО is null. 5 лаба. Создание и использование представлений protip: строчки GO в генерируемых автоматически скриптах разделение наборов команд внутри одного подключения. Если ругается, что какоето выражение must be first in a batch, поставьте перед ним GO. ● Информация о представлениях ● В начале скрипта сделайте удаление представлений: DROP VIEW <имя представления> Вопросы: 1.
Что такое представление. Для чего нужны представления. 2. Что такое WITH CHECK OPTION 3. Можно ли в представлении упорядочить записи? Ответ. Несмотря на то, что предложение ORDER BY в представлениях недопустимо, записи можно упорядочить, используя конструкцию SELECT TOP 100 PERCENT price FROM .... В примере сортировка выполняется по цене price, вместо неё может быть что угодно, в том числе строки (фамилия, имя). ● Можно ли через представления изменять исходные таблицы? Ответ: через некоторые можно, через некоторые нельзя. Подробнее по ссылке выше.Внимание! Вам нужно написать скрипты для выборок из вашего варианта и САМИМ придумать ЕЩЁ 34 выборки в предметной области (например, для поликлиники: “Определить процент смертности от заболевания 'кариес' ”). Выборки должны быть болееменее сложными. Не ленитесь! От этого зависит ваша оценка за лабу. (А результаты логичными. 100 смертность от кариеса или отрицательная успеваемость студентов повод схлопотать минус балл, задержав сдачу на 10 минут, пока КБТ и компания будут над этим ржать. Серьезно.) 4.
Какие представления являются изменяемыми? Пожалуй, один из самых важных вопросов, т.к. то что написано в литературе, например, запрещает изменяемым представлениям x GROUP BY, что не верно. Некоторые представления могут быть изменяемыми, несмотря на то, что Грабер (автор книги по MS SQL) утверждает обратное. Готовьтесь к ответу на вопрос: “Какие созданные вами представления изменяемы?”. 6 лаба. Управление транзакциями ●●●Справка Страница на Википедии, которая поможет разобраться в этой лабе Для сдачи нужно знать суть всех аномалий и уметь их объяснить. Если вы сдаёте лабу в срок (без дисконта) в надежде получить не менее 6, то про все аномалии, кроме потерянных изменений вас спрашивать не будут, т.к. только потерянные изменения являются ключевым моментом лабы и по ним можно очень много чего спросить. ● Внимание! На сайте представлен неправильный пример потерянных изменений. Вам лучше написать два скрипта. Один как с сайта. Другой правильный (согласно определению потерянных изменений). Пример: Первый скрипт: DROP TABLE tmp; CREATE TABLE tmp (id int, tmp_value int) INSERT INTO tmp VALUES (1,0) go SELECT * FROM tmp SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRANSACTION UPDATE tmp SET tmp_value = (SELECT experience FROM doctor WHERE doctor_id = 301) WHERE id = 1; go SELECT * FROM tmp UPDATE doctor SET experience = (SELECT tmp_value FROM tmp WHERE id = 1) WHERE doctor_id = 301 COMMIT; SELECT d.doctor_id, d.experience FROM doctor d WHERE doctor_id = 301; Второй скрипт: SELECT d.doctor_id, d.experience FROM doctor d WHERE doctor_id = 301; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRANSACTION UPDATE doctor SET experience = experience + 1 WHERE doctor_id = 301 COMMIT; go SELECT d.doctor_id, d.experience FROM doctor d WHERE doctor_id = 301; ● Пример потерянных изменений Одна транзакция читает данные, вторая читает данные. Первая, скажем, увеличивает значение ячейки на 1 (Типа было 90, стало 90+1=91) и заканчивается. Вторая увеличивает значение на 1 (90+1=91) и заканчивается. В итоге у нас в ячейке записано 91, хотя должно быть 90+1=91, 91+1=92. Это потерянные изменения. Этот пример привел Теймуразов. Вопросы: 1.
Можно ли в одной транзакции запустить другую транзакцию? Ответ: Можно 2. Что будет, если во второй транзакции (в неглавной) поменять READ UNCOMMITTED на READ COMMITTED Ответ: ничего не будет 3. Вопрос: Как узнать, не выполняя COMMIT или ROLLBACK, количество открытых транзакций для данного пользователя? Ответ: Испольуем системную переменную @@TRANCOUNT (узнать ее значение можно, выполнив SELECT @@TRANCOUNT). + 4. Наверное, одним из самых сложных вопросов является: “Почему же тогда все уровни изоляции блокируют потерянные изменения, а ты их мне продемонстрировал? Неужели во всех справочниках ошибки?”. КБТ после незнания ответа сказал мне его, но я ничего не понял, причина кроется в несоответствии “классического” определения и определения из справочников. May the Force be with you, Luke! кстати, в итоге не помогло. Общий смысл таков в справочниках аномалия, а это, продемонстрированное нами криво спроектированная бд. 5.
Свойства транзакций 6. Что выдаст, если запустить begin tran begin tran rollback commit Ответ ошибку:The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. 7. Какой уровень изоляции стоит по дефолту? Ответ READ COMMITTED 7 лаба. Управление доступом Лучше сделать всю лабу в одном скрипте. Вводный материал Вопросы: 1. Как поменять пароль Ответ: Можно использовать EXEC sp_password Можно использовать ALTER LOGIN Можно изменить вручную, используя Management Studio 2.
Что такое WITH GRANT OPTION 3. Что такое Роль именованный набор привелегий 4. Что такое GRANT, REVOKE и DENY 5. Что будет, если для некоторого пользователя сделать подряд DENY, GRANT, REVOKE для некоторой привилегии? a. Ответ: ничего не изменится, у пользователя не появится никаких новых привилегий. 6. Что будет, если для роли пользователя сделали DENY, а для пользователя потом GRANT? Для роли GRANT, для пользователя потом DENY? a.
Ответ: i.DENY в любом случае пользуется приоритетом над GRANT не всегда, есть случаи когда DENY не глушит GRANT, google в помощь (вне роли делаешь сначала deny потом grant) b. 7. Чем отличается LOGIN от USER Ответ: LOGIN это доступ уровня сервера USER это доступ уровня базы LOGIN и USER выполняется скриптом: CREATE LOGIN “login_name” FROM WIND. Таким образом, если на сервере лежит несколько баз данных, мы можем создать комуто LOGIN для доступа к нескольким из них. Внутри каждой базы этот пользователь должен иметь свой USER. 8. Доп. задача: пользователю, который аутентифицируется из WINDOWS разрешить создавать базы данных. Решение заключается в присвоении этому пользователю стандартной роли dbcreator. Создание OWS CREATE USER “user_name” FOR LOGIN “login_name” a. Замечание. Крайне нежелательно вместо роли использовать команду GRANT CREATE DATABASE TO “user_name” b.
т.к. это влечёт некоторые дальнейшие неудобства. 9. Доп. задача: пользователю test разрешить создавать новые таблицы. 10. Что такое dbo в названии таблиц базы данных (например dbo.Price)? a. Ответ: схема b. + сопутствующие вопросы что такое схема и для чего она нужна? 11. Доп. задача: написать скрипт, который даёт право пользователю создавать представления. Какие привилегии на базовые таблицы, из которых создаётся представление, он должен иметь. 8 лаба. Выборка метаданных ● Сделайте все скрипты в одном запросе. Так удобнее (вас все равно попросят это сделать). Вопросы: 1.
К первому скрипту: убрать из всех выборок системные таблицы. Если их нет, то попробуйте, например, создать в обозревателе объектов диаграмму БД. При выводе всех таблиц в первом скрипте не должно быть таблицы sysdiagrams. Ответ: возможны несколько вариантов устранения проблемы a. Использование fn_listextendedproperty(...). b. Использование таблицы sys.extended_properties, заключающееся в исключении из выборки всех объектов, id которых содержатся в ней, т.е. исключаются все системные таблицы. AND sysobjects.id NOT IN (SELECT major_id FROM sys.extended_properties) 2. Вывести список всех ролей в базе. Ответ: SELECT "name" FROM sys.database_principals WHERE type = 'R' 3.
Просят переписать, используя новые версии системных представлений (например, sys.objects вместо sys.sysobjects). 4. AND a.xtype = 'U' AND a.uid = (SELECT uid FROM sys.sysusers WHERE name=user_name()) ORDER BY 1, 2 5. Доп. вопрос: для каждой таблицы вывести имя столбца, для которого используется IDENTITY Ответ: SELECT a.name AS table_name, b.name AS column_name, is_identity FROM sysobjects a, sys.columns b WHERE a.id = b.object_id and is_identity=1 6. Доп. вопрос: вывести все ограничения CHECK, присутствующие в базе. Возможно несколько правильных вариантов ответа. a. Присоединить к таблице sysobjects таблицу syscomments и выбрать название ограничения и его описание: SELECT name, text FROM sysobjects SO INNER JOIN syscomments SC ON SO.id = SC.id WHERE xtype = 'C' b.
Использование INFORMATION_SCHEMA: SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS 7. Доп. вопрос: вывести identity и их параметры: SELECT a.name AS table_name, b.name AS column_name, b.is_identity, seed_value, increment_value, last_value FROM sysobjects a, sys.columns b, sys.identity_columns c WHERE a.id = b.object_id AND a.xtype = 'U' AND b.is_identity = 1 AND b.object_id = c.object_id ORDER BY 1, 2 9 лаба. Создание и использование триггеров ● Перед сдачей лабы внимательно прочтите, что от вас требуется. Возможно, написание такого триггера, как на сайте, невозможно для вашей БД. В этом случае следует на предыдущей сдаче (или по почте) попросить КБТ перефразировать условия, которым должен удовлетворять триггер.
Вопросы: 1. Продемонстрируйте, как работает триггер при добавлении/удалении/изменении строки, а потом нескольких строк. Тут у большинства триггер падает и надо исправить за пару. 2. В каком порядке могут выполняться триггеры на одно и то же действие? Ответ: Можно задать первый и последний, остальные в рандомном порядке 3. Доп. вопрос: создать таблицу с двумя полями user_login и logon_date, в которой будет храниться информация о пользователях и времени их захода в базу. После чего написать триггер, который при входе каждого нового пользователя будет эту таблицу заполнять. CREATE TABLE LOGIN_DATA ( "user_login" VARCHAR(40), "logon_date" DATETIME ); CREATE TRIGGER logon_event ON ALL SERVER AFTER LOGON AS INSERT INTO [database_name].[schema_name].LOGIN_DATA VALUES(ORIGINAL_LOGIN(), GETDATE()) [database_name].[schema_name].LOGIN_DATA главное не забудьте явно указать название базы и название схемы например: Chess.dbo.login_data 10 лаба. Использование индексов и средств оптимизации запросов Вопросы: Если у вас express версия, то вы скорее всего не сможете воспользоваться анализатором ядра СУБД, и у вас нет SQL Profiler. Решение: переустановить(я поставил MS SQL STUDIO ENTERPRISE 2008 R2 например) или возьмите ноут у друга:D 1.
На плане выполнения запроса необходимо, чтобы на всех Ваших индексах было seek , а не scan. Например, так может получиться, если Вы использовали в запросе функцию year (some_date) = some_year. Решение данной проблемы: необходимо использовать some_date between yyyymmdd and yyyymmdd (зависит от формата вашей даты) 2. Спрашивал, сколько полей задействовано в первом запросе и где это можно посмотреть (в плане выполнения запроса навести мышку, чтобы появилось оранжевое окошко, последняя строчка то, что вам нужно) 3.
Попросил открыть запросы из 4 лабы и для одного из них дал задание: переписать, чтобы время выполнения было оптимальным. 4. Что такое worktable? Ответ на msdn. .
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
