Работа в среде Microsoft SQL SERVER 2008

Лабораторная работа 1

Анализ предметной области и построение концептуальной модели базы данных.

Цель лабораторной работы: Изучение приемов и приобретение практических навыков анализа предметной области (ПрОбл) и концептуального проектирования БД в процессе разработки информационной системы (ИС).

Содержание лабораторной работы. В ходе выполнения лабораторной работы студенты должны выполнить следующие задачи:

- определение целей разработки информационной системы в целом;

- исследование структуры предметной области (предприятия, организации, приложения и пр.) информационной системы;

- определение категорий пользователей информационной системы и распределение информационных задач между соответствующими приложениями (подсистемами);

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

- построение концептуальной модели БД.

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

- цель разработки информационной системы в целом;

- текстовое описание структуры предметной области информационной системы, включая структуру предприятия (организации, приложения), свойства различных объектов, взаимосвязи, ограничения и пр.;

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

Пример оформления таблицы, описывающей категории пользователей информационной системы:

№ п/п	Категория пользователей	Наименование приложения (подсистемы)	Информационные задачи

- табличное описание бизнес-процессов, относящихся к конкретному приложению информационной системы;

Пример оформления таблицы, описывающей бизнес-процессы информационной системы:

№ п/п	Бизнес-процесс	Исполнитель	Входные данные	Выходные данные	Ограничения
Поставщик	Содержание	Потребитель	Содержание
	Добавление преподавателя	Методист	Деканат	Предложение кафедры	Кафедра	Список преподавателей	- Каждому преподавателю должен быть назначен уникальный табельный номер - Необходимо исключить дублирование сведений о преподавателе
	Удаление/изменение данных о преподавателе	Методист	Деканат	Предложение кафедры	Кафедра	Список преподавателей	-
	...	...	...	...	...	...	...

- табличное описание типов сущностей предметной области;

Пример оформления таблицы, описывающей типы сущностей предметной области:

№	Наименование	Описание	Категория	Количество экземпляров	Режим обновления
	Преподаватель	Списочный состав преподавателей	Стержневая		5% в год
	...	...	...	...	...

- табличное описание типов связей между типами сущностей предметной области;

Пример оформления таблицы, описывающей типы связей между типами сущностей предметной области:

№	Тип сущности 1	Тип сущности 2	Описание	Мощность	Обязательность	Ограничения
	Преподаватель	Курс лекций	Преподаватель Читает Курс лекций/ Курс лекций Читается Преподавателем	1: М	Необязательная (Курс лекций может быть не закреплен за конкретным преподавателем)	За одним Преподавателем может быть закреплено не более трех курсов лекций в одном семестре
	...	...	...		...	...

- табличное описание атрибутов для каждого типа сущности;

Пример оформления таблицы, описывающей атрибуты типов сущностей предметной области:

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

Пример оформления графического представления концептуальной модели базы данных в виде диаграммы «сущность-связь»:

Примечание. Отчет может быть подготовлен, как в индивидуальном варианте (в виде отдельных отчетов для каждого члена бригады), так и в коллективном варианте (в виде единого отчета для бригады).

Лабораторная работа №2

Создание базы данных

Задание

1. С помощью команды CREATE DATABASE с оздать базу данных для заданной предметной области. Подробное описание всех команд приведено в [1].

2. С помощью команды CREATE TABLE создать все таблицы и ввести все ограничения, разработанные на этапе проектирования.

3. Добавить в БД таблицу, содержащую несколько столбцов. Выполнить модификацию характеристик столбцов, добавить столбец в таблицу, удалить столбец из таблицы (c помощью команды ALTER TABLE)

4. Отчет по лабораторной работе должен содержать команды SQL для создания БД и всех таблиц БД, команды модификации, а также диаграмму БД, построенную в SQL Server 2008. При создании таблиц следует задать ограничения целостности, разработанные при выполнении лабораторной работы №1.

Создание базы данных

