Создайте несколько (не менее 2) триггеров для вашей базы данных. Например, с помощью триггеров вы можете реализовать каскадную политику ссылочной целостности.
Приложение 1. Работа с ERModeler
Программа ERModeler разработана одним из авторов пособия специально для данного курса. Загрузить программу можно по адресу:
http://kek.ksu.ru/EOS/BD/ERModeler.zip .
После загрузки следует распаковать программу в произвольный каталог. Файл для запуска – ERModel.exe. Программа представляет собой исполняемый файл .NET, поэтому для функционирования программы необходимо, чтобы на компьютере был установлен .NET Framework версии 2.0 или выше.
Краткое описание функций программы содержится в справочной системе, в секции «Часто задаваемые вопросы»:
Как создать сущность?
Для создания сущности щелкните левой кнопкой мыши в любом месте рабочего поля. Эту настройку можно отключить через меню "Настройки".
Как изменить имя сущности?
Для того чтобы изменить имя сущности, выполните двойной щелчок мышью по этой сущности. Появится текстовое поле, в котором можно изменить имя сущности. Для подтверждения нового имени нажмите "Enter". Обратите внимание, что имя сущности не может быть длиннее 10 букв.
Можно ли перемещать сущность по рабочему полю?
Как сущности, так и атрибуты, и связи можно перетаскивать по рабочему полю. Для этого следует щелкнуть по объекту левой кнопкой мыши и, не отпуская кнопки, переместить объект в другое место. Обратите внимание, что сущность перемещается вместе со своими атрибутами.
Как создать атрибут сущности?
Для создания атрибута сущности сначала выделите сущность, т.е., щелкните на ней левой кнопкой мыши. Выделенная сущность будет отмечена черными квадратиками по углам. Теперь выберите пункт меню "Сущность" – "Новый атрибут". Атрибут будет добавлен над сущностью. Его можно передвинуть в любое место.
Как изменить имя атрибута?
Для того чтобы изменить имя атрибута, выполните двойной щелчок мышью по этому атрибуту. Появится текстовое поле, в котором можно изменить имя атрибута. Для подтверждения нового имени нажмите "Enter". Обратите внимание, что имя атрибута не может быть длиннее 10 букв. Другой способ изменить имя атрибута – использование диалогового окна "Свойства атрибута". Оно вызывается по щелчку правой кнопкой мыши на атрибуте.
Как изменить тип и длину атрибута?
Для того чтобы изменить тип и длину атрибута, используйте диалоговое окно "Свойства атрибута". Оно вызывается по щелчку правой кнопкой мыши на атрибуте.
Как создать ключевое поле?
Для того чтобы создать ключевое поле, используйте диалоговое окно "Свойства атрибута". Оно вызывается по щелчку правой кнопкой мыши на атрибуте. Название ключевого поля в модели будет подчеркнуто.
Как создать связь между сущностями?
Для создания связи между сущностями используйте диалоговое окно "Свойства связи". Оно вызывается через пункт меню "Связь" – "Новая связь". В этом окне выбирайте сущности и тип связи между ними. Можно создавать только бинарные связи.
Как изменить тип связи?
Для изменения типа связи используйте то же самое диалоговое окно "Свойства связи". Его можно вызвать, щелкнув правой кнопкой мыши на связи.
Как изменить название связи?
Для изменения названия связи используйте то же самое диалоговое окно "Свойства связи". Его можно вызвать, щелкнув правой кнопкой мыши на связи.
Как создать атрибут связи?
Для создания атрибута связи сначала выделите связь, т.е., щелкните на ней левой кнопкой мыши. Выделенная связь будет отмечена черными квадратиками по углам. Теперь выберите пункт меню "Связь" – "Новый атрибут". Атрибут будет добавлен над связью. Его можно передвинуть в любое место.
Как удалить сущность?
Для удаления сущности сначала выберите ее, а затем используйте пункт меню "Сущность" – "Удалить сущность".
Как удалить атрибут?
Для удаления атрибута сущности или связи сначала выберите атрибут, а затем используйте пункт меню "Сущность" – "Удалить атрибут" или "Связь" – "Удалить атрибут".
Как удалить связь?
Для удаления связи сначала выберите ее, а затем используйте пункт меню "Связь" – "Удалить связь".
Как сохранить ER-модель для дальнейшей работы?
В пункте меню "Сохранить как..." можно использовать два формата – XML и двоичный файл. Преимущество файла XML в том, что его можно прочитать в текстовом редакторе или браузере. Преимущество двоичного формата в том, что получаются более короткие файлы.
Как преобразовать ER-модель в графический файл?
В пункте меню "Сохранить как..." есть возможность сохранить модель в виде PNG-файла.
Заметим также, что в окне настроек данной программы можно управлять некоторыми параметрами интерфейса ER-модели:
Обратите внимание, что цвета сущностей, атрибутов и связей сохраняются не в файле модели, а в реестре Windows (т.е., они связаны с определенным компьютером).
Приложение 2. Некоторые типичные ошибки SQL
При отладке программ неизбежно обнаруживаются разнообразные ошибки. Рассмотрим некоторые типичные ситуации при работе в SQL Management Studio.
Команда:
SELCT * FROM k_bill
Ошибка:
Msg 102, Level 15, State 1, Line 1
Неправильный синтаксис около конструкции "*".
Объяснение:
Синтаксическая ошибка, пропущена буква в слове SELECT.
Команда:
INSERT INTO k_firm (firm_name, firm_addr)
VALUES(10, 'Сигма', 'Киев');
Ошибка:
Msg 110, Level 15, State 1, Line 1
Число столбцов в инструкции INSERT меньше числа значений, указанных в предложении VALUES. Число значений в предложении VALUES должно соответствовать числу столбцов, указанному в инструкции INSERT.
Объяснение:
В команде вставки в списке полей перечислены два поля, а в списке значений – три значения.
Команда:
INSERT INTO k_firm (firm_num, firm_name, firm_addr)
VALUES(10, 'Сигма', 'Киев');
Ошибка:
Msg 544, Level 16, State 1, Line 1
Невозможно вставить явное значение для столбца идентификаторов в таблице "k_firm", когда параметр IDENTITY_INSERT имеет значение OFF.
Объяснение:
По умолчанию нельзя указывать явное значение для поля, у которого установлено свойство IDENTITY, т.е., для поля firm_num. Если требуется явно указывать значения для таких полей, следует предварительно выполнить команду:
Конфликт инструкции INSERT с ограничением FOREIGN KEY "fk_staff_dept_num". Конфликт произошел в базе данных "kontora", таблица "dbo.k_dept", column 'dept_num'.
Выполнение данной инструкции было прервано.
Объяснение:
Нарушено ограничение внешнего ключа: мы пытаемся вставить ссылку на несуществующий отдел с номером 4.
Команда:
DELETE FROM k_contract WHERE contract_num=1
Ошибка:
Msg 547, Level 16, State 0, Line 1
Конфликт инструкции DELETE с ограничением REFERENCE "fk_bill_contract_num". Конфликт произошел в базе данных "kontora", таблица "dbo.k_bill", column 'contract_num'.
Выполнение данной инструкции было прервано.
Объяснение:
Нарушено ограничение внешнего ключа: мы пытаемся удалить договор с номером 1, а к этому договору привязаны счета в таблице k_bill.
Команда:
SELECT * FROM k_contract
WHERE contract_date BETWEEN '01/03/2012' AND '31/03/2012'
Ошибка:
Msg 242, Level 16, State 3, Line 1
Преобразование типа данных char в тип данных datetime привело к значению datetime за пределами диапазона.
Объяснение:
Видимо, на компьютере установлен другой формат даты.
Если вы хотите задать определенный формат даты, например, день:месяц:год, выполните команду:
SET DATEFORMAT dmy
Команда:
SELECT price_name, MIN(price_sum) FROM k_price
Ошибка:
Msg 8120, Level 16, State 1, Line 1
Столбец "k_price.price_name" недопустим в списке выбора, поскольку он не содержится ни в статистической функции, ни в предложении GROUP BY.
Объяснение:
Если используются агрегирующие функции без группировки, в списке полей могут присутствовать только агрегирующие функции.
Если в нескольких таблицах, используемых в запросе, есть поля с одинаковыми названиями, то для обращения к таким полям следует использовать синтаксис имя_таблицы.имя_поля или псевдоним.имя_поля.
Команда:
SELECT contract_num, contract_date FROM k_contract
WHERE contract_num =
(SELECT contract_num FROM k_bill
WHERE bill_date
BETWEEN '01/01/2012' AND '12/31/2012'
AND k_contract.contract_num=k_bill.contract_num)
Ошибка:
Msg 512, Level 16, State 1, Line 1
Вложенный запрос вернул больше одного значения. Это запрещено, когда вложенный запрос следует после =, !=, <, <=, >, >= или используется в качестве выражения.
Объяснение:
Нельзя использовать обычные операции сравнения с подзапросом, если подзапрос возвращает несколько строк. Следует использовать ключевые слова ALL или ANY.
Команда:
INSERT INTO k_dept (dept_short_name, dept_full_name)
VALUES('Служба безопасности', 'Отдел №1');
Ошибка:
Msg 8152, Level 16, State 14, Line 1
Символьные или двоичные данные могут быть усечены.
Выполнение данной инструкции было прервано.
Объяснение:
Длина значения в строковом поле не должна превышать длину поля, заданную при создании таблицы.
Приложение 3. Реляционная алгебра и SQL
Рассмотрим, как связаны операции реляционной алгебры и язык SQL, т.е. приведем примеры запросов SQL, аналогичных операциям реляционной алгебры. В качестве примера базы данных будем использовать «Музыкантов».
Операция проекции projвыражается через SELECT с ключевым словом DISTINCT.
Получить все названия ансамблей:
proj НазАнс (Ансамбли)
SELECT DISTINCT НазАнс FROM Ансамбли
Операция выбора sel выражается через SELECT с ключевым словом WHERE.
Получить данные об ансамблях из России:
sel СтрАнс='Россия' (Ансамбли)
SELECT * FROM Ансамбли WHERE СтрАнс='Россия'
Условия также могут быть и сложными.
Получить имена музыкантов, родившихся в 20-м веке
SELECT ИмяМуз FROM Музыканты WHERE ДатаРожд>'31.12.1900' AND ДатаРожд<'01.01.2001'
Операция соединения таблиц join может быть выражена несколькими способами.
Получить имена композиторов:
proj ИмяМуз (Музыканты join Сочинения)
Можно использовать связь таблиц через условие WHERE:
SELECT DISTINCT ИмяМуз FROM Музыканты М, Сочинения С WHERE С.НомМуз=М.НомМуз
Можно использовать более современный синтаксис JOIN ... ON
SELECT DISTINCT ИмяМуз FROM Музыканты М JOIN Сочинения С ON С.НомМуз=М.НомМуз
Если требуется вывести данные из одной таблицы, а условие накладывать на другую таблицу, то удобно использовать подзапросы, связанные и несвязанные.
SELECT DISTINCT ИмяМуз FROM Музыканты WHERE НомМуз IN
(SELECT НомМуз FROM Сочинения)
или
SELECT DISTINCT ИмяМуз FROM Музыканты WHERE НомМуз = Any
(SELECT НомМуз FROM Сочинения)
или
SELECT DISTINCT ИмяМуз FROM Музыканты М WHERE EXISTS
(SELECT * FROM Сочинения С WHERE
С.НомМуз=М.НомМуз)
Приведем пример сложного запроса, использующего данные из всех 6 таблиц базы данных.
Получить названия ансамблей, которые играли Моцарта на саксофоне:
