3496 БАЗЫ ДАННЫХ

Введение

Концепция баз данных (БД) открыла новую эру в развитии информационных систем. Она пришла на смену традиционным файловым системам и позволила поднять системы управления информацией на качественно новый уровень. Начав свой путь с 60-х годов прошлого столетия со сложных сетевых систем, реализуемых в рамках универсального языка программирования, БД в течение 10 лет превратились в самостоятельные программные продукты со своими встроенными языковыми средствами управления, теорией и практическим воплощением в коммерческих проектах.

Концепция БД имеет два направления своего развития:

  • теория и практика построения баз данных;
  • теория и практика построения систем управления базами данных.

Кроме того, необходимо различать два термина, связанных с данными:

  • управление базами данных;
  • управление информацией.

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

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

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

Соотношение между понятиями  управление базами данных и управление информацией показано на рис. 1.

Система баз данных (СБД) - это такая среда хранения и управления данными, которая удовлетворяет следующим требованиям:

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Рис. 1. Соотношение между управлением базами данных и управлением информацией

1. Рабочая программа

1.1. Цели и задачи дисциплины

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

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

Дисциплина «Базы данных» опирается на дисциплины математического, программного и аппаратного циклов, читаемых в 1–4-м семестрах.

1.2. Требования к уровню освоения содержания дисциплины

В результате изучения курса студенты должны знать:

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

должны уметь:

  • применять  современную  систему управления базами данных для реализации проекта БД;
  • применять современные языки манипулирования и описания данных.

1.3. Объем дисциплины и виды учебной работы

Вид учебной работы

Всего часов

Общая трудоемкость дисциплины

138

Аудиторные занятия

24

Лекции

12

Лабораторные работы (ЛР)

12

Самостоятельная работа

114

Контрольная работа

+

Вид итогового контроля

Экзамен

1.4. Содержание дисциплины

1.4.1. Содержание разделов дисциплины

Раздел 1. Концепция систем баз данных

Введение. История развития систем, основанных на базах данных. Файловые системы и системы баз данных. Преимущества информационных технологий с базами данных. Поколения систем управления базами данных.

Раздел 2. Архитектура систем баз данных

Трехуровневая архитектура систем баз данных ANSI/SPARC. Внешняя, концептуальная и внутренняя модели данных. Алгоритм реализации запроса пользователя. Администратор базы данных. Словарь-справочник данных. Интерфейс пользователя.

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

Раздел 3. Данные и модели данных

Введение, проблемы моделирования данных, цель моделирования. Семантика данных. Моделирование данных, сильно и слабо типизированные модели данных.

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

Раздел 4. Системы управления базами данных (СУБД)

Определение СУБД. Характеристики СУБД. Инструментальные средства СУБД. Классификация моделей данных и СУБД.

Язык определения данных и язык манипулирования данных. Объектно-ориентированные СУБД и базы данных. Средства разработки приложений.

Раздел 5. Реляционная модель данных (РМД)

Определение реляционной модели данных. Достоинства и недостатки РМД. 13 правил Кодда для реляционной модели. Расширенная реляционная модель данных. Перспективы развития РМД.

Раздел 6. Реляционные структуры данных

Реляционные объекты данных, множества и атрибуты. Отношения и домены. Свойства отношений, виды отношений. Предикат отношения. Определение отношений и доменов в СУБД. Различия между отношениями и таблицами. Способы представления отношений. Атрибуты, типы атрибутов и свойства атрибутов.

Раздел 7. Ограничения целостности

Что такое ограничения целостности. Классификация ограничений, общие и частные ограничения.

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

Внешние ключи и ссылочная целостность.

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

Раздел 8. Операции реляционных баз данных

Классификация операций в моделях данных. Реляционная алгебра Кодда. Замкнутость и полнота реляционной алгебры. Минимальная реляционная алгебра. Реляционное исчисление кортежей и реляционное исчисление доменов.

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

Языки манипулирования данными высокого уровня SQL и QBE. Конструирование запросов на языке реляционной алгебры и языках высокого уровня SQL и QBE.

1.4.2. Лабораторный практикум

Раздел

Наименование лабораторных работ

Количество часов

2

Моделирование предметной области

1

4

Управляющий центр СУБД

1

5

Конструирование таблиц

1

5

Типы и свойства атрибутов

1

8

Конструирование запросов на языке QBE

2

8

Конструирование запросов на языке SQL

2

4

Конструирование форм и отчетов

3

8

Макросы и их разработка

1

2. Методические указания к контрольной работе

2.1. Содержание контрольной работы

Выполнить задание, выданное преподавателем, предварительно изучив теоретический материал. Задание состоит в следующем:

  • разработать схему реляционной базы данных (не менее трех таблиц);
  • реализовать базу данных в среде реляционной СУБД;
  • заполнить базу данных реальными данными (10–15 записей в каждой таблице);
  • сконструировать запросы к базе данных различных типов и разной степени сложности (не менее 10 запросов).
  • Обязательными для конструирования являются следующие типы запросов: на выборку (SELECT): на создание таблицы (SELECT … INTO): на добавление: одной записи (INSERT … VALUES), множества записей (INSERT … SELECT); на удаление (DELETE); на изменение (UPDATE); перекрестный запрос (TRANSFORM); запрос с: группированием (… GROUP BY), вычислениями по горизонтали и по вертикали, подзапросом (SELECT … WHERE … (SELECT …)), параметрами.

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

Контрольная работа должна содержать:

  • титульный лист;
  • формулировку контрольного задания;
  • схему базы данных;
  • тексты запросов на русском языке;
  • тексты запросов на языке реляционной алгебры;
  • тексты запросов на языке SQL с подробными комментариями;
  • результаты выполнения запросов;
  • ответы на контрольные вопросы.

Контрольная работа выполняется в рукописном или машинописном виде.

2.2. Вопросы к контрольной работе

  1. Основные этапы (поколения) развития систем баз данных и систем управления базами данных.
  2. Как формулируется минимальное определение реляционной модели данных (РМД)?
  3. Чем отличается таблица от отношения РМД?
  4. Что такое модель данных?
  5. Назначение и общая характеристика языка описания данных (ЯОД).
  6. Схема данных и ее описание в СУБД Access.
  7. Назначение и общая характеристика языка манипулирования данными (ЯМД).
  8. Типы языков запросов к базе данных.
  9. Что такое ограничения целостности данных?
  10. Какие операции включает в себя минимальная алгебра Кодда?
  11. Какие реляционные операции реализованы в языке SQL?

3. Основные сведения о реляционных базах данных

3.1. Определение базы данных

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

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

В зависимости от характеристик этих трех составных частей и получают свои названия различные модели данных. Наиболее известными моделями данных являются: сетевая, иерархическая, реляционная и объектно-ориентированная модели.

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

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

3.2. Реляционные таблицы

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

Например, таблица Студент состоит из множества записей, состоящих из одних и тех же столбцов (полей): НомерЗачетки, Фамилия, Имя, Отчество, ДатаРождения, НомерГруппы, Хобби, СемПоложение, СредБалл. В каждом столбце каждой записи этой таблицы размещается простое значение, например,<990601, Патрикеева, Алиса, Федоровна, 28.02.1982, 943, Плавание, Не замужем, 4,56>.

Элементы реляционной таблицы имеют следующие названия:

  • Заголовок - список полей таблицы (схема таблицы);
  • Степень – количество полей в заголовке (арность таблицы);
  • Тело – все строки с данными, входящими в таблицу;
  • Мощность – количество строк в теле таблицы (кардинальное число).

Каждая таблица представляет собой некоторый объект предметной области. Например, Сотрудник, Дисциплина, Товар, Клиент, Покупатель, Договор, Ведомость, Больничный лист, Посещение поликлиники, Землетрясение, Операция и т. д.

Каждая строка таблицы представляет собой отдельный экземпляр объекта и состоит из одного и того же множества полей-столбцов, при этом каждое поле имеет смысл некоторого свойства объекта. Например, Табельный номер, Фамилия, Дата рождения - сотрудника; Название дисциплины, Артикул, Стоимость – товара; Номер, Дата заключения – договора; Дата, Место, Мощность, Ущерб – землетрясения и т. д.

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

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

3.3. Моделирование данных предметной области

Предметная область – это некоторая часть реального мира, деятельность которой требуется автоматизировать с помощью базы данных. Например, Вуз, Факультет, Поликлиника, Магазин, Фирма, Школа, Ателье, Автопредприятие, Сессия, Расписание занятий и т. д. – это предметные области (ПО).

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

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

M                         ОЦЕНКА N

СТУДЕНТ  <----------------------------------------------->  ДИСЦИПЛИНА,

где M и N означают степень участия в связи каждого из объектов. В данном случае M определяет степень участия в связи объекта дисциплина (каждую дисциплину сдают много студентов), а N определяет степень участия в связи объекта СТУДЕНТ (каждый студент сдает много дисциплин). Для реаализации связи ОЦЕНКА требуется в БД создать таблицу с тем же именем и заголовком, включающим ключевые поля связываемых объектов и, возможно, другие поля.

ОЦЕНКА (НомерЗачетки, НазваниеДисциплины, Балл, ДатаСдачи, ФИОПреп)

Таким образом, таблица ОЦЕНКА будет хранить все оценки всех студентов по всем дисциплинам.

Другой пример, объекты Студент и Группа взаимодействуют друг с другом в процессе обучения, то есть между ними существует связь, которую можно назвать Учится (со стороны студента) или Включает (со стороны группы). Эту связь можно изобразить следующим образом:

M                     УЧИТСЯ                        1

СТУДЕНТ  <----------------------------------------------->  ГРУППА,

где 1 означает, что каждый студент учится только в одной группе, то есть степень участия объекта СТУДЕНТ в связи УЧИТСЯ равна 1, тогда как степень участия объекта ГРУППА в связи УЧИТСЯ такая же, как в предыдущем примере, т. е. М, так как группа включает много студентов. Для реализации связи УЧИТСЯ требуется в состав полей односвязного объекта СТУДЕНТ ввести для связи ключевое поле многосвязного объекта ГРУППА, например поле НомерГруппы.

СТУДЕНТ (НомерЗачетки, ФИО, ДатаРожд, …, НомерГруппы)

Связи, рассмотренные в примерах, кратко обозначаются M : N и М : 1 (или 1 : М). Кроме этих связей, в практике встречаются и связи вида 1 : 1, например:

1                          СТОЛИЦА 1

ГОРОД  <----------------------------------------------------->  ГОСУДАРСТВО

В качестве основного примера для дальнейшего изложения материала выберем ПО Расписание занятий, далее просто РАСПИСАНИЕ. Здесь и далее по тексту будем обозначать имена таблиц прописными буквами, а имена полей таблиц – начинать с прописной буквы и далее строчными.

3.4. Схема реляционной базы данных

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

  • имена всех базовых таблиц;
  • имена полей каждой таблицы;
  • типы полей каждой таблицы;
  • значения свойств каждого поля каждой таблицы;
  • описание связей между таблицами.

Схема БД РАСПИСАНИЕ (рис. 2) без указания типов и свойств полей содержит 11 таблиц-объектов и 1 таблицу-связь РАСПИСАНИЕ.

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

ГРУППА (НомерГруппы, ШифрСпец, КоличСтудентов, КоличКоммерчСтуд, КоличМуж, КоличЖен, Староста, ШифрФак).

СПЕЦИАЛЬНОСТЬ (ШифрСпец, НазваниеСпец, СрокОбучения, Квалификация, ШифрКаф).

ФАКУЛЬТЕТ (ШифрФак, НазваниеФак, Декан).

ДИСЦИПЛИНА (ШифрДисц, НазваниеДисц).

ПАРА (НомерПары, ВремяНач, ВремяОконч).

ДЕНЬ (НомерДня, НазваниеДня).

ЗАНЯТИЕ (НомерЗан, НомерПары, НомерДня).

ПРЕПОДАВАТЕЛЬ (ШифрПрепод, ИмяПрепод, ШифрКаф).

КАФЕДРА (ШифрКаф, НазваниеКаф, ЗавКаф, ТелефКаф, НомерАуд).

ВИДЗАН (ШифрВидЗан, НазваниеВидаЗан).

АУДИТОРИЯ (НомерАуд, Вместимость, Ключ).

РАСПИСАНИЕ (НомерГруппы, НомерЗан, НомерАуд, ШифрПрепод, ШифрДисц, ШифрВидЗан).

Рис. 2. Упрощенная схема БД РАСПИСАНИЕ

Схема связей БД РАСПИСАНИЕ с указанием только полей связей  и первичных ключей таблиц (рис. 3) содержит связи между таблицами без контроля целостности данных по этим связям. Отсутствие таких ограничений означает, что в базу данных разрешается вводить любые данные в смысле их согласованности по связям между собой.

3.5. Типы полей

Каждое поле определяет какое-либо свойство сущности (объекта) или связи. Тип поля – основная его характеристика, которая определяется исходя из природы значений, принимаемых полем как в реальной жизни, так и в процессе эксплуатации БД. Выбирая тип поля, необходимо учитывать диапазон допустимых для него значений, а также набор операций, которые потребуется применять к этим значениям в процессе выполнения запросов  пользователя к БД. Например, СУБД Access 2000 имеет типы полей, представленные в табл. 1.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Рис. 3. Схема связей базы данных

Таблица 1

Тип

Описание

1

Текстовый

Цепочка алфавитно-цифровых символов (<256)

2

MEMO

Текст переменной длины  до 64000 байт

3

Числовой

Числовые значения (целые или вещественные)

4

Дата/Время

Дата и время

5

Денежный

Денежный (целые или вещественные числа)

6

Счетчик

Уникальное число в каждой новой записи

7

Логический

Логические значения (истина или ложь)

8

Объект OLE

OLE-объекты и иллюстрации

9

Гиперссылка

Строка, состоящая из букв и цифр, и представляющая адрес гиперссылки

3.5.1. Мастер подстановок

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

Таким образом, использование Мастера подстановок позволяет добиться двух преимуществ при ведении БД:

1)       ускорения ввода значений за счет выбора их из списка;

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

3.6. Свойства полей

3.6.1. Общие сведения о свойствах полей

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

В частности, в конструкторе таблиц СУБД Access 2000 можно установить следующие свойства полей:

  • Размер поля (FieldSize) – объем памяти в байтах, отводимый на каждое значение поля;
  • Число десятичных знаков (DecimalPlaces) – число десятичных знаков в дробной части числа (только для числовых полей);
  • Формат поля (Format) – формат отображения значения при выводе;
  • Маска ввода (InputMask) - формат значения при вводе (только для текстовых полей и полей типа Дата/Время);
  • Подпись (Caption) – заголовок поля при выводе в таблице, форме, отчете, который выводится вместо имени поля;
  • Значение по умолчанию (DefaultValue) – значение, присваиваемое полю автоматически при добавлении новой записи;
  • Условие на значение (ValidationRule) – ограничение на вводимое значение;
  • Сообщение об ошибке (ValidationText) – сообщение, выводимое при нарушении свойства Условия на значение;
  • Обязательное поле (Required) – обязательный ввод значения в поле при добавлении новой записи или обновлении существующей записи;
  • Пустые строки (AllowZeroLength) – допускается или нет ввод пустых строк (только для текстовых полей);
  • Индексированное поле (Indexed) – создается или нет индекс (уникальный или не уникальный) для данного поля;
  • Сжатие Юникод (Unicode) – сжимать Юникод символа, если в первом его байте значение 0 (только для текстовых полей);
  • Новые значения (wValue) – по порядку или случайное;
  • Мастер подстановки (Wizard) – выбор значения из списка и подстановка его в поле при добавлении или изменении записи.

 

Рис. 4. Окно конструктора таблицы со свойствами поля текстового типа

Каждому типу поля соответствует свой список свойств, некоторые из которых могут совпадать у нескольких типов, а некоторые являются уникальными, т.е. применимыми только к данному типу поля. Например, свойство Новые значения имеет только поле типа Счетчик, это свойство определяет то, как будет присваиваться значение этого поля при добавлении новой записи: в порядке возрастания или случайным образом. Распределение свойств между типами представлено в табл. 2.

Таблица 2

Свойство поля

Варианты

значений

Тип поля

Текстовый

Мемо поле

Числовой

Дата/Время

Денежный

Логический

OLE объект

Гиперссылка

Счетчик

Размер поля

…; 6

+

 

+

 

 

 

 

 

+

Число десятичных знаков

Авто; 0, 1, ..., 15

 

 

+

 

+

 

 

 

 

Формат поля

…; 6

+

+

+

+

+

+

 

+

+

Маска ввода

+

 

 

+

 

 

 

 

 

Подпись

Вводится

+

+

+

+

+

+

+

+

+

Значение по умолчанию

+

+

+

+

+

+

 

+

 

Условие на значение

+

+

+

+

+

+

 

+

 

Подпись

Вводится

+

+

+

+

+

+

 

+

 

Обязательное поле

Да; Нет

+

+

+

+

+

+

+

+

 

Пустые строки

Да; Нет

+

+

 

 

 

 

 

 

 

Индексированное поле

Да (Совп. допускаются);

Да (Совп. не допускаются); Нет

+

 

+

+

+

+

 

 

+

Новые значения

Последовательные, Случайные

 

 

 

 

 

 

 

 

+

Сжатие Юникод

Да; Нет

+

+

 

 

 

 

 

 

 

Подстановка

…; 6

+

 

+

+

 

+

 

 

 

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

  1. Ручной ввод с клавиатуры (Вводится) произвольного значения, например свойство Сообщение об ошибке.
  2. Выбор из списка (6), раскрывающегося при щелчке мыши в правой части строки свойства, например свойства Индексированное поле, Формат.
  3. Формирование значения с помощью построителя выражения (…), который вызывается стандартными способами: контекстное меню ( Построить…); элемент управления в строке свойства конструктора (  ); панель инструментов (  ).

Подробная информация о каждом типе поля и его свойстве может быть получена из справочной системы конкретной СУБД или литературы [5].

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

3.6.2. Свойство Формат поля

Формат определяет внешний вид значения поля при отображении его на экране. Например в Access имеются встроенные форматы для числовых полей и полей типа Дата/Время (рис. 5).

 

а                                                                   б

Рис. 5. Форматы полей типов: а - Дата/Время, б - Числовой, Денежный, Счетчик

Кроме того, можно создавать свои форматы отображения значений поля в таблице или форме с помощью форматных символов, например: #, 0. d, m.

Примеры

  1. Для отображения значения поля типа Дата/Время в виде 12 января 2000 г. следует задать формат dd mmmm yy г..
  2. Для отображения значения числового поля в виде $1000.45 следует задать формат $#, число десятичных знаков 2, размер поля Одинарное с плавающей точкой.

3.6.3. Свойство Маска ввода

Маска определяет содержимое и порядок ввода значения поля и тем самым позволяет организовать ввод значений поля в наиболее удобном для пользователя виде. Например, ввод значения номера телефона целесообразно организовать по маске вида: (9999)99-99-99.

Между масочными символами могут вставляться любые другие символы, например ! (999) 999-9999, здесь символы ! и 9 – это масочные символы, а символы (, ) и - – это обычные символы. Чтобы отменить действие масочного символа, необходимо поставить перед ним символ \ (обратный слэш). Например \LL, здесь первый символ L является обычным символом, а второй – масочным.

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

Таблица 3

Символ

Описание (функция)

Пусто

Маска ввода не применяется

0

Цифры от 0 до 9 (ввод обязателен, знак + или – не допустимы)

9

Цифры или пробел (ввод не обязателен, знак + или – не допустимы)

#

Цифры или пробел (ввод не обязательный, знак + или – допустимы, пустые символы преобразуются в пробелы)

L

Буквы от  A до Z или от А до Я (ввод обязателен)

?

Буквы от  A до Z или от А до Я (ввод не обязателен)

А

Буква или цифра (ввод обязателен)

а

Буква или цифра (ввод не обязателен)

&

Любой символ или пробел (ввод обязателен)

C

Любой символ или пробел (ввод не обязателен)

<

Все вводимые за ним символы преобразуются к нижнему регистру (все строчные)

>

Все вводимые за ним символы преобразуются к верхнему регистру (все прописные)

\

Любой символ, следующий за ним, воспринимается как обычный, а не масочный, например \L будет восприниматься как просто буква L, а не маска "Любая буква"

Таблица 4

Маска ввода

Варианты  вводимых значений

(00000)  0-00-00

(09234)  3-34-57

(9999) 999-999