CREATE DATABASE database_name [ON [DEFAULT|database_device][= size] [,database_device][= size]]...] [LOG ON database_device[= size] [,database_device[= size]...] [FOR LOAD]

Создает БД и, возможно, журнал транзакций на указанных devices, size - размер в мегабайтах. При создании новой базы данных используется как образец БД model.

CREATE DATABASE students

Создание таблиц, входящих в БД. Задание для их атрибутов типов данных и ограничений целостности, соответствующих смыслу таблиц.

Таблицы БД создаются с помощью команды CREATE TABLE. Эта команда создает пустую таблицу, то есть таблицу, не имеющую строк. Значения в эту таблицу вводятся с помощью команды INSERT. Команда CREATE TABLE определяет имя таблицы и множество поименованных столбцов в указанном порядке. Для каждого столбца должен быть определен тип и размер. Тип данных, для которого обязательно должен быть указан размер, — это CHAR. Реальное количество символов, которое может находиться в поле, изменяется от нуля (если в поле содержится NULL-значение) до заданного в CREATE TABLE максимального значения.

Ограничение NOT NULL

Чтобы запретить возможность использования в поле NULL-значений, можно при создании таблицы командой CREATE TABLE указать для соответствующего столбца ключевое слово NOT NULL. NULL — это специальный маркер, обозначающий тот факт, что поле пусто. Но он полезен не всегда. Первичные ключи, например, в принципе не должны содержать NULL-значений (быть пустыми), поскольку это нарушило бы требование уникальности первичного ключа (более строго — функциональную зависимость атрибутов таблицы от первичного ключа). Во многих других случаях также необходимо, чтобы поля обязательно содержали определенные значения. Если ключевое слово NOT NULL размещается непосредственно после типа данных (включая размер) столбца, то любые попытки оставить значение поля пустым (ввести в поле NULL-значение) будут отвергнуты системой.

Например, для того, чтобы в определении таблицы STUDENT запретить использование NULL-значений для столбцов STUDENT_ID, SURNAME и NAME, можно записать следующее:

CREATE TABLE STUDENT

(STUDENT_ID INTEGER NOT NULL,

SURNAME CHAR (25) NOT NOLL,

NAME CHAR (10) NOT NOLL,

STIPEND INTEGER,

KURS INTEGER,

CITY CHAR (15),

BIRTHDAY DATE,

UNIV_ID INTEGER);

Важно помнить: если для столбца указано NOT NULL, то при использовании команды INSERT обязательно должно быть указано конкретное значение, вводимое в это поле. При отсутствии ограничения NOT NULL в столбце значение может отсутствовать, если только не указано значение столбца по умолчанию (DEFAULT). Если при создании таблицы ограничение NOT NULL не было указано, то его можно указать позже, используя команду ALTER TABLE. Однако для того, чтобы для вновь вводимого с помощью команды ALTER TABLE столбца можно было задать ограничение NOT NULL, таблица, в которую добавляется столбец, должна быть пустой.

 

CREATE TABLE STUDENT

(STUDENT_ID INTEGER NOT NOLL UNIQUE,

SURNAME CHAR (25) NOT NULL,

NAME CHAR (10) NOT NULL,

STIPEND INTEGER,

KURS INTEGER,

CITY CHAR (15),

BIRTHDAY DATE,

UNIV_ID INTEGER);

Объявляя поле STUDENT_ID уникальным, можно быть уверенным, что в таблице не появится записей для двух студентов с одинаковыми идентификаторами. Столбцы, отличные от первичного ключа, для которых требуется поддержать уникальность значений, называются возможными ключами или уникальными ключами (CANDIDATE KEYS или UNIQUE KEYS).

MARK CHAR (1),

EXAM_DATE DATE NOT NULL,

CONSTRAINT STUD_SUBJ_CONSTR

UNIQUE (STUDENT_ID, EXAM_DATE);

В этом запросе STUD_SUBJ_CONSTR — это имя, присвоенное указанному ограничению таблицы.

 

CREATE TABLE STUDENT

(STUDENT_ID INTEGER PRIMARY KEY,

SURNAME CHAR (25) NOT NULL,

NAME CHAR (10) NOT NULL,

STIPEND INTEGER,

KURS INTEGER,

CITY CHAR (15),

BIRTHDAY DATE,

UNIV_ID INTEGER);

 

Составные первичные ключи

Ограничение PRIMARY KEY может также быть применено для нескольких полей, составляющих уникальную комбинацию значений — составной первичный ключ. Рассмотрим таблицу EXAM_MARKS. Очевидно, что ни к полю идентификатора студента (STUDENT_ID), ни к полю идентификатора предмета обучения (EXAM_ID) по отдельности нельзя предъявить требование уникальности. Однако для того, чтобы в таблице не могли появиться разные записи для одинаковых комбинаций значений полей STUDENT_ID И EXAM_ID (конкретный студент на конкретном экзамене не может получить более одной оценки), имеет смысл объявить уникальной комбинацию этих полей. Для этого мы можем применить ограничение таблицы PRIMARY KEY, объявив пару EXAM_ID И STUDENT_ID первичным ключом таблицы.

CREATE TABLE NEW_EXAM_MARKS

(STUDENT_ID INTEGER NOT NULL,

SUBJ_ID INTEGER NOT NULL,

MARK INTEGER,

DATA DATE,

CONSTRAINT EX_PR_KEY PRIMARY KEY (EXAM_ID, STUDENT_ID));

Проверка значений полей

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

Рассмотрим таблицу STUDENT. Значение столбца STIPEND в этой таблице выражается десятичным числом. Наложим на значения этого столбца ограничение — величина размера стипендии должна быть меньше 200.

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

CREATE TABLE STUDENT

(STUDENT_ID INTEGER PRIMARY KEY,

SURNAME CHAR (25) NOT NULL,

NAME CHAR (10) NOT NULL,

STIPEND INTEGER CHECK (STIPEND < 200),

KURS INTEGER,

CITY CHAR (15),

BIRTHDAY DATE,

UNIV_ID INTEGER);

 

CREATE TABLE STUDENT

(STUDENT_ID INTEGER PRIMARY KEY,

SURNAME CHAR(25) NOT NOLL,

NAME CHAR (10) NOT NULL,

STIPEND INTEGER,

KURS INTEGER,

CITY CHAR(15),

BIRTHDAY DATE,

UNIV_ID INTEGER UNIQUE,

CHECK (STIPEND < 200 AND CITY = 'Воронеж'));

 

или в несколько другой записи:

 

CREATE TABLE STUDENT

(STUDENT_ID INTEGER PRIMARY KEY,

SURNAME CHAR(25) NOT NULL,

NAME CHAR (10) NOT NULL,

STIPEND INTEGER,

KURS INTEGER,

CITY CHAR (15),

BIRTHDAY DATE,

UNIV_ID INTEGER UNIQUE,

CONSTRAINT STUD_CHECK CHECK (STIPEND < 200 AND CITY = 'Воронеж'));

CREATE TABLE STUDENT

(STUDENT_ID INTEGER PRIMARY KEY,

SURNAME CHAR (25) NOT NULL,

NAME CHAR (10) NOT NULL,

STIPEND INTEGER CHECK (STIPEND < 200),

KURS INTEGER,

BIRTHDAY DATE,

UNIV_ID INTEGER);

Другая цель практического применения задания значения по умолчанию — это использование его как альтернативы для NULL. Присутствие NULL в качестве возможных значений поля существенно усложняет интерпретацию операций сравнения, в которых участвуют значения таких полей, поскольку NULL представляет собой признак того, что фактическое значение поля неизвестно или неопределенно. Следовательно, строго говоря, сравнение с ним любого конкретного значения в рамках двузначной булевой логики является некорректным, за исключением специальной операции сравнения is NULL, которая определяет, является ли содержимое поля каким-либо значением или оно отсутствует. Действительно, каким образом в рамках двузначной логики ответить на вопрос, истинно или ложно условие CITY = 'Воронеж', если текущее значение поля CITY неизвестно (содержит NULL)?

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

Например, можно установить для столбца опцию NOT NULL, а для неопределенных значений числового типа установить значение по умолчанию «равно нулю», или для полей типа CHAR — пробел, использование которых в операциях сравнения не вызывает никаких проблем.

 

3. Модификация структуры таблицы
(добавление и удаление нового столбца, добавление ограничений, изменение типа данных, ограничений целостности)

Для модификации структуры и параметров существующей таблицы используется команда ALTER TABLE. Синтаксис команды ALTER TABLE для добавления столбцов в таблицу имеет вид

ALTER TABLE <ИМЯ ТАБЛИЦЫ> ADD (<ИМЯ СТОЛБЦА> <ТИП ДАННЫХ> <размер>);

По этой команде для существующих в таблице строк добавляется новый столбец, в который заносится NULL-значение. Этот столбец становится последним в таблице. Можно добавлять несколько столбцов, в этом случае их определения в команде ALTER TABLE разделяются запятой.

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

ALTER TABLE <ИМЯ ТАБЛИЦЫ> ALTER СOLUMN <ИМЯ СТОЛБЦА> <ТИП ДАННЫХ> <размер/точность>;

Следует иметь в виду, что модификация характеристик столбца может осуществляться не в любом случае, а с учетом следующих ограничений:

• изменение типа данных возможно только в том случае, если столбец пуст;

• для незаполненного столбца можно изменять размер/точность. Для заполненного столбца размер/точность можно увеличить, но нельзя понизить;

• ограничение NOT NULL может быть установлено, если ни одно значение в столбце не содержит NULL. Опцию NOT NULL всегда можно отменить;

• разрешается изменять значения, установленные по умолчанию.

Синтаксис команды ALTER TABLE для удаления столбцов в таблице имеет вид

ALTER TABLE <ИМЯ ТАБЛИЦЫ> DROP СOLUMN (<ИМЯ СТОЛБЦА>);

Лабораторная работа №3

INSERT INTO STUDENT

VALUES (111,'Петров','Александр', ‘Иванович’, ‘1994-05-05’, 23, 3,5);

Если в какое-либо поле необходимо вставить NULL-значение, то оно вводится как обычное значение:

INSERT INTO STUDENT

VALUES (101, 'Иванов', NULL, 200, 3, 'Москва','6/10/1979', 15);

В случаях, когда необходимо ввести значения полей в порядке, отличном от порядка столбцов, заданного командой CREATE TABLE, или требуется ввести значения не во все столбцы, следует использовать следующую форму команды INSERT:

INSERT INTO STUDENT (ID_STUDENT, SURNAME, FIRST_NAME,)

VALUES (111, 'Иванов', ‘Александр’);

Столбцам, наименования которых не указаны в приведенном в скобках списке, автоматически присваивается значение по умолчанию, если оно назначено при описании таблицы (команда CREATE TABLE), либо значение NULL.

С помощью команды INSERT можно извлечь значение из одной таблицы и разместить его в другой, например, запросом следующего вида:

INSERT INTO STUDENT1

SELECT *

FROM STUDENT

WHERE ID_Group =23;

При этом таблица STUDENT1 должна быть предварительно создана командой CREATE TABLE и иметь структуру, идентичную таблице STUDENT.

2. Удаление строк из таблицы осуществляется с помощью команды DELETE.

Следующее выражение удаляет все строки таблицы STUDENT.

DELETE FROM STUDENT;

В результате таблица становится пустой (после этого она может быть удалена командой DROP TABLE).

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

DELETE FROM STUDENT

WHERE STUDENT_ID = 111;

Можно удалить группу строк:

DELETE FROM STUDENTI

WHERE CITY = 'Москва';

3. Команда UPDATE позволяет изменять значения некоторых или всех полей в существующей строке или строках таблицы. Например, чтобы для всех предметов, сведения о которых находятся в таблице SUBJECT, изменить количество часов на значение 64, можно использовать конструкцию:

UPDATE SUBJECT

SET HOURS = 64;

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

UPDATE SUBJECT

SET HOURS = 64

WHERE ID_ SUBJECT=12;

В результате выполнения этого запроса будет изменено количество часов только у предмета с ID_ SUBJECT, равным 12.

Команда UPDATE позволяет изменять не только один, но и множество столбцов. Для указания конкретных столбцов, значения которых должны быть модифицированы, используется предложение SET.

Например, наименование предмета обучения 'Математика' (для него ID_ SUBJECT=12) должно быть заменено на название Дискретная математика', при этом идентификационный номер необходимо сохранить, но в соответствующие поля строки таблицы ввести новые данные об этом предмете обучения. Запрос будет выглядеть следующим образом:

UPDATE SUBJECT

SET SUBJ_NAME = 'Дискретная математика', HOURS = 36

WHERE SUBJ_ID = 12;

Задание

1. Ввести данные в таблицы с помощью команды Insert

2. Изменить некоторые данные с помощью команды Update

3. Удалить несколько строк с помощью команды Delete

Лабораторная работа №4

Проектирование запросов

Задание

Спроектировать запросы и представить формулировку запросов в виде предложений русского языка. Количество запросов – 10. Из общего количества запросов простыми могут быть не более 3, остальные запросы должны быть сложными. (Простые запросы – это запросы, с помощью которых информация выбирается из одной сущности. В сложных запросах информация должна выбираться из двух или более сущностей одновременно.)

Запросы должны быть спроектированы так, чтобы одновременно обладать физическим смыслом с точки зрения предметной области и продемонстрировать следующие возможности языка SQL:

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

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

в) применение кванторов (запросы на всеобщность, существование – ключевые слова EXISTS и ALL),

