Лабораторный практикум по курсу «технологии баз данных»

ЛАБОРАТОРНЫЙ ПРАКТИКУМ ПО КУРСУ «ТЕХНОЛОГИИ БАЗ ДАННЫХ»

 

Учебное пособие

 

 

 

 

 

СОДЕРЖАНИЕ

ВВЕДЕНИЕ

ЧАСТЬ I. СЕРВЕРНЫЕ ТЕХНОЛОГИИ

1.1. МОДЕЛЬ ДАННЫХ

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

1.3. КОМАНДЫ МОДИФИКАЦИИ ДАННЫХ (DML)

1.4. ВЫБОРКА ДАННЫХ. ОПЕРАТОР SELECT (DQL)

1.5. ХРАНИМЫЕ ПРОЦЕДУРЫ. ФУНКЦИИ И ТРИГГЕРЫ

Часть II. КЛИЕНТСКИЕ ТЕХНОЛОГИИ

2.1. ВЫПОЛНЕНИЕ ЗАПРОСА К БАЗЕ ДАННЫХ ИЗ КЛИЕНТСКОГО

ПРИЛОЖЕНИЯ.

2.2. ПАРАМЕТРЫ ЗАПРОСА

2.3. ВЫПОЛНЕНИЕ КОМАНД DML.

2.4. ПОНЯТИЕ НАБОРА ДАННЫХ КАК ВИРТУАЛЬНОЙ БАЗЫ ДАННЫХ

2.5. СВЯЗЬ НАБОРА ДАННЫХ И БАЗЫ ДАННЫХ

2.6. КАК СИНХРОНИЗИРОВАТЬ ИЗМЕНЕНИЯ В НАБОРЕ ДАННЫХ С БАЗОЙ ДАННЫХ

2.7. ПОЛЬЗОВАТЕЛЬСКИЙ ИНТЕРФЕЙС НА ОСНОВЕ ТАБЛИЦ

2. 8.ПОЛЬЗОВАТЕЛЬСКИЙ ИНТЕРФЕЙС НА ОСНОВЕ ОДНОЙ ЗАПИСИ.

2.9. ГЕНЕРАЦИЯ ОТЧЕТОВ И ПЕЧАТНЫХ ФОРМ

2.10. ГЕНЕРАЦИЯ ОТЧЕТОВ В ФОРМАТЕ XML.

ЧАСТЬ III. ВВЕДЕНИЕ В ХРАНИЛИЩА ДАННЫХ

3.1. ПРОЕКТИРОВАНИЕ ХРАНИЛИЩ ДАННЫХ

3.2. ЗАГРУЗКА ДАННЫХ

3.3. ПОИСК ИНФОРМАЦИИ В ХРАНИЛИЩЕ

3.4. ПОСТРОЕНИЕ ОТЧЕТОВ С ПОМОЩЬЮ ЗАПРОСОВ К ХРАНИЛИЩУ

СПИСОК ЛИТЕРАТУРЫ

ВВЕДЕНИЕ

 

Базы данных являются одной из основных составляющих большинства современных приложений, особенно прикладного или аналитического характера. Любое предприятие имеет свою базу данных (а, возможно, и множество баз данных). Заходя в интернет, мы видим информацию из баз данных через сервисы социальных сетей, интернет-магазинов, электронных университетов и др. Немало математических задач связано с использованием баз данных. Примером тому являются задачи анализа данных или машинного обучения. Таким образом, знание и навыки работы с базами данных становятся неотъемлемой составляющей компетенции современного ИТ-специалиста. Данное учебнометодическое пособие призвано помочь студенту в практической форме приобрести необходимые навыки работы с базами данных и их использованием в различных приложениях. 