(0912) 214-667;                  (    ) 555-024 ;                                                ( 095)  21- 44

(000) AAA-AAAA

(206) 555-TELE;                 (301) ВПМ-206а

#999

-20;    229;    -100;      +1;       0;       2000

>L????L?000L0

GREENGR339M3;            MAY R 452B7

>L<??????????????

Maria;     Рязань;     Гагарин

РГРТА 000-&&-0000

РГРТА 555-5а-5555;    РГРТА 123-Fa-1234

>L\L00000-0000

DL51392-0493;       ПL12345-1234

4. Запросы к реляционной базе данных (РБД)

4.1. Языки запросов

Как правило, при проектировании РБД получается множество реляционных таблиц, каждая из которых содержит данные о некотором простом объекте предметной области. В процессе эксплуатации БД пользователю чаще всего требуется получать данные из многих таблиц, соединенные вместе и отобранные по определенным признакам. Для эффективного получения таких данных в распоряжении пользователя СУБД имеется набор команд, объединенных под общим названием Язык Манипулирования Данными (ЯМД). Наибольшее применение и практическое развитие получили 2 вида таких языков:

  • SQL (Structure Query Language) – язык структурированных запросов, основанный на Реляционной алгебре;
  • QBE (Query By Example) – графический язык запросов по образцу, основанный на Реляционном исчислении.

Оба эти языка являются реляционно полными, т.е. любой запрос, построенный на одном языке, может быть построен и на другом языке. Фундаментом для разработки этих языков является реляционная алгебра, которая состоит из набора операторов, использующих отношения в качестве операндов и возвращающих отношение в качестве результата. Такое свойство реляционной алгебры называется замкнутостью. Это позволяет строить вложенные реляционные выражения по аналогии с обычной числовой алгеброй. Е.Ф. Кодд впервые предложил ряд операторов реляционной алгебры для манипулирования данными, размещенными в отношениях (1970 г.).

Однако правильным является и утверждение о том, что операции реляционной модели данных, а следовательно и языки запросов, основаны на реляционном исчислении. Другими словами, алгебра и исчисление представляют собой два подхода к построению операторов работы с отношениями. Например, операцию объединения двух отношений на языке реляционной алгебры можно представить как R È S, а на языке реляционного исчисления как {t |( t Î R) Ú( t Î S)}, где t - это некоторый кортеж результирующего отношения такой, что логическое выражение, стоящее после знака |, равно истине.

4.2. Основы реляционной алгебры

4.2.1. Реляционная алгебра и реляционное исчисление

Фундаментальное отличие реляционной алгебры (РА) от реляционного исчисления (РИ) заключается в том, что РА определяет, что и в какой последовательности необходимо сделать с реляционными таблицами (отношениями), чтобы получить нужные данные, а РИ определяет, какие данные требуется получить, ничего не говоря о том, как это надо сделать.

4.2.2. Основные реляционные операторы

Рассмотрим более подробно операторы РА. Е.Ф. Кодд определил 8 основных операторов РА.

1. Селекция (WHERE) – выбор из таблицы-аргумента только тех записей, которые удовлетворяют заданному в операторе условию, например расписание группы 1030:

РАСПИСАНИЕ WHERE НомерГруппы = '1030';.

2. Проекция ([ ])отбор из таблицы-аргумента только тех столбцов (полей), которые указаны в операторе, например номера групп, хранящихся в базе данных: ГРУППА [НомерГруппы];.

3. Объединение (UNION, È) – слияние двух таблиц-аргументов в одну по вертикали, при этом к записям первой таблицы добавляются те записи из второй таблицы, которые отсутствуют в первой, например R UNION S, где R и S – таблицы, имеющие эквивалентные схемы.

4. Разность (EXCEPT, ) – удаление из таблицы-уменьшаемого тех записей, которые принадлежат таблице-вычитаемому, например R EXCEPT S, где R и S – таблицы, имеющие эквивалентные схемы.

5. Пересечение (INTERSECT, Ç ) – удаление из первой таблицы-аргумента тех записей, которые не принадлежат второй таблице-аргументу, например R INTERSECT S, где R и S – таблицы, имеющие эквивалентные схемы. Примеры операций È,  –  и  Ç  приведены в табл. 5.

Таблица 5

r

s

r È s

r – s

r Ç s

A

B

C

A

B

C

A

B

C

A

B

C

A

B

C

a

b

c

a1

b

c

a

b

c

a

b

c1

a

b

c

a1

b1

c1

a

b1

c

a1

b1

c1

a2

b1

c

a1

b1

c1

a1

b

c

a2

b2

c2

a1

b

c

 

 

 

a1

b

c

a

b

c1

a1

b1

c1

a

b

c1

 

 

 

a2

b2

c2

a2

b2

c2

a1

b2

c3

a2

b2

c2

 

 

 

 

 

 

a2

b1

c

a

b

c

a2

b1

c

 

 

 

 

 

 

 

 

 

a3

b3

c3

a1

b2

c3

 

 

 

 

 

 

 

 

 

 

 

 

a3

b3

c3

 

 

 

 

 

 

6. Декартово произведение (TIMES) – слияние двух таблиц-аргументов в одну по горизонтали таким образом, что каждая запись первой таблицы соединяется по горизонтали с каждой записью второй таблицы (конкатенация строк). В таблице-результате заголовок будет равен объединению заголовков таблиц-аргументов, а тело – произведению мощностей таблиц-аргументов. Например, чтобы получить таблицу ЗАНЯТИЕ, содержащую все возможные варианты проведения занятий в течение недели вида: <НомерПары, НомерДня>, необходимо перемножить таблицы ДЕНЬ и ПАРА, т.е. ЗАНЯТИЕ = ДЕНЬ TIMES ПАРА; или в других обозначениях: ДЕНЬ ⊗ ПАРА. Результат выполнения этой операции приведен в табл. 6.

Таблица 6

ДЕНЬ

ПАРА

ЗАНЯТИЕ

НазваниеДня

ВремяНачала

НазваниеДня

ВремяНачала

Пн

8:10

Пн

8:10

Вт

9:55

Пн

9:55

 

11:40

Пн

11:40

 

 

Вт

8:10

 

 

Вт

9:55

 

 

Вт

11:40

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

РАСПИСАНИЕ_ГР_1030 = (((((((((РАСПИСАНИЕ JOIN ЗАНЯТИЕ) JOIN ПАРА) JOIN АУДИТОРИЯ) JOIN ПРЕПОДАВАТЕЛЬ) JOIN ДИСЦИПЛИНА) JOIN ВИДЗАН) JOIN ДЕНЬ) WHERE НомерГруппы = '1030') [НазваниеДня, ВремяНачала, НазваниеДисц, ИмяПреподав, НазваниеВидаЗан, НомерАуд]).

В этом запросе, кроме операции соединения, использованы также операции селекции и проекции.

8. Деление (DIVISION) – операция, в которой таблица-делитель должна иметь поля, входящие во множество полей таблицы-делимого. Если представить их схемы в обобщенном виде как R (A, B) и S (B), то S  R. Заголовок таблицы-частного будет равен разности R–S, а тело образуют те записи таблицы-делимого, которые соединятся с каждой записью таблицы-делителя. Например, чтобы определить, кто из преподавателей проводит занятия в понедельник первой парой, во вторник – второй парой, а в среду – третьей парой, необходимо разделить таблицу РАСПИСАНИЕ на специально сконструированную таблицу, к примеру ПН1_ВТ2_СР3 (НазваниеДня, НомерПары)

Пн                       1

Вт                       2

Ср                       3

Искомый запрос будет выглядеть следующим образом:

ПРЕПОД_1_2_3 (ИмяПрепод) =((((( РАСПИСАНИЕ JOIN ПРЕПОДАВАТЕЛЬ) JOIN ЗАНЯТИЕ) JOIN ДЕНЬ)[ИмяПрепод, НазваниеДня, НомерПары]) DIVISION ПН1_ВТ2_СР3.

В этом примере требуется сначала соединить таблицы, в которых размещаются нужные нам поля, затем выполнить над результатом соединения операцию проекции и в заключение разделить эту проекцию на вспомогательную таблицу. Или в других обозначениях: (((РАСПИСАНИЕ « ПРЕПОДАВАТЕЛЬ) « ЗАНЯТИЕ) « ДЕНЬ) ¸  ПН1_ВТ2_СР3.

Из восьми вышеперечисленных реляционных операций пять операций образуют минимальную алгебру Кодда, к ним относятся операции Селекция, Проекция, Разность, Объединение и Декартово произведение. Оставшиеся три операции могут быть выражены через эти пять. Например,

Соединение:

R  JOIN  S  =  ((R(A, B, C)  TIMES  S(C, D, E))  WHERE  R.C=S.C ) [A, B, R.C, D, E].

4.2.3. Дополнительные операции реляционной алгебры

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

1. Переименование ([RENAME] AS) – операция, не имеющая самостоятельного применения, а использующаяся внутри других операций для переименования полей, включаемых в результат их выполнения. Например, реляционное выражение: АУДИТОРИЯ RENAME Вместимость AS КолМест возвращает таблицу АУДИТОРИЯ, в которой поле Вместимость переименовано в поле КолМест.

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

2. Расширение (EXTENDED ADD) – операция, расширяющая список полей результирующей таблицы полями, не хранящимися в исходной таблице, а вычисляемыми в данной операции. Расширение позволяет выполнить различные скалярные вычисления и включить их результат в список полей результирующего множества основной реляционной операции. Операцию расширения можно назвать также операцией вычисления по горизонтали. Например, чтобы вычислить процент коммерческих студентов в каждой группе, применяем операцию расширения к таблице ГРУППА.

EXTENDED ГРУППА ADD (КоличКоммерчСтуд / КоличСтудентов * 100) AS ПроцКомм.

3. Подведение итогов (GROUP BY) – операция, предназначенная для вычисления некоторой агрегатной функции для каждой группы записей результирующего множества. К типичным агрегатным функциям относятся такие функции, как Сумма (SUM), Количество (COUNT), Среднее (AVG), Минимум (MIN), Максимум (MAX) и другие. Подведение итогов позволяет вычислить значение какой-либо агрегатной функции как для всех записей результирующей таблицы, так и для отдельных групп записей, объединенных по одному и тому же значению какого-либо поля или группы полей или выражению, построенному на совокупности полей. Операцию подведения итогов можно назвать также операцией вычисления по вертикали. Например, чтобы вычислить количество студентов – мужчин на каждом факультете,             требуется выполнить следующий запрос с применением операции подведения итогов.

GROUP ГРУППА BY (ШифрФак) ADD SUM(КоличМуж) AS КолРебят.

В данном примере все записи таблицы ГРУППА с одинаковым значением поля ШифрФак образуют одну запись результата, в которую помимо поля ШифрФак добавляется также поле с результатом вычисления общего количества студентов – мужчин (агрегатная функция SUM). Очевидно, что операция подведения итогов содержит в себе и операцию расширения (ADD), и операцию переименования (AS).

Внимание! Не допускается включать в проекцию запроса с группированием какие-либо поля, кроме полей, входящих в список группирования.

4. Реляционное присваивание (:=) – операция, предназначенная для запоминания результата запроса в постоянной таблице для последующего ее применения в других операциях. Например, чтобы создать постоянную таблицу со списком дисциплин, изучаемых группой 243, следует выполнить следующий запрос с сохранением результата в таблице ДИСЦ_ГР_243:

ДИСЦ_ГР_243 := ((РАСПИСАНИЕ where НомерГруппы = 243) join ДИСЦИПЛИНА) [НомерГруппы, НазваниеДисц].

В общем случае операцию присваивания можно представить в виде: Приемник := Источник.

5. Добавление (INSERT) – операция, предназначенная для добавления записей Источника в указанную таблицу Приемник: INSERT ИСТОЧНИК INTO ПРИЕМНИК. Например, добавим к таблице-результату предыдущего примера ДИСЦ_ГР_243 еще одну дисциплину:

INSERT {243, 'Структурное программирование'} INTO ДИСЦ_ГР_243; или

добавим к этой же таблице дисциплины, изучаемые группой 244:

INSERT ((РАСПИСАНИЕ where НомерГруппы = 244) join ДИСЦИПЛИНА) [НомерГруппы, НазваниеДисц] INTO ДИСЦ_ГР_243.

Очевидно, что операция добавления может быть реализована с помощью операции объединения (UNION).

6. Изменение (UPDATE) – операция, предназначенная для обновления данных в указанных записях таблицы Приемника: UPDATE ПРИЕМНИК SET (Список выражений присваивания). Например, заменим в расписании аудиторию 310 на 110:

UPDATE РАСПИСАНИЕ where НомерАуд = 310 SET (НомерАуд = 110).

7. Удаление (DELETE) – операция, предназначенная для удаления записей из таблицы Приемника: DELETE ПРИЕМНИК. Например, удалим из расписания все занятия, проводимые в аудитории 410:

DELETE РАСПИСАНИЕ where НомерАуд = '410'.

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

 

4.3. Примеры запросов на языке реляционной алгебры

Рассмотрим ряд примеров записи запросов к базе данных РАСПИСАНИЕ на языке реляционной алгебры.

  1. Получить список названий дисциплин, изучаемых на специальности 2204:

((РАСПИСАНИЕ JOIN  ДИСЦИПЛИНА) WHERE  ШифрСпец = '2204') [НазваниеДисц];.

В этом запросе выполняются следующие операции:

  • соединение таблиц РАСПИСАНИЕ и ДИСЦИПЛИНА по полю ШифрДисц , так как в таблице РАСПИСАНИЕ нет поля НазваниеДисц;
  • затем над результатом соединения выполняется операция селекции, т.е. отбираются только те записи, поле ШифрСпец которых имеет значение 2204;
  • и, наконец, выполняется операция проекции, которая оставляет в результирующем множестве записей единственное поле НазваниеДисц.
  1. Получить список аудиторий, свободных в понедельник в 15 ч 20 мин и вмещающих не менее 100 человек:

((АУДИТОРИЯ where Вместимость >=100) [НомерАуд]) except ((((РАСПИСАНИЕ join ЗАНЯТИЕ) join ПАРА) where ВремяНач = '15:20' and НомерДня = 1) [НомерАуд]).

В этом запросе выполняются следующие операции:

  • селекция из таблицы АУДИТОРИЯ тех записей, поле Вместимость которых имеет значение не менее 100;
  • проекция предыдущего результата на поле НомерАуд, в результате получается список всех номеров аудиторий заданной вместимости;
  • соединение таблицы РАСПИСАНИЕ с таблицей ЗАНЯТИЕ по полю НомерЗан, в результате чего получается список всех строк расписания, в которых присутствуют поля НомерПары и НомерДня;
  • соединение предыдущего результата с таблицей ПАРА по полю НомерПары, в результате чего получается множество строк расписания с полем ВремяНач;
  • селекция предыдущего результата, в результате которой из него исключаются все строки, не удовлетворяющие предикату ВремяНач = '15:20' and НомерДня = 1, то есть список занятий в указанное время;
  • проекция предыдущего результата по полю НомерАуд, таким образом, в результате второй цепочки операций мы получаем множество аудиторий, которые заняты в расписании в указанное время и день;
  • вычитание множества аудиторий, полученных на предыдущем шаге из множества аудиторий, полученных на втором шаге, в результате получаем искомый список аудиторий.
  1. Получить список номеров и старост групп, занятия которых проходят хотя бы раз в заданной аудитории, например 346:

((ГРУППА join РАСПИСАНИЕ) where НомерАуд = '346') [НомерГруппы, Староста].

В этом запросе выполняются следующие операции:

  • соединение таблицы ГРУППА с таблицей РАСПИСАНИЕ, в результате которого получается множество строк занятий, расширенных полями из таблицы ГРУППА;
  • селекция предыдущего результата для аудитории с номером 346, в результате получаем расписание занятий, проходящих в аудитории 346;
  • проекция предыдущего результата на поля НомерГруппы и Староста, таким образом, получается искомое множество групп и их старост.
  1. Получить расписание занятий для заданного преподавателя с указанием дня недели, номера аудитории, номера группы и времени начала:

((((((ПРЕПОДАВАТЕЛЬ where ИмяПрепод = 'Макаров Н.П.') [ШифрПрепод]) join РАСПИСАНИЕ) join ЗАНЯТИЕ) join ПАРА) join ДЕНЬ) [НазваниеДня, ВремяНач, НомерАуд, НомерГруппы].

В этом запросе выполняются следующие операции:

  • селекция таблицы ПРЕПОДАВАТЕЛЬ по фамилии преподавателя, в результате чего выбирается одна запись, соответствующая заданной фамилии (если нет однофамильцев);
  • проекция предыдущего результата на поле ЩифрПрепод, в результате получается единственное значение шифра преподавателя;
  • последовательное соединение предыдущего результата с таблицами РАСПИСАНИЕ по полю ШифрПрепод, ЗАНЯТИЕ по полю НомерЗан, ПАРА по полю НомерПары , ДЕНЬ по полю НомерДня, в результате получается множество строк расписания занятий для заданного преподавателя;
  • проекция предыдущего результата на указанные в запросе поля.
  1. Получить расписание лекционных занятий с указанием аудитории, преподавателя и дисциплины:

(((((РАСПИСАНИЕ join ВИДЗАН) where НазваниеВидаЗан = 'Лекции')[НомерАуд, ШифрПрепод, ШифрДисц]) join ПРЕПОДАВАТЕЛЬ) join ДИСЦИПЛИНА)[НомерАуд, ИмяПрепод, НазваниеДисц].

В этом запросе выполняются следующие операции:

  • соединение таблицы РАСПИСАНИЕ с таблицей ВИДЗАН по полю ШифрВидЗан, в результате получается множество строк расписания с добавленными полями таблицы ВИДЗАН;
  • селекция предыдущего результата по значению поля НазваниеВидаЗан, равного 'Лекции', в результате получается множество лекционных занятий;
  • проекция предыдущего результата на поля, требующиеся на выходе запроса, то есть НомерАуд, ШифрПрепод, ШифрДисц;
  • соединение проекции с таблицами ПРЕПОДАВАТЕЛЬ и ДИСЦИПЛИНА соответственно по полям ШифрПрепод и ШифрДисц с целью получения доступа к полям ИмяПрепод и НазваниеДисц;
  • проекция предыдущего результата на поля, затребованные в исходном запросе.
  1. Решение предыдущего примера нельзя считать оптимальным, так как первое соединение возвращает много лишних записей. Эффективность запроса будет выше, если на первое место поставить операцию селекции по виду занятий. Эта перестановка позволяет на первом шаге отсечь все другие виды занятий, кроме лекций, и тем самым намного сократить мощность результата соединения, выполняющегося на втором шаге:

((((РАСПИСАНИЕ join (ВИДЗАН where НазваниеВидаЗан = 'Лекции'))[НомерАуд, ШифрПрепод, ШифрДисц]) join ПРЕПОДАВАТЕЛЬ) join ДИСЦИПЛИНА)[НомерАуд, ИмяПрепод, НазваниеДисц].

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

  1. Подсчитать среднее количество студентов в группе для каждой специальности:

GROUP ГРУППА BY (ШифрСпец) ADD AVG (КоличСтудентов) AS СредКоличСтуд.

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

5. Основы использования языка структурированных запросов  SQL

5.1. Типы запросов к БД

В СУБД построить запрос можно в одном из двух режимов:

  • Ø режим конструктора (ручное конструирование, языки QBE или SQL);
  • Ø режим мастера (автоматизированное конструирование).

В свою очередь, в режиме конструктора можно использовать одновременно один из двух языков: QBE или SQL. Это очень удобно для изучения тех или иных элементов одного языка с помощью другого, так как при внесении изменений в запрос на одном из этих языков они немедленно вносятся и на другом. Запросы, связанные с манипулированием данными, можно разделить на следующие типы:

  • запрос на выборку (SELECT) – создает динамическое отношение с использованием большинства рассмотренных выше реляционных операций, при определенных ограничениях его можно редактировать; этот тип запроса является основой для конструирования большинства других типов запросов;
  • запрос на создание новой таблицы (SELECT … INTO) – аналогичен запросу на выборку с единственным отличием, что результат запроса запоминается в новой таблице и отрывается от своего источника, т.е. является статическим; результат этого запроса редактировать нельзя;
  • перекрестный запрос (TRANSFORM … SELECT … PIVOT) – создает перекрестную таблицу (матрицу), отражающую информацию о взаимном сочетании значений двух полей таблицы-источника, например, количество часов в неделю у каждого преподавателя по каждому виду занятий в расписании;
  • запрос на добавление (INSERT) – позволяет добавить в какую-либо таблицу одну или более новых записей как с клавиатуры, так и из другого источника данных;
  • запрос на изменение (UPDATE) – позволяет изменить значения определенных полей в определенных записях определенной таблицы;
  • запрос на удаление (DELETE) – позволяет удалить из какой-либо таблицы определенные записи.

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

5.2. Запрос на выборку

5.2.1. Обобщенный синтаксис команды выборки SELECT

SELECT <список операции проекции>

FROM <список операции соединения>

WHERE <предикат операции селекции>

GROUP BY <список операции группирования>

HAVING <предикат операции группирования>

ORDER BY <список операции упорядочивания>;

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

  • минимальная форма команды требует обязательного присутствия в ней только двух первых фраз, определяющих соответственно: "что выбрать" и "откуда выбрать";
  • порядок следования фраз требуется соблюдать тот же, за исключением фраз WHERE и GROUP BY, HAVING, которые допускается переставлять; фраза ORDER BY в любом случае должна быть последней;
  • весь текст команды может размещаться на одной строке;
  • признаком окончания команды является символ ";" (точка с запятой);
  • внутри команд SQL могут использоваться и другие разделительные символы:
  • запятая (,) – для разделения элементов списков (полей, таблиц, значений);
  • квадратные скобки ([ ]) – для записи имен полей, содержащих недопустимые символы, например пробел;
  • точка (.) – для записи составного имени, когда в запросе присутствуют одноименные поля из разных таблиц, то для их различения перед ними ставится имя таблицы, затем точка и далее имя поля (например, ДЕНЬ.НомерДня);
  • одинарные (') или обычные (") кавычки – для ограничения текстовых констант (например, "10а"  или 'красный');
  • решетка (#) – для ограничения констант типа Дата/Время (например, #12.10.03# или #12:35:15#);
  • звездочка (*) и вопросительный знак (?) – как символы шаблона, заменяющие соответственно любое количество символов или один символ в конструкции LIKE (в стандарте SQL им соответствуют символы % и _);
  • Ø порядок выполнения команды следующий:
  • соединение таблиц  - источников данных (FROM);
  • селекция записей, удовлетворяющих предикату (WHERE);
  • группирование записей в соответствии со списком (GROUP BY);
  • селекция групп, удовлетворяющих предикату (HAVING);
  • проекция полей в соответствии со списком (SELECT);
  • упорядочивание результата в соответствии со списком (ORDER BY).

5.2.2. Фраза SELECT

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

  • DISTINCT - аргумент, используемый для подавления дублей в выборке;
  • ALL или TOP n, или BOTTOM n – аргумент, используемый для управления объемом выборки, где ALL означает все записи (действует по умолчанию), TOP n – оставить первые n записей в выборке, BOTTOM n – оставить последние n записей в выборке;
  • символ * (звездочка) означает включить в проекцию все поля источника запроса;
  • любое скалярное выражение, возвращающее значение допустимого типа;
  • любое реляционное выражение (команда SELECT), возвращающее единственное значение (таблица из одной строки и одного столбца).

Таким образом, кроме имен полей таблиц – источников запроса, в список проекции могут входить и любые правильно сконструированные скалярные выражения, которые реализуют вычисления по горизонтали и представление результатов вычислений по вертикали (агрегатной функции). Причем аргументами в этих выражениях могут быть такие элементы, как поля таблиц – источников запроса, функции СУБД и собственные функции пользователя, элементы управления форм, константы и переменные модулей VBA. Если требуется какому-либо полю запроса присвоить собственное имя, то следует использовать фразу AS <Имя> после этого поля в списке операции проекции.

Примеры списков операции проекции при выполнении запроса на выборку из таблиц БД РАСПИСАНИЕ (рис. 2):

SELECT DISTINCT НомерАуд FROM расписание; ' запрос выбирает список аудиторий, используемых в расписании, фраза DISTINCT подавляет все дубли в списке;

SELECT TOP 5 НомерГруппы, ШифрСпец, КоличКоммерчСтуд, КоличКоммерчСтуд / КоличСтудентов * 100 AS ПроцентКоммСтуд FROM ГРУППА; 'запрос выбирает первые 5 записей из таблицы ГРУППА и вычисляет процент коммерческих студентов, новое поле получает имя ПроцентКоммСтуд;

SELECT SUM (КоличЖен) AS ВсегоЖенщин, SUM (КоличЖен) / SUM (КоличСтудентов) * 100 AS Впроцентах FROM ГРУППА; ' запрос вычисляет общее количество студенток во всех группах в абсолютном и процентном исчислении; для вычисления использована агрегатная функция SUM;

SELECT НомерГруппы, КоличКоммерчСтуд / ( SELECT SUM (КоличКоммерчСтуд ) ) * 100, "%" FROM ГРУППА; ' запрос вычисляет для каждой группы процент коммерческих студентов от их общего числа в институте; для вычисления общего числа коммерческих студентов в институте использованы вложенный запрос SELECT и агрегатная функция SUM; единица измерения % записана в виде текстовой константы;

SELECT COUNT (*) FROM ДИСЦИПЛИНА; ' запрос вычисляет общее количество записей в таблице ДИСЦИПЛИНА, для вычисления использована агрегатная функция COUNT.

5.2.3. Фраза FROM

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

  • имя одной единственной таблицы, как в вышеприведенных примерах;
  • несколько таблиц, разделенных запятой (означает их декартово произведение);
  • соединение (JOIN) нескольких таблиц, которое может быть внутренним (INNER) или внешним, последнее, в свою очередь, может быть левым (LEFT) или правым (RIGHT); записываются соответственно: INNER JOIN, LEFT JOIN, RIGHT JOIN.

При использовании нескольких таблиц во фразе FROM может возникнуть конфликт имен полей, совпадающих в разных таблицах и используемых в какой-либо части запроса. В этом случае к имени поля следует добавить приставку – имя таблицы и разделить их точкой, например ГРУППА.№Группы. Для сокращения записи таких составных имен таблице можно присвоить псевдоним, записав его через пробел после имени таблицы во фразе FROM, например FROM РАСПИСАНИЕ РС, ПРЕПОДАВАТЕЛЬ ПР. Здесь РС и ПР - псевдонимы, тогда обращаться к полям этих таблиц можно по псевдониму, например: ПР.ШифрПрепод или РС.ШифрПрепод.

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

Группа Г INNER JOIN РАСПИСАНИЕ Р ON Г.НомерГруппы = Р.НомерГруппы

В SQL реализуется так называемое тета-соединение, то есть вместо знака равенства можно использовать любой другой знак сравнения >, <, >=, <=, <>.

Замечание: язык QBE поддерживает только эквисоединение, то есть =.

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

SELECT НомерГруппы, А.НомерА, Вместимость

FROM АУДИТОРИЯ А INNER JOIN РАСПИСАНИЕ Р ON А.НомерА = Р.НомерА;.

Здесь потребовалось выполнить соединение таблиц АУДИТОРИЯ и РАСПИСАНИЕ, в которых размещены нужные поля. Очевидно, что в результате получим дублированные записи, если какая-либо группа занимается несколько раз в одной и той же аудитории. Подавить дубли можно с помощью ключевого слова DISTINCT (смотри примеры выше).

Еще пример. Получить список всех возможных пар занятий, проходящих в течение недели, упорядоченный по дням и времени начала и окончания пары:

SELECT НазваниеДня, ВремяНач, ВремяОконч

FROM ПАРА, ДЕНЬ

ORDER BY НомерДня, ВремяНач;.

Здесь во фразе FROM записана операция декартова произведения (ПАРА Ä ДЕНЬ), во фразе ORDER BY указаны поля упорядочивания. В результате получим часть содержимого таблицы ЗАНЯТИЕ.

5.2.4. Фраза WHERE

После слова WHERE содержится операция селекции, то есть здесь записывается предикат, истинное значение которого определяет множество записей, включаемых в результат запроса или, проще сказать, условие отбора. Предикат может принимать одно из трех значений: "Истина" (True), "Ложь" (False) или "Неизвестно" (Null) и имеет несколько вариантов применения, а именно:

  • простое сравнение с использованием знаков сравнения: <, >, =, <=, <> и логических операций: AND, OR, NOT (Рост < 165 OR Рост >= 185);
  • А BETWEEN Min AND Max (Min <= А >= Max);
  • А LIKE <Строка> с использованием символов шаблона (*, ?, [], !, -, #), где
    • * (звездочка) – любая цепочка символов (*ов – окончание "ов");
    • ? – любой один символ (агро? – оканчивается на любой знак);
    • [строка] – любой один символ, заключенный в скобки (ст[ое]н – "стон" или "стен");
    • [!строка] – любой один символ, кроме заключенных в скобки (ст[!ое]н – "стан", "стин", но не "стон" или "стен");
    • [а-я] – любой один символ из диапазона "а-я" (на[в-з]р – "нагар", "навар", "назар";
    • А IN (Зн1, Зн2, …, ЗнN) – А принадлежит множеству значений Знi, i =1, 2, …,N;
    • A IS NULL – пустое или не пустое (TRUE или FALSE);
    • А q SOME \ ANY (<подзапрос>) - А q любому значению из подзапроса (q - знак сравнения) (квантор существования - $);
    • А q ALL (<подзапрос>) - А q каждому значению из подзапроса (квантор общности - ");
    • EXISTS (<подзапрос>) - в подзапросе есть хотя бы одна запись (строка).

Примеры

  1. Список свободных аудиторий:

SELECT НомерАуд FROM АУДИТОРИЯ

WHERE НомерАуд <> ALL (SELECT НомерАуд FROM РАСПИСАНИЕ);.

  1. Список дисциплин, в названии которых встречается строка "математ":

SELECT НазваниеДисц FROM ДИСЦИПЛИНА

WHERE НазваниеДисц LIKE "*математ*";.

  1. Расписание группы 444 на понедельник:

SELECT ВремНач, НомерАуд, ИмяПрепод, НазвДисц

FROM ПРЕПОДАВАТЕЛЬ ПР INNER JOIN (ПАРА П INNER JOIN

((ДЕНЬ Д INNER JOIN

ЗАНЯТИЕ З ON Д.НомерДня = З. НомерДня) INNER JOIN

(ДИСЦИПЛИНА ДС INNER JOIN РАСПИСАНИЕ Р

ON ДС.ШифрДисц = Р.ШифрДисц)

ON З.НомерЗан = Р.НомерЗан)

ON П.НомерПары = З.НомерПары)

ON П.ШифрПрепод = Р.ШифрПрепод

WHERE(НомерГруппы="444") AND (НазваниеДня="Пн");.

5.2.5. Фраза GROUP BY

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

Пример

Количество студентов, на каждой специальности:

SELECT ШифрСпец, SUM(КоличСтудентов) AS ВсегоСтудентов

FROM ГРУППА GROUP BY ШифрСпец;

 

5.2.6. Фраза HAVING

Фраза HAVING имеет тот же смысл, что и фраза WHERE, но только для групп. Эта фраза имеет смысл только внутри фразы GROUP BY.

Примеры

  1. Количество коммерческих студентов на каждой специальности факультета вычислительной техники (ФВТ):

SELECT ШифрСпец, SUM(КоличСтудентов) AS ВсегоСтудентов

FROM ГРУППА

GROUP BY ШифрСпецк HAVING ШифрФак = "ФВТ";.

Здесь из всего списка специальностей выбираются только специальности ФВТ.

  1. Количество лекций и лабораторных работ в расписании занятий группы 2030:

SELECT ШифрВидЗан, COUNT(ШифрВидЗан) AS ВсегоЗанятий

FROM РАСПИСАНИЕ

WHERE НомерГруппы = "2030" GROUP BY ШифрВидЗан

HAVING ШифрВидЗан = "Лк" OR ШифрВидЗан = "Лб";.

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

5.2.7. Фраза ORDER BY

Эта фраза определяет порядок представления результата. Она может включать любое количество уровней упорядочивания. Список упорядочивания состоит из скалярных выражений, разделенных запятой. Для определения направления упорядочивания используются фразы ASC(ending) (по возрастанию – действует по умолчанию) или DESC(ending) (по убыванию).

Пример

Получить список первых 10 групп с самым большим количеством юношей:

SELECT TOP 10 НомерГруппы, КоличМуж FROM ГРУППА

ORDER BY КоличМуж;.

5.3. Запрос на создание новой таблицы

Этот вид запроса аналогичен запросу на выборку, рассмотренному выше, за одним исключением: результат запроса сохраняется как постоянная таблица и не имеет связи со своим источником. Для этого в запросе должна быть фраза INTO, за которой следует имя новой таблицы – результата. Фраза INTO должна записываться перед фразой FROM. Например, создать таблицу со списком групп специальности 2204 можно следующим запросом:

SELECT НомерГруппы, Староста, КоличСтудентов

INTO Группы_2204 FROM ГРУППА WHERE ШифрСпец = '2204';.

5.4. Перекрестный запрос

5.4.1. Назначение перекрестного запроса

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

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

5.4.2. Обобщенный синтаксис перекрестного запроса

TRANSFORM <Значение>

SELECT <Заголовки столбцов>

FROM <Источник данных>

GROUP BY <Поле группирования>

PIVOT <Заголовки столбцов>;.

5.4.3. Примеры перекрестных запросов

Пример 1

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

TRANSFORM SUM(КоличСтудентов)

SELECT ШифрСпец, SUM(КоличСтудентов) AS ВсегоСтудентов

FROM ГРУППА GROUP BY ШифрСпец

PIVOT НомерГруппы;.

Результат выполнения запроса представлен в табл. 7.

Таблица 7

ШифрСпец

ВсегоСтудентов

0412

0421

0422

0423

043

0432

0435

144

0719

22

 

 

 

 

 

 

 

22

2204

21

 

 

 

 

21

 

 

 

Сп1

49

24

 

 

 

 

 

 

 

Сп2

95

 

27

25

 

 

 

23

 

Сп3

54

 

 

 

26

 

28

 

 

Пример 2

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

Определим вначале компоненты перекрестного запроса.

  • Источник данных – соединение таблиц РАСПИСАНИЕ, ЗАНЯТИЕ, ДЕНЬ, ПАРА, ДИСЦИПЛИНА, ВИДЗАН, ПРЕПОДАВАТЕЛЬ.
  • Заголовки столбцов – поле НомерГруппы.
  • Заголовки строк – поле НазваниеДня, поле ВремяНач.
  • Значения – выражение, включающее конкатенацию полей НазваниеДисц & ИмяПрепод & НомерАуд.

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

TRANSFORM First(НазваниеД & ', ' & ИмяПрепод & Chr(13) & Chr(10) & "ауд. " & НомерАуд)

SELECT ДЕ.НазваниеДня AS День, ПА.ВремяНач AS Время

FROM ГРУППА ГР INNER JOIN (ПАРА ПА INNER JOIN ((ДЕНЬ ДЕ INNER JOIN ЗАНЯТИЕ ЗА ON ДЕ.НомерДня] = ЗА.НомерДня) INNER JOIN (ДИСЦИПЛИНА ДИ INNER JOIN (ПРЕПОДАВАТЕЛЬ ПР INNER JOIN РАСПИСАНИЕ РА ON ПР.ШифрПрепод = РА.ШифрПрепоп) ON ДИ.ШифрДисц = РА.ШифрДисц) ON ЗА.НомерЗан = РА.НомерЗан) ON ПА.НомерПары = ЗА.НомерПары) ON ГР.НомерГруппы = РА.НомерГруппы

WHERE (((ГР.ШифрФак)="ФВТ") AND ((ГР.НомерГруппы) Like "0*"))

GROUP BY ЗА.ДеньНед, ПА.ВремяНач, ДЕ.НомерДня, ГР.ШифрФак, ГР.НомерГруппы

ORDER BY ДЕ.НомерДня, ПА.ВремяНач

PIVOT РА.НомерГруппы;.

Комментарии к запросу:

  • агрегатная функция First используется для соблюдения синтаксиса группировки и не несет своей основной нагрузки;
  • цепочка функций Chr(13) & Chr(10) используется для обеспечения вывода значения в 2 строки;
  • фильтр Like "0*" обеспечивает селекцию групп 3 курса, что характерно только для данного конкретного периода обучения и данного вуза;
  • список упорядочивания во фразе ORDER BY обеспечивает вывод расписания по дням недели, а внутри одного дня по времени начала занятия.

Результат выполнения запроса представлен в табл. 8.

Таблица 8

День

Время

0324

0412

0421

ПН

8:10

 

 

Физика, Иванов В И
ауд. 444

ПН

13:35

 

 

Физика, Петров И А
ауд. 333

ВТ

8:10

Физика, Иванов В И
ауд. 234

 

 

ВТ

8:10

 

 

Математика, Новиков А.И.
ауд. 423

СР

8:10

 

Математика, Петров И А
ауд. 333

 

5.5. Запрос на добавление

Этот вид запроса обеспечивает добавление записей в таблицу. Добавление может быть двух типов:

  • добавить одну запись с заданными значениями полей:

INSERT INTO <Имя таблицы - приемника> (<Список полей>)

VALUES (<Список значений>);

  • добавить множество записей:

INSERT INTO <Имя таблицы - приемника> (<Список полей>)

<Запрос на выборку>.

Рассмотрим оба варианта добавления, но вначале следует отметить, что в СУБД Access синтаксис добавления одной записи отличается от стандарта SQL тем, что вместо слова VALUES используется слово SELECT, за которым далее идет текст обычного запроса на выборку, но в усеченном варианте.

Примеры

  1. Добавить новую группу в таблицу ГРУППА:

INSERT INTO ГРУППА ( НомерГруппы, ШифрСпец, КоличСтудентов, КоличКоммерчСтуд, КоличМуж, КоличЖен, Староста, ШифрФак)

SELECT 2110 AS Выраж1, 2010 AS Выраж2, 24 AS Выраж3;

5 AS Выраж4, 18 AS Вырае5, 6 AS Выраж6, "Степанова О" AS Выраж7, "РТФ" AS Выраж8;.

  1. Добавить множество записей из таблицы ГРУППА во вспомогательную таблицу ГРУППЫ_ФВТ:

INSERT INTO ГРУППЫ_ФВТ ( НомерГруппы, ШифрСпец, КоличСтудентов, КоличКоммерчСтуд, КоличМуж, КоличЖен, Староста, ШифрФак )

SELECT НомерГруппы, ШифрСпец, КоличСтудентов, КоличКоммерчСтуд, КоличМуж, КоличЖен, Староста, ШифрФак

FROM ГРУППА WHERE ШифрФак="ФВТ";.

  1. Вспомогательную таблицу ГРУППЫ_ФВТ можно создать с помощью следующего запроса на создание таблицы:

SELECT * INTO ГРУППЫ_ФВТ FROM ГРУППА WHERE КоличСтудентов =100;.

Здесь задана селекция с заведомо ложным значением, что позволяет создать, тем не менее, пустую таблицу, заголовок которой повторяет заголовок таблицы ГРУППА, символ * во фразе SELECT означает взять все поля.

 

5.6. Запрос на удаление

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

Обобщенный синтаксис запроса на удаление:

DELETE <Список полей> FROM <Имя таблицы - источника>

WHERE <Предикат операции селекции>;.

Здесь <Cписок полей> используется для соблюдения синтаксиса и не имеет какого-либо другого смысла, т. к. удаляются все записи целиком. Например, удалим из таблицы РАСПИСАНИЕ все занятия 5 курса специальности 2204.

DELETE Р.*

FROM РАСПИСАНИЕ Р INNER JOIN ГРУППА Г ON Р.НомерГруппы = Г.НомерГруппы

WHERE (Р.НомерГруппы Like "8*") AND (Г.ШифрСпец)="2204");.

Здесь в источнике использовано соединение основной таблицы РАСПИСАНИЕ с таблицей ГРУППА для селекции записей, значение специальности  в которых равно 2204.

5.7. Запрос на изменение

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

Обобщенный синтаксис запроса на изменение:

UPDATE <Соединение таблиц - источника>

SET <Имя поля = Выражение>, <Имя поля = Выражение>, …

WHERE <Предикат операции селекции>;.

Здесь <Выражение> - это любое скалярное выражение, совместимое по типу с полем, подлежащим изменению.

Пример

Увеличить на 1 значения полей КоличСтудентов и КоличКоммерчСтуд в группах, занимающихся в аудитории 123:

UPDATE ГРУППА Г INNER JOIN РАСПИСАНИЕ Р ON Г.НомерГгруппы = Р.НомерГруппы

SET Г.КоличСтудентов =Г.КоличСтудентов + 1, КоличКоммерчСтуд = КоличКоммерчСтуд + 1

WHERE Р.НомерАуд  = "123";.

5.8. Параметрические запросы

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

Параметр – это любой текст, заключенный в квадратные скобки. В последнем примере запроса на изменение можно вместо констант 1 во фразе SET использовать параметры.

SET Г.КоличСтудентов =Г.КоличСтудентов + [Добавить всего студентов?], КоличКоммерчСтуд = КоличКоммерчСтуд + [Из них коммерческих?].

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

SELECT А.НомерАуд

FROM АУДИТОРИЯ А

WHERE NOT EXISTS

(SELECT DISTINCT Р.НомерАуд

FROM (ПАРА П INNER JOIN ЗАНЯТИЕ З ON П.НомерПары = З.НомерПары) INNER JOIN РАСПИСАНИЕ ON З.НомерЗан = Р.НомерЗан

WHERE (П.ВремяНач = [Время?]) And (А.НомерАуд = Р.НомерАуд));.

Здесь в выражении селекции фразы WHERE использован подзапрос, который формирует единственное значение номера аудитории из таблицы расписание, если она занята в расписании в заданное время. Таким образом, используя параметр NOT EXISTS, основной запрос включает ту или иную аудиторию в целевое множество только в том случае, если результат выполнения подзапроса будет пустым, то есть искомая аудитория в данное время свободна.

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

6. Конструирование запросов на языке QBE

 

6.1. Открытие конструктора QBE

Процесс проектирования запроса на языке QBE можно открыть несколькими способами.

  • Ø В окне базы данных на вкладке Запросы нажать кнопку Создать или (для Access 2000) выбрать одну из строк: Создание запроса в режиме конструктора или Создание запроса с помощью мастера.
  • Ø В окне базы данных на вкладке Таблицы выбрать инструмент Новый объект/Запрос.
  • Ø Выбрать в главном меню пункт Вставка/Запрос.

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

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

 

6.2. Режимы представления запроса

В процессе конструирования запроса его можно представить в одном из трех режимов.

1. Окно QBE – конструктор запроса. В этом режиме запрос конструируется непосредственно на экране путем переноса нужных объектов БД в соответствующее место экрана.

2. Страница SQL – текст запроса на языке структурированных запросов SQL. В этом режиме результат конструирования запроса отображается в виде текста на языке SQL Этот текст можно редактировать

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

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

  • Ø панель инструментов (обычно левый крайний инструмент);
  • Ø контекстное меню;
  • Ø главное меню Вид.

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

6.3. Технология конструирования запроса на языке QBE

6.3.1. Состав конструктора запроса

Основное окно конструктора запроса состоит из двух частей.

  • Верхняя часть содержит источник данных для запроса, которым может быть совокупность связанных или не связанных таблиц и (или) запросов, то есть в ней формируется параметр фразы FROM команды языка SQL. Добавить таблицу в конструктор можно либо через меню (главное или контекстное), либо с помощью инструмента со значком .Удалить таблицу из конструктора можно стандартным способом.
  • Нижняя часть содержит строки и столбцы для реализации остальных компонентов запроса, а именно:
    • строка Поле – содержит список полей, используемых в запросе; поля могут относиться к списку проекции (фраза SELECT), к списку группирования (фраза GROUP BY), предикату селекции (фраза WHERE), списку упорядочивания (фраза ORDER BY); одно и то же поле может входить во все списки одновременно;
    • строка Имя таблицы – содержит имена таблиц, которым принадлежат соответствующие поля;
    • строка Групповая операция – содержит агрегатную функцию, применяемую к соответствующему полю;
    • строка Сортировка – содержит список упорядочивания;
    • строка Вывод на экран – содержит список проекции, то есть определяет, будет ли соответствующее поле  включено в список фразы SELECT;
    • строка Условие отбора – определяет предикат селекции, при этом условия, записанные в строке, соединяются логической операцией AND (И);
    • строка или - определяет расширение предиката селекции, при этом условия, записанные в этой строке, соединяются с предыдущей строкой логической операцией OR (ИЛИ).

Замечания:

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

-   в зависимости от типа запроса в список строк конструктора запроса могут включаться следующие строки:

  • Перекрестная таблица – содержит компоненты перекрестного запроса: заголовки строк, заголовки столбцов, значения;
  • Обновление – содержит список выражений фразы SET запроса на изменение, то есть выражений, значения которых должны принимать поля в результате выполнения запроса;
  • Добавление – содержит список полей фразы INSERT, то есть полей, в которые будут добавляться значения;
  • Удаление – может содержать одно из двух значений: Из или Условие; значение Из используется для указания  таблицы, из которой будут удаляться записи (как правило, это поле содержит значение *); значение Условие служит для указания того, что это поле используется только для определения условия удаления.

6.3.2. Последовательность конструирования запроса в QBE

Рекомендуется следующая последовательность действий при конструировании запроса в окне QBE.

  1. Определить источник запроса, для чего:

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

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

  1. Определить тип запроса, воспользовавшись одним из двух средств:

а)    главное меню, пункт Запрос;

б)    контекстное меню, пункт Тип запроса.

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

а)       в проекции запроса (строка Поле);

б)       в предикате селекции (строка Условие отбора);

