Базы данных

Лекция 1. Раздел 1. Лекция 2. Лекция 3. Лекция 4. Лекция 5. Лекция 6. Лекция 7. Лекция 8. Лекция 9. Лекция 10. Лекция 11. Лекция 12. Раздел 2. Лекция 13. Лекция 14. Лекция 15. Лекция 16. Лекция 17. Лекция 18. Ведение. Основные понятия ТЕОРИЯ ПРОЕКТИРОВАНИЯ БАЗ ДАННЫХ Физическая и логическая независимость данных. Модели данных Развитие фактографических моделей Реляционная модель данных. Основные понятия Связанные отношения. Принципы поддержки целостности данных Реляционная алгебра. Теоретико-множественные операторы Реляционная алгебра. Специальные реляционные операторы Этапы проектирования баз данных Инфологическое проектирование Критерии выбора СУБД Переход к реляционной модели данных Теория нормализации ОРГАНИЗАЦИЯ БАЗ ДАННЫХ СУБД MS Access. Создание таблиц и определение связей Создание форм средствами MS Access Создание отчетов средствами MS Access Создание запросов на выборку средствами MS Access Итоговые и модифицирующие запросы Перекрестные запросы. Макросы. 4 8 8 12 16 20 24 29 35 41 46 50 53 58 58 67 76 82 90 97 1 Лекция 1. Основные понятия Основные понятия Рассмотрим основные понятия, связанные с развитием концепций баз данных. База данных (БД) – структурированная совокупность данных, организованную по определенным правилам. БД используются для хранения информации об объектах какой-либо предметной области. Предметная область - часть реального мира, подлежащая изучению с целью автоматизации. Предметную область можно представить как множество взаимосвязанных объектов. Объект (сущность) - это выделенный элемент предметной области, подлежащий хранению в БД. Другими словами - это «нечто, о чем мы хотим хранить информацию в БД». Объект может быть реальным (человек, населенный пункт, какой-либо предмет) и абстрактным (событие, счет покупателя). Для каждого объекта выделяют набор признаков (характеристик, свойств или атрибутов) которые позволяют описать объект в рамках выбранной предметной области. Если рассматривать человека как объект, о котором мы хотим хранить информацию в БД, то можно заметить что для предметных областей связанных с медициной наиболее значимыми наборами характеристик человека могут оказаться: рост, вес, пол и т.д. Для производства набор значимых характеристик человека иной: возраст, должность, рейтинг и т.д. Характеристики (свойства, атрибуты) – набор признаков определяющих объект для выбранной предметной области. Данные (в концепции БД) – это набор конкретных значений, параметров, характеризующих объект. Не следует путать характеристики и данные, например, «ВЕС» – это характеристика объекта, а 120кг – это конкретное значение (данные). Класс объектов - совокупность объектов, обладающих одинаковым набором свойств (характеристик). Пример. Секретарь учебного заведения должен учитывать контактную информацию об учащихся (адрес, телефон и т.д.). Эту информацию удобно расположить в таблице (таблица 1.1). Столбцы таблиц обычно называют полями, а строки – записями. Каждая запись таблицы (строка) содержит индивидуальные данные конкретного ученика – объекта. Заголовок таблицы представляет собой 2 набор свойств (характеристик). Все однотипные объекты (ученики) составят класс объектов. Можно сказать, что мы получили однотабличную БД. Таблица 1.1 – Контактная информация учащихся № п/п Фамилия Имя Телефон Адрес 1 2 3 … Александров Андреев Башкирова … Саша Степан Ольга … 5-55-57 5-15-23 5-50-44 … пр. Ленина, д. 25, кв. 17 ул. Чапаева, д. 18, кв. 8 ул. Северная, д. 117 … Система управления базами данных (СУБД) – это совокупность языковых и программных средств, предназначенных для создания, ведения и использования БД. СУБД позволяют структурировать, систематизировать и организовывать данные для компьютерного хранения и обработки. Системы управления базами данных (DataBase Management System – DBMS) являются основами практически любой информационной системы. Использование современных СУБД позволяет дать следующие преимущества: 1. Может быть сокращена избыточность в хранимых данных. 2. Может быть устранена возможность возникновения противоречивости хранимых данных. 3. Централизованное управление обеспечивает соблюдение стандартов в представлении данных, принятых в данной предметной области. 4. Могут быть выполнены условия безопасности данных. 5. Может поддерживаться целостность данных. Благодаря централизованному управлению, могут быть определены процедуры проверки, выполняющиеся при операциях запоминания. 6. Может быть обеспечена независимость данных и приложений, т.е. возможность расширения приложений независимо от базы данных и наоборот возможность расширения базы данных без влияния на приложения. Информационная система (ИС) - взаимосвязанная совокупность средств, методов и персонала, используемых для хранения, обработки и выдачи информации в интересах достижения поставленной цели. В большинстве случаев сегодня под ИС понимают автоматизированные информационные системы (АИС). АИС позволяют автоматизировать деятельность, повысить качество и достоверность обрабатываемой информации. Информационную основу ИС составляют хранящиеся в ней данные. В большинстве случаев это БД, а для управления данными используют СУБД. Контрольные вопросы 1. В чем причины появления систем обработки данных? 3 2. Определите роль дисциплины в профессиональной деятельности. 3. Определите роль дисциплины «Базы данных» при освоении других дисциплин выбранной специальности. 4. Что такое базы данных? 5. Что подразумевается под предметной областью? 6. Что такое «объект»? 7. Что такое «характеристики»? 8. Что представляют собой данные? 9. Что такое «класс объектов»? 10.В чем назначение СУБД? 11.Какие преимущества может дать использование СУБД? 12.Что такое ИС и в чем заключается ее назначение? 4 Раздел 1. ТЕОРИЯ ПРОЕКТИРОВАНИЯ БАЗ ДАННЫХ Лекция 2. Физическая и логическая независимость данных. Модели данных Физическая и логическая независимость данных В процессе исследований посвященных устройству СУБД, предлагались различные способы ее реализации. Наиболее жизнеспособной оказалась трехуровневая модель, определяющая следующие уровни абстракций для определения структуры СУБД (рисунок 2.1): внутренний (физический), концептуальный и внешний (логический). 1. Внешний уровень, отражает представления прикладного программиста или конечного пользователя и связан с тем, как отдельные пользователи представляют себе эти данные или «видение» собственных данных каждым отдельным приложением. 2. Концептуальный уровень отражает обобщенную модель предметной области (объектов реального мира). Концептуальный уровень дает наиболее полное представление о структурах данных. Концептуальная модель состоит из множества экземпляров различных типов объектов и связей (концептуальных записей). 3. Внутренний уровень близок к физической памяти и связан со способом физического хранения данных, например, каким способом данные располагаются на носителях. Внешний (логический) уровень Пользователь 1 и его приложение Пользователь 2 и его приложение Пользователь N и его приложение Концептуальный уровень (Центральное управляющее звено) Физический уровень (Физическое расположение БД) Рисунок 2.1 - Трехуровневая модель СУБД (ANSI) Эта архитектура была предложена ANSI (American National Standards Institute), ее основным достоинством является то, что она позволяет обеспечить логическую и физическую независимость при работе с данными. Логическая независимость предполагает возможность изменения одного приложения без корректировки других приложений, работающих с этой БД. Физическая независимость предполагает возможность изменения способа хранения, расположения или переноса данных на носителях (на 5 физическом уровне) без влияния на работоспособность всех приложений работающих с БД. Другими словами, если происходит какое-либо изменение в расположении данных на носителях, это никак не отражается на работе приложений внешнего уровня. И наоборот, если происходит какое-либо дополнение приложений внешнего уровня, то изменений на физическом уровне не происходит. Модели данных Одними из основополагающих в концепции БД являются обобщенные категории «данные и модель данных». Данные - это набор конкретных значений, параметров, характеризующих объект. Модель данных – это некоторая абстракция, прикладываемая к конкретным данным, позволяет пользователям трактовать их как информацию, то есть сведения, содержащие не только данные, но и взаимосвязь между ними. В соответствии с изложенной ранее трехуровневой архитектурой мы сталкиваемся с понятием модели данных по отношению к каждому уровню (Рисунок 2.2). Модели данных Дескрипторные Тезаурусные Иерархическая Файловая структура Фактографические модели Документальны е модели Модель Сущность связь (ER–модель) Диаграммы Бахмана Ориентированные на формат документа Физические модели Теоретикографовые Сетевая Теоретикомножественные Реляционная Странично – сегментная организация Даталогические модели Инфологические модели Объектноориентированные Бинарных ассоциаций Рисунок 2.2 - Классификация моделей данных Физическая модель данных оперирует категориями касающаяся организации внешней памяти и структур хранения, используемых в данной 6 операционной среде. В настоящий момент в качестве физических моделей используются различные методы размещения данных, основанные на файловых структурах: это организация файлов прямого и последовательного доступа, индексных файлов, файлов, использующих различные методы кэширования, взаимосвязанных файлов. Кроме того, в современных СУБД широко используют страничную организацию данных. Физические модели данных, основанные на страничной организации, являются наиболее перспективными. Наибольший интерес вызывают модели данных, используемые на концептуальном уровне. По отношению к ним внешние модели называются подсхемами и используют те же абстрактные модели, что и концептуальные модели данных. Кроме трех рассмотренных уровней абстракций при проектировании БД существует еще один уровень, предшествующий им. Модель этого уровня должна выражать информацию о предметной области в виде, независимом от используемой СУБД. Эти модели называют инфологическими или семантическими, и отражают естественный и удобный для разработчиков и других пользователей форме, связанной с описанием объектов предметной области, их свойств и взаимосвязей. Инфологические модели используются на этапе проектирования БД. Документальные модели данных соответствуют представлению о слабоструктурированной информации, ориентированной в основном на свободные форматы документов, текстов на естественном языке. Модели, основанные на языках разметки документов, связаны, прежде всего, со стандартным общим языком разметки — SGML (Standart Generalised Markup Language), который был утвержден ISO в качестве стандарта еще в 80-х годах. Этот язык предназначен для создания других языков разметки, он определяет допустимый набор тегов (ссылок), их атрибуты и внутреннюю структуру документа. Контроль за правильностью использования тегов осуществляется при помощи специального набора правил. С помощью SGML можно описывать структурированные данные, организовывать информацию, содержащуюся в документах, представлять эту информацию в некотором стандартизованном формате. Но ввиду некоторой своей сложности SGML использовался в основном для описания синтаксиса других языков (наиболее известным из которых является HTML), и немногие приложения работали с SGML-документами напрямую. Язык HTML позволяет определять оформление элементов документа и имеет некий ограниченный набор инструкций - тегов, при помощи которых осуществляется процесс разметки. Инструкции HTML в первую очередь предназначены для управления процессом вывода содержимого документа на экране программы-клиента и определяют этим самым способ представления документа, но не его структуру. В качестве элемента гипертекстовой базы данных, описываемой HTML, используется текстовый файл, который может легко передаваться по сети с использованием протокола HTTP. Эта особенность, а также то, что HTML является открытым стандартом и огромное количество пользователей имеет возможность применять возможности этого языка для оформления своих документов, безусловно, повлияли на рост популярности 7 HTML и сделали его сегодня главным механизмом представления информации в Интернете. Однако HTML сегодня уже не удовлетворяет в полной мере требованиям, предъявляемым современными разработчиками к языкам подобного рода. И ему на смену был предложен новый язык гипертекстовой разметки, мощный, гибкий и, одновременно с этим, удобный язык XML. XML (Extensible Markup Language) — это язык разметки, описывающий целый класс объектов данных, называемых XML-документами. Он используется в качестве средства для описания грамматики других языков и контроля за правильностью составления документов. То есть сам по себе XML не содержит никаких тегов, предназначенных для разметки, он просто определяет порядок их создания. Тезаурусные модели основаны на принципе организации словарей, содержат определенные языковые конструкции и принципы их взаимодействия в заданной грамматике. Эти модели эффективно используются в системахпереводчиках, особенно многоязыковых переводчиках. Принцип хранения информации в этих системах и подчиняется тезаурусным моделям. Дескрипторные модели - самые простые из документальных моделей, они широко использовались на ранних стадиях использования документальных баз данных. В этих моделях каждому документу соответствовал дескриптор — описатель. Этот дескриптор имел жесткую структуру и описывал документ в соответствии с теми характеристиками, которые требуются для работы с документами в разрабатываемой документальной БД. Например, для БД, содержащей описание патентов, дескриптор содержал название области, к которой относился патент, номер патента, дату выдачи патента и еще ряд ключевых параметров, которые заполнялись для каждого патента. Обработка информации в таких базах данных велась исключительно по дескрипторам, то есть по тем параметрам, которые характеризовали патент, а не по самому тексту патента. Контрольные вопросы 1. В чем разница между внутренним, концептуальным и внешним уровнями абстракции в трехуровневая модели СУБД (ANSI)? 2. В чем преимущество использования трехуровневая модели СУБД (ANSI)? 3. Что понимается под физической и логической независимостью данных? 4. Почему изменения на физическом уровне не отражаются на логическом уровне абстракции? 5. Что такое модель данных? 6. В чем назначение инфологической модели? 7. Что определяют физические модели? 8. В каких случаях используют документальные модели данных? 9. Какие языки основаны на документальной модели данных? 10.Что представляют собой тезаурусные модели данных? 11.Что представляют собой дескрипторные модели данных? 8 Лекция 3. Развитие фактографических моделей Несколько последних десятилетий появлялось множество СУБД, основанных на трех базовых моделях данных: иерархической, сетевой и реляционной. Последнее время активные работы ведутся в направлении постреляционных СУБД. Иерархическая модель Первые иерархические и сетевые СУБД были созданы в начале 60-х годов. Причиной послужила необходимость управления миллионами записей (связанных друг с другом иерархическим образом), например при информационной поддержке лунного проекта Аполлон. Из иерархических СУБД, самое большое распространение получила СУБД IMS (Information Management System компании IBM). Отношения в иерархической модели данных организованы в виде совокупностей деревьев, где дерево - структура данных, в которой тип сегмента потомка связан только с одним типом сегмента предка. Графически: Предок узел на конце стрелки, а Потомок - узел на острие стрелки (рисунок 3.1). В базах данных определено, что узлы - это типы записей, а стрелки представляют отношения один - к - одному или один - ко - многим. Рисунок 3.1 - Иерархическая база данных Если структура данных не соответствует строгой иерархии то использование иерархических СУБД становится невозможным. 9 Сетевая модель Сетевая модель являлась улучшенной иерархической моделью, в которой одна запись могла участвовать в нескольких отношениях предок/потомок, как показано на рисунке 3.2, это давало определенную гибкость. В сетевой модели такие отношения назывались множествами. Рисунок 3.2 - Сетевая модель данных В 1971 году на конференции по языкам систем данных был опубликован официальный стандарт сетевых баз данных, который известен как модель CODASYL. В 70-х годах независимые производители программного обеспечения реализовали сетевую модель в таких продуктах, как IDMS компании Cullinet, Total компании Cincom и СУБД Adabas, которые приобрели большую популярность. Тем временем IBM продолжили развитие своего проекта IMS. Как и иерархические базы данных, сетевые БД были очень жесткими. Наборы отношений и структуру записей приходилось задавать наперёд. Изменение структуры базы данных обычно означало перестройку всей базы данных. Реализации многих запросов иногда требовали переписывания кодов и, следовательно, вмешательства программистов. Реляционная модель Недостатки иерархической и сетевой моделей привели к появлению новой, реляционной модели данных, созданной Э. Ф. Коддом в 1970-1971 годах и вызвавшей всеобщий интерес. Реляционная модель была попыткой упростить структуру базы данных. В ней отсутствовали явные указатели на предков и потомков, а все данные были представлены в виде простых таблиц, разбитых на строки и столбцы. Реляционной называется база данных, в которой все данные, доступные пользователю, организованны в виде таблиц, а все операции над данными сводятся к операциям над этими таблицами. На сегодняшний день реляционная модель данных является наиболее популярной. На ее идеологии построены СУБД: FoxPro, Paradox, MS Access и т.д. Постреляционные модели Современные программные системы становятся сложнее, претендуя на решение глобальных задач, например таких, как создание единой системы 10 управления предприятием. При этом автоматизация отдельных операций или отделов фактически исчерпала свой потенциал, для создания таких систем реляционные СУБД могут оказаться малоэффективными. Все это послужило предпосылкой к созданию новых моделей – пострелляционных. Постреляционными моделями принято называть объектные или объектно-ориентированные модели данных. Один из идеологов ООСУБД Versant, очень кратко и точно сформулировала актуальность объектного подхода к базам данных: “Модель данных более близка сущностям реального мира. Объекты можно сохранить и использовать непосредственно, не раскладывая их по таблицам. Типы данных определяются разработчиком и не ограничены набором предопределенных типов”. Когда сложный объект заносится в реляционную базу, обязательна процедура декомпозиции его данных для их размещения в таблице. При чтении объекта из реляционной базы он собирается из отдельных элементов и только затем пригоден для использования. В объектных СУБД все иначе. Данные объекта, а также его методы помещаются в хранилище как единое целое. Объектная СУБД, как правило, поддерживает один или несколько объектно-ориентированных языков - C++, Java, Smalltalk, Object Lisp и т.п. Программист использует единый язык программирования для создания логики приложения, разработки интерфейса и общения с базой данных, а также для поддержания целостности данных. Структура объектной модели описываются с помощью трех базовых концепций:  инкапсуляция – возможность объединенного хранения данных и методами для их обработки внутри одного объекта. Таким образом, объекты можно рассматривать как самостоятельные сущности, отделенные от внешнего мира.  наследование - подразумевает возможность создавать из классов объектов новые классы объекты, которые наследуют структуру и методы своих предков, добавляя к ним черты, отражающие их собственную индивидуальность.  полиморфизм - различные объекты могут по разному реагировать на одинаковые внешние события в зависимости от того, как реализованы их методы. Основными достоинствами объектно-ориентированного подхода являются увеличение коэффициента повторно используемого кода, а также возможность модификации и развития. Применительно к базам данных, это положение позволяет начать проектирование будущей системы, не имея исчерпывающего представления о предметной области. Поскольку получение детальной информации о предметной области - процесс весьма трудоёмкий, то это может сократить сроки разработки систем, а, следовательно, и их стоимость. Объектно-ориентированным моделям также присущи и недостатки, которые связанны с большой стоимостью и сложностью БД, кроме того, сложен и сам процесс перехода от имеющихся реляционных СУБД к объектным или объектно-ориентированным. 11 В заключении необходимо отметить, что всем моделям присущи свои недостатки, возможно, качественно новым направлением является интеграции объектно-ориентированной технологии и реляционной модели данных. Слияние этих ведущих направлений открывает новые возможности как в процессе проектирования баз данных, так и на стадиях эксплуатации и модернизации. Возможно, это направление позволит максимально использовать преимущества реляционной и объекто-ориентированной моделей. Контрольные вопросы 1. В чем различие между иерархической и сетевой моделями данных? 2. В чем заключаются ограничения иерархической модели данных? 3. Какие общие недостатки присущи иерархической и сетевой моделям? 4. В чем суть реляционной модели данных? 5. Какие модели называют постреляционными и почему? 6. Какие преимущества имеют постреляционные модели данных? 7. Какие принципы лежат в основе объектной модели? 8. Какие языки используют в ОО СУБД и почему? 9. Почему ОО БД не получают большого распространения? 10.Исчерпали ли свои возможности РСУБД? 12 Лекция 4. Реляционная модель данных. Основные понятия Теоретической основой модели стала теория отношений, основу которой заложили два логика – американец Чарльз Содерс Пирс и немец Эрнст Шредер. Позднее, в 1970-1971 годах американский математик Э. Ф. Кодд, основываясь на трудах предшественников, сформулировал основные понятия и ограничения реляционной модели, ограничив набор операций семью основными и одной дополнительной. Предложения Кодда были настолько эффективны для систем баз данных, что он был удостоен премии Тьюринга в области теоретических основ вычислительной техники. Реляционная модель данных (РМД) наиболее проста и имеет в основе развитый математический аппарат (реляционная алгебра или реляционное исчисление), поэтому она фактически стала стандартной моделью представления данных в СУБД. Основными понятиями реляционных баз данных являются тип данных, домен, атрибут, кортеж, первичный ключ и отношение. Базы данных, между отдельными таблицами которой существуют связи, называются реляционными (от relation – отношение). Таким образом, реляционная модель данных представляет информацию в виде совокупности взаимосвязанных таблиц, которые принято называть отношениями или реляциями. Связанные отношения взаимодействуют по принципу главная (master) – подчиненная (detail). Главную таблицу часто называют родительской, а подчиненную – дочерней. Одна и та же таблица может быть главной по отношению к одной таблице БД и дочерней по отношению к другой. Отношение – реляционная таблица. Тип данных. Понятие тип данных в реляционной модели полностью эквивалентно соответствующему понятию в алгоритмических языках. Тип данных определяет возможные способы обработки данных и место, необходимое для их хранения. Набор поддерживаемых типов данных определяется СУБД и может сильно различаться в разных системах. Однако существуют типы данных общие для всех СУБД:  целочисленный тип;  вещественный;  строковый;  специализированный тип данных для денежных величин;  специальные типы данных для хранения даты или даты и времени;  типы двоичных объектов (данный тип не имеет аналога в языка программирования; обычно для его обозначения используется аббревиатура BLOB – Binary Large Object). Домен – это множество атомарных значений одного и того же типа. Домены представляют собой пользовательский тип. Атрибут – это характеристика объекта (сущности). Атрибуты имеют имена, через которые к ним производится обращение. Имя атрибута должно быть уникальным внутри отношения. Схема отношения - это именованное множество пар {имя атрибута, имя домена (или типа, если понятие домена не поддерживается)}. 13 Степень отношения – это число атрибутов отношения. Отношение степени один называют унарным, степени два – бинарным, степени три – тернарным, степени n – n-арным. Схема базы данных (в структурном смысле) - это набор именованных схем отношений с указанием взаимосвязей между ними. Кортеж (схемы отношения) представляет собой множество пар (имя атрибута, значение), которое содержит одно значение каждого имени атрибута, принадлежащего схеме отношения. «Значение» является допустимым значением домена данного атрибута (или типа, если домены не поддерживается). Степень кортежа отношения (число элементов в нем) совпадает со степенью соответствующей схемы отношения. Ключи отношения. Отношение с математической точки зрения является множеством и не может содержать совпадающих элементов, т.е. в любой момент времени никакие два кортежа отношения не могут быть дубликатами друг друга. Таким образом, в отношении должен присутствовать некоторый атрибут (или набор атрибутов), однозначно определяющий каждый кортеж отношения и обеспечивающий уникальность строк таблицы. Такой атрибут (или набор атрибутов) называют первичным ключом отношения. Свойства первичного ключа:  уникальность: в любой момент времени никакие два кортежа отношения не должны иметь одного и того же значения;  минимальность: ни один из атрибутов не может быть исключен из набора атрибутов первичного ключа, без нарушения свойств уникальности. Типы данных Строковый Числовой Домен {M,Ж} Первичный ключ № п/п Фамилия Имя Пол Рост Вес Атрибуты 1 2 3 … Александров Андреев Башкирова … Саша Степан Ольга … М М Ж … 170 176 164 … 70 75 56 … Кортежи Рисунок 4.1 – Основные понятия РМД, на примере фрагмента отношения «Студент» для медпункта. В зависимости от количества атрибутов, ходящих в ключ, различают простые и сложные (составные) ключи. Простой ключ – ключ, одержащий только один атрибут. Как правило, в качестве него используют самый короткий и простой из возможных типов данных 14 (целочисленный тип), при этом операции использующие ключ (операции объединения) выполняются значительно быстрее. Сложный (составной) ключ – ключ, состоящий из нескольких атрибутов. Суперключ – сложный ключ, с большим числом столбцов, не удовлетворяющий свойству минимальности. Используется крайне редко, когда избыточность может оказаться полезной пользователю. С точки зрения информативности атрибута (или нескольких атрибутов) составляющего первичный ключ, различают искусственные и естественные ключи. Искусственный или суррогатный ключ – ключ создаваемый самой СУБД или пользователем с помощью некоторой процедуры, который сам по себе не одержит информации. Используется для создания уникальности идентификаторов строк. Им так же заменяют слишком сложные ключи. Как правило, пользователю они не показываются. Естественный ключ – ключ, содержащий только значимые атрибуты, т.е. содержащий информацию. К достоинствам естественных ключей можно отнести следующие: они несут вполне определенную информацию, и их использование не приводит к необходимости добавлять к таблице атрибуты, значения которых для пользователя не несут никакого смысла и используются только для связи между отношениями, что позволяет получить более компактную форму таблиц. Основным же недостатком естественных ключей является то, что их использование весьма затруднительно в случае изменения предметной области. Значения атрибутов первичного ключа не должны изменяться, т.е. однажды заданное значение первичного ключа для кортежа не может быть изменено. Это требование необходимо для поддержания ссылочной целостности базы данных, т.к. связь между отношениями обычно устанавливается по первичному ключу. Как правило, для избежания подобных проблем в отношения водятся искусственные ключи. Другим недостатком естественных ключей является то, что, как правило, они являются составными и содержат строковые атрибуты, что сказывается на скорости выполнения операций над данными и в этом случае так же удобнее бывает вводить суррогатные ключи. В любой из таблиц может оказаться несколько наборов атрибутов, которые можно выбрать в качестве ключа, такие наборы называются потенциальными и альтернативными ключами. Вторичные ключи – ключи, имеющие комбинации атрибутов отличные от комбинации атрибутов первичного ключа. Они могут не обладать свойством уникальности. Перекрывающиеся ключи – сложные ключи, которые имеют один или несколько общих столбцов. 15 Контрольные вопросы Для чего необходим тип данных? Чем отличается домен от типа данных? Что такое атрибут? Как можно представить схему отношения? Что такое схема базы данных? Что такое картеж? В чем заключается назначение первичного ключа? Какие основные свойства первичного ключа вы знаете и в чем их смысл? 9. Назовите классификацию ключей по количеству входящих в него атрибутов. 10. Назовите классификацию ключей с точки зрения информативности входящих в них атрибутов. 11. В чем причины использования искусственных ключей? 12.Что такое потенциальный ключ? 13.В чем различие вторичных и первичных ключей? 1. 2. 3. 4. 5. 6. 7. 8. 16 Лекция 5. целостности данных Связанные отношения. Принципы поддержки В реляционной модели данные представляются в виде совокупности взаимосвязанных таблиц. Таким образом, еще одним важным понятием реляционной модели является связь между отношениями. В связях между отношениями используют ключи, и в зависимости от типа ключей участвующих в связях возникают различные типы связей. Основным условием связи между отношениями является совпадение доменов (или типов, если домены не используются) ключей используемых в связи. При этом ключ связи в подчиненном отношении называется внешним ключом. Иногда внешний ключ может ссылаться на ту же таблицу, которой он принадлежит, в этом случае он будет называться рекурсивным. Типы связей между отношениями Различают три основных типа связей между таблицами в РМД:  один-к-одному (1:1) – каждому кортежу одного отношения соответствует только один кортеж другого отношения (рисунок 5.1); Таблица «Сотрудники» Таблица «Информация о сотрудниках» № ФИО Должность Отдел № Год рожд. Детей … 1 2 3 … Федотов А.А. Фролов С.А. Сидоров В.В. … бухгалтер инженер инженер … 3 2 2 … 1 2 3 … 1960 1970 1975 … 2 1 2 … … … … … Рисунок 5.1 – тип связи один-к-одному  один-ко-многим (1:М) – одному кортежу главного отношения соответствует несколько кортежей подчиненного отношения (рисунок 5.2); Таблица «Товары» № 1 2 3 … Наименование Сахар Макароны Куры … Таблица «Отпуск товаров» Единица Цена № Дата Кол-во кг кг кг … 22,00р. 13,50р. 50,00р. … 1 1 2 3 1 23.08.05 24.08.05 24.08.05 24.08.05 25.08.05 50 85 52 35 72 Рисунок 5.2 – тип связи один-ко-многим  многие-ко-многим (М:М) – одному кортежу одного отношения соответствует множество кортежей другого отношения и наоборот (рисунок 5.3). 17 Таблица «Блюдо» Таблица «Продукт» № Наименование № 1 2 … Салат «Зимний» Салат «Сельдь под шубой» … 1 2 3 4 … … Наименование Яйцо Майонез Морковь Свекла … Рисунок 5.3 – тип связи многие-ко-многим Связь один-к-одному встречается на много реже связи один-ко-многим, ее используют, если не хотят чтобы основная таблица «распухала» от второстепенной информации. Кроме того, считается, что БД в состав которых входят подобные связи не могут считаться полностью нормализованными. Тип связи один-ко-многим является самым распространенным в реляционных БД, он позволяет моделировать иерархические структуры данных. Связь многие-ко-многим встречается достаточно часто, однако РСУБД не поддерживают этот тип связи на уровне индексов и ссылочной целостности, но позволяют реализовывать его неявно. Считается, что БД всегда можно перестроить так, чтобы любая связь многие-ко-многим, была преобразована в одну и более связи один-ко-многим (рисунок 5.4). Промежуточная таблица Таблица «Блюдо» № Наименование 1 2 … Салат «Зимний» Салат «Сельдь под шубой» … №Б №П 1 1 2 2 2 … 1 2 1 3 4 … Таблица «Продукт» № 1 2 3 4 … Наименование Яйцо Майонез Морковь Свекла … Рисунок 5.4 – преобразование связи многие-ко-многим Значения промежуточной таблицы наглядно показывают, какое блюдо использует какой продукт. Условия целостности данных Чтобы информация, хранящаяся в БД была однозначной и непротиворечивой и сохраняла свою информативность, в реляционной модели устанавливаются некоторые ограничительные условия. Ограничительные условия – это правила, определяющие возможные значения данных. Они обеспечивающие логическую основу для поддержания корректности значений данных и позволяют свести к минимуму ошибки, возникающие при обновлении и обработке. Важнейшими ограничениями целостности данных являются:  категорийная целостность;  ссылочная целостность. 18 Ограничение категорийной целостности заключается в следующем. Кортежи отношения представляют в БД элементы определенных объектов реального мира, в соответствии с терминологией реляционных СУБД, категорий. Первичный ключ таблицы должен однозначно определять каждый кортеж и, следовательно, каждый элемент категории. Нарушение категорийной целостности возникает при попытке внести в отношение кортежи с неопределенным значением первичного ключа, т.е. можно сказать неизвестной категории. Поэтому картеж не может быть занесен в БД до тех пор, пока не будут определены все атрибуты ее первичного ключа. Ограничения, накладываемые на внешние ключи для обеспечения целостности данных, называются ссылочной целостностью. Другими словами, если две таблицы связаны между собой, то внешний ключ дочерней таблицы должен содержать только те значения, которые уже имеются среди значений первичного ключа родительской таблицы, в противном случае будет нарушено условие ссылочной целостности данных. Для соблюдения ссылочной целостности при вставке новых кортежей или модификации значений внешнего ключа в существующих кортежах, необходимо следить за корректностью значений внешнего ключа. Обычно СУБД следит за уникальностью значений первичного ключа и корректностью значений внешних ключей, следовательно, за котегорийной и ссылочной целостностью. Правила сохранения ссылочной целостности могут выбираться разработчиком в зависимости от предметной области разрабатываемой БД и возможностей СУБД. При удалении кортежей из отношения, на которые имеется ссылка, можно использовать один из трех вариантов, каждый из которых поддерживает ссылочную целостность:  запрещать удаление кортежа, на который существует ссылка в подчиненном отношении (в этом случае для удаления кортежа главного отношения, необходимо удалить все ссылающиеся на него кортежи, либо изменить значения их внешнего ключа);  при удалении кортежа, на который имеются ссылки, во всех ссылающихся кортежах значение внешних ключей автоматически станет неопределенным;  (каскадное удаление) при удалении кортежа из отношения, на который имеются ссылки, в подчиненных отношениях, автоматически удаляются все ссылающиеся кортежи подчиненных таблиц. При обновлении значений в первичном ключе кортежа главной таблицы могут использоваться подобные правила:  запрещать изменения значений в первичном ключе, на который существуют ссылки;  (каскадное обновление) при изменении значений в первичном ключе главной таблицы, на который имеются ссылки, автоматически произойдет обновление значений внешних ключей ссылающихся на него кортежей. 19 Контрольные вопросы Что такое внешний ключ? Каковы условия соединения ключей отношений? Какие существуют типы связей? Какой тип связи получится, если в качестве внешнего ключа будет выступать первичный ключ? 5. Какой тип связи установится при использовании не уникального внешнего ключа? 6. Как реализуется связь многие-ко-многим в РСУБД? 7. Что такое «ограничительные условия»? 8. Что такое «категорийная целостность»? 9. Что такое «ссылочная целостность»? 10. Какие правила позволяют сохранить ссылочную целостность при удалении? 11. Какие правила позволяют сохранить ссылочную целостность при обновлении? 12. Что произойдет, если СУБД не будет поддерживать категорийную или ссылочную целостность? Лекция 6. Реляционная алгебра. Теоретико-множественные операторы 1. 2. 3. 4. Доступ к реляционным данным осуществляется при помощи реляционной алгебры. В реализациях конкретных реляционных СУБД сейчас не используется в чистом виде ни реляционная алгебра, ни реляционное исчисление. Фактическим стандартом доступа к реляционным данным стал язык SQL (Structured Query Language). Язык SQL представляет собой смесь операторов реляционной алгебры и выражений реляционного исчисления, использующий синтаксис, близкий к фразам английского языка и расширенный дополнительными возможностями, отсутствующими в реляционной алгебре и реляционном исчислении. Вообще, язык доступа к данным называется реляционно-полным, если он по выразительной силе не уступает реляционной алгебре, т.е. любой оператор реляционной алгебры может быть выражен средствами этого языка. Именно таким и является язык SQL. Практически все операции реляционной модели предназначены для организации запросов к БД в терминах отношений. Эти запросы относятся к включению, соединению, выборке кортежей соответствующих отношений. Традиционно определяют восемь реляционных операторов, объединенных в две группы. Теоретико-множественные операторы:  Объединение  Пересечение  Вычитание  Декартово произведение Специальные реляционные операторы: 20  Выборка  Проекция  Соединение  Деление Не все они являются независимыми, т.е. некоторые из этих операторов могут быть выражены через другие реляционные операторы. Отношения, совместимые по типу Некоторые реляционные операторы (например, объединение) требуют, чтобы отношения имели одинаковые заголовки. Действительно, отношения состоят из заголовка и тела. Операция объединения двух отношений есть просто объединение двух множеств кортежей, взятых из тел соответствующих отношений. Но будет ли результат отношением? Во-первых, если исходные отношения имеют разное количество атрибутов, то, очевидно, что множество, являющееся объединением таких разнотипных кортежей нельзя представить в виде отношения. Во-вторых, пусть даже отношения имеют одинаковое количество атрибутов, но атрибуты имеют различные наименования. Как тогда 21 определить заголовок отношения, полученного в результате объединения множеств кортежей? В-третьих, пусть отношения имеют одинаковое количество атрибутов, атрибуты имеют одинаковые наименования, но определенны на различных доменах. Тогда снова объединение кортежей не будет образовывать отношение. Определение. Будем называть отношения совместимыми по типу, если они имеют идентичные заголовки, а именно:  отношения имеют одно и то же множество имен атрибутов, т.е. для любого атрибута в одном отношении найдется атрибут с таким же наименованием в другом отношении;  атрибуты с одинаковыми именами определены на одних и тех же доменах (или типах, если домены не поддерживаются). Некоторые отношения не являются совместимыми по типу, но после переименования атрибутов могут ими стать, для этого можно использовать вспомогательный оператор переименования атрибутов. Теоретико-множественные операторы Объединением двух совместимых по типу отношений А и В называется отношение с тем же заголовком, что и у отношений А и В, и телом, состоящим из совокупности кортежей обоих отношений. Синтаксис операции объединения: A UNION B (A  В) Замечание. Объединение, как и любое отношение, не может содержать одинаковых кортежей. Поэтому, если некоторый кортеж входит и в отношение А, и отношение В, то в объединение он входит один раз. Пусть даны два отношения А (таблица 6.1) и В (таблица 6.2) с информацией о сотрудниках: Таблица 6.1 - Отношение А Табельный номер 1 2 3 Фамилия Зарплата Иванов 1000 Петров 2000 Сидоров 3000 Таблица 6.2. Отношение В Табельный номер 1 2 4 Фамилия Зарплата Иванов 1000 Пушников 2500 Сидоров 3000 В результате операции объединения (A  В) будет получено отношение C с тем же заголовком что и у отношений А и В (таблица 6.3): Отношение С, не наследует первичного ключа. Поэтому, в объединении отношений А и В атрибут «Табельный номер» может содержать дубликаты значений. Наследование ключей противоречило бы понятию объединения как 22 «объединению множеств». Конечно, объединение отношений А и В имеет, как и любое отношение, потенциальный ключ, например, состоящий из всех атрибутов. Таблица 6.3 - Результирующее отношение (A  В) Табельный номер 1 2 3 2 4 Фамилия Зарплата Иванов 1000 Петров 2000 Сидоров 3000 Пушников 2500 Сидоров 3000 Вообще, никакие реляционные операторы не передают результирующему отношению никаких данных о потенциальных ключах. Причина заключается в том, что потенциальный ключ - семантическое понятие, отражающее различимость объектов предметной области. Наличие потенциальных ключей не выводится из структуры отношения, а явно задается для каждого отношения, исходя из его смысла. Реляционные же операторы являются формальными операциями над отношениями и выполняются одинаково, независимо от смысла данных, содержащихся в отношениях. Поэтому, реляционные операторы ничего не могут «знать» о смысле данных. Трактовка результата реляционных операций - дело пользователя. Пересечением двух совместимых по типу отношений А и В называется отношение с тем же заголовком, что и у отношения А и В, и телом, состоящим из кортежей, принадлежащих одновременно обоим отношениям. Синтаксис операции пересечения: A INTERSECT B (A  В) Для исходных отношений (таблицы 6.1 и 6.2) пересечение примет вид (таблица 6.4): Таблица 6.4 - Результирующее отношение (A  В) Табельный номер 1 Фамилия Иванов Зарплата 1000 Вычитанием двух совместимых по типу отношений А и В называется отношение с тем же заголовком, что и у отношений А и В, и телом, состоящим из кортежей, принадлежащих отношению А и не принадлежащих отношению В. Синтаксис операции вычитания: A MINUS B (A \ В) Для исходных отношений (таблицы 6.1 и 6.2) результат вычитания примет вид (таблица 6.5): Таблица 6.5 - Результирующее отношение (A \ В) Табельный номер 2 3 Фамилия Зарплата Петров 2000 Сидоров 3000 23 Декартовым произведением двух отношений А и В называется отношение С полученное сцеплением их заголовков и кортежей соответствующих отношений, причем каждому кортежу отношения А должны быть противопоставлены все кортежи отношения В Синтаксис операции декартового произведения: A TIMES B (A  В) Пусть даны два отношения с информацией о поставщиках А и деталях В. Пример. Пусть даны два отношения А и В с информацией о поставщиках и деталях (таблицы 6.6 и 6.7). Тогда декартово произведение отношений А и В примет вид указанный в таблице 6.8. Таблица 6.6 - Отношение А (Поставщики) Номер поставщика 1 2 3 Наименование поставщика Иванов Петров Сидоров Таблица 6.7 - Отношение В (Детали) Номер детали 1 2 3 Наименование детали Болт Гайка Винт Таблица 6.8 - Результирующие отношение (A  В) Номер Наименование поставщика поставщика 1 Иванов 1 Иванов 1 Иванов 2 Петров 2 Петров 2 Петров 3 Сидоров 3 Сидоров 3 Сидоров Номер детали 1 2 3 1 2 3 1 2 3 Наименование детали Болт Гайка Винт Болт Гайка Винт Болт Гайка Винт Замечания: 1. Мощность произведения A  B равна произведению мощностей отношений А и В, т.к. каждый кортеж отношения А соединяется с каждым кортежем отношения В. 2. Если в отношениях А и В имеются атрибуты с одинаковыми именами, то перед выполнением операции такие атрибуты необходимо переименовать. 3. Перемножать можно любые два отношения, совместимость по типу при этом не требуется. 24 4. Декартово произведение не дает никакой новой информации, по сравнению с предыдущими операциями, однако она важна для выполнения специальных реляционных операций. Контрольные вопросы 1. Какие отношения называются совместимыми по типу? 2. В чем смысл реляционного оператора «Объединение»? 3. В чем причины утраты первичного ключа при использовании реляционных операций? 4. В чем смысл реляционного оператора «Пересечение»? 5. В чем смысл реляционного оператора «Вычитание»? 6. Почему невозможно использование операций «Объединения», «Пересечения» и «Вычитания», если исходные отношения не совместимы по типу? 7. В чем смысл реляционного оператора «Декартово произведение»? 8. Чему равна мощность декартова произведения? 25 Лекция операторы 7. Реляционная алгебра. Специальные реляционные С практической точки зрения, специальные реляционные операции имеют большее практическое значение по сравнению с теоретикомножественными. Выборкой (ограничением, селекцией или фильтрацией) на отношении А, с условием С называется отношение с тем же заголовком, что и у отношения А, и телом, состоящем из кортежей, значения атрибутов которых при подстановке в условие С дают значение ИСТИНА. С - логическое выражение, в которое могут входить атрибуты отношения А и (или) скалярные выражения. В простейшем случае условие С имеет вид ХY, где  - один из операторов сравнения (, , , , ,  и т.д.), а Х и Y – атрибуты отношения А или скалярные значения. Такие выборки называются  - выборки (тэта-выборки) или  - селекция,  - ограничения. Синтаксис операции выборки: А WHERE C, где С – условие выборки, или ХY. Пусть дано отношение А с информацией о сотрудниках (таблица 6.1), необходимо выбрать всех сотрудников с зарплатой менее 3000, в этом случае выполняем выборку А WHERE Зарплата < 3000, результат выборки в таблице 7.1: Таблица 7.1 - Результат операции А WHERE Зарплата<3000 Табельный номер 1 2 Фамилия Зарплата Иванов 1000 Петров 2000 Смысл операции выборки очевиден - выбрать кортежи отношения, удовлетворяющие некоторому условию. Таким образом, операция выборки дает «горизонтальный срез» отношения по некоторому условию. Проекцией отношения А по атрибутам (X,Y,…,Z), где каждый из атрибутов принадлежит отношению А, называется отношение с заголовком (X,Y,…,Z) и телом, содержащим кортежи соответствующих атрибутов Синтаксис проекции: А[X,Y,…,Z] Для отношения А (таблица 6.1) результатом проекции А [Фамилия, Зарплата] таблица 7.2: Таблица 7.2 - Результат операции А [Фамилия, Зарплата] Фамилия Зарплата Иванов 1000 Петров 2000 Сидоров 3000 26 Видно, что операция проекции выполняет «вертикальный срез» отношения, в котором будут удалены все возникшие при таком срезе дубликаты кортежей. Соединение. Операция соединения отношений, наряду с операциями выборки и проекции, является одной из наиболее важных реляционных операций. Обычно рассматривается несколько разновидностей операции соединения:  общая операция соединения;  -соединение (тэта-соединение);  экви-соединение;  естественное соединение. Наиболее важным из этих частных случаев является операция естественного соединения. Все разновидности соединения являются частными случаями общей операции соединения. Соединением отношений А и В по условию С называется отношение образованное последовательностью операций декартова произведения и выборки: (A  B) WHERE C, где С представляет собой логическое выражение, в которое могут входить атрибуты отношений А и В и (или) скалярные выражения. Если в отношениях А и В имеются атрибуты с одинаковыми наименованиями, то перед выполнением соединения такие атрибуты необходимо переименовать. Тэта – соединение. Пусть отношение А содержит атрибут Х, отношение В содержит атрибут Y, а  - один из операторов сравнения (, , , , ,  и т.д.). Тогда -соединением отношения А по атрибуту Х с отношением В по атрибуту Y называют отношение (A  B) WHERE XY Это частный случай операции общего соединения. Иногда, для операции соединения применяют более короткий синтаксис A[XY]B. Экви-соединение является наиболее важным частным случаем тэтасоединения, когда тэта является просто равенством и имеет следующий синтаксис: A[X=Y]B или (A  B) WHERE X=Y. Пусть даны два отношения А и В. Отношение А (таблица 7.3) - данные о товарах, отношение В (таблица 7.4) - данные о продаже товаров. Необходимо определить, когда и в каком количестве отпускались товары со склада. Таблица 7.3 - Отношение А, «Товары» Код товара 1 2 Товар Сахар Макароны Единица кг кг Цена единицы 16р. 14р. 27 Таблица 7.4 - Отношение В, «Отпуск товаров» Код тов. 1 2 2 Дата продажи 12.07.02 12.07.02 12.07.02 Количество 10 15 3 Таблица 7.5 - Соединение (A  B) WHERE А.Код товара = В.Код тов. Код товара 1 1 1 2 2 2 Товар Единица Сахар Сахар Сахар Макароны Макароны Макароны кг кг кг кг кг кг Цена единицы 16р. 16р. 16р. 16р. 16р. 16р. Код тов. 1 2 2 1 2 2 Дата продажи 12.07.02 12.07.02 12.07.02 12.07.02 12.07.02 12.07.02 Количество 10 15 3 10 15 3 Таблица 7.5 представляет собой декартово произведение двух отношений, в котором темным выделены кортежи, для которых не выполнится условие выборки А.Код товара = В.Код тов., следовательно, они будут вычеркнуты из окончательного результата (таблица 7.6). Таблица 7.6 – Окончательный результат соединения (A  B) WHERE А.Код товара = В.Код тов. Код товара 1 2 2 Товар Единица Сахар Макароны Макароны кг кг кг Цена единицы 16р. 16р. 16р. Код тов. 1 2 2 Дата продажи 12.07.02 12.07.02 12.07.02 Количество 10 15 3 Естественное соединение Пусть даны отношения А(А1, А2,…,Аn, Х1, Х2,…,Хр) и В(Х1, Х2,…,Хр, В1, В2,…,Вm), имеющие одинаковые атрибуты Х1, Х2,…,Хр (т.е. атрибуты с одинаковыми именами и определенные на одинаковых доменах). Тогда естественным соединением отношений А и В называется отношение с заголовком (А1, А2,…,Аn, Х1, Х2,…,Хр, В1, В2,…,Вm), и телом, содержащим множество соответствующих кортежей. Естественное соединение настолько важно, что для него используют специальный синтаксис: A JOIN B. Замечания:  В синтаксисе естественного соединения не указываются, по каким атрибутам производится соединение. Естественное соединение производится по всем одинаковым атрибутам. 28  Естественное соединение эквивалентно следующей последовательности реляционных операций: 1. Переименовать одинаковые атрибуты в отношениях 2. Выполнить декартово произведение отношений 3. Выполнить выборку по совпадающим значениям атрибутов, имевших одинаковые имена 4. Выполнить проекцию, удалив повторяющиеся атрибуты 5. Переименовать атрибуты, вернув им первоначальные имена  Можно выполнять последовательное естественное соединение нескольких отношений. Естественное соединение (как и соединение общего вида) обладает свойством ассоциативности, т.е. (A JOIN B) JOIN С = A JOIN (B JOIN С), поэтому его можно записать, опуская скобки A JOIN B JOIN С. Применяя естественное соединение, результат, полученный в таблице 7.6, можно было получить операцией A JOIN B, но с одним условием, атрибут отношения В используемый для связи с отношением А должен иметь имя совпадающее с атрибутом связи отношения А (т.е. Код товара). Деление. Пусть даны отношения А(Х1,Х2,…,Хn,Y1,Y2,…,Ym) иB(Y1,Y2,…,Ym), причем атрибуты (Y1,Y2,…,Ym) - общие для двух отношений. Делением отношений А на В называется отношение с заголовком (Х1,Х2,…,Хn) и телом, содержащим множество кортежей (х1,х2,…,хn), только таких, для которых найдутся все кортежи (y1,y2,…,ym)В, в отношении А. Синтаксис операции деления: А DEVIDBY В (А : В) Замечание. Типичные запросы, реализуемые с помощью операции деления, обычно в своей формулировке имеют слово «все» - «какие поставщики поставляют все детали?». Таблицы 6.6 и 6.7 нуждаются в логическом дополнении, т.е. нужна таблица, связывающая поставляемые товары и поставщиков (по их кодам). Введем такую таблицу и на ее примере рассмотрим операцию деления. Таблица 7.7 - Отношение Х «Поставщики-Детали» Номер поставщика 1 1 1 2 2 3 Номер детали 1 2 3 1 2 1 Требуется узнать, какой поставщик поставляет все детали. Отношение Х возьмем в качестве делимого, а проекцию таблицы 7 «детали» - Y=B[Номер детали] (таблица 7.8): 29 Таблица 7.8 - Отношение Y = B[Номер детали] Номер детали 1 2 3 Деление Х DEVIDBY Y дает список номеров поставщиков, поставляющих все детали (таблица 7.9): Таблица 7.9 - Результирующее отношение (А : В) Номер поставщика 1 Контрольные вопросы 1. В чем смысл реляционного оператора «Выборки»? 2. Какие операторы сравнения или логические операторы можно использовать в условиях выборки? 3. В чем смысл реляционного оператора «Проекции»? 4. В чем смысл реляционного оператора «Соединения»? 5. Какие существуют виды соединений? 6. В чем особенность естественного соединения? 7. В чем смысл реляционного оператора «Деление»? 30 Лекция 8. Этапы проектирования баз данных Невозможно создать БД без подробного ее описания, также как и не возможно сделать какое-либо сложное изделие без чертежа и подробного описания технологий его изготовления. Другими словами, нужен проект. Проектом принято считать эскиз некоторого устройства, который в дальнейшем будет воплощен в реальность. Процесс проектирования БД представляет собой процесс переходов от неформального словесного описания информационной структуры предметной области к формализованному описанию объектов предметной области в терминах некоторой модели. Конечной целью проектирования является построение конкретной БД. Очевидно, что процесс проектирования сложен и поэтому имеет смысл разделить его на логически завершенные части – этапы. Можно выделить пять основных этапов проектирования БД: 1. Сбор сведений и системный анализ предметной области. 2. Инфологическое проектирование. 3. Выбор СУБД. 4. Даталогическое проектирование. 5. Физическое проектирование. Сбор сведений и системный анализ предметной области - это первый и важнейший этап при проектировании БД. В нем необходимо провести подробное словесное описание объектов предметной области и реальных связей, присутствующих между реальными объектами. Желательно чтобы в описании определялись взаимосвязи между объектами предметной области. В общем случае выделяют два подхода к выбору состава и структуры предметной области:  Функциональный подход – применяется тогда, когда заранее известны функции некоторой группы лиц и комплексы задач, для обслуживания которых создается эта БД, т.е. четко выделяется минимальный необходимый набор объектов предметной области под описание.  Предметный подход – когда информационные потребности заказчиков БД четко не фиксируются и могут быть многоаспектными и динамичными. В данном случае минимальный набор объектов предметной области выделить сложно. В описание предметной области включаются такие объекты и взаимосвязи, которые наиболее характерны и существенны для нее. При этом БД становится предметной, и подходит для решения множества задач (что кажется наиболее заманчивым). Однако трудность всеобщего охвата предметной области и невозможность конкретизации потребностей пользователей приводит к избыточно сложной схеме БД, которая для некоторых задач будет неэффективной. Рекомендуется использовать компромиссный вариант, который, с одной стороны, ориентирован на конкретные задачи, а с другой стороны, учитывает возможность расширения приложения. Системный анализ должен заканчиваться подробным описанием информации об объектах предметной области, которая должна храниться в БД, 31 формулировкой конкретных задач, которые будут решаться с использованием данной БД с кратким описанием алгоритмов их решения, описанием выходных и входных документов при работе с БД. Инфологическое проектирование – частично формализованное описание объектов предметной области в терминах некоторой семантической модели. Зачем нужна инфологическая модель, и какую пользу она дает проектировщикам? Дело в том, что процесс проектирования длительный, требует обсуждений с заказчиком и специалистами в предметной области. Кроме того, при разработке серьезных корпоративных информационных систем проект базы данных является фундаментом, на котором строится вся система в целом, и вопрос о возможности кредитования часто решается экспертами банка на основании именно грамотно сделанного инфологического проекта БД. Следовательно, инфологическая модель должна включать такое формализованное описание предметной области, которое легко будет восприниматься не только специалистами в области БД. Описание должно быть настолько емким, чтобы можно было оценить глубину и корректность проработки проекта БД. На сегодняшний день наиболее широкое распространение получила модель Чена «Сущность-связь» (Entity Relationship), она стала фактическим стандартом в инфологическом моделировании, и получило название ER – модель. Выбор СУБД осуществляется на основе различных требований к БД и, соответственно, возможностей СУБД, а также в зависимости от имеющегося опыта разработчиков. Даталогическое проектирование есть описание БД в терминах принятой даталогической модели данных. В реляционных БД даталогическое или логическое проектирование приводит к разработке схемы БД, т.е. совокупности схем отношений, которые адекватно моделируют объекты предметной области и семантические связи между объектами. Основой анализа корректности схемы являются функциональные зависимости между атрибутами БД. В некоторых случаях между атрибутами отношений могут появиться нежелательные зависимости, которые вызывают побочные эффекты и аномалии при модификации БД. Под модификацией понимают внесение новых данных в БД, удаление данных из БД, а также обновление значений некоторых атрибутов. Для ликвидации возможных аномалий предполагается проведение нормализации отношений БД. Этап логического проектирования не заключается только в проектировании схемы отношений. В результате выполнения этого этапа, как правило, должны быть получены следующие результирующие документы:  Описание концептуальной схемы БД в терминах выбранной СУБД.  Описание внешних моделей в терминах выбранной СУБД.  Описание декларативных правил поддержки целостности БД.  Разработка процедур поддержки семантической целостности БД. Физическое проектирование заключается в увязке логической структуры БД и физической среды хранения с целью наиболее эффективного размещения данных, т.е. отображение логической структуры БД в структуру хранения. Решается вопрос размещения хранимых данных в пространстве памяти, 32 выбора эффективных методов доступа к различным компонентам «физической» БД, решаются вопросы обеспечения безопасности и сохранности данных. Ограничения, имеющиеся в логической модели данных, реализуются различными средствами СУБД, например, при помощи индексов, декларативных ограничений целостности, триггеров, хранимых процедур. При этом опять-таки решения, принятые на уровне логического моделирования определяют некоторые границы, в пределах которых можно развивать физическую модель данных. Точно также, в пределах этих границ можно принимать различные решения. Например, отношения, содержащиеся в логической модели данных, должны быть преобразованы в таблицы, но для каждой таблицы можно дополнительно объявить различные индексы, повышающие скорость обращения к данным. Кроме того, для повышения производительности могут использоваться возможности параллельной обработки данных. В результате БД может размещаться на нескольких сетевых компьютерах. С другой стороны могут использоваться преимущества многопроцессорных систем. Для обеспечения безопасности и сохранности данных решаются вопросы способы восстановления после сбоев, резервного копирования информации, настройка систем защиты под выбранную политику безопасности и т.д. Необходимо отметить, что некоторые современные реляционные СУБД в основном используют физические структуры и методы доступа, опирающиеся на технологию проектирования файла, что по существу практически снимает вопрос о физическом проектировании. Таким образом, ясно, что решения, принятые на каждом этапе моделирования и разработки базы данных, будут сказываться на дальнейших этапах. Поэтому особую роль играет принятие правильных решений на ранних этапах моделирования. Контрольные вопросы 1. 2. 3. 4. 5. 6. 7. 8. Что такое проект? Какие этапы проектирования БД принято выделять? В чем назначение системного анализа? Какие подходы могут применяться в системном анализе предметной области? Что представляет собой этап инфологическое проектирование? В чем различие инфологического и даталогического этапов проектирования? Какие документы и модели необходимо получить при завершении этапа даталогического проектирования? Назовите результаты физического проектирования. 33 Пример описания предметной области проекта «Библиотека» Пусть требуется разработать информационную систему для автоматизации учета получения и выдачи книг в библиотеке. Система должна предусматривать режимы ведения системного каталога, отражающего перечень областей знаний, по которым имеются книги в библиотеке. Внутри библиотеки области знаний в систематическом каталоге могут иметь уникальный внутренний номер и полное наименование. Каждая книга может содержать сведения из нескольких областей знаний. Каждая книга в библиотеке может присутствовать в нескольких экземплярах. Каждая книга, хранящаяся в библиотеке, характеризуется следующими параметрами:  уникальный шифр;  название;  фамилии авторов (могут отсутствовать);  место издания (город);  издательство;  год издания;  количество страниц;  стоимость книги;  количество экземпляров книги в библиотеке. Книги могут иметь одинаковые названия, но они различаются по своему уникальному шифру (ISBN). В библиотеке ведется картотека читателей. На каждого читателя в картотеку заносятся следующие сведения:  фамилия, имя, отчество;  домашний адрес;  телефон (будем считать, что у нас два телефона — рабочий и домашний);  дата рождения. Каждому читателю присваивается уникальный номер читательского билета. Каждый читатель может одновременно держать на руках не более 5 книг. Читатель не должен одновременно держать более одного экземпляра книги одного названия. Каждая книга в библиотеке может присутствовать в нескольких экземплярах. Каждый экземпляр имеет следующие характеристики:  уникальный инвентарный номер;  шифр книги, который совпадает с уникальным шифром из описания книг;  место размещения в библиотеке. В случае выдачи экземпляра книги читателю в библиотеке хранится специальный вкладыш, в котором должны быть записаны следующие сведения:  номер билета читателя, который взял книгу;  дата выдачи книги;  дата возврата. Предусмотреть следующие ограничения на информацию в системе: 34 1. Книга может не иметь ни одного автора. 2. В библиотеке должны быть записаны читатели не моложе 17 лет. 3. В библиотеке присутствуют книги, изданные начиная с 1960 по текущий год. 4. Каждый читатель может держать на руках не более 5 книг. 5. Каждый читатель при регистрации в библиотеке должен дать телефон для связи: он может быть рабочим или домашним. 6. Каждая область знаний может содержать ссылки на множество книг, но каждая книга может относиться к различным областям знаний. С данной информационной системой должны работать следующие группы пользователей:  библиотекари;  читатели;  администрация библиотеки, При работе с системой библиотекарь должен иметь возможность решать следующие задачи: 1. Принимать новые книги и регистрировать их в библиотеке. 2. Относить книги к одной или к нескольким областям знаний. 3. Проводить каталогизацию книг, то есть назначение новых инвентарных номеров вновь принятым книгам, и, помещая их на полки библиотеки, запоминать место размещения каждого экземпляра. 4. Проводить дополнительную каталогизацию, если поступило несколько экземпляров книги, которая уже есть в библиотеке, при этом информация о книге в предметный каталог не вносится, а каждому новому экземпляру присваивается новый инвентарный номер и для него определяется место на полке библиотеки. 5. Проводить списание старых и не пользующихся спросом книг. Списывать можно только книги, ни один экземпляр которых не находится у читателей. Списание проводится по специальному акту списания, который утверждается администрацией библиотеки. 6. Вести учет выданных книг читателям, при этом предполагается два режима работы: выдача книг читателю и прием от него возвращаемых им книг обратно в библиотеку. При выдаче книг фиксируется, когда и какой экземпляр книги был выдан данному читателю и к какому сроку читатель должен вернуть этот экземпляр книги. При выдаче книг наличие свободного экземпляра и его конкретный номер могут определяться по заданному уникальному шифру книги или инвентарный номер может быть известен заранее. Не требуется вести «историю» чтения книг, то есть требуется отражать только текущее состояние библиотеки. При приеме книги, возвращаемой читателем, проверяется соответствие возвращаемого инвентарного номера книги выданному инвентарному номеру, и она ставится на свое старое место на полку библиотеки. 7. Проводить списание утерянных читателем книг по специальному акту списания или замены, подписанному администрацией библиотеки. 8. Проводить закрытие абонемента читателя, то есть уничтожение данных о нем, если читатель хочет выписаться из библиотеки и не является ее должником, то есть за ним не числится ни одной библиотечной книги. 35 Читатель должен иметь возможность решать следующие задачи: 1. Просматривать системный каталог, то есть перечень всех областей знаний, книги по которым есть в библиотеке. 2. По выбранной области знаний получить полный перечень книг, которые числятся в библиотеке. 3. Для выбранной книги получить инвентарный номер свободного экземпляра книги или сообщение о том, что свободных экземпляров книги нет. В случае отсутствия свободных экземпляров книги читатель должен иметь возможность узнать дату ближайшего предполагаемого возврата экземпляра данной книги. Читатель не может узнать данные о том, у кого в настоящий момент экземпляры данной книги находятся на руках (в целях обеспечения личной безопасности держателей требуемой книги). 4. Для выбранного автора получить список книг, которые числятся в библиотеке. Администрация библиотеки должна иметь возможность получать сведения о должниках—читателях библиотеки, которые не вернули вовремя взятые книги; сведения о книгах, которые не являются популярными, т. е. ни один экземпляр которых не находится на руках у читателей; сведения о стоимости конкретной книги, для того чтобы установить возможность возмещения стоимости утерянной книги или возможность замены ее другой книгой; сведения о наиболее популярных книгах, то есть таких, все экземпляры которых находятся на руках у читателей. Этот совсем небольшой пример показывает, что перед началом разработки необходимо иметь точное представление о том, что же должно выполняться в нашей системе, какие пользователи в ней будут работать, какие задачи будет решать каждый пользователь. И это правильно, ведь когда мы строим здание, мы тоже заранее предполагаем; для каких целей оно предназначено, в каком климате оно будет стоять, на какой почве, и в зависимости от этого проектировщики могут предложить нам тот или иной проект. Но, к сожалению, очень часто по отношению к базам данных считается, что все можно определить потом, когда проект системы уже создан. Отсутствие четких целей создания БД может свести на нет все усилия разработчиков, и проект БД получится «плохим», неудобным, не соответствующим ни реально моделируемому объекту, ни задачам, которые должны решаться с использованием данной БД. 36 Лекция 9. Инфологическое проектирование Инфологическое проектирование может выполняться на основе ERмодели (модель «Сущность-связь»). В основе ER-модели лежат следующие базовые понятия: Сущность - примитивный объект данных, отображающий элемент предметной области (человека, место, вещь, идею и т. д.). Она определяет класс однотипных объектов, в связи с этим предполагается, что в системе существует множество экземпляров данной сущности. Сущность имеет имя уникальное в пределах моделируемой системы. Объект, которому соответствует понятие сущности, имеет свой набор атрибутов (характеристик), определяющих свойства данного представителя класса. Например у сущности Сотрудник может быть следующий набор атрибутов: Табельный номер, Фамилия, Имя, Отчество, Дата рождения, Количество детей и т.д. Причем однозначно идентифицирует экземпляр сущности атрибут Табельный номер, поэтому он называется ключевым. Экземпляром сущности Сотрудник будет описание конкретного сотрудника. Общепринятое обозначение сущности – прямоугольник, в верхней части которого записано имя сущности, а ниже перечисляются атрибуты, причем ключевые атрибуты выделяются шрифтом или подчеркиванием. Рисунок 9.1 - Пример определения сущности в ER-модели Между сущностями могут быть установлены связи - бинарные ассоциации, показывающие, каким образом сущности соотносятся или взаимодействуют между собой. Связь может существовать между различными сущностями, или внутри одной сущности (рекурсивная связь). Пример связи между сущностями изображен на рисунке 9.2. Рисунок 9.2 - Связывание сущностей «Студент» – «Преподаватель», в отношении один-ко-многим 37 Как видно из примера, связь имеет общее имя «Дипломное проектирование» и имена родителей со стороны обеих сущностей. Со стороны студента эта связь называется «Пишет диплом», со стороны преподавателя эта связь называется «Руководит». Между двумя сущностями может быть множество связей с разными смысловыми нагрузками. В примере с использованием рисунка 9.2, кроме первой, можно установить еще одну смысловую связь, условно названную «Лекции» (рисунок 9.3). Она определит, лекции каких преподавателей слушает студент, и каким студентам данный преподаватель читает лекции. Рисунок 9.3 - Множественность смысловых связей между сущностями Пример. Спроектируем инфологическую модель системы, предназначенной для хранения информации о книгах и областях знаний, представленных в библиотеке. Описание предметной области было приведено ранее. Разработку модели начнем с выделения основных сущностей. Прежде всего, существует сущность «Книги», каждая книга имеет уникальный шифр, который является ее ключом, и ряд атрибутов, которые взяты из описания предметной области. Множество экземпляров сущности определяет множество книг, которые хранятся в библиотеке. Каждый экземпляр сущности «Книги» соответствует не конкретной книге, стоящей на полке, а описанию некоторой книги, которое дается обычно в предметном каталоге библиотеке. Каждая книга может присутствовать в нескольких экземплярах, и это как раз те конкретные книги, которые стоят на полках библиотеки. Для того чтобы отразить это, мы должны ввести сущность «Экземпляры», которая будет содержать описания всех экземпляров книг, которые хранятся в библиотеке. Каждый экземпляр сущности «Экземпляры» соответствует конкретной книге на полке. Каждый экземпляр имеет уникальный инвентарный номер, однозначно определяющий конкретную книгу. Кроме того, каждый экземпляр книги может находиться либо в библиотеке, либо на руках у некоторого читателя, и в последнем случае для данного экземпляра указываются дополнительно дата взятия книги читателем и дата предполагаемого возврата книги. Между сущностями «Книги» и «Экземпляры» существует связь один-комногим, обязательная с двух сторон. Чем определяется данный тип связи? Мы можем предположить, что каждая книга может присутствовать в библиотеке в 38 нескольких экземплярах, поэтому связь один-ко-многим. При этом если в библиотеке нет ни одного экземпляра дайной книги, то мы не будем хранить ее описание, поэтому если книга описана в сущности «Книги», то по крайней мере один экземпляр этой книги присутствует в библиотеке. Это означает, что со стороны книги связь обязательная. Что касается сущности «Экземпляры», то не может существовать в библиотеке ни одного экземпляра, который бы не относился к конкретной книге, поэтому и со стороны «Экземпляры» связь тоже обязательная. Теперь нам необходимо определить, как в нашей системе будет представлен читатель. Естественно предложить ввести для этого сущность «Читатели», каждый экземпляр которой будет соответствовать конкретному читателю. В библиотеке каждому читателю присваивается уникальный номер читательского билета, который будет однозначно идентифицировать нашего читателя. Номер читательского билета будет ключевым атрибутом сущности «Читатели». Кроме того, в сущности «Читатели» должны присутствовать дополнительные атрибуты, которые требуются для решения поставленных задач, этими атрибутами будут: «Фамилия Имя Отчество», «Адрес читателя», «Телефон домашний» и «Телефон рабочий». Почему мы ввели два отдельных атрибута под телефоны? Потому что надо в разное время звонить по этим телефонам, чтобы застать читателя, поэтому администрации библиотеки будет важно знать, к какому типу относится данный телефон. В описании нашей предметной области существует ограничение на возраст наших читателей, поэтому в сущности «Читатели» надо ввести обязательный атрибут «Дата рождения», который позволит нам контролировать возраст наших читателей. Из описания предметной области мы знаем, что каждый читатель может держать на руках несколько экземпляров книг. Для отражения этой ситуации нам надо провести связь между сущностями «Читатели» и «Экземпляры». А почему не между сущностями «Читатели» и «Книги»? Потому что читатель берет из библиотеки конкретный экземпляр конкретной книги, а не просто книгу. А как же узнать, какая книга у данного читателя? А это можно будет узнать по дополнительной связи между сущностями «Экземпляры» и «Книги», и эта связь каждому экземпляру ставит в соответствие одну книгу, поэтому мы в любой момент можем однозначно определить, какие книги находятся на руках у читателя, хотя связываем с читателем только инвентарные номера взятых книг. Между сущностями «Читатели» и «Экземпляры» установлена связь один-комногим, и при этом она не обязательная с двух сторон. Читатель в данный момент может не держать ни одной книги на руках, а с другой стороны, данный экземпляр книги может не находиться ни у одного читателя, а просто стоять на полке в библиотеке. Теперь нам надо отразить последнюю сущность, которая связана с системным каталогом. Системный каталог содержит перечень всех областей знаний, сведения по которым содержатся в библиотечных книгах. Мы можем вспомнить системный каталог в библиотеке, с которого мы обычно начинаем поиск нужных нам книг, если мы не знаем их авторов и названий. Название области знаний может быть длинным и состоять из нескольких слов, поэтому для моделирования системного каталога мы введем сущность «Системный каталог» с 39 двумя атрибутами: «Код области знаний» и «Название области знаний». Атрибут «Код области знаний» будет ключевым атрибутом сущности. Из описания предметной области известно, что каждая книга может содержать сведения из нескольких областей знаний, а с другой стороны, из практики известно, что в библиотеке может присутствовать множество книг, относящихся к одной и той же области знаний, поэтому нам необходимо установить между сущностями «Системный каталог» и «Книги» связь многие-комногим, обязательную с двух сторон. Действительно, в системном каталоге не должно присутствовать такой области знаний, сведения по которой не представлены ни в одной книге нашей библиотеки, противное было бы бессмысленно. И обратно, каждая книга должна быть отнесена к одной или нескольким областям знаний для того, чтобы читатель мог ее быстрее найти. Модель предметной области «Библиотека» представлена на рисунок 9.4. Рисунок 9.4 - модель «Библиотека» Модель «Библиотека» разработана нами под те задачи, которые были перечислены ранее. В этих задачах мы не ставили условие хранения истории чтения книги, например, с целью поиска того, кто раньше держал книгу и мог нанести ей вред или забыть в ней случайно большую сумму денег. Если бы мы ставили перед собой задачу хранения и этой информации, то наша инфологическая модель была бы другой. 40 Контрольные вопросы 1. 2. 3. 4. 5. В чем смысл инфологического моделирования? Какие основные понятия лежат в основе ER – модели? Как обозначаются основные элементы ER – модели? Как указываются связи? Как осуществить переход от словесного описания предметной области к ER-модели? 41 Лекция 10. Критерии выбора СУБД Выбор СУБД представляет собой сложную многопараметрическую задачу и является одним из важных этапов при разработке БД. Выбранный программный продукт должен удовлетворять как текущим, так и будущим потребностям предприятия, при этом следует учитывать финансовые затраты на приобретение необходимого оборудования, самой системы, разработку необходимого программного обеспечения на ее основе, а также обучение персонала. Кроме того, необходимо убедиться, что новая СУБД способна принести предприятию реальные выгоды. Можно выделить ряд критериев, по которым следует проводить выбор СУБД:  модель данных;  дополнительные возможности;  особенности архитектуры и функциональные возможности;  особенности разработки приложений;  производительность;  надежность;  требования к рабочей среде;  смешанные критерии; Используемая модель данных. Существует множество моделей данных. Самыми распространенными являются - иерархическая, сетевая, реляционная, объектно-реляционная и объектная. Выбор модели СУБД определяется тем, какая модель лучше всего подходит для решения задачи. Дополнительные возможности. Существует ли поддержка триггеров и хранимых процедур. Хранимая процедура – программа, которая хранится на сервере и может вызываться клиентом. Поскольку хранимые процедуры выполняются непосредственно на сервере БД, обеспечивается более высокое быстродействие, нежели при выполнении тех же операций средствами клиента БД. В различных программных продуктах для реализации триггеров и хранимых процедур используются различные инструменты. Триггер – программа БД, вызываемая всякий раз при вставке, изменении или удалении строки таблицы. Триггеры обеспечивают проверку любых изменений на корректность, прежде чем эти изменения будут приняты. Триггеры позволяют вести журнализацию всех изменений в БД. Средства поиска. Некоторые современные системы имеют встроенные дополнительные средства контекстного поиска. Расширенные типы данных. Все СУБД поддерживают стандартные типы данных, однако механизмы расширения типов данных в системах того или иного производителя существенно различаются. Реализация языка запросов. Все современные системы совместимы со стандартным языком доступа к данным SQL-92, однако многие из них реализуют те или иные расширения данного стандарта. 42 Особенности автонастройки системы и наличие контроля использования различных видов памяти. Особенности архитектуры и функциональные возможности. Мобильность – это независимость системы от среды, в которой она работает. Под средой понимается аппаратно-программная платформа. Масштабируемость. При выборе СУБД необходимо учитывать, сможет ли данная система соответствовать росту ИС, причем рост может проявляться в увеличении числа пользователей, объема хранимых данных и объеме обрабатываемой информации. Распределенность. Стремления к централизованному хранению и обработке данных заставляют использовать распределенные БД. Различные системы имеют разные возможности управления распределенными БД. Сетевые возможности. Многие системы позволяют использовать множество сетевых протоколов и служб для работы и администрирования. Особенности разработки приложений. Используемые языки программирования и средства разработки. Спектр используемых языков программирования, а также средств для разработки приложений повышает доступность системы для разработчиков, а также может функциональность создаваемых приложений. Средства проектирования. Некоторые системы имеют средства автоматического проектирования (CASE), как БД, так и прикладных программ. Средства проектирования различных производителей могут существенно различаться. Возможности Web разработки. Средства разработки некоторых производителей имеют большой набор инструментов для построения приложений под Web. Производительность. Рейтинг TPC (Transactions per Cent) является одним из самых популярных и объективных для анализа производительности систем. Показатель TPC – это отношение количества запросов обрабатываемых за некий промежуток времени к стоимости всей системы. Возможности параллельной архитектуры. Для обеспечения параллельной обработки данных существует два основных подхода: распараллеливание обработки запросов на несколько процессоров, либо использование нескольких компьютеров, работающих с одной БД, которые объединенных в так называемый параллельный сервер. Возможности оптимизации запросов. Некоторые СУБД способны оптимизировать выполнение сложных запросов за счет генерации плана его выполнения. Надежность. Возможности восстановления после сбоев. Функции резервного копирования. В результате серьезных сбоев может быть частично поврежден или выведен из строя носитель данных, в этом случае восстановление возможно только из резервных копий. Существует множество механизмов резервирования данных (хранение одной или более копий всей базы 43 данных, хранение копии ее части, копирование логической структуры и т.д.). Часто системы поддерживают несколько таких механизмов. Откат изменений. При выполнении транзакции применяется простое правило – либо транзакция выполняется полностью, либо не выполняется вообще. Это означает, что в случае сбоев, все результаты недоведенных до конца транзакций должны быть аннулированы. Механизм отката может иметь различное быстродействие и эффективность. Многоуровневая система защиты. Для предотвращения несанкционированного доступа к конфиденциальной информации СУБД имеют механизмы защиты. Уровень защиты может быть различным. Требования к рабочей среде. Поддерживаемые аппаратно-программные платформы. Требования к оборудованию. Максимальный размер адресуемой памяти. Поскольку почти все современные системы используют свою файловую систему, немаловажным фактором является то, какой максимальный объем физической памяти они могут использовать. Смешанные критерии. Качество и полнота документации. К сожалению, не все системы имеют полную и подробную документацию. Модель формирования стоимости. Стоимость СУБД может существенно изменяться в зависимости от того, сколько пользователей будет с ним работать. Стабильность производителя. Распространенность СУБД. Четкий и глубокий сравнительный анализ на основании вышеперечисленных критериев поможет рационально выбрать подходящую СУБД для конкретного проекта. Перечень критериев поможет осознать масштабность задачи и выполнить ее адекватную постановку. Следует отметить, что по существующей практике решение об использовании той или иной СУБД принимает один человек – обычно, руководитель предприятия, а он может опираться отнюдь не на технические критерии. Здесь свою роль могут сыграть такие факторы как рекламная раскрутка компании-производителя СУБД, использование конкретных систем на других предприятиях, стоимость. Контрольные вопросы Какие характеристики СУБД взяты за основу критериев выбора? Для чего необходимо проводить выбор СУБД? На что влияет модель данных СУБД? Какие дополнительные характеристики СУБД принято выделять для сравнения? 5. Для чего предназначены хранимые процедуры и триггеры? 1. 2. 3. 4. 44 6. В чем причины отклонения от стандарта SQL в различных реализациях СУБД? 7. Что такое мобильность и масштабируемость? 8. В чем смысл характеристики – распределенность? 9. Какие возможны особенности разработки приложений различных СУБД? 10. В чем может измеряться и как может регулироваться производительность СУБД? 11. Какие возможности и характеристики СУБД могут определять надежность? 45 Лекция 11. Переход к реляционной модели данных. Переход к реляционной модели данных Инфологическая модель позволяет понять суть разрабатываемой базы данных, но она не подходит для непосредственной реализации структуры БД. Необходимо преобразование инфологической модели в даталогическую, с учетом особенностей выбранной даталогической модели. Если выбор СУБД остановился на реляционной СУБД, а именно этот случай рассматривается в данном курсе лекций, то особенности реляционной модели данных достаточно жестко описывает стандарт языка SQL, поэтому переход к реляционной модели, в общем случае, можно свести к ряду последовательных преобразований. Для ER-модели существует алгоритм однозначного преобразования в реляционную модель данных, что позволило разработать множество инструментальных систем (САПР – система автоматизированного проектирования), поддерживающих процесс разработки информационных систем, базирующихся на технологии баз данных. И во всех этих системах существуют средства описания инфологической модели (в рамках реальных САПР она часто имеет название концептуальной модели) разрабатываемой БД с возможностью автоматической генерации той даталогической модели, на которой будет реализовываться проект в дальнейшем. Алгоритм перехода от ER-модели к реляционной модели данных обычно сводится к следующим шагам: 1. Каждой сущности ставится в соответствие отношение РМД. Имена отношений могут быть ограничены требованиями конкретной СУБД, они ограничены по длине и не должны содержать пробелов и некоторых специальных символов. 2. Каждый атрибут сущности становится атрибутом соответствующего отношения, на их имена также могут накладываться некоторые ограничения (например, многие СУБД не поддерживают кириллицу). Для каждого атрибута задается конкретный допустимый в СУБД тип данных и обязательность или необязательность данного атрибута. На рисунке 11.1, упрощенно, показан процесс преобразования, с учетом выбранной СУБД (в правой части отношения указаны типы данных принятые в СУБД MS Access для соответствующих полей). Книги ISBN Название Автор Издательство Место издания Год издания Количество страниц Книги ISBN Текст. 14 Название Текст. 255 Автор Текст. 30 Издательство Текст. 30 Место_издания Текст. 30 Год_издания Целое Страниц Целое Рисунок 11.1 – Преобразование сущности «Книги» к отношению с учетом выбранной реляционной БД. 46 3. Первичный ключ сущности становится первичным ключом соответствующего отношения (на рисунке 11.1 первичный ключ выделен подчеркиванием и курсивом). Атрибуты, входящие в первичный ключ отношения, автоматически получают свойство обязательности и уникальности. 4. В каждое отношение, соответствующее подчиненной сущности, добавляется набор атрибутов первичного ключа главной сущности. В отношении, соответствующем подчиненной сущности, этот набор атрибутов становится внешним ключом (атрибут ISBN таблицы «Экземпляры» рисунок 11.2). Книги ISBN Текст. 14 Название Текст. 255 Автор Текст. 30 Издательство Текст. 30 Место_издания Текст. 30 Год_издания Целое Страниц Целое ISBN = ISBN Экземпляры Инв_номер Длин. целое ISBN Текст. 14 Номер_ЧБ Длин. целое Наличие Логический Дата_взятия Дата/время Дата_возврата Дата/время Рисунок 11.2 – Реализация связи между отношениями «Книги» и «Экземпляры» 5. Для необязательных типов связи на физическом уровне у атрибутов, соответствующих внешнему ключу, устанавливается свойство допустимости неопределенных значений. При обязательном типе связи атрибуты получают свойство отсутствия неопределенных значений. Книги ISBN Текст. 14 Название Текст. 255 Автор Текст. 30 Издательство Текст. 30 Место_издания Текст. 30 Год_издания Целое Страниц Целое ISBN = ISBN Связь ISBN Код_ОЗ Текст. 14 Целое Код_ОЗ = Код_ОЗ ISBN = ISBN Экземпляры Инв_номер Длин. целое ISBN Текст. 14 Номер_ЧБ Длин. целое Наличие Логический Дата_взятия Дата/время Дата_возврата Дата/время Номер_ЧБ = Номер_ЧБ Читатели Номер_ЧБ Длин. целое ФИО Текст. 30 Дата_рождения Дата/время Пол Текст. 1 Тел_дом Текст. 7 Тел_раб Текст. 7 Каталог Код_ОЗ Целое Наименование Текст. 255 Рисунок 11.3 – Реляционная схема БД «Библиотека» 47 6. При реализации связи многие-ко-многим, допустимой в инфологической модели, производится ее преобразование к связям один-комногим, например, через промежуточное отношение (рисунок 11.3 – отношение «Связь»). Промежуточное отношение будет иметь первичный ключ, состоящий из первичных ключей связываемых отношений. После выполнения преобразований необходимо убедиться в корректности полученной схемы БД, в противном случае в таблицах могут оказаться нежелательные функциональные зависимости, которые впоследствии могут привести к возникновению различных аномалий. Проверить полученную схему БД, на отсутствие нежелательных функциональных зависимостей, можно используя правила нормализации (см. лекцию 12). На данном этапе выполняется описание внешних моделей в терминах РСУБД. Под внешними моделями подразумевается совокупность моделей данных для каждого приложения, использующего БД. Например, для БД «Библиотека» можно выделить три основных внешних модели: для приложения библиотекаря, администрации и читателя. Каждое из этих приложений будет решать свои прикладные задачи, используя для этого не всю БД, а лишь требуемую ее часть. После создания схемы БД и проверки ее корректности необходимо описать так называемые бизнес-правила. Бизнес-правила (БП) задают ограничения на значения данных в БД. Они также определяют механизмы, согласно которым при изменении одних данных изменяются и связанные с ними данные в той же или других таблицах БД. Таким образом, бизнес правила определяют условия поддержания БД в целостном состоянии. На первом этапе проектирования, т.е. при описании предметной области определяются и ограничительные условия, правда, на данном этапе они реализуются уже в рамках реляционной модели данных. Контрольные вопросы 1. В чем принципиальное отличие инфологической модели от реляционной? 2. Какие возможности дает формализация инфологической модели для САПР? 3. Какие правила преобразования используются для перехода от ERмодели к РМД? 4. Каким правилом нужно руководствоваться при преобразовании связи многие-ко-многим? 5. Для чего необходимо проводить проверку корректности схемы? 6. Что понимается под описанием внешних моделей? 7. Что такое бизнес-правила? 48 Лекция 12. Теория нормализации Переход от инфологического проектирования к даталогическому производится с учетом выбора СУБД. В данном курсе мы изучаем РМД и, следовательно, выбираем реляционную СУБД. Прежде всего, необходимо построить корректную схему БД, ориентируясь на РМД. Основой анализа корректности схемы являются так называемые функциональные зависимости между атрибутами БД. Некоторые функциональные зависимости атрибутов являются нежелательными из-за побочных явлений и аномалий, которые они могут вызвать. Обычно различают следующие проблемы:  избыточность данных;  аномалии обновления;  аномалии удаления;  аномалии ввода. Избыточность данных характеризуется наличием в кортежах отношений повторяющейся информации. Многократное дублирование данных приводит к неоправданному увеличению занимаемого объема внешней памяти. Аномалии обновления, прежде всего, связанны с избыточностью данных, что приводит к проблемам при их изменении. При изменении повторяющихся данных придется многократно изменять их значения, однако, если изменения будут внесены не во все кортежи, возникнет несоответствие информации, которое называется аномалией обновления. Аномалии удаления могут возникать при удалении записей из ненормализованных таблиц и характеризуются вероятностью удаления не всех дублированных кортежей. Аномалии ввода возникают при добавлении в таблицу новых записей, обычно в поля с ограничениями NOT NULL (не пустые). Кода в отношение на данный момент времени невозможно ввести однозначную информацию. Для ликвидации нежелательных функциональных зависимостей есть специальный формальный механизм называемый нормализацией. В процессе нормализации происходит устранение избыточности и противоречивости хранимых данных. Нормальные формы Теория нормализации основана на концепции нормальных форм. Каждой нормальной форме соответствует набор ограничений. Отношение находится в нормальной форме, ели оно удовлетворяет свойственному данной форме набору ограничений. В теории реляционных БД обычно выделяется следующая последовательность нормальных форм:  первая нормальная форма (1НФ);  вторая нормальная форма (2НФ);  третья нормальная форма (3НФ); 49  нормальная форма Бойса-Кодда (БКНФ);  четвертая нормальная форма (4НФ);  пятая нормальная форма, или нормальная форма проекциисоединения (5НФ или ПС/НФ). Основные свойства нормальных форм:  каждая следующая нормальная форма, в некотором смысле, улучшает свойства предыдущей;  при переходе к следующей нормальной форме свойства предыдущих нормальных форм сохраняются. Определение 1НФ. Отношение находится в первой нормальной форме тогда и только тогда, когда каждый его атрибут содержит атомарные значения и отношение не содержит повторяющихся групп. Пусть необходимо автоматизировать процесс отпуска товаров со склада по накладной, примерный вид накладной на рисунке 12.1. Накладная № 234 Дата 10.01.2002 Отпущен товар Тушенка Сахар Макароны Покупатель ООО «Геракл» Количество 1000 50 300 Адрес г. Москва, ул. Стромынка, 20 Ед. изм. банки КГ кг Цена ед. изм 25 10 10 ИТОГО Общая стоимость 25000 500 3000 28500 Рисунок 12.1 - Примерный вид накладной. По накладной можно сформировать удовлетворяющее 1НФ (рисунок 12.2): следующее отношение ОТПУК ТОВАРОВ Номер накладной Дата Покупатель Город Адрес Товар Количество Ед.изм. Цена ед.изм. Общая стоимость Рисунок 12.2 - Отношение «Отпуск товаров» 50 Определение 2НФ. Отношение находится во второй нормальной форме тогда и только тогда, когда оно находится в первой нормальной форме и каждый не ключевой атрибут функционально зависим от атрибутов первичного ключа. Прежде всего, необходимо определить понятие функциональной зависимости. Функционально зависимым считается атрибут, значение которого однозначно определяется значением другого атрибута, т.е. значение одного атрибута зависит от значения другого. Функциональная зависимость значения атрибута Y от значения атрибута Х обозначается следующим образом: XY. Необходимо отметить, что атрибут, указываемый в левой части называется детерминантом. Продолжим рассмотрение описанного выше примера. Для приведения отношения к 2НФ необходимо вначале выделить первичный ключ. Первичный ключ возможно определить из следующих рассуждений. Если бы по одной накладной отпускался бы только один товар, то первичным ключом являлся бы атрибут «Номер накладной», однако по одной накладной отпускается несколько различных товаров, следовательно, первичный ключ должен состоять из двух атрибутов «Номер накладной» и «Товар», только в этом случае будет обеспечено свойство уникальности. Рассмотрим функциональные зависимости атрибутов от первичного ключа, при этом проще начинать рассмотрение с частей первичного ключа, в данном случае с атрибута «Номер накладной»: Номер накладной  Покупатель Номер накладной  Дата Номер накладной  Город Номер накладной  Адрес Определим функциональные зависимости от атрибута «Товар»: Товар  Ед.изм Товар  Цена ед.изм Оставшиеся атрибуты определяются первичным ключом: Номер накладной, Товар  Количество Номер накладной, Товар  Общая стоимость В результате мы получили три различные категории, у каждой из которых свой первичный ключ. После проведения вышеуказанного анализа отношения производим его декомпозицию и определяем типы связей (рисунок 12.3) НАКЛАДНАЯ Номер накладной Дата Покупатель Город Адрес ОТПУСК ТОВАРОВ Номер накладной Товар Количество Общая стоимость ТОВАР Товар Ед. изм. Цена ед.изм Рисунок 12.3 - Отношение «Отпуск товаров», находящееся в 2НФ 51 В результате получаем отношения, все атрибуты которых полностью, функционально зависимы от своих первичных ключей и, следовательно, удовлетворяют условию 2НФ. Определение 3НФ. Отношение находится в третьей нормальной форме тогда и только тогда, когда оно находится во второй нормальной форме и не содержит транзитивных зависимостей между не ключевыми атрибутами. Функциональная зависимость атрибутов X и Y отношения называется транзитивной, если существует такой атрибут Z, что имеются функциональные зависимости X Z и Z Y, но отсутствует функциональная зависимость Z Х. В результате анализа отношения «НАКЛАДНАЯ» определяем следующую транзитивную зависимость: Номер накладной  Покупатель Номер накладной  Город Номер накладной  Адрес Покупатель  Город Покупатель  Адрес В результате анализа отношения «ОТПУСК ТОВАРОВ», также определяется транзитивная зависимость следующего вида: Номер накладной, Товар  Количество Номер накладной, Товар  Общая стоимость Количество  Общая стоимость От транзитивной зависимости в отношении «НАКЛАДНАЯ» можно избавиться простой декомпозицией отношения. С атрибутом «Общая стоимость» отношения «ОТПУСК ТОВАРОВ» можно поступить еще проще, отказаться от использования этого атрибута, т.к. общую стоимость можно всегда получить, зная цену единицы товара и какое его количество продано, следовательно, не имеет смысла использовать внешние носители для хранения этих данных. В результате получим следующую схему отношений приведенную к 3НФ (рисунок 12.4). НАКЛАДНАЯ Номер накладной Дата Покупатель ОТПУСК ТОВАРОВ Номер накладной Товар Количество ТОВАР Товар Ед. изм. Цена ед.изм ПОКУПАТЕЛЬ Покупатель Город Адрес Рисунок 12.4 - Отношение «Отпуск товаров», находящееся в 3НФ В большинстве случаев достижение третьей нормальной формы, или даже формы Бойса-Кодда считается достаточным для реальных проектов БД. Четвертая и пятая считаются нормальными формами высших порядков, связанными не с функциональными зависимостями атрибутов, а отражают более 52 тонкие вопросы семантики предметной области и связаны с другими видами зависимостей, поэтому в данном курсе не рассматриваются. Контрольные вопросы 1. Для чего необходим процесс нормализации? 2. Какие аномалии могут возникать при использовании ненормализованных отношений и почему? 3. Определите процессы синтеза и декомпозиции. 4. Назовите определение 1НФ. 5. Назовите определение 2НФ. 6. Назовите определение 3НФ. 7. Что дает приведение БД к 3НФ? 8. Почему приведение к 3НФ считается достаточным для большинства проектов БД? 53 Раздел 2. Организация баз данных Лекция 13. СУБД MS Access. Создание таблиц и определение связей В настоящее время наибольшее распространение получили реляционные СУБД к числу которых относится и офисная СУБД - MS Access. Основным преимуществом MS Access является его наличие в составе пакета MS Office. СУБД MS Access имеет богатые функциональные возможностями и удобна в использовании. Особенно удобной является визуальная среда разработки, значительно ускоряющая процесс создания БД. Начиная с пакета MS Office 2000, в составе MS Access появились новые функциональные возможности:  Поддержка клиент-серверных приложений. В качестве серверов БД могут быть использованы Microsoft SQL Server 6.5 или 7.0, а также процессор обработки данных MSDE (Microsoft Database Engine).  Экспорт объектов БД в формат HTML, создание статических и динамических Web-страниц для доступа к данным.  Возможность использования электронной почты для отправки данных и т.д. Взаимодействие со средой MS Access осуществляется через главное меню и панели инструментов их использование и настройка не отличается от других приложений пакета MS Office. СУБД MS Access создает всю систему в одном фале с расширением *.mdb, с этим связаны недостатки и преимущества этой СУБД. Основы работы в СУБД MS Access После создания или открытия БД через окно диалога «Microsoft Access» откроется рабочее окно диалога «База данных» (рисунок 13.1). Рисунок 13.1 - Диалоговое окно «База данных» 54 Рабочее окно «База данных» позволяет получить доступ к основным объектам MS Access, таким как: таблицы, запросы, формы, отчеты, страницы, макросы, модули. Определим основные объекты, используемым в СУБД Access:  Таблица – основной объект реляционной базы данных, необходимый для хранения данных.  Запрос – это вопрос, формируемый по отношению к базе данных. Именно запросы позволяют получать необходимую информацию из реляционных БД. В основе них лежит реляционная алгебра и реляционное исчисление.  Форма – формат отображения в виде окна ОС Windows. Весь пользовательский интерфейс базируется на формах.  Отчет – форматированное представление данных, выводимое на экран, принтер или файл.  Страница – формат отображения в виде формы служащий для доступа к Web-страницам. Новый объект, добавленный в MS Access 2000.  Макрос – набор из одной и более макрокоманд, выполняющий определенные, наиболее часто используемые операции.  Модуль – программа на языке Access Basic. Использование модулей позволяет более полно реализовать возможности MS Access. Практически каждый объект MS Access, кроме макросов и модулей, имеет несколько вариантов создания, традиционными можно назвать конструктор и мастер:  Мастер – позволяет создавать объект общего вида и представляет собой окно диалога с рядом задаваемых последовательно вопросов, ответы на которые позволяют уточнить свойства создаваемого объекта. Обычно применяется как первичное средство создания объекта.  Конструктор – предоставляет мощные механизмы настройки и редактирования, обычно служит средством редактирования уже созданного объекта и придания ему надлежащего вида. Создание таблиц СУБД MS Access поддерживает ряд базовых типов данных: текстовый, числовой, денежный, счетчик, даты/времени, логический, поле МЕМО, поле объекта OLE, гиперссылка. Среди перечисленных типов расширенными можно считать последние два: поле объекта OLE и гиперссылка, последний не нуждается в пояснениях. Поле объекта OLE позволяет импортировать в таблицы БД объекты, поддерживающие технологию OLE, например изображения, документы MS Word и т.д. На имена таблиц и полей в СУБД MS Access накладываются следующие ограничения:  Имя поля не может содержать более 64 символов.  В именах полей и таблиц недопустимо использование управляющих символов с кодами 0 – 31 и символов (. ! ‘ [ ]).  Имя поля не может начинаться с пробела, кроме того, привыкать к использованию пробелов вообще не желательно, лучше использовать нижнее подчеркивание. 55 СУБД MS Access предлагает несколько способов создания таблиц:  Режим таблицы – позволяет создать таблицу в режиме таблицы, т.е. открывается таблица, в которую необходимо вписать заголовки столбцов (поля). Все типы полей принимаются, по умолчанию как текстовые.  Конструктор – позволяет создать новую таблицу в конструкторе таблиц, при этом сразу можно указать все возможные свойства полей таблицы.  Мастер таблиц – позволяет создать таблицу с помощью мастера имеющего большой запас шаблонов таблиц с уже определенными свойствами. Процесс создания заключается в выборе необходимых полей из образцов таблиц.  Импорт таблиц – позволяет импортировать таблицы из внешних файлов, например из существующих БД MS Access, таблиц MS Excel и т.д. В результате в текущей БД будет создана таблица, полученная из внешнего источника.  Связь с таблицами – позволяет осуществить связь с таблицами расположенными во внешних файлах. Основным средством создания таблиц в MS Access является конструктор таблиц. Рассмотрим структуру конструктора таблиц (рисунок 13.2) и особенности создания таблиц в этом режиме. Рисунок 13.2 - Окно конструктора таблиц В верхней части окна конструктора находится бланк-таблица содержащая три столбца: «Имя поля», «Тип данных», «Описание». В столбце «Имя поля» указываются имена полей (атрибутов) создаваемой таблицы. В столбце «Тип данных» указываются базовые типы данных соответствующих полей. Столбец «Описание» не является обязательным и предназначен для комментариев. Каждое поле, наряду с определенным типом, имеет дополнительные свойства, отображаемые в разделе «Свойства поля» в нижней части конструктора. 56 Можно выделить следующие свойства полей расположенные на вкладке «Общие»:  Размер поля – определяет максимальное число знаков (применяется для полей типа: текстовые, числовые, счетчик);  Новые значения – определяет способ изменения значений счетчика (только для типа счетчик);  Формат поля – определяет формат вывода значений поля (применяется для всех полей кроме полей объектов OLE);  Число десятичных знаков – определяет число десятичных знаков при отображении чисел (применяется для типов: числовой, денежный);  Маска ввода – позволяет задать маску ввода, для упрощения ввода данных (применяется для полей типа: текстовые, дата/время, числовые, денежные);  Подпись – определяет текст, который будет выводиться в качестве заголовка столбца вместо имени поля (для всех полей);  Значение по умолчанию – позволяет задать значение, автоматически вводимое в поле при создании новой записи;  Условие на значение – определяет требования к данным, вводимым в поле;  Сообщение об ошибке – содержит сообщение, которое будет выводиться в случае нарушения условия на вводимое значение (см. выше);  Обязательное поле – указывает, требуется ли обязательный ввод значений в поле;  Пустые строки – определяет, допускается ли ввод пустых строк в поле (только для текстовых полей);  Индексное поле – определяет индекс создаваемый по одному полю;  Сжатие ЮНИКОД – включает сжатие полей, при использовании кодировки Unicode (для полей МЕМО, гиперссылок и текстовых полей). Замечание. Определение таких свойств как «Маска ввода», «Значение по умолчанию», «Условие на значение», «Сообщение об ошибке» позволят повысить удобство и скорость работы пользователей, а также снизить количество возможных ошибок. Вкладка «Подстановка» предоставляет средства создания элементов управления для альтернативного ввода значений таких как: список и поле со списком. По умолчанию определено обычное поле. Назначение первичного ключа осуществляется командой Правка Ключевое поле, командой контекстного меню Ключевое поле или кнопкой панели инструментов . Сложный первичный ключ можно создать аналогично, предварительно выделив поля входящие в первичный ключ мышкой, с удержанием клавиши Shift или Ctrl. Как и во многих СУБД, ключи определяются индексами (в том числе первичный ключ), поэтому для назначения сложных ключей или их специальной настройки можно использовать окно диалога «Индексы» (рисунок 13.3). 57 В столбце «Индекс» определяются имена индексов таблицы, причем первичный ключ таблицы всегда имеет имя PrimaryKey. Столбец «Имя поля» позволяет указать набор полей входящих в состав индекса. Специальные параметры индекса определяются в нижней части окна «Свойства индекса». Рисунок 13.3 - Окно диалога создания составных индексов. Обычно, после того как созданы все таблицы и определены все свойства их полей создаются связи между таблицами. Связи позволяют реализовать принципы поддержки целостности между данными, размещенными в различных таблицах. Связи создаются через инструмент – «Схема данных», вызываемый командой Сервис - Схема данных или кнопкой панели инструментов . Добавление таблиц в схему реализуется через окно диалога «Добавление таблицы» простым выделением и нажатием кнопки «Добавить» (рисунок 13.4). Необходимо отметить, что в связях могут участвовать не только таблицы, но и запросы. Рисунок 13.4 – Диалоговые окна «Схема данных» и «Добавление таблицы» Создание связей между таблицами осуществляется простым протягиванием, для этого нажимаем левую кнопку мыши на поле первичного 58 ключа главной таблицы и тянем до внешнего ключа подчиненной таблицы, после этого левую кнопку мыши отпускаем. В результате появится диалоговое окно «Изменение связей» (рисунок 13.5). В окне диалога «Изменение связей» поля со списками «Таблица/запрос» и «Связанная таблица/запрос» указывают на главную и подчиненную таблицы соответственно. Левый часть списка содержит поля главной таблицы участвующие в связи, а в правой части указываются связанные с ними поля подчиненной таблицы. При соединении с использованием простых ключей MS Access заполняет списки связей автоматически, но при соединении сложных ключей приходится заполнять списки вручную. Окно диалога «Изменение связей» позволяет определять принципы поддержки ссылочной целостности, для этого используется группа флажков «Обеспечение целостности данных». Если флажок «Обеспечение целостности данных» не выставлен, то MS Access не следит за поддержкой целостности связанных записей. MS Access поддерживает два основных принципа поддержки ссылочной целостности на удаление:  запрет на удаление записей главной таблицы, на которые существуют ссылки в подчиненных таблицах, если флажок «Каскадное удаление связанных записей» не включен;  при удалении записи главной таблицы, на которые существуют ссылки в подчиненных таблицах, происходит каскадное удаление последних, если флажок «Каскадное удаление связанных записей» включен (рисунок 13.5); Аналогичные принципы действуют при обновлении записей, но управляются флажком «Каскадное обновление связанных полей». Рисунок 13.5 - Диалоговое окно «Изменение связей» Контрольные вопросы 1. 2. 3. 4. 5. Какие новые возможности появились в СУБД MS Access 2000? Какие категории объектов выделяют в СУБД MS Access? Какие методы создания объектов можно назвать основными? Назовите основные типы данных, используемые в MS Access. Какие ограничения накладываются на имена полей и таблиц в СУБД MS Access? 59 6. Какие режимы работы используются для работы с таблицей? 7. Назовите основные свойства полей MS Access. 8. В чем назначение вкладки «Подстановка»? 9. Как определяются ключи и индексы? 10. Как создаются связи между таблицами в СУБД MS Access? 11. Какие принципы поддержки ссылочной целостности используются СУБД MS Access? 60 Лекция 14. Создание форм средствами MS Access Для создания формы следует в окне базы данных перейти на вкладку Формы и нажать кнопку «Создать», в результате MS Access предложит следующие способы создания форм:  Конструктор форм. Позволяет разрабатывать произвольные экранные формы с заданными свойствами для просмотра, ввода и редактирования данных.  Мастер форм. Позволяет достаточно быстро создать форму на основе выбранных данных.  Автоформа: в столбец. Автоматическое создание формы на основе указанного источника строк (таблицы/запроса), в которой все поля располагаются в один или несколько столбцов.  Автоформа: в ленточная. Автоматическое создание формы на основе указанного источника строк (таблицы/запроса), в которой все поля расположены в строку. Названия полей расположены сверху, как в таблице, но каждое значение расположено в собственном поле.  Автоформа: табличная. Автоматическое создание формы на основе указанного источника строк (таблицы/запроса), с обычной таблицей.  Диаграмма. Позволяет создавать форму, в которой данные представлены в графическом виде – в виде диаграммы.  Сводная таблица. Создает форму, в которую внедряется объект MS Excel в виде сводной таблицы. По сравнению с простыми автоформами, формы, созданные с помощью мастера более разнообразны по стилю оформления, могут содержать выбранные поля, в т.ч. и из нескольких связанных таблиц, но значительно уступают средствам конструктора. Мастер создания форм Для запуска Мастера форм необходимо в окне базы данных и нажать кнопку «Создать» и в раскрывшемся окне «Новая форма» выбрать вариант создания формы – «Мастер форм», откроется окно мастера «Создание форм». Традиционно, создание формы с использованием мастера выполняется по шагам: 1. В списке «Таблицы и запросы» выбираем объект на основе которого необходимо создать форму, затем производим выбор требуемых полей. Перечень полей создаваемой формы определяется кнопками «>», «>>», «<», «<<» позволяющими перемещать поля между списками «Доступные поля» и «Выбранные поля». Для формирования сложных иерархических форм эту процедуру можно выполнять несколько раз. 2. Определяется внешний вид формы: в один столбец, ленточный, табличный, выровненный (для иерархических форм определяется вариант расположения подчиненной формы: на текущей форме или отдельно). 3. Выбирается стиль формы из списка стилей. 4. Вводится имя создаваемой формы и определяется вариант дальнейшей работы (открытие формы для просмотра, изменение макета формы). 61 Конструктор форм и его инструменты Конструктор форм является более «мощным» средством, позволяющим формировать формы практически любой степени сложности. Процесс проектирования формы в режиме конструктора состоит в размещении объектов в областях формы и определении их свойств, а также связанных с ними событий и выполняемых действий. Для создания формы в режиме конструктора достаточно нажать кнопку «Конструктор» в рабочем окне «База данных» или нажать кнопку «Создать» и выбрать в списке «Конструктор». Окно конструктора форм изображено на рисунке 14.1. Рисунок 14.1 - Окно Конструктора форм Как видно по рисунку 14.1, окно в режиме конструктора может иметь горизонтальную и вертикальную линейку и сетку которые предназначены для более удобного и точного позицирования объектов на форме. При необходимости включить или выключить линейку или сетку можно командами Вид - Сетка и Вид - Линейка. Форма делится на несколько частей – разделов, каждый из которых имеет определенное назначение. Необходимо знать положение и назначение этих разделов. Например, от выбора раздела, в который будет помещен вычисляемый элемент управления, зависит способ вычисления результата. Рассмотрим эти разделы:  «Верхний колонтитул» и «Нижний колонтитул» позволяют выводить общую информацию, используются только при печати формы. Включение или отключение отображения этих разделов выполняется командой меню Вид – Колонтитулы.  «Заголовок формы» и «Примечание формы» В заголовке обычно располагают основные элементы управления, в том числе изображения, например эмблема компании, название и назначение формы. В разделе «Примечание формы» могут подводиться итоги, в том числе и в виде вычислений. Для включения или отключения этих разделов используется команда меню Вид - Заголовок/примечание формы 62  «Область данных» Элементы управления, расположенные в области данных, отображаются для каждой записи базовой таблицы/запроса. Изменение размера разделов выполняется перемещением их границ указателем мыши. Объекты формы и их размещение Неотъемлемой частью конструктора форм является панель элементов. Панель элементов является хранилищем объектов предназначенных для размещения на форме и составляет часть визуальной среды проектирования форм в режиме «Конструктор». Обычно панель элементов появляется автоматически при входе режим конструктора, но при необходимости ее можно отключить или показать командой Вид-Панель элементов. Краткое описание основных объектов панели элементов: Мастера – включает мастер создания объектов. Надпись – размещение на форме произвольного текста. Поле – размещение на форме данных из соответствующего поля базовой таблицы/запроса, вывод результатов вычислений, а также прием данных, вводимых пользователем. Выключатель – кнопка с фиксацией. Переключатель – селекторный переключатель. Флажок – контрольный переключатель. Поле со списком – элемент управления, объединяющий поле и раскрывающийся список. Список – список, допускающий прокрутку. Выбранное из списка значение можно ввести в новую запись или использовать для замены уже существующего значения. Кнопка – командная кнопка, позволяет осуществлять разнообразные действия (поиск записей, печать отчета, установка фильтров и т.п.). Рисунок – произвольный рисунок, не являющийся объектом OLE. Свободная рамка объекта – свободный объект OLE, не связанный с БД. Присоединенная рамка объекта – объект OLE размещенный в таблицах БД, например изображение, сохраненное в поле объекта OLE. Подчиненная форма/отчет – позволяет внедрить связанную подчиненную форму или отчет. Линия – графический объект создания структуры формы. Прямоугольник – графический объект создания структуры формы. Другие элементы – доступ к объектам ActiveX среды ОС Windows Для размещения на форме объектов ввода и редактирования данных рекомендуется использовать «Список полей», который можно вывести командой 63 Вид-Список полей. «Список полей» позволяет размещать объекты на форме простым перетаскиванием из списка на форму. Компоненты, расположенные на форме можно перемещать, удалять, изменять их свойства. Для изменения свойств, компонент следует выделить кликом мыши. Выделенный элемент имеет маркеры выделения в виде квадратиков, расположенных по краям компонента их можно использовать для изменения его размеров. Для выделения нескольких компонентов используется клавиша SHIFT, удерживаемая при выделении или используется метод протягивания мыши. Выделив несколько элементов, можно управлять их общими свойствами. Такой подход позволит сэкономить время и избежать ошибок и разногласий при установке свойств. Например, так можно установить единый стиль оформления для всех подписей полей на форме. Как и в любых приложениях, порядок обхода компонентов, расположенных на форме, осуществляется нажатием клавиши TAB. Порядок обхода определяется в окне диалога «Последовательность перехода» вызываемом командой Вид-Последовательность перехода. Свойства объектов формы Все компоненты формы характеризуются своими свойствами, значения которых, при размещении на форме, устанавливаются по умолчанию. Свойства предоставляют мощный и гибкий инструмент формирования дружественного пользовательского интерфейса и позволяют реализовать все скрытые возможности компонентов. Доступ к свойствам компонентов, а также к разделам форм или отчетов можно получить через команду контекстного меню Вид-Свойства или кнопка панели инструментов Свойства, можно использовать контекстное меню. Окно диалога «Свойства» (рисунок 14.2) содержит свойства, распределенные по пяти группам: «Макет», «Данные», «События», «Другие» и «Все». Рисунок 14.2 – Окно свойств объекта - Поле Каждая вкладка содержит свойства, сгруппированные по определенным принципам: 64  Вкладка «Макет» - содержит свойства, определяющие внешний вид компонента, размеры и его положение на форме.  Вкладка «Данные» - содержит свойства, определяющие связь с данными источника строк, а также ограничения на вводимые значения. Свойства данной вкладки устанавливаются автоматически из поля таблицы, с которым связан компонент. Основное свойство – «Данные», в него можно также вводить формулы для вычисления текущих значений.  Вкладка «События» - определяет перечень событий, на которые может реагировать компонент. В качестве значений указываются процедурыобработчики событий, реализованные средствами VBA или макросы.  Вкладка «Другие» - определяет свойства, не вошедшие в состав первых трех вкладок (имя компонента, возможность перехода по ТАВ, индекс перехода оп ТАВ и т.п.).  Вкладка «Все» - Содержит весь перечень свойств и событий в алфавитном порядке. Построение диаграмм Диаграммы используются для наглядного представления информации базы данных. В Access диаграмма как отдельный объект не существует, а может являться элементом формы либо отчета. Для построения диаграмм в СУБД Access используется модуль MSGraph, в который передаются все исходные данные для построения диаграммы с помощью механизма обмена данными в Windows. Для передачи данных можно использовать Мастер диаграмм, существующий в Access. Исходными данными для построения диаграмм могут быть данные таблиц либо запросов. Чаще всего диаграммы строят по результатам итоговых или перекрестных запросов. Рисунок 14.3 – Внешний вид диаграммы Диаграмму можно создать с использованием мастера нажав кнопку Создать и выбрать тип объекта – «Диаграмма». После указания источника 65 данных (таблица или запрос) начнется процесс построения диаграммы состоящий из следующих шагов: 1. В первом окне Мастера указываются поля, необходимые при построении диаграммы, перемещением из списка «Доступные поля» в список «Поля диаграммы». 2. Второе окно Мастера диаграмм служит для выбора типа диаграммы. Правильный выбор типа диаграммы имеет большое значение, т.к. неудачный выбор может привести к ложным выводам. 3. В третьем окне Мастера диаграмм можно изменить способ представления данных на диаграмме, меняя с помощью мыши положение кнопок с именами полей, расположенных в правой части диалогового окна. Результат построения диаграммы можно просмотреть, нажав кнопку Образец. 4. В последнем окне Мастера диаграмм вводится название диаграммы. На этом процесс построения диаграммы завершен. Контрольные вопросы 1. Какие способы можно использовать для создания форм? 2. Какие возможности имеет мастер создания форм? 3. В чем преимущества и недостатки использования конструктора форм? 4. В каких случаях предпочтительней использовать конструктор? 5. Назовите основные области форм. В чем их назначение? 6. Какие основные компоненты с панели элементов вы знаете? 7. Как получить доступ к свойствам объектов? 8. Какие категории свойств выделяют в окне диалога? 9. Назовите способ создания сложных форм. 10.Как создаются диаграммы? 66 Лекция 15. Создание отчетов средствами MS Access Отчеты представляют собой формат вывода информации в виде бумажного документа. По сравнению с формами, которые тоже могут выводиться на печать, отчеты обладают двумя принципиальными отличиями:  предоставляют широкие возможности для группировки и вычисления промежуточных и общих итогов для больших наборов данных;  отчеты могут быть использованы для получения эстетично оформленных материалов, наклеек и других документов. В основном отчеты применяются для просмотра «картины в целом», поэтому часто базируются на многотабличных итоговых запросах. Окно отчета и его инструменты Все инструменты, используемые для создания отчета эквиваленты соответствующим инструментам создания форм: Конструктор, Мастер отчетов, Автоотчет: в столбец, Автоотчет: ленточный, Мастер диаграмм, Почтовые наклейки. Среди всех вышеперечисленных средств, новым является только мастер создания почтовых наклеек. Этот мастер позволяет выбрать поля с именами и адресами, выполнить их форматирование для использования в качестве почтовых наклеек, имеет жесткую специализацию и поэтому в настоящем курсе лекций не рассматривается. Структура отчета в режиме конструктора практически полностью соответствует структуре формы и делится на разделы, имеющие то же назначение, но некоторые свои особенности:  «Верхний колонтитул» и «Нижний колонтитул» позволяют выводить общую информацию для всех страниц отчета, так как печатаются вверху и внизу каждой страницы соответственно, например, название документа, нумерация страниц, дата и время и т.д. Отобразить эти разделы можно командой меню Вид – Колонтитулы.  «Заголовок отчета» и «Примечание отчета» Печатаются только один раз в начале отчета и в конце отчета соответственно. В заголовок включается информация, обычно помещаемая на обложке, например эмблема компании, название отчета. Если в этих разделах помещен вычисляемый элемент управления, использующий статистическую функцию Sum, то сумма рассчитывается для всего отчета. Включить эти разделы можно командой Вид Заголовок/примечание отчета.  «Область данных» Раздел печатается один раз для каждой строки данных из источника записей. В нем размещаются элементы управления, составляющие основное содержание отчета.  «Заголовок группы» и «Примечание группы» используются для реализации группировки в отчетах. Выводятся перед и после каждой новой группы записей, соответственно. Используется для печати названия группы и вычисления статистических функций. Например, если отчет сгруппирован по изделиям, в заголовках групп можно указать их названия. Если поместить в эти 67 разделы вычисляемый элемент управления, использующий статистическую функцию Sum, сумма будет рассчитываться для текущей группы. Линейка с делениями и сетка помогают планировать расположение данных на странице. Изменить размер любого отдела можно, перетащив его границу. Панель элементов практически полностью аналогична панели элементов форм и в комментариях не нуждается. Отображение панели элементов, аналогично формам, управляется командой Вид - Панель элементов, либо кнопкой Панель элементов на панели инструментов. Для размещения полей таблиц или запросов в отчете удобно использовать окно «Список полей», которое можно включить командой Вид Список полей. В окне свойств можно установить свойства самого отчета, его разделов и элементов управления. Набор свойств, представленных в нем зависит от объекта. Для доступа к окну свойств компонента необходимо его выделить и нажать кнопку Свойства панели инструментов или выполнить команду меню Вид Свойства. Описание окна свойств имеется в предыдущей лекции. Вычисления в отчетах В качестве значения свойства «Данные» практически любого объекта, позволяющего выводить данные, можно задать выражения любой сложности с использованием арифметических операторов. Кроме того, в вычислениях можно использовать любые встроенные функции, а также функции, которые определены разработчиком в модуле VBA. При необходимости можно использовать построитель выражений. Чтобы указать MS Access на необходимость использования выражения, ввод выражения обязательно должен начинаться со знака равно (=). Например, чтобы рассчитать надбавку к значению поля «Стипендия» в отдельном поле, необходимо разместить на форме пустое поле и в качестве значения его свойства «Данные» (в свойствах объекта) указать выражение: = [Стипендия]*0,5 Ссылаться можно только на поля данных размещенных на данной форме, либо на открытых отдельных формах. Во втором случае необходимо предусмотреть принудительное открытие форм содержащих требуемые данные, например средствами макросов. Группировка в отчетах Отчеты, в отличие от форм, позволяют выполнять группировку. Для включения группировки в отчет, используется окно «Сортировка и группировка», которое можно вызвать командой меню Вид-Сортировка и группировка (рисунок 15.1). 68 Рисунок 15.1 - Окно Сортировка и группировка. В окне Сортировка и группировка можно определить до 10 полей или выражений, которые будут использоваться в отчете для группировки данных. Первый элемент списка определяет основную группу, а последующие подгруппы внутри групп. В первой строке столбца Поле/выражение раскрывается список полей базовой таблицы или запроса. Если начать ввод со знака (=), то можно ввести в этом столбце выражение, содержащее ссылку на любое поле базовой таблицы или запроса. Для каждого поля или выражения можно определить диапазон группировки, т.е. данные можно группировать не только по отдельным значениям, но и по диапазонам значений. Вид и размер диапазона зависят от типа данных поля или выражения. При группировке по текстовому полю можно начать новую группу при изменении первого или нескольких начальных символов в значениях поля. Например, чтобы образовать по одной группе для каждой буквы алфавита, необходимо определить создание новой группы при изменении первой буквы значения поля – таким образом, будет получена группа записей, в которых значения поля начинаются с букв А, Б, В и т.д. Чтобы задать группировку по первым символам, устанавливается значение свойства «Группировка» - По первым знакам, а значение свойства Интервал определяет число начальных символов, по которым необходимо образовывать группы. Для чисел можно установить свойство «Группировка» в значение Интервал. В этом случае свойство «Интервал» определяет диапазоны группировки. Если в свойстве «Интервал» указано значение 10, то группы будут формироваться через интервал равный 10, например: от –20 до –11, от –10 до –1, от 0 до 9, от 10 до 19, от 20 до 29 и т.д. Для полей даты/времени можно устанавливать значения свойства «Группировка»: По годам, По кварталам, По месяцам, По неделям, По дням, По часам, По минутам. Тогда свойство «Интервал» будет указывать на количество лет, кварталов, месяцев и т.д. в интервале. 69 Вычисление промежуточных итогов и итогов с накоплением Еще одна задача, которую обычно приходиться решать при составлении отчетов, - это получение итоговых величин по группам данных. Для получения промежуточных итоговых значений для групп в эти разделы можно поместить дополнительные свободные элементы управления, в которых используется выражение, построенное с применением итоговых функций: Sum, Min, Max, Avg, Count, First, Last, StDev и Var. Выражение в разделе группы, использующее итоговую функцию, не должно ссылаться на элемент управления, размещенный в области данных, т.е. не удастся создать вычисляемое поле в области данных и сослаться на него в выражении, использующем итоговую функцию. Но возможно повторить вычисляемое выражение в итоговом поле. Например, если вычисляемое поле в области данных с именем Цена содержит выражение [Цена за единицу]*[Количество], то в разделе примечаний группы необходимо использовать выражение Sum([Цена за единицу]*[Количество]), а не Sum([Цена]). Используя раздел примечаний отчета, можно получить общие итоги по всем записям для любого поля или выражения. В примечании отчета, как и в примечаниях групп, допустимы любые итоговые функции. Наряду с вычислением итогов в разделах групп можно создавать итоги с накоплением в области данных. Свойство «Сумма с накоплением» любого числового поля, размещенного в области данных, позволяет вычислять итоги, которые обнуляются в начале каждой группы или накапливаются на протяжении всего отчета. При размещении в области данных поля, для него устанавливается свойство «Данные» – значение поля для которого вычисляется сумма, а для свойства «Сумма с накоплением» - значение Для группы. Контрольные вопросы 1. В чем основное назначение отчетов? 2. Какие способы создания отчетов вы знаете? 3. Что общего и в чем различие между основными разделами форм и отчетов? 4. Как создаются вычисляемые поля? 5. Как выполняется группировка в отчетах? 6. Как вычисляются общие итоги? 7. Как вычислить итоги с накоплением? 8. Назовите механизм настройки отчетов. 70 Лекция 16. Создание запросов средствами MS Access 2000 Запросы являются единственным средств позволяющим извлекать, необходимую пользователям, информацию, а также средством управления данными. Опираясь на логику реляционных операций, запросы позволяют выполнять:  соединение данных нескольких таблиц;  отображение только требуемых полей;  формирование сложных критериев отбора записей;  вычисления с использованием данных из исходных таблиц;  группировку информации по каким-либо критериям;  модификацию данных в таблицах. MS Access предоставляет удобные механизмы быстрого создания запросов без особой подготовки, тогда как в других СУБД часто требуются знание языковых средств, для создания запросов. Для создания запросов MS Access предлагает следующие средства:  Конструктор запросов – средство конструирования запросов с использованием QBE (Query by example – запрос по образцу), требует минимальных знаний. Средство, уступающее режиму SQL.  Простой запрос – позволяет быстро создавать запросы на основе одной или нескольких таблиц, в том числе итоговые запросы.  Перекрестный запрос – позволяет создавать результирующие таблицы на основе результатов расчетов, полученных при анализе группы таблиц;  Запрос на повторяющиеся записи – средство создания запросов для выявления повторяющихся записей, выполнено в виде мастера.  Записи без подчиненных – позволяет создать запрос, отыскивающий те записи главной таблицы, которые не имеют подчиненных записей в дочерних таблицах. Создание запросов начинается с выбора категории «Запросы» окна диалога «Базы данных». Затем, кнопкой «Создать» может быть вызвано окно диалога «Новый запрос», в котором выбирается один из вариантов создания запроса (рисунок 16.1). Рисунок 16.1 - Диалоговое окно «Новый запрос» 71 Создание простых запросов с помощью мастера Мастер запросов позволяет сформировать запрос на основе одной или нескольких таблиц с возможностью группирования (итоговый запрос), но без определения условий выборки, именно поэтому он получил название «Простой запрос». Рисунок 16.2 - Окно мастера создания запросов Процесс создания запроса с помощью мастера сводится к следующим шагам:  Запускается мастер создания простых запросов, например командой – Создание запроса с помощью мастера  в раскрывающемся списке «Таблицы и запросы» последовательно выбираются таблицы или запросы, информация из которых необходима пользователю, а затем, из списка «Доступные поля» в список «Выбранные поля» перемещаются требуемые поля (рисунок 16.2).  На втором шаге работы мастера определяется тип запроса: подробный или итоговый. Если выбран итоговый запрос, то необходимо определить итоговые операции над полями запроса: Max, Min, Sum, Avg или Count.  На третьем шаге указываем имя запроса и один из вариантов действий: открыть запрос для просмотра данных или изменить макет запроса, в первом случае результаты запроса будут выданы на экран, а во втором, запрос откроется в режиме конструктора. Создание запроса в режиме конструктора Конструктор редко используется для создания запросов, чаще используется как средство модификации уже существующих. Конструктор запросов использует QBE (Query by example – запрос по образцу) позволяющий сформировать относительно сложные запросы на основе специального бланка, 72 заполнение которого позволяет сформировать запрос, безусловно, этот способ уступает непосредственному использованию конструкций языка SQL, но требует значительно меньших знаний. При необходимости можно использовать язык SQL, для этого необходимо перейти в режим SQL, переход выполняется командой контекстного меню конструктора запросов – «Режим SQL». Окно конструктора (рисунок 16.3) разделено на две части. Верхняя часть предназначена для расположения таблиц, на основе которых формируется запрос. Добавление таблиц в запрос осуществляется командой панели инструментов Отобразить таблицу. Рисунок 16.3 - Окно конструктора запроса. Пример ввода условия. Нижняя часть является бланком запроса (QBE), здесь выполняется построение самого запроса. Формирование запроса в QBE осуществляется определением таблиц и полей, участвующих в запросе, параметров вывода, сортировки, указанием условия отбора, вычислений и т.д. Включение новых полей в запрос выполняется простым перетаскиванием имени поля из списков полей таблиц (верхней части конструктора) в требуемый столбец строки «Поле:», при этом имя таблицы определяется автоматически. При необходимости сортировки по какому-либо полю в столбце этого поля устанавливается свойство «Сортировка» в значение По возрастанию или По убыванию, выбором из раскрывающегося списка. Строка – «Вывод на экран» управляет отображением полей в результирующем наборе данных, полученном в результате выполнения запроса. Для любого из полей можно указать условие отбора, тем самым, выполнив фильтрацию данных. Поля запроса, наследуют свойства, заданные для соответствующих полей таблицы, но можно изменить формат вывода полей. Для изменения формата вывода полей используется команда контекстного меню – Свойства. Количество строк в QBE может варьироваться в зависимости от типа создаваемого запроса, например при использовании группировки появится новая строка – «Групповая операция». 73 Установка критериев отбора записей Определение критериев выборки в запросах является одной из основных задач. Чтобы сформировать требуемый пользователю набор данных необходимо определить значения условий отбора в строке «Условие отбора:» QBE. В условиях отбора можно использовать логические операторы (or, and, not), операторы сравнения (<, >, <=, >=, <>, =), а также операторы Between, In и Like. Можно задать несколько условий отбора, соединенных логическим оператором или (or), для некоторого поля одним из двух способов: 1. ввести всё условие в одну ячейку строки «Условие отбора:», с использованием логического оператора или (or). 2. одну часть условия указать в ячейке строки «Условие отбора:», а вторую часть условия вписать в нижнюю ячейку строки «Или:». Оба варианта будут эквивалентными, второй вариант изображен на рисунке 16.4. Результатом этого запроса будет список студентов имеющих оценки 4 или 5. Рисунок 16.4 - Использование логического оператора или (or). Логическая операция и (and) используется в том случае, когда должны быть выполнены оба условия одновременно и только в этом случае запись будет включена в результирующий набор данных. Например, условие >2 and <5 даст список студентов имеющих оценки 3 и 4. Логическая операция and может быть использована не только для одного поля, но и для нескольких полей, условия в которых должны выполниться одновременно, однако, в этом случае она используется неявно. На рисунке 16.5 показано неявное использование логической операции and. В результате такого запроса будут выданы все студенты, проживающие в городе «Уфа» и имеющие оценку 5. Фактически, если заглянуть в код SQL (в режиме SQL) то можно увидеть следующее условие: [Студенты].[Город] = “Уфа” and [Студенты и занятия].[Балл] = “5” Рисунок 16.5 – Пример использования логической операции и (and) 74 Операторы и и или применяются как отдельно, так и в комбинации. Следует помнить, что условия связанные оператором и выполняются раньше условий, объединенных оператором или. Оператор Between позволяет задать диапазон значений, например диапазон от 10 до 20 может быть определен следующим образом: between 10 and 20 Оператор In позволяет использовать для сравнения список значений, указываемый в качестве аргумента. Например: in (“первый”,”второй”,”третий”) Оператор Like полезен для поиска образцов в текстовых полях, причем позволяет использовать шаблоны: * - обозначает любое количество символов; ? - любой одиночный символ; # - указывает, что в данной позиции должна быть цифра. Например, для выбора фамилии, начинающейся с буквы П и с окончанием “ов” можно записать следующие условие: Like П*ов Особый подход к работе с полями типа дата. Значение даты в условиях отбора должно быть заключено в символы #. Например: >#31.12.1996# Если требуется выделить временные события конкретного дня, месяца или года, то можно использовать встроенные функции MS Access:  Day(дата) – возвращает значение дня месяца в диапазоне от 1 до 31;  Month(дата) – возвращает значение месяца в диапазоне от 1 до 12;  Year(дата) – возвращает значение года в диапазоне от 100 до 9999; Текущую дату можно определить функцией Now(), используемую без аргументов. Вычисляемые поля В запросах на выборку можно определять вычисляемые поля, значения которых будут получены в результате каких-либо арифметических операций над полями участвующими в запросе. Например, если необходимо определить размер надбавки, равной 15% от оклада, то можно записать в строке «Поле» свободного столбца бланка запросов следующее выражение: Надбавка: [ Оклад]*0.15 В результате будет создано вычисляемое поле с именем «Надбавка», содержащее результат вычисления для каждой строки полученного набора данных. При создании выражений для вычисляемых полей можно использовать «Построитель выражений», который можно вызвать кнопкой «Построить», панели инструментов. В верхней части окна область ввода, в которой редактируется выражение. Выражение может быть введено вручную, но обычно используются кнопки арифметических и логических операторов, расположенные под областью ввода. Нижний, средний список содержит перечень полей входящих в запрос, эти 75 поля можно использовать в выражении. Поля добавляются двойным кликом мыши или нажатием кнопки «Вставить». Допустим, существует запрос «Отпуск товаров со склада», который содержит поля «КолОтп» (количество отпущенное) и «ЦОтп» (цена отпускная), требуется определить сумму продажи. Открываем запрос в режиме конструктора, переходим на пустой столбец и запускаем построитель выражений командой «Построить», контекстного меню. Находим в среднем списке имя поля «ЦОтп» и дважды щелкаем по нему мышкой. Нажимаем кнопку «*» и повторяем ту же последовательность действий для поля «КолОтп». Получаем требуемое выражение (рисунок 16.6). Рисунок 16.6 - Окно построителя выражений Прежде чем применить созданное выражение, рекомендуется определить имя вычисляемого поля, например «Сумма»: Сумма: [ЦОтп]*[КолОтп] После нажатия «ОК» полученный результат будет помещен в новый столбец бланка QBE. Контрольные вопросы Что такое запрос? В чем заключаются функции запроса? Какие способы создания запросов существуют в MS Access? Чем отличаются возможности различных способов создания запросов? 5. Как создается запрос с помощью мастера? 6. Что представляет собой бланк запросов? 7. Как сформировать условия отбора записей? 8. Назовите основные логические операторы. 9. Что представляет собой оператор Between? 10.В чем назначение оператора Like? 11.Как создаются вычисляемые поля? 12.Преимущества и недостатки конструктора запросов? 1. 2. 3. 4. 76 77 Лекция 17. Итоговые и модифицирующие запросы Итоговые запросы Итоговые запросы отличаются от обычных запросов на выборку. В них поля делятся на 2 типа:  поля, по значениям которых производится группировка данных (поле «Наименование», рисунок 17.1);  поля, по значениям которых проводятся вычисления (поля «Количество», «Сумма», рисунок 17.1); Поля, не относящиеся к вышеперечисленным типам (поля «Цена», «Единица» и «Дата», рисунок 17.1), исключаются из состава полей итогового запроса. Целью итоговых запросов является вычисление значений для групп записей, например, определение суммы значений по заданному полю для каждой группы, определение количества записей в каждой группе, поиск максимума или минимума среди значений поля для каждой группы и т.д. Для вычисления итоговых значений используются агрегатные функции стандарта SQL, основными являются следующие:  SUМ(Имя поля) – сумма значений указанного поля;  AVG(Имя поля) – среднее значение для записей указанного поля;  MIN(Имя поля) – минимальное значение в определенном поле;  MAX(Имя поля) – максимальное значение в определенном поле;  COUNT(*) – количество записей; В MS Access могут быть использованы функции расширения, отличные от функций предусмотренных стандартом, например:  FIRST(Имя поля) – первое значение в указанном поле;  LAST(Имя поля) – последнее значение в указанном поле. Рассмотрим пример группировки на основе таблицы «Продажи товаров» (рисунок 17.1), из которой необходимо получить информацию об итогах продаж каждого товара. Для каждой группы товаров должны быть рассчитаны: итоговая сумма продаж и количество единиц проданного товара. Таким образом, проводим группировку по полю «Наименование», вычисляем суммы для полей «Количество» и «Сумма» (рисунок 17.1). Прочерком помечены поля, которые не используются при подведении итогов и исключаются из запроса. Наименование Сахар Мука Окорока Мука Сахар Цена 25 20 70 20 25 Единица кг кг кг кг кг Количество 10 100 10 50 50 Сумма 250 2000 700 1500 1250 Дата 01.05.2008 01.05.2008 01.05.2008 02.05.2008 01.05.2008 Группировка - - Sum Sum - Рисунок 17.1 – Таблица «Продажи товаров» 78 В результате выполнения группировки получаем три группы, внутри каждой группы рассчитана сумма по количеству проданных товаров и общая сумма продаж (рисунок 17.2) Наименование Сахар Мука Окорока Количество 60 150 10 Сумма 1500 3500 700 Группировка Sum Sum Рисунок 17.2 – Результат выполнения группировки Итоговые запросы средствами MS Access могут быть созданы средствами мастера или конструктора. Процесс создания итогового запроса с помощью средства «Простой запрос» сводится к следующим шагам:  Запускается мастер создания простых запросов, например командой – Создание запроса с помощью мастера  в раскрывающемся списке «Таблицы и запросы» последовательно выбираются таблицы или запросы, информация из которых необходима пользователю, а затем, из списка «Доступные поля» в список «Выбранные поля» перемещаются требуемые поля.  На втором шаге работы мастера определяется тип запроса «Итоговый». Нажимаем кнопку «Итоги…» и в окне диалога «Итоги» (рисунок 17.3), выбираем способы вычисления итогов: Max, Min, Sum, Avg или Count (подсчет числа записей). Рисунок 17.3 – Фрагмент окна диалога «Итоги»  На третьем шаге указываем имя запроса и один из вариантов действий: открыть запрос для просмотра данных или изменить макет запроса, в первом случае результаты запроса будут выданы на экран, а во втором, запрос откроется в режиме конструктора. 79 Для создания итогового запроса в режиме конструктора можно использовать кнопку «Групповые операции» на панели инструментов или воспользоваться командой Вид - Групповые операции. В результате, в бланке запроса появится новая строка – «Групповая операция». Если для соответствующего поля из списка выбрать функцию Группировка (рисунок 17.4), то при выполнении запроса записи будут объединяться по этому полю и вся группа будет представлена одной строкой. Рисунок 17.4 - Строка «Групповая операция» в бланке QBE Для всех остальных полей необходимо определить итоговые функции, как изображено на рисунке 17.5. Рисунок 17.5 – Определение итоговых функций для полей запроса Группировка и итоговые выражения могут проводиться не только по реально существующим полям, но и по вычисляемым в запросе выражениям. Например, если сумма продажи товара не задана явно, однако присутствуют поля «Количество» и «Цена единицы», то сумма может быть получена как: [Цена] * [Количество] В этом случае итоговую сумму продаж можно получить выражением: Сумма: Sum (Количество] * [Цена]) На рисунке 17.5 поле «Сумма» получено с использованием вышеуказанного метода. В случае необходимости, по вычисляемым значениям или результатам работы встроенных функций можно проводить группирование. 80 Модифицирующие запросы В большинстве случаев запросы выполняют выборку тех или иных данных из таблиц БД, однако чаще всего этого бывает недостаточно, например, приходится решать задачи связанные с модификацией множества записей на основе выбранных данных. Под словом модификация понимается изменение, добавление или удаление данных из таблиц БД. Представим некоторую таблицу (прайс-лист), содержащую информацию о товарах и их ценах, пусть необходимо произвести наценку на импортные товары. Если товаров, на которые необходимо произвести наценку немного, то пользователя не затруднит внести изменения, если же таких около 20 это уже вызовет определенные проблемы. В реальных ситуациях подобных записей может оказаться сотни и тысячи. Похожие ситуации могут возникать при удалении и добавлении записей. Для решения подобных задач используются модифицирующие запросы. Выделяют следующие модифицирующие запросы:  запросы на удаление;  запросы на обновление;  запросы на добавление;  запросы на создание таблицы (не является модифицирующим, но имеет подобный способ создания). Внимание! При использовании модифицирующих запросов, желательно предусматривать меры по обеспечению сохранности информации. Например, при удалении (обновлении) неплохо было бы сохранить удаляемые (обновляемые) записи во временно созданную таблицу (при помощи запроса на создание таблицы), эта мера позволит восстановить данные при ошибочных модификациях. Можно выделить несколько основных этапов присутствующих при создании всех модифицирующих запросов в СУБД MS Access: 1. Создание запроса на выборку с указанием условия отбора. 2. Запуск запроса на выборку и проверка полученных результатов. В результат запроса должны войти только те записи, которые должны быть подвергнуты изменениям. 3. Преобразование запроса на выборку в соответствующий модифицирующий запрос (только в режиме конструктора запросов). Создание запроса на удаление Предположим, что необходимо удалить множество записей из одной таблицы, условие по которому вы будете отбирать записи на удаление известно. В этом случае удобнее не удалять записи с использованием клавиши Del, а применить запрос на удаление. Опишем последовательность создания запроса на удаление: 1. Создать запрос на выборку по таблице, из которой необходимо удалить записи. Указать то условие отбора, которое обеспечит выборку записей, подлежащих удалению. 81 2. Выполнить запрос на выборку. В результате запроса должны быть получены только те записи, которые подлежат удалению! 3. Преобразовать запрос на выборку в запрос на удаление командой Запрос – Удаление или Тип запроса – Удаление и из раскрывающегося списка выбрать запрос на удаление (только в режиме конструктора запросов). Внимание! Удаление записей не всегда приводит к ожидаемому результату. Необходимо учитывать политику сохранения ссылочной целостности. Например, если таблица является частью отношения один-ко-многим и выступает в роли главной, при этом установлены параметры каскадного удаления, то в результате будут удалены записи не только из этой таблицы, но и все связанные записи дочерних таблиц. Создание запроса на обновление Запросы на обновление позволяют изменять значения выбранных полей таблицы по записям, удовлетворяющим определенным условиям. Опишем последовательность создания запроса на обновление: 1. Создать запрос на выборку по таблице, в которой необходимо обновить записи и указать то условие отбора, которое обеспечит выборку записей, подлежащих обновлению. 2. Выполнить запрос на выборку. В результате запроса должны быть получены только те записи, которые необходимо обновить! 3. Преобразовать запрос на выборку в запрос на обновление командой Запрос – Обновление или Тип запроса - Обновление. В результате в бланке запросов появится новая строка «Обновление». 4. В строке «Обновление» бланка запроса для поля, значения которого подлежат обновлению, указывается формула расчета новых значений или подставляются константы, которые будут замещать значения соответствующих полей отобранных записей. Создание запроса на добавление Данный тип запросов используется для добавления записей из одной или нескольких таблиц в другую. Результирующая таблица может являться частью текущей или другой БД. Количество и наименования полей исходной и результирующей таблиц может не совпадать, что учитывается при создании запроса. Опишем последовательность создания запроса на добавление: 1. Создать запрос на выборку для таблицы, из которой необходимо отобрать записи и указать то условие отбора, которое обеспечит выборку записей, подлежащих добавлению. 2. Выполнить запрос на выборку. В результате запроса должны быть получены только те записи, которые необходимо добавить! 3. Преобразовать запрос на выборку в запрос на обновление командой Запрос – Добавление или Тип запроса - Добавление. В результате в бланке 82 запросов появится новая строка «Добавление», позволяющая произвести совмещение полей двух таблиц. 4. Если имена и типы полей обеих таблиц совпадают и необходимо перенести значения всех полей, то для строки «Поле» и для строки «Добавление» необходимо выставить значение «*». Если известно, что имена полей не совпадают, то их можно назначить принудительно для каждого поля (строки «Поле») в строке «Добавление». Запрос на создание таблицы Запрос на создание таблицы позволяет создать таблицу и разместить в ней данные из одной или нескольких таблиц. В некоторых случаях требуется создание временных таблиц, например, сохранить данные перед изменением для реализации возможности отката, т.е. отмены изменений. Опишем последовательность создания запроса на создание таблицы: 1. Создать запрос на выборку для таблицы (или нескольких таблиц), данные которой планируется поместить в новую таблицу и указать условие отбора, которое позволит выбрать необходимые записи. 2. Выполнить запрос на выборку. В результате запроса должны быть получены только те записи, которые необходимо поместить в новую таблицу! 3. Преобразовать запрос на выборку в запрос на создание таблицы командой Запрос – Создание таблицы или Тип запроса - Создание таблицы. В результате появиться окно диалога «Создание таблицы», в котором необходимо указать имя таблицы и ее местоположение (в этой или другой БД). После нажатия кнопки «ОК» будет создан запрос на создание таблицы. Контрольные вопросы 1. Что такое итоговый запрос? 2. Как создается итоговый запрос средствами мастера? 3. Как создается итоговый запрос средствами конструктора? 4. Какие групповые функции вы знаете? 5. Что такое модифицирующий запрос? 6. Относится ли запрос на создание таблицы к модифицирующим? 7. Как создается запрос на удаление? 8. Как создается запрос на добавление? 9. Как создается запрос на обновление? 10. Что общего в механизме создания модифицирующих запросов? 83