Разработка приложения, использующего базу данных, включает в себя множество задач. Во-первых, требуется сформировать логическую модель базы данных и, как следствие, набор таблиц, которые будут хранить данные. Вторым моментом является выбор системы управления базами данных (СУБД), на котором будет храниться база. Именно СУБД отвечают за выполнение основных операций, выполняемых с базой данных. Во многом этот выбор зависит от масштабов создаваемого приложения. В дальнейшем следует определить серверную часть приложения, включающую определения целостности данных, серверные процедуры, позволяющие выполнять основные преобразования данных. Только после решения всех этих вопросов речь заходит о клиентской части приложения работы с базой данных. Некоторые СУБД имеют собственные средства создания клиентской части (например, MS FoxPro или более популярный MS Access), но в большинстве своем современные СУБД являются серверными, т.е. предоставляют средства доступа к данным из других приложений. Этот момент позволяет создавать гибкий пользовательский интерфейс на тех технологиях, которые являются более приемлемыми для пользователя. Отдельным вопросом функционирования приложения базы данных являются вопросы экспорта и импорта данных из других источников информации и агрегация информации из различных источников для предоставления сводной и аналитической отчетности (концепция хранилищ данных).

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

1. Создание логической модели базы данных. Описание ER-модели, генерация на ее основе реляционной модели данных.

2. Реализация модели в СУБД. В качестве СУБД могут быть выбраны: MS SQL Server, MySQL или PostgreSQL или иное серверное СУБД.

3. Заполнение базы данных.

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

5. Создание хранимых процедур и триггеров для обеспечения серверной части работы с данными.

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

7. Создание модулей экспорта и импорта информации в базу данных (интеграция с xml-файлами).

8. Реализация концепции хранилищ данных на примере создания OLAPкуба для многомерного поиска данных для публикации в отчетах.

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

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

В качестве сервера баз данных можно использовать:

• 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.

Создание соединения оболочки с сервером производится с помощью меню «База данных» -> «Новое подключение…». Здесь вводятся параметры подключения и имя, по которому в дальнейшем к этому подключению можно будет обращаться:

 

Рис. 2. Параметры соединения с базой данных MS SQL Server.

• MySQL (версии с 5.0). Этот бесплатный сервер баз данных устанавливается отдельно и конфигурируется с помощью специального wizard’a. Обратим внимание не то, что при конфигурировании экземпляра сервера требуется установить параметры учетной записи. По умолчанию, логин и пароль для сервера root. В качестве оболочки для работы с сервером MySQL можно использовать программный пакет MySQL Workbench – это свободное программное обеспечение, которое содержит средства моделирования, администрирования сервера и визуальной работы с базами данных, размещенными на нем.

 

Рис. 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), логин и пароль учетной записи пользователя, а также имя подключения. Еще не следует забывать на вкладке «Дополнительно» установить кодировку данных (сейчас настройки наиболее часто используют кодировку utf8) (MySQL очень чувствителен к кодировкам и отсутствие настройки кодировки может привести к проблемам с данными, написанными кириллицей):

 

Рис. 5. Параметры соединения с базой данных MySQL.

 PostgreSQL также является свободным сервером баз данных. Также имеет оболочку проектирования pgAdmin. Существует уже оболочка dbForge Studio для PostgreSQL, однако на момент написания данного текста она была платным программным обеспечением.

 

 

Рис. 6. Окно программы pgAdmin.

При установке сервера PostgreSQL и его дополнительного программного обеспечения будут запрошены параметры учетной записи пользователя. По умолчанию создается запись с логином postgres, пароль к которой устанавливает пользователь в момент установки. Аналогично MySQL, PostgreSQL идентифицируется хостом и номером порта (по умолчанию, 5432).

МОДЕЛЬ ДАННЫХ

 

Разберем принципы формирования модели базы данных на примере приложения «Деканат». Модель будет создаваться с помощью инструментов моделирования данных в различных оболочках.

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

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

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

Реляционная модель представляет всю базу данных как набор связанных таблиц. Большинство таблиц отвечает за хранение информации о сущностях (столбцы таблиц характеризуют их атрибуты). Среди атрибутов сущности выделяют ключевые атрибуты – атрибуты, которые являются идентифицирующими, точно определяющими запись, объект сущности. С помощью внедрения ключевых атрибутов одних сущностей (родительские таблицы) в качестве столбцов в другие таблицы (дочерние) реализуются различные связи между сущностями.

CE).

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

 

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

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

 

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

 

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

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

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

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

CREATE DATABASE proba

ON PRIMARY(

NAME = N'proba',

FILENAME = N'c:\Program Files\Microsoft SQL Server\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 Server\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,

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.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, idTeacher)

)

