CREATE TABLE dbo.Subjects ( idSubject int IDENTITY, TitleSubject varchar(50) NOT NULL,

Стр 1 из 3Следующая ⇒

Nbsp; «ТЕХНОЛОГИИ БАЗ ДАННЫХ» СОДЕРЖАНИЕ ВВЕДЕНИЕ .................................................................................................................. 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 3 ВВЕДЕНИЕ Базы данных являются одной из основных составляющих большинства современных приложений, особенно прикладного или аналитического характе- ра. Любое предприятие имеет свою базу данных (а, возможно, и множество баз данных). Заходя в интернет, мы видим информацию из баз данных через серви- сы социальных сетей, интернет-магазинов, электронных университетов и др. Немало математических задач связано с использованием баз данных. Примером тому являются задачи анализа данных или машинного обучения. Таким обра- зом, знание и навыки работы с базами данных становятся неотъемлемой со- ставляющей компетенции современного ИТ-специалиста. Данное учебно- методическое пособие призвано помочь студенту в практической форме приоб- рести необходимые навыки работы с базами данных и их использованием в различных приложениях. Разработка приложения, использующего базу данных, включает в себя множество задач. Во-первых, требуется сформировать логическую модель базы данных и, как следствие, набор таблиц, которые будут хранить данные. Вторым моментом является выбор системы управления базами данных (СУБД), на ко- тором будет храниться база. Именно СУБД отвечают за выполнение основных операций, выполняемых с базой данных. Во многом этот выбор зависит от масштабов создаваемого приложения. В дальнейшем следует определить сер- верную часть приложения, включающую определения целостности данных, серверные процедуры, позволяющие выполнять основные преобразования дан- ных. Только после решения всех этих вопросов речь заходит о клиентской ча- сти приложения работы с базой данных. Некоторые СУБД имеют собственные средства создания клиентской части (например, MS FoxPro или более популяр- ный MS Access), но в большинстве своем современные СУБД являются сервер- ными, т.е. предоставляют средства доступа к данным из других приложений. Этот момент позволяет создавать гибкий пользовательский интерфейс на тех технологиях, которые являются более приемлемыми для пользователя. Отдель- 4 ным вопросом функционирования приложения базы данных являются вопросы экспорта и импорта данных из других источников информации и агрегация ин- формации из различных источников для предоставления сводной и аналитиче- ской отчетности (концепция хранилищ данных). Пособие создано в поддержку практикума по курсу «Технологии баз дан- ных», который реализуется в Казанском (Приволжском) федеральном универ- ситете на кафедре системного анализа и информационных технологий. За время практикума каждый студент должен разработать собственное приложение баз данных, которое обязательно должно включать следующие элементы: 1. Создание логической модели базы данных. Описание ER-модели, генера-ция на ее основе реляционной модели данных. 2. Реализация модели в СУБД. В качестве СУБД могут быть выбраны: MS SQL Server, MySQL или PostgreSQL или иное серверное СУБД. 3. Заполнение базы данных. 4. Создание различных запросов на получение данных (для формирования навыков работы с реализацией различных операций реляционной алгеб-ры). Для каждой из операций (исключая деление) нужно показать мини-мум три запроса (хотя один и тот же запрос может демонстрировать вы-полнение нескольких операций). 5. Создание хранимых процедур и триггеров для обеспечения серверной ча-сти работы с данными. 6. Создание клиентского windows-приложения для работы с базой данных. Приложение должно иметь возможности добавления, изменения и удале-ния информации. 7. Создание модулей экспорта и импорта информации в базу данных (инте-грация с xml-файлами). 8. Реализация концепции хранилищ данных на примере создания OLAP- куба для многомерного поиска данных для публикации в отчетах. Каждая из перечисленных задач рассматривается в учебно-методическом пособии на примере создания элементов приложения «Деканат», с помощью 5 которого предоставляются возможности отслеживать оценки, которые получа- ют студенты во время сессии. Структурно в учебно-методическом пособии бу- дет выделено три главы, посвященные разработке серверных средств (базы данных и серверных процедур), разработке клиентской части приложения и введению в концепции хранилищ данных. В качестве средств разработки (программного обеспечения) нужно вы- брать сервер баз данных, т.е. СУБД, инструментальную оболочку для работы с выбранным сервером, технологию создания клиентского интерфейса. В качестве сервера баз данных можно использовать: · 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. 6 Создание соединения оболочки с сервером производится с помощью меню «База данных» -> «Новое подключение…». Здесь вводятся параметры подключения и имя, по которому в дальнейшем к этому подключению мож- но будет обращаться: Рис. 2. Параметры соединения с базой данных MS SQL Server. · MySQL (версии с 5.0). Этот бесплатный сервер баз данных устанавлива-ется отдельно и конфигурируется с помощью специального wizard’a. Об-ратим внимание не то, что при конфигурировании экземпляра сервера требуется установить параметры учетной записи. По умолчанию, логин и пароль для сервера root. В качестве оболочки для работы с сервером MySQL можно использовать программный пакет MySQL Workbench – это свободное программное обеспечение, которое содержит средства мо-делирования, администрирования сервера и визуальной работы с базами данных, размещенными на нем. 7 Рис. 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), логин и пароль учетной записи пользователя, а также 8 имя подключения. Еще не следует забывать на вкладке «Дополнительно» установить кодировку данных (сейчас настройки наиболее часто используют кодировку utf8) (MySQL очень чувствителен к кодировкам и отсутствие настройки кодировки может привести к проблемам с данными, написанными кириллицей): Рис. 5. Параметры соединения с базой данных MySQL. · PostgreSQL также является свободным сервером баз данных. Также имеет оболочку проектирования pgAdmin. Существует уже оболочка dbForge Studio для PostgreSQL, однако на момент написания данного текста она была платным программным обеспечением. Рис. 6. Окно программы pgAdmin. 9 При установке сервера PostgreSQL и его дополнительного программ- ного обеспечения будут запрошены параметры учетной записи пользовате- ля. По умолчанию создается запись с логином postgres, пароль к которой устанавливает пользователь в момент установки. Аналогично MySQL, Post-greSQL идентифицируется хостом и номером порта (по умолчанию, 5432). 10 ЧАСТЬ I. СЕРВЕРНЫЕ ТЕХНОЛОГИИ 1.1. МОДЕЛЬ ДАННЫХ Разберем принципы формирования модели базы данных на примере приложения «Деканат». Модель будет создаваться с помощью инструментов моделирования данных в различных оболочках. Описание задачи. Пусть требуется хранить и управлять информацией о результатах обучения студентов: об учебных группах; студентах, обучаю- щихся в этих группах; дисциплинах, которые изучаются и сдаются в разные семестры; преподавателях, которые ведут эти дисциплины; оценках, кото- рые были получены студентами при сдаче зачетов/экзаменов. Существует несколько концепций моделей баз данных (иерархическая, сетевая, объектная, реляционная). Наиболее распространенной моделью яв- ляется реляционная модель, которая очень тесно переплетается с принципа- ми объектно-ориентированного анализа и еще одного популярного подхода в моделировании данных – ER-модели (модель «сущность-связь»). ER-модель удобна для начального проектирования, поскольку она ин- туитивно понятна большинству пользователей. В ней выделяются понятия сущности (основные объекты базы), атрибуты (свойства сущности) и связи (взаимодействия между сущностями). В ряде оболочек именно в этих тер- минах и создан сервис создания модели данных. Реляционная модель представляет всю базу данных как набор связан- ных таблиц. Большинство таблиц отвечает за хранение информации о сущ- ностях (столбцы таблиц характеризуют их атрибуты). Среди атрибутов сущ- ности выделяют ключевые атрибуты – атрибуты, которые являются иденти- фицирующими, точно определяющими запись, объект сущности. С помо- щью внедрения ключевых атрибутов одних сущностей (родительские табли- цы) в качестве столбцов в другие таблицы (дочерние) реализуются различ- ные связи между сущностями. 11 Построение модели с помощью оболочки MySQL Workbench (версия

CE).

