Алматинский институт энергетики и связи

Кафедра инженерной кибернетики

 

 

 

 

 

 

 

 

 

 

 

 

ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ

 

Методические указания к выполнению лабораторных работ

для студентов всех специальностей

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Алматы 2004

 

 

СОСТАВИТЕЛИ: Ибраева Л.К., Сатимова Е.Г. Проектирование баз данных. Методические указания к выполнению лабораторных работ (для студентов всех специальностей). – Алматы: АИЭС, 2004 – 45 с.

 

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

             В методических указаниях рассматривается разработка баз данных в среде MS SQL Server: от создания объектов базы данных, манипуляции данными до конструирования сложных запросов по поиску информации.

             Ориентированный на работу с таблицами SQL не имеет достаточных средств для создания сложных прикладных программ. Поэтому он часто используется вместе с языками программирования высокого уровня. Одна лабораторная работа посвящена созданию “клиентского” приложения в среде Delphi.

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

 

 

Рецензент: канд.техн.наук, доцент кафедры ИК АИЭС Ю.В.Шевяков

 

 

 

 

 

 

 

 

       Печатается по плану издания Алматинского института энергетики и

связи на 2004 г.

 

 

 

Ó     Алматинский институт энергетики  и  связи,  2004 г.

Содержание

 

Введение

3

1 Лабораторная работа № 1. Создание проекта и реализация структуры

   базы данных

 

4

 

1.1 Концептуальное проектирование

4

 

1.2 Концептуальная модель данных учебного процесса

6

 

1.3 Преобразование концептуальной модели в реляционную 

7

 

1.4 Задание на лабораторную работу

11

 

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

11

 

      1.4.2 Создание таблиц         

12

 

      1.4.3 Изменение структуры таблиц

14

 

       1.4.4 Удаление таблиц

14

 

1.5 Контрольные вопросы

14

2 Лабораторная работа № 2  манипуляция данными

15

 

2.1 Команды манипуляции данным

15

 

2.2 Задание на лабораторную работу

16

 

2.3. Контрольные вопросы

17

3 Лабораторная работа № 3 запросы к базе данных 

17

 

3.1  Извлечение информации из таблиц             

17

 

 3.2 Операции в условиях для отбора данных

20

 

3.3 Обобщение данных с помощью агрегатных функций

22

 

3.4 Задание на лабораторную работу

24

 

3.5 Контрольные вопросы

24

4 Лабораторная работа № 4 Создание сложных запросов

25

 

4.1 Объединение таблиц в запросах

25

 

4.2 Использование подзапросов

26

 

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

 

 

4.4 Задание на лабораторную работу

30

 

4.5 Контрольные вопросы

31

5 Лабораторная работа № 5. Хранимые процедуры. Триггеры

 

 

5.1 Использование процедур

 

 

5.2 Триггеры

 

 

5.3 Задание на лабораторную работу

 

 

5.4 Контрольные вопросы

 

6 Лабораторная работа № 6. Разработка клиентских приложений

 

 

 5.1 Доступ к данным из приложений DELPHI (создание альяса)

 

 

5.2 Создание приложения к базе данных «Education»

 

 

5.3 Задание на лабораторную работу

 

 

5.4 Контрольные вопросы

 

Задание на самостоятельную работу

 

Список литературы

 

 

            Введение                                                                                                  Современная волна информационных технологий управления основывается на использовании систем управления реляционными базами данных (СУРБД), которые являются развитием традиционных СУБД.                     Широко известными СУБД, используемыми в архитектуре клиент-сервер, являются Microsoft  SQL Server, Oracle, Informix, Sybase SQL Server и другие. Эти СУБД являются реляционными SQL-серверами баз данных. СУБД архитектуры клиент-сервер может включать собственную клиентскую программу. В то же время в качестве клиентов сервера баз данных могут выступать другие СУБД. Для взаимосвязи этих клиентов с сервером разработано специальное программное обеспечение.                                                      Microsoft  SQL Server ориентирован на создание и ведение БД на уровне предприятия. Основное назначение – работа с крупными корпоративными базами. 

Требование нужной информации из базы данных формулируется в виде запросов. Универсальным языком запросов к SQL Server является язык структурированных запросов SQL (Structured Query Language). Следует отметить, что язык SQL имеет множество диалектов, порожденных различными разработчиками. В последней версии SQL Server 7.0 используется диалект Transact SQL, который базируется на SQL 92 и очень близок к нему (SQL 92 - существующий на сегодня стандарт SQL для реляционных баз данных, установленный ANSI - Американским национальным институтом стандартов).         На сегодняшний день нет идеальной системы управления базами данных, имеющей как развитый интерфейс, так и оптимизированную структуру. Например, СУБД MS Access достаточно удобна в качестве клиентского приложения при работе с базами данных систем клиент-сервер, но использование ее в качестве основной системы управления базами данных представляется проблематичным. С другой стороны, СУБД SQL Server не имеет интерфейса с пользователем в обычном понимании. Однако, эта система является высокоэффективным хранилищем с развитыми средствами работы с данными и возможностями создавать приложения для работы с базой данных непрофессиональным пользователям.

 

         1 Лабораторная работа № 1 Создание проекта и реализация структуры

          базы данных

                                                                                                                     1.1 Концептуальное проектирование                                                           Методы доступа к данным развивались на протяжении последних нескольких десятилетий от громоздких, физически ориентированных методов начального периода обработки файлов к различным видам обработки баз данных. Одним из важных аспектов реляционной «революции» стала идея отделения логической структуры и манипуляции данными, как они понимаются конечным пользователем, от физического представления, требуемого компьютерным оборудованием. Эта идея была повсеместно принята и воплотилась предложением обобщенной структуры баз данных, названной трехуровневой архитектурой базы данных: три уровня абстракции, на которых можно рассматривать базу данных. Трехуровневая архитектура – это стандартная структура базы данных состоящая из концептуального, внешнего и внутреннего уровней.                             На концептуальном уровне выполняется концептуальное проектирование базы данных. Концептуальное проектирование базы данных включает анализ информационных потребностей пользователей и определение нужных им элементов данных. Результатом концептуального проектирования является концептуальная схема - единое логическое описание всех элементов данных и отношений между ними.                              Внешний уровень составляют пользовательские представления данных базы данных, с точки зрения которых различные группы пользователей в организации понимают устройство базы данных, обращаются с ней и обновляют данные.                                                                            Внутренний уровень определяет физический вид на базы данных, посредством которого СУБД контролирует и обновляет базу данных (физические адреса, индексы, указатели и т.д.). За этот уровень отвечают проектировщики физической базы данных. Ни один пользователь (как пользователь) не касается этого уровня.                                                                           Этап концептуального проектирования включает создание концептуальной схемы базы данных. Спецификации разрабатываются в той степени, которая требуется для перехода к реализации. На этом этапе создаются подробные модели пользовательских представлений данных; затем они интегрируются в концептуальную модель, фиксирующую все элементы данных, которые будет содержать база данных.                                         Главными элементами концептуальной модели данных являются объекты и отношения.                                                                                               Объекты представляют вещи, которые пользователи считают важными в моделируемой части реальности. Объектное множество – множество вещей одного типа.                                                                                                       Отношение – связь между элементами двух объектных множеств.            Составное объектное множество – отношение, рассматриваемое как объектное множество.                                                                                                  Мощность отношения обозначает максимальное количество элементов одного объектного множества, связанных с одним элементом другого объектного множества.                                                                            Концептуальные модели данных значительно проще понимать, чем реляционные модели, поскольку они лучше соответствуют естественному взгляду на вещи.                                                                                                                                                                                                                                          1.2 Концептуальная модель данных учебного процесса                                 Пусть необходимо построить базу данных, содержащую информацию об учебном процессе текущего семестра: списки студентов групп; перечень изучаемых предметов; преподавательский состав кафедр, обеспечивающих учебный процесс; сведения о лекционных, практических и других видах занятий в каждой группе; результаты сдачи экзаменов (зачетов) по каждому из проведенных занятий. Назовем базу данных «Учебный процесс».                     Поскольку вещи одного типа хранятся в отдельных объектных множествах, можем выделить следующие объектные множества:         ГРУППЫ, СТУДЕНТЫ,  КАФЕДРЫ, ПРЕПОДАВАТЕЛИ, ПРЕДМЕТЫ,   ВИДЫ_ЗАНЯТИЙ.                                                                                                            Определим некоторые атрибуты для этих объектных множеств.                     ГРУППЫ     -  Название_группы, Количество_студентов, Курс.       СТУДЕНТЫ - Фамилия_студента, Имя_студента, Отчество_студента,                                   Дата_рождения_студента, Адрес_студента.                               КАФЕДРЫ -   Название_кафедры, Телефон_кафедры, ФИО_Заведующего.      ПРЕПОДАВАТЕЛИ - Фамилия_преподавателя,    Имя_преподавателя,                                                 Отчество_преподавателя, Должность_преподавателя,                                      Ученая_степень_преподавателя.                             ПРЕДМЕТЫ – Название_предмета, Всего_часов, Часов_лекционных_занятий,                           Часов_практических, Часов_лабораторных.        ВИДЫ_ЗАНЯТИЙ – Вид_занятия.

Может быть сформулировано множество вопросов к базе данных, например: к какой группе относится студент, на каких кафедрах работают преподаватели, какие предметы, в каких группах они ведут, какую оценку получили студенты по определенным видам занятий по определенному предмету и т.д. Чтобы ответить на широкий круг возможных вопросов к базе данных, следует рассмотреть отношения между объектными множествами.                    Между объектами ГРУППЫ и СТУДЕНТЫ существует одно-многозначное отношение, поскольку одна группа включает много студентов, а один студент входит только в одну группу.                                                          Аналогично устанавливается связь между объектами КАФЕДРЫ и ПРЕПОДАВАТЕЛИ, которые также находятся в одно-многозначных отношениях (на одной кафедре работает много преподавателей, но каждый преподаватель работает на определенной кафедре).                                                 По каждому предмету проводится множество видов занятий в различных группах разными преподавателями. Это определяет много-многозначные отношения между множествами ГРУППЫ и ПРЕДМЕТЫ, ГРУППЫ и ПРЕПОДАВАТЕЛИ, ПРЕДМЕТЫ и ПРЕПОДАВАТЕЛИ, ПРЕДМЕТЫ и ВИДЫ_ЗАНЯТИЙ                                                                                              Отношения между группами, предметами и преподавателями с их мощностями представлены на следующей схеме (рисунок 1). Атрибуты объектов на схеме не указаны.                                                                                 Эти отношения представляют собой составное объектное множество, назовем его ИЗУЧЕНИЕ. Это множество имеет собственный атрибут - Часов_занятий (значение этого атрибута равно одному из значений атрибутов: Часов_лекционных, Часов_практических, Часов_лабораторных объектного множества ПРЕДМЕТЫ).                                                                           База данных должна обеспечить хранение информации об итоговых оценках студента за семестр по каждому виду занятия, отображенному в объекте ИЗУЧЕНИЕ. Отношения между объектами СТУДЕНТЫ и  ИЗУЧЕНИЕ являются много-многозначными, так как одному студенту соответствует много видов занятий, отображаемых объектом ИЗУЧЕНИЕ, а один вид занятия проводится со многими студентами.        

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