ON [PRIMARY]

GO 

--

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

--

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

GO

Semestr)

)

ON [PRIMARY]

GO 

--

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

--

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

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-

ALTER TABLE dbo.Sessions

ADD CONSTRAINT FK_Sessions_Teachers_idTeacher FOREIGN KEY (idTeacher) REFER-

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-

ALTER TABLE dbo.Results

ADD CONSTRAINT FK_Results_Teachers_idTeacher FOREIGN KEY (idTeacher) REFER-

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 (

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,

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: 

--

 

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

);

 

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;

\. 

--

-- 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; Tablespace: 

--

 

ALTER TABLE ONLY "Departments"

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; Tablespace: --

 

ALTER TABLE ONLY "Results"

ADD CONSTRAINT pk_results PRIMARY KEY ("idStudent", "idTeacher", "idSubject", "NumSemestr");

 

--

-- TOC entry 1859 (class 2606 OID 16422)

-- Name: pk_sessions; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: --

 

ALTER TABLE ONLY "Sessions"

ADD CONSTRAINT pk_sessions PRIMARY KEY ("NumGroup", "NumSemestr", "idSubject", "idTeacher");

 

--

-- TOC entry 1861 (class 2606 OID 16430)

-- Name: pk_students; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: --

 

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; Tablespace: --

 

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; Tablespace: --

 

ALTER TABLE ONLY "Teachers"

ADD CONSTRAINT pk_teacher PRIMARY KEY ("idTeacher");

 

--