г) запросы с объединением результатов двух и более запросов (ключевое слово UNION),

д) запросы с применением группировки,

е) запросы с применением маски,

ж) запросы для работы с датами.

В запросах должны быть заданы условия поиска (Сравнение, Диапазон, Принадлежность множеству, Соответствие шаблону, Значение NULL)

 

Отчет по лабораторной работе должен содержать список запросов в виде:

Простые запросы:

1. Найти группу с минимальным/максимальным количеством студентов.

2. Вывести фамилии студентов, дата рождения которых попадает в диапазон от 12.05.1994г до 28.12.1996

Сложные запросы:

1. По фамилии студента определить фамилию его куратора

2. По фамилии преподавателя определить название кафедры, на которой он работает.

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

 

Лабораторная работа №5

Создание запросов

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

Теоретический материал по созданию запросов приведен в [1].

Задание

Лабораторная работа №6

 

Лабораторная работа №6

Задание

1. Спроектировать необновляемое, обновляемое и агрегирующее представление.

Задание

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

Проектирование триггеров

Задание.

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

Лабораторная работа №7

Создание представлений

Представления

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

Создания и изменения представлений представлены следующей командой:

<определение_просмотра>::= { CREATE| ALTER} VIEW имя_просмотра [(имя_столбца [,...n])] [WITH ENCRYPTION] AS SELECT_оператор [WITH CHECK OPTION]

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