В качестве объекта-связки используем объект УСПЕВАЕМОСТЬ, который будет содержать данные об успеваемости конкретного студента по конкретному виду занятия. Поэтому он является связанным с объектом СТУДЕНТЫ и с объектом ИЗУЧЕНИЕ. Один студент имеет данные по нескольким предметам, но эти данные относятся всегда к одному конкретному студенту. Это означает, что объект УСПЕВАЕМОСТЬ является подчиненным и находится в одно-многозначных отношениях с объектом СТУДЕНТЫ. Объект УСПЕВАЕМОСТЬ является также подчиненным и находится в одно-многозначных отношениях с объектом ИЗУЧЕНИЕ, так как по одному виду занятия имеется множество данных об успеваемости разных студентов, но каждое из них всегда определено по конкретному занятию. У этого составного объектного множества есть собственные атрибуты – Вид_контроля, Оценка и Дата_сдачи.                                          Окончательная логическая схема базы данных представлена на рисунке 2. В схему добавлены одно-многозначные отношения между объектами ГРУППЫ и СТУДЕНТЫ, а также между КАФЕДРЫ и ПРЕПОДАВАТЕЛИ. Атрибуты объектов-справочников на схеме не приведены.    

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


                                                                                             

 

 

 

 

 

 

 

 

 

 

 

Рисунок 2 - Концептуальная модель данных учебного процесса

 
 

 

 

 


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

  Преобразование объектных множеств и атрибутов. Объектное множество с атрибутами может быть преобразовано в реляционную таблицу с именем объектного множества в качестве имени таблицы и атрибутами объектного множества в качестве атрибутов таблицы. Если некоторый набор этих атрибутов может быть использован в качестве ключа таблицы, то он выбирается ключом таблицы. В противном случае в таблицу добавляется атрибут, значения которого будут однозначно определять объекты-элементы исходного объектного множества, и который, таким образом, может служить ключом таблицы.                                                                                         Преобразуем объектные множества ГРУППЫ, КАФЕДРЫ, ПРЕДМЕТЫ в реляционные таблицы с соответствующими названиями. Потенциальными ключами этих таблиц могут быть выбраны атрибуты Название (группы, кафедры, предмета), при условии, что названия не будут повторяться. Но мы для однозначной идентификации каждого элемента таблицы, введем в эти таблицы атрибут Номер (группы, кафедры, предмета) и будем его использовать в качестве первичного ключа. Этим ключом будет проще пользоваться при повседневной работе по вводу данных.                                 Таким образом, имеем реляционные таблицы (ключевые поля выделены курсивом):                                                                                ГРУППЫ (Номер_группы, Название_группы, Количество_студентов, Курс);           КАФЕДРЫ (Номер_кафедры, Название_кафедры, Телефон_кафедры,                                    ФИО_Заведующего);                                                                           ПРЕДМЕТЫ (Номер_предмета, Название_предмета, Всего_часов,                                                         Часов_лекций, Часов_практики, Часов_лабораторных)     .                  Объект ВИДЫ_ЗАНЯТИЙ преобразуется в таблицу с единственным атрибутом: ВИДЫ_ЗАНЯТИЙ (Вид_занятия).                                                                           В объекте СТУДЕНТЫ для идентификации студента может быть выбрана фамилия. Однако, учитывая возможность повторений, лучше в качестве идентификатора студента рассматривать его порядковый номер (идентификаторы типа «№_зачетной_книжки», «№_удостоверения», «№_РНН» не рекомендуется применять, ввиду их неудобства при вводе данных и возможных изменений, например, при утере):                           СТУДЕНТЫ (Номер_студента, Фамилия_студента, Имя_студента,                                      Отчество_студента, Дата_рождения_студента, Адрес_студента).             Аналогично определяется структура таблицы ПРЕПОДАВАТЕЛИ:      ПРЕПОДАВАТЕЛИ (Номер_преподавателя, Фамилия_преподавателя,                                              Имя_преподавателя, Отчество_преподавателя,                                             Должность_преподавателя,         Ученая_степень).                  

Преобразование отношений. В любом отношении «один-ко-многим» в таблицу, описывающую объект, мощность со стороны которого равна «многим», включается столбец, являющийся внешним ключом, указывающим на другой объект.                                                                              Связи между объектами ГРУППЫ и СТУДЕНТЫ характеризуются одно-многозначными отношениями, поскольку одна группа включает много студентов, а один студент входит только в одну группу. Тогда по правилам преобразований отношений, связь между ними осуществляется по номеру группы. Этот атрибут будет внешним ключом таблицы СТУДЕНТЫ. Кроме того, добавим в таблицу атрибут Студент_староста; в этом поле будем указывать номер студента, который является старостой группы, то есть значения ключа этой же таблицы. Этот атрибут связывает объект СТУДЕНТЫ с самим собой – так называемое рекурсивное отношение. Атрибут Студент_староста – это рекурсивный внешний ключ.   Итак, структура таблицы  СТУДЕНТЫ примет вид:         

СТУДЕНТЫ (Номер_студента, Фамилия_студента, Имя_студента,                                   Отчество_студента, Дата_рождения_студента, Адрес_студента,                            Номер_группы, Студент_староста).

Заметим, что в этой таблице можно было выбрать составной ключ из номера студента и номера группы. Такой идентификатор позволял бы явно определять принадлежность студента к группе. Но в этом случае для определения старост групп потребовалась бы отдельная таблица. Мы решили использовать простой ключ.                                                                                       Объекты КАФЕДРЫ и ПРЕПОДАВАТЕЛИ также находятся в одно-многозначных отношениях. Связь между ними осуществляется по уникальному ключу главного объекта КАФЕДРЫ  - номеру кафедры, который включается в подчиненный объект:                                   ПРЕПОДАВАТЕЛИ (Номер_преподавателя, Фамилия_преподавателя,                                  Имя_преподавателя, Отчество_преподавателя,                                                     Должность_преподавателя, Ученая_степень, Номер_кафедры).                   Объект  ИЗУЧЕНИЕ определяет много-многозначные отношения (рисунок 1). Для преобразования отношений «много-ко-многим» создается таблица пересечений, в которой используются только ключевые столбцы исходных таблиц. Таблица пересечений может иметь дополнительные неключевые атрибуты, присущие только ей. Определим атрибуты таблицы ИЗУЧЕНИЕ:                                                                                             ИЗУЧЕНИЕ (Номер_группы, Номер_предмета, Номер_преподавателя,                                    Вид_занятий, Часов_занятий).                                           Примечание. В дальнейшем при реализации проекта мы не будем создавать таблицу ВИДЫ_ЗАНЯТИЙ, так как единственный атрибут этой таблицы входит в состав ключевых атрибутов таблицы ИЗУЧЕНИЕ.                          Объект ИЗУЧЕНИЕ играет роль объекта-связки    в рассматриваемых      много-многозначных отношениях объектов.                                                                   В каждой группе проводится ряд видов занятий в соответствии с изучаемыми предметами. С другой стороны, каждый вид занятия определен для конкретной группы. Поэтому имеет место связь типа «один-ко-многим» между объектами ГРУППЫ и ИЗУЧЕНИЕ.                                                                    По каждому предмету проводится множество занятий в различных группах разными преподавателями. С другой стороны, каждое занятие проводится по конкретному предмету, что определяет отношения типа «один-ко-многим» между объектами ПРЕДМЕТЫ и ИЗУЧЕНИЕ. Аналогично определяются отношения типа «один-ко-многим» между объектами ПРЕПОДАВАТЕЛИ и ИЗУЧЕНИЕ.                                                                  Объект УСПЕВАЕМОСТЬ обеспечивает хранение информации об итоговых оценках студента за семестр по каждому виду занятия, отображенному в объекте ИЗУЧЕНИЕ.  Такая оценка определяется с одной стороны идентификатором студента (Номер_студента), а с другой стороны – идентификатором занятия (Номер_группы + Номер_преподавателя + Номер_предмета + Вид_занятия). Их объединение образует уникальный идентификатор объекта УСПЕВАЕМОСТЬ. Этот объект  содержит данные об успеваемости конкретного студента по конкретному виду занятий. По разным видам занятий в различное время могут проводиться различные виды контроля (экзамен, зачет, дифференцированный зачет, допуск). Атрибуты Вид_контроля, Дата_сдачи и Оценка являются неключевыми атрибутами этой таблицы:                                                                                 УСПЕВАЕМОСТЬ         (Номер_студента, Номер_группы, Номер_предмета,                Номер_преподавателя, Вид_занятий, Вид_контроля, Дата_сдачи, Оценка).                                                                                                                                           Итак, окончательно структура реляционных таблиц проектируемой базы данных имеет вид (первичные ключи выделены курсивом):                  

ГРУППЫ (Номер_группы, Название_группы, Количество_студентов, Курс);                КАФЕДРЫ (Номер_кафедры, Название_кафедры, Телефон_кафедры,                            ФИО_Заведующего);                                                                           ПРЕДМЕТЫ (Номер_предмета, Название_предмета, Всего_часов,                                                         Часов_лекций, Часов_практики, Часов_лабораторных);                               СТУДЕНТЫ (Номер_студента, Фамилия_студента, Имя_студента,                                  Отчество_студента, Дата_рождения_студента, Адрес_студента,                            Номер_группы, Студент_староста).

ПРЕПОДАВАТЕЛИ (Номер_преподавателя, Фамилия_преподавателя,                                         Имя_преподавателя, Отчество_преподавателя,                                                       Должность_преподавателя,  Ученая_степень, Номер_кафедры); 

