ку. УП 07. Практикум по курсу технологии баз данных Учебное пособие
 Скачать 2.49 Mb.
|
|
КАЗАНСКИЙ (ПРИВОЛЖСКИЙ) ФЕДЕРАЛЬНЫЙ УНИВЕРСИТЕТ ИНСТИТУТ ВЫЧИСЛИТЕЛЬНОЙ МАТЕМАТИКИ И ИНФОРМАЦИОННЫХ ТЕХНОЛОГИЙ Кафедра системного анализа и информационных технологий А.А. АНДРИАНОВА, Т.М. МУХТАРОВА, Р.Г. РУБЦОВА ЛАБОРАТОРНЫЙ ПРАКТИКУМ ПО КУРСУ «ТЕХНОЛОГИИ БАЗ ДАННЫХ» Учебное пособие Казань – 2016 УДК 004.43 ББК 32.973.26 – 018.1 Принято на заседании кафедры системного анализа и информационных технологий Протокол № 5 от 2 февраля 2016 года Принято на заседании учебно-методической комиссии Института Вычислительной математики и информационных технологий Протокол № 6 от 18 февраля 2016 года Рецензенты: кандидат педагогических наук, доцент кафедры информационных систем КФУ Е.Е.Лаврентьева; кандидат физико-математических наук, доцент кафедры анализа данных и исследования операций КФУ В.В.Бандеров Андрианова А.А., Мухтарова Т.М., Рубцова Р.Г. Лабораторный практикум по курсу «Технологии баз данных»: Учебное пособие / А.А. Андрианова, Т.М. Мухтарова, Р.Г. Рубцова. – Казань: КФУ, 2016. – 97 с. Учебное пособие предназначено для проведения лабораторного практикума по курсам «Технологии баз данных», «Базы данных» для студентов, обучающихся по направлениям «Фундаментальная информатика и информационные технологии», «Прикладная математика и информатика», «Информационная безопасность». Основ- ная цель практикума – в сжатом изложении показать все основные задачи и фазы ра- боты программиста с базами данных, технологии использования баз данных при про- ектировании приложений прикладной направленности. В пособии рассматриваются примеры с помощью нескольких популярных систем управления базами данных (СУБД). © Андрианова А.А. Мухтарова Т.М. Рубцова Р.Г., 2016 © Казанский университет, 2016 3 СОДЕРЖАНИЕ ВВЕДЕНИЕ .................................................................................................................. 4 ЧАСТЬ I. СЕРВЕРНЫЕ ТЕХНОЛОГИИ ................................................................ 11 1.1. МОДЕЛЬ ДАННЫХ ...................................................................................... 11 1.2. ПЕРЕНОС БАЗЫ ДАННЫХ НА ДРУГОЙ СЕРВЕР ................................ 19 1.3. КОМАНДЫ МОДИФИКАЦИИ ДАННЫХ (DML) ................................... 37 1.4. ВЫБОРКА ДАННЫХ. ОПЕРАТОР SELECT (DQL) ................................ 41 1.5. ХРАНИМЫЕ ПРОЦЕДУРЫ. ФУНКЦИИ И ТРИГГЕРЫ ........................ 48 Часть II. КЛИЕНТСКИЕ ТЕХНОЛОГИИ .............................................................. 62 2.1. ВЫПОЛНЕНИЕ ЗАПРОСА К БАЗЕ ДАННЫХ ИЗ КЛИЕНТСКОГО ПРИЛОЖЕНИЯ. ............................................................................................ 62 2.2. ПАРАМЕТРЫ ЗАПРОСА ............................................................................ 67 2.3. ВЫПОЛНЕНИЕ КОМАНД DML. ............................................................... 68 2.4. ПОНЯТИЕ НАБОРА ДАННЫХ КАК ВИРТУАЛЬНОЙ БАЗЫ ДАННЫХ ............................................................................................ 69 2.5. СВЯЗЬ НАБОРА ДАННЫХ И БАЗЫ ДАННЫХ ...................................... 70 2.6. КАК СИНХРОНИЗИРОВАТЬ ИЗМЕНЕНИЯ В НАБОРЕ ДАННЫХ С БАЗОЙ ДАННЫХ ..................................................................................... 71 2.7. ПОЛЬЗОВАТЕЛЬСКИЙ ИНТЕРФЕЙС НА ОСНОВЕ ТАБЛИЦ ............ 72 2.8.ПОЛЬЗОВАТЕЛЬСКИЙ ИНТЕРФЕЙС НА ОСНОВЕ ОДНОЙ ЗАПИСИ. ....................................................................................................... 74 2.9. ГЕНЕРАЦИЯ ОТЧЕТОВ И ПЕЧАТНЫХ ФОРМ ..................................... 76 2.10. ГЕНЕРАЦИЯ ОТЧЕТОВ В ФОРМАТЕ XML. ......................................... 78 ЧАСТЬ III. ВВЕДЕНИЕ В ХРАНИЛИЩА ДАННЫХ .......................................... 81 3.1. ПРОЕКТИРОВАНИЕ ХРАНИЛИЩ ДАННЫХ ........................................ 82 3.2. ЗАГРУЗКА ДАННЫХ .................................................................................. 83 3.3. ПОИСК ИНФОРМАЦИИ В ХРАНИЛИЩЕ .............................................. 89 3.4. ПОСТРОЕНИЕ ОТЧЕТОВ С ПОМОЩЬЮ ЗАПРОСОВ К ХРАНИЛИЩУ ............................................................................................... 95 СПИСОК ЛИТЕРАТУРЫ ......................................................................................... 97 4 ВВЕДЕНИЕ Базы данных являются одной из основных составляющих большинства современных приложений, особенно прикладного или аналитического характе- ра. Любое предприятие имеет свою базу данных (а, возможно, и множество баз данных). Заходя в интернет, мы видим информацию из баз данных через серви- сы социальных сетей, интернет-магазинов, электронных университетов и др. Немало математических задач связано с использованием баз данных. Примером тому являются задачи анализа данных или машинного обучения. Таким обра- зом, знание и навыки работы с базами данных становятся неотъемлемой со- ставляющей компетенции современного ИТ-специалиста. Данное учебно- методическое пособие призвано помочь студенту в практической форме приоб- рести необходимые навыки работы с базами данных и их использованием в различных приложениях. Разработка приложения, использующего базу данных, включает в себя множество задач. Во-первых, требуется сформировать логическую модель базы данных и, как следствие, набор таблиц, которые будут хранить данные. Вторым моментом является выбор системы управления базами данных (СУБД), на ко- тором будет храниться база. Именно СУБД отвечают за выполнение основных операций, выполняемых с базой данных. Во многом этот выбор зависит от масштабов создаваемого приложения. В дальнейшем следует определить сер- верную часть приложения, включающую определения целостности данных, серверные процедуры, позволяющие выполнять основные преобразования дан- ных. Только после решения всех этих вопросов речь заходит о клиентской ча- сти приложения работы с базой данных. Некоторые СУБД имеют собственные средства создания клиентской части (например, MS FoxPro или более популяр- ный MS Access), но в большинстве своем современные СУБД являются сервер- ными, т.е. предоставляют средства доступа к данным из других приложений. Этот момент позволяет создавать гибкий пользовательский интерфейс на тех технологиях, которые являются более приемлемыми для пользователя. Отдель- 5 ным вопросом функционирования приложения базы данных являются вопросы экспорта и импорта данных из других источников информации и агрегация ин- формации из различных источников для предоставления сводной и аналитиче- ской отчетности (концепция хранилищ данных). Пособие создано в поддержку практикума по курсу «Технологии баз дан- ных», который реализуется в Казанском (Приволжском) федеральном универ- ситете на кафедре системного анализа и информационных технологий. За время практикума каждый студент должен разработать собственное приложение баз данных, которое обязательно должно включать следующие элементы: 1. Создание логической модели базы данных. Описание ER-модели, генера- ция на ее основе реляционной модели данных. 2. Реализация модели в СУБД. В качестве СУБД могут быть выбраны: MS SQL Server, MySQL или PostgreSQL или иное серверное СУБД. 3. Заполнение базы данных. 4. Создание различных запросов на получение данных (для формирования навыков работы с реализацией различных операций реляционной алгеб- ры). Для каждой из операций (исключая деление) нужно показать мини- мум три запроса (хотя один и тот же запрос может демонстрировать вы- полнение нескольких операций). 5. Создание хранимых процедур и триггеров для обеспечения серверной ча- сти работы с данными. 6. Создание клиентского windows-приложения для работы с базой данных. Приложение должно иметь возможности добавления, изменения и удале- ния информации. 7. Создание модулей экспорта и импорта информации в базу данных (инте- грация с xml-файлами). 8. Реализация концепции хранилищ данных на примере создания OLAP- куба для многомерного поиска данных для публикации в отчетах. Каждая из перечисленных задач рассматривается в учебно-методическом пособии на примере создания элементов приложения «Деканат», с помощью 6 которого предоставляются возможности отслеживать оценки, которые получа- ют студенты во время сессии. Структурно в учебно-методическом пособии бу- дет выделено три главы, посвященные разработке серверных средств (базы данных и серверных процедур), разработке клиентской части приложения и введению в концепции хранилищ данных. В качестве средств разработки (программного обеспечения) нужно вы- брать сервер баз данных, т.е. СУБД, инструментальную оболочку для работы с выбранным сервером, технологию создания клиентского интерфейса. В качестве сервера баз данных можно использовать: MS SQL Server – устанавливается вместе с MS Visual Studio, которая мо- жет использоваться как оболочка доступа к базам данных. При установке SQL Server’у присваивается определенное имя, по которому к нему мож- но будет обращаться (по умолчанию SQLEXPRESS). Для локальной ра- боты с сервером можно использовать при подключении имя (local). Сво- бодной оболочкой (для некоммерческого использования) для MS SQL Server является программный продукт dbForge Studio компании DEVART ( http://www.devart.com/ru/dbforge/sql/studio ): Рис. 1. Главное окно dbForge Studio для MS SQL Server. 7 Создание соединения оболочки с сервером производится с помощью меню «База данных» -> «Новое подключение…». Здесь вводятся параметры подключения и имя, по которому в дальнейшем к этому подключению мож- но будет обращаться: Рис. 2. Параметры соединения с базой данных MS SQL Server. MySQL (версии с 5.0). Этот бесплатный сервер баз данных устанавлива- ется отдельно и конфигурируется с помощью специального wizard’a. Об- ратим внимание не то, что при конфигурировании экземпляра сервера требуется установить параметры учетной записи. По умолчанию, логин и пароль для сервера root. В качестве оболочки для работы с сервером MySQL можно использовать программный пакет MySQL Workbench – это свободное программное обеспечение, которое содержит средства мо- делирования, администрирования сервера и визуальной работы с базами данных, размещенными на нем. 8 Рис. 3. Главное окно MySQL Workbench. Для MySQL (аналогично MS SQL Server) компанией DEVART была разработана версия оболочки проектирования dbForge Studio. Она также яв- ляется свободной для некоммерческого использования ( http://www.devart.com/ru/dbforge/mysql/studio ): Рис. 4. Главное окно dbForge Studio для MySQL. При создании подключения к MySQL серверу требуется указать дру- гие параметры – это имя хоста, на котором установлен сервер баз данных (для локальных машин localhost), номер порта (по умолчанию MySQL ста- вится на порт 3306), логин и пароль учетной записи пользователя, а также 9 имя подключения. Еще не следует забывать на вкладке «Дополнительно» установить кодировку данных (сейчас настройки наиболее часто используют кодировку utf8) (MySQL очень чувствителен к кодировкам и отсутствие настройки кодировки может привести к проблемам с данными, написанными кириллицей): Рис. 5. Параметры соединения с базой данных MySQL. PostgreSQL также является свободным сервером баз данных. Также имеет оболочку проектирования pgAdmin. Существует уже оболочка dbForge Studio для PostgreSQL, однако на момент написания данного текста она была платным программным обеспечением. Рис. 6. Окно программы pgAdmin. 10 При установке сервера PostgreSQL и его дополнительного программ- ного обеспечения будут запрошены параметры учетной записи пользовате- ля. По умолчанию создается запись с логином postgres, пароль к которой устанавливает пользователь в момент установки. Аналогично MySQL, Post- greSQL идентифицируется хостом и номером порта (по умолчанию, 5432). 11 ЧАСТЬ I. СЕРВЕРНЫЕ ТЕХНОЛОГИИ 1.1. МОДЕЛЬ ДАННЫХ Разберем принципы формирования модели базы данных на примере приложения «Деканат». Модель будет создаваться с помощью инструментов моделирования данных в различных оболочках. Описание задачи.Пусть требуется хранить и управлять информацией о результатах обучения студентов: об учебных группах; студентах, обучаю- щихся в этих группах; дисциплинах, которые изучаются и сдаются в разные семестры; преподавателях, которые ведут эти дисциплины; оценках, кото- рые были получены студентами при сдаче зачетов/экзаменов. Существует несколько концепций моделей баз данных (иерархическая, сетевая, объектная, реляционная). Наиболее распространенной моделью яв- ляется реляционная модель, которая очень тесно переплетается с принципа- ми объектно-ориентированного анализа и еще одного популярного подхода в моделировании данных – ER-модели (модель «сущность-связь»). ER-модель удобна для начального проектирования, поскольку она ин- туитивно понятна большинству пользователей. В ней выделяются понятия сущности (основные объекты базы), атрибуты (свойства сущности) и связи (взаимодействия между сущностями). В ряде оболочек именно в этих тер- минах и создан сервис создания модели данных. Реляционная модель представляет всю базу данных как набор связан- ных таблиц. Большинство таблиц отвечает за хранение информации о сущ- ностях (столбцы таблиц характеризуют их атрибуты). Среди атрибутов сущ- ности выделяют ключевые атрибуты – атрибуты, которые являются иденти- фицирующими, точно определяющими запись, объект сущности. С помо- щью внедрения ключевых атрибутов одних сущностей (родительские табли- цы) в качестве столбцов в другие таблицы (дочерние) реализуются различ- ные связи между сущностями. 12 Построение модели с помощью оболочки MySQL Workbench (версия 5.2.39 CE). Рис. 7. Создание модели базы данных в MySQL Workbench. Создаем новую ER-модель и диаграмму в модели. В полученном окне модели представлено полотно, на которое можно наносить новые таблицы, с помощью визуальных средств редактирования, создать столбцы (атрибуты) таблиц и с помощью панели инструментов создать связи между таблицами. При установке связи ключевые поля родительских сущностей добавляются в дочерние таблицы автоматически. Рис. 8. Вид окна редактирования модели данных. Полотно для создания модели Панель инструментов 13 Рис. 9. Состав панели инструментов окна редактирования модели данных. Проведем анализ состава таблиц для решаемой задачи. При описании столбцов таблицы поля, входящие в первичный ключ, будут подчеркнуты. Имеется таблица Студенты (Students): (№Зач.книжки, ФИОСту- дента, №Группы). Для хранения групп не будем выделять отдельную таблицу. Имеется таблица Преподаватель (Teachers): (№Преподавателя, ФИОПреподавателя, Должность, №Кафедры). Чтобы избежать дублирования информации с названием кафедры вве- дем справочную таблицу кафедр: таблица Кафедра (Departments): (№Кафедры, Название, Телефон). Имеется таблица учебных дисциплин Дисциплина (Subjects): (№Дисциплины, Название). Таблица Сессия содержит информацию о том, каков состав зачетов и экзаменов для каждой конкретной группы по семестрам, каким преподавате- Новая таблица Новое представление Связь 1:1 Связь 1: n Связь 1:1 (с до- бавлением клю- ча в дочернюю таблицу) Связь 1 : n (с до- бавлением клю- ча в дочернюю таблицу) Связь m : n Связь 1 : n для существующих столбцов 14 лям следует сдавать зачеты и экзамены: Sessions (№Группы, №Семестра, №Дисциплины, Отчетность, №Преподавателя). Заметим, что отчетность может определяться номером дисциплины и номером семестра, но в предпо- ложении наличии нескольких специальностей один и тот же предмет может сдаваться в разных семестрах разными группами. Поэтому отчетность и преподаватель зависят и от группы тоже. Наконец, результаты сдачи сессии хранятся в таблице результатов Results (№Студента, №Группы, №Семестра, №Дисциплины, Баллы, ДатаСдачи, Оценка). Окончательную оценку хранить не требуется, так как она определяется количеством набранных баллов и таблицей оценок. Marks (Оценка, НижняяГраница, ВерхняяГраница) – эта таблица является справочной и не связана с основными таблицами базы. Ее роль за- ключается в определении правильной оценки по набранным баллам. В результате данного анализа задачи получится следующая модель: - сначала формируется состав таблиц без связующих атрибутов: Рис. 10. Модель таблиц базы данных «Деканат» без указания связей. - затем устанавливаем связи. Заметим, что можно было бы все связующие атрибуты сразу добавить в таблицы. Тогда все связи можно было бы добавить как связи «один-ко-многим» для существующих столбцов. Отметим также, что связь таблицы результатов и сессии не является очевидной, так как сессия зави- 15 сит от номера группы, а в таблице результатов указываются оценки конкретных студентов. Поэтому эту связь можно сделать идентифицирующей, а потом уда- лить из таблицы результатов атрибут номера группы. Другой вариант решения этой проблемы, добавить все поля в таблицу результатов и не устанавливать связь на уровне модели. Далее после создания таблиц в базе данных добавить ограничения внешних ключей для полей номера дисциплины и номера препо- давателя. Рис. 11. Модель таблиц базы данных «Деканат» с указанием связей. Отметим некоторую избыточность таблицы результатов относительно номера группы. Требуется обеспечить, чтобы номер группы и студенты были согласованы по таблицам студентов и результатов сессии. Построение модели в оболочке dbForge Studio для SQL Server Новую модель (диаграмму) базы данных можно создать с помощью меню «База данных»-> «Диаграмма БД». Окно редактирования новой диаграммы состоит из полотна, на которое можно наносить новые таблицы с помощью визуальных средств редактирова- ния, создать столбцы (атрибуты) таблиц и с помощью панели инструментов со- здать связи между таблицами. 16 Рис.12. Окно редактирования диаграммы базы данных. На панели инструментов следует отметить пока только две кнопки «Новая таблица», «Новая связь», которые позволяют создать новую таблицу, определив ее состав столбцов, первичные ключи и основные ограничения, и создать связи между таблицами, определив тем самым ограничения внешне- го ключа. Рис. 13. Окно создания столбцов таблицы. Для столбцов можно задать простые ограничения: допустимы ли пу- стые значения и определяет ли столбец поле-счетчик. Кроме того, можно выбрать столбцы, определяющие первичный ключ. При создании связи требуется «нарисовать» мышью линию от дочер- ней таблицы к родительской. Для подтверждения параметров связи будет Полотно для созда- ния модели Панель инструментов 17 показано окно, в котором нужно уточнить имена полей родительской и до- черней таблиц, которые будут связаны ограничением внешнего ключа: Рис. 14. Окно задания параметров внешнего ключа. На вкладках «Ограничения» и «Индексы» можно увидеть все ограни- чения, которые сгенерируются в базе данных применительно к этой таблице. На вкладке T-SQL можно увидеть SQL-команду, выполнение которой экви- валентно выполнению всех сделанных настроек. Рис. 15. Команда SQL создания таблицы «Студенты». 18 Отметим, что построитель модели синхронизирует все действия поль- зователя с базой данных, создавая указанные таблицы вместе со всеми огра- ничениями. Таким образом, будет получена следующая модель: Рис. 16. Модель данных, построенная с помощью dbForge Studio. Замечания относительно синхронизации номера группы в таблицах «Сес- сия» и «Студент» остаются на уровне модели нерешенным. Аналогичным образом создается модель и, соответственно, база данных в среде dbForge Studio для MySQL. Для PostgreSQL в стандартный набор инструмент формирования модели данных не входит. Поэтому состав таблиц нужно будет создать или с помощью специального SQL-оператора, или с помощью конструкторов таблиц: 19 Рис. 17. Вид окна редактирования таблицы. Действия с базой данных можно производить с помощью контекстного меню соответствующего элемента (таблицы, столбца, ограничения) в дереве объектов сервера. Настройки любого элемента производятся с помощью пункта контекстного меню «Свойства». 1.2. ПЕРЕНОС БАЗЫ ДАННЫХ НА ДРУГОЙ СЕРВЕР Любое СУБД имеет средства резервного копирования базы данных. Та- кому копированию подвергаются как метаданные (структура данных базы), так и сами данные. Конечно, каждое СУБД имеет свои собственные форматы, но традиционным форматом является сохранение в виде последовательности SQL- команд (создания, вставки, изменения) (SQL-скрипт), выполнение которых приведет к текущему состоянию базы данных. В оболочке dbForge Studio для SQL Server создание резервной копии (backup) можно осуществить двумя способами: 20 1. пункт меню «База данных» -> «Задачи» - > «Резервное копирование» (соответственно, для восстановления из резервной копии используется пункт меню «База данных» -> «Задачи» - > «Восстановление»). Этот способ связан с использованием специального формата MS SQL Server. 2. Генерация SQL-скрипта осуществляется с помощью пункта меню «База данных» -> «Задачи» - > «Сгенерировать скрипт…». Кстати, многие важные опции, доступные через меню, доступны и на стартовой странице приложения, чтобы можно было получить к ним быстрый доступ: Рис. 18. Вид стартовой страницы для вкладки «Миграция данных». В результате будет сгенерирован файл, содержащий следующие SQL- команды. Выделим полужирным шрифтом те команды, которые касаются со- здания базы данных и всех ее таблиц, а также определение ограничений: -- -- Скрипт сгенерирован Devart dbForge Studio for SQL Server, Версия 3.8.180.1 -- Домашняя страница продукта: http://www.devart.com/ru/dbforge/sql/studio -- Дата скрипта: 04.08.2014 23:36:06 -- Версия сервера: 11.00.2100 -- Версия клиента: -- USE master GO 21 IF DB_NAME() <> N'master' SET NOEXEC ON -- -- Создать базу данных "proba" -- PRINT (N'Создать базу данных "proba"') GO CREATE DATABASE proba ON PRIMARY( NAME = N'proba', FILENAME = N'c:\Program Files\Microsoft SQL Serv- er\MSSQL11.SQLEXPRESS\MSSQL\DATA\proba.mdf', SIZE = 4160KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON( NAME = N'proba_log', FILENAME = N'c:\Program Files\Microsoft SQL Serv- er\MSSQL11.SQLEXPRESS\MSSQL\DATA\proba_log.ldf', SIZE = 1040KB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ) GO -- -- Изменить базу данных -- PRINT (N'Изменить базу данных') GO ALTER DATABASE proba SET ANSI_NULL_DEFAULT OFF, ANSI_NULLS OFF, ANSI_PADDING OFF, ANSI_WARNINGS OFF, ARITHABORT OFF, AUTO_CLOSE ON, AUTO_CREATE_STATISTICS ON, AUTO_SHRINK OFF, AUTO_UPDATE_STATISTICS ON, AUTO_UPDATE_STATISTICS_ASYNC OFF, COMPATIBILITY_LEVEL = 110, CONCAT_NULL_YIELDS_NULL OFF, CONTAINMENT = NONE, CURSOR_CLOSE_ON_COMMIT OFF, CURSOR_DEFAULT GLOBAL, DATE_CORRELATION_OPTIMIZATION OFF, DB_CHAINING OFF, HONOR_BROKER_PRIORITY OFF, MULTI_USER, NUMERIC_ROUNDABORT OFF, PAGE_VERIFY CHECKSUM, PARAMETERIZATION SIMPLE, 22 QUOTED_IDENTIFIER OFF, READ_COMMITTED_SNAPSHOT OFF, RECOVERY SIMPLE, RECURSIVE_TRIGGERS OFF, TRUSTWORTHY OFF WITH ROLLBACK IMMEDIATE GO ALTER DATABASE proba SET ENABLE_BROKER GO ALTER DATABASE proba SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE proba SET FILESTREAM (NON_TRANSACTED_ACCESS = OFF) GO USE proba GO IF DB_NAME() <> N'proba' SET NOEXEC ON GO -- -- Создать таблицу "dbo.Teachers" -- PRINT (N'Создать таблицу "dbo.Teachers"') GO CREATE TABLE dbo.Teachers ( idTeacher int IDENTITY, FIOTeacher varchar(50) NOT NULL, idDepartment int NOT NULL, CONSTRAINT PK_Teachers PRIMARY KEY (idTeacher) ) ON [PRIMARY] GO -- -- Создать таблицу "dbo.Subjects" -- PRINT (N'Создать таблицу "dbo.Subjects"') GO CREATE TABLE dbo.Subjects ( idSubject int IDENTITY, TitleSubject varchar(50) NOT NULL, CONSTRAINT PK_Subjects PRIMARY KEY (idSubject) ) ON [PRIMARY] GO -- -- Создать таблицу "dbo.Students" -- PRINT (N'Создать таблицу "dbo.Students"') GO 23 CREATE TABLE dbo.Students ( idStudent int IDENTITY, FIOStudent varchar(50) NOT NULL, NumGroup int NOT NULL, CONSTRAINT PK_Students PRIMARY KEY (idStudent) ) ON [PRIMARY] GO -- -- Создать таблицу "dbo.Sessions" -- PRINT (N'Создать таблицу "dbo.Sessions"') GO CREATE TABLE dbo.Sessions ( NumGroup int NOT NULL, NumSemestr int NOT NULL, idSubject int NOT NULL, idTeacher int NOT NULL, Zach_Exam varchar(7) NOT NULL, CONSTRAINT PK_Sessions PRIMARY KEY (NumGroup, NumSemestr, idSubject, idTeach- er) ) ON [PRIMARY] GO -- -- Создать таблицу "dbo.Departments" -- PRINT (N'Создать таблицу "dbo.Departments"') GO CREATE TABLE dbo.Departments ( idDepartment int IDENTITY, TitleDepartment varchar(50) NOT NULL, PhoneDepartment varchar(50) NOT NULL, CONSTRAINT PK_Departments PRIMARY KEY (idDepartment) ) ON [PRIMARY] GO -- -- Создать таблицу "dbo.Results" -- PRINT (N'Создать таблицу "dbo.Results"') GO CREATE TABLE dbo.Results ( idStudent int NOT NULL, idSubject int NOT NULL, idTeacher int NOT NULL, DateExam datetime NOT NULL, Balls int NOT NULL, Mark int NOT NULL, NumSemestr int NOT NULL, CONSTRAINT PK_Results PRIMARY KEY (idStudent, idSubject, idTeacher, Num- Semestr) ) 24 ON [PRIMARY] GO -- -- Создать таблицу "dbo.Marks" -- PRINT (N'Создать таблицу "dbo.Marks"') GO CREATE TABLE dbo.Marks ( idMark int IDENTITY, LowBalls int NOT NULL, HighBalls int NOT NULL, CONSTRAINT PK_Marks PRIMARY KEY (idMark) ) ON [PRIMARY] GO -- -- секция для команд вставки данных из всех таблиц – ее пропустим -- -- Создать внешний ключ "FK_Teachers" для объекта типа таблица "dbo.Teachers" -- PRINT (N'Создать внешний ключ "FK_Teachers" для объекта типа таблица "dbo.Teachers"') GO ALTER TABLE dbo.Teachers ADD CONSTRAINT FK_Teachers FOREIGN KEY (idDepartment) REFERENCES dbo.Departments (idDepartment) GO -- -- Создать внешний ключ "FK_Sessions_Subjects_idSubject" для объекта типа табли- ца "dbo.Sessions" -- PRINT (N'Создать внешний ключ "FK_Sessions_Subjects_idSubject" для объекта типа таблица "dbo.Sessions"') GO ALTER TABLE dbo.Sessions ADD CONSTRAINT FK_Sessions_Subjects_idSubject FOREIGN KEY (idSubject) REFER- ENCES dbo.Subjects (idSubject) GO -- -- Создать внешний ключ "FK_Sessions_Teachers_idTeacher" для объекта типа табли- ца "dbo.Sessions" -- PRINT (N'Создать внешний ключ "FK_Sessions_Teachers_idTeacher" для объекта типа таблица "dbo.Sessions"') GO ALTER TABLE dbo.Sessions ADD CONSTRAINT FK_Sessions_Teachers_idTeacher FOREIGN KEY (idTeacher) REFER- ENCES dbo.Teachers (idTeacher) GO -- -- Создать внешний ключ "FK_Results" для объекта типа таблица "dbo.Results" -- 25 PRINT (N'Создать внешний ключ "FK_Results" для объекта типа таблица "dbo.Results"') GO ALTER TABLE dbo.Results ADD CONSTRAINT FK_Results FOREIGN KEY (idStudent) REFERENCES dbo.Students (idStudent) GO -- -- Создать внешний ключ "FK_Results_Subjects_idSubject" для объекта типа таблица "dbo.Results" -- PRINT (N'Создать внешний ключ "FK_Results_Subjects_idSubject" для объекта типа таблица "dbo.Results"') GO ALTER TABLE dbo.Results ADD CONSTRAINT FK_Results_Subjects_idSubject FOREIGN KEY (idSubject) REFER- ENCES dbo.Subjects (idSubject) GO -- -- Создать внешний ключ "FK_Results_Teachers_idTeacher" для объекта типа таблица "dbo.Results" -- PRINT (N'Создать внешний ключ "FK_Results_Teachers_idTeacher" для объекта типа таблица "dbo.Results"') GO ALTER TABLE dbo.Results ADD CONSTRAINT FK_Results_Teachers_idTeacher FOREIGN KEY (idTeacher) REFER- ENCES dbo.Teachers (idTeacher) GO SET NOEXEC OFF GO При работе с оболочкой dbForge Studio для MySQL используется другой путь к пункту меню: «База данных» -> «Резервная копия» -> «Создать резерв- ную копию БД» (аналогично можно использовать гиперссылку на стартовой странице в разделе «Миграция данных»). Приведем содержимое этого файла: -- -- Скрипт сгенерирован Devart dbForge Studio for MySQL, Версия 6.2.233.0 -- Домашняя страница продукта: http://www.devart.com/ru/dbforge/mysql/studio -- Дата скрипта: 04.08.2014 23:47:11 -- Версия сервера: 5.0.67-community-nt -- Версия клиента: 4.1 -- -- -- Отключение внешних ключей -- /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; -- 26 -- Установить режим SQL (SQL mode) -- /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -- -- Установка базы данных по умолчанию -- USE decanat; -- -- Описание для таблицы departments -- DROP TABLE IF EXISTS departments; CREATE TABLE departments ( idDepartment INT(11) NOT NULL AUTO_INCREMENT, TitleDepartment VARCHAR(255) NOT NULL, PhoneDepartment VARCHAR(255) NOT NULL, PRIMARY KEY (idDepartment) ) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER SET utf8 COLLATE utf8_general_ci; -- -- Описание для таблицы marks -- DROP TABLE IF EXISTS marks; CREATE TABLE marks ( idMark INT(11) NOT NULL, LowBalls INT(11) NOT NULL, HighBalls INT(11) NOT NULL, PRIMARY KEY (idMark) ) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_general_ci; -- -- Описание для таблицы students -- DROP TABLE IF EXISTS students; CREATE TABLE students ( idStudent INT(11) NOT NULL AUTO_INCREMENT, FIOStudent VARCHAR(255) NOT NULL, NumGroup INT(11) NOT NULL, PRIMARY KEY (idStudent) ) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER SET utf8 COLLATE utf8_general_ci; -- -- Описание для таблицы subjects -- DROP TABLE IF EXISTS subjects; CREATE TABLE subjects ( 27 idSubject INT(11) NOT NULL AUTO_INCREMENT, TitleSubject VARCHAR(255) NOT NULL, PRIMARY KEY (idSubject) ) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER SET utf8 COLLATE utf8_general_ci; -- -- Описание для таблицы teachers -- DROP TABLE IF EXISTS teachers; CREATE TABLE teachers ( idTeacher INT(11) NOT NULL AUTO_INCREMENT, FIOTeacher VARCHAR(255) NOT NULL, idDepartment INT(11) NOT NULL, PRIMARY KEY (idTeacher), CONSTRAINT FK_teachers_departments_idDepartment FOREIGN KEY (idDepartment) REFERENCES departments(idDepartment) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER SET utf8 COLLATE utf8_general_ci; -- -- Описание для таблицы results -- DROP TABLE IF EXISTS results; CREATE TABLE results ( idStudent INT(11) NOT NULL, idSubject INT(11) NOT NULL, idTeacher INT(11) NOT NULL, DateExam DATETIME NOT NULL, NumSemestr INT(11) NOT NULL, Balls INT(11) NOT NULL, Mark INT(11) NOT NULL, PRIMARY KEY (idStudent, idSubject, idTeacher, NumSemestr), CONSTRAINT FK_results_students_idStudent FOREIGN KEY (idStudent) REFERENCES students(idStudent) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT FK_results_subjects_idSubject FOREIGN KEY (idSubject) REFERENCES subjects(idSubject) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT FK_results_teachers_idTeacher FOREIGN KEY (idTeacher) REFERENCES teachers(idTeacher) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_general_ci; -- -- Описание для таблицы sessions -- DROP TABLE IF EXISTS sessions; CREATE TABLE sessions ( NumGroup INT(11) NOT NULL, 28 NumSemestr INT(11) NOT NULL, idSubject INT(11) NOT NULL, idTeacher INT(11) NOT NULL, Zach_Exam VARCHAR(7) NOT NULL, PRIMARY KEY (NumGroup, NumSemestr, idSubject, idTeacher), CONSTRAINT FK_sessions_subjects_idSubject FOREIGN KEY (idSubject) REFERENCES subjects(idSubject) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT FK_sessions_teachers_idTeacher FOREIGN KEY (idTeacher) REFERENCES teachers(idTeacher) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_general_ci; -- -- секция для команд вставки данных из таблиц -- -- -- Включение внешних ключей -- /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; В PostgreSQL резервную копию можно создать с помощью пункта кон- текстного меню «Резервная копия» для элемента дерева, соответствующего ко- пируемой базе данных. В результате будет сгенерирован следующий SQL-код: -- -- PostgreSQL database dump -- -- Dumped from database version 9.3.5 -- Dumped by pg_dump version 9.3.5 -- Started on 2014-08-04 23:54:32 SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; -- -- TOC entry 177 (class 3079 OID 11750) -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- TOC entry 1991 (class 0 OID 0) -- Dependencies: 177 -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -- 29 COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; SET search_path = public, pg_catalog; SET default_tablespace = ''; SET default_with_oids = false; -- -- TOC entry 171 (class 1259 OID 16397) -- Name: Departments; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- CREATE TABLE "Departments" ( "idDepartment" oid NOT NULL, "TitleDepartment" text NOT NULL, "PhoneDepartment" text ); ALTER TABLE public."Departments" OWNER TO postgres; -- -- TOC entry 170 (class 1259 OID 16394) -- Name: Marks; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- CREATE TABLE "Marks" ( "idMark" integer NOT NULL, "LowBalls" integer NOT NULL, "HighBalls" integer NOT NULL ); ALTER TABLE public."Marks" OWNER TO postgres; -- -- TOC entry 176 (class 1259 OID 16431) -- Name: Results; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- CREATE TABLE "Results" ( "idStudent" integer NOT NULL, "idSubject" integer NOT NULL, "idTeacher" integer NOT NULL, "NumSemestr" integer NOT NULL, "DateExam" date, "Balls" integer, "Mark" integer ); ALTER TABLE public."Results" OWNER TO postgres; -- -- TOC entry 174 (class 1259 OID 16415) -- Name: Sessions; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- CREATE TABLE "Sessions" ( "NumGroup" integer NOT NULL, "NumSemestr" integer NOT NULL, "idSubject" integer NOT NULL, "idTeacher" integer NOT NULL, "Zach_Exam" text 30 ); ALTER TABLE public."Sessions" OWNER TO postgres; -- -- TOC entry 175 (class 1259 OID 16423) -- Name: Students; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- CREATE TABLE "Students" ( "idStudent" oid NOT NULL, "FIOStudent" text, "NumGroup" integer ); ALTER TABLE public."Students" OWNER TO postgres; -- -- TOC entry 172 (class 1259 OID 16403) -- Name: Subjects; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- CREATE TABLE "Subjects" ( "idSubject" oid NOT NULL, "TitleSubject" text NOT NULL ); ALTER TABLE public."Subjects" OWNER TO postgres; -- -- TOC entry 173 (class 1259 OID 16409) -- Name: Teachers; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- CREATE TABLE "Teachers" ( "idTeacher" oid NOT NULL, "FIOTeacher" text NOT NULL, "idDepartment" integer NOT NULL ); ALTER TABLE public."Teachers" OWNER TO postgres; -- -- TOC entry 1978 (class 0 OID 16397) -- Dependencies: 171 -- Data for Name: Departments; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY "Departments" ("idDepartment", "TitleDepartment", "PhoneDepartment") FROM stdin; \. -- -- TOC entry 1977 (class 0 OID 16394) -- Dependencies: 170 -- Data for Name: Marks; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY "Marks" ("idMark", "LowBalls", "HighBalls") FROM stdin; 31 \. -- -- TOC entry 1983 (class 0 OID 16431) -- Dependencies: 176 -- Data for Name: Results; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY "Results" ("idStudent", "idSubject", "idTeacher", "NumSemestr", "DateExam", "Balls", "Mark") FROM stdin; \. -- -- TOC entry 1981 (class 0 OID 16415) -- Dependencies: 174 -- Data for Name: Sessions; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY "Sessions" ("NumGroup", "NumSemestr", "idSubject", "idTeacher", "Zach_Exam") FROM stdin; \. -- -- TOC entry 1982 (class 0 OID 16423) -- Dependencies: 175 -- Data for Name: Students; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY "Students" ("idStudent", "FIOStudent", "NumGroup") FROM stdin; \. -- -- TOC entry 1979 (class 0 OID 16403) -- Dependencies: 172 -- Data for Name: Subjects; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY "Subjects" ("idSubject", "TitleSubject") FROM stdin; \. -- -- TOC entry 1980 (class 0 OID 16409) -- Dependencies: 173 -- Data for Name: Teachers; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY "Teachers" ("idTeacher", "FIOTeacher", "idDepartment") FROM stdin; \. -- -- TOC entry 1853 (class 2606 OID 16439) -- Name: pk_department; Type: CONSTRAINT; Schema: public; Owner: postgres; Ta- blespace: -- ALTER TABLE ONLY "Departments" 32 ADD CONSTRAINT pk_department PRIMARY KEY ("idDepartment"); -- -- TOC entry 1851 (class 2606 OID 16441) -- Name: pk_mark; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: -- ALTER TABLE ONLY "Marks" ADD CONSTRAINT pk_mark PRIMARY KEY ("idMark"); -- -- TOC entry 1863 (class 2606 OID 16435) -- Name: pk_results; Type: CONSTRAINT; Schema: public; Owner: postgres; Table- space: -- ALTER TABLE ONLY "Results" ADD CONSTRAINT pk_results PRIMARY KEY ("idStudent", "idTeacher", "idSub- ject", "NumSemestr"); -- -- TOC entry 1859 (class 2606 OID 16422) -- Name: pk_sessions; Type: CONSTRAINT; Schema: public; Owner: postgres; Table- space: -- ALTER TABLE ONLY "Sessions" ADD CONSTRAINT pk_sessions PRIMARY KEY ("NumGroup", "NumSemestr", "idSub- ject", "idTeacher"); -- -- TOC entry 1861 (class 2606 OID 16430) -- Name: pk_students; Type: CONSTRAINT; Schema: public; Owner: postgres; Table- space: -- ALTER TABLE ONLY "Students" ADD CONSTRAINT pk_students PRIMARY KEY ("idStudent"); -- -- TOC entry 1855 (class 2606 OID 16443) -- Name: pk_subject; Type: CONSTRAINT; Schema: public; Owner: postgres; Table- space: -- ALTER TABLE ONLY "Subjects" ADD CONSTRAINT pk_subject PRIMARY KEY ("idSubject"); -- -- TOC entry 1857 (class 2606 OID 16445) -- Name: pk_teacher; Type: CONSTRAINT; Schema: public; Owner: postgres; Table- space: -- ALTER TABLE ONLY "Teachers" ADD CONSTRAINT pk_teacher PRIMARY KEY ("idTeacher"); 33 -- -- TOC entry 1864 (class 2606 OID 16446) -- Name: fk_dep_tea; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY "Teachers" ADD CONSTRAINT fk_dep_tea FOREIGN KEY ("idDepartment") REFERENCES "Depart- ments"("idDepartment"); -- -- TOC entry 1867 (class 2606 OID 16461) -- Name: fk_res_stud; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY "Results" ADD CONSTRAINT fk_res_stud FOREIGN KEY ("idStudent") REFERENCES "Stu- dents"("idStudent"); -- -- TOC entry 1868 (class 2606 OID 16466) -- Name: fk_res_sub; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY "Results" ADD CONSTRAINT fk_res_sub FOREIGN KEY ("idSubject") REFERENCES "Sub- jects"("idSubject"); -- -- TOC entry 1869 (class 2606 OID 16471) -- Name: fk_res_tea; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY "Results" ADD CONSTRAINT fk_res_tea FOREIGN KEY ("idTeacher") REFERENCES "Teach- ers"("idTeacher"); -- -- TOC entry 1865 (class 2606 OID 16451) -- Name: fk_sess_subj; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY "Sessions" ADD CONSTRAINT fk_sess_subj FOREIGN KEY ("idSubject") REFERENCES "Sub- jects"("idSubject"); -- -- TOC entry 1866 (class 2606 OID 16456) -- Name: fk_sess_tea; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY "Sessions" ADD CONSTRAINT fk_sess_tea FOREIGN KEY ("idTeacher") REFERENCES "Teach- ers"("idTeacher"); 34 -- -- TOC entry 1990 (class 0 OID 0) -- Dependencies: 5 -- Name: public; Type: ACL; Schema: -; Owner: postgres -- REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM postgres; GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO PUBLIC; -- Completed on 2014-08-04 23:54:32 -- -- PostgreSQL database dump complete -- При внимательном рассмотрении трех сгенерированных скриптов видно, что основные команды по созданию таблиц и ограничений первичного и внеш- него ключей почти не отличаются для этих трех СУБД. Дело в том, что язык SQL является стандартом для работы с базами данных и все современные реля- ционные базы данных стараются соблюдать этот стандарт. В основном, суще- ственные различия заключаются только в используемых типах данных и не- больших дополнениях, связанных с особенностями задания владельца таблицы (пользователя, который создал таблицу и, следовательно, имеет максимальные права для работы с ней), используемой кодировки и других настроек СУБД. Нередко первоначальное проектирование выполняется с ошибками или недочетами (не все условия учтены, требуются новые столбцы или, напротив, какие-то столбцы являются лишними). Очевидно, что необходимы средства для обеспечения простого внесения изменений в таблицы. Этим средством является команда SQL ALTER TABLE, которую используют для корректировки списка столбцов таблицы и наложения разных ограничений как на отдельные столбцы, так и на таблицу в целом. Покажем на нескольких примерах, как можно ис- пользовать эту команду. Выполнение SQL-команд осуществляется в оболочках с помощью специ- альных окон редактирования и выполнения SQL-скриптов. В dbForge Studio его можно создать с помощью меню «Новый»-> «SQL». В pgAdmin окно выполне- ния пользовательских запросов можно вызвать с помощью специальной кнопки 35 на панели инструментов . После создания ограничения можно увидеть как объекты соответствующих таблиц в дереве элементов базы данных (ограниче- ния или индексы): Рис. 19. Ограничение на проверку баллов в dbForge для SQL Server. Рис.20. Ограничение на провер- ку баллов в pgAdmin. Пример 1. Добавим ограничение уникальности на название кафедры в таблице Departments: MS SQL Server, MySQL: ALTER TABLE Departments ADD CONSTRAINT un_title UNIQUE (TitleDepartment); PostgreSQL (отличием является заключением в кавычки имен таблиц, ограничения, столбцов): ALTER TABLE "Departments" ADD CONSTRAINT "un_title" UNIQUE ("TitleDepartment"); 36 Пример 2. Требуется добавить ограничение проверки условия для табли- цы оценок, означающее, что нижняя граница баллов должна быть меньше верх- ней. MS SQL Server: ALTER TABLE Marks ADD CONSTRAINT check_balls CHECK (LowBalls |