в)       в операции группирования (строка Групповые операции);

г)        в списке на изменение (строка Добавление);

д)       в списке на удаление (строка Удаление);

е)       в перекрестном запросе (строка Перекрестная таблица);

ж)      в списке упорядочивания (строка Сортировка).

  1. Установить, если это требуется, свойство уникальности записей результата, для чего открыть окно свойств запроса (меню или инструмент  ) и установить в строке Уникальные значения значение Да.
  2. Определить основные параметры запроса в соответствии с типом запроса и требованиями пользователя, двигаясь от верхней строки к нижней. При этом для конструирования выражений можно использовать в качестве помощника Построитель выражений, вызываемый с помощью:

а)       пункта Построить… в контекстном меню;

б)       инструмент  на панели инструментов.

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

  1. Определить поля, выводимые в результате запроса, строка Вывод на экран. Каждое поле должно включаться в список вывода только один раз.
  2. Периодически по ходу конструирования запроса следует переключаться в режим таблицы (просмотра) и проверять правильность получаемого результата.
  3. Сохранить запрос, задав ему имя, семантически близкое к сути этого запроса (не следует оставлять имена, задаваемые самой СУБД). Рекомендуется начинать имя запроса с буквы З, например ЗРасписанНаПонед или ЗАудСвободВПн955 (лучше не включать пробел в имя).
    1. Управление информацией и управление базами данных.
    2. Тенденции развития информационных систем: распределение и децентрализация, неоднородность, стандартизация, моделирование реального мира.
    3. История и основные этапы развития систем, основанных на базах данных. Поколения баз данных.
    4. Манифест баз данных третьего поколения.
    5. Сравнительный анализ файловых систем и систем, основанных на базах данных.
    6. Основные отличительные признаки информационных систем, основанных на базах данных.
    7. Концепция баз данных. Определения базы данных (БД) и предметной области (ПО).
    8. Происхождение трехуровневой архитектуры данных и ее назначение.
    9. Содержание внешнего, концептуального и внутреннего уровней.
    10. Назначение отображений: внешний – концептуальный и концептуальный – внутренний. Логическая и физическая независимость данных.
    11. Языки баз данных, их назначение и классификация.
    12. Системы управления базами данных (СУБД): архитектура, выполняемые функции, характеристики.
    13. Администратор БД (АБД): архитектура, выполняемые функции.
    14. Словарь-справочник данных и его назначение.
    15. Общие представления о системах БД: система БД, уровни абстракции в СУБД, различие восприятия БД конечными пользователями, проектировщиками, прикладными программистами.
    16. Данные и модели данных, семантика данных.
    17. Моделирование данных.
    18. Определение модели данных (МД). Классификация моделей данных.
    19. Реляционные структуры данных: множества, отношения, домены.
    20. Предикат отношения.
    21. Ограничения целостности РМД, общие понятия, классификация.
    22. Ограничения целостности по существованию, потенциальные ключи.
    23. Ограничения целостности по связи, внешние ключи.
    24. Операции РМД, общая характеристика.
    25. Начальная алгебра Кодда.
    26. Расширение алгебры Кодда, вычисления по вертикали и горизонтали.
    27. Реляционная алгебра и язык SQL.
    28. Схема реляционной модели базы данных.
    29. Типы и свойства полей: тип, формат, маска ввода, значение по умолчанию, индекс, условие на значение.
    30. Ключ отношения, свойства ключа и их обеспечение в СУБД Access.
    31. Команда SELECT-SQL: назначение, синтаксис.
    32. Связь между командой SELECT-SQL и реляционной алгеброй Кодда.
    33. Управляющие структуры языка манипулирования данными СУБД.
    34. Система управления базами данных (СУБД) Access: общая характеристика.
    35. Состав и функциональные возможности управляющего центра Access.
    36. Проектирование таблиц, типы и свойства полей в СУБД Access.
    37. Реализация ограничений целостности в СУБД Access: первичный ключ, потенциальный ключ, внешний ключ.
    38. Конструирование запросов и языки SQL. и QBE.
    39. Виды запросов в Access. Управляющие запросы, запросы на обновление данных, запросы на выборку данных.
    40. Отбор, упорядочивание и группирование данных в запросах. Вложенные запросы.