ИЗУЧЕНИЕ   (Номер_группы, Номер_предмета, Номер_преподавателя,                          Вид_занятий, Часов_занятий).                                      УСПЕВАЕМОСТЬ (Номер_студента, Номер_группы, Номер_предмета , Номер_преподавателя, Вид_занятий, Вид_контроля, Дата_сдачи, Оценка).                                                                                                                                       1.4 Задание на лабораторную работу                                                     Реализуйте спроектированную модель данных учебного процесса в среде СУБД MS SQL Server. В лабораторной работе  для объектов рассматриваемой базы данных применяются названия на английском языке. Это связано с требованиями используемого программного обеспечения. Поэтому в приведенных ниже примерах база данных называется “Education”.         Для создания любого объекта SQL Server существует несколько способов, базирующихся на выполнении определенной команды.                          Для ввода и тестирования процедур и операторов Transact-SQL используется SQL Server Query Analyser (анализатор запросов SQL) – средство, входящее в комплект поставки SQL Server 7.  Окно этого средства можно вызвать из меню Tools основного окна программы.

Размещение пользовательских баз может меняться в зависимости от версии SQL и размещения  Program Files. Выяснить место расположения пользовательских баз можно из основного окна программы, выбрав из контекстного меню Свойства (Properties) любой базы в списке Databases. Также в окне свойств на вкладке TransactionLog просмотрите место расположения журнала транзакций. Команды резервного копирования и восстановления базы данных тоже выбираются в контекстном меню: строка Все задачи (All Tasks), команды  Backup Databases и Restore Databases.

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

Физически база данных располагается в одном или нескольких файлах операционной системы. В одном файле операционной системы не может содержаться несколько баз данных. В этом файле хранятся такие объекты как таблицы и индексы.                                                                                            Журнал транзакций – это рабочие области, которые SQL Server применяет для записи информации до и после выполнения транзакции. Эта информация может использоваться для отмены выполненной транзакции или для восстановления базы данных, если возникнет такая необходимость. В версии 7.0 журналы транзакций хранятся в отдельном файле, а не вместе с таблицами, как было в предыдущих версиях.                                                             Для создания базы данных с помощью Transact-SQL используется команда CREATE DATABASE.

CREATE DATABASE education

          ON PRIMARY

(NAME = education_data, FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\education_data.mdf',  size = 4, maxsize =25, filegrowth = 1 mb)

         LOG ON

(NAME = education_log, FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\education_log.ldf', size = 4, maxsize = 20,

            filegrowth =1 mb)                                                                                  

Внимание: Размещение базы и журнала транзакций -  'С:\…’ - может меняться в зависимости от версии SQL и размещения  Program Files.

Здесь:

education    - Имя создаваемой базы данных.                                                          ON         - Определяет список файлов на диске, в которых будет храниться информация базы данных.                                                                         PRIMARY  - Определяет файл, содержащий логическое начало базы данных и системных таблиц. В  базе  данных  может  быть  только  один первичный (PRIMARY) файл. Если этот параметр пропущен, то первичным считается первый файл в списке.                                                                                                      LOG ON - Определяет список файлов на диске, в которых будет храниться журнал транзакций. Если этот параметр не определен, то размер журнала транзакций будет составлять 25% от общего размера файлов данных. education_data – Определяет логическое имя, которое SQL Server будет   использовать для обращения к файлу.                                                                  FILENAMEЗадает параметры файла операционной системы (имя файла,   который должен находиться на сервере, где установлен SQL Server,   первоначальный и максимальный размеры базы данных и приращение для увеличения размера базы данных).                                                                                                                                                                                                                          1.4.2 Создание таблиц                                                                                                        Определите текущую базу данных с помощью следующей команды:

USE education      

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

                                                                                                                          Таблицу можно создать с помощью оператора     CREATE TABLE языка Transact-SQL. Главную роль в создании таблицы играет определение типов данных для столбцов таблицы. Для столбца можно определить не только тип данных, но и еще одну дополнительную характеристику NULL  или NOT NULL. Если для столбца определен атрибут NULL, это позволяет опустить при вводе данных значения данного столбца. Если же, наоборот, для столбца определен атрибут NOT NULL, SQL Server не позволит оставить данный столбец пустым во  время вставки строки. Таким образом, атрибуты NULL  и NOT NULL представляют собой нечто вроде проверки допустимости данных (это не пробел и не нуль). Элемент NULL для столбца интерпретируется как неопределенный или отсутствующий, так как при вставке строки в столбец не было введено явное или неявное его значение.

 

Создание таблицы   Группы:

CREATE  TABLE Gruppa (  

            Grup_ID                 integer  identity (1,1) not null  PRIMARY KEY, 

Grup_NAME         char (9)   not null,

            Grup_KOLSTUD  smallint  not null,

            Grup_COURSE     int   not null                                                                                                                          )

        

Создание таблицы Студенты (здесь приведены варианты описания ключей):   

CREATE  TABLE Students (   

Stud_ID               bigint  not null,

Stud_FAM           char(20)  not null,

Stud_IMA            char(10)  not null,

Stud_OTCH         char(15)  not null,

Stud_DATE          datetime  not null,

Stud_ADDRESS  char(25)   null,

Grup_ID            integer   not null   FOREIGN KEY

   REFERENCES Gruppa (Grup_ID ),

            Stud_STAR       bigint  not null,                                                                              CONSTRAINT  PK_Students  PRIMARY KEY (Stud_ID),                                                CONSTRAINT  FK_Students_Students   FOREIGN KEY  (Stud_STAR)

                                                                        REFERENCES Students (Stud_ID)

                                   )

 

Создание таблицы  Кафедры:

CREATE  TABLE  Chair ( 

            Chair_ID           integer   not null  PRIMARY KEY,

Chair_NAME   char(20)  not null,

            Chair_PHONE  char(10)  null,

            Chair_CHIEF    char(15)  not null    )

 

Создание таблицы   Преподаватели:

CREATE  TABLE   Teacher (

            Teach_ID                bigint     not null PRIMARY KEY   ,

            Teach_FAM            char(20)  not null,

            Teach_IMA             char(10)  not null,

            Teach_OTCH          char(15)  not null,

            Teach_POSITION   char(18)  not null,

            Teach_STEPEN      char(12)  null ,

            Chair_ID         integer   not null FOREIGN KEY

          REFERENCES Chair (Chair_ID)                     

)

 

Создание таблицы  Предметы:

CREATE  TABLE  Subject ( 

            Subj_ID                integer   not null  PRIMARY KEY ,

            Subj_NAME        char(20)  not null,

         Total_Hours      integer  not null,

         Lection_Hours  integer  not null,

         Practice_Hours  integer  not null,

         Labor_Hours     integer  not null                                                                                                           )

                                                                                                                       Создание таблицы    Изучение: 

CREATE  TABLE Study (

 Grup_ID             integer  not null  FOREIGN KEY

  REFERENCES Gruppa (Grup_ID ),

 Subj_ID              integer  not null  FOREIGN KEY

  REFERENCES Subject (Subj_ID ),

           Teach_ID           bigint   not null   FOREIGN KEY                                                                                                                              REFERENCES Teacher (Teach_ID),

             Form_Study     char (20) not null,

          Lesson_Hours  integer  not null,

CONSTRAINT  PK_Study                                                                                                                               PRIMARY KEY (Grup_ID, Subj_ID, Teach_ID, Form_Study )

       )

 

Создание таблицы    Успеваемость:

CREATE  TABLE  Progress (

Stud_ID             bigint  not null  FOREIGN KEY                                                                                                                        REFERENCES  Students (Stud_ID),        

          Grup_ID            int  not null,   

         Subj_ID              integer  not null,

         Teach_ID          bigint     not null,

            Form_Study    char (20) not null,

            Form_Control char (20) not null,

            Pr_DATE           datetime   null,                                                  

            OCENKA            integer  CHECK ( OCENKA in  (0,1,2,3,4,5,6,7,8,9)) DEFAULT(0),

CONSTRAINT  FK_Progress_Study 

                        FOREIGN KEY  (Grup_ID, Subj_ID, Teach_ID, Form_Study)                                        REFERENCES Study (Grup_ID, Subj_ID, Teach_ID, Form_Study),

CONSTRAINT  PK_Progress 

                        PRIMARY KEY (Stud_ID, Grup_ID, Subj_ID, Teach_ID, Form_Study)

                           )                                                      

 

         1.4.3 Изменение структуры таблиц

         Структуру таблиц можно изменять командой ALTER  TABLE.

Добавление  полей. Добавьте в таблицу  Students поле, где будет храниться информация о стипендии студентов:

             USE   education

             ALTER  TABLE Students

            ADD   Stud_STIP  smallmoney

         Удаление полей из таблицы. Удалите введенное поле из таблицы:

 ALTER  TABLE Students

                       DROP COLUMN Stud_STIP                                                                                            

            Добавление ограничений. Если в таблице не были определенны первичные или внешние ключи, это также можно исправить с помощью ALTER TABLE.                                                                                                         Предположим, что в таблице Students не был определен первичный ключ:                                                                                                                                                                               ALTER TABLE Students                                                                                                               ADD CONSTRAINT PK_Students PRIMARY KEY (Stud_ID)                                     Если в таблице Study не был описан один из внешних ключей:                                            ALTER TABLE Study                                                                                                                      ADD CONSTRAINT PK_Study_Gruppa FOREIGN KEY (Grup_ID)                                                                                              REFERENCES Gruppa (Grup_ID)                                                                                                                                                                                1.4.4 Удаление таблиц

         Для удаления таблиц предназначена команда DROP.

Например,                                                                                                                  DROP Students                                                                                          Будьте осторожны с командой DROP!                                                                                                                                                                           1.5 Контрольные вопросы                                                                                1.5.1 Что такое концептуальная модель данных?                                             1.5.2 Что понимается под  реляционной схемой базы данных?                      1.5.3 Объясните смысл терминов:  первичный ключ; внешний ключ; составной ключ;  реляционная таблица; нормализация.                                           1.5.4 Что означает аббревиатура SQL?                                                               1.5.5. Каковы главные отличия технологии клиент/сервер от технологии, использующей мэйнфрейм?                                                                           1.5.6 В рамках технологии клиент/сервер персональный компьютер является клиентом или сервером?                                                                                                                                                                                                       2 Лабораторная работа № 2  Манипуляция данными

 

 2.1 Команды манипуляции данными

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

    Для добавления информации к таблице используется инструкция INSERT. В самой простой форме используется следующий синтаксис:

             INSERT INTO имя_таблицы

                VALUES (значение,…, значение)

 Команды модификации не производят никакого вывода. Но Query Analyzer сообщит, что была добавлена 1 запись. Таблица уже должна существовать к моменту исполнения этой команды, а тип каждого значения в скобках после VALUES должен совпадать с типом данных столбца, в который оно вставляется. Первое значение попадает в столбец 1, второе – во столбец 2 и т.д.

 Например, чтобы добавить две записи в таблицу студентов можно использовать команды:                                                                                                                                                                                                                                          INSERT INTO Students                                                                                                             VALUES (030101, 'Ахметов', 'Данияр',  'Муратович', 02/04/85,'Алматы', 1, 30101) INSERT INTO Students                                                                                                      VALUES (030102,'Ким', 'Алла', 'Юрьевна',11/09/85б, 'Алматы',1, 030101)

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

                INSERT INTO Students                                                                                     VALUES (030103, 'Цой', 'Олег','Иванович',10/05/85, NULL,1,030101)

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

         DELETE [FROM] имя_таблицы

            WHERE имя_столбца = значение

         Ключевое слово FROM в инструкции необязательно. Инструкция DELETE без предложения WHERE применяется очень редко. Прежде чем выполнить операцию, убедитесь в правильности написания инструкции, так как можно непреднамеренно удалить все строки таблицы.

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

          DELETE  FROM  students                                                                              Чаще всего  не рекомендуется выполнять эту команду! Обычно Вам требуется удалять некоторые определенные строки в таблице. Чтобы определить какие строки будут удалены, используйте условие отбора. Например, чтобы удалить определенную запись о студенте, можно ввести:

                DELETE FROM students

                WHERE  Stud_ID = 30103

    Разумеется, если условию будет соответствовать несколько записей, все они будут удалены.

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

Изменение значения поля. Для изменения существующих значений в столбцах таблицы используется инструкция UPDATE. Упрощенный синтаксис этой инструкции следующий:

UPDATE имя_таблицы                                                                                                        SET имя_столбца_1 = значение, …, имя_ столбца_n = значение

WHERE имя_столбца  оператор_сравнения  значение

Команда UPDATE позволяет изменять некоторые или все значения в существующей записи в таблице. Эта команда содержит предложение UPDATE, за которым указывается имя таблицы, и предложение SET, указывающее на изменение, которое нужно сделать для определенного столбца. Например, чтобы изменить должности всех преподавателей на  доцента можно ввести команду:                                                                                         UPDATE  Teacher SET                                                                                                                 Teach_POSITION = ‘доцент’                                                           Аналогично команде DELETE, команда UPDATE может использовать условия для выбора записей, подлежащих изменению. Вот так можно изменить размер стипендии студента  (код студента 30102):                                                             UPDATE Students                                                                                                SET Stud_STIP  = 2000                                                                                      WHERE Stud_ID   = 30102                                                                                   В предложении SET можно указывать несколько столбцов, разделяя их запятыми.                                                                                                                                                                                                                                            2.2 Задание на лабораторную работу                                                                  2.2.1 Заполните данными все таблицы вашей базы данных.      Придерживайтесь следующих правил:                                                                    а) Первыми заполняются таблицы, имеющие наименьшее количество связей (справочники).                                                                                                  б) Правило категорной целостности: никакой ключевой атрибут       строки не может быть пустым.                                                                            в) Правило целостности на уровне ссылок: значение каждого внешнего     ключа  должно быть либо пустым, либо равным одному из текущих          значений ключа другой таблицы.                                                                        2.2.2  Добавьте в таблицу Students поле Stud_STIP, в котором будет    храниться         информация о стипендии студентов.                                                     2.2.3 Заполните поле Stud_STIP таблицы Students различными данными (используйте команду UPDATE).                                                                            2.2.4  Выполните по своему усмотрению команды по изменению         данных в таблицах базы данных.                                                                                                                                                                                              2.3. Контрольные вопросы                                                                              2.3.1  Какие команды манипуляции данными вы знаете?                                2.3.2  Дайте определение целостности данных.                                                         2.3.3 Сформулируйте правило целостности на уровне ссылок.                             2.3.4 Что означает определение поля NOT NULL?                                        2.3.5 Если поле определено как NULL, значит ли это, что в это поле                 обязательно  должно быть что-нибудь введено?                                           2.3.6 Что означает определение поля identity?

  3 Лабораторная работа № 3 Запросы к базе данных                                                                                                                                                                          3.1 Извлечение информации из таблиц                                                         Запрос к базе данных представляет собой операцию выборки, которая сужает диапазон считываемой информации и ограничивает ее столбцами и строками, соответствующими заданным критериям.

  Для выборки данных в Transact-SQL используется инструкция SELECT.  Большинство реальных запросов SQL предназначено для выборки не всех, а определенных строк и столбцов таблиц. Как правило, требуется считывать больше одной строки и одного столбца, но меньше всех строк и всех столбцов всех таблиц базы данных. Для ограничения объема считываемой информации используются достаточно сложные инструкции SELECT. При получении нужной информации определить те типы и выполнить то количество операций, которые допустимы для таблиц баз данных можно как с помощью простых инструкций SELECT, так и очень сложных, требующих значительной доли ресурсов сервера.

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

