отчет. Учебная практика 4ИП. Практическая работа 1 2 Построение er модели. 2 Практическая работа 2. 10 Выборка с использованием условий отбора. 10
 Скачать 1.35 Mb.
|
Практическая работа №8-9.Изменение и удаление таблиц и индексов.Изменение структуры таблиц.Цель работы: Изучить команды DDL: ALTER TABLE, DROP TABLE. Ход работы  Команда ALTER TABLE Если при создании таблицы были допущены ошибки в ее описании, исправить их можно несколькими способами. Во-первых, если таблица еще не содержит информации, ее можно просто удалить и создать снова. В противном случае целесообразно использовать команду ALTER TABLE. Команда ALTER TABLE позволяет Добавлять и удалять столбцы. Добавлять и изменять описания столбцов. Добавлять, удалять, отключать ограничения. При успешном завершении команды выдается сообщение The command(s) completed successfully. (Команда выполнена успешно). Модификация ограничений Синтаксис команды:ALTER TABLE <имя таблицы>ALTER| ADD| DROP {CONSTRAINT <имя ограничения>}FOREIGN KEY [REFERENCES<имя таблицы> (<имя столбца> [,…n])] | PRIMARY KEY | UNIQUE | CHECK (<имя столбца> [,…n])} [ON DELETE CASCADE] Если необходимо наложить дополнительное ограничение на значение атрибута, то следует использовать команду ALTER совместно с опцией ADD, которая позволяет добавлять столбец или новое ограничение в таблицу, в свою очередь опция DROP даст возможность удалить то или иное ограничение, наложенное на данные в таблице. Если ограничение необходимо исправить (Foreign key, Primary key, Unique, Check), то его сначала удаляют, а затем создают снова. Для того чтобы удалить ограничение, необходимо указать его имя. Просмотреть созданные ограничения можно выполнив системную хранимую процедуру: EXEC SP_HELP <имя таблицы> Пример 1 Задача. Ввести ограничение на столбец DataPasport, полагая, что значение этого атрибута должно превышать значение 01.01.2000. Решение. ALTER TABLE Student ADD CONSTRAINT StudentYearBeginCheck Check(DataPasport>'01.01.2000'); Примечание. При попытке вставить следующую строку INSERT INTO Student (NRecordBook,SName,IDGroup,SPasport,NPasport,DataPasport, NameDeptPasport,INN) VALUES('050004','Митькин М.М.', 2,'8701','192417','11.26.1999', 'УВД г.Ухты','1111111114') система генерирует следующее сообщение об ошибке: Server: Msg 547 INSERT statement conflicted with COLUMN CHECK constraint 'StudentYearBeginCheck'. The conflict occurred in database 'Student', table 'Student', column 'DataPasport'. The statement has been terminated. При запуске же команды: INSERT INTO Student (NRecordBook,SName,IDGroup,SPasport,NPasport,DataPasport, NameDeptPasport,INN) VALUES('050004','Митькин М.М.', 2,'8701','192417','11.26.2000','УВД г.Ухты', '1111111114') система генерирует сообщение: (1 row(s) affected) Пример 2 Задача. Ввести ограничения FOREIGN KEY для таблицы Uplan. Таблица Uplan ссылается на таблицу Subject по атрибуту IDSubject. Решение. ALTER TABLE UPlan ADD CONSTRAINT PlanSubjectForeign FOREIGN KEY (IDSubject) REFERENCES Subject(IDSubject); ! Обратите внимание, что когда ограничение FOREIGN KEY задается таким образом, что ни одна строка в отношении Subject не может быть удалена, до тех пор пока в отношении UPlan есть строки, ссылающиеся на удаляемый предмет. Как избежать этой коллизии смотрите в следующем примере Задача. Ввести ограничения FOREIGN KEY для таблицы UPlan таким образом, чтобы при удалении из таблицы Subject записей по тому или иному предмету, были бы удалены и соответствующие этим предметам записи из таблицы UPlan. Решение.ALTER TABLE UPlan ADD CONSTRAINT PlanSubjectForeign FOREIGN KEY (IDSubject) REFERENCES Subject(IDSubject) ON DELETE CASCADE; Проблему каскадного удаления соответствующих строк в ссылающейся таблице (Progress) при удалении строк в ссылочной таблице (Subject) решает опция ON DELETE CASCADE. Для того чтобы удалить ограничение, необходимо указать его имя. Пример 3 Задача. Удалить ограничение FOREIGN KEY PlanSubjectForeign на атрибуте IDSubject в таблице Uplan. Решение. Прежде чем удалить ограничение, выясним его имя. В случае, если вы руководствовались своим правилом именования объектов, эта задача значительно упрощается. В противном случае можно использовать уточнить имя ограничения, выполнив команду EXEC sp_help UPLAN. ALTER TABLE UPlan DROP CONSTRAINT PlanSubjectForeign ; Добавление ограничений с ограниченной областью проверки Синтаксис команды: ALTER TABLE <имя таблицы>[WITH CHECK| WITH NOCHECK] ADD {CONSTRAINT <имя ограничения>} FOREIGN KEY [REFERENCES<имя таблицы> (<имя столбца> [,…n])] | CHECK (<имя столбца> [,…n])}[ON DELETE CASCADE] В некоторых ситуациях может возникнуть необходимость в отмене проверки ограничений на какой-то период времени. Или возникнет ситуация, когда необходимо ввести новое ограничение, но вывести из-под его действия уже существующие данные. Сразу следует отметить, что ни одна из вышеперечисленных операций не может быть произведена для ограничений PRIMARY KEY и UNIQUE. Таким образом, если мы вводим новое ограничение и хотим, чтобы SQL Server 2000 проверил все существующие данные на соответствие этому ограничению, следует добавить это ограничение с опцией WITH CHECK, в противном случае с опцией WITH NOCHECK Временное отключение ограничений и использование опции WITH NOCHECK требует большой осторожности. В противном случае вы можете получить некорректный результат. Например, в таблице Progress могут оказаться оценки несуществующего студента, учащегося в несуществующей группе, изучающего несуществующие дисциплины и т. д. Пример 4 Задача. Добавить ограничение FOREIGN KEY для столбца IDGroup в таблице Student5. Все данные в таблице Student проверить на соответствие новому ограничению. Решение. ALTER TABLE Student WITH CHECK ADD CONSTRAINT StudentIDGroupForeign FOREIGN KEY (IDGroup) REFERENCES SGroup(IDGroup) Результат выполнения команды будет зависеть от состояния вашей базы данных. Вполне возможно, что команда не будет выполнена, тогда вы получите следующее сообщение, говорящее о том, что нарушена целостность базы данных. Server: Msg 547 ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'StudentIDGroupForeign'. The conflict occurred in database 'Student', table 'SGroup', column 'IDGroup'. Пример 5 Задача. Добавить ограничение FOREIGN KEY для столбца IDGroup в таблице Student. Все данные в таблице Student, введенные на момент создания ограничения, проверке не подлежат. Решение. ALTER TABLE Student WITH NOCHECK ADD CONSTRAINT StudentIDGroupForeign FOREIGN KEY (IDGroup) REFERENCES SGroup(IDGroup) В этом случае независимо от содержания таблиц результат будет следующим. The command(s) completed successfully. Задание 1 Ввести ограничение на оценку в отношении Успеваемость. Оценка не должна превышать 5 баллов. Номер семестра не должен превышать 10. Задание 2 Создать внешние ключи во всех таблицах, используя опцию Foreign Key, при этом установить опцию каскадного удаления там, где это необходимо. Задание 3 Удалить первичный ключ в отношении Student. Задание 4 Проследить за изменением ограничения Foreign key в отношениях, связанных с отношением Student. Еще раз восстановите все удаленные ограничения. Отключение и подключение ограничений Отключить можно как отдельное ограничение, указав его имя, так и все, используя опцию ALL Синтаксис команды: ALTER TABLE <имя таблицы>[CHECK| NOCHECK]{CONSTRAINT <имя ограничения>| ALL}FOREIGN KEY [REFERENCES<имя таблицы> (<имя столбца> [,…n])] | CHECK (<имя столбца> [,…n])} Задание 5 Отключите ограничения внешнего ключа в таблице Student. Введите в таблицу Student студента Васькина В.В. из несуществующей группы. Попытайтесь подключить ранее отключенное ограничение. Выполните все необходимые действия для того, чтобы вновь подключить ограничение, а все данные в отношении Student соответствовали условиям целостности базы данных. Задание 6 Смоделируйте ситуацию, когда необходимо отключить ограничения и разработайте мероприятия, которые позволят вам в дальнейшем привести базу данных в согласованное состояние, отвечающее всем условиям целостности. Правила для изменения и модификации описания столбцов При корректировке таблиц нельзя: добавлять новый столбец с опцией NOT NULL. добавлять к столбцу опцию NOT NULL, если в нем есть пустые значения уменьшить размер поля или изменить его тип, если в нем содержатся какие-либо данные. Удалить столбец из таблицы, если на этот столбец были установлены какие-либо ограничения кроме NOT NULL| NULL^ Добавление столбца Синтаксис команды:ALTER TABLE <имя таблицы>ADD <имя столбца> <тип данных> <ширина столбца>[DEFAULT <значение>] [,…n];где DEFAULT – определяет значение столбца по умолчанию. При добавлении столбца он автоматически становится последним в таблице. Изменить положение столбца в таблице не представляется возможным. Пример Задача. Добавить столбец YearBegin (год начала учебы в институте) в таблицу Student, задав тип данных Datetime. Решение. ALTER TABLE StudentADD YearBegin Datetime; Модификация столбца Синтаксис команды:ALTER TABLE <имя таблицы> ALTER COLUMN <имя столбца> <новый тип данных> <длина>[DEFAULT <значение>][NULL|NOT NULL] [,…n]; SQL Server не разрешает изменять столбцы типа text, ntext, image, rowversion, вычисляемые столбцы, столбцы, используемые в репликации, и столбцы, на которые имеются ссылки в выражениях вычисляемых столбцов или ограничений, а также столбцы с установленным свойством ROWGUIDCOL6. Нельзя удалить или изменить столбец, имеющий значение по умолчанию (ограничение DEFAULT). Однако можно увеличить размер столбцов переменой длины, которые используются в индексах, в ограничениях CHECK или UNIQUE. Задание 7 Добавить в таблицу Student столбец Single, тип данных VARCHAR(3), назначив значение по умолчанию “Да”. Удалить столбец. Задание 8 Добавить в таблицу Student столбец AVGMARK, тип Numeric (5,2). В столбце будет храниться средняя оценка студента. Мы оставим этот столбец в базе данных лишь для того, чтобы в дальнейшем продемонстрировать с помощью него работу некоторых команд и процедур, написание которых как раз и будет обусловлено наличием этого избыточного столбца. Отсюда вывод такие столбцы, содержащие расчетные данные, полученные на основании уже хранящихся в таблице данных, не следует включать в таблицы. Задание 9 Изменить длины полей в соответствии с таблицей. Выполнить анализ почему не удалось выполнить заданные операции с некоторыми столбцами? Что необходимо предпринять, чтобы эти изменения всё же произвести?
Удаление столбца Синтаксис команды:ALTER TABLE <имя таблицы>DROP COLOMN <имя столбца> Ранее уже отмечалось, что нельзя удалить столбец, если на него наложено хотя бы одно ограничение за исключением NULL. Кроме этого следует отметить, что нельзя удалить Реплицированные столбцы. Индексированные столбцы. Столбцы, для которых определено правило (rule). Это связано с тем, что все ограничения целостности, значения по умолчанию, индексы и правила хранятся в виде отдельных объектов базы данных и связываются со столбцом таблицы. Удаление столбцов без предварительного удаления объектов привело бы к появлению в базе данных несвязанных объектов, что способствовало бы ее засорению. Удаление таблицы при наличии на нее ссылок Таблица удаляется с помощью команды DROP. Одной командой можно удалить сразу несколько таблиц. Однако вы не сможете удалить таблицу, если на нее есть ссылки из других таблиц. То есть вам сначала придется удалить соответствующий внешний ключ в ссылочной таблице и только после этого выполнить команду DROP Синтаксис команды: DROP TABLE<имя таблицы>[, <имя таблицы>] Переименование таблицы Иногда приходится корректировать не только ограничения и структуру таблицы, но и ее имя. Для переименования таблиц используется системная хранимая процедура SP_RENAME. Однако следует быть предельно осторожным, так как в этом случае необходимо корректировать и все ограничения, ссылающиеся на эту таблицу. Синтаксис команды: EXEC SP_RENAME 'старое имя',' новое имя' ; Задание 10 Переименовать таблицу Progress в таблицу Progress1. Контрольные вопросы Каково основное назначение команды ALTER? Какие операции над ограничениями можно выполнить с помощью команды АLTER? Какие ограничения подлежат корректировке? Каковы правила назначения ограничения NULL/NOT NULL? Когда нельзя изменить ширину столбца в таблице? Какие существуют ограничения на изменения параметров столбца? Как можно отменить действие ограничения? Для столбцов с каким типом данных разрешены изменения их размера? Как можно удалить столбец, если на него наложено одно из ограничений? Как переименовать столбец в таблице? В каких случаях это возможно? В каких случаях используют отключение ограничений? Допустим, мы вводим новое ограничение и хотим, чтобы SQL проверил все существующие данные на соответствие этому ограничению, что следует для этого сделать? Каковы могут быть последствия временного отключения ограничений? Какова последовательность действий при модификации столбца, имеющего ограничение DEFAULT? Какое место в таблице занимает вновь создаваемый столбец? Возможно ли изменить порядок следования столбцов в таблице, не используя операцию удаления? Какие из ограничений не могут быть временно отключены? Требования к оформлению отчета Титульный лист. Название работы. Тему, цель и задание к работе. Снимки экрана (скриншоты) процесса разработки. Выводы (что узнали, где можно применить полученные знания). Ответы на контрольные вопросы. |