Практическая работа № 9. Проектирование и построение функциональной и концептуальной моделей выбранной предметной области (индивидуальный проект)

Цель: обучить студентов разработке, проектированию и построению функциональной и концептуальной моделей выбранной предметной области по индивидуальной тематике.

Результат: построенные функциональная и концептуальная модели проектного задания по индивидуальной тематике.

Задание.

• 1. Выберите предметную область.
• 2. Определите функции и потоки данных выбранной предметной области.
• 3. Постройте функциональную модель по выделенным функциям и потокам данных выбранной предметной области (модель должна состоять не менее чем из 5 диаграмм).
• 4. Идентифицируйте сущности построенной функциональной модели.
• 5. Определите атрибуты каждой сущности.
• 6. Установите связи между сущностями и атрибутами.
• 7. Нормализуйте модель.
• 8. Минимизируйте, если это необходимо, число полученных сущностей.
• 9. Постройте концептуальную схему выбранной предметной области (модель должна состоять не менее чем из 5 сущностей и связей между ними).

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

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

• 2. Какие потоки данных необходимо представить на уровне данных в функциональной модели, чтобы на их основе построить концептуальную модель?
• 3. Какие сущности были выделены на основе данных функциональной модели и какие типы связей были между ними установлены?
• 4. Что такое обеспечение целостности данных при построении концептуальной модели? Не нарушена ли она в вашей модели?
• 5. До какой нормальной формы была нормализована ваша концептуальная схема данных?

Практическая работа № 10.

Практическая работа №10. Создание базы данных «Домашние животные» (СУБД MySQL)

Цель: обучить студентов созданию SQL-запросов основных типов: создание таблицы, добавление записи в таблицу, обновление данных в таблице, удаление данных из таблицы, выборка данных из таблицы.

Результат: созданная база данных «Домашние животные», состоящая из двух таблиц и запросов на выборку (столбцов, строк, вычисление дат, сравнение по шаблонам, подсчёт количества строк, использование нескольких таблиц и др.).

Задание № 1. Подсоединение к серверу и отсоединение от него

При подключении к серверу с помощью mysql обычно нужно ввести имя пользователя MySQL и, в большинстве случаев, пароль. Если сервер запущен не на том компьютере, с которого вы вошли в систему, необходимо также указать имя хоста. Параметры соединения (соответствующее имя хоста, пользователя и пароль) вы сможете узнать у администратора. Получив соответствующие параметры, подсоединиться к серверу можно следующим образом:

shell> mysql -h host -u user -p

Enter password: ********

Символы ******** обозначают ваш пароль; введите его, когда mysql выведет на экран запрос Enter password:.

Если все сработает, на экране должна появиться следующая информация и метка командной строки mysql>:

shell> mysql -h host -u user -p

Enter password: ******** mysql>

Метка обозначает, что программа mysql готова к вводу команд.

После установки соединения можно в любой момент отключиться от сервера, набрав в командной строке mysql> команду quit: mysql> QUIT Bye

Отсоединиться от сервера можно и при помощи сочетания клавиш Control-D.

Задание № 2. Ввод простейших запросов без использования базы данных

Ниже приведена простая команда, запрашивающая у сервера информацию об его версии и текущей дате. Введите ее в командной строке mysql> и нажмите Enter: mysql> SELECT VERSION(), CURRENT_DATE;

Для ввода ключевых слов можно использовать любой регистр символов. Убедитесь, что приведенные ниже запросы абсолютно идентичны:

mysql> SELECT VERSION(), CURRENT_DATE; mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE;

Ниже демонстрируется использование mysql в качестве несложного калькулятора:

mysql> SELECT SIN(PI()/4), (4+1)*5;

Все команды, представленные выше, были относительно короткими и состояли из одной строки. В одну строку можно поместить и несколько команд. Но каждая из них должна заканчиваться точкой с запятой:

mysql> SELECT VERSION(); SELECT NOW();