В следующей инструкции SELECT предложение FROM указывает, что возвратить надо все данные, которые находятся в таблице  Students ”:

                SELECT *                                                                                                         FROM Students

Использование звездочки (*) в инструкции SELECT возвращает все столбцы. Это избавляет от необходимости специально указывать их в запросе.

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

         Столбцы со значениями, возвращаемыми из таблиц базы данных, указываются в виде списка сразу после ключевого слова SELECT и отделяются друг от друга запятыми:

                SELECT   Stud_ID, Stud_FAM, Stud_IMA, Stud_ADDRESS

                FROM  Students  

В этой команде:

·        SELECT - ключевое слово

·        Stud_ID, Stud_FAM, Stud_IMA, Stud_ADDRESS  - список столбцов из              таблицы, которые выбираются запросом. Любые другие столбцы, не                    указанные в этом списке игнорируются.

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

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

Столбцы таблицы можно отобразить в отличном от первоначального порядке:

                                                                                                                                                SELECT   Stud_ID, Stud_ADDRESS, Stud_FAM, Stud_IMA

                FROM  Students  

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

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

             SELECT  Stud_ID

                FROM   Progress

При выполнении этой команды, коды дублируются (некоторые студенты имеют по несколько оценок). Для получения списка без дубликатов надо ввести:

             SELECT DISTINCT Stud_ID

                 FROM   Progress

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

Если вместо DISTINCT указать ALL, это будет иметь противоположный эффект, дублирование строк сохранится. Поскольку это тот же самый случай, когда не указывается никакой аргумент, то ALL по существу лишь пояснительный аргумент.

Ограничение строк таблицы. Таблицы имеют тенденцию становиться очень большими. Поскольку обычно только некоторые их них интересуют Вас в данное время, имеется возможность устанавливать критерии, чтобы определить какие строки будут выбраны. Для этого используется ключевое слово WHERE - предложение команды SELECT, позволяющее задавать условие, которое может быть верным или неверным для любой строки таблицы. Команда извлекает только те строки из таблицы, для которых это условие верно. Синтаксис предложения WHERE:

 

WHERE имя_столбца   оператор_сравнения   значение

 

Инструкция SELECT с предложением WHERE сужает результирующий набор запроса. Она может возвратить всего одну строку или даже вообще не возвратить ничего, если строки, совпадающие с указанным в инструкции критерием, не найдены.  Предположим, необходимо получить имена и  фамилии всех студентов с  отчеством  “Николаевич”.

                          SELECT   Stud_FAM, Stud_IMA

                                FROM  Students

                                WHERE  Stud_OTCH = ' Николаевич '

  Когда в запросе имеется предложение WHERE,  SQL Server просматривает всю таблицу построчно и исследует каждую строку, чтобы определить верно ли утверждение.

Примечание: Строковые константы типа 'Москва' в Transact-SQL ограничиваются либо апострофами ', либо кавычками ".

             Теперь попробуем построить запрос с числовым полем в предложении WHERE. Выберем всех студентов со стипендией  1200:

                          SELECT *

                                FROM Students

                                WHERE  Stud_STIP = 1200

        

    Сортировка вывода. Обычно  требуется, чтобы данные как-то были упорядочены. Упорядочение задается с помощью ключевого слова ORDER BY (по умолчанию упорядочение по возрастанию). Синтаксис предложения ORDER BY:                                                                                               ORDER BY Столбец1 | Целое_значение  [ACS | DESC]                                                     Например, выведем список студентов в алфавитном порядке:                                      SELECT Stud_ID, Stud_FAM            

                FROM Students

                ORDER BY Stud_FAM                                                                                                     

            Если после имени столбца указать имя еще одного столбца, то по значениям второго столбца будут упорядочены строки, содержащие одинаковые значения в первом столбце.  Столбец, указанный в списке ключевого слова ORDER BY можно заменить числом, соответствующим порядку столбца в списке (параметр Целое_значение ):                                                                                           

                SELECT Grup_ID, Stud_ID, Stud_FAM          

                FROM Students

                ORDER BY 1, 3 DESC                                                                       

Параметры ASC (ascending) – по возрастанию,  DESC (descending) – по убыванию задают порядок сортировки.                                                                                                     

3.2 Операции в условиях для отбора данных

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

Операторы отношения:

·        =  Равно

·        >  Больше чем

·        <  Меньше чем

·        >= Больше или равно

·        <= Меньше или равно

·        <> Не равно

Они имеют стандартные значения для символьных значений и дат. Символьные значения сравниваются в терминах их кодов.

Предположим, что нам надо увидеть всех студентов со стипендией выше 1200:

                          SELECT *

                                FROM Students

                                WHERE Stud_STIP > 1200

    Логические операторы:

·        AND логическое "И"

·        OR    логическое "ИЛИ"

·        NOT логическое отрицание

Оператор AND сравнивает два логических значения и возвращает TRUE (истина), если оба значения истинны (т.е. равны TRUE), в остальных случаях - FALSE (ложь). Оператор OR возвращает TRUE, если хотя бы один из аргументов равен TRUE. Оператор NOT возвращает TRUE, если его аргумент равен FALSE и наоборот.

Использование логических операторов значительно увеличивает возможности команды SELECT.

Логический оператор AND (логическое И) имеет смысл, если возвращаемые в результате выполнения запроса строки должны удовлетворять обоим условиям сравнения, заданным в предложении WHERE:

SELECT *

