Материал: Аксенов_методичка

а затем, опять в скобках, – список значений, которые подлежат добавлению. Порядок значений должен строго соответствовать порядку колонок в таблице.

3.1.2. Общие сведения об SQLite

SQLite – легковесный реляционный движок; представляет собой встраиваемую в приложение библиотеку, и так же как Microsoft Jet, не работает по схеме «клиент-сервер». В отличие от Microsoft Jet, который используется только в Access, SQLite широко используется в прикладных приложениях, в том числе на мобильных платформах, в качестве средства хранения данных. С учетом легкости установки, отсутствия необходимости настройки и неплохой поддержки стандарта SQL, данная СУБД хорошо подходит для использования в учебных целях.

3.2. Рекомендации по выполнению лабораторной работы

Лабораторную работу рекомендуется выполнять в программе SQLiteSpy, но допустимо использование других клиентов.

Особенностью движка SQLite является выключенное по умолчанию ограничение ссылочной целостности (не производится форсирование внешних ключей). Это объясняется стремлением сохранить обратную совместимость с приложениями, написанными для более старых версий библиотеки (поддержка внешних ключей была реализована в SQLite начиная с версии 3.6.19 в 2009 году). В академических целях необходимо в самом начале скрипта принудительно включить форсирование внешних ключей командой

PRAGMA foreign_keys = ON;

Скрипт лабораторной работы представляет собой одиночный текстовый файл, структурированный следующим образом:

– команда включения внешних ключей; – команды очистки схемы данных (DROP TABLE);

– команды создания схемы данных (CREATE TABLE); – команды заполнения базы данных данными (INSERT). Таблицы желательно заполнять данными с учетом ограничений

ссылочной целостности, т.е. сначала главные, а затем подчиненные. Иными словами, вначале заполняются данными таблицы, на которые указывают ссылки, а затем – таблицы, которые содержат ссылки. В этом случае ссылки при добавлении строк в подчиненные таблицы будут валидными и не вызовут нарушений целостности.

16

Команды очистки схемы данных необходимы для удобства исполнения скрипта целиком в случае, если в схему данных были внесены изменения и надо перегенерировать ее. При этом нужно помнить, что если в таблицах уже содержатся данные, при удалении таблиц будут форсироваться ограничения ссылочной целостности (не будет позволено удалить таблицу, если существуют данные, которые ссылаются на строки из удаляемой таблицы). Чтобы гарантированно перегенерировать схему и данные, следует придерживаться определенного порядка при удалении таблиц, а именно противоположного их созданию и заполнению: сначала имеет смысл удалять подчиненные таблицы, содержащие ссылки, и лишь затем – главные таблицы, на которые те ссылались.

Перед сдачей лабораторной работы следует убедиться, что скрипт исполняется полностью многократно и не вызывает ошибок.

3.3. Индивидуальные варианты

Номер варианта соответствует порядковому номеру в журнале сдачи лабораторных работ.

Индивидуальные варианты соответствуют вариантам ЛР1.

3.4. Порядок выполнения

1. Реализовать реляционную схему, полученную в ЛР1, операторами DDL языка SQL в SQLite (с помощью SQLiteSpy или другого приложения, использующего движок SQLite).

2. Командой PRAGMA включить проверку ограничения ссылочной целостности.

3. Осуществить проверку ограничений в соответствии с индивидуальным вариантом ЛР2 средствами языка SQL.

4. Операторами INSERT языка SQL заполнить базу данных данными, позволяющими удостовериться в работоспособности ограничений реляционной модели и пользовательских ограничений.

5. Оформить отчет.

3.5. Содержание отчета

1. Титульный лист. 2. Цель работы.

3. Индивидуальный вариант задания.

4. Снимок экрана реляционной схемы в Microsoft Access.

17

5. Листинг скрипта SQL создания и заполнения базы данных. 6. Выводы.

Примечание: листинг скрипта должен быть приведен с использованием моноширинного шрифта для удобочитаемости.

3.6. Контрольные вопросы