-- 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 "Departments"("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 "Students"("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 "Subjects"("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 "Teachers"("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 "Subjects"("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 "Teachers"("idTeacher");

 

--

-- 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 окно выполнения пользовательских запросов можно вызвать с помощью специальной кнопки на панели инструментов . После создания ограничения можно увидеть как объекты соответствующих таблиц в дереве элементов базы данных (ограничения или индексы):

 

Рис. 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");

 

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

MS SQL Server:

ALTER TABLE Marks ADD CONSTRAINT check_balls 

             CHECK (LowBalls<HighBalls);

 

Для MySQL эта команда выполняется, но как таковой объект базы данных не создается.

 

PostgreSQL:

ALTER TABLE "Marks" ADD CONSTRAINT "check_balls" 

               CHECK ("LowBalls"<"HighBalls");

 

Пример 3. Требуется добавить ограничение проверки условия для поля телефона кафедры – телефон должен состоять из 7 цифр и иметь формат «ххххх-хх». Можно также, как и в предыдущем примере, добавить ограничение CHECK. Однако мы для демонстрации возможностей команды ALTER TABLE сначала удалим столбец телефона кафедры, а потом добавим новый столбец с учетом ограничения. В телефоне первая цифра 2 или 5, остальные цифры могут быть любыми. Для этого используется конструкция языка LIKE, задающая шаблон записи телефона (для PostgreSQL это конструкция SIMILAR TO):

 

MS SQL Server, MySQL:

ALTER TABLE Departments DROP COLUMN PhoneDepartment;

ALTER TABLE Departments ADD PhoneDepartment VARCHAR(9) CHECK 

(PhoneDepartment LIKE '[2,5][0-9][0-9]-[0-9][0-9]-[0-9][0-9]');

 

PostgreSQL:

ALTER TABLE "Departments" DROP COLUMN "PhoneDepartment";

ALTER TABLE "Departments" ADD "PhoneDepartment" text CHECK

("PhoneDepartment" SIMILAR TO 

              '(2|5)[0-9][0-9]-[0-9][0-9]-[0-9][0-9]');

 

Пример 4. Введем ограничение на согласованность баллов и оценки в таблице результатов сессии. Будем полагать для простоты, что в таблицу заносятся только положительные результаты сдачи зачетов и экзаменов. Таким образом, оценка должна быть только 3, 4 или 5. При этом должна быть учтена согласованность баллов исходя из шкалы принятой балльно-рейтинговой системы:

MS SQL Server, MySQL:

ALTER TABLE Results ADD CONSTRAINT ch_res_marks CHECK (Mark IN (3,4,5) AND ((Mark=3 AND Balls BETWEEN 55 AND 70) OR (Mark=4 AND Balls BETWEEN 71 AND 85) OR (Mark=5 AND Balls BETWEEN 86 AND 100)));

 PostgreSQL:

ALTER TABLE "Results" ADD CONSTRAINT "ch_res_marks" CHECK ("Mark" IN (3,4,5) AND (("Mark"=3 AND "Balls" BETWEEN 55 AND 70) OR ("Mark"=4 AND "Balls" BETWEEN 71 AND 85) OR ("Mark"=5 AND "Balls" BETWEEN 86 AND 100)));

 

Заметим, что для аналогичных целей была предназначена таблица Marks.

В дальнейшем мы будем использовать ее.

Пример 5. В MySQL иногда требуется явно указать кодировку данных таблиц. Это также можно сделать с помощью команды ALTER TABLE:

ALTER TABLE `Departments` CONVERT TO CHARACTER SET utf8;

 

MS SQL Server:

CREATE FUNCTION dbo.IsCorrect(@idStud INT, @idSubj INT, 

                         @Sem INT, @idTeach INT) RETURNS INT

AS

BEGIN

IF EXISTS (SELECT * FROM Students INNER JOIN Sessions 

                 ON Students.NumGroup=Sessions.NumGroup 

                 INNER JOIN Subjects ON 

                 Sessions.idSubject=Subjects.idSubject

                 INNER JOIN Teachers ON 

                 Sessions.idTeacher=Teachers.idTeacher

                 WHERE Students.idStudent=@idStud AND 

                 Subjects.idSubject=@idSub

                 AND Teachers.idTeacher=@idTeach and NumSemestr=@Sem)

   RETURN 1;

RETURN 0;

END GO

 

 

MySQL:

CREATE FUNCTION IsCorrect (idStud INT, idSubj INT, Sem INT, idTeach INT)    RETURNS INT(11)

BEGIN

RETURN EXISTS (SELECT * FROM Students INNER JOIN Sessions 

                ON Students.NumGroup=Sessions.NumGroup 

                INNER JOIN Subjects ON 

                   Sessions.idSubject=Subjects.idSubject                 INNER JOIN Teachers ON 

                Sessions.idTeacher=Teachers.idTeacher

                WHERE Students.idStudent=idStud AND 

                Subjects.idSubject=idSubj

                AND Teachers.idTeacher=idTeach and NumSemestr=Sem); END

 

PostgreSQL:

CREATE FUNCTION IsCorrect(integer, integer, integer, integer) 

                                              RETURNS BOOLEAN AS $$ BEGIN

RETURN EXISTS (SELECT * from "Students" INNER JOIN "Sessions" 

                ON "Students"."NumGroup"="Sessions"."NumGroup" 

                INNER JOIN "Subjects" ON

                  "Sessions"."idSubject"="Subjects"."idSubject" 

                INNER JOIN "Teachers" ON

                    "Sessions"."idTeacher"="Teachers"."idTeacher" 

                WHERE "Students"."idStudent"=$1 AND 

                "Subjects"."idSubject"=$2

                AND "Teachers"."idTeacher"=$3 AND "NumSemestr"=$4); END;

$$ LANGUAGE plpgsql;

 

Триггер на вставку записи в таблицу Results будет вызывать функцию проверки корректности, передавая в функцию поля из новой записи. Если запись будет корректной, будут скорректированы поля оценки и даты сдачи зачета/экзамена. В противном случае должен быть произведен откат транзакции. MS SQL Server:

При вставке записи сначала запись попадает в виртуальную таблицу inserted (при удалении будет использоваться таблица deleted, при изменении записи используются обе таблицы – в inserted хранятся новые значения записи, в deleted – прежние значения полей записи). Поэтому сначала получаем данные новой записи из таблицы inserted, после чего проверяем их на корректность. В случае корректных данных оставшиеся поля (дата и оценка) изменяются посредством команды UPDATE. Откат транзакции в случае некорректных данных производится с помощью команды ROLLBACK.

 

CREATE TRIGGER trigger1

ON dbo.Results

FOR INSERT

AS

BEGIN

-- объявление необходимых переменных для хранения данных новой записи

DECLARE @idStudent INT, @idSubject INT, 

     @idTeacher INT, @NumSemestr INT, @Balls INT;

 

-- чтение данных новой записи

SET @idStudent =(SELECT idStudent FROM inserted);

SET @idSubject =(SELECT idSubject FROM inserted);

SET @idTeacher =(SELECT idTeacher FROM inserted);

SET @NumSemestr =(SELECT NumSemestr FROM inserted);

SET @Balls =(SELECT Balls FROM inserted);   

-- проверка на корректность данных

IF dbo.IsCorrect(@idStudent, @idSubject, @NumSemestr, @idTeacher)=0 

BEGIN

-- данные некорректны. Выводим сообщение об ошибке 

-- и производим откат транзакции

PRINT 'Ошибка данных: данные некорректны';

ROLLBACK;

END

ELSE

-- изменение полей даты и вычисление оценки. 

-- В условии указывается первичный ключ

UPDATE dbo.Results SET mark=dbo.GetMark3(@Balls), DateExam=GETDATE() 

                WHERE idStudent=@idStudent AND idSubject=@idSubject                  AND idTeacher=@idTeacher AND NumSemestr=@NumSemestr; END GO

 

MySQL:

Для MySQL данный триггер запишется проще, так как здесь проще получить данные новой записи. Новая запись хранится в виде объекта New (запись при удалении хранится в виде объекта Old). Однако имеется проблема, связанная с отсутствием команды отката триггера. В этом случае рекомендуется выполнить какую-нибудь ошибочную команду, например, вставить запись с уже существующим ключом. Ошибка в этой команде приведет к отмене действий всей транзакции (команды и триггера).

CREATE TRIGGER decanat.trigger1

   BEFORE INSERT

   ON decanat.results

   FOR EACH ROW

BEGIN

IF IsCorrect(New.idStudent, New.idSubject, New.NumSemestr, New.idTeacher)   THEN

   SET New.Mark=GetMark3(New.Balls);

   SET New.DateExam=Now();   ELSE

    insert into Departments values (1,"","");

END IF;

END

 

PostgreSQL:

В PostgreSQL триггер как таковой связан со специальной триггерной функцией, в которой и осуществляется вся обработка данных. Триггерная функция возвращает объект-запись (NEW или OLD), с которой производится работа. При написании триггера мы указываем только для какой операции, для какой таблицы и каков тип триггера, после чего вызываем триггерную функцию. Откат производится генерацией исключительной ситуации с указанием сообщения об ошибке. В остальном код похож на тот, что писался для MySQL:

-- Создание триггерной функции на вставку результата сдачи экзамена 

CREATE FUNCTION trigger_results_insert() RETURNS trigger AS $$ 

BEGIN 

IF IsCorrect(NEW."idStudent", NEW."idSubject", 

                           NEW."idTeacher", NEW."NumSemestr")    THEN

   SELECT GetMark3(NEW."Balls") INTO NEW."Mark";

   SELECT Now() INTO New."DateExam"; 

ELSE

    -- генерация исключительной ситуации

    RAISE EXCEPTION 'Ошибка корректности данных';

END IF;

RETURN NEW;

END; 

$$ LANGUAGE plpgsql;

 

-- Создание триггера на вставку нового результата экзамена

CREATE TRIGGER tr_results_insert 

BEFORE INSERT ON "Results" FOR EACH ROW 

EXECUTE PROCEDURE trigger_results_insert();

 

Для проверки работы триггера (например, для MySQL) проведем следующие операции вставки:

INSERT INTO Results (idStudent, idSubject,idTeacher, NumSemestr, Balls)

VALUES (1,1,1,1,78);

INSERT INTO Results (idStudent, idSubject,idTeacher, NumSemestr, Balls) VALUES (2,1,1,1,98);

INSERT INTO Results (idStudent, idSubject,idTeacher, NumSemestr, Balls)

VALUES (6,1,1,1,68);

 

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

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

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

CREATE TABLE Reyting

(idStudent INT PRIMARY KEY, summ_balls INT, CONSTRAINT fk_reyting  

          FOREIGN KEY (idStudent) REFERENCES Students (idStudent)

)

  

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