FROM Students

WHERE Stud_IMA = 'Алексей'

                              AND

                                       Stud_ADDRESS = ‘Таугуль

         Если в таблице имеется две строки, в которых значение в столбце Stud_IMA равно 'Алексей' (то есть имеются тезки), то логический оператор AND позволяет выбрать ту строку, в которой значение столбца Stud_ADDRESS равно «Таугуль» (то есть того Алексея, который живет в Таугуле).

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

                          SELECT *

                                FROM Students

                                WHERE Stud_IMA = 'Алексей'    OR

                                                Stud_IMA = 'Николай'

Оператор NOT указывается перед столбцом в операциях сравнения:

                       SELECT *

FROM Students

WHERE NOT Stud_ADDRESS = ‘Таугуль

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

Для группировки выражений Transact-SQL позволяет использовать круглые скобки ( ).

Например:

                          SELECT *

                                FROM Students

                                WHERE NOT (Stud_IMA = 'Алексей'   OR

                                                           Stud_IMA = 'Николай')

 

Специальные операторы: IN, BETWEEN, LIKE, IS NULL.

         Оператор IN определяет список значений, в который должно входить значение поля. Например, если необходимо  найти всех  студентов с именем  'Алексей'  и 'Николай', можно использовать такой запрос:

                          SELECT *

                                FROM Students

                                WHERE Stud_IMA  IN ('Алексей' , 'Николай')

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

Оператор BETWEEN похож на оператор IN. В отличие от списка допустимых значений BETWEEN определяет диапазон значений. В запросе необходимо указать слово BETWEEN, затем начальное значение, ключевое слово AND и конечное значение. Первое значение должно быть меньше второго. Следующий запрос выберет студентов  с оценками между  3 и 5:

                          SELECT *

                                FROM   Progress

                                WHERE Ocenka BETWEEN 3 AND 5

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

·        символ подчеркивания _ - замещает любой одиночный символ. Например, 'к_т' будет соответствовать 'кот' и 'кит', но не 'крот'.

·        знак процента % - замещает последовательность любого числа символов.

Например, '%м%р' будет соответствовать 'компьютер' и 'омар'.

Давайте выберем студентов, чьи имена начинаются с буквы 'О':

                         

                          SELECT *

                                FROM Students

                                WHERE Stud_IMA  LIKE 'О%'

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

Часто необходимо различать строки, содержащие значения NULL в каком-либо столбце. Так как NULL указывает на отсутствие значения, для сравнненя с NULL существует специальный оператор - IS NULL. Выберем из нашей базы студентов с NULL значениями в столбце Stud_STIP:

                          SELECT *

                                FROM Students

                    WHERE Stud_STIP IS NULL

 

3.3 Обобщение данных с помощью агрегатных функций

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

·        COUNT - производит подсчет строк, удовлетворяющих условию запроса

·        SUM - вычисляет арифметическую сумму всех значений колонки

·        AVG - вычисляет среднее арифметическое всех значений

·        MAX - определяет наибольшее из всех выбранных значений

·        MIN - определяет наименьшее из всех выбранных значений

 

     Функции SUM и AVG применимы только к числовым полям. С функциями COUNT, MAX, MIN могут использоваться числовые или символьные поля. При применении к символьным полям MAX, MIN сравнивают значения в алфавитном порядке. Агрегатные функции при своей работе игнорируют значения NULL.

Чтобы найти среднюю стипендию  студентов, можно ввести запрос:

                          SELECT  AVG (Stud_STIP)

                                FROM Students

 

Функция COUNT несколько отличается от остальных. Она подсчитывает число значений в данной колонке или число строк в таблице. Например, подсчитаем количество студентов, сдавших учебные предметы

                          SELECT COUNT( DISTINCT Stud_ID)

                                FROM Progress

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

         Чтобы подсчитать общее число строк в таблице используйте функцию COUNT со звездочкой вместо имени поля:

                          SELECT COUNT (*)

                                FROM Students

Предложение GROUP BY позволяет задавать Вам подмножество значений, для которых применяется агрегатная функция. Это дает возможность объединять поля и агрегатные функции в одном предложении SELECT. Предположим, что надо найти номера студентов с минимальной оценкой из ведомости успеваемости:

                          SELECT  Stud_ID,  MIN(ocenka )

                                FROM Progress

                                GROUP BY Stud_ID

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

                                SELECT  Stud_ID,  MIN(ocenka) AS  MIN_OCENKA

                                FROM Progress

                                GROUP BY Stud_ID

GROUP BY применяет агрегатные функции к группам записей. Условие формирования группы - одинаковое значение поля (в данном случае Stud_ID). GROUP BY можно использовать с несколькими полями. Усложним предыдущий запрос:                                                                                                                                                   SELECT Stud_ID, Pr_Date, MAX(ocenka )

                FROM Progress

                GROUP BY Stud_ID, Pr_DATE 

То есть мы выбираем строки с номерами студентов  и максимальные оценки, полученные ими  на каждую дату. Дни, в которые не было оценок, не будут показаны.                                                                                                        Ключевое слово HAVING используется в операторе SELECT вместе с ключевым словом GROUP BY, чтобы указать какие из групп должны быть представлены в выводе. Для GROUP BY ключевое слово HAVING играет ту же роль, что и WHERE для.ORDER BY.                                                                                Предположим, что мы хотим получить максимальную оценку  каждого студента, которая больше 4. Для достижения такого эффекта применяется предложение HAVING, которое определяет критерий, используемый для удаления групп из результата запроса, как это делает предложение WHERE для отдельных строк:                                                                                                                       SELECT Stud_ID, Pr_DATE, MAX (ocenka )                                                                  FROM Progress                                                                                                   GROUP BY Stud_ID, Pr_DATE                                                                             HAVING MAX (ocenka ) > 4                                                             Агрегатные функции позволяют не просто выбирать определенные значения из базы, но и производить их обобщение и анализ.                                                                                                                                                              3.4 Задание на лабораторную работу                                                                  Перед выполнением задания рекомендуется проработать все примеры, приведенные в п.п. 3.1 – 3.3.   

Выполните поиск информации в отдельных таблицах:                                   3.4.1 Список преподавателей с указанием их должностей в алфавитном порядке.                                                                                                                 3.4.2 Названия кафедр с фамилиями заведующих.                                            3.4.3 Список студентов с различными фамилиями, обучающихся в первой группе (предполагается, что в этой группе есть однофамильцы). 3.4.4 Список студентов, у которых стипендия больше 2000.                         3.4.5 Список студентов, проживающих в Астане и Караганде.                       3.4.6 Список студентов второй группы, у которых нет стипендии.              3.4.7 Список студентов третьей группы, фамилии которых начинаются на букву «А».                                                                                                             3.4.8 Список студентов, которые родились в 1986 году.                                       3.4.9 Посчитайте суммарную стипендию студентов третьей группы.             3.4.10 Посчитайте среднее число лекционных часов по всем предметам.           3.4.11 Сколько студентов введено в базу данных?                                          3.4.12          Выведите всю информацию о предметах.                                   3.4.13 Список студентов, которые не проживают в Алматы.                          3.4.14 Список студентов, чьи дни рождения в мае.                                         3.4.15          Номера студентов с минимальной оценкой из ведомости     успеваемости.                                                                                                 3.4.16          Номера студентов с максимальной оценкой из ведомости успеваемости.                                                                                                                                                                                                                                     3.5 Контрольные вопросы                                                                               3.5.1         Назовите обязательные составляющие оператора SELECT.                    3.5.2 Для всех ли данных в выражении ключевого слова WHERE обязательно нужно использовать кавычки?                                                        3.5.3 К какому разделу SQL относится оператор SELECT?                                          3.5.4 Можно ли в выражении для ключевого слова WHERE задать несколько условий?                                                                                                  3.5.5 Допустимы ли кавычки для значений числовых полей?                        3.5.6 Играет ли роль тип данных при использовании функции COUNT.      3.5.7 Чтобы группировать данные по столбцу, должен ли этот столбец быть   указан в списке ключевого слова SELECT?                                                       3.5.8 При использовании ключевого слова ORDER BY в операторе SELECT обязательно ли использовать ключевое слово GROUP BY?                                                                                                                                                       4 Лабораторная работа № 4 Создание сложных запросов                                                                                                                                                          4.1 Объединение таблиц в запросах                                                   До сих пор все наши запросы обращались к одной таблице. Однако SQL позволяет в одном запросе обращаться к нескольким таблицам. Именно это свойство и сделало язык  SQL столь популярным. В таких запросах вместе с именами каждого из стобцов указываются имена соответствующих таблиц. Это называется подробным определением столбца в запросе. Подробные определения требуются только для тех столбцов, которые присутствуют в нескольких из указанных в запросе таблиц. Но в операторе обычно указываются подробные определения для всех столбцов:                          SELECT  Teacher.Teach_FAM, Chair.Chair_NAME                                      FROM Teacher, Chair                                                                             WHERE Teacher.Chair_ID = Chair.Chair_ID                                              

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

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

SELECT  Teacher.*,  Chair.Chair_NAME

FROM Teacher, Chair

WHERE Teacher.Chair_ID = Chair.Chair_ID

                                                                                                                                             Использование псевдонимов для имен таблиц. Псевдонимы назначаются таблицам с целью сокращения объема печатания, а также для использования при рекурсивном связывании таблиц. Пример:                                               SELECT  A.Teach_ID, B.Chair_NAME

FROM Teacher  A,

                          Chair     B

