Белорусский национальный технический университет Республиканский институт инновационных технологий Кафедра «Инженерная педагогика и психология» СОГЛАСОВАНО Заведующий кафедрой ________И.Л. Прокопчик-Гайко СОГЛАСОВАНО Директор института ________В.Л.Соломахо УЧЕБНО-МЕТОДИЧЕСКИЙ КОМПЛЕКС ПО УЧЕБНОЙ ДИСЦИПЛИНЕ «Модели данных и системы управления базами данных» для специальности 1-31 03 74 «Прикладная информатика» Составители: Молчина Л.И., старший преподаватель Рассмотрено и утверждено на заседании Совета РИИТ ____. _____. 20___ г., протокол № Молчина Л.И. ЭУМК Модели данных и системы управления базами данных2 © 2011 Молчина Л.И. 0 СОДЕРЖАНИЕ Часть I Теоретический раздел 4 ................................................................................................................................... 41 Материалы к лекционному курсу .......................................................................................................................................................... 4Элементы теории множеств ......................................................................................................................................................... 4Теория множеств. .......................................................................................................................................................... 13Понятия математической логики .......................................................................................................................................................... 13Основные понятия реляционной алгебры ......................................................................................................................................................... 13Основные понятия реляционной алгебры .......................................................................................................................................................... 25Автоматизация обработки информации ......................................................................................................................................................... 25 Понятие экономической и автоматизированной информационной системы. ......................................................................................................................................................... 26 Понятие БД и СУБД. функциональные возможности и классификация СУБД. .......................................................................................................................................................... 29Проектирование БД Организация связей между объектами БД ......................................................................................................................................................... 30Требования, предъявляемые к БД ......................................................................................................................................................... 31Модели данных Компьютерная реляционная БД ......................................................................................................................................................... 34Этапы проектирования баз данных .......................................................................................................................................................... 34Общая характеристика СУБД MS Access ......................................................................................................................................................... 341. Общая характеристика СУБД MS Access ......................................................................................................................................................... 372. Описание полей базы данных .......................................................................................................................................................... 40Среда СУБД MS Access Проектирование таблиц и работа с ними ......................................................................................................................................................... 401. Создание новой базы данных ......................................................................................................................................................... 422.Создание связей между таблицами ................................................................................................................................... 432 Мультимедийные презентации ................................................................................................................................... 433 Электронные образовательные ресурсы по дисциплине Часть II Практический раздел 44 ................................................................................................................................... 441 Задания для практических занятий .......................................................................................................................................................... 44Теория множеств .......................................................................................................................................................... 52Математическая логика .......................................................................................................................................................... 53Реляционная алгебра .......................................................................................................................................................... 73Проектирование БД Организация связей между объектами БД ......................................................................................................................................................... 74Проектирование таблиц базы данных ......................................................................................................................................................... 84Создание связей между таблицами .......................................................................................................................................................... 86Функциональные возможности СУБД .......................................................................................................................................................... 86Среда СУБД MS Access Проектирование таблиц и работа с ними .......................................................................................................................................................... 89Формирование запросов в СУБД MS Access ......................................................................................................................................................... 90Задание условий отбора в запросе ......................................................................................................................................................... 92Создание параметрических запросов ......................................................................................................................................................... 93Групповые вычисления в запросе ......................................................................................................................................................... 94Запрос на удаление .......................................................................................................................................................... 95Проектирование форм в СУБД MS Access ......................................................................................................................................................... 95Создание формы с помощью Автоформы ......................................................................................................................................................... 96Создание формы с помощью Мастера ......................................................................................................................................................... 97Создание формы в режиме конструктора .......................................................................................................................................................... 102Проектирование отчетов в СУБД MS Access ......................................................................................................................................................... 102Создание отчета с помощью Автоотчета 3Contents 3 © 2011 Молчина Л.И. ......................................................................................................................................................... 103Редактирование отчета в режиме конструктора ......................................................................................................................................................... 103Создание кнопочной формы ......................................................................................................................................................... 105Создание отчета с помощью Мастера .......................................................................................................................................................... 106Организация запросов в языке SQL .......................................................................................................................................................... 106Программирование в среде MS Access .......................................................................................................................................................... 109Объектно-ориентированные базы данных .......................................................................................................................................................... 109Администрирование баз данных ................................................................................................................................... 1232 Задания для самостоятельной работы Часть III Раздел контроля знаний 125 ................................................................................................................................... 1251 Материалы текущей аттестации ................................................................................................................................... 1312 Тематика контрольных работ Часть IV Вспомогательный раздел 133 ................................................................................................................................... 1331 Учебно-методическая документация .......................................................................................................................................................... 134Методические рекомендации по организации учебного процесса .......................................................................................................................................................... 135Методические рекомендации по изучению дисциплины .......................................................................................................................................................... 136Перечень учебных изданий и информационно-аналитических материалов Часть V Программный блок 137 ................................................................................................................................... 1371 Учебная программа Index 0 Молчина Л.И. ЭУМК Модели данных и системы управления базами данных4 © 2011 Молчина Л.И. 1 Теоретический раздел В теоретическом разделе представлены: · материалы к лекционному курсу; · мультимедийные презентации; · электронные образовательные ресурсы по дисциплине. Разделы "Теория множеств", "Математическая логика", "Реляционная алгебра" разработаны кандидатом технических наук, доцентом Чичко О.И. 1.1 Материалы к лекционному курсу Материалы к лекционному курсу 1.1.1 Элементы теории множеств Человеческое мышление устроено так, что мир представляется состоящим из отдельных “объектов”, хотя философам давно ясно, что мир – единое неразрывное целое, и выделение в нем объектов – это не более чем произвольный акт нашего мышления, позволяющий сформировать доступную для рационального анализа картину мира. Но как бы там ни было, выделение объектов и их совокупностей – естественный (или даже единственно возможный) способ организации нашего мышления, поэтому неудивительно, что он лежит в основе главного инструмента описания точного знания – математики. 1.1.1.1 Теория множеств. План 1. Понятие множества. 2. Задание множеств. 3. Операции над множествами 4. Свойства операций над множествами. 5. Упорядоченные пары. 6. Прямое произведение множеств. 7. Отношения и их свойства. 8. Функции и их свойства . 1. Множества. Понятия “множества”, “отношения”, “функции” и близкие к ним составляют основной словарь дискретной (равно как и классической “непрерывной”) математики. Именно эти базовые понятия будем рассматривать, закладывая необходимую основу для дальнейшего изучения предмета. Причем, будем рассматриваются только конечные множества Можно сказать, что множество – это любая определенная совокупность Теоретический раздел 5 © 2011 Молчина Л.И. объектов. Объекты, из которых составлено множество, называются его элементами. Элементы множества различны и отличимы друг от друга. Примеры. Множество S страниц в книге. Множество N натуральных чисел 1, 2, 3, ... Множество Р простых чисел 2, 3, 5, 7, 11, ... Множество Z целых чисел: ... , -2, -1, О, 1, 2, ... Множество R вещественных чисел. Если объект х является элементом множества М, то говорят, что х принадлежит М. Обозначение: х ° М. В противном случае говорят, что х не принадлежит М. Обозначение: х  М. ЗАМЕЧАНИЕ ––––––––––––––– Обычно множества обозначают прописными буквами латинского алфавита, а элементы множеств – строчными буквами. ОТСТУПЛЕНИЕ ––––––––––––– Понятия множества, элемента и принадлежности, которые на первый взгляд представляются интуитивно ясными, при ближайшем рассмотрении такую ясность утрачивают. Во-первых, проблематична отличимость элементов. Например, символы а и a, которые встречаются– это один элемент множества А или два разных элемента? Во-вторых, проблематична возможность (без дополнительных усилий) указать, принадлежит ли данный элемент данному множеству. Например, является ли число 86958476921537485067857467 простым? Множества, как объекты, могут быть элементами других множеств. Множество, элементами которого являются множества, обычно называется классом или семейством. ЗАМЕЧАНИЕ –––––––––––––––––– Семейства множеств обычно обозначают прописными “рукописными” буквами латинского алфавита, чтобы отличить их от множеств, не содержащих множеств в качестве элементов. Множество, не содержащее элементов, называется пустым. Обозначение  . Обычно в конкретных рассуждениях элементы всех множеств берутся из некоторого одного, достаточно широкого множества (своего для каждого случая), которое называется универсальным множеством или универсумом и обозначается U. 2. Задание множеств. Чтобы задать множество, нужно указать, какие элементы ему принадлежат. Это можно сделать различными способами: перечислением элементов: М: ={a1, а2,..., аk}; характеристическим предикатом'. М :={х\ Р(х)}; порождающей процедурой: М : = [х \ х: = f}. ЗАМЕЧАНИЕ При задании множеств перечислением обозначения элементов обычно Молчина Л.И. ЭУМК Модели данных и системы управления базами данных6 © 2011 Молчина Л.И. заключают в фигурные скобки и разделяют запятыми. Характеристический предикат – это некотоpое условие, выраженное в форме логического утверждения или процедуры, возвращающей логическое значение. Если для данного элемента условие выполнено, то он принадлежит определяемому множеству, в противном случае – не принадлежит. Порождающая процедура – это процедура, которая, будучи запущенной, порождает некоторые объекты, являющиеся элементами определяемого множества На латыни слово "предикат" (praedicatum) означает "сказуемое". Традиционная логика выделяет в элементарном высказывании (суждении) субъект и предикат. Субъект (логическое подлежащее) - то, о чем говориться в высказывании; предикат (логическое сказуемое) - то, что говорится (утверждается или отрицается) о субъекте. Например, в высказывании "Кошка имеет четыре ноги" "кошка" - субъект, "имеет четыре ноги" - предикат. Если на место слова "кошка" поставить слово "собака", то снова получится истинное высказывание "Собака имеет четыре ноги". Если же в качестве субъекта взять слово "курица", то получится ложное высказывание "Курица имеет четыре ноги". Заменив субъект переменной, получаем высказывательную форму "х имеет четыре ноги" и предикат как функцию, задаваемую этой формой. Естественным обобщением понятия "одноместный предикат" является понятие "n-местный предикат". Одноместные предикаты иногда называют предикатами-свойствами, а п- местные при п > 1 - предикатами-отношениями. Так, например, предикат х < 0 выражает свойство чисел, а предикат х < у - отношение между числами. Позже смысл слов "свойство" и "отношение" будет уточнен. Примеры. 1. М1: ={1,2,3,4,5,6,7,8,9}; 2. M2:={n|n° N&n< 10}; 3. Предикат Р задан таблицей 1 2 3 4 5 6 7 8 9 л и л и л и л и л Выпишите область определения и множество значений предиката Р. Укажите, чему равны Р(1), Р(6). Подберите высказывательную форму, с помощью которой можно задать предикат Р. 3. Предикат Q задан на множестве однозначных натуральных чисел вы сказы нательной формой "х - простое число". Задайте предикат Q с помощью таблицы. Чему равны Q(l), Q(2), Q(6)? Множество целых чисел в диапазоне от т до п обозначают так: m...n. То есть т..п: = {k ° Z | 0 k & k  n} . Перeчислением можно задавать только конечные множества. 3. Операции над множествами. Самого по себе понятия множества еще недостаточно – нужно определить Теоретический раздел 7 © 2011 Молчина Л.И. способы конструирования новых множеств из уже имеющихся, то есть операции над множествами. Множество А содержится в множестве В (множество В включает множество А), если каждый элемент А есть элемент В: AB:=x ° A. x ° B. В этом случае A называется подмножеством В, В – надмножеством А. Если А  В и А В, то А называется собственным подмножеством В. Заметим, что "M М  М. По определению "M   М. Два множества равны, если они являются подмножествами друг друга: А=В:=АВ&ВА. Совокупность всех подмножеств множества А называется его булеаном и обозначается 2А. Мощность множества М обозначается как ]М|. Для конечных множеств мощность – это число элементов. Например, | | = 0, но |{ }| = 1. Если |A| = |B|, то множества A n В называются равномощными. Обычно рассматриваются следующие операции над множествами: объединение: A B:={x\ x° А * х° В}; пересечение: А В:={х\ х А & х В}; разность: А\В:={х | х A&x  B}; симметрическая разность: A! B: =(А U В) \ (А  B) = {х \ {х  А & х  В) V (x А &  В)}; дополнение: А:={х\х А}. Пример Пусть А: ={1,2,3}, В: ={3,4,5}. Тогда А U В ={1,2,3,4,5}, А  В {3}, A\В={1,2}, A ! В =-{1,2,4,5}. На рис. 1.1 приведены диаграммы Эйлера, иллюстрирующие операции над множествами. Сами исходные множества изображаются фигурами (в данном случае овалами), а результат графически выделяется (в данном случае для выделения использована штриховка). Операции пересечения и объединения допускают следующее обобщение. Пусть I – некоторое множество, элементы которого используются как индексы, и пусть для любого i ° I множество Ai известно. Тогда  Аi: = {х | $ i ° I х ° Аi},  Аi: = {х | " i ° I х ° Аi}. i° I i° I Молчина Л.И. ЭУМК Модели данных и системы управления базами данных8 © 2011 Молчина Л.И. Рис. 1.1. Операции над множествами 4. Свойства операций над множествами. Пусть задан универсум. Тогда для " А,В,С  U выполняются следующие свойства: 1. идемпотентность А  А = А А  А = А 1. коммутативность А  В = В  А А  В = В  А 1. ассоциативность А  (В  С) = (А  В)  С А  (В  С) = (А  В)  С 1. дистрибутивность А  (В  С) = (А  В)  (А  С) А  (В  С) = (А  В)  (А  С) 1. поглощение (А  В) А = А (А  В)  А = А 1. свойство нуля А   = А А   =  1. свойство единицы А  U = U А  U = А 1. инволютивность 1. законы де Моргана Теоретический раздел 9 © 2011 Молчина Л.И. 1. свойства дополнения  1. выражение для разности А \ В = В справедливости перечисленных свойств можно убедиться различными способами. Например, нарисовать диаграммы Эйлера для левой и правой частей равенства и убедиться, что они совпадают, или же провести формальное рассуждение для каждого равенства. Рассмотрим для примера первое равенство: A  А = А. Возьмем произвольный элемент х, принадлежащий левой части равенства, х ° А А. По определению операции объединения  имеем х ° А* х ° А . В любом случае х ° А. Взяв произвольный элемент из множества в левой части равенства, обнаружили, что он принадлежит множеству в правой части. Отсюда по определению включения множеств получаем, что A ° А. Пусть теперь х ° А. Тогда, очевидно, верно х ° А* х ° А. Отсюда по определению операции объединения имеем х ° А  А . Таким образом, А A  А. Следовательно, по определению равенства множеств, А А=А. Аналогичные рассуждения нетрудно провести и для остальных равенств. 5. Упорядоченные пары. Если а и b – объекты, то через (a,b) обозначим упорядоченную пару. Равенство упорядоченных пар определяется следующим образом: (a,b) == (c,d):=a=c&b=d. Вообще говоря, (а,b)  (b,а). ЗАМЕЧАНИЕ Упорядоченные пары можно рассматривать как множества, если определить их так: (а,b):={а,{а,b}}. Упорядоченную последовательность из n элементов х1, х2,…, хn будем обозначать через (х1, х2,…, хn). Круглые скобки используются для того, чтобы указать на порядок, в котором записаны элементы. Такая последовательность называется кортежем длины n или просто n-кой. Элемент х i называется i- той координатой кортежа. 5.2. Прямое произведение множеств. Пусть А и В – два множества. Прямым (декартовым) произведением двух множеств А и В называется множество упорядоченных пар, в котором первый элемент каждой пары принадлежит А, а второй принадлежит В: А * В: = {(а, b) | а  А & b  В}. Молчина Л.И. ЭУМК Модели данных и системы управления базами данных10 © 2011 Молчина Л.И. ЗАМЕЧАНИЕ ––––––––––––––––––––––– Точка на плоскости может быть задана упорядоченной парой координат, то есть двумя точками на координатных осях. Таким образом, R2 = R x R. Метод координат ввел в употребление Рене Декарт (1596-1650), отсюда и название «декартово произведение». Степенью множества А называется его прямое произведение самого на себя. Обозначение: An=A *A… *A. Пример. Пусть А={1,2}, B={3,4}. Тогда, A*B={(1,3), (1,4), (2,3), (2,4)}, B*A={(3,1), (3,2), (4,1), (4,2)}, A*A={(1,1), (1,2), (2,1), (2,2)}. 6. Отношения. Равенство, неравенство, параллельность, перпендикулярность, подобие, а также дружба, родство, соседство – все это примеры отношений. Примеры. 1. Форма выделяет из множества прямых на плоскости такие пары, компоненты которых находятся в отношении параллельности. 2. Если А={2,3,4,5,6,7,8}, то бинарное отношение Р{(x,y) ¦x,y ° A, x делит y и x 3} можно записать в виде Р={(2,2), (2,4), (2,6), (2,8), (3,3), (3,6)}. 3. На множестве (1,2,3,4) задано отношение x>y . Выпишите пары, компоненты которых находятся в отношении R. Бинарные отношения R A*В иногда удобно изображать графически. Пример 4. Показать отношение Р1 = {(a,2), (b,1),(c,2)} между множествами A= {a, b,c} и B={1,2,3}, а также Р2 = {(a,b), (b,b), (c,a)} на множестве А. Пусть А и В – два множества. (Бинарным) отношением R из множества A в множество В называется подмножество прямого произведения А и В: R  А х В. Для бинарных отношений обычно используется инфиксная форма записи: аRb:==(а,b) ° R  Aх B. Если A = В, то говорят, что R есть отношение на множестве А. Пример. Пусть задан универсум U. Тогда ° (принадлежность) – отношение из множества U в множество 2U, а  (включение) и = (равенство) – отношения на 2U . Хорошо известны отношения =, <, , >, V , , определенные на множестве чисел. Введем обобщенное понятие отношения: п-местное (п-арное) отношение R – это множество упорядоченных наборов (кортежей): R A1 х ••• х An = {(a1,a2,...,an) | a1° A1 &...&an° An}. Множества Ai не обязательно различны. Теоретический раздел 11 © 2011 Молчина Л.И. Произведением бинарных отношений Р1  А*В и Р2  В*С или композицией Р1 и Р2 называется множество Р1 ° Р2 {(x,y) | x° A,y° C, и найдется элемент z° B такой, что (x,z) ° Р1 и (z,y)° Р2}. 7. Свойства отношений. Отношения характеризуются наличием у них следующих свойств: 1) отношение называется симметричным, если "а,b° Х из следует bRа; 2) отношение называется антисимметричным, если "а,b° Х из аRb следует, что Ь не находится в отношении R к а; 3) отношение (Х,R) называется рефлексивным, если "а° Х и справедливо аRа; 4) отношение называется антирефлексивным, если "а° Х не выполняется аRа 5) отношение называется транзитивным, если из того, что аRb и bRс, следует аRс. Примеры. 1. Симметричности и антисимметричности: 1. Отношение равенства симметрично на любом множестве 1. Отношение «быть больше» и «быть не меньше» на любом числовом множестве 1. Отношение родства симметрично на любом множестве людей 1. Отношение «быть сестрой» симметрично на любом множестве женщин. 1. Отношение дружбы, как правило, симметрично, а отношение любви, к сожалению, часто бывает несимметричным. 2. Рефлексивности и антирефлексивности: 2. Отношение равенства рефлексивно на любом множестве – каждый предмет равен самому себе. 2. Отношение неравенства антирефлексивно на любом множестве 3. Транзитивности: 3.1. На множестве прямых плоскости отношение параллельности транзитивно, а отношение перпендикулярности нетранзитивно. Наиболее известными отношениями являются отношение эквивалентности и отношение порядка. Отношение называется отношением эквивалентности и обозначается символом «T», если оно рефлексивно (хTх), симметрично хTу–>уTх) и транзитивно (хT у и yTz–>xTz). Примерами отношений эквивалентности являются отношение «жить в одном городе» для множества людей, отношение параллельности прямых и т.п. Отношение эквивалентности на любом множестве М задаёт разбиение его на подмножества, которые в этом случае называются классами эквивалентности. С другой стороны, любое разбиение M/ = {А1,А2,...,Ат} множества М задаёт на этом множестве отношение, которое можно назвать «входить в одно и то же подмножество разбиения». Нетрудно убедиться, что это отношение симметрично, рефлексивно и транзитивно, т.е. является отношением эквивалентности. Молчина Л.И. ЭУМК Модели данных и системы управления базами данных12 © 2011 Молчина Л.И. Отношение называется отношением нестрогого порядка и обозначается символом «», если оно рефлексивно, антисимметрично и транзитивно. Отношение называется отношением строгого порядка и обозначается символом «<», если оно антирефлексивно, антисимметрично и транзитивно. Для числовых множеств отношениями нестрогого порядка являются известные отношения «меньше или равно», «больше или равно», отношениями строго порядка - «меньше» или «больше». Отношения порядка определяют некоторый порядок расположения элементов множества, на котором они заданы, другими словами позволяют сравнивать элементы множества по некоторому признаку. Множество называется упорядоченным, если любые два его элемента сравнимы, и частично упорядоченным в противном случае. 8. Функции и их свойства. Понятие «функции» является одним из основополагающих в математике. В данном случае подразумеваются прежде всего функции, отображающие одно конечное множество объектов в другое конечное множество. Определение функции. Пусть f – отношение из А в В, такое что Такое свойство отношения называется однозначностью, или функциональностью, а само отношение называется функцией из А в В и обозначается следующим образом: или Если то обычно используется префиксная форма записи: Если то а называют аргументом, а b – значением функции. ЗАМЕЧАНИЕ –––––––––––––––––––––––––– Вообще, всякому отношению R из А в можно сопоставить (тотальную) функцию (эта функция называется характеристической функцией отношения полагая: Пусть тогда область определения функции: Теоретический раздел 13 © 2011 Молчина Л.И. область значений функции: Если то функция называется тотальной, а если – частичной. Сужением функции на множество М с А называется функция определяемая следующим образом: Для тотальной функции . Функция называется функцией n аргументов, или n - местной функцией. . 1.1.2 Понятия математической логики Презентация: Лекция - Понятия математической логики 1.1.3 Основные понятия реляционной алгебры Основная идея реляционной алгебры состоит в том, что коль скоро отношения являются множествами, то средства манипулирования отношениями могут базироваться на традиционных теоретико-множественных операциях, дополненных некоторыми специальными операциями, специфичными для баз данных. 1.1.3.1 Основные понятия реляционной алгебры Реляционная алгебра (http://citforum.ru/database/osbd/glava_20.shtml) Основная идея реляционной алгебры состоит в том, что коль скоро отношения являются множествами, то средства манипулирования отношениями могут базироваться на традиционных теоретико-множественных операциях, дополненных некоторыми специальными операциями, специфичными для баз данных. Существует много подходов к определению реляционной алгебры, которые различаются набором операций и способами их интерпретации, но в принципе, более или менее равносильны. Мы опишем немного расширенный начальный вариант алгебры, который был предложен Коддом. В этом варианте набор основных алгебраических операций состоит из восьми операций, которые делятся на два класса - теоретико-множественные операции и специальные реляционные операции. В состав теоретико-множественных операций входят операции: · объединения отношений; Молчина Л.И. ЭУМК Модели данных и системы управления базами данных14 © 2011 Молчина Л.И. · пересечения отношений; · взятия разности отношений; · прямого произведения отношений. Специальные реляционные операции включают: · ограничение отношения; · проекцию отношения; · соединение отношений; · деление отношений. Кроме того, в состав алгебры включается операция присваивания, позволяющая сохранить в базе данных результаты вычисления алгебраических выражений, и операция переименования атрибутов, дающая возможность корректно сформировать заголовок (схему) результирующего отношения. 1.1. Общая интерпретация реляционных операций Если не вдаваться в некоторые тонкости, которые мы рассмотрим в следующих подразделах, то почти все операции предложенного выше набора обладают очевидной и простой интерпретацией. · При выполнении операции объединения двух отношений производится отношение, включающее все кортежи, входящие хотя бы в одно из отношений- операндов. · Операция пересечения двух отношений производит отношение, включающее все кортежи, входящие в оба отношения-операнда. · Отношение, являющееся разностью двух отношений включает все кортежи, входящие в отношение - первый операнд, такие, что ни один из них не входит в отношение, являющееся вторым операндом. · При выполнении прямого произведения двух отношений производится отношение, кортежи которого являются конкатенацией (сцеплением) кортежей первого и второго операндов. · Результатом ограничения отношения по некоторому условию является отношение, включающее кортежи отношения-операнда, удовлетворяющее этому условию. Теоретический раздел 15 © 2011 Молчина Л.И. · При выполнении проекции отношения на заданный набор его атрибутов производится отношение, кортежи которого производятся путем взятия соответствующих значений из кортежей отношения-операнда. · При соединении двух отношений по некоторому условию образуется результирующее отношение, кортежи которого являются конкатенацией кортежей первого и второго отношений и удовлетворяют этому условию. · У операции реляционного деления два операнда - бинарное и унарное отношения. Результирующее отношение состоит из одноатрибутных кортежей, включающих значения первого атрибута кортежей первого операнда таких, что множество значений второго атрибута (при фиксированном значении первого атрибута) совпадает со множеством значений второго операнда. · Операция переименования производит отношение, тело которого совпадает с телом операнда, но имена атрибутов изменены. · Операция присваивания позволяет сохранить результат вычисления реляционного выражения в существующем отношении БД. Поскольку результатом любой реляционной операции (кроме операции присваивания) является некоторое отношение, можно образовывать реляционные выражения, в которых вместо отношения-операнда некоторой реляционной операции находится вложенное реляционное выражение. 1.2. Замкнутость реляционной алгебры и операция переименования Как мы говорили в предыдущей лекции, каждое отношение характеризуется схемой (или заголовком) и набором кортежей (или телом). Поэтому, если действительно желать иметь алгебру, операции которой замкнуты относительно понятия отношения, то каждая операция должна производить отношение в полном смысле, т.е. оно должно обладать и телом, и заголовком. Только в этом случае будет действительно возможно строить вложенные выражения. Заголовок отношения представляет собой множество пар <имя-атрибута, имя-домена>. Если посмотреть на общий обзор реляционных операций, приведенный в предыдущем подразделе, то видно, что домены атрибутов результирующего отношения однозначно определяются доменами отношений-операндов. Однако с именами атрибутов результата не всегда все так просто. Например, представим себе, что у отношений-операндов операции прямого произведения имеются одноименные атрибуты с одинаковыми доменами. Каким был бы заголовок результирующего отношения? Поскольку это множество, в нем не должны содержаться одинаковые элементы. Но и потерять атрибут в результате недопустимо. А это значит, что в этом случае вообще невозможно корректно выполнить операцию Молчина Л.И. ЭУМК Модели данных и системы управления базами данных16 © 2011 Молчина Л.И. прямого произведения. Аналогичные проблемы могут возникать и в случаях других двуместных операций. Для их разрешения в состав операций реляционной алгебры вводится операция переименования. Ее следует применять в любом случае, когда возникает конфликт именования атрибутов в отношениях - операндах одной реляционной операции. Тогда к одному из операндов сначала применяется операция переименования, а затем основная операция выполняется уже безо всяких проблем. В дальнейшем изложении мы будем предполагать применение операции переименования во всех конфликтных случаях. 1.3. Особенности теоретико-множественных операций реляционной алгебры Хотя в основе теоретико-множественной части реляционной алгебры лежит классическая теория множеств, соответствующие операции реляционной алгебры обладают некоторыми особенностями. Начнем с операции объединения (все, что будет говориться по поводу объединения, переносится на операции пересечения и взятия разности). Смысл операции объединения в реляционной алгебре в целом остается теоретико-множественным. Но если в теории множеств операция объединения осмысленна для любых двух множеств- операндов, то в случае реляционной алгебры результатом операции объединения должно являться отношение. Если допустить в реляционной алгебре возможность теоретико-множественного объединения произвольных двух отношений (с разными схемами), то, конечно, результатом операции будет множество, но множество разнотипных кортежей, т.е. не отношение. Если исходить из требования замкнутости реляционной алгебры относительно понятия отношения, то такая операция объединения является бессмысленной. Все эти соображения приводят к появлению понятия совместимости отношений по объединению: два отношения совместимы по объединению в том и только в том случае, когда обладают одинаковыми заголовками. Более точно, это означает, что в заголовках обоих отношений содержится один и тот же набор имен атрибутов, и одноименные атрибуты определены на одном и том же домене. Если два отношения совместимы по объединению, то при обычном выполнении над ними операций объединения, пересечения и взятия разности результатом операции является отношение с корректно определенным заголовком, совпадающим с заголовком каждого из отношений-операндов. Напомним, что если два отношения "почти" совместимы по объединению, т.е. совместимы во всем, кроме имен атрибутов, то до выполнения операции типа соединения эти отношения можно сделать полностью совместимыми по объединению путем применения операции переименования. Заметим, что включение в состав операций реляционной алгебры трех операций Теоретический раздел 17 © 2011 Молчина Л.И. объединения, пересечения и взятия разности является очевидно избыточным, поскольку известно, что любая из этих операций выражается через две других. Тем не менее, Кодд в свое время решил включить все три операции, исходя из интуитивных потребностей потенциального пользователя системы реляционных БД, далекого от математики. Другие проблемы связаны с операцией взятия прямого произведения двух отношений. В теории множеств прямое произведение может быть получено для любых двух множеств, и элементами результирующего множества являются пары, составленные из элементов первого и второго множеств. Поскольку отношения являются множествами, то и для любых двух отношений возможно получение прямого произведения. Но результат не будет отношением! Элементами результата будут являться не кортежи, а пары кортежей. Поэтому в реляционной алгебре используется специализированная форма операции взятия прямого произведения - расширенное прямое произведение отношений. При взятии расширенного прямого произведения двух отношений элементом результирующего отношения является кортеж, являющийся конкатенацией (или слиянием) одного кортежа первого отношения и одного кортежа второго отношения. Но теперь возникает второй вопрос - как получить корректно сформированный заголовок отношения-результата? Очевидно, что проблемой может быть именование атрибутов результирующего отношения, если отношения-операнды обладают одноименными атрибутами. Эти соображения приводят к появлению понятия совместимости по взятию расширенного прямого произведения. Два отношения совместимы по взятию прямого произведения в том и только в том случае, если множества имен атрибутов этих отношений не пересекаются. Любые два отношения могут быть сделаны совместимыми по взятию прямого произведения путем применения операции переименования к одному из этих отношений. Следует заметить, что операция взятия прямого произведения не является слишком осмысленной на практике. Во-первых, мощность ее результата очень велика даже при допустимых мощностях операндов, а во-вторых, результат операции не более информативен, чем взятые в совокупности операнды. Как мы увидим немного ниже, основной смысл включения операции расширенного прямого произведения в состав реляционной алгебры состоит в том, что на ее основе определяется действительно полезная операция соединения. По поводу теоретико-множественных операций реляционной алгебры следует еще заметить, что все четыре операции являются ассоциативными. Т. е., если обозначить через OP любую из четырех операций, то (A OP B) OP C = A (B OP C), и следовательно, без введения двусмысленности можно писать A OP B OP C (A, B и C - отношения, обладающие свойствами, требуемыми для корректного выполнения соответствующей Молчина Л.И. ЭУМК Модели данных и системы управления базами данных18 © 2011 Молчина Л.И. операции). Все операции, кроме взятия разности, являются коммутативными, т.е. A OP B = B OP A. 1.4. Специальные реляционные операции В этом подразделе мы несколько подробнее рассмотрим специальные реляционные операции реляционной алгебры: ограничение, проекция, соединение и деление. Операция ограничения Операция ограничения требует наличия двух операндов: ограничиваемого отношения и простого условия ограничения. Простое условие ограничения может иметь либо вид (a comp-op b), где а и b - имена атрибутов ограничиваемого отношения, для которых осмысленна операция сравнения comp-op, либо вид (a comp-op const), где a - имя атрибута ограничиваемого отношения, а const - литерально заданная константа. В результате выполнения операции ограничения производится отношение, заголовок которого совпадает с заголовком отношения-операнда, а в тело входят те кортежи отношения-операнда, для которых значением условия ограничения является true. Пусть UNION обозначает операцию объединения, INTERSECT - операцию пересечения, а MINUS - операцию взятия разности. Для обозначения операции ограничения будем использовать конструкцию A WHERE comp, где A - ограничиваемое отношение, а comp - простое условие сравнения. Пусть comp1 и comp2 - два простых условия ограничения. Тогда по определению: · A WHERE comp1 AND comp2 обозначает то же самое, что и (A WHERE comp1) INTERSECT (A WHERE comp2) · A WHERE comp1 OR comp2 обозначает то же самое, что и (A WHERE comp1) UNION (A WHERE comp2) · A WHERE NOT comp1 обозначает то же самое, что и A MINUS (A WHERE comp1) С использованием этих определений можно использовать операции ограничения, в которых условием ограничения является произвольное булевское выражение, составленное из простых условий с использованием логических связок AND, OR, NOT и скобок. На интуитивном уровне операцию ограничения лучше всего представлять как взятие некоторой "горизонтальной" вырезки из отношения-операнда. Операция взятия проекции Операция взятия проекции также требует наличия двух операндов - проецируемого Теоретический раздел 19 © 2011 Молчина Л.И. отношения A и списка имен атрибутов, входящих в заголовок отношения A. Результатом проекции отношения A по списку атрибутов a1, a2, ..., an является отношение, с заголовком, определяемым множеством атрибутов a1, a2, ..., an, и с телом, состоящим из кортежей вида таких, что в отношении A имеется кортеж, атрибут a1 которого имеет значение v1, атрибут a2 имеет значение v2, ..., атрибут an имеет значение vn. Тем самым, при выполнении операции проекции выделяется "вертикальная" вырезка отношения-операнда с естественным уничтожением потенциально возникающих кортежей-дубликатов. Операция соединения отношений Общая операция соединения (называемая также соединением по условию) требует наличия двух операндов - соединяемых отношений и третьего операнда - простого условия. Пусть соединяются отношения A и B. Как и в случае операции ограничения, условие соединения comp имеет вид либо (a comp-op b), либо (a comp-op const), где a и b - имена атрибутов отношений A и B, const - литерально заданная константа, а comp-op - допустимая в данном контексте операция сравнения. Тогда по определению результатом операции сравнения является отношение, получаемое путем выполнения операции ограничения по условию comp прямого произведения отношений A и B. Если внимательно осмыслить это определение, то станет ясно, что в общем случае применение условия соединения существенно уменьшит мощность результата промежуточного прямого произведения отношений-операндов только в том случае, когда условие соединения имеет вид (a comp-op b), где a и b - имена атрибутов разных отношений-операндов. Поэтому на практике обычно считают реальными операциями соединения именно те операции, которые основываются на условии соединения приведенного вида. Хотя операция соединение в нашей интерпретации не является примитивной (поскольку она определяется с использованием прямого произведения и проекции), в силу особой практической важности она включается в базовый набор операций реляционной алгебры. Заметим также, что в практических реализациях соединение обычно не выполняется именно как ограничение прямого произведения. Имеются более эффективные алгоритмы, гарантирующие получение такого же результата. Имеется важный частный случай соединения - эквисоединение и простое, но важное расширение операции эквисоединения - естественное соединение. Операция соединения называется операцией эквисоединения, если условие соединения имеет вид (a = b), где a и b - атрибуты разных операндов соединения. Этот случай важен потому, что (a) он часто встречается на практике, и (b) для него существуют эффективные Молчина Л.И. ЭУМК Модели данных и системы управления базами данных20 © 2011 Молчина Л.И. алгоритмы реализации. Операция естественного соединения применяется к паре отношений A и B, обладающих (возможно составным) общим атрибутом c (т.е. атрибутом с одним и тем же именем и определенным на одном и том же домене). Пусть ab обозначает объединение заголовков отношений A и B. Тогда естественное соединение A и B - это спроектированный на ab результат эквисоединения A и B по A/c и BBC. Если вспомнить введенное нами в конце предыдущей главы определение внешнего ключа отношения, то должно стать понятно, что основной смысл операции естественного соединения - возможность восстановления сложной сущности, декомпозированной по причине требования первой нормальной формы. Операция естественного соединения не включается прямо в состав набора операций реляционной алгебры, но она имеет очень важное практическое значение. Операция деления отношений Эта операция наименее очевидна из всех операций реляционной алгебры и поэтому нуждается в более подробном объяснении. Пусть заданы два отношения - A с заголовком {a1, a2, ..., an, b1, b2, ..., bm} и B с заголовком {b1, b2, ..., bm}. Будем считать, что атрибут bi отношения A и атрибут bi отношения B не только обладают одним и тем же именем, но и определены на одном и том же домене. Назовем множество атрибутов {aj} составным атрибутом a, а множество атрибутов {bj} - составным атрибутом b. После этого будем говорить о реляционном делении бинарного отношения A(a,b) на унарное отношение B(b). Результатом деления A на B является унарное отношение C(a), состоящее из кортежей v таких, что в отношении A имеются кортежи такие, что множество значений {w} включает множество значений атрибута b в отношении B. Предположим, что в базе данных сотрудников поддерживаются два отношения: СОТРУДНИКИ ( ИМЯ, ОТД_НОМЕР ) и ИМЕНА ( ИМЯ ), причем унарное отношение ИМЕНА содержит все фамилии, которыми обладают сотрудники организации. Тогда после выполнения операции реляционного деления отношения СОТРУДНИКИ на отношение ИМЕНА будет получено унарное отношение, содержащее номера отделов, сотрудники которых обладают всеми возможными в этой организации именами. 2. Реляционное исчисление Предположим, что мы работаем с базой данных, обладающей схемой СОТРУДНИКИ (СОТР_НОМ, СОТР_ИМЯ, СОТР_ЗАРП, ОТД_НОМ) и ОТДЕЛЫ (ОТД_НОМ, ОТД_КОЛ, ОТД_НАЧ), и хотим узнать имена и номера сотрудников, являющихся начальниками отделов с количеством сотрудников больше 50. Если бы для формулировки такого запроса использовалась реляционная алгебра, то мы Теоретический раздел 21 © 2011 Молчина Л.И. получили бы алгебраическое выражение, которое читалось бы, например, следующим образом: · выполнить соединение отношений СОТРУДНИКИ и ОТДЕЛЫ по условию СОТР_НОМ = ОТД_НАЧ; · ограничить полученное отношение по условию ОТД_КОЛ > 50; · спроецировать результат предыдущей операции на атрибут СОТР_ИМЯ, СОТР_НОМ. Мы четко сформулировали последовательность шагов выполнения запроса, каждый из которых соответствует одной реляционной операции. Если же сформулировать тот же запрос с использованием реляционного исчисления, которому посвящается этот раздел, то мы получили бы формулу, которую можно было бы прочитать, например, следующим образом: Выдать СОТР_ИМЯ и СОТР_НОМ для сотрудников таких, что существует отдел с таким же значением ОТД_НАЧ и значением ОТД_КОЛ большим 50. Во второй формулировке мы указали лишь характеристики результирующего отношения, но ничего не сказали о способе его формирования. В этом случае система должна сама решить, какие операции и в каком порядке нужно выполнить над отношениями СОТРУДНИКИ и ОТДЕЛЫ. Обычно говорят, что алгебраическая формулировка является процедурной, т.е. задающей правила выполнения запроса, а логическая - описательной (или декларативной), поскольку она всего лишь описывает свойства желаемого результата. Как мы указывали в начале лекции, на самом деле эти два механизма эквивалентны и существуют не очень сложные правила преобразования одного формализма в другой. 2.1. Кортежные переменные и правильно построенные формулы Реляционное исчисление является прикладной ветвью формального механизма исчисления предикатов первого порядка. Базисными понятиями исчисления являются понятие переменной с определенной для нее областью допустимых значений и понятие правильно построенной формулы, опирающейся на переменные, предикаты и кванторы. В зависимости от того, что является областью определения переменной, различаются исчисление кортежей и исчисление доменов. В исчислении кортежей областями определения переменных являются отношения базы данных, т.е. допустимым значением каждой переменной является кортеж некоторого отношения. В исчислении доменов областями определения переменных являются домены, на которых определены атрибуты отношений базы данных, т.е. допустимым значением каждой переменной является значение некоторого домена. Мы рассмотрим более подробно исчисление кортежей, а в конце лекции коротко опишем особенности исчисления Молчина Л.И. ЭУМК Модели данных и системы управления базами данных22 © 2011 Молчина Л.И. доменов. В отличие от раздела, посвященного реляционной алгебре, в этом разделе нам не удастся избежать использования некоторого конкретного синтаксиса, который мы, тем не менее, формально определять не будем. Необходимые синтаксические конструкции будут вводиться по мере необходимости. В совокупности, используемый синтаксис близок, но не полностью совпадает с синтаксисом языка баз данных QUEL, который долгое время являлся основным языком СУБД Ingres. Для определения кортежной переменной используется оператор RANGE. Например, для того, чтобы определить переменную СОТРУДНИК, областью определения которой является отношение СОТРУДНИКИ, нужно употребить конструкцию RANGE СОТРУДНИК IS СОТРУДНИКИ Как мы уже говорили, из этого определения следует, что в любой момент времени переменная СОТРУДНИК представляет некоторый кортеж отношения СОТРУДНИКИ. При использовании кортежных переменных в формулах можно ссылаться на значение атрибута переменной (это аналогично тому, как, например, при программировании на языке Си можно сослаться на значение поля структурной переменной). Например, для того, чтобы сослаться на значение атрибута СОТР_ИМЯ переменной СОТРУДНИК, нужно употребить конструкцию СОТРУДНИК.СОТР_ИМЯ. Правильно построенные формулы (WFF - Well-Formed Formula) служат для выражения условий, накладываемых на кортежные переменные. Основой WFF являются простые сравнения (comparison), представляющие собой операции сравнения скалярных значений (значений атрибутов переменных или литерально заданных констант). Например, конструкция "СОТРУДНИК.СОТР_НОМ = 140" является простым сравнением. По определению, простое сравнение является WFF, а WFF, заключенная в круглые скобки, является простым сравнением. Более сложные варианты WFF строятся с помощью логических связок NOT, AND, OR и IF ... THEN. Так, если form - WFF, а comp - простое сравнение, то NOT form, comp AND form, comp OR form и IF comp THEN form являются WFF. Наконец, допускается построение WFF с помощью кванторов. Если form - это WFF, в которой участвует переменная var, то конструкции EXISTS var (form) и FORALL var (form) представляют wff. Переменные, входящие в WFF, могут быть свободными или связанными. Все переменные, входящие в WFF, при построении которой не использовались кванторы, являются свободными. Фактически, это означает, что если для какого-то набора значений свободных кортежных переменных при вычислении WFF получено значение true, то эти значения кортежных переменных могут входить в результирующее отношение. Если же имя переменной использовано сразу после квантора при построении WFF вида EXISTS var (form) или FORALL var (form), то в этой WFF и во Теоретический раздел 23 © 2011 Молчина Л.И. всех WFF, построенных с ее участием, var - это связанная переменная. Это означает, что такая переменная не видна за пределами минимальной WFF, связавшей эту переменную. При вычислении значения такой WFF используется не одно значение связанной переменной, а вся ее область определения. Пусть СОТР1 и СОТР2 - две кортежные переменные, определенные на отношении СОТРУДНИКИ. Тогда, WFF EXISTS СОТР2 (СОТР1.СОТР_ЗАРП > СОТР2. СОТР_ЗАРП) для текущего кортежа переменной СОТР1 принимает значение true в том и только в том случае, если во всем отношении СОТРУДНИКИ найдется кортеж (связанный с переменной СОТР2) такой, что значение его атрибута СОТР_ЗАРП удовлетворяет внутреннему условию сравнения. WFF FORALL СОТР2 (СОТР1. СОТР_ЗАРП > СОТР2.СОТР_ЗАРП) для текущего кортежа переменной СОТР1 принимает значение true в том и только в том случае, если для всех кортежей отношения СОТРУДНИКИ (связанных с переменной СОТР2) значения атрибута СОТР_ЗАРП удовлетворяют условию сравнения. На самом деле, правильнее говорить не о свободных и связанных переменных, а о свободных и связанных вхождениях переменных. Легко видеть, что если переменная var является связанной в WFF form, то во всех WFF, включающих данную, может использоваться имя переменной var, которая может быть свободной или связанной, но в любом случае не имеет никакого отношения к вхождению переменной var в WFF form. Вот пример: EXISTS СОТР2 (СОТР1.СОТР_ОТД_НОМ = СОТР2.СОТР_ОТД_НОМ) AND FORALL СОТР2 (СОТР1.СОТР_ЗАРП > СОТР2.СОТР_ЗАРП) Здесь мы имеем два связанных вхождения переменной СОТР2 с совершенно разным смыслом. 2.2. Целевые списки и выражения реляционного исчисления Итак, WFF обеспечивают средства формулировки условия выборки из отношений БД. Чтобы можно было использовать исчисление для реальной работы с БД, требуется еще один компонент, который определяет набор и имена столбцов результирующего отношения. Этот компонент называется целевым списком (target_list). Целевой список строится из целевых элементов, каждый из которых может иметь следующий вид: · var.attr, где var - имя свободной переменной соответствующей WFF, а attr - имя атрибута отношения, на котором определена переменная var; · var, что эквивалентно наличию подсписка var.attr1, var.attr2, ..., var.attrn, где attr1, attr2, ..., attrn включает имена всех атрибутов определяющего отношения; · new_name = var.attr; new_name - новое имя соответствующего атрибута Молчина Л.И. ЭУМК Модели данных и системы управления базами данных24 © 2011 Молчина Л.И. результирующего отношения. Последний вариант требуется в тех случаях, когда в WFF используются несколько свободных переменных с одинаковой областью определения. Выражением реляционного исчисления кортежей называется конструкция вида target_list WHERE wff. Значением выражения является отношение, тело которого определяется WFF, а набор атрибутов и их имена - целевым списком. 2.3. Реляционное исчисление доменов В исчислении доменов областью определения переменных являются не отношения, а домены. Применительно к базе данных СОТРУДНИКИ-ОТДЕЛЫ можно говорить, например, о доменных переменных ИМЯ (значения - допустимые имена) или НОСОТР (значения - допустимые номера сотрудников). Основным формальным отличием исчисления доменов от исчисления кортежей является наличие дополнительного набора предикатов, позволяющих выражать так называемые условия членства. Если R - это n-арное отношение с атрибутами a1, a2, ..., a n, то условие членства имеет вид R (ai1:vi1, ai2:vi2, ..., aim:vim) (m <= n), где vij - это либо литерально задаваемая константа, либо имя кортежной переменной. Условие членства принимает значение true в том и только в том случае, если в отношении R существует кортеж, содержащий указанные значения указанных атрибутов. Если vij - константа, то на атрибут aij задается жесткое условие, не зависящее от текущих значений доменных переменных; если же vij - имя доменной переменной, то условие членства может принимать разные значения при разных значениях этой переменной. Во всех остальных отношениях формулы и выражения исчисления доменов выглядят похожими на формулы и выражения исчисления кортежей. В частности, конечно, различаются свободные и связанные вхождения доменных переменных. Для примера сформулируем с использованием исчисления доменов запрос "Выдать номера и имена сотрудников, не получающих минимальную заработную плату" (будем считать для простоты, что мы определили доменные переменные, имена которых совпадают с именами атрибутов отношения СОТРУДНИКИ, а в случае, когда требуется несколько доменных переменных, определенных на одном домене, мы будем добавлять в конце имени цифры): СОТР_НОМ, СОТР_ИМЯ WHERE EXISTS СОТР_ЗАРП1 (СОТРУДНИКИ (СОТР_ЗАРП1) AND Теоретический раздел 25 © 2011 Молчина Л.И. СОТРУДНИКИ (СОТР_НОМ, СОТР_ИМЯ, СОТР_ЗАРП) AND СОТР_ЗАРП > СОТР_ЗАРП1) Реляционное исчисление доменов является основой большинства языков запросов, основанных на использовании форм. В частности, на этом исчислении базировался известный язык Query-by-Example, который был первым (и наиболее интересным) языком в семействе языков, основанных на табличных формах. Литература и источники 1. Кузнецов С.Н., Центр Информационных Технологий. "Основы современных баз данных" 2. Дейт К. Введение в системы баз данных. М., 1998. 3. Пушников А.Ю. Введение в системы управления базами данных. Часть 1. Реляционная модель данных: Учебное пособие/Изд-е Башкирского ун-та. - Уфа, 1999. - 108 с. , а также CITFORUM.RU 4. Пушников А.Ю. Введение в системы управления базами данных. Часть 2. Нормальные формы отношений и транзакции: Учебное пособие/Изд-е Башкирского ун-та. - Уфа, 1999. - 138 с. а также CITFORUM.RU . 1.1.4 Автоматизация обработки информации Вопросы 1. Понятие экономической и автоматизированной информационной системы. 2. Понятие БД и СУБД. функциональные возможности и классификация СУБД. 1.1.4.1 Понятие экономической и автоматизированной информационной системы. Информационная система (ИС) – коммуникационная система по сбору, передаче, переработке информации об объекте, снабжающая работников различного ранга информацией для реализации функций управления. В зависимости от степени (уровня) автоматизации выделяют ручные, автоматизированные и автоматические информационные системы. Ручные ИС характеризуются тем, что все операции по переработке информации выполняются человеком. Автоматизированные ИС – часть функций (подсистем) управления или обработки данных осуществляется автоматически, а часть – Молчина Л.И. ЭУМК Модели данных и системы управления базами данных26 © 2011 Молчина Л.И. человеком. Автоматические ИС – все функций управления и обработки данных осуществляется техническими средствами без участия человека. Экономическая ИС (ЭИС) – система для хранения, поиска и выдачи экономической информации по запросам пользователей. Классификация ЭИС По уровню применения и административному делению различают ЭИС: 1. государства; 2. области; 3. района; 4. города; 5. предприятия; 6. подразделения. По сфере применения – ЭИС: 1. банковские ИС; 2. ИС фондового рынка; 3. налоговые ИС; 4. страховые ИС; 5. статистические ИС; 6. ИС промышленных предприятий и организаций (особое место по значимости и распространенности среди них занимают бухгалтерские ИС). ЭИС [систему] можно разделить на 2 части: обеспечивающую и функциональную. Обеспечивающая часть ЭИС (включает в себя следующие виды обеспечения: информационное, техническое, программное, правовое и другие). 1.1.4.2 Понятие БД и СУБД. функциональные возможности и классификация СУБД. База данных (БД) – это организованная структура, предназначенная для Теоретический раздел 27 © 2011 Молчина Л.И. хранения информации. Система управления базами данных (СУБД) – это программа (или комплекс программ), предназначенная для создания структуры новой базы данных, наполнения ее содержимым, редактирования содержимого и визуализации информации. Под визуализацией информации базы понимается отбор отображаемых данных по заданному критерию, их упорядочение, оформление и выдача на устройства вывода или передача по каналам связи. Функциональные возможности СУБД СУБД позволяют осуществлять следующие функции: · Создавать БД в виде файла (или файлов) на внешнем носителе; · Загружать, редактировать БД; · Осуществлять поиск информации по запросу; · Упорядочивать информацию в БД; · Выводить информацию из БД; · Формировать отчёты и другие объекты на основании БД; · Обеспечивать защиту данных от разрушения и несанкционированного доступа; · Сохранять имеющиеся данные при изменении структуры БД; · Сохранять имеющиеся программы при изменении структуры БД. Классификация СУБД 1. По уровню использования СУБД делят на: · Профессиональные (промышленные); · Настольные (персональные). Профессиональные СУБД представляют собой программную основу для разработки автоматизированных систем управления (АСУ) крупными экономическими объектами. На основе этих СУБД создаются АСУ банков, крупных предприятий, отраслей. К этой группе относятся Oracle, DB2, Informix, Progress, Ingress. Молчина Л.И. ЭУМК Модели данных и системы управления базами данных28 © 2011 Молчина Л.И. Персональные СУБД ориентированы на решение задач отдельного пользователя или небольшой группы пользователей, и предназначены для использования на ПК. Основными характеристиками настольных СУБД являются: - простота эксплуатации; - невысокие требования к аппаратным ресурсам компьютера. К ним относятся: Dbase, FoxBase, FoxPro, Clipper, MS Access, Approach. 2. По степени универсальности различают СУБД: · Общего назначения; · Специального назначения; СУБД общего назначения не имеют ориентации на какую-то конкретную область. Они обладают средствами настройки на конкретную БД. Однако в некоторых предметных областях СУБД общего назначения не позволяют эффективно организовать работу с конкретной БД. В таких случаях создаются специализированные СУБД для конкретного применения, например, СУБД Imbase, используемая для автоматизации проектных и конструкторских работ. 3. По типу моделей данных: · иерархическая (IMS фирмы IBM); · сетевая (IDS фирмы General Electric) · реляционные (MS Access). 4. По способу распределения данных: · централизованные; · децентрализованные; · смешанные. Централизованная организация БД является самой простой. На одном компьютере – сервере находится единственная БД. Все операции с БД обеспечиваются этим сервером. Децентрализованная организация БД предполагает разбиение БД на несколько Теоретический раздел 29 © 2011 Молчина Л.И. физически распределенных БД. Каждый клиент пользуется своей БД, которая может быть либо копией общей БД, либо ее частью (используется дублирование). Возможна смешанная организация, которая объединяет два этих способа. Концепции сетевой обработки данных файл-сервер и клиент-сервер Концепция файл-сервер предполагает наличие компьютера, выделенного под файловый сервер, на котором находится сетевая ОС и централизованно хранимые файлы. Для этой архитектуры характерен коллективный доступ к общей базе данных на файловом сервере. При обновлении файла одним из пользователей, он блокируется для доступа другим пользователям. Запрошенные данные передаются по сети с файлового сервера на рабочие станции, где их обработка выполняется средствами СУБД. В концепции файл-сервер вся тяжесть выполнения запросов к БД и управления целостностью БД ложится на СУБД пользователя. Концепция клиент-сервер подразумевает разделение функций обработки данных между клиентом – рабочей станцией и машиной-сервером баз данных, где обработку осуществляет установленная там СУБД. Запрос на обработку данных выдается клиентом и передается по сети на сервер баз данных, где осуществляется поиск. Обработанные данные передаются по сети от сервера к клиенту. (Спецификой архитектуры клиент-сервер является использование языка SQL для запросов к БД, что обеспечивает работу с общими данными из разнотипных приложений клиентов сети.) 1.1.5 Проектирование БД. Организация связей между объектами БД Вопросы 1. Требования, предъявляемые к БД 2. Модели данных. Компьютерная реляционная БД 3. Этапы проектирования баз данных Молчина Л.И. ЭУМК Модели данных и системы управления базами данных30 © 2011 Молчина Л.И. 1.1.5.1 Требования, предъявляемые к БД Первые автоматизированные информационные системы (АИС) появились в конце 50-х начале 60-х годов. Данные в первых АИС хранились в файлах последовательного доступа. Последовательному доступу присущи следующие недостатки: · низкая скорость обработки; · недостаточная возможность управления данными; · большой объем дублирования данных; · ограниченные возможности по контролю данных; · жесткая связь данных и прикладных программ. БД представляет собой поименованную совокупность данных, отражающую состояние объекта или множества объектов, их свойств и взаимоотношений. Требования, предъявляемые к БД: 1. Обеспечение независимости программ от данных (это позволяет сохранять существующие программы при модификации БД). 2. Возможность модификации структуры БД без повторного ввода имеющихся данных. 3. Отсутствие избыточности (минимальная избыточность), что позволяет однократно вводить и корректировать информацию. 4. Обеспечение непротиворечивости данных и целостности БД. Это значит, что обработка данных должна быть построена таким образом, чтобы в случае возникновения физических сбоев или попыток неверного изменения данных или структуры данных система смогла восстановить и согласовать данные без потерь. Важнейшим механизмом поддержания целостности БД является транзакция. Транзакция – это совокупность операций с БД, которая должна быть выполнена до конца, чтобы БД не оказалась в противоречивом состоянии (например, изменение в БД при переводе студента на следующий курс, удалении из БД сведений о сотруднике при увольнении). 5. Возможность многоаспектного доступа, т.е. различные пользователи могут Теоретический раздел 31 © 2011 Молчина Л.И. представлять себе данные, хранимые в БД, по-разному, в соответствии с собственными потребностями. 6. Возможность осуществлять всевозможные выборки данных и использовать их различными приложениями пользователя. 7. Разграничение доступа к данным и защита от несанкционированного доступа. 8. Наличие языка запроса высокого уровня, ориентируемого на конечного пользователя-непрограммиста. 1.1.5.2 Модели данных. Компьютерная реляционная БД Модель данных – это совокупность взаимосвязанных структур данных и операций над этими структурами. По способу установления связей между данными наиболее распространёнными являются иерархическая, сетевая и реляционная модели данных. Иерархическая модель представляется в виде дерево-графа, где возможны только односторонние связи от старших вершин к младшим, т.е. любой объект может подчиняться только одному объекту старшего уровня. Достоинство этой модели – простота доступа к данным. Недостаток – жесткая фиксированность взаимосвязей между элементами данных. Сетевая модель обеспечивает прямой доступ к любому объекту (каждый элемент связан со всеми остальными). Сетевая БД состоит из набора записей и связей между ними. Запись в сетевой модели в отличие от иерархической может иметь множество как подчиненных ей записей, так и записей, которым она подчинена. По сравнению с иерархичной моделью сетевая обладает большей гибкостью, но ее недостаток – трудности реализации этой модели в полном объёме. Реляционная модель (relation – отношение) считается простейшей и наиболее привычной формой представления данных в виде таблиц. Под реляционным отношением (таблицей) будем понимать двумерный массив типа объекты-признаки, обладающий следующими свойствами: · все столбцы таблицы однородны; · все столбцы таблицы имеют уникальные имена: Молчина Л.И. ЭУМК Модели данных и системы управления базами данных32 © 2011 Молчина Л.И. · в таблице нет одинаковых строк; · все строки таблицы имеют одну и ту же структуру, т.е. одно и то же количество атрибутов с соответственно одинаковыми именами; · в операциях с таблицами строки и столбцы могут просматриваться в любом порядке без относительно к их информационному содержанию и смыслу. Столбец таблицы со значениями соответствующего атрибута называется полем, а строка со значением разных атрибутов называется записью. Одно или несколько полей, значения которых однозначно идентифицируют строку таблицы, является ключом таблицы (или первичным ключом). Первичный ключ называется простым, если он состоит из одного поля, или составным, если он состоит из нескольких полей. Кроме первичных ключей в таблицах могут присутствовать вторичные ключи. Вторичный ключ (еще его называют индекс) – это такой ключ, значения которого могут повторяться в разных строках (записях). В отличие от первичного ключа, по которому всегда отыскивается только одна строка, по вторичному ключу может отыскиваться группа строк с одинаковым значениям вторичного ключа. Типы связи между таблицами В реляционных БД поддерживается четыре типа отношений (связей) между таблицами: 1. Отношение один-к-одному означает, что каждая запись в одной таблице соответствует одной записи в другой таблице. (Связь уникальна как в одном направлении, так и в обратном направлении.) 2. Отношение один-ко-многим означает, что каждой записи в одной таблице соответствует несколько записей в другой таблице. (В одном направлении связь не является уникальной, а в обратном направлении – уникальна.) 3. Отношение многие-к-одному означает, что нескольким записям в одной таблице соответствует одна запись в другой таблице. (В одном направлении связь уникальна, а в обратном направлении – нет.) 4. Отношение многие-ко-многим означает, что нескольким записям в одной Теоретический раздел 33 © 2011 Молчина Л.И. таблице соответствует несколько записей в другой таблице и наоборот. (Связь не является уникальной в обоих направлениях.) Нормализация баз данных При проектировании реляционной БД необходимо решить вопрос о наиболее эффективной структуре данных. Основные цели, которые при этом преследуются: · обеспечить быстрый доступ к данным в таблицах; · исключить ненужное повторение данных, которое может являться причиной ошибок при вводе и нерационального использования дискового пространства компьютера; · обеспечить целостность данных таким образом, чтобы при изменении одних объектов автоматически происходило соответствующее изменение связанных с ними объектов. Процесс уменьшения избыточности информации в БД называется нормализацией. Таблица в первой нормальной форме должна удовлетворять следующим требованиям: 1. Таблица не должна иметь повторяющихся записей. 2. В таблице не должно быть повторяющихся полей или групп полей. Таблица во второй нормальной форме должна удовлетворять следующим требованиям: 1. Она должна находиться в первой нормальной форме. 2. Любое ключевое поле однозначно идентифицируется полным набором ключевых полей. Таблица в третей нормальной форме должна удовлетворять следующим требованиям: 1. Она должна находиться во второй нормальной форме. 2. Ни одно из неключевых полей таблицы не идентифицируется с помощью другого неключевого поля Молчина Л.И. ЭУМК Модели данных и системы управления базами данных34 © 2011 Молчина Л.И. 1.1.5.3 Этапы проектирования баз данных Проектирование базы данных можно разбить на три этапа – концептуальное, логическое и физическое. 1. На этапе концептуального проектирования осуществляется сбор, анализ и упорядочивание требований к данным, построение модели данных. Концептуальная модель создаётся как обобщение пользовательских представлений и включает в себя совокупность всех данных и требований к ним. 2. Этап логического проектирования включает в себя выбор конкретной модели в СУБД и отображение концептуального представления в логическую модель, основанную на структурах, характерных для выбранной СУБД. Для реляционной БД этот этап включает разработку структуры записей данных, организацию их в таблицы, определение связей между таблицами и полей для реализации этих связей. На этом же этапе разрабатывается словарь данных, в который включается информация о базе в целом, обо всех ее таблицах, полях, ключах, а также используемых кодах. 3. На этапе физического проектирования логическая модель реализуется средствами выбранной СУБД: создаются объекты БД и связи между ними. На этом же этапе выбираются физические устройства для размещения БД и ее копий. 1.1.6 Общая характеристика СУБД MS Access Вопросы 1. Общая характеристика СУБД MS Access 2. Описание полей базы данных 1.1.6.1 1. Общая характеристика СУБД MS Access СУБД MS Access – это программа, позволяющая легко и быстро проектировать базы данных, состоящие из 7 типов объектов: таблиц, запросов, форм, отчетов, страниц, макросов, модулей. Таблица – это основная структура, предназначенная для хранения информации. Ее строки называются записями, а столбцы - полями базы данных. Каждое поле имеет определенный тип данных (текст, число, дата и т.д.), длину и уникальное имя, которое идентифицирует (однозначно определяет) хранящуюся в этом поле информацию. Теоретический раздел 35 © 2011 Молчина Л.И. Записи идентифицируются по одному или нескольким полям, которые однозначно определяют хранящуюся в этой записи информацию. Такие поля называются ключевыми полями или ключом. Запрос – это требование на отбор данных, хранящихся в таблицах, на выполнение определенных действий с данными. Запрос позволяет создать набор из записей, находящихся в разных таблицах, и использовать его как источник данных для формы или отчета. Кроме того, запрос дает возможность вносить изменения в саму БД. Запросы служат для анализа данных. Форма – это созданный на экране шаблон для ввода, просмотра и редактирования записей БД (как бумажный бланк). Отчет – это средство для отображения на экране или принтере информации из БД в виде, удобном для ее восприятия и анализа пользователем. Макрос – это последовательность действий (макрокоманд) для автоматизации выполнения операций (в среде без программирования). Модуль – это программа для обработки данных, написанная на языке Visual Basic для приложений (VBA). Access позволяет создавать эффективные модули для работы с БД, содержащие меню, диалоговые окна и командные кнопки. Страница – это специальные объекты баз данных, позволяющие просматривать информацию из базы данных, через Web-страницы на удаленных компьютерах ( страницы доступа к данным). Таблицы, запросы, формы, отчеты, страницы, макросы, модули – называются объектами БД. Объекты БД хранятся в едином файле - файле БД, имеющем расширение .mdb. Это упрощает их перенос с компьютера на компьютер, облегчает создание связанных объектов, проверку целостности данных. В Access имеется несколько средств создания каждого из основных объектов базы. Их можно классифицировать как: · ручные (создание и редактирование объектов в режиме Конструктора); · автоматизированные (создание объектов с помощью Мастеров); · автоматические – средства ускоренного создания простейших объектов. Ручные средства являются наиболее трудоемкими, но обеспечивают Молчина Л.И. ЭУМК Модели данных и системы управления базами данных36 © 2011 Молчина Л.И. максимальную гибкость; автоматизированные и автоматические средства являются наиболее производительными, но и наименее гибкими. Конструктор предоставляет пользователю ряд инструментальных средств, с помощью которых можно быстро и просто создавать и модифицировать объекты БД. Мастер делает это по-другому: задает пользователю ряд вопросов и на основе его ответов строит вполне законченный объект БД. В учебных целях для создания разных объектов целесообразно пользоваться разными средствами. При разработке учебных таблиц и запросов рекомендуется использовать ручные средства – работать в режиме Конструктора. Использование Мастеров ускоряет работу, но не способствует освоению понятий и методов. При разработке учебных форм, отчетов и страниц доступа к данным наоборот лучше пользоваться автоматизированными средствами, предоставляемыми Мастерами , так как для данных объектов большую роль играет внешний вид. Дизайн этих объектов весьма трудоемок, поэтому, его лучше поручить программе, и сосредоточиться на содержательной части работы. MS Access располагает разнообразными графическими средствами для оформления таблиц, форм, отчетов, страниц. Кроме проектирования объектов БД, MS Access осуществляет управление БД: · защиту; · резервирование; · репликацию (создание специальных копий БД, с которыми пользователи могут одновременно работать на разных компьютерах); · восстановление; · сжатие; · повышение быстродействия БД; · просмотр сведений о БД; · поиск файла БД по свойствам БД; · экспорт и импорт данных. Теоретический раздел 37 © 2011 Молчина Л.И. 1.1.6.2 2. Описание полей базы данных Чтобы пользователь мог ввести свои данные в компьютерную БД, он должен описать эти поля согласно требованиям Access: · присвоить имена полям; · указать, какого типа данные допускается вводить в каждое поле; · каждому полю дать определенные свойства, которые позволят управлять сохранением, обработкой и отображением данных поля. Полям присваиваются имена с учетом следующих требований: · имя должно содержать не более 64 символов; · оно может включать любую комбинацию букв, цифр, пробелов и специальных символов, за исключением точки (.), восклицательного знака (!), апострофа (‘) и квадратных скобок ([ ]); · имя не должно начинаться с символа пробела; · оно не может включать управляющие символы ( с кодами ASCII от 0 до 31). Типы данных В поля БД можно вводить данные следующих типов. Текстовый. В поля такого типа помещают текст или комбинацию текстовых и числовых значений (например, адреса) длиной до 255 символов. Кроме того, в такие поля записывают числовые значения, для которых не предполагается выполнение расчетов, такие как телефонные или инвентарные номера или почтовые индексы. Поле MEMO. Длинный текст (до 65 535 символов), например, примечания или описания. Числовой. Числовые данные, используемые в математических вычислениях, за исключением денежных расчетов (для последних определен тип "Денежный"). Денежный. Денежные значения. Тип "Денежный" позволяет проводить вычисления без округления значений. Максимальная точность составляет 15 знаков слева от десятичной запятой и 4 знака справа от запятой. Дата/время. Значения даты или времени. Счетчик. Уникальные последовательные (с шагом 1) или случайные номера, Молчина Л.И. ЭУМК Модели данных и системы управления базами данных38 © 2011 Молчина Л.И. автоматически вставляемые при вставке записи в БД. Логический. Поля, которые могут иметь только одно значение из пары значений, таких как Да/Нет, Истина/Ложь или Вкл/Выкл. Поле объекта OLE. Объекты, созданные в других приложениях, которые могут быть связаны или внедрены в таблицу Microsoft Access (например документы Microsoft Word, электронные таблицы Microsoft Excel, рисунки, звукозапись или другие данные в двоичном формате). Гиперссылка. Специальное поле для хранения адресов (URL и UNC) Web- объектов Интернета. Мастер подстановок. Создает поле, позволяющее выбрать с помощью раскрывающегося списка значение из другой таблицы или из списка значений. Набор допустимых свойств для поля зависит от того, какого типа данные будут храниться в поле. Наборы свойств для полей с часто используемыми типами данных приведены в табл. 4.1. Таблица 4.1. Свойства часто используемых полей Числовой тип данных Текстовый тип данных Тип данных Дата/время Размер поля Размер поля – Формат поля Формат поля Формат поля Число десятичных знаков – – Маска ввода Маска ввода Маска ввода Подпись Подпись Подпись Значение по умолчанию Значение по умолчанию Значение по умолчанию Условие на значение Условие на значение Условие на значение Сообщение об ошибке Сообщение об ошибке Сообщение об ошибке Обязательное поле Обязательное поле Обязательное поле – Пустые строки – Теоретический раздел 39 © 2011 Молчина Л.И. Индексированное поле Индексированное поле Индексированное поле Размер поля. Для числового поля допустимыми являются следующие значения: · целые числа от 0 до 255. Данный размер поля обозначается в Access как байт; · целые числа от -32 768 до 32 767 (обозначение размера – целое); · целые числа от -2 147 483 648 до 2 147 483 647 (длинное целое); · числа с плавающей точкой от -3.402823E38 до 3.402823E38, в дробной части до 7 знаков (с плавающей точкой (4 байт)); · числа с плавающей точкой от -1.79769313486232E308 до 1.79769313486232E308, в дробной части - до 15 знаков (с плавающей точкой (8 байт)). Формат поля. Это свойство определяет способ отображения текста, чисел, дат и значений времени на экране и на печати. Число десятичных знаков. Дает возможность указывать для чисел количество дробных знаков. Маска ввода. Задает маску ввода, облегчающую ввод данных в поле. Подпись. Определяет текст, который выводится в подписях полей в таблицах, запросах, формах, отчетах. Значение по умолчанию. Позволяет указать значение, автоматически вводящееся в поле при создании новой записи. Условие на значение. Определяет требования к данным, вводящимся в поле. Сообщение об ошибке. Позволяет указать текст сообщения, выводящегося на экран, если введенные данные нарушают условие, определенное в свойстве Условие на значение. Обязательное поле. Указывает, требует ли поле обязательного ввода значения. Пустые строки. Определяет, допускается ли ввод в текстовое поле пустых строк (не содержащих символов). Индексированное поле. Задает индекс для поля, ускоряющий поиск и сортировку в таблице. Молчина Л.И. ЭУМК Модели данных и системы управления базами данных40 © 2011 Молчина Л.И. 1.1.7 Среда СУБД MS Access. Проектирование таблиц и работа с ними Вопросы 1. Создание новой базы данных 2. Создание связей между таблицами 1.1.7.1 1. Создание новой базы данных Способы создания новой базы данных В Access поддерживается 2 способа создания базы данных: 1-й способ: Создание пустой базы данных, а затем добавление в нее таблиц, запросов, форм, отчетов, страниц, макросов, модулей (объектов). 2-й способ: Создание с помощью мастера базы данных выбранного из списка типа со всеми необходимыми таблицами, отчетами и др. объектами. Access содержит набор различных типовых баз данных. Создание пустой базы данных После запуска Access открывается диалоговое окно, с помощью которого можно приступить к созданию новой базы данных (Новая база данных), вызвать мастер создания (Мастера, страницы и проекты баз данных) или открыть существующую базу данных (Открыть базу данных). Эти режимы можно вызвать также выполнением команд Файл-Создать базу данных, Файл-Открыть базу данных. Способы создания таблиц Процесс создания новой БД начинается с создания структуры таблицы. Таблица это основной (обязательный) объект СУБД Access, предназначенный для хранения информации. Ее строки называются записями, а столбцы – полями БД. Каждая таблица имеет уникальное имя в БД и содержит информацию о каких-либо объектах или свойствах объектов одного типа. Каждое поле содержит однородные данные и имеет уникальное имя, определенный тип, длину и другие свойства. Таблицы создаются в окне уже созданной (пустой или содержащей объекты) БД – Имя_файла:База данных. Теоретический раздел 41 © 2011 Молчина Л.И. В Access имеется 5 способов создания таблицы: 1. Режим таблицы – упрощенный режим создания таблицы. Однако в этом режиме отсутствует доступ ко многим управляющим элементам, которые позволяют указывать свойства полей таблицы. Этот режим подходит для создания "черновика" таблицы, наброска таблицы. 2. Конструктор – предоставляет наиболее широкие возможности по заданию свойств создаваемой таблицы и ее полей. 3. Мастер таблиц – позволяет создавать новые таблицы на основе готовых шаблонов, имеющихся в Access. 4. Импорт таблицы – позволяет выполнить создание новой таблицы вставкой существующей таблицы из внешнего файла. 5. Связь с таблицами – позволяет выполнить создание новой таблицы методом связывания с таблицей из внешнего файла. Создание в таблицы в режиме Конструктора Режим Конструктора предоставляет наиболее широкие возможности по определению параметров создаваемой таблицы. После запуска Access открывается диалоговое окно, с помощью которого можно приступить к созданию новой базы данных (Новая база данных), вызвать мастер создания (Мастера, страницы и проекты баз данных) или открыть существующую базу данных (Открыть базу данных). Эти режимы можно вызвать также выполнением команд Файл-Создать базу данных, Файл-Открыть базу данных. Для создания таблицы вручную, следует использовать режим Создание таблицы в режиме конструктора или на Панели инструментов кнопку Создать, затем режим Конструктор. В результате откроется окно конструктора, предназначенное для ввода структуры создаваемой таблицы. В верхней части окна в каждой строке задается имя поля, его тип и описание (если необходимо). В нижней части задаются свойства текущего поля. Для создания таблицы в режиме Конструктора необходимо: 1. Выбрать объект Таблицы в диалоговом окне База данных. (Это окно Молчина Л.И. ЭУМК Модели данных и системы управления базами данных42 © 2011 Молчина Л.И. появляется после создания нового или открытия уже имеющегося на диске файла базы данных.) 2. Выбрать режим Создание таблицы в режиме конструктора. (или кнопку Создать на Панели инструментов и, в появившемся диалоговом окне Новая таблица, выбрать способ создания таблицы Конструктор). 3. В появившемся окне Таблица ввести в колонку Имя поля имена полей создаваемой таблицы. 4. В колонке Тип данных из раскрывающегося списка выбрать нужный тип данных для каждого имени поля. 5. В нижней части окна задать нужные свойства поля для каждого имени поля. 6. Установить признаки ключевых полей, выделив нужные поля и щелкнув мышью по кнопке Ключевое поле на Панели инструментов или командой Правка- Ключевое поле, (или щелчком правой кнопки мыши по нужному полю (или выделенным полям) и выбрав из контекстного меню команду Ключевое поле) 1.1.7.2 2.Создание связей между таблицами Связь между таблицами устанавливает откос между совпадающими значениями полей разных таблиц. Поля, с помощью которых устанавливается связь, могут иметьразличные имена, но один тип и одинаковый размер данных. Исключение из этого правила: Можно установить между полем, имеющим тип данных счетчик и полем, имеющим тип данных числовой, размер длинное целое. Связь 1:1 – устанавливается обычно по полю, являющимся ключевым. Связь 1:M – обычно устанавливается по полю, которое является ключевым в основе таблицы и индексом, для которой установлено значение “да”; значения повторяются связями таблицы. Связь M:M – в Access реализуется только через промежуточную таблицу. Для создания связи используется диалог по схеме данных. В окне связи присутствуют следующие флажки: Теоретический раздел 43 © 2011 Молчина Л.И. 1) Обеспечение целостности данных 2) Каскадное обновление связанных полей 3) Каскадное удаление связанных полей Если включается (1) флажок , то невозможно ввести в поле связанной таблицы значение , не содержащиеся в ключевом поле главной таблицы. Если включается (2) флажок , то при изменении ключевого поля главной таблицы , в связанной оно изменяется автоматически. Если включается (3) флажок – удаление автоматически из связанной таблицы. 1.2 Мультимедийные презентации В разделе представлены ссылки на мультимедийные презентации по темам: 1. Понятия математической логики  Лекция  Практическое занятие - часть 1  Практическое занятие - часть 2  Практическое занятие - часть 3 2. Организация и хранение данных в базах данных 1.3 Электронные образовательные ресурсы по дисциплине Для более полного и подробного изучения дисциплины рекомендуем обратиться к электронным образовательным ресурсам: · материалы университетских репозиториев открытого доступа ( http://rep.bntu.by/; http://elib.bsu.by; http://www.lib.grsu.by/cgi-bin/ lib.cgi; http://ir.kneu.edu.ua:8080/ и др.) Молчина Л.И. ЭУМК Модели данных и системы управления базами данных44 © 2011 Молчина Л.И. · электронные архивы научной периодики (http://csl.bas-net.by/ Web/Pages/magNAS.asp и др.) 2 Практический раздел В практическом разделе представлены: · задания для практических занятий; · задания для самостоятельной работы. Разделы "Теория множеств", "Математическая логика", "Реляционная алгебра" разработаны кандидатом технических наук, доцентом Чичко О. И. 2.1 Задания для практических занятий В разделе представлены задания для практической работы с методическими указаниями для их выполнения. 2.1.1 Теория множеств 1) Доказать принцип двойственности: C(A U B) = CA CB, C(A B) = CA U CB. 2) Доказать равенства A U (A B) = A (A U B) = A. 3) Доказать равенства: a) CCA = A; б) ; в) . 4) Доказать справедливость включения . 5) Определить множества A U B, A B, A\B, B\A, A B, если: а) A = {x: 0 < x < 2}, B = {x: 1 x 3}; б) A = {x: x2 - 3x < 0}, B = {x: x2 - 4x + 3 0}; в) A = {x: |x - 1| < 2}, B = {x: |x - 1| + |x - 2| < 3}. 6) Имеем . Показать, что . Практический раздел 45 © 2011 Молчина Л.И. 7) Пусть A = {x: 2 x 4}, B = {y: 1 y 3}. Изобразить на плоскости xOy множество точек A Ч B. 8) Показать, что семейство R, замкнутое относительно объединения и разности, является кольцом. 9) Показать, что семейство R = { , O}, состоящее из непустого множества и пустого множества O, образует кольцо. Является ли это кольцо алгеброй? 10) Пусть множество = { , , } состоит из трех элементов, а P( ) - семейство всех подмножеств множества . а) Записать все алгебры, которые можно построить из элементов множества P( ), и указать их единицы. б) Описать все кольца, которые можно построить из элементов множества P( ). в) Описать все полукольца, которые можно построить из элементов множества P( ) и которые не являются кольцами. 11) Доказать, что (A B) Ч (D E) = (A Ч D) (B Ч E). (Информация с сайта: http://www.pm298.ru/reshenie/elem5.php) 12) Определить множества (http://cadmium.ru/content/view/754/45/) 13) Записать отношения, представленные на диаграмме Эйлера– Венна (http://webmath.exponenta.ru/s/c/planimetry/content/chapter16/ section/paragraph1/theory.html): Молчина Л.И. ЭУМК Модели данных и системы управления базами данных46 © 2011 Молчина Л.И. 14) Найти объединение и пересечение множеств, если Ответ. По определению, объединение множеств A и B состоит из элементов как множества A, так и B. Таким образом, A B состоит из всех действительных чисел, входящих либо в промежуток (0; 10), либо в промежуток [-7; 2), то есть является объединением этих промежутков. На числовой прямой отметим концы данных промежутков. Объединением, очевидно, будет промежуток [-7; 10). Пересечением данных числовых промежутков будет промежуток с двойной штриховкой. Таким образом, Практический раздел 47 © 2011 Молчина Л.И. 15) Изобразите с помощью диаграмм Эйлера-Венна следующую форму логического рассуждения: "Если некоторые a являются b, а некоторые b являются c, то некоторые a являются c". Ответ. На языке теории множеств это означает, что если A B и B C , то A C . Но это не всегда верно, что и демонстрирует приведенная диаграмма. Выполните упражнения( http://festival.1september.ru/articles/513260/): 1. Назовите известные вам названия множеств людей (например, команда). 2. Назовите известные вам названия множеств живых существ (например, табун). 3. Запишите множества, элементами которого являются: а) планеты солнечной системы; б) столицы государств; в) все двузначные числа; г) числа, делящиеся на 7. 4. Пусть А – множество чисел, на которые делится число 100 без остатка. Верна ли запись: . 5. Запишите множество В, элементами которого являются ученики 5 А класса. Выпишите два элемента принадлежащих множеству В и два элемента не принадлежащих множеству В. Молчина Л.И. ЭУМК Модели данных и системы управления базами данных48 © 2011 Молчина Л.И. 6. Пусть даны множества и . Выпишите: а) два элемента принадлежащих множеству А, но не принадлежащих множеству В; б) два элемента принадлежащих множеству В, но не принадлежащих множеству А; в) два элемента принадлежащих и множеству А и множеству В; г) два элемента не принадлежащих ни множеству А ни множеству В. 7. Придумайте и запишите какое-нибудь множество. Выпишите два элемента принадлежащих этому множеству и два элемента не принадлежащих этому множеству. 8. Придумайте и запишите множество, не содержащее ни одного элемента (пустое множество). Выполните упражнения ( http://festival.1september.ru/ articles/513260/): 1. Верна ли запись: 2. Запишите все множества, равные множеству . 3. Запишите множества, равные данным: 4. Придумайте и запишите множество, состоящее из пяти элементов. Выпишите все множества равные придуманному множеству. Сколько множеств получилось? 5. Выполните упражнения ( http://festival.1september.ru/ articles/513260/): 1. Даны множества: a. множество А учеников 5 класса нашей школы; b. множество В всех учеников нашей школы; Практический раздел 49 © 2011 Молчина Л.И. c. множество С учеников 5 класса нашей школы, посещающих бассейн; d. множество Е всех учащихся школ города Новокузнецка; e. множество К учеников 5 математического класса нашей школы. Верно ли что: a. множество А есть подмножество множества В; b. множество А есть подмножество множества К ; c. множество В есть подмножество множества Е; d. множество К есть подмножество множества С; Запишите с помощью знака I названия множеств в таком порядке, чтобы каждое следующее множество было подмножеством предыдущего множества. 2. Для множества выпишите все его подмножества. Выполните упражнения ( http://festival.1september.ru/ articles/513260/): 1. Даны множества . Найдите: а) ; б) ; в) ; г) . 2. Найдите , если а) ; б) Выполните упражнения ( http://festival.1september.ru/ articles/513260/): 1. Даны множества . Найдите: а) ; б) ; в) ; г) . Молчина Л.И. ЭУМК Модели данных и системы управления базами данных50 © 2011 Молчина Л.И. 2. Найдите если и . 3. Даны множества . Найдите: а) ; б) ; в) ; г) . Выполните упражнения ( http://festival.1september.ru/ articles/513260/): Даны множества . Найдите: а) ; б) ; в) ; г) . Найдите и если и . Даны множества . Найдите: а) ; б) ; в) ; г) Пример решения задачи ( http://festival.1september.ru/ articles/513260/): Задача. В классе 35 учеников. Из них 20 занимаются в математическом кружке, 11 – в биологическом, 10 ребят не посещают эти кружки. Сколько биологов увлекаются математикой? Решение. Изобразим эти кружки (рис. 5). Большой круг будет изображать учащихся класса. В этот круг поместим два поменьше. Один обозначим буквой М и он будет изображать математиков класса. Другой круг Практический раздел 51 © 2011 Молчина Л.И. обозначим Б – биологи класса. Очевидно, в общей части кругов, обозначенной МБ, окажутся те самые биологи – математики, которые нас интересуют. Теперь посчитаем: Всего внутри большого круга 35 ребят, внутри двух меньших ребят. Внутри “математического” круга М находятся 20 ребят, значит, в той части “биологического” круга, которая расположена вне круга М, находятся биологов, не посещающих математический кружок. Остальные биологи, их человек, находятся в общей части кругов МБ. Таким образом, 6 биологов увлекаются математикой. Ответ. 6 биологов увлекаются математикой . Выполните упражнения ( http://festival.1september.ru/ articles/513260/): 1. В классе 29 учащихся. Каждый из них изучает хотя бы один язык – английский или немецкий. Английский язык изучают 18 человек, немецкий язык изучают 15 человек. Сколько человек изучают два языка и немецкий, и английский? 2. В классе 29 учащихся. Из них 16 занимаются музыкой, 21 посещают математический кружок; 4 не занимаются музыкой и не посещают математический кружок. Сколько учащихся посещают только математический кружок? Сколько математиков занимаются и музыкой? 3. В пионерском лагере 70 ребят. Из них 27 занимаются в драмкружке, 32 поют в хоре, 22 увлекаются спортом. В Молчина Л.И. ЭУМК Модели данных и системы управления базами данных52 © 2011 Молчина Л.И. драмкружке 10 ребят из хора, в хоре 6 спортсменов, в драмкружке 8 спортсменов; 3 спортсмена посещают и драмкружок и хор. Сколько ребят не поют, не увлекаются спортом, не занимаются в драмкружке? Сколько ребят заняты только спортом? 4. В классе 38 человек. Из них 16 человек играют в баскетбол, 17 человек – в хоккей, 18 человек – в волейбол. Увлекаются двумя видами спорта – баскетболом и хоккеем 4 человека, баскетболом и волейболом 3 человека, волейболом и хоккеем 5 человек. Трое не увлекаются ни баскетболом, ни волейболом, ни хоккеем. Сколько ребят увлекаются одновременно тремя видами спорта? Вопросы к тематическому зачету по теме “Элементы теории множеств” 1. Кто является основоположником теории множеств? 2. Какие два множества являются равными? 3. Как называется множество, в котором нет ни одного элемента? 4. Составьте множество и запишите два элемента принадлежащие этому множеству и два элемента не принадлежащие ему. 5. Какое множество является подмножеством данного множества? 6. Для множества составьте все его подмножества. 7. Какое множество является пересечением двух множеств? Приведите пример. 8. Какое множество является объединением двух множеств? Приведите пример. 9. Как вычитаются множества? Приведите пример. 2.1.2 Математическая логика Презентация: Практическое занятие - часть 1 Презентация: Практическое занятие - часть 2 Презентация: Практическое занятие - часть 3 Практический раздел 53 © 2011 Молчина Л.И. 2.1.3 Реляционная алгебра Основы реляционной алгебры (http://habrahabr.ru/post/145381/) Реляционная алгебра базируется на теории множеств и является основой логики работы баз данных. Реляционной базой данных называется совокупность отношений, содержащих всю информацию, которая должна хранится в базе. В данном определении нам интересен термин отношение, но пока оставим его без строго определения. Лучше представим себе таблицу продуктов. таблица PRODUCTS ID NAME COMPANY PRICE 123 Печеньки ООО ”Темная сторона” 190 156 Чай ООО ”Темная сторона” 60 235 Ананасы ОАО ”Фрукты” 100 623 Томаты ООО ”Овощи” 130 Таблица состоит из 4х строк, строка в таблице является кортежем в реляционной теории. Множество упорядоченных кортежей называется отношением. Перед тем как дать определение отношения, введем еще один термин — домен. Домены применительно к таблице это столбцы. Для ясности, теперь введем строгое определение отношения. Пусть даны N множеств D1,D2, …. Dn (домены), отношением R над этими множествами называется множество упорядоченных N-кортежей вида , где d1 принадлежит D1 и тд. Множества D1,D2,..Dn называются доменами отношения R. Каждый элемент кортежа представляет собой значение одного из атрибутов, соответствующего одному из доменов. Ключи в отношениях В отношении требованием является то, что все кортежи должны различаться. Для однозначной идентификации кортежа существует первичный ключ. Первичный ключ это атрибут или набор из Молчина Л.И. ЭУМК Модели данных и системы управления базами данных54 © 2011 Молчина Л.И. минимального числа атрибутов, который однозначно идентифицирует конкретный кортеж и не содержит дополнительных атрибутов. Подразумевается, что все атрибуты в первичном ключе должны быть необходимыми и достаточными для идентификации конкретного кортежа, и исключение любого из атрибутов в ключе сделает его недостаточным для идентификации. Например, в такой таблице ключом будет сочетание атрибутов из первого и второго столбца. таблица DRIVERS COMPANY DRIVER ООО ”Темная сторона” Владимир ООО ”Темная сторона” Михаил ОАО ”Фрукты” Руслан ООО ”Овощи” Владимир Видно, что в организации может быть несколько водителей, и чтобы однозначно идентифицировать водителя необходимо и значение из столбца “Название организации” и из “Имя водителя”. Такой ключ называется составным. В реляционной БД таблицы взаимосвязаны и соотносятся друг с другом как главные и подчиненные. Связь главной и подчиненнной таблицы осуществляется через первичный ключ (primary key) главной таблицы и внешний ключ ( foreign key ) подчиненной таблицы. Внешний ключ это атрибут или набор атрибутов, который в главной таблице является первичным ключем. Этой подготовительной теории будет достаточно для знакомства с основными операциями реляционной алгебры. Операции реляционной алгебры Основные восемь операций реляционной алгебры были предложены Э. Коддом. · Объединение · Пересечение Практический раздел 55 © 2011 Молчина Л.И. · Вычитание · Декартово произведение · Выборка · Проекция · Соединение · Деление Первая половина операций аналогична таким же операциям над множествами. Часть операций можно выразить через другие операции. Рассмотрим большую часть операций с примерами. Для понимания важно запомнить, что результатом любой операции алгебры над отношениями является еще одно отношение, которое можно потом так же использовать в других операциях. Создадим еще одну таблицу, которая нам пригодится в примерах. таблица SELLERS ID SELLER 123 OOO “Дарт” 156 ОАО ”Ведро” 235 ЗАО “Овоще База” 623 ОАО ”Фирма” Условимся, что в этой таблице ID это внешний ключ, связанный с первичным ключом таблицы PRODUCTS. Для начала рассмотрим самую простую операцию — имя отношения. Её результатом будет такое же отношение, то есть выполнив операцию PRODUCTS, мы получим копию отношения PRODUCTS. Проекция Проекция является операцией, при которой из отношения выделяются атрибуты только из указанных доменов, то есть из таблицы выбираются только нужные столбцы, при этом, если получится несколько одинаковых кортежей, то в результирующем отношении остается только Молчина Л.И. ЭУМК Модели данных и системы управления базами данных56 © 2011 Молчина Л.И. по одному экземпляру подобного кортежа. Для примера сделаем проекцию на таблице PRODUCTS выбрав из нее ID и PRICE. Синтаксис операции: (ID, PRICE) PRODUCTS В результате этой операции получим отношение: ID PRICE 123 190 156 60 235 100 623 130 Выборка Выборка — это операция, которая выделяет множество строк в таблице, удовлетворяющих заданным условиям. Условием может быть любое логическое выражение. Для примера сделаем выборку из таблицы с ценой больше 90. Синтаксис операции: (PRICE>90) PRODUCTS ID NAME COMPANY PRICE 123 Печеньки ООО ”Темная сторона” 190 235 Ананасы ОАО ”Фрукты” 100 623 Томаты ООО ”Овощи” 130 В условии выборки мы можем использовать любое логическое выражение. Сделаем еще одну выборку с ценой больше 90 и ID товара меньше 300: (PRICE>90 ^ ID<300) PRODUCTS ID NAME COMPANY PRICE 123 Печеньки ООО ”Темная сторона” 190 Практический раздел 57 © 2011 Молчина Л.И. 235 Ананасы ОАО ”Фрукты” 100 Совместим операторы проекции и выборки. Мы можем это сделать, потому что любой из операторов в результате возвращает отношение и в качестве аргументов использует также отношение. Из таблицы с продуктами выберем все компании, продающие продуты дешевле 110. COMPANY (PRICE<100 ) PRODUCTS COMPANY ООО ”Темная сторона” ОАО ”Фрукты” Умножение Умножение или декартово произведение является операцией, производимой над двумя отношениями, в результате которой мы получаем отношение со всеми доменами из двух начальных отношений. Кортежи в этих доменах будут представлять из себя все возможные сочетания кортежей из начальных отношений. На примере будет понятнее. Получим декартово произведения таблиц PRODUCTS и SELLERS. Синтаксис операции: PRODUCTS Ч SELLERS Можно заметить, что у двух этих таблиц есть одинаковый домен ID. В подобной ситуации домены с одинаковыми названиями получают префикс в виде названия соответствующего отношения, как показано ниже. Для краткости перемножим не полные отношения, а выборки с условием ID<235 (цветом выделены одни и те же кортежи) PRODUCTS. ID NAME COMPANY PRIC E SELLERS. ID SELLER 123 Печеньки ООО ”Темная сторона” 190 123 OOO “Дарт” Молчина Л.И. ЭУМК Модели данных и системы управления базами данных58 © 2011 Молчина Л.И. 156 Чай ООО ”Темнаясторона” 60 156 ОАО ”Ведро” 123 Печеньки ООО ”Темная сторона” 190 156 ОАО ”Ведро” 156 Чай ООО ”Темнаясторона” 60 123 OOO “Дарт” Для примера использования этой операции представим себе необходимость выбрать продавцов с ценами меньше 90. Без произведения необходимо было бы сначала получить ID продуктов из первой таблицы, потом по этим ID из второй таблицы получить нужные имена SELLER, а с использованием произведения будет такой запрос: (SELLER) (RODUCTS.ID=SELLERS.ID ^ PRICE<90) PRODUCTS Ч SELLERS В результате этой операции получим отношение: SELLER ОАО ”Ведро” Соединение и естественное соединение Операция соединения обратна операции проекции и создает новое отношение из двух уже существующих. Новое отношение получается конкатенацией кортежей первого и второго отношений, при этом конкатенации подвергаются отношения, в которых совпадают значения заданных атрибутов. В частности, если соединить отношения PRODUCTS и SELLERS, этими атрибутами будут атрибуты доменов ID. Также для понятности можно представить соеднинение как результат двух операций. Сначала берется произведение исходных таблиц, а потом из полученного отношения мы делаем выборку с условием равенства атрибутов из одинаковых доменов. В данном случае условием явлется равенство PRODUCTS.ID и SELLERS.ID. Попробуем соединить отношения PRODUCTS и SELLERS и получим отношение. PRODUCTS NAME COMPANY PRIC SELLERS. SELLER Практический раздел 59 © 2011 Молчина Л.И. .ID E ID 123 Печеньки ООО ”Темная сторона” 190 123 OOO “Дарт” 156 Чай ООО ”Темнаясторона” 60 156 ОАО ”Ведро” 235 Ананасы ОАО ”Фрукты” 100 235 ЗАО “Овоще База” 623 Томаты ООО ”Овощи” 130 623 ОАО ”Фирма” Натуральное соединение получает схожее отношение, но в случае, если у нас корректно настроена схема в базе ( в данном случае первичный ключ таблицы PRODUCTS ID связан с внешним ключем таблицы SELLERS ID), то в результирующем отношении остается один домен ID. Синтаксис операции: PRODUCTS SELLERS; Получится такое отношение: PRODUCTS. ID NAME COMPANY PRICE SELLER 123 Печеньки ООО ”Темная сторона” 190 OOO “Дарт” 156 Чай ООО ”Темнаясторона” 60 ОАО ”Ведро” 235 Ананасы ОАО ”Фрукты” 100 ЗАО “ОвощеБаза” 623 Томаты ООО ”Овощи” 130 ОАО ”Фирма” Пересечение и вычитание. Результатом операции пересечения будет отношение, состоящее из кортежей, полностью входящих в состав обоих отношений. Результатом вычитания будет отношение, состоящее из кортежей, которые являются кортежами первого отношения и не являются кортежами второго отношения. Данные операции аналогичны таким же операциям над множествам, так что, я думаю, нет необходимости подробно их расписывать. Молчина Л.И. ЭУМК Модели данных и системы управления базами данных60 © 2011 Молчина Л.И. Источники информации · Основы использования и проектирования баз данных — В. М. Илюшечкин · курс лекций Introduction to Databases — Jennifer Widom, Stanford University Основные операции реляционной алгебры (http://www.e-uni.ee/e- kursused/eucip/arendus_vk/252____.html) Для того чтобы действительно понять поиск данных, полезно начать с понятий реляционной алгебры, исследуя ее потенциальные операции. · Объединение двух таблиц (union). Это набор строк (совокупность, комплект), который принадлежит, по крайней мере, одному из двух табличных комплектов. Объединение можно представлять, как таблицу, которая собрана из различных столбцов двух в таблице. · Разница между двумя таблицами (difference). Предполагая, что в двух таблицах имеются одинаковые столбцы, определим разницу как набор строк (совокупность, комплект), которые имеются в первой и отсутствуют во второй таблице. · Прямым произведением (декартово (Descartes) произведение, Cartesian product) является набором строк (совокупность, комплект), который получается путем расположения каждой строки из первой таблицы по одной рядом со строками второй таблицы. Пример прямого произведения двух приведенных ниже таблиц приведен в следующей ниже второй таблице. Таким образом, имеем здесь дело со всевозможными комбинациями между строками двух таблиц. Практический раздел 61 © 2011 Молчина Л.И. Пользователи     Учебный предмет   Код студент а Имя Предмет Студе нт A01 Марек   История A01 A02 Мария   История A03 A03 Элла   География A01       География A02   Прямое произведение: Пользователи и Учебные предметы Код студента Имя Предмет Студент A01 Марек История A01 A01 Марек История A03 A01 Марек География A01 A01 Марек География A02 A02 Мария История A01 Молчина Л.И. ЭУМК Модели данных и системы управления базами данных62 © 2011 Молчина Л.И. A02 Мария История A03 A02 Мария География A01 A02 Мария География A02 A03 Элла История A01 A03 Элла История A03 A03 Элла География A01 A03 Элла География A02 · Выборка из таблицы. Это набор строк (совокупность, комплект) таблицы, удовлетворяющий сериям условий, которые видны из самого выбора. В приведенной выше таблице серым цветом выделены строки, которые удовлетворяют условию «Код студента» таблицы «Пользователей» соответствует «Студенту» таблицы «Учебные предметы». Эти строки скопированы в следующую таблицу. Выборка из прямого произведения Код студента Имя Предмет Студент A01 Марек История A01 A01 Марек География A01 A02 Мария География A02 A03 Элла История A03 · Проекция. Проекция представляет собой подмножество Практический раздел 63 © 2011 Молчина Л.И. столбцов, получаемое из прямого произведения путем удаления многократного вхождения столбцов (или неопределенных атрибутов), таким образом, исключают многократно представленные столбцы и удаляют столбцы с нежелательной информацией. В приведенном выше примере столбец «Студент» представляет ту же информацию, что столбец «Код студента» и, в итоге, не присутствует в результате операции проекция. Проекция Код студента Имя Предмет A01 Марек История A01 Марек География A02 Мария География A03 Элла История · Конъюнкция или соединение (слияние, объединение - join). Операция соединения, по сути, прямое произведение с последующей выборкой. Поскольку выборка содержит только равные условия, поэтому название операции - «эквивалентная связь, объединение по эквивалентности - equi-join ». Результат эквивалентной связи - особое промежуточное соединение двух таблиц. Если этому следует проекция, то такая операция именуется «естественное соединение - natural join». Две таблицы, сопровождающие последний вышеприведенный пример есть примеры эквивалентной связи и естественного соединения. · Переименование. Используют для переименования столбцов таблицы. Например, столбец «Код студента» можно переименовать в столбец «Код». Молчина Л.И. ЭУМК Модели данных и системы управления базами данных64 © 2011 Молчина Л.И. Переименование Код Имя Предмет A01 Марек История A01 Марек География A02 Мария География A03 Элла История Операции над данными ( http://library.fentu.ru/book/iu/31/_2__.html) Операции обработки кортежей Эти операции связаны с изменением состава кортежей в каком-либо отношении. · ДОБАВИТЬ - необходимо задать имя отношения и ключ кортежа. · УДАЛИТЬ - необходимо указать имя отношения, а также идентифицировать кортеж или группу кортежей, подлежащих удалению. · ИЗМЕНИТЬ - выполняется для названного отношения и может корректировать как один, так и несколько кортежей. Операции обработки отношений. На входе каждой такой операции используется одно или несколько отношений, результатом выполнения операции всегда является новое отношение. В реляционной алгебре определены следующие операций обработки отношений: · ПРОЕКЦИЯ (ВЕРТИКАЛЬНОЕ ПОДМНОЖЕСТВО). Операция Практический раздел 65 © 2011 Молчина Л.И. проекции представляет из себя выборку из каждого кортежа отношения значений атрибутов, входящих в список A, и удаление из полученного отношения повторяющихся строк. · ВЫБОРКА (ОГРАНИЧЕНИЕ, ГОРИЗОНТАЛЬНОЕ ПОДМНОЖЕСТВО). На входе используется одно отношение, результат - новое отношение, построенное по той же схеме, содержащее подмножество кортежей исходного отношения, удовлетворяющих условию выборки. Молчина Л.И. ЭУМК Модели данных и системы управления базами данных66 © 2011 Молчина Л.И. · ОБЪЕДИНЕНИЕ. Отношения-операнды в этом случае должны быть определены по одной схеме. Результирующее отношение содержит все строки операндов за исключением повторяющихся. Практический раздел 67 © 2011 Молчина Л.И. · ПЕРЕСЕЧЕНИЕ. На входе операции два отношения, определенные по одной схеме. На выходе - отношение, содержащие кортежи, которые присутствуют в обоих исходных отношениях. Молчина Л.И. ЭУМК Модели данных и системы управления базами данных68 © 2011 Молчина Л.И. · РАЗНОСТЬ. Операция во многом похожая на ПЕРЕСЕЧЕНИЕ, за исключением того, что в результирующем отношении содержатся кортежи, присутствующие в первом и отсутствующие во втором исходных отношениях. · ДЕКАРТОВО ПРОИЗВЕДЕНИЕ Входные отношения могут быть Практический раздел 69 © 2011 Молчина Л.И. определены по разным схемам. Схема результирующего отношения включает все атрибуты исходных. Кроме того: o степень результирующего отношения равна сумме степеней исходных отношений o мощность результирующего отношения равна произведению мощностей исходных отношений. · СОЕДИНЕНИЕ Данная операция имеет сходство с ДЕКАРТОВЫМ ПРОИЗВЕДЕНИЕМ. Однако, здесь добавлено условие, согласно которому вместо полного произведения всех строк в результирующее отношение включаются только строки, удовлетворяющие опредленному соотношению между атрибутами соединения (А1,A2) соответствующих отношений. Молчина Л.И. ЭУМК Модели данных и системы управления базами данных70 © 2011 Молчина Л.И. · ДЕЛЕНИЕ Пусть отношение R , называемое делимым, содержит атрибуты (A1,A2,...,An). Отношение S - делитель содержит подмножество атрибутов A: (A1,A2,...,Ak) (k 10 С2 = С1 [ОТД_КОЛ > 10]. (3).спроецировать результаты предыдущей операции на атрибуты СОТР_ИМЯ, СОТР_НОМЕР С3 = С2 [СОТР_ИМЯ, СОТР_НОМЕР] Заметим, что порядок выполнения шагов может повлиять на эффективность выполнения запроса. Так, время выполнения приведенного выше запроса можно сократить, если поменять местами этапы (1) и (2). В этом случае сначала из отношения СОТРУДНИКИ будет сделана выборка всех кортежей со значением атрибута ОТДЕЛ_КОЛ > 10, а затем выполнено соединение результирующего отношения с отношением ОТДЕЛЫ. Машинное время экономится за счет того, что в операции соединения участвуют меньшие отношения. На языке реляционного исчисления данный запрос может быть записан как: Выдать СОТР_ИМЯ и СОТР_НОМ для СОТРУДНИКИ таких, что существует ОТДЕЛ с таким же, что и СОТР_НОМ значением ОТД_НАЧ и значением ОТД_КОЛ большим 50. Практический раздел 73 © 2011 Молчина Л.И. Здесь мы указываем лишь характеристики результирующего отношения, но не говорим о способе его формирования. СУБД сама должна решить какие операции и в каком порядке надо выполнить над отношениями СОТРУДНИКИ и ОТДЕЛЫ. Задача оптимизации выполнения запроса в этом случае также ложится на СУБД. . 2.1.4 Проектирование БД. Организация связей между объектами БД Цель работы: 1) получить практические навыки создания баз данных; 2) научится создавать таблицы базы данных; 3) приобрести практические навыки создания и изменения связей между таблицами; 4) освоить основные приемы заполнения и редактирования таблиц базы данных. Задача. В туристической фирме требуется автоматизировать учет организуемых поездок и покупки путевок клиентами. Для этого необходимо создать базу данных, в которой будут содержаться все необходимые сведения о поездках, формируемых группах, клиентах, а также сведения о покупке путевок. Для решения поставленной задачи требуется обеспечить хранение следующей информации в таблицах базы данных «Туристическая фирма» ? о странах: КодСтраны, название, столица, язык страны; ? о поездках: КодПоездки, Страна, маршрут, фото, количество дней поездки, тип поездки, вид транспорта, размещение, питание, стоимость; ? о группах: №группы, код поездки, дата отъезда, количество путевок; ? о клиентах: №паспорта, фамилия, имя, отчество, адрес, контактный телефон; ? о заявках: №заявки, №группы, №паспорта, количество путевок, скидка (нет скидки, 5 % или 10 %), необходимость визы. Необходимо создать связи и обеспечить целостность данных ? между таблицами Страны и Поездки по полю КодСтраны и Страна, тип связи «один-ко-многим»; ? между таблицами Поездки и Группы по полю КодПоездки, тип связи «один-ко- многим»; ? между таблицами Группы и Заявки по полю №группы, тип связи «один-ко- Молчина Л.И. ЭУМК Модели данных и системы управления базами данных74 © 2011 Молчина Л.И. многим»; ? между таблицами Клиенты и Заявки по полю №паспорта, тип связи «один-ко- многим». 2.1.4.1 Проектирование таблиц базы данных Задание 1. Создайте новую базу данных – Туристическая фирма. Порядок выполнения: 1. Загрузите MS Access. 2. В области задач перейдите по ссылке Новая база данных. 3. В диалоговом окне Файл новой базы данных выберите свою папку в списке папок и щелкните в поле ввода Имя файла. Введите имя базы данных – Туристическая фирма – и нажмите кнопку Создать. Примечание: После создания нового файла БД в поле окна Access появится окно базы данных. В окне базы данных каждый ярлык соответствует объекту базы данных. Можно работать как с базой данных целиком (создание, открытие и закрытие базы данных), так и с каждым ее объектом в отдельности. Для работы с любым объектом предусмотрено два режима: оперативный режим, в котором осуществляется просмотр и изменение информации (кнопка Открыть), и режим Конструктора, в котором изменяется макет, структура объекта (кнопка Конструктор). Набор пунктов горизонтального меню и состав панели инструментов зависят от активного окна, т. е. от того, какому объекту соответствует активное окно и в каком режиме происходит работа с этим объектом. Задание 2. Создайте в режиме Конструктора таблицу базы данных Страны, структура которой приведена в табл. 10 (значок в схеме таблицы означает, что поле является ключевым). Заполните таблицу данными. Практический раздел 75 © 2011 Молчина Л.И. Таблица 1 Структура таблицы Страны Имя поля Тип данных Свойства поля Описание Общие Подста- новка КодСтраны Текстов ый Разме р поля – 2 – Уникальный код страны Название Текстов ый Разме р поля – 100; Обяза тельн ое поле – Да; Пуст ые строк и – Нет – Название государства Столица Текстов ый Разме р поля – 30 – Столица государства Язык страны Текстов ый Разме р поля – 30 – Государственный язык страны Порядок выполнения: Молчина Л.И. ЭУМК Модели данных и системы управления базами данных76 © 2011 Молчина Л.И. 1. В окне базы данных щелкните по ярлыку Таблицы. 2. Нажмите кнопку Создать в верхней части окна базы данных. 3. В окне Новая таблица выберите пункт Конструктор. Примечание: Окно таблицы в режиме Конструктора состоит из 2-х областей: области проекта таблицы и области свойств поля. Переход между областями осуществляется клавишей [F6] или с помощью мыши. Область проекта содержит таблицу из 3-х столбцов: Имя поля, Тип данных, Описание. Переход между столбцами осуществляется одним из способов: мышкой, нажатием на клавишу [Enter] либо [Тab], клавишами управления курсором. Чтобы активизировать поле, достаточно щелкнуть мышью в строке, в которой описывается данное поле. В нижней области свойств поля располагаются 2 вкладки свойств: Общие и Подстановка. На вкладке Общие указаны отдельные характеристики каждого поля, необходимые для определения параметров сохранения данных в поле, их дальнейшего отображения и редактирования. Вкладка Подстановка содержит список некоторых дополнительных параметров, необходимых, в частности, для настройки связей с полями других таблиц. 4. В окне проекта таблицы создайте поле КодСтраны. Для этого: · введите в первую строку столбца Имя поля наименование поля КодСтраны и нажмите клавишу [Enter]; · во втором столбце Тип данных оставьте выводящееся по умолчанию значение Текстовый (любой другой выбирается из раскрывающегося списка типов данных); · поле КодСтраны сделайте ключевым. Для этого активизируйте поле и нажмите кнопку Ключевое поле на панели инструментов Конструктор таблиц либо выполните команду Правка® Ключевое поле; · в колонке Описание добавьте текст в соответствии с табл. 10; · переключитесь в область Свойства поля и на вкладке Общие измените размер поля на значение 2. 5. Введите имя поля Название, перейдите в Область свойств и измените размер поля в соответствии с табл. 10, затем установите следующие значения свойств поля: · обязательное поле – Да; · пустые строки – Нет. Практический раздел 77 © 2011 Молчина Л.И. Значения следует выбирать из раскрывающего списка в строке нужного свойства. 6. Аналогично добавьте в таблицу поля Столица, ЯзыкСтраны (свойства полей установите по табл. 10). 7. Выполните команду Файл® Сохранить либо нажмите одноименную кнопку на панели инструментов и в отрывшемся окне введите имя таблицы – Страны. 8. Перейдите в режим таблицы, выполнив команду Вид® Режим таблицы. Примечание: В левой части окна таблицы расположена область выделения. Треугольный маркер (>) в области выделения указывает на активную запись, а звездочка (*) – на пустую. Для обозначения записи, в которой осуществляется ввод, используется обозначение карандаша. Передвижение по таблице можно производить с помощью клавиш управления курсором, [Tab] и [Enter], а также щелчком мыши. Можно пользоваться стандартными для Windows комбинациями клавиш для быстрого продвижения по таблице. Записи в таблице можно перемещать, копировать и удалять теми же способами, что и в электронных таблицах. Столбец можно выделить щелчком мыши по заголовку. Столбцы можно перемещать вправо и влево, пользуясь методом drag and drop. 9. Введите следующие 7 записей в таблицу: КодСтраны Название Столица Язык страны 01 Польша Варшава Польский 02 Великобритания Лондон Английский 03 Италия Рим Итальянский 04 США Вашингтон Английский 05 Болгария София Болгарский 06 Франция Париж Французский 07 Чехия Прага Чешский 10.Закройте таблицу. Задание 2. Молчина Л.И. ЭУМК Модели данных и системы управления базами данных78 © 2011 Молчина Л.И. 1. С помощью Конструктора создайте таблицу Клиенты в соответствии с табл. 2. Таблица 2 Структура таблицы Клиенты Имя поля Тип данных Свойства поля Общие Подстановка НомерПаспор та Текстовый Размер поля – 30 Подпись – Номер паспорта – Имя Текстовый Размер поля – 50 Обязательное поле – Да Пустые строки – Нет Отчество Текстовый Размер поля – 50 Фамилия Текстовый Размер поля – 50 Обязательное поле – Да Пустые строки – Нет Адрес Текстовый Размер поля – 255 – КонтактныйТ елефон Текстовый Размер поля – 30 Обязательное поле – Да Пустые строки – Нет – 2. Перейдите в режим таблицы, нажав кнопку Вид на панели инструментов Конструктор таблиц. Введите произвольные 8 записей. Задание 3. С помощью Конструктора создайте таблицу Поездки в соответствии с табл. 3. Практический раздел 79 © 2011 Молчина Л.И. Таблица 3 Структура таблицы Поездки Имя поля Тип данных Свойства поля Общие Подстановка КодПоездки Числовой Размер поля – Целое - Страна Текстовый Размер поля – 2 - Маршрут Текстовый Размер поля – 255 Пустые строки – Нет - КоличествоДней Числовой Размер поля – Целое - ТипПоездки Текстовый Размер поля – 30 Список значений: экскурсия; отдых; учеба; работа; шопинг. Ограничиться списком –Нет ВидТранспорта Текстовый Размер поля – 30 Список значений: автобус; авиа; ж/д; самостоятельно. Ограничиться списком – Да Размещение Текстовый Размер поля – 30 Список значений: "**"; "***"; "****"; апартаменты, по выбору. Ограничиться списком – Да Молчина Л.И. ЭУМК Модели данных и системы управления базами данных80 © 2011 Молчина Л.И. Имя поля Тип данных Свойства поля Общие Подстановка Питание Логический Формат поля – Да/Нет Стоимость Денежный Формат поля – Евро Методическме рекомендации: 1. Установите свойства полей КодПоездки, Страна и Маршрут в соответствии с табл. 3. 2. Для поля ТипПоездки используйте Мастер подстановок: · в столбце Тип данных выберите из списка значение Мастер подстановок; · на первом шаге Мастера в окне Создание подстановки установите переключатель Будет введен фиксированный набор значений; · в следующем окне диалога оставьте без изменений число столбцов – 1 и введите в Столбец1 значения, указанные в табл. 3. При этом получится столбец значений, представленный на рис. 9. Рис. 9. Создание подстановки Примечание: Переход к следующей строке столбца осуществляется с помощью клавиш [Tab], управления курсором либо щелчком мыши. Нажатием клавиши [Enter], так же, как и кнопки Далее, выполняется переход в следующее диалоговое окно. · нажмите кнопку Готово по завершении процедуры создания столбца подстановок. 3. Измените свойства поля согласно табл. 3. 4. Аналогично вышеописанной технологии создайте поля со списком: Размещение и ВидТранспорта. Списки значений этих полей указаны в табл. 3. 5. Активизируйте поле КоличествоДней. Задайте размер поля – Целое – в области свойств. Практический раздел 81 © 2011 Молчина Л.И. 6. Для поля Питание измените тип данных – логический, формат поля – Да/Нет. 7. Для поля Стоимость установите формат Евро. 8. Закройте таблицу Поездки, сохранив изменения. Задание 4. В режиме Конструктора создайте таблицу Группы, структура которой приведена в табл. 4. Заполните таблицу данными. Таблица 4 Структура таблицы Группы Имя поля Тип данных Свойства поля Общие Подстановка НомерГруппы Числовой Размер поля – Целое - Маршрут Числовой Размер поля – Целое Обязательное поле – Да Поле КодПоездки из таблицы Поездки ДатаОтъезда Дата/время Краткий формат даты Маска ввода- 00/00/0000;0;# - КоличествоПутево к Числовой Размер поля – Целое Условие на значение – >0 - Порядок выполнения: 1. Дважды щелкните по ярлыку Создание таблицы в режиме конструктора в окне базы данных 2. Создайте ключевое поле НомерГруппы по технологии, описанной в задании 1. 3. Создайте поле со списком Маршрут, в качестве источника строк которого используются значения поля КодПоездки из таблицы Поездки. Для этого: · введите имя поля, в столбце Тип данных выберите из списка значение Мастер подстановок; · в окне Создание подстановки установите переключатель Объект «столбец подстановки» будет использовать значения из таблицы или запроса; · в следующем диалоговом окне выберите из списка таблицу, из которой будет Молчина Л.И. ЭУМК Модели данных и системы управления базами данных82 © 2011 Молчина Л.И. осуществляться подстановка – Поездки; · на следующем шаге Мастера из списка Доступные поля в список Выбранные поля с помощью кнопки переместите поля КодПоездки и Маршрут (рис. 10); Рис. 10. Выбор полей при создании подстановки · в следующем окне оставьте без изменения флажок Скрыть ключевой столбец и измените, если нужно, ширину столбца подстановки; · на последнем шаге Мастера оставьте без изменения подпись, которую содержит столбец подстановок, и нажмите кнопку Готово; · подтвердите создание связи в ответ на запрос MS Access. 4. Добавьте в таблицу поле ДатаОтъезда, задайте для него тип данных – Дата/время. Установите для поля формат – Краткий формат даты. 5. Используя Мастер масок ввода, задайте для поля ДатаОтъезда маску ввода. Для этого: · щелкните мышью в поле свойства Маска ввода и нажмите кнопку Построителя […], которая находится справа поля; · подтвердите сохранение таблицы, имя таблицы – Группы; · в окне Создание масок ввода выберите значение Краткий формат даты; · на следующем шаге мастера выберите из списка символ заполнителя #. Для проверки ввода щелкните в поле Проба и введите произвольную дату; · на последнем шаге мастера нажмите кнопку Готово. Примечание: Маска ввода – это шаблон, позволяющий вводить в поле значения, имеющие одинаковый формат. Маска ввода автоматически изображает в поле постоянные символы, при вводе данных достаточно заполнить пустые позиции в маске ввода. Практический раздел 83 © 2011 Молчина Л.И. 6. Добавьте в таблицу поле КоличествоПутевок. Задайте тип данных – числовой. 7. Перейдите в область Свойств и установите формат поля – Целое, в строке свойства Условие на значение введите выражение >0. В поле свойства Сообщения об ошибке введите текст «Количество не может быть отрицательным числом». 8. Сохраните таблицу. Заполнять таблицу данными пока не следует. Задание для самостоятельного выполнения. В режиме Конструктора создайте таблицу Заявки согласно табл. 5. Указания к выполнению: чтобы определить составной ключ, необходимо в области маркирования мышью выделить все три поля и любым из описанных ранее способов сделать их ключевыми. Таблица 5 Структура таблицы Заявки Имя поля Тип данных Свойства поля Общие Подстановка НомерЗаявки Счетчик Индексированное поле – Да (совпадения не допускаются) - НомерГруппы Числовой Размер поля – Целое Индексированное поле – Да (совпадения допускаются) Поле НомерГруппы из таблицы Группы НомерПаспорта Текстовый Размер поля – 20 Индексированное поле – Да (совпадения допускаются) Поле НомерПаспорта из таблицы Клиенты КоличествоПутевок Числовой Размер поля – Байт Скидка, % Числовой Размер поля – Байт Необходимость визы Логически й Формат поля – Вкл/Выкл - Молчина Л.И. ЭУМК Модели данных и системы управления базами данных84 © 2011 Молчина Л.И. 2.1.4.2 Создание связей между таблицами Задание 5. Установите связи между таблицами согласно поставленной задаче. Порядок выполнения: 1. В окне базы данных выполните команду Сервис® Схема данных либо нажмите кнопку Схема данных на панели инструментов. 2. При первом обращении к схеме данных на экран автоматически будет выведено окно Добавление таблицы. Если необходимо добавить таблицы в существующую схему, нажмите кнопку Отобразить таблицу на панели инструментов либо выполните команду Связи® Добавить таблицу. 3. Выделите название таблицы Страны в списке и нажмите кнопку Добавить. Закройте окно Добавление таблицы. 4. В окне Схема Данных с помощью мыши перетяните поле КодСтраны из таблицы Страны на поле Страна таблицы Поездки. 5. В появившемся окне Изменение связей настройте параметры связи – установите флажки в области Обеспечение целостности данных (см. рис. 11) и нажмите кнопку Создать. Рис. 11. Окно Изменение связей Примечание: Включение флажка Обеспечение целостности данных позволяет защититься от случаев удаления записей из одной таблицы, при которых связанные с ними данные других полей окажутся без связи. Чтобы условие целостности могло Практический раздел 85 © 2011 Молчина Л.И. существовать, поле основной таблицы обязательно должно быть ключевым и оба поля должны иметь одинаковый тип. Флажки Каскадное обновление связанных полей и Каскадное удаление связанных полей обеспечивают одновременное обновление или удаление данных во всех подчиненных таблицах при их изменении в главной таблице. 6. Между таблицами Поездки и Группы связь уже была создана (в процессе настройки подстановочного поля), но для обеспечения целостности данных требуется ее дополнительная настройка. Измените связь между таблицами Страны и Поездки. Для этого: · выделите с помощью мыши связь между этими таблицами; · выполните команду Связи® Изменить связь; · в окне Изменение связей установите все нужные флажки и нажмите ОК. Примечание: Обратите внимание, что концы линии связи в окне схемы данных после включения флажка Обеспечения целостности данных помечены знаками "1" и "• ". Это означает, что в качестве значений поля из связанной таблицы могут выступать только значения из соответствующего поля основной таблицы и каждое значение из поля основной таблицы может много раз встречаться в поле связанной таблицы (связь "один ко многим"). Поле со стороны "• " является внешним ключом к основной таблице. 7. Дважды щелкните по линии связи между таблицами Заявки и Клиенты, в открывшемся окне Изменение связей установите нужные флажки. 8. По вышеописанной технологии определите связи между таблицами Заявки и Группы. В результате должна получиться схема данных, представленная на рис. 12. 9. Закройте окно схемы данных. 10.Закройте базу данных, выполнив команду Файл® Закрыть. Молчина Л.И. ЭУМК Модели данных и системы управления базами данных86 © 2011 Молчина Л.И. Рис. 12. Схема данных 2.1.5 Функциональные возможности СУБД Презентация: Функциональные возможности СУБД 2.1.6 Среда СУБД MS Access. Проектирование таблиц и работа с ними Задание 6. Заполните данными таблицу Группы данными. Отсортируйте таблицу в порядке возрастания по полю ДатаОтъезда. Порядок выполнения: 1. В окне базы данных выделите имя таблицы Группы и щелкните кнопку Открыть. 2. Введите 7 записей согласно табл. 15. Проверьте, как работает проверка вводимых значений в поле КоличествоДней. Таблица 6 Группы Номер группы Маршрут* Дата Отъезда** Количество путевок 1 Краков – Аквапарк – Соляные Копи – "Величка" 3 дня назад 25 2 Карловы Вары Через 8 дней 30 3 Прага – Париж – Ницца – Берлин Через 3 дня 25 4 Бостон Через 30 дней 10 5 Краков – Аквапарк – Соляные Копи – "Величка" Через 15 дней 25 Практический раздел 87 © 2011 Молчина Л.И. Номер группы Маршрут* Дата Отъезда** Количество путевок 7 Лондон – Йорк – Глазго – Амстердам – Берлин Через 10 дней 20 8 Рим – Венеция – Флоренция Через 20 дней 15 Внимание! *При вводе данных значения поля Маршрут следует выбирать из раскрывающегося списка, созданного подстановкой значений из таблицы Поездки. **В поле ДатаОтъезда указаны прошедшие либо будущие даты относительно текущей. Например, если сегодня 17.02.2005, то в первой записи в поле ДатаОтъезда следует ввести значение 14.02.2005. 3. Отсортируйте записи в таблице по полю ДатаОтъезда в порядке возрастания: · активизируйте нужное поле; · выполните команду Записи ® Сортировка® Сортировка по возрастанию или нажмите кнопку на панели инструментов Таблица в режиме таблицы. 4. Закройте таблицу Группы, сохранив изменения. Задание 7. Оформите таблицу Поездки. Выполните поиск записей по образцу и фильтрацию данных в таблице Поездки. Порядок выполнения: 1. В окне базы данных выполните двойной щелчок на имени таблицы Поездки. 2. Выполните команду Формат® Шрифт и установите для таблицы шрифт Arial, полужирный курсив, размер – 12 пт. 3. Выполните команду Формат® Режим таблицы и установите значения параметров диалогового окна Формат таблицы по своему усмотрению. Примечание: Для оформления таблицы можно использовать соответствующие кнопки на панели инструментов Формат (режим таблицы). 4. Измените ширину поля Маршрут таким образом, чтобы информация полностью помещалась в столбце. Для этого: · выделите весь столбец, щелкнув по его названию; Молчина Л.И. ЭУМК Модели данных и системы управления базами данных88 © 2011 Молчина Л.И. · выполните команд Формат® Ширина столбца® По ширине данных. 5. Откройте таблицу Поездки в режиме таблицы. 6. Найдите в таблице все записи, в которых поле ТипПоездки имеет значение Отдых. Для этого: · выполните команду Правка® Найти · в окне Поиск и замена введите в поле Образец значение, которое требуется найти; для поля Поиск в задайте значение Поездки:таблица, остальные параметры оставьте без изменения; · нажмите кнопку Найти далее, чтобы перейти к искомому значению; · после просмотра всех найденных записей закройте окно Поиск и замена. 7. Аналогично найдите в таблице все записи, в которых поле ВидТранспорта имеет значение а*. Символ * заменяет любое количество символов, т. е. должны быть найдены записи, в которых поле ВидТранспорта имеет значение авиа или автобус. 8. Используя фильтр по выделенному фрагменту, отберите все записи таблицы, которые содержат данные об экскурсионных поездках, т. е. в которых поле ТипПоедзки содержит значение экскурсия. Для этого: · активизируйте в таблице ячейку со значением экскурсия и выполните последовательность команд Записи® Фильтр® Фильтр по выделенному либо нажмите одноименную кнопку на панели инструментов Режим таблицы; · просмотрите результат фильтрации; · чтобы отменить фильтр, нажмите кнопку Удалить фильтр на панели инструментов либо выберите одноименную команду в контекстном меню. 9. Измените фильтр, просмотрите записи обо всех поездках, целью которых является отдых, размещение – отель****, вид транспорта – авиа: · нажмите кнопку Изменить фильтр на панели инструментов Режим таблицы. Access запоминает последний применяемый фильтр, поэтому в поле ТипПоездки будет отображаться значение Экскурсия; · в поле ТипПоездки раскройте список и выберите значение отдых, аналогично Практический раздел 89 © 2011 Молчина Л.И. установите значения для полей Размещение и ВидТранспорта; 10.Для просмотра результатов фильтрации нажмите кнопку Применение фильтра либо выполните команду Фильтр® Применить фильтр. 11.Отмените фильтр для таблицы. 12.Закройте таблицу Поездки. Задание для самостоятельного выполнения. Введите в таблицу Заявки записи таким образом, чтобы во все группы, за исключением группы №5, оформили заявки по несколько клиентов и несколько клиентов оформили заявки более 1 раза в разные группы. При вводе данных значения полей НомерПаспорта и НомерГруппы следует выбирать из списков, созданных подстановкой полей из таблиц Клиенты и Группы. Оформите таблицу по своему усмотрению. 2.1.7 Формирование запросов в СУБД MS Access Цель работы: 1) получить навыки конструирования запросов различного типа для выборки данных из таблиц и управления данными; 2) освоить технологию создания запросов с помощью Мастеров создания запросов. Задача. Требуется получить определенные сведения по установленным критериям из одной или нескольких таблиц базы данных. Также нужно получить новые данные, вычисляемые по значениям, хранящимся в таблице. В ряде случаев требуется удалять или обновлять записи в имеющихся таблицах базы данных и создать новую таблицу на основе условий отбора. Для решения поставленной задачи нужно сформировать запросы на выборку данных из таблиц и запросы действия для изменения данных в таблицах. Откройте из своей папки базу данных Туристическая фирма. Молчина Л.И. ЭУМК Модели данных и системы управления базами данных90 © 2011 Молчина Л.И. 2.1.7.1 Задание условий отбора в запросе Задание 1. Сформируйте запрос на выборку, выбирающий из таблицы Страны записи о странах, основной язык в которых – английский. Порядок выполнения: 1. В окне базы данных щелкните по ярлыку Запросы. 2. Дважды щелкните по ярлыку Создание запроса в режиме конструктора в окне базы данных. Откроется бланк запроса и окно Добавление таблицы. Примечание: Для добавления таблицы в бланк запроса можно выделить нужную таблицу и нажать кнопку Добавить или дважды щелкнуть мышью по таблице. Если нужно добавить несколько таблиц, эту операцию следует повторить для каждой таблицы. Для удаления таблицы из бланка запроса нужно выделить таблицу и нажать кнопку Delete. В верхней области окна конструктора запросов отображаются выбранные таблицы и связи между ними. Нижняя область – бланк запроса – отображает поля и условия вывода для данных из таблиц. 3. Добавьте в запрос таблицу Страны и закройте окно Добавление таблицы. 4. Перетащите поле Название из таблицы в строку Поле первого столбца бланка запроса. 5. В области таблиц дважды щелкните по полю Столица. Поле переместится в следующий свободный столбец бланка запроса. 6. В третьем столбце бланка запроса раскройте список в строке Поле и выберите поле ЯзыкСтраны. 7. Для задания условий отбора на пересечении строки Условие отбора и столбца ЯзыкCтраны наберите английский. 8. Запустите запрос на выполнение с помощью кнопки Запуск (!) на панели инструментов. 9. Сохраните запрос под именем Страны с английским языком. Задание 2. Сформируйте запрос на выборку для отбора записей из таблицы Страны о странах, основной язык в которых французский или итальянский. Порядок выполнения: 1. В окне базы данных нажмите кнопку Создать и в окне диалога Новый запрос Практический раздел 91 © 2011 Молчина Л.И. выберите пункт Конструктор. 2. Добавьте таблицу Страны в бланк запроса. 3. Включите поля Название, Столица, ЯзыкСтраны в бланк запроса. 4. Для задания условий отбора на пересечении строки Условие отбора и столбца ЯзыкCтраны наберите французский. Для задания второго условия отбора на пересечении строки или и столбца ЯзыкCтраны наберите итальянский. 5. Запустите запрос на выполнение, выполнив команду Запрос® Запуск. 6. Сохраните запрос под именем Страны с французским и итальянским языком. Задание 3. Создайте запрос на основе таблицы Поездки, который выбирает все поля таблицы и выводит все записи. Добавьте в запрос две новых записи. Порядок выполнения: 1. Создайте новый запрос в режиме Конструктора. 2. Чтобы включить в запрос все поля таблицы Поездки, переместите символ * из списка полей таблицы в первый столбец бланка запроса. 3. Сохраните запрос под именем Новые поездки и запустите на выполнение. 4. Нажмите кнопку Новая запись на панели инструментов Запрос (режим таблицы) либо выполните команду Записи® Ввод данных. Введите следующую запись: Код поездк и Страна Маршр ут Тип поездки Количе ство дней Размещение Вид транспорта Пита ние Стоимо сть 11 02 Оксфо рд Учеба 90 самостояте льно авиа нет 2768 5. Закройте окно запроса и в окне базы данных щелкните по ярлыку Таблицы. 6. Откройте таблицу Поездки и просмотрите записи. 7. Закройте таблицу и вернитесь к работе с запросами. Задание 4. Сформируйте запрос на выборку записей из связанных таблиц Клиенты, Заявки, Группы, Поездки и Страны, выбирающий записи о клиентах, которым нужна виза, с указанием страны и срока оформления. Порядок выполнения: Молчина Л.И. ЭУМК Модели данных и системы управления базами данных92 © 2011 Молчина Л.И. 1. Создайте новый запрос в режиме Конструктора. 2. Добавьте таблицы Клиенты, Заявки, Группы, Поездки и Страны в бланк запроса. 3. Из таблицы Клиенты в бланк запроса включите поля: НомерПаспорта, Фамилия, Имя и Отчество, из таблицы Страны – поле Название, из таблицы Группа включите поле ДатаОтъезда, а из таблицы Заявки – НеобходимостьВизы. 4. Для задания условий отбора в ячейке Условие отбора для поля НеобходимостьВизы наберите Да. 5. Для того чтобы столбец НеобходимостьВизы не отображался на экране, в строке Вывод на экран данного поля снимите флажок. 6. Запустите запрос на выполнение. 7. Сохраните запрос под именем Клиенты, которым нужна виза. Задание для самостоятельного выполнения. Сформируйте запрос на основе таблицы Поездки, выбирающий записи о поездках стоимостью < 300 Евро, продолжительностью > 10 дней и типом поездки – отдых. Сохраните запрос под именем Недорогой отдых. 2.1.7.2 Создание параметрических запросов Задание 5. Сформируйте запрос на выборку записей из таблицы Поездки, запрашивающий тип поездки и выдающий информацию о маршруте, количестве дней, транспорте, размещении, питании и стоимости. Порядок выполнения: 1. Создайте новый запрос в режиме Конструктора. 2. Добавьте таблицу Поездки в бланк запроса. 3. В бланк запроса включите поля: Маршрут, КоличествоДней, ВидТранспорта, Размещение, Питание, Стоимость и ТипПоездки. 4. В строке Условие отбора для поля ТипПоездки введите текст [Введите тип поездки]. Внимание! В параметрических запросах обращение должно быть задано в квадратных скобках! 5. Запустите запрос на выполнение. Практический раздел 93 © 2011 Молчина Л.И. 6. В появившемся диалоговом окне задайте одно из значений типа поездки (например, отдых). 7. Просмотрите записи. 8. Сохраните запрос под именем Поездки по типам. Задания для самостоятельного выполнения. Сформируйте параметрический запрос на основе связанных таблиц Страны и Поездки, запрашивающий название страны и выводящий полную информацию о поездках, организованных в эту страну. Сохраните запрос под именем Поездки по странам. 2.1.7.3 Групповые вычисления в запросе Задание 6. Сформируйте запрос, который позволяет определить, сколько раз каждый клиент воспользовался услугами фирмы. Порядок выполнения: 1. Нажмите кнопку Создать и в окне Новый запрос выберите способ создания Конструктор. 2. Добавьте таблицы Клиенты и Заявки в бланк запроса. 3. Из таблицы Клиенты в бланк запроса включите поле Фамилия, Имя, Отчество, а из таблицы Заявки – поле НомерЗаявки. 4. Выполните команду Вид® Групповые операции. 5. Щелкните мышью в строке Групповая операция поля НомерЗаявки. В раскрывающемся списке выберите функцию Count (статистическая функция Count вычисляет количество значений). 6. Сохраните запрос под именем Постоянные клиенты. 7. Запустите запрос на выполнение. 8. Просмотрите записи. Обратите внимание, что появилось поле Count_ НомерЗаявки, в котором подсчитывается количество заявок каждого клиента. Задание 7. Сформируйте запрос, который позволяет посчитать количество купленных путевок в каждую группу. Порядок выполнения: 1. Создайте новый запрос в режиме Конструктора. 2. Добавьте таблицы Группа и Заявки в бланк запроса. Молчина Л.И. ЭУМК Модели данных и системы управления базами данных94 © 2011 Молчина Л.И. 3. Из таблицы Группа в бланк запроса включите поле НомерГруппы, из таблицы Заявки – поле КоличествоПутевок. 4. Нажмите кнопку Групповые операции (математический знак суммы) на панели инструментов Конструктор запросов. 5. Щелкните мышью в строке Групповая операция в поле КоличествоПутевок. В раскрывающемся списке выберите функцию Sum (статистическая функция Sum суммирует значения определенного поля). 6. Чтобы изменить подпись поля Sum_КоличествоПутевок, установите курсор перед именем поля и наберите Куплено путевок: (имя поля должно быть следующим – Куплено путевок: КоличествоПутевок). 7. Сохраните запрос под именем Количество купленных путевок. 8. Запустите запрос на выполнение. 9. Просмотрите записи. Обратите внимание: поле, в котором подсчитывается количество купленных путевок называется Куплено путевок. 2.1.7.4 Запрос на удаление Задание 11. Сформируйте запрос на удаление записей из таблицы Группы. Необходимо удалить приехавшие группы (дата возвращения раньше текущей даты). Порядок выполнения: 1. Создайте новый запрос в режиме Конструктора. 2. Добавьте таблицу Группы и запрос Дата возвращения групп в бланк запроса. 3. Из таблицы Группы в бланк запроса включите все поля. Из запроса Дата возвращения групп в бланк запроса включите поле ДатаВозвращения. 4. Для задания условий отбора на дату возвращения в ячейке Условие отбора для этого поля наберите