Параметр WITH ENCRYPTION предписывает серверу шифровать SQL-код запроса, что гарантирует невозможность его несанкционированного просмотра и использования. Если при определении представления необходимо скрыть имена исходных таблиц и столбцов, а также алгоритм объединения данных, необходимо применить этот аргумент.

Параметр WITH CHECK OPTION предписывает серверу исполнять проверку изменений, производимых через представление, на соответствие критериям, определенным в операторе SELECT. Это означает, что не допускается выполнение изменений, которые приведут к исчезновению строки из представления. Такое случается, если для представления установлен горизонтальный фильтр и изменение данных приводит к несоответствию строки установленным фильтрам. Использование аргумента WITH CHECK OPTION гарантирует, что сделанные изменения будут отображены в представлении. Если пользователь пытается выполнить изменения, приводящие к исключению строки из представления, при заданном аргументе WITH CHECK OPTION сервер выдаст сообщение об ошибке и все изменения будут отклонены.

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

CREATE VIEW LCUSTT

AS SELECT *

FROM UNIVERSITY

WHERE CITY = 'Москва';

Это обновляемое представление.

Если просмотр удовлетворяет этим условиям, к нему могут применяться операторы INSERT, UPDATE, DELETE. Различия между модифицируемыми представлениями и представлениями, предназначенными только для чтения, не случайны. Цели, для которых их используют, различны. С модифицируемыми представлениями в основном обходятся точно так же, как и с базовыми таблицами. Фактически, пользователи не могут даже осознать, является ли объект, который они запрашивают, базовой таблицей или представлением, т.е. прежде всего это средство защиты для сокрытия конфиденциальных или не относящихся к потребностям данного пользователя частей таблицы. Представления в режиме <только для чтения> позволяют получать и форматировать данные более рационально. Они создают целый арсенал сложных запросов, которые можно выполнить и повторить снова, сохраняя полученную информацию. Результаты этих запросов могут затем использоваться в других запросах, что позволит избежать сложных предикатов и снизить вероятность ошибочных действий.

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