WHERE A.Chair_ID = B.Chair_ID                                                                                 

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

                        SELECT  A.Stud_FAM, B.Stud_FAM                                                                                         FROM Students A,                                                                                                                                   Students B                                                                                                WHERE A.Stud_Star = B.Stud_ID                                                                           

          Связывание по нескольким ключам. Предположим вы хотите увидеть,  кто из  студентов, по каким предметам получили оценки:                                                                                                                                                 SELECT Students. Stud_FAM, Subject.Subj_NAME, Progress.OCENKA                                 FROM Students, Subject, Progress                                                                        WHERE Students.Stud_ID = Progress.Stud_ID    AND                                                                          Subject.Subj_ID = Progress.Subj_ID

            SQL Server проверяет каждую комбинацию строк двух таблиц и проверяет их на условие, указанное в предложении WHERE. Если эта комбинация удовлетворяет ему, то она выводится.                                                         Предположим, что мы хотим найти всех студентов, сдавших хорошо преподавателю Серикову. Для этого требуется связать четыре таблицы:                                                                                                                                                                                                                                                                   SELECT Students.Stud_FAM, 'по предмету ', Subject.Subj_NAME,                                                                                         'получили оценку ', Progress.OCENKA,                                                                             'у преподавателя ', Teacher.Teach_FAM                      FROM Students, Subject, Progress, Teacher                                                                        WHERE Students.Stud_ID = Progress. Stud_ID          AND

                          Teacher.Teach_ID = Progress.Teach_ID AND

                           Subject.Subj_ID = Progress.Subj_ID      AND

                           Progress.Ocenka <>2                              AND

                               Teacher.Teach_FAM =  'Сериков '

 

         4.2 Использование подзапросов

          Запросы могут управлять другими запросами. Это делается путем помещения запроса внутрь условия другого запроса и использования вывода внутреннего запроса в верном или неверном условии.

         Обычно внутренний запрос генерирует значение, которое проверяется в условии внешнего запроса, определяющего верно оно или нет. Например, мы знаем фамилию студента - Сидоров, но не знаем его кода (Stud_ID), и хотим получить все его оценки из таблицы Progress:                                                       

SELECT *

FROM Progress

             WHERE  Stud_ID  = (

             SELECT  Stud_ID

             FROM Students

             WHERE Stud_FAM = 'Сидоров'  )                                                   

        Чтобы выполнить внешний (основной запрос), сначала выполняется внутренний запрос (подзапрос) внутри предложения WHERE. При выполнении подзапроса просматривается таблица Students, в которой выбираются строки, где поле Stud_FAM равно 'Сидоров', затем извлекается значение поля Stud_ID. Пусть единственной строкой будет Stud_ID = 30104. Далее полученное значение помещается в условие основного запроса, вместо самого подзапроса, так что условие примет вид:

                WHERE   Stud_ID  = 301004                                                                            

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

Если Вы хотите использовать подзапрос, который возвращает несколько строк, то необходимо использовать оператор IN. Например, если в базе несколько студентов с фамилией Сидоров и все имеют оценки:

SELECT *

FROM Progress

             WHERE  Stud_ID IN (

             SELECT  Stud_ID

             FROM    Students

                                      WHERE Stud_FAM = 'Сидоров'  )                                      Найдем все оценки для предмета Информатика:

SELECT *

FROM Progress

             WHERE    Subj_ID   IN (

             SELECT   Subj_ID

             FROM      Subject

             WHERE    Subj_NAME = 'Информатика'  )

Этот результат можно получить и с помощью объединения:

                            SELECT Progress.*                                                                                              FROM   Subject, Progress                                                                                             WHERE Subject.Subj_ID = Progress.Subj_ID AND                                                                      Subject.Subj_NAME = 'Информатика'                                                                                                                               Хотя этот запрос эквивалентен предыдущему, SQL Server должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и проверить их соответствие условию.

         Все вышеприведенные подзапросы объединяет то, что все они выбирают один единственный столбец. Это обязательно, поскольку их результат сравнивается с одиночным значением. Команды типа SELECT * запрещены в подзапросах.

         Подзапросы можно также использовать в предложении HAVING. Эти подзапросы могут использовать собственные предложения GROUP BY или HAVING. Следующий запрос является тому примером:

SELECT Ocenka,  COUNT (DISTINCT Stud_ID  )

FROM Progress

GROUP BY Ocenka

             HAVING Ocenka > (

                          SELECT AVG(ocenka )

                          FROM Progress

                          WHERE Pr_DATE >04/06/02)                                        

        Эта команда подсчитывает студентов с оценкой выше средней, сдавших экзамен после 04.06.02.

Связанные подзапросы. При использовании подзапросов можно обратиться во вложенном подзапросе к таблицам из внешнего подзапроса. Например, как найти всех  студентов, сдавших экзамены 3 июня :

                                                                                                                         SELECT *

FROM Students C

WHERE '2004-06-03' IN (

             SELECT Pr_DATE

             FROM Progress O

             WHERE O.Stud_ID   = C.Stud_ID  )          

Можно было бы использовать объединение следующего вида:

SELECT C.*

FROM Students C, Progress O

WHERE C.Stud_ID   = O.Stud_ID   AND

             O. Pr_DATE = '2004.06.03'

 

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

SELECT *

FROM Progress O

WHERE ocenka > (

   SELECT AVG(ocenka )

   FROM Progress O1

   WHERE O1.Stud_ID  = O.Stud_ID )

Использование оператора EXISTS. Оператор EXISTS берет подзапрос, как аргумент, и оценивает его как верный, если подзапрос возвращает какие-либо записи и неверный, если тот не делает этого. Вот как выполняется проверка наличия студентов со стипендией = 1200:

SELECT Stud_ID , Stud_FAM, Stud_STIP

FROM Students

WHERE EXISTS (

   SELECT *

   FROM Students

   WHERE Stud_STIP = 2000  )

Внутренний подзапрос выбирает все данные для всех студентов, у которых стипендия = 1200. Оператор EXISTS во внешнем условии отмечает, что подзапрос вернул некоторые данные, следовательно, условие верное. Подзапрос будет выполнен один раз для всего внешнего запроса и имеет одно значение во всех случаях. Поэтому EXISTS, когда используется таким образом, делает условие верным или неверным для всех строк сразу.

Использование операторов ANY, ALL. Рассмотрим новый способ поиска студента, сдавшего экзамен:                                                                                   SELECT *                                                                                                            FROM Students

WHERE Stud_ID = ANY (

                   SELECT Stud_ID 

                    FROM Progress               )

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

В приведенном выше запросе можно было бы использовать оператор IN. Однако оператор ANY можно применять не только с оператором равенства.

Оператор ALL считает условие верным, если каждое значение, выбранное подзапросом, удовлетворяет условию внешнего запроса. Выберем тех студентов, у которых стипендия выше 1500:

SELECT *

FROM Students

WHERE Stud_STIP > ALL(

                    SELECT Stud_stip

                    FROM Students

                    WHERE Stud_STIP = 1500  )

 Использование команды UNION. Команда UNION объединяет вывод нескольких запросов с исключением повторяющихся строк. Например, приведенный ниже запрос выводит всех студентов и преподавателей, чьи фамилии размещены между буквами К и С:                                                                            SELECT Stud_FAM                                                                                                                                 FROM Students                                                                                                   WHERE  Stud_FAM   BETWEEN  'К'  AND 'С'                                                                  UNION                                                                                                                                SELECT Teach_FAM                                                                                                                   FROM Teacher                                                                                                     WHERE Teach_FAM  BETWEEN 'К' AND 'С'                                        Для применения команды UNION существует 2 правила:

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

·        типы данных должны быть совместимы

 

           UNION автоматически исключает дубликаты строк из вывода.             Если вы хотите, чтобы все строки из запросов попали в результат, используйте UNION ALL.:

               

 

 

                SELECT  Stud_FAM

                FROM Students

                          UNION ALL

                SELECT Teach_FAM   FROM Teacher

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

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

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

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

Представление создается с помощью команды CREATE VIEW. Представление можно создать на базе данных одной или нескольких таблиц, а также других представлений. Например, создадим представление StudAddress из таблицы Students:

CREATE VIEW    StudAddress                                                                                      AS

                                SELECT   Stud_ID, Stud_FAM, Stud_IMA,Stud_ADDRESS

                                   FROM  Students

        Для удаления представлений из базы данных используется команда DROP VIEW. У этой команды есть две опцииRESTRICT и CASCADE. Если используется RESTRICT и в условиях имеются зависимые представления, то оператор DROP VIEW возвращает ошибку. При использовании опции CASCADE и наличии зависимых представлений оператор DROP VIEW завершается успешно и все зависимые представления тоже удаляются.

 

        4.4 Задание на лабораторную работу                                                       Вначале выполните все примеры, приведенные в п.п.4.1-4.3.

Получите следующую информацию из базы данных:                                              4.4.1 Список преподавателей, ведущих дисциплины «Информатика» и          «Физика».                                                                                                                           4.4.2 Список студентов, имеющих неудовлетворительные оценки.                             4.4.3 Список студентов, не сдавших экзамен по высшей математике.             4.4.4 Список преподавателей кафедры «Информатика».                                                             4.4.5 Список кафедр с указанием фамилий заведующих кафедр.                                    4.4.6 Список названий групп с указанием фамилий старост этих групп.          4.4.7 Списки студентов каждой группы с их оценками по всем предметам.                                                                                    4.4.8 В каких группах проводятся занятия по предмету «Информатика»?         4.4.9 Какие виды занятий по Информатике проводятся в первой группе? 4.4.10 Сколько часов занятий по каждому предмету в каждой группе         проводится в семестре?                                                                                                                4.4.11 Создайте представление STUDOСENKA из таблиц Students, Subject

и Progress, в котором отражается список студентов с их оценками по предметам.                                                                                                                                                                                                                                                4.5 Контрольные вопросы                                                                                                                            4.5.1 Можно ли иметь несколько ключевых слов AND в выражении, заданном ключевым словом  WHERE?                                                                                                                                                         4.5.2 Что такое рекурсивное связывание?                                                                                 4.5.3 При связывании таблиц должны ли они связываться в том же         порядке, в  каком они указаны в выражении ключевого слова FROM?                                  4.5.4 При использовании в операторе запроса таблицы-связки,      обязательно ли выбирать в запросе ее столбцы?                                                                                                                   4.5.5 Можно ли связывать в запросе не один, а несколько столбцов      таблиц?                                                                                                      4.5.6 Какая часть оператора SQL задает условия связывания таблиц?                                            4.5.7 Что будет, если в запросе указать выборку из двух таблиц, но не связать их?                                                                       4.5.8 Для чего используются псевдонимы таблиц?                                                           4.5.9 Что такое подзапрос? Как он работает?                                                          4.5.10 Что случится, если таблица, на основе которой строится        представление, будет удалена?                                                                                                                    4.5.11 Как представление можно использовать для защиты данных?                                                           

     5 Лабораторная работа № 5. Хранимые процедуры и триггеры

   

         5.1 Использование процедур                                                                   Команды SQL (CREATE TABLE, INSERT, UPDATE, SELECT) дают возможность сообщить базе данных, что делать, но не как делать. Сообщить ядру базы данных, каким образом следует обрабатывать данные, можно посредством составления процедур.                                                                       Хранимые процедуры – это набор операторов SQL, созданный для удобства использования в программах. Сохраненную процедуру использовать проще, чем каждый раз записывать весь набор входящих в нее операторов SQL. Сохраненные процедуры можно вкладывать одну в другую (уровень вложенности не может превышать 16).   

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

         Некоторые из преимуществ использования сохраненных процедур:             -  операторы процедуры уже сохранены в базе данных;                                    - операторы процедуры уже проверены и находятся в готовом для использования виде;                                                                                                        -  при использовании процедур результат получается быстрее;                          -  возможность сохранения процедур позволяет использовать        модульное программирование;                                                                                 -  сохраненные процедуры могут вызывать другие процедуры;                    -  сохраненные процедуры могут вызываться другими программами.

 