1. Объясните синтаксис и семантику оператора CREATE TABLE. 2. Объясните синтаксис и семантику оператора INSERT.

3. В чем проявляется то, что SQLite проверяет ссылочную целостность данных?

4. В чем отличие двух потенциальных ключей a и b от составного потенциального ключа (a, b)?

5. В чем отличие двух внешних ключей a и b от составного внешнего ключа (a, b)?

6. Объясните принцип работы ограничения X.

18

Лабораторная работа № 4. СОЗДАНИЕ ПРЕДСТАВЛЕНИЙ

НА ОСНОВАНИИ ЗАПРОСОВ НА ВЫБОРКУ В СУБД SQLITE. ЗАПРОСЫ НА МОДИФИКАЦИЮ И УДАЛЕНИЕ ДАННЫХ В СУБД SQLITE

Цель работы

Освоение синтаксиса операторов SELECT, UPDATE и DELETE при выполнении запросов на выборку, модификацию и удаление данных. Приобретение навыков использования средств языка SQL для выполнения типовых запросов к данным в реляционной модели. Знакомство с принципами работы представлений в реляционных СУБД.

4.1. Теоретические сведения

Для выполнения запросов на выборку данных из базы используется оператор SELECT. Его синтаксис таков:

SELECT список колонок результата FROM список таблиц

WHERE условие отбора для строк

GROUP BY список колонок для группировки

HAVING условие отбора для групп строк

ORDER BY порядок сортировки

LIMIT количество строк в результате запроса

Из 7 секций оператора SELECT обязательной является только секция SELECT. При этом при наличии остальных секций, они должны идти в указанном порядке и не повторяться.

Выполнение запроса происходит в другом порядке, нежели его запись.

1) декартово произведение таблиц, указанных в секции FROM; 2) фильтрация строк удовлетворяющих условию, указанному

в секции WHERE;

3) группировка строк, имеющих одинаковое значение в колонке, указанной в GROUP BY;

4) отбор полученных групп в соответствии с условием в секции

HAVING;

5) сортировка полученных строк / групп строк в соответствии с указаниями секции ORDER BY;

6) отбор только верхние строки результата, количество которых указано в LIMIT;

7) отбор колонок, указанных в секции SELECT.

19

Для изменения данных используется оператор UPDATE. После ключевого слова UPDATE указывается имя таблицы, подлежащей изменению, затем – ключевое слово SET, после которого через запятую названия изменяемых колонок и после знака «=» значения, которые в них записываются. Если необходимо изменить только некоторые строки, то указывается условие фильтрации строк после слова WHERE. В противном случае изменяются все строки таблицы.

Для удаления строк из таблицы используется оператор DELETE. Если не указано слово WHERE и условие отбора удаляемых строк, то удаляются все строки таблицы. В противном случае удаляются только строки, соответствующие указанному условию.

4.2 Рекомендации по выполнению лабораторной работы

Лабораторную работу рекомендуется выполнять в программе SQLiteSpy, но допустимо использование других клиентов.

Скрипт лабораторной работы представляет собой одиночный текстовый файл, структурированный следующим образом:

– команды удаления представлений (DROP VIEW); – команды создания представлений (CREATE VIEW);

– команда на модификацию данных (UPDATE, должна быть закомментирована);

– команда на удаление данных (DELETE, должна быть закомментирована).

Команда создания представления должна сопровождаться SQLкомментарием, объясняющим назначение этого представления.

Следует отметить, что представление должно создаваться одиночным SQL-запросом (при этом допускается использование вложенных запросов) и не обращаться к другим представлениям в схеме. И вообще, создание иных представлений помимо тех, которые фигурируют в задании, должно быть согласовано с преподавателем.

Команды модификации и удаления данных следует закомментировать, чтобы они не выполнялись при выполнении скрипта целиком.

Перед сдачей лабораторной работы следует убедиться, что скрипт исполняется полностью многократно и не вызывает ошибок.

4.3. Индивидуальные варианты

Индивидуальные задания можно получить у преподавателя.

20