В нижеследующей таблице 3.10.1 приведены все возможные варианта вида метки командной строки и соответствующие им состояния mysql:

Таблица 3.10.1

Обычно многострочные команды получаются случайно, когда хочешь создать обычную команду, но забываешь поставить завершающую точку с запятой. В таком случае mysql ожидает продолжения:

mysql> SELECT USER()

Метки ' > и " > используются при сборе строк. В MySQL строки можно заключать как в одинарные ('), так и в двойные (") кавычки (можно, например, написать 'hello' или "goodbye"), кроме того, mysql позволяет вводить строковые выражения, состоящие из нескольких строчек текста. Метка '> или "> обозначает, что вы ввели строку, открывающуюся символом кавычек (' или "), но еще не ввели завершающую строковое выражение закрывающую кавычку.

Знать значение меток ' > и " > необходимо, так как при вводе незавершенной строки все последующие строки будут игнорироваться mysql, включая строку с командой QUIT.

Задание 3. Проверка наличия баз данных на сервере, создание и выбор базы данных

1. Проверьте наличие каких-либо баз данных на сервере в настоящее время при помощи команды SHOW:

mysql> SHOW DATABASES;

На вашем компьютере список баз, вероятно, будет другим, но в нем все равно, скорее всего, будут присутствовать базы mysql 244 _______________________________________________________________________ и test. База данных mysql просто необходима, так как в ней она описываются пользовательские права доступа. База test часто применяется для экспериментов.

Если база данных test существует, попробуйте обратиться к ней:

mysql> USE test

Database changed

В команде USE, как и QUIT, точка с запятой не нужна (однако данные команды тоже можно завершать точкой с запятой). Команда USE отличается от остальных - она должна задаваться одной строкой.

2. Если администратор при выдаче разрешения создаст для вас базу, с ней можно сразу начинать работу. В противном случае вам придется создать ее самостоятельно:

mysql> CREATE DATABASE menagerie;

Создавать базу нужно только однажды, но выбирать ее приходится в каждом сеансе работы с mysql. Делать это можно с помощью команды USE, представленной выше. Можно также выбирать базу из командной строки при запуске mysql. Для этого достаточно лишь ввести ее имя после параметров соединения, которые нужно вводить в любом случае. Например: shell> mysql -h host -u user -p menagerie

Enter password: ********

Задание 4. Создание таблицы.

• 1. Убедитесь при помощи команды SHOW TABLES, что в Вашей базе пока нет таблиц: mysql> SHOW TABLES.
• 2. Создайте таблицу «pet», содержащую поля имя (name), владелец (owner), вид животного (species), пол (sex), дата ро-ждения (birth), дата_смерти (death). Пол животного будем обозначать буквами «ш» (мужской) и «f» (женский).

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(l), birth DATE, death DATE);

3. Проверьте наличие в базе данных таблицы pet с помощью команды SHOW TABLES;

4. Убедитесь в правильности создания таблицы при помощи команды DESCRIBE:

mysql> DESCRIBE pet;

Использовать команду DESCRIBE можно в любое время, например, если вы забудете имена столбцов или типы, к которым они относятся.

Задание 5. Загрузка данных в таблицу.

1. Заполните таблицу 3.10.2 следующими записями (обратите внимание: MySQL принимает даты в формате ГГГГ-ММ-ДД), создав текстовый файл.

Таблица 3.10.2

Так как первоначально таблица пуста, заполнить её проще всего, создав текстовый файл, содержащий по строке на каждое из животных, а затем загрузив его содержимое в таблицу одной командой. Создайте текстовый файл с именем «pet.txt», содержащий по одной записи в каждой строке (значения столбцов должны быть разделены символами табуляции и даны в том порядке, который был определен командой CREATE TABLE). Незаполненным полям (например, неизвестный пол или даты смерти живых на сегодняшний день животных), можно присвоить значение NULL. В текстовом файле это значение представляется

Практическая работа № 10. Создание базы данных «Домашние животные» (СУБД MySQL) символами N. Например, запись для птицы Whistler должна выглядеть примерно так (таблица 3.10.3) (между значениями должны располагаться одиночные символы табуляции):

Таблица 3.10.3

Загрузить файл pet. txt в таблицу можно с помощью следующей команды:

mysql> LOAD DATA LOCAL INFILE "C:pet.txt" INTO TABLE pet;

2. Добавьте одиночную запись в таблицу. Предположим, Диана (Diane) купила хомячка по имени Puffball. Соответствующую запись в таблицу можно внести с помощью команды INSERT:

mysql> INSERT INTO pet

-> VALUES ('Puffball', 'Diane', 'hamster', 'f', '2009-03-30', NULL);

Обратите внимание на то, что здесь строковые выражения и даты представлены в виде ограниченных кавычками строк. Кроме того, в команде INSERT отсутствующие данные можно прямо заменять на NULL (вместо N как в команде LOAD DATA).

Задание 6. Выборка всех данных из таблицы, проверка и исправление ошибок

1. Просмотрите все данные в таблице pet:

mysql> SELECT * FROM pet;

Убедитесь, что дата рождения и смерти для собаки Bowser введены неверно.

2. Исправьте ошибку, отредактировав файл pet.txt (ввести произвольное подходящее по смыслу значение даты рождения), очистив таблицу и снова заполнив ее:

mysql> DELETE FROM pet;

mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

Убедитесь, что запись о Puffball отсутствует, введите её снова.

3. Исправьте только ошибочно введенные данные при помощи команды UPDATE:

mysql> UPDATE pet SET birth = "2008-08-31" WHERE name = "Bowser";

Задание 7. Выборка определенных строк из таблицы

1. Проверьте правильность внесенных в дату рождения собаки Bowser изменений:

mysql> SELECT * FROM pet WHERE name = "Bowser";

В операции сравнения строк обычно не учитывается регистр символов, так что имя можно записать как «bowser», «BOWSER» и т.п. Убедитесь, что результаты запросов будут идентичными.

2. В условиях может указываться любой из столбцов, а не только name. Узнайте, какие их животных родились после 2008 года:

mysql> SELECT * FROM pet WHERE birth >= "2008-1-1";

3. Создайте запрос, комбинируя несколько условий для выделения всех собак женского пола (с использованием оператора AND):

mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";

4. Создайте запрос, комбинируя несколько условий для выделения всех змей или птиц (с использованием оператора OR): mysql> SELECT * FROM pet WHERE species = "snake"

OR species = "bird";

5. Используйте операторы AND и OR совместно для выявления всех кошек женского пола и всех собак мужского (скобки указывают на порядок группировки условий):

mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m") OR (species = "dog" AND sex = "f");

Задание 8. Выборка определённых столбцов

• 1. Создайте запрос, позволяющий узнать, когда родились животные: mysql> SELECT name, birth FROM pet;
• 2. Получите имена владельцев животных можно с помощью следующего запроса:

mysql> SELECT owner FROM pet;

3. Сократите количество выводимых строк, воспользовавшись ключевым словом DISTINCT (в этом случае будут выводиться только уникальные записи):

mysql> SELECT DISTINCT owner FROM pet;

4. Используйте выражение WHERE для комбинирования выбора строк и столбцов: загрузите даты рождения только кошек и собак:

mysql> SELECT name, species, birth FROM pet WHERE species = "dog" OR species = "cat";

Задание 9. Сортировка строк

1. Отсортируйте даты рождения животных с помощью команды ORDER BY:

mysql> SELECT name, birth FROM pet ORDER BY birth;

2. Для сортировки в обратном порядке к имени столбца следует добавить ключевое слово DESC (по убыванию):

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

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

mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

Обратите внимание: действие ключевого слова DESC распространяется только на тот столбец, имя которого располагается в запросе прямо перед этим словом (в данном случае - birth); значения поля «species» по-прежнему отсортированы в возрастающем порядке.

Задание 10. Вычисление дат

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

mysql> SELECT name, birth, CURRENT_DATE,

• -> (YEAR(CURRENT—DATE)-YEAR(birth))
• -> - (RIGHT(CURRENT—DATE,5)
• -> AS age FROM pet;

В этом примере функция YEAR() выделяет из даты год, a RIGHTO - пять крайних справа символов, представляющих календарный день (MM-DD). Часть выражения, сравнивающая даты, выдает 1 или 0, что позволяет уменьшить результат на единицу, если текущий день (CURRENT—DATE) находится к началу календаря ближе, нежели день рождения животного. Вместо этого выражения в заголовке соответствующего столбца результатов будем выводить псевдоним (аде - «возраст»).

• 2. Отсортируйте полученный результат по имени: mysql> SELECT name, birth, CURRENT_DATE,
• -> (YEAR(CURRENT_DATE)-YEAR(birth))
• -> - (RIGHT(CURRENT_DATE,5)
• -> AS age
• -> FROM pet ORDER BY name;
• 3. Отсортируйте полученный результат по возрасту: mysql> SELECT name, birth, CURRENT—DATE,
• -> (YEAR(CURRENT_DATE)-YEAR(birth))
• -> - (RIGHT(CURRENT—DATE,5)
• -> AS age
• -> FROM pet ORDER BY age;
• 4. Определите возраст, которого достигли умершие животные на момент смерти. Выделить умерших животных можно, проверив значение поля death на предмет равенства NULL. Затем для записей, значения поля death которых не равно NULL, можно вычислить разницу между датами смерти и рождения: mysql> SELECT name, birth, death,

• -> (YEAR(death)-YEAR(birth)) -(RIGHT(death,5)
• -> AS age
• -> FROM pet WHERE death IS NOT NULL ORDER BY age;
• 5. Убедитесь, что в этом запросе вместо выражения death IS NOT NULL не может быть использовано death <> NULL, так как NULL - особое значение.
• 6. Определите, у каких животных дни рождения наступят в следующем месяце. Для таких расчетов день и год значения не имеют; из столбца, содержащего дату рождения, нас интересует только месяц. В MySQL предусмотрено несколько функций для получения частей дат - YEAR () , MONTH () , и DAYOFMONTH () . Увидеть работу функции MONTH () можно с помощью простого запроса, выводящего дату рождения birth и MONTH (birth) : mysql> SELECT name, birth, MONTH(birth) FROM pet;

Найдём животных, дни рождения которых наступят в следующем месяце. Предположим, что сейчас апрель. Тогда номер текущего месяца — 4, а искать надо животных, родившихся в мае (5-м месяце), таким образом: mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

Однако в декабре возникают некоторые осложнения. Если просто добавить единицу к номеру месяца (12) и поискать животных, родившихся в тринадцатом месяце, найти ничего не удастся. Вместо этого нужно искать животных, родившихся в январе (1-м месяце).

Создадим запрос, который будет работать вне зависимости от того, какой сейчас месяц. Функция DATE_ADD () позволяет прибавить к дате некоторый интервал времени. Если добавить к значению, возвращаемому функцией NOW (), месяц, а затем извлечь из получившейся даты номер месяца при помощи функции MONTH (), мы получим именно тот месяц, который нам нужен: mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));

Ту же задачу можно решить и другим методом. Для этого нужно прибавить единицу к номеру месяца, следующего за текущим (воспользовавшись функцией расчета по модулю (MOD) для перехода к 0, если номер текущего месяца равен 12):

mysql> SELECT name, birth FROM pet

-> WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;

Функция MONTH возвращает число от 1 до 12, а выражение MOD (число, 12) - число от 0 до 11. Поэтому операцию сложения нужно проводить после MOD(), иначе результат «перепрыгнет» с ноября (11) сразу на январь (1).

Задание 11. Сравнение по шаблонам

• 1. Найдите все имена, начинающиеся с «Ь»: mysql> SELECT * FROM pet WHERE name LIKE "b%";
• 2. Найдите все имена, заканчивающиеся на «f у»: mysql> SELECT * FROM pet WHERE name LIKE "%fy";
• 3. Найдите все имена, содержащие «w»:

mysql> SELECT * FROM pet WHERE name LIKE "%w%";

4. Найдите все имена, содержащие ровно пять символов, при помощи шаблонного символа «_»:

mysql> SELECT * FROM pet WHERE name LIKE "";

Задание 12. Подсчет строк

1. Узнайте при помощи функции COUNT (), сколько животных принадлежит каждому из владельцев:

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

2. Обратите внимание на использование команды GROUP BY для объединения всех записей по каждому из владельцев. Убедитесь, что без этой команды запрос выдал бы только сообщение об ошибке:

mysql> SELECT owner, COUNT(owner) FROM pet;

3. Подсчитайте количество животных каждого вида: mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;

• 4. Подсчитайте количество животных каждого пола: mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
• (в этой таблице результатов NULL обозначает, что пол животного неизвестен)
• 5. Подсчитайте количество животных каждого вида с учетом пола:

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

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

mysql> SELECT species, sex, COUNT(*) FROM pet

• -> WHERE species = "dog" OR species = "cat"
• -> GROUP BY species, sex;
• 7. Узнайте количество животных каждого пола с учетом только тех экземпляров, пол которых известен:

mysql> SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL

-> GROUP BY species, sex;

Задание 13. Использование нескольких таблиц

• 1. Создайте таблицу событие (event), содержащую дополнительную информацию о животных (записи о событиях наподобие посещения ветеринара или рождения потомства). Эта таблица должна содержать:
  • - имена животных, чтобы знать, к какому животному относится какое событие;
  • - дату события;
  • - поле для описания события;
  • - поле, отражающее тип события, для распределить их по категориям.

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,

-> type VARCHAR(15), remark VARCHAR(255));

Как и в случае с таблицей pet, начальные данные в таблицу проще всего загрузить, создав текстовый файл с информацией, разделенной символами табуляции (таблица 3.10.4):

Таблица 3.10.4

Создайте соответствующий запрос.

2. Узнайте, в каком возрасте животные давали приплод. В таблице event указаны даты рождения потомства, но для того, чтобы рассчитать возраст матери, нужно знать и дату её рождения. Так как даты рождения хранятся в таблице pet, в этом запросе нужно использовать обе таблицы:

mysql> SELECT pet.name,

• -> (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark FROM pet, event
• -> WHERE pet.name = event.name AND type = "litter";

Для объединения не обязательно иметь две отдельные таблицы; иногда можно объединить таблицу с самой собой - если нужно сравнить одни записи таблицы с другими записями той же таблицы. Например, для того, чтобы обнаружить среди жи-254 _______________________________________________________________________ вотных «семейные пары», можно объединить таблицу pet с ней самой, составив пары животных разного пола, но одного вида:

mysql> SELECT pl.name, pl.species

• -> FROM pet AS pl,
• -> WHERE pl.species

AND p2.sex = "m";

pl.sex, p2.name,

pet AS p2

= p2.species AND

р2.sex,

pl.sex =

В этом запросе мы указываем псевдонимы имен таблицы, для обращения к столбцам и определения, к какой из таблиц относится каждая ссылка на столбец.

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

• 1. Какие основные типы данных существуют в SQL?
• 2. Какие основные функции SQL-запросов существуют?
• 3. С помощью какого служебного слова осуществляется сортировка данных в SQL?
• 4. Каков синтаксис записи формата дат в SQL?
• 5. Что такое шаблоны в SQL?

Практическая работа № 11.