7. Вопросы для экзамена по дисциплине

 

Примечание. В ответах вместо СУБД Access может быть использована любая СУБД подобного класса.

 

8. Список предметных областей для проектирования базовых таблиц

 

  1. 1. Физкультура

Зачетка, Фамилия, Группа, Дата рождения, Преподаватель, Специализация, Медицинская группа, Разряд, Вид, Особенности, Норматив, Результат, Дата, Оценка, Семестр, Пол.

  1. 2. Аптека

Номер, Дата, Врач, Поликлиника, Лекарство, Количество, Режим приема, Стоимость, Особые замечания, Шифр, Название, Группа, Краткая рекомендация по применению, Срок хранения рецепта, Дата поступления, Цена, Единица измерения, Количество, Срок годности.

  1. 3. Библиотека

Шифр, Автор, Название, Тематика Издательство, Год издания, Тираж, Количество страниц, Аннотация, Билет , Фамилия, Место работы, Должность, Телефон, Возраст, Особые отметки, Дата выдачи, Срок.

  1. 4. Расписание экзаменов

Номер группы, Специальность, Число студентов, Староста, Факультет, Курс, Дисциплины, Преподаватель, Дата консультации, Время консультации, Дата экзамена, Время экзамена, Аудитория.

  1. 5. Сессия