CREATE VIEW view4(Код, Название, Тип, Цена, Налог) AS SELECT КодТовара, Название,

Тип, Цена, Цена*0.05 FROM Товар

FROM STUDENT

WHERE CITY = 'Москва';

Видно, что в данное представление не включено поле CITY таблицы STUDENT.

Что будет происходить при попытках вставки строки в это представление? Так как мы не можем указать значение CITY в представлении как значение по умолчанию (ввиду отсутствия, в нем этого поля), то этим значением будет NULL, и оно будет введено в поле CITY базовой таблицы STUDENT '(считаем, что для этого поля опция NOT NULL не используется). Так как в этом случае значение поля CITY базовой таблицы STUDENT не будет равняться значению 'Москва', вставляемая строка будет исключена из самого представления и поэтому не будет видна пользователю. Причем так будет происходить для любой вставляемой в представление MOSC_STUD строки. Другими словами, пользователь вообще не сможет видеть строки, вводимые им в это представление. Данная проблема не решается и в случае, если в определение представления будет добавлена опция WITH CHECK OPTION:

CREATE VIEW MOSC_STUD AS

SELECT STUDENT_ID,SURNAME, STIPEND

FROM STUDENT

WHERE CITY = 'MoCKBa'

WITH CHECK OPTION;

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

