Главная Учебники - Разные Лекции (разные) - часть 23
|
Федеральное агентство по образованию Государственное образовательное учреждение высшего профессионального образования «ТОМСКИЙ ПОЛИТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ
» Утверждаю Декан (директор) С. А. Гайворонский « » 2009 г. А.А. Пономарев
Создание структуры БД и манипулирование данными в СУБД MySQL
Методические указания к выполнению лабораторных работ по курсу «Базы данных» для студентов III курса специальностей 230105 «Программное обеспечение вычислительной техники и автоматизированных систем». 230201 «Информационные системы и технологии». Издательство Томского политехнического университета 2009 УДК 681.324.016 ББК 00000 П00 Пономарев А.А.
П00 Базы данных: методические указания по выполнению лабораторного практикума для студентов специальностей 230105 «
Программное обеспечение вычислительной техники и автоматизированных систем»
, 230201 «
Информационные системы и технологии».
В трех частях, часть первая. /Сост. А.А. Пономарев. – Томск: Изд-во ТПУ, 2009. – 80 с. УДК 681.324.016
ББК 00000
Методические указания рассмотрены и рекомендованы « 29
» октября
2009 г. Зав. кафедрой АиКС доктор технических наук __________Г.П. Цапко
Председатель учебно-методической комиссии __________Е.А. Кочегурова
Рецензент
Доктор технических наук, профессор Д.Г. Копаница
© Пономарев А.А., 2009
©Оформление. Издательство Томского Оглавление Лабораторная работа №2. Моделирование баз данных средствами Erwin. 16
Лабораторная работа №3. Создание баз данных и таблиц в среде MYSQL. Информационное наполнение. 26
Лабораторная работа №4 Создание запросов и модификация таблиц базы данных. 35
Лабораторная работа №5. Работа с внешними базами данных. 46
Лабораторная работа №6. Представления, хранимые процедуры, функции, триггеры. 51
Хранимые процедуры и функции. 52
Цель работы:
Ознакомиться с приложениями, включенными в состав СУБД MySQL. Получить навыки управления учетными записями пользователей и определения привилегий. Ознакомиться с утилитами, входящими в состав СУБД MySQL, получить навыки работы с ними. Запуск MySQL
Управление сервером обычно осуществляется из командной строки. Запуск в Windows 95/98/2000/XP осуществляется через сеанс DOS выполнением следующей команды: D:\usr\local\Mysql\bin\mysqld --standalone Эта команда запустит демон mysql в фоновом режиме. В Windows 95/98 не предусмотрен запуск mysqld в виде службы. В Windows 2000 демон mysql запускается в виде службы. Можно осуществить запуск winmysqladmin.exe, в этом случае все настройки перечисляются в файле my.ini При запуске mysqld можно указывать следующие опции: Таблица 1- Опции команды MySQLD -?, --help Справка -b, --basedir=[path] Путь к каталогу в котором установлен mysql -h, --datadir [homedir] Путь к каталогу, в котором хранятся базы данных. -l, --log=[filename] Имя журнала транзакций -L, --language=[language] Язык по умолчанию(обычно English). -P, --port=[port] Порт для соединения. --skip-grant-tables Игнорировать таблицы привилегий. Это дает любому ПОЛНЫЙ доступ ко всем таблицам. Не следует предоставлять обычным пользователям разрешений на запуск mysqld. --skip-name-resolve Позволяет предоставлять доступ только тем хостам, чьи IP-адреса указаны в таблицах привилегий. Ипользуется для более высокого уровня защиты. --skip-networking Использовать подключения только через интерфейс localhost. -V, --version Вывести информацию о версии. Наличие в статусной строке иконки светофора с активным зеленым цветом указывает на то, что сервер запущен (см. рис 1).
Рисунок 1 - Приложение winmysqladmin запущено Теперь можно попытаться войти в сервер. В случае, если предполагается управление сервером через консоль, то необходимо использовать команду mysql
. Изначально существует единственный пользователь, которому предоставляется право входа - root
, которая не имеет пароля. Первое, что нужно сделать войти под именем root
и зарегистрировать нового пользователя и установить для него пароль. Команда mysql
может использовать следующие опции: Таблица 2 - Опции команды MySQL Справка Имя сервера mysql. Имя пользователя для доступа к mysql. Пароль пользователя для доступа к mysql. Порт для соединения с сервером. Информация о версии Примечание
.
Команды mysqld и mysql имеют еще некоторые опции, но в данный момент они особого интереса не представляют. Запуск из сеанса ДОС осуществляется как показано на Рисунок 2 (в указанном случае осуществляется подключение к БД mysql).
Рисунок
2 - Запуск консоли MYSQL Для выполнения в строке наберите команду: mysql –u root
Рисунок 3 - Успешный запуск консоли Если вы это получили, значит вы успешно вошли в консоль mysql, которая используется для администрирования сервера. Для составления отчета вам понадобятся приведение команд, которые вы будете посылать на сервер. В MySQL имеется возможность ведение протокола выполняемых команд, чтобы запустить ведение протокола необходимо выполнить команду \T filename
!!! обязательно в верхнем регистре. Filename – имя файла, в который будут записываться команды (создается автоматически при выполнении команды, и действует во время жизни сеанса, т.е. в случае отключения от сервера лог прерывается и для возобновления необходимо повторить команду с выводом в новый файл, так как команда затирает имеющиеся в файле данные). Просмотр списка БД, доступных на сервере осуществляется командой SHOW DATABASES.
Для выполнения в строке наберите команду: show databases.
Командой: USE
MYSQL
;
– выбираем текущую БД где MYSQL имя БД. Система привилегий и безопасность в MySQL
· User · Db · Host · Пользовательские привилегии База данных mysql и таблицы привилегий.
Итак, вы успешно вошли в базу данных mysql, которая используется для администрирования сервера. Что же здесь находится? А находятся здесь 5 таблиц, которые ничем не отличаются от других таблиц баз данных, за исключением того, что эти таблицы используются для предоставления доступа к базам данных и таблицам в них пользователям. Рассмотрим каждую из них. Введите следующую команду, show tables
,
которая покажет таблицы в базе данных mysql. Кратко рассмотрим функции каждой из таблиц: Определяет, разрешено ли пользователю, пытающемуся подключиться к серверу делать это. Содержит имя пользователя, пароль а также привилегии. Если ввести команду show columns from user; то получим следующее: Таблица
3- Структура таблицы User Field
Type
Null
Key
Default
Extra
Host char(60) PRI User char(16) PRI Password char(41) Select_priv enum('N','Y') N Insert_priv enum('N','Y') N Update_priv enum('N','Y') N Delete_priv enum('N','Y') N Create_priv enum('N','Y') N Drop_priv enum('N','Y') N Reload_priv enum('N','Y') N Shutdown_priv enum('N','Y') N Process_priv enum('N','Y') N File_priv enum('N','Y') N Grant_priv[1]
enum('N','Y') N References_priv enum('N','Y') N Index_priv enum('N','Y') N Alter_priv enum('N','Y') N Show_db_priv enum('N','Y') N Super_priv enum('N','Y') N Create_tmp_table_priv enum('N','Y') N Lock_tables_priv enum('N','Y') N Execute_priv enum('N','Y') N Repl_slave_priv enum('N','Y') N Repl_client_priv enum('N','Y') N Create_view_priv enum('N','Y') N Show_view_priv enum('N','Y') N Create_routine_priv enum('N','Y') N Alter_routine_priv enum('N','Y') N Create_user_priv enum('N','Y') N Event_priv enum('N','Y') N Trigger_priv enum('N','Y') N ssl_type enum('','ANY','X509','SPECIFIED') ssl_cipher blob NULL x509_issuer blob NULL x509_subject blob NULL max_questions int(11) unsigned 0 max_updates int(11) unsigned 0 max_connections int(11) unsigned 0 max_user_connections int(11) unsigned 0 Изначально эта таблица содержит пользователя root без пароля. По умолчанию root может входить с любого хоста, имеет все привилегии и доступ ко всем базам данных.Также в таблице содержится запись для пользователя '%'. В БД MYSQL содержатся таблицы, называемых таблицами привилегий. Система привилегий будет подробно рассмотрена в следующих работах, а пока вы можете выполнить команды на добавления своего пользователя: Для добавления нового пользователя your
_
name
, можно выполнить следующие операторы языка (Insert): Выполнением команды Select host, user, password from user;
Мы выводим перечисленные поля в виде таблицы Host User Password % root 456g879k34df9 Если необходимо выделить все столбцы таблицы, то необходимо набрать * в качестве аргумента команды select
. Чтобы изменения вступили в силу нужно перегрузить сервер, предварительно закончив текущий сеанс работы командой quit
. mysqladmin -u root reload
(эта команда перегружает сервер) После установки пароля для пользователя нужно перезагрузить сервер командой mysqladmin reload, чтобы изменения вступили в силу. После этого можно попробовать войти снова: Mysql/bin/mysql -u your_name -p mysql
Enter password:******* Если же после этой операции вы не получите приглашение ко входу, то необходимо будет повторить вход в сервер под учетной записью ROOT
и назначить необходимые права.
Т.о., недостаточно добавить сведения о пользователе в системную БД, дополнительно необходимо назначить права пользователю, после чего можно начинать настраивать таблицы привилегий, вводить новых пользователей, создавать базы данных и таблицы, то есть делать все то, что называется администрированием. Назначить права можно указанием инструкцией INSERT
для заполнения соответствующие привилегии (перечень привилегий см. Mysql/bin/mysql -u root
И выполнить следующий запрос к БД: Mysql>USE MYSQL;
Mysql>GRANT ALL PRIVILEGES ON *.* TO 'your_name'@'localhost
[3]
' IDENTIFIED BY 'your_pass' WITH GRANT OPTION;
Mysql>FLUSH PRIVILEGES;
Если пароль был случайно забыт, чтобы его задать по новой, придется стереть файлы mysql.frm mysql.MYI и mysql.MYD из папки с базами данных, затем запустить скрипт mysql_install_db и повторить все по новой. Можно воспользоваться ключом MYSQL и ввести --
skip
-
grant
-
tables
,
при этом все пароли будут имеет пустое поле. Команда имеет вид mysqld --skip-grant-tables.
Пояснения:
1.Команда insert вставляет данные в таблицу, не забывайте завершать команды ';'. 2.При вводе пароля используйте функцию password(), иначе пароль работать не будет! 3.Все пароли шифруются mysql, поэтому в поле Password вы видите абракадабры. Это делается в целях безопасности. 4.Не есть хорошей практикой назначать привилегии пользователям в таблице user, так как в этом случае они являются глобальными и распространяются на все базы данных. Предоставляйте привилегии каждому пользователю к конкретной базе данных в таблице db, которая будет рассмотрена далее. 5.При задании имени хоста для входа через сеть рекомендуется явно указывать полное имя хоста, а не '%'. В приведенном выше примере пользователю mary разрешается вход на сервер со всех машин домена tomsk.ru. Можно также указывать IP-адреса машин и маски подсетей для большей безопасности. Определяет к каким базам данных каким пользователям и с каких хостов разрешен доступ. В этой таблице можно предоставлять каждому пользователю доступ к базам данных и назначать привилегии. Eсли выполнить команду show columns from db;
получим следующее: Таблица 4 - Структура таблицы Db Field
Type
Null
Key
Default
Extra
Host char(60) PRI Db char(32) PRI User char(16) PRI Select_priv char(1) N Insert_priv char(1) N Update_priv char(1) N Delete_priv char(1) N Create_priv char(1) N Drop_priv char(1) N · По умолчанию, все привилегии установлены в 'N'. Например, предоставим юзеру mary доступ к базе данных mysql и дадим ему привилегии select
, insert
и update
(описание основных команд mysql будет дано в следующих лабораторных работах, сейчас ваша цель увидеть, как работают таблицы привилегий). · Для справки: Insert into db (host, user, db, select_priv, insert_priv, update_priv)
Values (''localhost', 'your_name', mysql, 'Y', 'Y', 'Y');
· Привилегии, устанавливаемые в таблице db, распространяются только на базу данных library. Если же установить эти привилегии в таблице user, то они будут распространяться и на другие базы данных, даже если доступ к ним и не установлен явно. Таблица host используется для расширения диапазона доступа в таблице db. К примеру, если доступ к какой-либо базе данных должен быть предоставлен более чем одному хосту, тогда следует оставить пустой колонку host в таблице db, и внести в таблицу host необходимые имена хостов. Выполним команду show columns from host;
Таблица 5 - Структура таблиц Host Field
Type
Null
Key
Default
Extra
Host char(60) PRI Db char(32) PRI Select_priv char(1) N Insert_priv char(1) N Update_priv char(1) N Delete_priv char(1) N Create_priv char(1) N Drop_priv char(1) N Как видно из таблицы, здесь также можно задавать привилегии для доступа к базе данных.Они обычно редко используются без необходимости. Все привилегии доступа нужно задавать в таблице db для каждого пользователя, а в таблице host только перечислить имена хостов.Сервер читает все таблицы, проверяет имя пользователя, пароль, имя хоста, имя базы данных, привилегии.Если в таблице db привилегии select, insert установлены в 'Y', а в таблице host в 'N', то в итоге юзер все равно получит 'Y'.Чтобы не вносить путаницы, лучше назначать привилегии в таблице db. Эти 3 таблицы являются основными.В новых версиях MySQL, начиная с 3.22 добавлены еще 2 таблицы- tables_priv и columns_priv, которые позволяют задать права доступа к определенной таблице в базе данных и даже к определенной колонке. Они работают подобно таблице db, только ссылаются на таблицы и колонки. Также, начиная с версии 3.22 можно использовать команду GRANT для предоставления доступа к базам данных, таблицам и колонкам таблиц, что избавляет от необходимости вручную модифицировать таблицы db, tables_priv и columns_priv. Команда GRANT будет подробно рассмотрена в следующих разделах. Привилегии, предоставляемые MySQL
Таблица 6 - Привилегии пользователя[4]
Привилегия
Колонка
Где используется
select
Select_priv таблицы insert
Insert_priv таблицы Update
Update_priv таблицы delete
Delete_priv таблицы index
Index_priv таблицы alter
Alter_priv таблицы create
Create_priv БД, таблицы, индексы drop
Drop_priv БД или таблицы grant
Grant_priv БД или таблицы References
References_priv БД или таблицы reload
Reload_priv администрирование сервера Shutdown
Shutdown_priv администрирование сервера Process
Process_priv администрирование сервера file
File_priv доступ к файлам на сервере Основные утилиты MySQL.
В состав дистрибутива MySQL входят следующие утилиты: · mysqld · mysql · mysqladmin · mysqlaccess · mysqlshow · mysqldump · isamchk Утилиты mysqld
и mysql
были подробно рассмотрены ранее, поэтому возвращаться к ним не будем. Кратко рассмотрим остальные. Утилита для администрирования сервера. Может использоваться администратором, а также некоторыми пользователями, которым предоставлены определенные привилегии, например – Reload_priv, Shutdown_priv, Process_priv
и File_priv
. Данная команда может использоваться для создания баз данных, изменения пароля пользователя(администратор может изменить пароль любому пользователю, а рядовой пользователь – только свой собственный), перезагрузки и остановки сервера, просмотра списка процессов, запущенных на сервере. Mysqladmin поддерживает следующие команды: Таблица 7 - Опции команды MySQLadmin Create [database_name] Создает базу данных Drop [database_name] Удаляет базу данных и все таблицы в ней Reload Перезагружает сервер Shutdown Останавливает работу сервера MySQL Processlist Выводит список процессов на сервере Status Выводит сообщение о статусе сервера Пример использования mysqladmin для изменения пароля: mysqladmin -u your_name password your_pass
Следует заметить, что в случае использования mysqladmin для установки пароля, не требуется использование функции password().Mysqladmin сам заботится о шифровании пароля. Используется для проверки привилегий пользователя для доступа к конкретной базе данных. Общий синтаксис: mysqlaccess [host] [user] [db] опции
Полезная утилита для проверки прав доступа пользователя, если он получает сообщение Access denied, при попытке соединиться с базой данных. Опции:
Таблица 8 - Опции команды MySQLAccess -?, --help Справка -u, --user=[username] Имя пользователя -p, --password=[password] Пароль пользователя -h, --host=[hostname] Имя хоста для проверки прав доступа -d, --db=[dbname] Имя базы данных для проверки прав доступа -U, --superuser=[susername] Имя суперпользователя(root) -P, --spassword=[spassword] Пароль администратора -b, --brief Выводит краткие сведения о таблице Mysqlshow
Используется, чтобы показать, с какими базами данных работает сервер, какие таблицы содержит каждая БД и какие колонки есть в каждой таблице.Синтаксис: mysqlshow [опции] [database [table [field]]]
Mysqlshow может использовать следующие параметры: Таблица 9 - Параметры команды Mysqlshow -?, --help Справка -h, --host=[hostname] Имя сервера -k, --key Показать ключи для таблицы -p, --password=[password] Пароль пользователя -u, --user=[username] Имя пользователя -p, --port=[port] Порт для связи -V, --version Вывести информацию о версии Если ввести mysqlshow без аргументов, будут показаны все базы данных, если указать имя БД, будут показаны все таблицы в ней. Команды mysqlshow
mysqlshow mysql
Mysqldump
Программа mysqldump используется для создания дампа содержания базы данных MySQL. Она пишет инструкции SQL в стандартный вывод. Эти инструкции SQL могут быть переназначены в файл. Можно резервировать базу данных MySQL, используя mysqldump, но при этом Вы должны убедиться, что в этот момент с базой данных не выполняется никаких других действий. А то mysqldump Вам такого нарезервирует... Программа mysqldump поддерживает следующие параметры (Вы можете использовать короткую или подробную версию): Таблица 10 - Опции команды MySQLdump Вывести в протокол отладочную информацию. В общем виде 'd:t:o, filename`.
Справка. Генерируйте полные инструкции insert (не исключая значений, которые соответствуют значениям столбца по умолчанию). Соединиться с сервером hostname. Экспорт только схемы информации (исключая данные). Экспорт только данных, исключая информацию для создания таблицы. Противоположность -d. Пароль пользователя, для соединения с сервером MySQL. Обратите внимание, что не должно быть пробела между -p и паролем. Не буферизовать результаты запроса, дамп выдать непосредственно к STDOUT. Имя пользователя. Если не задано, используется текущий логин. Вывести подробную информацию относительно различных стадий выполнения mysqldump. Порт для связи. Информация о версии. Вы можете направить вывод mysqldump в клиентскую программу MySQL, чтобы копировать базу данных. ПРИМЕЧАНИЕ: Вы должны убедиться, что база данных не изменяется в это время, иначе Вы получите противоречивую копию! Для справки: Примечание
флаг –p используется в случае, если пользователь наделен паролем. После выполнения этой команды у нас появился файл mysql-1.sql и mysql-2.sql. Загрузим их в текстовый редактор, чтобы поподробнее изучить, и, возможно, немного поправить. Задание
Запустите сервер MySQL. Зарегистрируйте своего пользователя в консольном приложении, задайте ему права. С помощью утилиты Mysqlshow выполните команду на просмотр структуры и состав таблиц базы Mysql. Приведите в отчете её схему. С помощью утилиты Mysqldump получите полный дамп базы Mysql (данные и таблицы), а также отдельные дампы таблиц и данных. Контрольные вопросы:
1. Каким способом возможен запуск серверной части СУБД. 2. Что такое привилегия. Каково её предназначение. 3. Какие основные утилиты входят в состав СУБД, какие функции они выполняют. Цель работы
– приобретение студентами практических навыков создания логических и физических моделей данных с помощью CASE – средств разработки информационных систем. Основные сведения
Система ERwin поддерживает прямое и обратное моделирование баз данных. При прямом моделировании схема базы данных описывается в прямом виде с использованием диаграммы сущность-связь. Сущности на диаграмме представляются прямоугольниками. Каждый прямоугольник может иметь различные визуальные атрибуты. Каждой сущности должно быть присвоено уникальное имя. Имена сущностей необходимо задавать в единственном числе. Это определяется тем, что система всегда оперирует отдельными экземплярами сущности. При этом отдельные экземпляры сущности рассматриваются как объекты, а сущности – как класс объектов. Если сущности были описаны при моделировании в BPwin, то их можно просто импортировать в ERwin. Пример диаграммы с созданными сущностями приведен на рисунке.
Рисунок
4 - Пример диаграммы с созданными сущностями Построение моделей в ERwin
Возможны две точки зрения на информационную модель и, соответственно, два уровня модели. Первый - логический уровень (точка зрения пользователя) означает прямое отображение фактов из реальной жизни. Например, люди, столы, отделы, собаки и компьютеры являются реальными объектами. Они именуются на естественном языке, с любыми разделителями слов (пробелы, запятые и т.д.). На физическом уровне модели рассматривается использование конкретной СУБД, определяются типы данных (например, целое или вещественное число), индексы для таблиц. ERwin предоставляет возможности создавать и управлять этими двумя различными уровнями представления одной диаграммы (модели), равно как и иметь много вариантов отображения на каждом уровне. Термин "логический уровень" в ERwin соответствует концептуальной модели. Этапы построения информационной модели
.
Erwin создает визуальное представление (модель данных) для решаемой задачи. Это представление может использоваться для детального анализа, уточнения и распространения документации, необходимой в цикле разработки. Однако ERwin далеко не только инструмент для рисования. ERwin автоматически создает базу данных (таблицы, индексы, хранимые процедуры, триггеры для обеспечения ссылочной целостности и другие объекты, необходимые для управления данными). Создание сущности.
Для внесения сущности в модель необходимо щелкнуть по кнопке сущности на панели инструментов (Erwin Toolbox) Каждая сущность должна быть полностью определена с помощью текстового описания в закладке Definition. Эти определения полезны как на логическом уровне, поскольку позволяют понять, что это за объект, так и на физическом уровне, поскольку их можно экспортировать как часть схемы и использовать в реальной БД (CREATE COMMENT on entity_name
). Закладки Note, Note2, Note3, UDP (User Defined Properties - Свойства, определенные пользователем) служат для внесения дополнительных комментариев и определений к сущности. В закладке Icon каждой сущности можно поставить в соответствие изображение, которое будет отображаться в режиме просмотра модели на уровне иконок и изображение, которое будет отображаться на всех других уровнях. Закладка UDP диалога Entity Editor служит для определения свойств, определяемых пользователем (User - Defined Properties). При нажатии на кнопку Создание атрибутов.
Следующий этап создания модели состоит в задании атрибутов для каждой сущности. При задании типа атрибута имеется возможность использовать домены. Домен – это абстрактный пользовательский тип, который присваивается любому физическому типу данных. При этом каждый домен может иметь свои значения по умолчанию и правила проверки вводимых данных. ERwin предоставляет возможность документировать все действия по созданию собственных типов данных. С использованием концепции домена обеспечивается переносимость базы данных на различные аппаратные платформы.
Рисунок 5 - Создание нового домена
Рисунок 6 - Указание свойств нового домена
Рисунок 7 - Значение по умолчанию для нового домена
Рисунок 8 - Использование домена для указания типа данных атрибуту.[5]
Для описания атрибутов следует, щелкнув правой кнопкой по сущности, выбрать в появившемся меню пункт Attribute Editor. Появится диалог Attribute Editor. Если щелкнуть по кнопке New, то в появившемся диалоге New Attribute можно указать имя атрибута, имя соответствующей ему в физической модели колонки и домен. Домен атрибута будет использоваться при определении типа колонки на уровне физической модели. Для атрибутов первичного ключа в закладке General диалога Attribute Editor необходимо сделать пометку в окне выбора Primary Key. Для большей наглядности диаграммы каждый атрибут можно связать с иконкой. Это можно сделать при помощи списка выбора Icon в закладке General. Очень важно дать атрибуту правильное имя. Атрибуты должны именоваться в единственном числе и иметь четкое смысловое значение. Согласно синтаксису IDEF1X, имя атрибута должно быть уникальным в рамках модели (а не только в рамках сущности!). По умолчанию при попытке внесения уже существующего имени атрибута ERwin переименовывает его. Например, если атрибут Комментарий уже существует в модели, другой атрибут (в другой сущности) будет назван Комментарий/2, затем Комментарий/3 и т.д. Для создания новой связи следует выбрать идентифицирующую или неидентифицирующую связь в палитре инструментов (ERwin Toolbox), щелкнуть сначала по родительской, а затем по дочерней сущности. В закладке General появившегося диалога можно задать мощность, имя и тип связи. Связи на диаграмме представляются линиями, идущими от одной сущности (таблицы) к другой. Каждой связи присваивается уникальное имя. Связанные таблицы разделяют на родительские и дочерние. Родительские таблицы отображаются прямоугольниками с прямыми углами, дочерние – со скругленными. Мощность связи (Cardinality)
- служит для обозначения отношения числа экземпляров родительской сущности к числу экземпляров дочерней. · общий случай, когда одному экземпляру родительской сущности соответствуют 0, 1 или много экземпляров дочерней сущности, не помечается каким-либо символом; · символом P помечается случай, когда одному экземпляру родительской сущности соответствуют 1 или много экземпляров дочерней сущности (исключено нулевое значение); · символом Z помечается случай, когда одному экземпляру родительской сущности соответствуют 0 или 1 экземпляр дочерней сущности (исключены множественные значения); · цифрой помечается случай, когда одному экземпляру родительской сущности соответствует заранее заданное число экземпляров дочерней сущности. По умолчанию символ, обозначающий мощность связи, не показывается на диаграмме. Для отображения имени следует в контекстном меню, которое появляется, если щелкнуть правой кнопкой мыши по любому месту диаграммы, не занятому объектами модели, выбрать пункт Display Options/Relationship и затем включить опцию Cardinality. Тип связи (идентифицирующая/неидентифицирующая).
В IDEF1X различают зависимые и независимые сущности. Тип сущности определяется ее связью с другими сущностями. Идентифицирующая связь устанавливается между независимой (родительский конец связи) и зависимой (дочерний конец связи) сущностями. Когда рисуется идентифицирующая связь, ERwin автоматически преобразует дочернюю связь в зависимую. Зависимая сущность изображается прямоугольником со скругленными углами. Экземпляр зависимой сущности определяется только через отношение к родительской сущности. При установлении идентифицирующей связи атрибуты первичного ключа родительской сущности автоматически переносятся в состав первичного ключа дочерней сущности. Эта операция дополнения атрибутов дочерней сущности при создании связи называется миграцией атрибутов. В дочерней сущности новые атрибуты помечаются как внешние ключи - (FK). При установлении неидентифицирующей связи дочерняя сущность остается независимой, а атрибуты первичного ключа родительской сущности мигрируют в состав неключевых компонентов дочерней. Неидентифицирующая связь служит для связи независимых сущностей. Идентифицирующая связь показывается на диаграмме сплошной линией с жирной точкой на дочернем конце связи, неидентифицирующая - пунктирной. Для неидентифицирующей связи можно указать обязательность (Nulls в закладке General диалога Relationship Editor). В случае обязательной связи (No Nulls) при генерации схемы БД атрибут внешнего ключа получит признак NOT NULL, несмотря на то, что внешний ключ не войдет в состав первичного ключа дочерней сущности. В случае необязательной связи (Nulls Allowed) внешний ключ может принимать значение NULL. Необязательная неидентифицирующая связь помечается прозрачным ромбом со стороны родительской сущности Имя связи (Verb Phrase)
- фраза, характеризующая отношение между родительской и дочерней сущностями. Для связи один-ко-многим идентифицирующей или неидентифицирующей достаточно указать имя, характеризующей отношение от родительской к дочерней сущности (Parent-to-Child). Для связи многие-ко-многим следует указывать имена как Parent-to-Child, так и Child-to-Parent. Для отображения имени следует в контекстном меню, которое появляется, если щелкнуть правой кнопкой мыши по любому месту диаграммы, не занятому объектами модели, выбрать пункт Display Options/Relationship и затем включить опцию Verb Phrase. Имя роли или функциональное имя (Rolename)
- это синоним атрибута внешнего ключа, который показывает, какую роль играет атрибут в дочерней сущности. Задать имя роли можно в закладке Rolename/RI Actions диалога Relationship Editor. Создание ключей.
Каждый экземпляр сущности должен быть уникален и отличаться от других атрибутов. Первичный ключ (primary key)
- это атрибут или группа атрибутов, однозначно идентифицирующие экземпляр сущности. Атрибуты первичного ключа на диаграмме не требуют специального обозначения - это те атрибуты, которые находятся в списке атрибутов выше горизонтальной линии. При внесении нового атрибута в диалоге Attribute Editor для того, чтобы сделать его атрибутом первичного ключа, нужно включить флажок Primary Key в нижней части закладки General. На диаграмме ключевой атрибут можно внести в состав первичного ключа, воспользовавшись режимом переноса атрибутов (кнопка В одной сущности может оказаться несколько атрибутов или наборов атрибутов, претендующих на роль первичного ключа. Такие претенденты называются потенциальными ключами (candidate key).
Ключи могут быть сложными, т.е. содержащими несколько атрибутов. Сложные первичные ключи не требуют специального обозначения - это список атрибутов выше горизонтальной линии. При выборе первичного ключа предпочтение должно отдаваться более простым ключам, т.е. ключам, содержащим меньшее количество атрибутов. Многие сущности имеют только один потенциальный ключ. Такой ключ становится первичным. Некоторые сущности могут иметь более одного возможного ключа. Тогда один из них становится первичным, а остальные - альтернативными ключами. Альтернативный ключ (Alternative Key)
- это потенциальный ключ, не ставший первичным. Каждому ключу соответствует индекс, имя которого также присваивается автоматически. Имена ключа и индекса при желании можно изменить вручную. На диаграмме атрибуты альтернативных ключей обозначаются как (Akn.m.), где n - порядковый номер ключа, m - порядковый номер атрибута в ключе. Когда альтернативный ключ содержит несколько атрибутов, (Akn.m.) ставится после каждого. Внешние ключи (Foreign Key)
создаются автоматически, когда связь соединяет сущности: связи образуют ссылку на атрибуты первичного ключа в дочерней сущности и эти атрибуты образуют внешний ключ в дочерней сущности (миграция ключа). Атрибуты внешнего ключа обозначаются символом (FK) после своего имени. Зависимая сущность может иметь один и тот же ключ из нескольких родительских сущностей. Сущность может также получить один и тот же внешний ключ несколько раз от одного и того же родителя через несколько разных связей. Когда ERwin обнаруживает одно из этих событий, он распознает, что два атрибута одинаковы, и помещает атрибуты внешнего ключа в зависимой сущности только один раз. Это комбинирование или объединение идентичных атрибутов называется унификацией. Есть случаи, когда унификация нежелательна. Например, когда два атрибута имеют одинаковые имена, но на самом деле они отличаются по смыслу, и необходимо, что бы это отличие отражалось в диаграмме. В этом случае необходимо использовать имена ролей внешнего ключа. Связи на диаграмме представляются линиями, идущими от одной сущности (таблицы) к другой. Каждой связи присваивается уникальное имя. Связанные таблицы разделяют на родительские и дочерние. Родительские таблицы отображаются прямоугольниками с прямыми углами, дочерние – со скругленными. После указания всем атрибутам формата данных необходимо созданную логическую модель преобразовать в физическую. Для этого необходимо в Tools
выбрать Derive
New
Model
, где в качестве Target Databases выберите ODBC
/
Generic
(для использования в СУБД MySQL) см. Рисунок 9. Наша модель (см Рисунок 4) будет преобразована к виду см.Рисунок 11. Далее выбрав в меню Tools
/
Forward
Engineer
/
Shema
Generation
и задав необходимые настройки, получим в меню Preview код на языке SQL для реализации схемы БД в СУБД MySQL.
Рисунок
9 - Преобразование логической модели в физическую
Рисунок
10 - Физическая модель с указанием формата данных.
Рисунок
11 - Генерация кода SQL Задание
1. Выполните построение диаграммы с заданными сущностями (прямое моделирование) для заданной предметной области. 2. Задайте атрибуты для каждой определенной сущности. При задании атрибутов используйте домены. 3. Введите связи между сущностями. Присвойте связям уникальные имена. 4. Используя СУБД MYSQL, решите прямую генерацию базы данных для проектируемой информационной. 5. Отчет должен содержать концептуальную модель и физическую базу данных в СУБД MYSQL. Контрольные вопросы
1. В чем состоит различие логического и физического уровней представления моделей данных с помощью ERwin? 2. В чем различие между моделями данных, представленных в форме диаграммы сущность-связь, на основе ключей и в виде полной атрибутивной модели? 3. Какие основные компоненты содержат модели данных, представленные по методологии IDEF1X? Цель работы:
Ознакомиться с возможностями СУБД MySQL и создать с его помощью базу данных, набор таблиц в ней и заполнить таблицы данными для последующей работы. Содержание работы и методические указания к ее выполнению
1. Ознакомиться с возможностями работы клиентского приложения MySQL . 2. Изучить набор команд языка SQL, связанный с созданием базы данных, созданием, модификацией структуры таблиц и их удалением, вставкой, модификацией и удалением записей таблиц. Функция
Описание
создание базы данных выбор существующей базы данных close database закрытие файлов текущей базы данных удаление базы данных создание таблицы базы данных модификация структуры базы данных удаление таблицы базы данных добавление одной или нескольких строк в таблицу удаление одной или нескольких строк из таблицы модификация одной или нескольких строк таблицы загрузка данных в таблицы из файла 3. Создать базу данных. Создание базы данных в MySQL производится с помощью утилиты mysqladmin. Изначально существует только БД mysql для администратора и БД test, в которую может войти любой пользователь и которая по умолчанию пуста. Приведенный ниже пример иллюстрирует создание базы данных. Mysql/bin>mysqladmin -u root -p create data_name
Enter password:****** Database "data_name" created. mysqlbin> Где data_name – имя создаваемой БД. Проверить, что БД создана можно ранее рассмотренной командой Show
databases
или утилитой mysqlshow
.
По умолчанию, root
имеет доступ ко всем базам данных и таблицам. Перейти в созданную базу данных можно, используя команду mysql
. Use
database Mysql/bin>mysql -u root -p data1
Enter password:****** Welcome to MySQL monitor. Или, находясь в другой базе данных, например в mysql ввести команду: mysql>use data1
Database changed. Создать базу данных можно непосредственно находясь в клиентском приложении MySQL, вводом команды: Где Base_name
имя создаваемой базы данных. В созданной базе можно создавать таблицы и вводить информацию. Указанные операции можно выполнить, используя специализированное программное обеспечение, например MySQL-Front, запуск которого осуществляется из меню ПУСК/ПРОГРАММЫ (см. рис 1, 2). Необходимо указать: · Имя; · Хост; · Пароль; · Порт; · Имя БД (при необходимости).
Рисунок 12 - Запуск MySQL-front
Рисунок 13 - Создание БД в среде MySQL-front После задания активной БД можно с помощью средств, предоставляемых программой изменять структуру БД, вводить данные, задавать ключевые поля. Помимо этого можно в специально отведенном окне напрямую вводить инструкции, используя синтаксис языка SQL, как показано на рисунке:
Рисунок 14 - Использование синтаксиса SQL 4. Средствами языка SQL необходимо создать четыре таблицы в базе данных, используя команду
CREATE TABLE
, синтаксис которой приведен в приложении. Для таблицы J: CREATE TABLE j (
Jnum varchar(6) NOT NULL default '',
Jnam varchar(20) default NULL,
Ci varchar(20) default NULL,
PRIMARY KEY (Jnum)
) TYPE=MyISAM;
Значками /* */ - выделяются комментарии в тексте запроса. При создании таблиц выполнить такую реализацию, чтобы она отражала структуру таблиц, указанную ниже (таблице S, P, J, SPJ ) и должны быть наложены следующие ограничения: - поля номер_поставщика, номер_детали, номер_изделия во всех таблицах имеет символьный тип и длину 6 (varchar
(6)); - поля рейтинг, вес и количество имеют целочисленный тип (integer
)
; - поля фамилия, город (поставщика, детали или изделия), название (детали или изделия) имеют символьный тип и длину 20 (varchar
(20)); - ни для одного поля не предусматривается использование индексов; -для всех полей допускаются значения NULL и значения-дубликаты, кроме полей первичного и внешнего ключей. После создания пустых таблиц их необходимо наполнить данными. Вводить данные в нее можно несколькими способами: а)Вручную, используя команду insert into
; Пример ввода данных вручную (команда INSERT
): mysql>insert into J (Jnum, Jnam, Ci)values ('J1','
Жесткий
диск
','Париж
'
); или mysql>insert into J values ('J1','
Жесткий
диск
','Париж
');
//т.е в случае если вы вставляете данные во все поля таблицы то их перечислять не обязательно. Таким образом SQL инструкция имеет следующий вид INSERT INTO table_name (id, name) VALUES ('id_value', 'name_value'
); Записать и выполнить совокупность запросов для занесения нижеприведенных данных в созданные таблицы
insert into имя_таблицы [(поле [,поле]...)] values (константа [,константа]...)
б)Загрузить данные из текстового файла, что является более предпочтительным, особенно если нужно ввести несколько тысяч записей. Синтаксис команды LOAD DATA INFILE. DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY 't'] [OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '' ]] [LINES TERMINATED BY 'n'] [IGNORE number LINES] [(col_name,...)] Пример
:
LOAD DATA LOCAL INFILE '/MyDocs/categories.txt' REPLACE В данном случае файл categories.txt
находится на машине под управлением MS Windows, в каталоге C:\MyDocs
. Обратите внимание на UNIX стиль написания пути. Слово REPLACE
В SQL запросе означает, что необходимо замещать записи с совпадающими значениями ключей. INTO TABLE[6]
указывает имя таблицы, куда будут импортированы данные. FIELDS TERMINATED BY ';' [7]
указывает разделители полей, порядок полей должен быть таким же, как и в таблице назначения, OPTIONALLY ENCLOSED BY '\"' указывает, что поля VARCHAR взяты в двойные кавычки, и LINES TERMINATED BY '\r' [8]
в) Использовать утилиту mysqlimport также для загрузки данных из текстового файла. Эти и другие операции можно выполнить также и в программе MySQL-Front.
Рисунок 15 - Использование программы MySQL-front для заполнения таблиц данными из файла Таблица поставщиков (S) Hомеp поставщика
Фамилия
Рейтинг
Город
S1 Смит 20 Лондон S2 Джонс 10 Париж S3 Блейк 30 Париж S4 Кларк 20 Лондон S5 Адамс 30 Афины Таблица деталей (P) Номер детали
Название
Цвет
Вес
Город
P1 Гайка Красный 12 Лондон P2 Болт Зеленый 17 Париж P3 Винт Голубой 17 Рим P4 Винт Красный 14 Лондон P5 Кулачок Голубой 12 Париж P6 Блюм Красный 19 Лондон Таблица изделий (J) Номер изделия
Название
Город
J1 Жесткий диск Париж J2 Перфоратор Рим J3 Считыватель Афины J4 Принтер Афины J5 Флоппи-диск Лондон J6 Терминал Осло J7 Лента Лондон Таблица поставок (SPJ) Номер поставщика
Номер детали
Номер изделия
Количество
S1 P1 J1 200 S1 P1 J4 700 S2 P3 J1 400 S2 P3 J2 200 S2 P3 J3 200 S2 P3 J4 500 S2 P3 J5 600 S2 P3 J6 400 S2 P3 J7 800 S2 P5 J2 100 S3 P3 J1 200 S3 P4 J2 500 S4 P6 J3 300 S4 P6 J7 300 S5 P2
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||