Экзаменационная ведомость, Дисциплина, Преподаватель, Группа, Студент, Факультет, Специальность, Оценка, Семестр, Дата, Количество часов по плану.

  1. 6. Поликлиника

Карта, Фамилия, Адрес, Возраст, Место работы, Профессия, Дата последнего посещения, Особые отметки, Номер кабинета, Название, Врач, Пропускная способность, Дата, Время, Жалобы, Диагноз.

  1. 7. Кулинария

Название блюда, Категория, Калорийность, Стоимость, Название продукта, Единица измерения, Жиры, Белки, Углеводы, Витамины, Цена, Поставщик, Количество, Состояние.

  1. 8. Больница

Номер палаты, Отделение, Число коек, Врач, Персонал, Фамилия, Карта, Возраст, Диагноз, Адрес, Профессия, Место работы, Специализация, Оклад, Телефон, Характеристика.

 

  1. 9. Турнир

Название, Город, Спонсор, Тренер,  Рейтинг, Фамилия игрока, Команда, Амплуа, Возраст, Адрес, Телефон, Характеристика, Хозяева, Гости, Дата, Судья, Результат, Оценка, Событие, Статистика, Место.

  1. 10. Коллекционирование монет

Шифр, Название, Страна, Государство, Тираж, Сплав, Год, Вес, Оценка, История, Фамилия, Адрес, Профессия, Место работы, Телефон, Количество, Событие, Номинал.

  1. 11. Телефон