Рис. 7. Создание модели базы данных в MySQL Workbench.

Создаем новую ER-модель и диаграмму в модели. В полученном окне модели представлено полотно, на которое можно наносить новые таблицы, с

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

таблиц и с помощью панели инструментов создать связи между таблицами.

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

Полотно для создания модели

Панель

инструментов

Рис. 8. Вид окна редактирования модели данных.

Новая таблица

Новое представление

Связь 1:1
Связь 1: n	Связь 1:1	(с до-
	бавлением	клю-
	ча в дочернюю
	таблицу)

Связь m : n

Связь 1 : n для существующих столбцов

Связь 1 : n (с до-бавлением клю-ча в дочернюю таблицу)

Рис. 9. Состав панели инструментов окна редактирования модели данных.

Проведем анализ состава таблиц для решаемой задачи. При описании

столбцов таблицы поля, входящие в первичный ключ, будут подчеркнуты.

Имеется таблица Студенты (Students): (№Зач.книжки, ФИОСту-

дента, №Группы).

Для хранения групп не будем выделять отдельную таблицу.

Имеется таблица Преподаватель (Teachers): (№Преподавателя,

ФИОПреподавателя, Должность, №Кафедры).

Чтобы избежать дублирования информации с названием кафедры вве-

дем справочную таблицу кафедр: таблица Кафедра (Departments):

(№Кафедры, Название, Телефон).

Имеется таблица учебных дисциплин Дисциплина (Subjects):

(№Дисциплины, Название).

Таблица Сессия содержит информацию о том, каков состав зачетов и экзаменов для каждой конкретной группы по семестрам, каким преподавате-

лям следует сдавать зачеты и экзамены: Sessions (№Группы, №Семестра,

№Дисциплины, Отчетность, №Преподавателя).Заметим,что отчетностьможет определяться номером дисциплины и номером семестра, но в предпо-

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

Наконец, результаты сдачи сессии хранятся в таблице результатов

Results (№Студента, №Группы, №Семестра,№Дисциплины, Баллы,

ДатаСдачи, Оценка).Окончательную оценку хранить не требуется,так какона определяется количеством набранных баллов и таблицей оценок.