CREATE VIEW MOSC_STUD AS

SELECT *

FROM STUDENT

WHERE CITY = 'Москва'

WITH CHECK OPTION;

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

SELECT STUDENT_ID, SURNAME,.STIPEND

FROM MOSC_STUD;

CREATE VIEW TOTALDAY AS

SELECT EXAM_DATE, COUNT(DISTINCT SUBJ_ID) AS SUBJ_CNT,

COUNT(STUDENT_ID) AS STUD_CNT,

COUNT(MARK; AS MARK_CNT,

AVG(MARK) AS MARK_AVG, SUM(MARK) AS MARK_SUM

FROM EXAM_MARKS

GROUP BY EXAM_DATE;

Теперь требуемую информацию можно увидеть с помощью простого запроса к представлению:

SELECT * FROM TOTALDAY;

Представления, основанные на нескольких таблицах

Представления часто используются для объединения нескольких таблиц (базовых и/или других представлений) в одну большую виртуальную таблицу. Такое решение имеет ряд преимуществ:

• представление, объединяющее несколько таблиц, может использоваться при формировании сложных отчетов как промежуточный макет, скрывающий детали объединения большого количества исходных таблиц;

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

• позволяет использовать при формировании отчетов более надежный модульный подход;

• предварительно объединенные и проверенные представления уменьшают вероятность ошибок, связанных с неполным выполнением условий объединения.

Можно, например, создать представление, которое показывает имена и названия сданных предметов для каждого студента:

CREATE VIEW STUD_SUBJ AS

SELECT A.STUDENT_ID, C.SUBJ_ID, A.SURNAME, C.SUBJ_NAME

FROM STUDENT A, EXAM_MARKS B, SUBJECT С

WHERE A.STUDENT_ID = B.STUDENT_ID

AND B.SUBJ_ID = C.SUBJ_ID;

Теперь все названия предметов, сданных студентом, или фамилии студентов, сдававших какой-либо предмет, можно выбрать с помощью простого запроса. Например, чтобы увидеть все предметы, сданные студентом Ивановым, подается запрос:

SELECT SUBJ_NAME

FROM STUD_SUBJ

WHERE SURNAME = 'Иванов';

Задание.

1. Реализовать представления, спроектированные в лабораторной работе № 6.

2. Привести примеры команд, подтверждающих, что данное представление является необновляемым / обновляемым.

 

Лабораторная работа 1

Анализ предметной области и построение концептуальной модели базы данных.

Цель лабораторной работы: Изучение приемов и приобретение практических навыков анализа предметной области (ПрОбл) и концептуального проектирования БД в процессе разработки информационной системы (ИС).

Содержание лабораторной работы. В ходе выполнения лабораторной работы студенты должны выполнить следующие задачи:

- определение целей разработки информационной системы в целом;

- исследование структуры предметной области (предприятия, организации, приложения и пр.) информационной системы;

- определение категорий пользователей информационной системы и распределение информационных задач между соответствующими приложениями (подсистемами);

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

- построение концептуальной модели БД.

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

- цель разработки информационной системы в целом;

- текстовое описание структуры предметной области информационной системы, включая структуру предприятия (организации, приложения), свойства различных объектов, взаимосвязи, ограничения и пр.;

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

Пример оформления таблицы, описывающей категории пользователей информационной системы:

№ п/п	Категория пользователей	Наименование приложения (подсистемы)	Информационные задачи

- табличное описание бизнес-процессов, относящихся к конкретному приложению информационной системы;

Пример оформления таблицы, описывающей бизнес-процессы информационной системы:

№ п/п	Бизнес-процесс	Исполнитель	Входные данные	Выходные данные	Ограничения
Поставщик	Содержание	Потребитель	Содержание
	Добавление преподавателя	Методист	Деканат	Предложение кафедры	Кафедра	Список преподавателей	- Каждому преподавателю должен быть назначен уникальный табельный номер - Необходимо исключить дублирование сведений о преподавателе
	Удаление/изменение данных о преподавателе	Методист	Деканат	Предложение кафедры	Кафедра	Список преподавателей	-
	...	...	...	...	...	...	...

- табличное описание типов сущностей предметной области;

Пример оформления таблицы, описывающей типы сущностей предметной области:

№	Наименование	Описание	Категория	Количество экземпляров	Режим обновления
	Преподаватель	Списочный состав преподавателей	Стержневая		5% в год
	...	...	...	...	...

- табличное описание типов связей между типами сущностей предметной области;

Пример оформления таблицы, описывающей типы связей между типами сущностей предметной области:

№	Тип сущности 1	Тип сущности 2	Описание	Мощность	Обязательность	Ограничения
	Преподаватель	Курс лекций	Преподаватель Читает Курс лекций/ Курс лекций Читается Преподавателем	1: М	Необязательная (Курс лекций может быть не закреплен за конкретным преподавателем)	За одним Преподавателем может быть закреплено не более трех курсов лекций в одном семестре
	...	...	...		...	...

- табличное описание атрибутов для каждого типа сущности;

Пример оформления таблицы, описывающей атрибуты типов сущностей предметной области:

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

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