В SQL Server   процедуры создаются с помощью оператора следующего вида:     

CREATE PROCEDURE имя_процедуры         

[ [ ( ] @имя_параметра

ТИП_ДАННЫХ [(ДЛИНА) | (ТОЧНОСТЬ)  [, МАСШТАБ ])

[=DEFAULT][OUTPUT] ]

[, @ИМЯ_ПАРАМЕТРА

ТИП_ДАННЫХ [(ДЛИНА) | (ТОЧНОСТЬ)  [, МАСШТАБ ])

[=DEFAULT][OUTPUT] ]

[WITH RECOMPILE]

AS операторы SQL

 

Сохраненные процедуры используются следующим образом:

  EXECUTE [ @ = ] имя_процедуры

  [ [ @ имя_параметра  =] значение |

  [ @ имя_параметра = ] @ переменная [ OUTPUT ] ]

  [WITH RECOMPILE]

                                                                                                                          Например, мы хотим увеличить на единицу значения номеров курсов (в поле Grup_COURSE) в таблице GRUPPA:     

        

         CREATE   PROCEDURE new_course                                                                       AS                                                                                                                                         UPDATE GRUPPA                                                                                                 SET Grup_COURSE = Grup_COURSE +1                                                                                                                                                                                            Проверим работу процедуры:                                                                          EXEC  new_course                                                                                               SELECT *                                                                                                            FROM   GRUPPA                                                                                                                                                                                                                                                                                       Вернем таблицу GRUPPA в первоначальное состояние:                                                                                                                                       CREATE   PROCEDURE old_course                                                                        AS                                                                                                                                         UPDATE GRUPPA                                                                                                    SET Grup_COURSE = Grup_COURSE -1                                                                                                                                                                                    EXEC  old_course                                                                                               SELECT *                                                                                                            FROM   GRUPPA                                                                                                                                                                                                                                                                        Пример процедуры, которую можно использовать для добавления новых строк в таблицу GRUPPA:                                                                                     CREATE PROCEDURE new_gruppa                 

            (@Grup_ID  int ,

            @Grup_NAME  char (9),                                                                                    

            @Grup_KOLSTUD  int,

            @Grup_COURSE  int)

            AS                             

                        INSERT INTO Gruppa                     

                        VALUES (@Grup_NAME, @Grup_KOLSTUD, @Grup_COURSE);       

Столбец Grup_ID таблицы GRUPPA имеет тип identity, поэтому определяется только при вводе.

Работа этой процедуры проверяется следующим образом:                                              EXEC new_gruppa 18, 'ECT-04-5', 25, 1                                                     Обратите внимание, что при вводе данных система автоматически присваивает полю Grup_ID  очередное значение независимо от  того,  какое значение определил пользователь.                                                                                  Простая процедура по использованию оператора SELECT:

CREATE PROCEDURE spisok_stud                                                                         AS                                                                                                                                        SELECT * from Students

          

  5.2 Триггеры                                                                                                                  Триггер – это откомпилированная процедура, используемая для выполнения действий, инициируемых происходящими в базе данных событиями. Триггер представляет собой сохраненную в базе данных процедуру, которая запускается тогда, когда в отношении таблицы выполняются определенные действия. Триггер может выполняться до или после операторов INSERT, UPDATE или DELETE. Наиболее распространенное применение триггеров – это проверка сложных критериев в базе данных. Триггер - особый инструмент SQL-сервера,  поддерживающий целостность данных в БД .

 Рассмотрим пример. Определим триггер tri_ins_progress для таблицы Progress, который будет запускаться каждый раз, когда запись вставляется в таблицу Progress или модифицируется. Если экзамен или зачет сданы не в срок (например, после 15-го числа месяца), то запись не принимается.

  Для создания триггера с помощью утилиты SQL Server Enterprise Manager необходимо выбрать таблицу Progress в списке  объектов  базы данных, после чего выполнить команду контекстного меню Аll tasks (Все задачи) - Manage Triggers. Эти действия приведут к открытию  диалогового  окна свойств триггера. Введите в это окно следующий текст:

 

CREATE TRIGGER tri_ins_progress

ON Progress

FOR INSERT, UPDATE

AS

/*описываются локальные переменные*/

DECLARE @nDayOfMonth TINYINT

/* определяется информация о вставляемых записях*/

SELECT @nDayOfMonth = DATEPART (Day, I.Pr_DATE)

FROM Progress P, Inserted I

WHERE P.Stud_ID = I.Stud_ID

AND P.Ocenka = I.Ocenka

/*проверяется условие вставки записи*/

/*и при необходимости сообщается об ошибке*/

IF @ nDayOfMonth >15

BEGIN

            /*Команда ROLLBACK используется для того, чтобы

  отменить модификацию данных в случае, если база заблокирована*/

ROLLBACK TRAN

RAISERROR ('Вводить оценки, полученные до 15-го числа', 16,10)

END

 

Попробуйте ввести в таблицу Progress данные об оценках, полученных

студентами позже 15-го числа.

  Триггер можно удалить следующим образом:                                                DROP TRIGGER имя_триггера          или из контекстного меню Аll tasks (Все задачи) - Manage Triggers выбором имени триггера из списка.                                                                                                                                                          5.2 Задание на лабораторную работу                                              

5.2.1     Выполните упражнения, приведенные в п.5.1.

5.2.2     Необходимо создать процедуры, позволяющие        выполнить          следующие действия:                                                                                                  - Увеличить суммы стипендий всех студентов на 15%.                    

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

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

5.3 Контрольные вопросы                                                                                       5.3.1 Может ли сохраненная процедура вызывать другую сохраненную  

процедуру?                                                                                                                                  5.3.2 В чем преимущества использования процедур?                                                       5.3.3 Когда выполняются триггеры – до или после выполнения команд      INSERT, UPDATE и DELETE?                                                                                                         

          5.3.4 Можно ли изменить триггер?

                                                                                     

6 Лабораторная работа № 6. Разработка клиентских приложений                                

6.1 Доступ к данным из приложений DELPHI (создание альяса)

Спроектировать и создать базу данных еще недостаточно для успешной работы с данными: у нас должна быть возможность как-то обращаться к этим данным, дополнять и изменять их. В этом и состоит цель «клиентского приложения» - специальной программы, позволяющей работать с данными в удобной для пользователя форме.                                                                                  Доступ к данным будет выполняться из приложений Delphi. В лабораторной работе используется механизм доступа через универсальный интерфейс, созданный на базе мощной программной библиотеки - Borland Database Engine (BDE). Главное удобство BDE в том, что все связанные с базой данных настройки находятся отдельно от клиентского приложения. Можно о них не задумываться – достаточно указать некоторое условное имя, и программа сама найдет и включит связанные с ним настройки. Это условное имя называется альясом (английское alias – псевдоним). Таким образом, чтобы программа могла манипулировать данными из базы, в первую очередь необходимо настроить BDE: создать некоторый альяс и связать его с базой данных. Разработчики предусмотрели для этого специальную служебную программу - BDE Administrator.                                 Эта процедура выполняется следующим образом:                                               6.1.1 Выберите меню Пуск-Программы- Borland Delphi7- BDE Administrator. В левой части программы BDE Administrator находится панель баз данных, в которой показаны все альясы, определенные на данный момент в BDE.                                                                                                              6.1.2  Выберите меню: Object-ODBC Administrator. В появившемся окне нажмите  кнопку Add (Добавить).                                                                                    6.1.3  В следующем окне из списка выберите тип драйвера базы данных – SQL Server.                                                                                                     6.1.4 В следующем окне задаем имя альяса (например, My_Application), описание и тип сервера (рисунок 3).                                                                         6.1.5 В одном из следующих окон выбирается из выпадающего  списка источник данных – например, education (рисунок 4), язык, используемый для

сообщений (Russian).

 

        

Рисунок 3 - Создание альяса

 
 

 


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

 

Рисунок 4 - Выбор источника данных

 
 

 

 


6.2 Создание приложения к базе данных «Education»                                     Создание первоначальной формы                                                                         6.2.1 Запустите Delphi 7. Из палитры компонентов нам, в основном, будут нужны:                                                                                                              - Data Access – источники данных. Эти компоненты могут применяться при всех типах доступа к базам.                                                                                   - BDE – компоненты, обеспечивающие доступ к данным через         механизм Borland Database Engine.                                                                            - Data Control – компоненты, предназначенные для работы с самими данными (редактирование, навигация, вывод на экран).

6.2.2 Поместите на форму компоненты Query (раздел  BDE), Data Source  (раздел Data Access), DB Greed (раздел Data Control).                             6.2.3.Установите следующие свойства компонент:                               

для Form1:  Caption  -   Справочник к базе данных education                                              Position  -   poScreenCenter                                                           для Query1: DatabaseName  -  MyApplication (выбором из списка);                                      requesLive      -   true;                                                                                         SQL                 -   select * from Students                          (свойство SQL – текст запроса. Для его ввода щелкните на кнопке с многоточием, появляющейся в правой части графы).                                            для Data Source1:     Data Set   -  Query1;                                                             для DBGreed:           Data Source  - DataSource1.                                                   6.2.4  Установите свойство  Active := true для компонента Query1. На экране появится диалоговое окно ввода запроса. Щелкните на ОК. Произойдет подключение к базе данных и в таблице появятся строки. Верните значение Active:= false для компонента Query1.

6.2.5 Добавьте в нижнюю часть  формы две кнопки (Button из раздела Standard). Установите свойства:                                                                        Caption для Button1 –  Подключиться                                                    Caption для Button2 –  Выход                                                                                             6.2.6 Напишите методы-обработчики события OnClick для кнопок. Щелкните           дважды на кнопке Button1 и отредактируйте ее метод OnClick: procedure TForm1.Button1Click(Sender: TObject);                                                      begin                                                                                                                           Query1.Active::=true;                                                                                          Button1.Enabled:=false;                                                                             end;                                                                                                          Метод кнопки Button2 (эта кнопка закрывает приложение):               procedure TForm1.Button1Click(Sender: TObject);                                                  begin                                                                                                                         if Query1.Active then Query1.Close;                                                                  Application.terminate;                                                                                 end;                                                                                                                  6.2.7 Сохраните проект. Запустите проект и нажмите кнопку «Подключиться».      Нажмите кнопку «Выход».                                                                                                                                                                                     В полученной таблице присутствуют все колонки таблицы базы данных, а в качестве заголовков колонок используются их имена. Изменим названия  заголовков таблицы на более понятные пользователю.                                  6.2.8 Выделите таблицу DBGreed1 и в контекстном меню выберите пункт Column       Editor. Нажмите кнопку Ins. Появится новая строка.. каждая строка этой таблицы соответствует колонке компонента DBGreed1.                              6.2.9 Установим следующие значения свойств колонки:                         FieldName - имя поля таблицы, связанного с этой колонкой (например, «Фамилия студента»  вместо Stud_FAM»; Раскройте список Title, щелкнув по крестику рядом со словом Title,     выберите свойства заголовка:                                    Alignment -  taCenter;                                                                                         Caption     Фамилия студента;                                                                         Font -           полужирный.                                                                             Аналогично создайте заголовки Имя, Отчество, Дата рождения.                                 6.2.10 Для компонента  Query1 включите свойство Active = true и отредактируйте ширину формы, таблицы и колонок так, чтобы все они помещались в окне. Проверьте работу приложения.                                                                                                                                                                   Работа с данными                                                                                              6.2.11 Запустите свое приложение.        Для перехода к режиму         редактирования установите курсор в редактируемой позиции и нажмите любую клавишу.         Отредактируйте какие-нибудь записи.                                            6.2.12 Реализуем программно удаление записей. Поместите на форму кнопку Button3 с надписью «Удалить». Создайте обработчик нажатия этой кнопки:                                                                                                      procedure TForm1.Button3Click(Sender:TObject);                                                     begin                                                                                                                           Query1.Delete;                                                                                          end;                                                                                                          6.2.13 Перед удалением записи  должно появляться предупреждающее сообщение. Поэтому добавьте обработчик события BeforeDelete (это свойство компонента SQL на вкладке EVENTS):                                                         procedure TForm1.Query1BeforeDelete(DataSet:DataSet);                                          begin                                                                                                                           if not (MessageDlg(‘Удалить запись?’,                                                                      mtError, [mbYes,mbNo],0) = mrYes) then                                                        begin                                                                                                                            Abort;                                                                                                                 end;                                                                                                          end;                                                                                                                 6.2.14 Разместите в нижней части формы элемент TPageControl (из раздела Win32). На одной из страниц этого элемента поместим элементы поиска, на другой – справочную информацию о разработчике программы. Отредактируйте размеры компонента.                                                            6.2.15  Для создания страниц компонента щелкните по нему правой кнопкой мыши и выберите пункт «New Page” контекстного меню Проставьте свойства Caption для TabSheet1 – «Поиск», для TabSheet2 – «Справка».           6.2.16          Разместите на странице «Поиск» компоненты TLabel, TEdit, TButton со     следующими свойствами:                                                                                 Caption   для Label1 – Фамилия;                                                                       Caption для Button4 – Найти;                                                                            Text для Edit1         - пусто.                                                            Создайте обработчик для кнопки Button4:                                          procedure TForm1.Button4Click(Sender: TObject);                                    begin                                                                                                                        Query1.Locate('Stud_FAM',Edit1.text,[loPartialkey, loCaseInsensitive]);           end;                                                                                                                   При нажатии этой кнопки будет происходить поиск записи с фамилией, начинающейся со строки, введенной в поле Edit1.                                                    6.2.17 У нас запись в базе данных находится даже по части введенного текста и без учета регистра введенных букв. Изменим поведение функции поиска. Добавим на форму два компонента TCheckBox (переключатели) с подписями «По части строки» и «Без регистра».                                                  Изменим обработчик для Button4 следующим образом:                                                                                                                                      procedure TForm1.Button4Click(Sender: TObject);                                          var LocOpts:TLocateOptions;                                                                                     begin                                                                                                                     LocOpts:=[ ];                                                                                                     if CheckBox1.Checked then LocOpts:= LocOpts+[loPartialKey];                               if CheckBox2.Checked then LocOpts:= LocOpts+[loCaseInsensitive];                     Query1.Locate (‘Stud_FAM’ Edit1.Text, LocOpts);                                          end;  

         Теперь работа функции поиска будет меняться в зависимости от поставленных пользователем «птичек».                                                                       6.2.18          Усовершенствуем программу, чтобы поиск выполнялся по нескольким полям таблицы, например, еще и по имени. Добавьте поля TEdit, TLabel. Измените подписи полей и обработчик кнопки Button4:                                                                                                                                               procedure TForm1.Button4Click(Sender: TObject);                                             var LocOpts:TLocateOptions;                                                                                      LocFields:string;                                                                                                 LocValues:variant;                                                                            begin                                                                                                                     LocOpts:=[ ];                                                                                                     LocFields:='Stud_FAM';                                                                                      if Length(Edit2.Text)>0  then                                                                                     begin                                                                                                                            LocFields:=LocFields+';Stud_IMA';                                                                     LocValues:=VarArrayCreate([0,1],varVariant);                                                     LocValues[0]:=Edit1.Text;                                                                                  LocValues[1]:=Edit2.Text;                                                                         end                                                                                                                                               else                                                                                                      LocValues:=Edit1.Text;                                                                      if CheckBox1.Checked then LocOpts:=LocOpts+[loPartialkey];                          if CheckBox2.Checked then LocOpts:=LocOpts+[loCaseInsensitive];                 Query1.Locate (LocFields, LocValues, LocOpts);                                         end;                                                                                                         6.2.19  На странице «Справка» компонента PageControl1 разместите справочную информацию.                                                                                                                                                                                                                   6.3 Задание на лабораторную работу                                                 Требуется создать приложение следующего вида для своей базы данных.                                                                                                               На основной форме устанавливается компонент TPageControl с тремя страницами: Таблицы, Запросы, Оценки.                                                                         На странице «Таблицы» выбором нужной таблицы из раскрывающегося списка (элемент TComboBox) и нажатием кнопки «Просмотреть таблицу» (TButton) вызывается нужная таблица для просмотра и  данные выбранной таблицы отражаются  в элементе TDBGrid.  С помощью компонента DBNavigator выполняется навигация по таблице.                                 Аналогично используются страницы «Запросы» и «Оценки».                      Допускается разработка собственного варианта приложения.                                                                                                                                                 6.4 Контрольные вопросы                                                                               6.4.1 Что такое интерфейсная часть приложения?                                            6.4.2 Что такое технология ODBC?                                                                            6.4.3 Как называется программное обеспечение, посредством которого осуществляется доступ к базам данных?                                                                 6.4.4 Может ли база данных на одном сервере быть доступной с другого сервера?                                                                                                                                                                                                                                      7 Задание на самостоятельную работу                                                                                                                                                                                                                         Задание на самостоятельную работу выдается преподавателем.           7.1 Проанализируйте данные выбранной (по варианту) предметной области. Определите информационные потребности и представления данных базы данных с точки зрения определенной группы пользователей. Сформулируйте возможные вопросы к моделируемой области.                           7.2 Спроектируйте информационно-логическую модель для указанной   предметной  области, позволяющую ответить на широкий круг вопросов моделируемой области: выделите объектные множества и возможные связи между ними. Отразите объекты и отношения между ними на концептуальной схеме.                                                                                                        7.3  Преобразуйте созданную концептуальную модель в реляционную.              7.4  Опишите среду разработки базы данных.                                                    7.5   Создайте таблицы базы данных. Заполните их данными.                       7.6   Выполните поиск информации в вашей базе данных, чтобы      получить ответы на поставленные вопросы. В этом разделе необходимо продемонстрировать по возможности большинство из изученных средств поиска.                                                                                                              7.7   Разработайте приложение в Delphi для доступа к данным базы  данных и для получения результатов разнообразных запросов.                               7.8  Оформите пояснительную записку к самостоятельной работе. В пояснительную записку входит:                                                                                     - описание предметной области с точки зрения конкретных пользователей;                                                                                                          -  концептуальная модель;                                                                                -  реляционная схема базы данных;                                                                    - описаны все команды, использованные для реализации проекта и работы с базой данных: создание объектов, формирование базы данных (заполнение таблиц данными); выполнение запросов к базе  данных; результаты выполнения этих команд;                                                                          - описание возможностей  приложения в Delphi с распечаткой текстов        модулей.                                                                                                            7.9 Студент сдает преподавателю пояснительную записку и выполненную работу (на дискете).                                                                                   7.10 После проверки пояснительной записки преподавателем студент допускается к защите своего проекта. Защита работы предусматривает  демонстрацию работы приложения, ответы на контрольные  вопросы.                                                                                                                                                                                  

 

 

 

 

СПИСОК ЛИТЕРАТУРЫ                                                                                                  1. Хансен Г., Хансен Д. Базы данных: разработка и управление. –     М.: ЗАО «Издательство БИНОМ», 1999.                                                                  2. Тихомиров Ю. Microsoft SQL Server 7.0 – СПб.: Издательство «Питер», 1999.                                                                                                       3. Плю Р., Стефенс Р.,  Райан К. Освой самостоятельно SQL за 24 часа. – М.:          Издательский дом «Вильямс», 2000.                                                               4. Кандзюба С.П., Громов В.Н. Delphi 6/7. Базы данных и приложения. – СПб: ООО «ДиаСофт», 2002.                                                                          5. Бобровский С. Delphi 5: учебный курс. – СПб: Издательство «Питер», 2000.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Сводный план 2004 г., поз. 18

 

 

 

 

 

 

 

Лида Куандыковна Ибраева

Елена Григорьевна Сатимова

 

 

ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ

 

Методические указания к выполнению лабораторных работ

(для студентов всех специальностей)

 

 

 

 

 

 

Редактор Н.М.Голева

 

Подписано к печати _________                                 Формат 60х84  1/16

Тираж   100  экз.                                                         Бумага типографская №1

Объем  3.0   уч.-изд.л.                                                Заказ             Цена        тг.

 

 

 

 

 

 

 

 

 

 

Копировально-множительное бюро

Алматинского института энергетики и связи

480013 Алматы, ул. Байтурсынова, 126