Marks (Оценка, НижняяГраница, ВерхняяГраница) –эта таблицаявляется справочной и не связана с основными таблицами базы. Ее роль за-

ключается в определении правильной оценки по набранным баллам.

В результате данного анализа задачи получится следующая модель: - сначала формируется состав таблиц без связующих атрибутов:

Рис. 10. Модель таблиц базы данных «Деканат» без указания связей.

- затем устанавливаем связи. Заметим, что можно было бы все связующие атрибуты сразу добавить в таблицы. Тогда все связи можно было бы добавить как связи «один-ко-многим» для существующих столбцов. Отметим также, что связь таблицы результатов и сессии не является очевидной, так как сессия зави-

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

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

давателя.

Рис. 11. Модель таблиц базы данных «Деканат» с указанием связей.

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

Построение модели в оболочке dbForge Studio для SQL Server

Новую модель (диаграмму) базы данных можно создать с помощью меню

«База данных»-> «Диаграмма БД».

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

ния, создать столбцы (атрибуты) таблиц и с помощью панели инструментов со-

здать связи между таблицами.

Полотно для созда-

ния модели

Панель инструментов

Рис.12. Окно редактирования диаграммы базы данных.

На панели инструментов следует отметить пока только две кнопки

«Новая таблица», «Новая связь», которые позволяют создать новую таблицу,

определив ее состав столбцов, первичные ключи и основные ограничения, и

создать связи между таблицами, определив тем самым ограничения внешне-

го ключа.

Рис. 13. Окно создания столбцов таблицы.

Для столбцов можно задать простые ограничения: допустимы ли пу-

стые значения и определяет ли столбец поле-счетчик. Кроме того, можно выбрать столбцы, определяющие первичный ключ.

При создании связи требуется «нарисовать» мышью линию от дочер-

ней таблицы к родительской. Для подтверждения параметров связи будет

показано окно, в котором нужно уточнить имена полей родительской и до-

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

Рис. 14. Окно задания параметров внешнего ключа.

На вкладках «Ограничения» и «Индексы» можно увидеть все ограни-

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

На вкладке T-SQL можно увидеть SQL-команду, выполнение которой экви-

валентно выполнению всех сделанных настроек.

Рис. 15. Команда SQL создания таблицы «Студенты».

Отметим, что построитель модели синхронизирует все действия поль-

зователя с базой данных, создавая указанные таблицы вместе со всеми огра-

ничениями.

Таким образом, будет получена следующая модель:

Рис. 16. Модель данных, построенная с помощью dbForge Studio.

Замечания относительно синхронизации номера группы в таблицах «Сес-

сия» и «Студент» остаются на уровне модели нерешенным.

Аналогичным образом создается модель и, соответственно, база данных в среде dbForge Studio для MySQL.

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

Рис. 17. Вид окна редактирования таблицы.

Действия с базой данных можно производить с помощью контекстного меню соответствующего элемента (таблицы, столбца, ограничения) в дереве объектов сервера. Настройки любого элемента производятся с помощью пункта контекстного меню «Свойства».

1.2. ПЕРЕНОС БАЗЫ ДАННЫХ НА ДРУГОЙ СЕРВЕР

Любое СУБД имеет средства резервного копирования базы данных. Та-

кому копированию подвергаются как метаданные (структура данных базы), так и сами данные. Конечно, каждое СУБД имеет свои собственные форматы, но традиционным форматом является сохранение в виде последовательности SQL-

команд (создания, вставки, изменения) (SQL-скрипт), выполнение которых приведет к текущему состоянию базы данных.

В оболочке dbForge Studio для SQL Server создание резервной копии

(backup) можно осуществить двумя способами:

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

IF DB_NAME() <> N'master' SET NOEXEC ON

-- Создать базу данных "proba"

PRINT (N'Создать базу данных "proba"')

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%

)

-- Изменить базу данных

PRINT (N'Изменить базу данных')

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,

QUOTED_IDENTIFIER OFF,

READ_COMMITTED_SNAPSHOT OFF,

RECOVERY SIMPLE,

RECURSIVE_TRIGGERS OFF,

TRUSTWORTHY OFF

WITH ROLLBACK IMMEDIATE

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)

USE proba

IF DB_NAME() <> N'proba' SET NOEXEC ON

-- Создать таблицу "dbo.Teachers"

PRINT (N'Создать таблицу "dbo.Teachers"')

CREATE TABLE dbo.Teachers ( idTeacher int IDENTITY, FIOTeacher varchar(50) NOT NULL, idDepartment int NOT NULL,

CONSTRAINT PK_Teachers PRIMARY KEY (idTeacher)

)

ON [PRIMARY]

-- Создать таблицу "dbo.Subjects"

PRINT (N'Создать таблицу "dbo.Subjects"')

CREATE TABLE dbo.Subjects ( idSubject int IDENTITY, TitleSubject varchar(50) NOT NULL,

Дата добавления: 2018-06-01; просмотров: 287; Мы поможем в написании вашей работы!

Поделиться с друзьями:

12 3 Следующая ⇒

Мы поможем в написании ваших работ!