Город, Индекс, Стоимость, Промежуточный пункт, Примечания, Телефон, Дата, Продолжительность, Вид оплаты, Документ об оплате, Факт оплаты, Сумма, Адрес, Фамилия, Сумма месячной оплаты.

  1. 12. Шахматы

Шифр, Команда, Возраст, Место работы, Профессия, Должность, Квалификация, Рейтинг, Телефон, Белые, Черные, Очки-б, Очки-ч, Дата, Итог, Число ходов, Номер игры, Ход-б, Ход-ч, Время-б, Время-ч.

  1. 13. Домоуправление

Адрес, Квартира, Фамилия, Площадь, Число комнат, Номер ордера, Дата получения, Сумма оплаты, Долг, Вид услуги, Стоимость, Дата введения, Дата оплаты.

  1. 14. Управление троллейбусами

Номер маршрута, Протяженность, Время, Число остановок, Начало движения, Конец движения, Состояние, Число машин, Название остановки, Номер маршрута, Номер остановки, Крыша, Время отправления, Троллейбус, Водитель, Кондуктор, Выручка.

  1. 15. Спортклуб

Название секции, Тренер, Число членов, Место занятий, Особенности приема, Оплата, Фамилия, Возраст, Адрес, Телефон, Рост, Вес, Личный рекорд, Достижения, Дата соревнования, Ранг, Результат, Место, Число участников.

  1. 16. Станция технического обслуживания

Фамилия, Разряд, Адрес, Телефон, Оклад, Стаж, Номер, Марка автомобиля, Цвет, Заводской номер, Пробег, Владелец, Техпаспорт, Год выпуска, Состояние, Дата поступления, Документ, Срок готовности, Дата окончания, Стоимость, Содержание ремонта.

  1. 17. Научно-исследовательская работа

Номер, Тема, Исполнитель, Заказчик, Дата заключения, Дата начала, Дата окончания, Сумма, Число этапов, Особые условия, Кафедра, Фамилия, Телефон, Число договоров, Сумма договоров, Адрес, Счет, Руководитель, Факс, Министерство.

  1. 18. Школа

Класс, Учитель, Предмет, Урок, Оценка, Пропуск занятия, Классный руководитель, Разряд, Ученик, Расписание, Родители, Место работы, Пол, Дата рождения, Ставка, Занятие, Кружок, Хобби, Телефон, Адрес.

  1. 19. Агентство недвижимости

Объект недвижимости, Агент, Клиент, Владелец, Арендатор, Покупатель, Продавец, Адрес, Характеристика, Площадь, Число комнат, Этаж, Договор, Сделка, Офис, Телефон, Осмотр, Сумма, Вид оплаты, Дата заключения, Дата осмотра, Работник, Срок оплаты, Тип недвижимости.

  1. 20. Брачная контора

Фамилия, Имя, Отчество, Пол, Возраст, Ориентация, Привычка, Город, Страна, Профессия, Хобби, Дети, Семейное положение, Адрес, Образование.

9. Учебно-методическое обеспечение дисциплины

9.1. Рекомендуемая литература

Основная литература

  1. Дейт К. Введение в системы БД: Пер. с англ. 6-е изд. К.: Диалектика, 1998. 784 с.
  2. Конноли Т., Бегг К., Страчан А. Базы данных: проектирование, реализация и сопровождение. Теория и практика: Уч. пособие: Пер. с англ. 2-е изд. М.: Издательский дом «Вильямс», 2000. 1120 с.
  3. Ульман Дж. Основы систем баз данных. М.: Финансы и статистика, 1983.

Дополнительная литература

  1. Дейт К. Руководство по реляционной СУБД DB2. М.: Наука, 1989.
  2. Дженнингс Р. Microsoft Access 97 в подлиннике: Пер. с англ. Т. 1, 2. СПб.: BHV – Санкт-Петербург, 1997. 624 с.
  3. Боуман Д., Эмерсон С., Дарновски М. Практическое руководство по SQL: Пер. с англ. 3-е изд. К.: Диалектика, 1997. 320 с.

Учебно-методическая литература

  1. Макаров Н.П., Пылькин А.Н., Суснина А.В., Цуканова Н.И. Проектирование БД. СУБД Microsoft Access. М.: Академия информатизации образования, 2002. 132 с.
  2. СУБД семейства dBASE: Методические указания к лабораторным работам/ Рязан. гос. радиотехн. акад.; Сост.: Н.П. Макаров, В.И. Никитин; Под ред. Н.П. Макарова. Рязань, 1992. № 2101.
  3. Реляционная модель данных: Методические указания к лабораторным работам/ Рязан. гос. радиотехн. акад.; Сост.: С.В. Бобылев, А.В. Ивашкин, Н.П. Макаров; Под ред. Н.П. Макарова. Рязань, 1995. № 2486.

10. Перечень разделов для самостоятельного изучения

 

СУБД Access: архитектура, характеристики. Конструирование основных компонентов: таблиц, запросов, форм, отчетов, макросов.

Основы языка SQL. Команда выборки данных SELECT.

Команды обновления данных. Конструирование выражений в СУБД.

 

Оглавление

Введение................................................................................................................... 1

1. Рабочая программа........................................................................................... 2

1.1. Цели и задачи дисциплины..................................................................... 2

1.2. Требования к уровню освоения содержания дисциплины............. 3

1.3. Объем дисциплины и виды учебной работы...................................... 3

1.4. Содержание дисциплины........................................................................ 3

1.4.1. Содержание разделов дисциплины.............................................. 3

1.4.2. Лабораторный практикум............................................................... 5

2. Методические указания к контрольной работе........................................ 5

2.1. Содержание контрольной работы........................................................ 5

2.2. Вопросы к контрольной работе............................................................. 6

3. Основные сведения о реляционных базах данных.................................. 6

3.1. Определение базы данных...................................................................... 6

3.2. Реляционные таблицы.............................................................................. 7

3.3. Моделирование данных предметной области.................................. 8

3.4. Схема реляционной базы данных......................................................... 9

3.5. Типы полей................................................................................................ 10

3.5.1. Мастер подстановок....................................................................... 12

3.6. Свойства полей........................................................................................ 12

3.6.1. Общие сведения о свойствах полей............................................ 12

3.6.2. Свойство формат поля................................................................... 15

3.6.3. Свойство Маска ввода................................................................... 15

4. Запросы к реляционной базе данных........................................................ 17

4.1. Языки запросов........................................................................................ 17

4.2. Основы реляционной алгебры............................................................. 17

4.2.1. Реляционная алгебра и реляционное исчисление.................. 17

4.2.2. Основные реляционные операторы........................................... 18

4.2.3. Дополнительные операции реляционной алгебры................ 20

4.3. Примеры запросов на языке реляционной алгебры...................... 22

5. Основы использования языка структурированных запросов  SQL.. 25

5.1. Типы запросов к БД................................................................................ 25

5.2. Запрос на выборку.................................................................................. 26

5.2.1. Обобщенный синтаксис команды выборки SELECT............. 26

5.2.2. Фраза SELECT.................................................................................. 28

5.2.3. Фраза FROM..................................................................................... 29

5.2.4. Фраза WHERE.................................................................................. 30

5.2.5. Фраза GROUP BY............................................................................ 31

5.2.6. Фраза HAVING................................................................................. 32

5.2.7. Фраза ORDER BY............................................................................ 32

5.3. Запрос на создание новой таблицы................................................... 33

5.4. Перекрестный запрос............................................................................. 33

5.4.1. Назначение перекрестного запроса........................................... 33

5.4.2. Обобщенный синтаксис перекрестного запроса.................... 33

5.4.3. Примеры перекрестных запросов............................................... 33

5.5. Запрос на добавление............................................................................ 35

5.6. Запрос на удаление................................................................................ 36

5.7. Запрос на изменение............................................................................... 37

5.8. Параметрические запросы.................................................................... 37

6. Конструирование запросов на языке QBE............................................... 38

6.1. Открытие конструктора QBE............................................................... 38

6.2. Режимы представления запроса.......................................................... 39

6.3. Технология конструирования запроса на языке QBE................... 39

6.3.1. Состав конструктора запроса..................................................... 39

6.3.2. Последовательность конструирования запроса в QBE........ 41

7. Вопросы для экзамена по дисциплине...................................................... 42

8. Список предметных областей для проектирования базовых таблиц 44

9. Учебно-методическое обеспечение дисциплины................................... 46

9.1. Рекомендуемая литература.................................................................. 46

10. Перечень разделов для самостоятельного изучения.......................... 47