51472

Основы проектирования баз данных средствами СУБД

Лекция

Информатика, кибернетика и программирование

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

Русский

2014-02-11

474.58 KB

6 чел.

Тема 5.5. Основы проектирования баз данных средствами СУБД  

5.5.1.  Основные понятия баз данных

5.5.2.  Основные понятия реляционной модели данных

5.5.3.  Основные понятия ER-модели

5.5.4.  Создание БД средствами  MS Access

5.3.4.1. Создание и работа с таблицами

5.3.4.2. Работа с формами

5.3.4.3. Запросы-выборки

5.5.5.  Задачи для самостоятельного решения по теме «Основы  

          проектирования  баз данных  средствами СУБД  Access»

5.5.6. Тестовые задания по теме «Основы  проектирования баз данных

         средствами СУБД  Access»

5.5.1. Основные понятия баз данных

Восприятие реального мира можно сопоставить с последовательностью разных, хотя иногда и взаимосвязанных, явлений. С давних времен люди пытались описать эти явления (даже тогда, когда не могли их понять). Такое описание называют данными.

А как осуществляется фиксация данных? Традиционно фиксация данных осуществляется с помощью конкретных средств общения (например, с помощью естественного языка или изображений) на конкретном носителе. Обычно  при естественном общении данные (факты, явления, события, идеи или предметы) и их интерпретация (семантика) фиксируются совместно, так как естественный язык (язык интерпретации) достаточно гибок для представления того и другого. Примером может служить утверждение "Номер телефона Иванова И.И. – 273-16-38". Здесь "273-16-38" – данное, а " Номер телефона Иванова И.И." – его семантика.

С понятием данные тесно связано понятие информации.

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

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

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

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

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

Для облегчения обработки информации используются информационные системы (ИС). Автоматизированными называются  ИС, в которых применяются  ЭВМ.

В широком понимании под определение ИС подпадает любая система обработки информации. Более узкая трактовка понятия  ИС – это совокупность аппаратно-программных средств, задействованных для решения некоторой прикладной задачи.

Исторически, одним из первых этапов интеграции данных было объединение данных в файлы.

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

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

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

Уточним элементы этого определения:

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

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


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

  1.  в численных расчетах – это обычно сложные алгоритмы обработки данных, простые неструктурированные данные или слабоструктурированные данные, сравнительно небольшой объем обрабатываемых данных;
  2.  в информационных системах - это обычно относительно простые алгоритмы обработки данных, имеющие сложную структуру и большой объем информации.

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

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

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

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

Приложение (прикладная программа) представляет собой программу или комплекс программ, обеспечивающих автоматизацию обработки информации для прикладной задачи. Мы будем рассматривать приложения, использующие БД. Приложения могут создаваться в среде или вне среды СУБД с помощью систем программирования, использующих средства доступа к БД. Например, Delphi, С++ Builder, Visual Basic, С#.

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

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

При выполнении основных из этих функций СУБД должна использовать различные описания данных.

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

Рис. 5.5.1-1. Уровни моделей данных

Остальные модели, показанные на рис. 5.5.1-1, являются компьютеро-ориентированными. С их помощью СУБД дает возможность программам и пользователям осуществлять доступ к хранимым данным лишь по их именам, не заботясь о физическом расположении этих данных. Нужные данные отыскиваются СУБД на внешних запоминающих устройствах по физической модели данных.

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

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

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

Инфологическая модель должна быть отображена в компьютеро-ориентированную даталогическую модель, «понятную» СУБД.

5.5.2. Основные понятия реляционной модели данных

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

Реляционная модель данных подразумевает определенный подход при создании данных.

Во-первых, БД представляются на внешнем, не зависящем от структуры ЭВМ уровне в виде совокупности двумерных таблиц (структурная часть реляционной модели). Такие таблицы в реляционной теории называются отношениями (relation).

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

В-третьих, манипулирование данными реляционной базы данных осуществляется в соответствии со специально разработанной для этой цели реляционными операциями (манипуляционная часть реляционной модели).

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

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

Домен - это семантическое понятие. Домен можно рассматривать как подмножество значений некоторого типа данных, имеющих определенный смысл. Домен характеризуется следующими свойствами: домен имеет уникальное имя (в пределах базы данных), домен определен на некотором простом типе данных или на другом домене, домен может иметь некоторое логическое условие, позволяющее описать подмножество данных, допустимых для данного домена, домен несет определенную смысловую нагрузку. Например, домен D, имеющий смысл «Возраст сотрудника» можно описать как следующее подмножество множества натуральных чисел:  .

Если тип данных можно считать множеством всех возможных значений данного типа, то домен напоминает подмножество в этом множестве. Отличие домена от понятия подмножества состоит именно в том, что домен отражает семантику, определенную предметной областью. Может быть несколько доменов, совпадающих как подмножества, но несущих различный смысл. Например, домены «Вес детали» и «Имеющееся количество» можно одинаково описать как множество неотрицательных целых чисел, но смысл этих доменов будет различным, и это будут различные домены.

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

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

Число атрибутов в отношении называют степенью (арностью) отношения.

Мощность множества кортежей отношения называют мощностью отношения.

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

Пример 5.5.2-1. Рассмотрим отношение «СОТРУДНИКИ» заданное на доменах «Номер_сотрудника», «Фамилия», «Зарплата», «Номер_отдела». Так как все домены различны, то имена атрибутов отношения удобно назвать так же, как и соответствующие домены. Заголовок отношения имеет вид:

СОТРУДНИКИ (Номер_сотрудника, Фамилия, Зарплата, Номер_отдела).

Пусть в данный момент отношение содержит три кортежа: (1,Иванов, 1000, 1),    
(2, Петров, 2000, 2), (3, Сидоров, 3000, 1) .  Такое отношение естественным образом представляется в виде таблицы 5.5.2-1.

                    Таблица 5.5.2-1

Номер сотрудника

Фамилия

Зарплата

Номер_отдела

1

Иванов

1000

1

2

Петров

2000

2

3

Сидоров

3000

1

Реляционной базой данных называется набор отношений.

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

Хотя любое отношение можно изобразить в виде таблицы, нужно четко понимать, что отношения не являются таблицами. Это близкие, но не совпадающие понятия  
(табл. 5.5.2-2).

     Таблица 5.5.2-2

Реляционный термин

Соответствующий «табличный» термин

База данных

Набор таблиц

Схема базы данных

Набор заголовков таблиц

Отношение

Таблица

Заголовок отношения

Заголовок таблицы

Тело отношения

Тело таблицы

Атрибут отношения

Наименование столбца таблицы

Кортеж отношения

Строка таблицы

Степень (арность) отношения

Количество столбцов таблицы

Мощность отношения

Количество строк таблицы

Домены и типы данных

Типы данных в ячейках таблицы

Первичный ключ

Один или несколько атрибутов

Рассмотрим основные свойства отношений. В этих свойствах в основном и состоят различия между отношениями и таблицами:

  1.  В отношении нет одинаковых кортежей. Действительно, тело отношения есть множество кортежей и, как всякое множество, не может содержать неразличимые элементы. Таблицы, в отличие от отношений, могут содержать одинаковые строки.
  2.  Кортежи не упорядочены (сверху вниз). Действительно, несмотря на то, что мы изобразили отношение «СОТРУДНИКИ» в виде таблицы, нельзя сказать, что сотрудник Иванов "предшествует" сотруднику Петрову. Причина та же - тело отношения есть множество, а множество не упорядочено. Это вторая причина, по которой нельзя отождествить отношения и таблицы - строки в таблицах упорядочены. Одно и то же отношение может быть изображено разными таблицами, в которых строки идут в различном порядке.
  3.  Атрибуты не упорядочены (слева направо). Так как каждый атрибут имеет уникальное имя в пределах отношения, то порядок атрибутов не имеет значения. Это свойство несколько отличает отношение от математического определения отношения (компоненты кортежей там упорядочены). Это также третья причина, по которой нельзя отождествить отношения и таблицы - столбцы в таблице упорядочены. Одно и то же отношение может быть изображено разными таблицами, в которых столбцы идут в различном порядке.
  4.  Все значения атрибутов атомарные. Это следует из того, что лежащие в их основе атрибуты имеют атомарные значения. Это четвертое отличие отношений от таблиц - в ячейки таблиц можно поместить что угодно - массивы, структуры, и даже другие таблицы.

5.5.3.  Основные понятия ER-модели

Одной из наиболее популярных семантических моделей данных на этапе инфологического проектирования является неформальная модель "Сущность-Связь" (Entity-Relationship - ER-модель). Моделирование предметной области базируется на использовании графических диаграмм, включающих небольшое число разнородных компонентов. В связи с наглядностью представления концептуальных схем баз данных (и не только их) ER-модели получили широкое распространение в CASE-системах.

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

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

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

Экземпляр сущностиэто конкретный представитель данной сущности. Например, представителем сущности «СТУДЕНТ» может быть «Петров И.И.». Экземпляры сущностей должны быть различимы, то есть сущности должны иметь некоторые свойства, уникальные для каждого экземпляра этой сущности.

Таким образом, необходимо различать такие понятия, как класс (тип) сущности и экземпляр сущности. Понятие тип сущности относится к набору однородных личностей, предметов, событий или идей, выступающих как целое. Экземпляр сущности относится к конкретной вещи в наборе. Например, типом сущности может быть ГОРОД, а экземпляром – Москва, Киев и т.д.

Атрибут сущности – это именованная характеристика сущности, являющаяся (определяющая) некоторым свойством  сущности и принимающая значения из некоторого множества значений. Его наименование должно быть уникальным для конкретного типа сущности, но может быть одинаковым для различного типа сущностей. Например,  Цвет может быть определен для многих сущностей: СОБАКА, АВТОМОБИЛЬ, ДЫМ и т.д. Атрибуты используются для определения того, какая информация должна быть собрана о сущности. Примерами атрибутов для сущности АВТОМОБИЛЬ являются Тип, Марка, Номерной знак, Цвет и т.д. Здесь также существует различие между типом и экземпляром. Тип атрибута Цвет имеет много экземпляров или  значений: Красный, Синий, Банановый, Белая ночь и т.д., однако каждому экземпляру сущности присваивается только одно значение атрибута.

Домен – это диапазон допустимых значений, которые может принимать атрибут. 

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

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

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

Например, связь между сущностями может выражаться следующей фразой – «каждый студент обязан числиться ровно в одной группе».

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

Первый вариант модели «сущность-связь» был предложен в середине 1970-х годов Питером Пин-Шен Ченом. К настоящему времени существуют различные способы описания ER-моделей – так называемые НОТАЦИИ.

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

                                   

 

 

Рис. 5.5.3-1

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

Каждая связь может иметь один из следующих типов связи (рис. 5.5.3-2).

Рис. 5.5.3-2

Такой способ изображения связей соответствует нотации Балкера. В нотации IDEF1X мощность связи со стороны «много» изображается черным кружком (рис.5.5.3-3).

Рис. 5.5.3-3

Связь типа один-к-одному (1:1) означает, что один экземпляр первой сущности (левой) связан с одним экземпляром второй сущности (правой). Связь один-к-одному чаще всего свидетельствует о том, что на самом деле мы имеем всего одну сущность, неправильно разделенную на две.

Связь типа один-ко-многим (1:M) означает, что один экземпляр первой сущности (левой) связан с одним или несколькими экземплярами второй сущности (правой).

Связь типа много-ко-многим (N:M) означает, что один экземпляр первой сущности (левой) связан с одним или несколькими экземпляром второй сущности (правой), а также,  что один экземпляр второй сущности (правой) связан с одним или несколькими экземпляром первой сущности (левой).

При большом объеме, ввод лишь нескольких основных атрибутов в описание значительно усложнит ER-диаграмму. В связи с этим язык ER-диаграмм используется для построения небольших моделей и иллюстрации отдельных фрагментов больших. Чаще же применяется менее наглядный, но более содержательный язык инфологического моделирования (ЯИМ), в котором сущности и ассоциации представляются предложениями вида:

СУЩНОСТЬ (атр. 1, атр. 2 , ..., атр. n)

АССОЦИАЦИЯ[СУЩНОСТЬ S1,СУЩНОСТЬ S2,...](атр.1,атр.2,..., атр. n)

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

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

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

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

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

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

Для формализации связи "один ко многим" вспомогательные атрибуты должны быть добавлены к сущности на стороне "много".

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

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

Пример 5.5.3.-1. Создать ER-диаграмму (модель) предметной области БИБЛИОТЕКА.

Описание предметной области.

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

 Уточнение задания.

Данные о книгах: номер книги в библиотеке (код книги), автор, название, количество. Данные о каждом читателе: номер читательского билета (код читателя), ФИО читателя. Данные о факте выдачи: дата сдачи книги в библиотеку.

Описание сущностей.

Опишем сущности на языке инфологического моделирования:

КНИГИ (КодКниги, Автор, Название, Количество)

ЧИТАТЕЛИ (КодЧитателя, ФИО)

Назначение ключевых атрибутов.

В сущности КНИГИ  ключевой атрибут – КодКниги, в сущности ЧИТАТЕЛИ  ключевой атрибут – КодЧитателя.

Описание связей.

Одна книга может быть выдана многим читателям, т.к. она имеет много экземпляров, один читатель может получить много книг. Таким образом, связь между сущностями КНИГИ и ЧИТАТЕЛИ «много-ко-многим».

Формализация связей.

При формализации связи «много-ко-многим» вводится дополнительная сущность-связь. Название такой сущности – ВЫДАЧА. Ключи сущностей КНИГИ и ЧИТАТЕЛИ входят как внешние в сущность ВЫДАЧА, и образуют составной ключ. Таким образом, на языке инфологического моделирования описание сущностей выглядит следующим образом:

КНИГИ ( КодКниги, Автор, Название, Количество)

ВЫДАЧА [КНИГИ, ЧИТАТЕЛИ ](КодКниги, КодЧитателя, ДатаСдачи)

ЧИТАТЕЛИ(КодЧитателя, ФИО)

Представление ER-модели.

ER-модель разрабатываемой БД представлена на рис. 5.5.3-4, где PK – это обозначение ключевого параметра. 

Рис. 5.3.3-4

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

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

Очевидно, что для одной и той же предметной области реляционные отношения можно спроектировать множеством различных способов. Например, можно спроектировать несколько отношений с большим количеством атрибутов, или наоборот, разнести все атрибуты по большому числу мелких отношений. В общем случае  проектирование схемы БД может быть выполнено двумя путями:

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

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

5.5.4. Создание БД средствами  СУБД Access2 

Первая версия Access была создана в 1993 г.  фирмой Microsoft. MS Access - это функционально полная реляционная СУБД, работающая в среде Windows. СУБД Access позволяет создавать довольно сложные базы данных, определяет структуры таблиц и связи между ними. СУБД Access обладает также системой создания запросов, отчетов и форм любой сложности. В Access, как любом приложении Windows, можно использовать все возможности обмена данными между приложениями, что позволяет включить в базу данных графическую и (или) звуковую информацию.

В Access база данных включает в себя все объекты, связанные с хранимыми данными (таблицы, формы, отчеты, запросы, макросы, модули). Все объекты  Access хранятся в одном файле с расширением  .mdb. В таблицах хранятся данные, которые можно просматривать, редактировать, добавлять. Используя формы, можно выводить данные на экран в удобном виде, просматривать и изменять их. Запросы позволяют быстро выбирать необходимую информацию из таблиц. С помощью отчетов можно создавать различные виды документов для вывода на печать, макросы и модули позволяют автоматизировать работу с базой данных. 

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

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

Для создания новой таблицы нужно перейти на вкладку Таблица  и нажать кнопку  Создать.

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

Рис. 5.5.4-1

Тип данных полей. Каждая строка в столбце Тип данных является полем со списком, элементами которого являются типы данных Access. Тип поля определяется характером вводимых в него данных:

  1.  Текстовый.  Алфавитно-цифровые данные (до 255 символов) 
  2.  Поле Mеmо.  Алфавитно-цифровые данные - предложения, абзацы, тексты ( до 64 000 символов)
  3.  Числовой.  Различные числовые данные (имеет несколько форматов). 
  4.  Дата\Время. Дата или время в одном из предлагаемых Access форматов. 
  5.  Денежный Денежные суммы, хранящиеся с 8 знаками  в десятичной части. В целой части каждые три разряда разделяются запятой. 
  6.  Счетчик.  Уникальное длинное целое, создаваемое Access для каждой новой записи 
  7.  Логический.  Логические данные, имеющие значения Истина или Ложь 
  8.  Поле объекта  OLE.  Картинки, диаграммы и другие объекты OLE из приложений Windows 
  9.  Гиперссылка.  В полях этого типа хранятся гиперссылки, которые представляют собой путь к файлу на жестком диске, либо адрес в Интернете. 

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

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

 Размер поля - определяется только для текстовых  и Memo-полей; указывает максимальное количество символов  в данном поле. По умолчанию длина текстового поля составляет 50 символов

Формат поля – определяется для полей числового, денежного типа, полей типа Счетчик и  Дата\Время. Выбирается один из форматов представления данных.

Число десятичных знаков - определяет количество разрядов в дробной части числа.

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

Подпись поля - содержит надпись, которая может быть выведена рядом с полем в форме или отчете ( данная надпись может и не совпадать  с именем поля, а также может содержать поясняющие сведения).

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

Условие на значение - определяет множество значений, которые пользователь может вводить в это поле при заполнении таблицы. Это свойство позволяет избежать ввода недопустимых в  данном поле значений.   Например, если стипендия студента не может превышать 250 р., то для этого поля можно задать условие на значение: <=250.

Сообщение об ошибке - определяет сообщение, которое появляется на экране в случае ввода  недопустимого значения.

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

Пустые строки - установка, которая определяет, допускается ли ввод в данное поле пустых строк.

Индексированное поле - определяет простые индексы для ускорения поиска записей.

Для сохранения структуры таблицы нужно ввести команду Файл\Сохранить и в окне Сохранение ввести имя таблицы.

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

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

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

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

Для определения связей нужно перейти в окно базы данных и выполнить команду Сервис\Схема данных или на панели инструментов нажать кнопку Схема данных. После этого Access откроет  окно Схема данных   и окно Добавление таблицы. В окне Добавление таблицы нужно выделить имена таблиц, добавляемых в схему данных, и нажать на кнопку  Добавить (рис. 5.5.4-2). После этого данное окно можно закрыть.

Рис. 5.5.4-2

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

Рис. 5.5.4-3

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

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

Если выбрать  первую опцию, то при изменении какого-либо значения ключевого поля в ключевой таблице Access автоматически обновит  значения этого поля для соответствующих записей во всех связанных таблицах. Например, если у одного из студентов изменился номер зачетки в таблице СТУДЕНТЫ, то он автоматически должен поменяться и в таблице ЭКЗАМЕНЫ.

Выбор второй опции  при удалении одной из записей в ключевой таблице приведет к удалению тех записей  в таблице со стороны "много", которые имеют такое же значение ключа. Например, если из таблицы СТУДЕНТЫ удалить запись об одном из студентов, то записи о результатах сданных им экзаменов будут удалены автоматически.

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

 

 Рис. 5.5.4-4

5.5.4.2. Работа с формами

Формы являются основным средством организации интерфейса пользователя  в приложениях Access. Хорошо разработанные формы позволяют работать с приложением даже неподготовленному пользователю.

Чаще всего формы создаются в следующих целях:

  1.  ввод и редактирование данных – это наиболее распространенный способ использования форм. Формы обеспечивают вывод на экран данных в удобном для пользователя виде;
  2.  управление ходом выполнения приложения – в этом случае формы используются для запуска макросов;
  3.  вывод сообщений – с помощью форм можно вывести на экран информацию, предупреждение или сообщение об ошибках;
  4.  печать информации – несмотря на то, что для печати информации чаще всего используются отчеты, можно напечатать информацию, содержащуюся в форме.

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

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

Access предлагает следующие способы создания форм:

Конструктор форм. Этот способ позволяет разрабатывать собственные экранные формы с заданными свойствами для просмотра, ввода и редактирования данных.

Мастер форм. Позволяет достаточно быстро создать форму на основе выбранных для нее данных.

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

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

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

Диаграмма. Позволяет создавать форму, данные в которой представлены в виде диаграммы.

Сводная таблица. Этот Мастер использует Microsoft Excel для создания объекта сводной таблицы, и Microsoft Access для создания формы, в которую внедряется объект сводной таблицы. 

Для вариантов создания форм Автоформа: в столбец, Автоформа: ленточная,  Автоформа: табличная и Диаграмма необходимо выбрать источник данных для создания формы. Во всех остальных случаях это делать необязательно.

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

Создавать и редактировать формы любой степени сложности позволяет только Конструктор форм. Также в режиме Конструктора можно отредактировать формы, созданные Мастером, или автоформы.

Создать форму, позволяющую просматривать и редактировать записи только одной таблицы, довольно просто. Но на практике требуются формы, позволяющие производить  одновременный ввод или просмотр данных, хранимых в двух или более связанных таблицах.  Например, при выводе информации о результатах экзаменов,  было бы неплохо сразу размещать необходимую  информацию о студентах. Эти данные реально хранятся в двух разных таблицах (СТУДЕНТЫ и ЭКЗАМЕНЫ), связанных отношением “один-ко-многим”.

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

 

Рис.  5.5.4-5.    Пример иерархической формы

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

Наиболее простым способом  создания иерархической формы является использование Мастера форм.

Как видно из рисунка, форма состоит из двух частей. В верхней половине окна формы находятся поля таблицы СТУДЕНТЫ, т.е. таблицы со стороны “один”. В нижней половине находится подчиненная форма, содержащая поля из таблицы ЭКЗАМЕНЫ (таблица со стороны “много”).

Для создания такой формы нужно:

- перейти на вкладку Формы в окне базы данных, нажать кнопку  Создать и выбрать в качестве режима создания Мастер форм;

- в окне Новая форма в качестве источника записей формы указать таблицу ЭКЗАМЕНЫ (в иерархических формах базовой всегда является  таблица со стороны “много”);

- в следующем окне Мастера форм определить главную и подчиненную таблицы. Здесь в качестве главной следует выбрать таблицу СТУДЕНТЫ. В правой части данного окна диалога отображаются поля таблиц: в верхней части поля главной, а в нижней – поля подчиненной таблицы. Опция Связанные формы предназначена для представления данных без использования подчиненной формы;

- далее следует выбрать  внешний вид подчиненной формы: табличный или ленточный;

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

5.5.4.3. Запросы-выборки

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

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

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

  1.  QBE (Query By Example) – язык запросов по образцу (используется при визуальном построении  запроса);
  2.  SQL (Structured Query Language) – структурированный язык запросов SQL (используется при описании запросов).

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

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

 

Для создания запроса следует открыть базу данных, и,  перейдя на  вкладку Запросы  нажать кнопку  Создать. Появится окно Новый запрос для выбора способа построения запроса (рис 5.5.4-6).

Рис. 5.5.4-6.  Диалоговое окно Новый запрос

Конструктор - создает запрос на основе пустого бланка запроса.

Простой запрос  - создает простой запрос из определенных  полей.

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

Повторяющиеся записи – создает запрос, выбирающий повторяющиеся записи  из таблицы или простого запроса.

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

При выборе  Конструктора через  диалоговое окно Добавление таблицы
(рис. 5.5.4-6)  добавляются имена таблиц в окно конструктора запроса.

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

Имена таблиц должны быть представлены в окне конструктора запроса
(рис 5.5.4-7).

Окно  конструктора (рис.5.5.4-7) разделено на две части. В верхней части находятся окна таблиц со списками полей. Имя каждой таблицы отображается в строке заголовка такого окна.

 

  Рис 5.5.4-7. Окно конструктора запроса. Пример ввода условия

 Например, на рис.5.5.4-7 представлены таблицы СТУДЕНТЫ и СТУДЕНТЫ И ЗАНЯТИЯ.

Нижняя часть является  бланком запроса, или, как его называют,   QBE – областью (Query by Example – запрос по образцу). Здесь указываются параметры запроса и данные, которые нужно отобрать, а также определяется способ их отображения на экране.

Для перемещения из верхней панели окна в нижнюю панель и обратно  используется клавиша  F6.

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

Еще один способ – двойной щелчок по имени поля.

Например, на рис.5.5.4-7  в бланк запроса включены поля Фамилия, Имя и Балл из таблицы СТУДЕНТ.

Обратите внимание, если был установлен флажок  Имена таблиц  из меню Вид, то во второй строке бланка QBE выйдет на экран имя таблицы,  из которой выбрано поле (рис.5.5.4-7). В строке Вывод на экран  флажком помечаются  те поля, которые должны быть выведены на экран.

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

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

Удалить поле из бланка запроса можно клавишей <Del> или через  меню Правка командой  Удалить столбцы. Чтобы удалить таблицу, следует маркировать ее в верхней части окна конструктора запроса, выполнив щелчок по имени, и нажать <Del> или в меню Правка командой  Удалить.

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

Чтобы найти записи с  конкретным  значением, в каком либо  поле, нужно ввести это   значение в данное поле  в строке бланка QBE Условие отбора (рис.5.5.4-7).

Критерии, устанавливаемые в QBE – области, должны быть  заключены  в кавычки. Если Access идентифицирует введенные символы как критерии отбора, то заключает их в кавычки автоматически, а если нет, то сообщает о синтаксической ошибке.

Например, как показано на рис.3, построен запрос, по которому  из данных по баллам будут выбраны фамилии и имена  студентов с оценками  только 4 и 5.

 

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

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

  1.  Ввести все условия в одну ячейку строки Условие отбора, соединив их логическим оператором или (or). В этом случае будут выбраны данные, удовлетворяющие хотя бы одному из условий (Например, запись   4 or 5 будет соответствовать тому, что будут выбраны фамилии с оценками 4 или 5). 
  2.  Ввести второе условие в отдельную ячейку строки  ИЛИ. И если используется несколько строк  ИЛИ, то чтобы запись была выбрана, достаточно выполнения условий, хотя бы  в одной из строк  ИЛИ,  как, например,  показано на рис. 5.5.4-8.

 

Рис 5.5.4-8. Пример записи условия с использованием оператора или (or)

При такой записи условия также будут выбраны фамилии с оценками 4 или 5.

Логическая операция И (and)  используется в том случае, когда должны быть выполнены оба условия и только в этом случае запись будет выбрана.

Например, записав условие    >2 and <5  будут выбраны только оценки 3 и 4.

Чтобы объединить несколько условий отбора оператором И (and), следует привести их в одной строке.

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

 

      Рис 5.5.4-9. Пример записи условия с использованием оператора и (and )

Исключить группу данных из состава анализируемых запросом записей   позволяет следующий критерий      < > 4.

В этом случае можно не использовать кавычки.

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

Оператор Between  позволяет задать диапазон значений, например:

between 10 and 20.

Оператор In позволяет задавать используемый для сравнения  список значений, например:

              

in (“первый”,”второй”,”третий”).

Оператор Like полезен для поиска образцов в текстовых полях, причем можно использовать шаблоны:

* — обозначает любое количество ( включая нулевой) символов;

?  — любой одиночный символ;

# — указывает, что в данной позиции должна быть цифра.

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

    

like ‘П*ов’.

Обратите внимание, что текстовые запросы должны быть заключены в одинарные кавычки (апостроф).

Можно ввести дату и время,  при этом значения должны быть заключены между символами #.  Например: 

 #10 мая 1998# 

 >#31.12.96#.

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

  Day(дата)  – возвращает значение дня месяца в диапазоне от 1 до 31;

  Month(дата)  – возвращает значение месяца года в диапазоне от 1 до 12;

  Year(дата)  – возвращает значение года в диапазоне от 100 до 9999.

Данные можно упорядочить  по возрастанию или убыванию.

Для упорядочения отобранных записей целесообразно воспользоваться возможностями сортировки самого запроса в строке Сортировка окна конструктора запроса (рис. 5.5.4-7).

Вычисляемые поля. Можно задать вычисления над любыми полями таблицы и сделать вычисляемое значение новым полем в запросе.

Для этого в строке Поле  бланка QBE вводится формула для вычисления, причем имена полей заключаются в  квадратные скобки.

Например:                           

=[ Оклад]*0.15.

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

  1.  арифметические: * умножение; + сложение;   - вычитание; / деление; ^ возведение в степень;
  2.  соединение частей текста при помощи знака &, например:

=[ Фамилия] & “ “&[Имя]

В кавычки заключен пробел для того, чтобы запись не была слитной.

Итоговые запросы. Итоговые запросы значительно отличаются от обычных. В них поля делятся на 2 типа: 

  1.  поля, по которым осуществляется группировка  данных;
  2.  поля, для которых проводятся вычисления.

Для составления итогового запроса, находясь в режиме конструктора, следует нажать кнопку Групповые операции  на панели инструментов  или воспользоваться командой Групповые операции из меню Вид.

В результате чего в бланке запроса появится строка Групповая операция. Если  для соответствующего поля из списка выбрать функцию Группировка (рис 5.5.4-10), то при выполнении запроса записи по этому полю группируются по значениям в этом поле, но итог не подводится.

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

 

   

        Рис. 5.5.4- 10. Строка Групповая операция в бланке QBE

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

Основные  групповые функции, которыми можно воспользоваться:

SUМ - вычисляет сумму всех значений заданного поля (для числовых или денежных полей), отобранных запросом;

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

MIN - выбирает минимальное значение в записях определенного поля, отобранных запросом;

MAX – выбирает максимальное значение в записях определенного поля, отобранных запросом;

COUNT – вычисляет количество записей, отобранных запросом в определенном поле, в  которых значения данного поля отличны от нуля;

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

LAST - определяет последнее значение в указанном поле записей.

Выполнение запроса. Готовый запрос выполняется после щелчка по кнопке панели инструментов в режиме Конструктора запросов или при активизации команды Запуск из меню Запрос. В результате будет получена таблица с ответом на заданные условия. Например, на рис 5.5.4-11 показан результат запроса, построенного на
рис. 5.3.4-7. 

 

              

Рис.5.5.4-11. Результат выполненного запроса

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

На рис. 5.5.4-7 представлены две таблицы СТУДЕНТЫ и СТУДЕНТЫ И ЗАНЯТИЯ, где показана связь «один-ко-многим».

Построения запросов на языке SQL. В реляционных БД существуют три основные операции извлечения данных: выборка, проекция и слияние. Выборка извлекает поднабор строк, проекция извлекает поднабор столбцов, слияние объединяет данные из двух таблиц (для объединения данных из более  двух таблиц используется больше одной операции слияния).

SQL выполняет все эти операции через одно выражение SELECT:

SELECT [список столбцов]

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

Необходимыми элементами являются слова SELECT и FROM. Операция проекции осуществляется путем указания списка столбцов.

Запишем выражение SELECT, предназначенное для извлечения перечня книг с именами авторов и соответствующим количеством экземпляров:

SELECT автор, количество

  FROM книги;

Для  отображения всех столбцов и строк необходимо записать

SELECT *

  FROM книги;

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

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

SELECT автор, название

  FROM книги

  ORDER BY  название ;

Если вы хотите, чтобы строки располагались в порядке убывания, необходимо добавить слово DESC:

SELECT автор, название

  FROM книги

  ORDER BY  название DESC ;

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

SELECT автор, название

  FROM книги

  WHERE количество > 10;

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

SELECT автор, название

  FROM книги

  WHERE автор=’Пушкин’;

Здесь выбираются книги А.С.Пушкина. Текстовые значения необходимо помещать в одинарные кавычки.

Слово Like позволяет выбирать строки, в которых совпадают некоторые символы:

SELECT автор, название

  FROM книги

  WHERE автор Like ‘П*’;

Здесь выбираются книги, у которых имена авторов начинаются на букву П. Знак ‘*’ используется как «маска», обозначающая любую комбинацию символов:

SELECT автор, название

  FROM книги

  WHERE автор Like ‘П*’ and количество <3;

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

В слове WHERE можно использовать те же виды критериев, которые были описаны выше.

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

SELECT автор, Sum(количество) AS количество

FROM книги

GROUP BY автор;

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

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

 

SELECT автор, MAX(количество) AS максимум

FROM Книга

GROUP BY автор

HAVING MAX(количество)>=5;

Слово HAVING должно быть после слова GROUP BY. Слово HAVING ограничивает результаты на основе встроенной функции MAX, а слово WHERE – на значениях отдельной строки. Запомните, что почти всегда, когда вы включаете в запрос слово HAVING, вам нужно использовать слово GROUP BY.

Рис. 5.5.4-12

Из результата (рис. 5.5.4-12) видно, что строки соответствуют группам.

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

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

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

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

ON Читатели.КодЧитателя = Выдача.КодЧитателя

Выдаются только те строки (записи), у которых в таблицах ЧИТАТЕЛИ и ВЫДАЧА совпадают коды читателей.

Типы связывания могут быть разнообразными. Приведем только некоторые из них.

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

Если в типе связывания указывается LEFT JOIN, то в результат включаются все строки из левой таблицы,  даже если для записей из правой таблицы отсутствуют совпадающие значения.

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

 

Пример 5.5.4-1. Построить запросы для БД  БИБЛИОТЕКА.

1.Выбрать книги, которые не выданы читателям.

Запрос, построенный средствами QBE, представлен на рис. 5.3.4-13.

Рис. 5.5.4-13

Запрос, сконструированный средствами Access на языке SQL, представлен на
рис. 5.5.4-14. Здесь и далее приводится вид запроса на
SQL в формате, принятом в Access.

SELECT Книга.КодКниги, Книга.Автор, Книга.Название, Книга.Количество

FROM Книга LEFT JOIN Выдача ON Книга.КодКниги = Выдача.КодКниги

WHERE (((Выдача.КодКниги) Is Null));

Рис. 5.5.4-14

Результат запроса представлен на рис 5.5.4-15.

Рис. 5.5.4-15

2.Выбрать читателей, которые имеют задолженность более месяца

Запрос, построенный средствами QBE, представлен на  рис. 5.5.4-16.

Рис. 5.5.4-16

Запрос, построенный средствами SQL, представлен на рис. 5.5.4-17.  В формате SQL Access 2003 в выражениях используются построения, принятые в построителе выражений Access.

SELECT Читатель.КодЧитателя, Читатель.Имя, Выдача.ДатаСдачи, Книга.КодКниги, Книга.Автор, Книга.Название, Date()-Выдача!ДатаСдачи AS НаСкПросрочено

FROM Читатель INNER JOIN (Книга INNER JOIN Выдача ON Книга.КодКниги = Выдача.КодКниги) ON Читатель.КодЧитателя = Выдача.КодЧитателя

WHERE (((Date()-[Выдача]![ДатаСдачи])>30));

Рис. 5.5.4-17

Результат запроса представлен на рис. 5.5.4-18.

Рис. 5.5.4-18

 


5.5.5. Задачи для самостоятельного решения по теме  
«Основы  проектирования баз данных средствами
СУБД  Access»

Номер задачи

Структура таблицы БД и запрос

1

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

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

2

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

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

3

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

Выбрать фамилии самых молодых студентов, имеющих суммарно наименьшее число пропусков.

4

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

Выбрать даты, в которые пропуски по математике превышали пропуски по физкультуре с указанием фамилии студентов и их групп.

5

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

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

6

Магазин: название магазина, название товара, стоимость товара, дата продажи.

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

7

Сотрудники: табельный номер, должность, год рождения, оклад, фамилия.

Выбрать самых молодых  сотрудников с максимальным окладом.

8

Товар: наименование товара, количество на складе, дата поступления, изготовитель.

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

9

Сотрудники: табельный номер, должность, оклад, фамилия.

Выбрать сотрудников заданной должности с максимальным окладом.

10

Аэропорт: марка самолета, количество мест, средняя стоимость билета, количество проданных билетов.

Выбрать марки самолетов, для которых количество проданных билетов меньше половины всех мест самолета.

11

Магазин: дата покупки, фамилия покупателя, название товара, цена.

Выбрать покупателей, купивших самый дешевый товар.

12

Детали: код детали, название, норма расхода материала на деталь, сорт.

Выбрать детали первого и второго сорта.

13

Предприятие: название предприятия, количество сотрудников предприятия, профессии.

Выбрать предприятия, имеющие заданные профессии.

14

Сотрудники: табельный номер, должность, оклад, фамилия.

Выбрать сотрудников, средний заработок которых больше заданного.

15

Сотрудники: табельный номер, профессия, оклад, фамилия.

Выбрать сотрудников, сгруппировав их по профессиям. Фамилии для каждой профессии должны быть расположены по алфавиту.

16

Автомобили: модель авто, стоимость, цвет кузова, наличие или отсутствие автоматической коробки передач.

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

17

Автомобили: модель, стоимость. год выпуска, страна сборки.

Выбрать автомобили, упорядочив их по году выпуска.

18

Изделия: код изделия, название предприятия, план выпуска, фактический выпуск. Вывести предприятия, сгруппировав их по наименованиям изделий. Вывести предприятия перевыполнившие план.

19

Изделия: код изделия, наименование изделия, название предприятия, план выпуска, фактический выпуск.

Вывести изделия, напечатав их по названиям предприятиям, фактический выпуск которых превосходит среднее значение по всем предприятиям.

20

Предприятия: название, количество сотрудников, профессии. Вывести предприятия, количество сотрудников которых находится в заданных пределах.

21

Предприятия: название, количество сотрудников, объем выпускаемой продукции, профессии.

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

22

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

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

23

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

Выбрать станции, сгруппировав их по типам антенн.

24

Студенты: год рождения, адрес, дата зачисления, номер приказа, факультет, группа.

Выбрать самых молодых студентов для каждого факультета.

25

Преподаватели: год рождения, адрес, телефон, кафедра, должность, учёная степень.

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

26

Книги издательства: авторы, название, раздел (техническая, общественно-политическая и т.п.), год издания, количество страниц, цена.

Выбрать все книги, цена которых выше средней цены для всех книг данного издательства.

27

Читатели: номер читательского билета, ФИО, год рождения, адрес, дата записи, вид читателя (студент, аспирант, преподаватель, сотрудник) названия взятых книг и даты их выдачи.

Определить общее количество книг по каждому виду читателя.

28

Продажи: наименования товаров, поставка из магазина или со склада, количество и стоимость товара. Определить самый дорогой товар из магазина и самый дешевый со склада.

29

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

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

30

Проекты: название, дата начала, дата окончания, размер финансирования, тип финансирования (периодический, разовый), исполнитель.

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


5.3.6. Тестовые задания по теме  
«Основы  проектирования баз данных средствами системы управления базами данных  ACCESS»

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

  1.  Пусть имеется отношение «Успеваемость» со следующей схемой:

Успеваемость (ФИО_студента, Дисциплина, Оценка, Дата, Преподаватель)

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

Select unique ФИО_студента from Успеваемость

будет

  1.  все отношение «Успеваемость»
  2.  ФИО, Дисциплина, Оценка, Дата, Преподаватель тех студентов, которые не имеют однофамильцев
  3.  все отношение «Успеваемость», отсортированное по полю ФИО_студента

  1.  Отличительной чертой реляционной базы данных является
  2.  подчиненность объектов нижнего уровня объектам верхнего уровня
  3.  то, что отношения между объектами определяются как «многие ко многим»
  4.  то, что каждая запись в таблице содержит информацию, относящуюся только к одному конкретному объекту
  5.  возможность поиска данных по ключу

  1.  Чтобы изменить структуру формы в СУБД Access, нужно открыть форму в режиме
  2.  таблицы
  3.  конструктора
  4.  формы
  5.  предварительного просмотра

  1.  Служебные слова order <атрибут> asc определяют в SQL
  2.  сортировку результата выборки в порядке возрастания
  3.  сортировку результата выборки в порядке убывания
  4.  сортировку данных по значениям
  5.  подсчет количества записей в таблице

  1.  К основным функциям СУБД не относится
  2.  определение данных
  3.  хранение данных
  4.  обработка данных
  5.  управление данными


  1.  В СУБД Access допустимы типы полей записей
  2.  числовой, символьный, графический, музыкальный
  3.  логический, дата, числовой, денежный, OLE
  4.  числовой, текстовый, гипертекстовый, логический
  5.  числовой, символьный, Memo, дата, логический массив

  1.  Команда выборки в SQL может содержать слова
  2.  select, from, where
  3.  order by, group by
  4.  having set, minus
  5.  все перечисленные в 1-3

  1.  Существует две концепции обработки данных: файл-сервер и
  2.  показатель-сервер
  3.  объект-сервер
  4.  запрос-сервер
  5.  клиент-сервер

  1.  Объект «Таблица» необходим для
  2.  выборки нужных данных
  3.  хранения данных по объекту
  4.  формирования выходного документа
  5.  ввода, просмотра, корректировки взаимосвязанных данных базы

  1.  Оператор, позволяющий указать имена исходных таблиц, участвующих в формировании выборки, это
  2.  WHERE
  3.  FROM
  4.  SELECT
  5.  LIKE

  1.  Назовите одну из существующих моделей данных
  2.  Иерархическая
  3.  Ступенчатая
  4.  Классическая
  5.  Регистрационная

  1.  В Access используются 8 типов данных: текстовый, Memo, числовой, дата/время, логический, поле объекта OLE и
  2.  Счетчик
  3.  Поле объекта SQL
  4.  Процентный
  5.  Корректирующий

  1.  Один из возможных вариантов объединения таблиц:
  2.  Сверху
  3.  Снизу
  4.  Слева
  5.  Вокруг

  1.  База данных – это
  2.  совокупность экземпляров записи одной структуры
  3.  комплекс программных и языковых средств
  4.  совокупность структурных данных, относящихся к определенной предметной области
  5.  совокупность правил классификации данных

  1.  Для каждого поля задаются и зависят от выбранного типа данных
  2.  Формы
  3.  Признаки
  4.  Свойства
  5.  Модели

  1.  Удаление определенной записи в таблице
  2.  DELETE … FROM
  3.  DELETE … COLUMN … FROM
  4.  DELETE … FROM … WHERE
  5.  DELETE … DROP … WHERE

  1.  Элементарная единица логической организации данных, которая соответствует реквизиту:
  2.  Поле
  3.  Запись
  4.  Таблица
  5.  Ключ

  1.  2. Укажите одно из важных свойств поля:
  2.  Код поля
  3.  Показатель поля
  4.  Структура поля
  5.  Подпись поля

  1.  3. Оператор, выполняющий проверку на диапазон значений:
  2.  FROM … TO
  3.  BETWEEN … AND
  4.  FROM … TO
  5.  BETWEEN … TO

  1.  1. Функция первичного ключа:
  2.  Выполняет роль группировочного признака
  3.  Создает выборку нужных данных
  4.  Формирует выходной документ
  5.  Идентифицирует экземпляр записи

  1.  Условие на значения – это:
  2.  Текст сообщения, выводимый на экран при нарушении условия
  3.  Текст, который выводится в заголовках
  4.  Текст, ограничивающий вводимые значения и позволяющий осуществить контроль ввода
  5.  Максимальный размер данных, сохраняемых в поле


  1.  Оператор IN выполняет:
  2.  Проверку выражения на NULL
  3.  Проверку выражения на совпадения с любым из элементов списка
  4.  Сравнение строковых значений
  5.  Логическую импликацию выражений

  1.  К основным элементам иерархической структуры относится:
  2.  Точка
  3.  Уровень
  4.  Путь
  5.  Связка

  1.  Базу данных в Access можно создать с помощью:
  2.  Конструктора
  3.  Ключа
  4.  Связи объектов
  5.  Формы

  1.  Выбор пяти первых фамилий студентов, упорядоченных по учебным группам:
  2.  SELECT TOP5[Имя],[Фамилия] FROM Студент[Группа]
  3.  SELECT TOP5[Имя],[Фамилия] ORDER BY [Группа] FROM Студент
  4.  SELECT TOP5[Имя],[Фамилия] FROM Студент ORDER BY [Группа]
  5.  SELECT TOP5[Имя],[Фамилия] ORDER BY [Группа] WHERE Студент

  1.  Запись - это
  2.  Совокупность экземпляров одной структуры
  3.  Совокупность структур данных
  4.  Совокупность логически взаимосвязанных полей
  5.  Совокупность материалов по ведению документов

  1.  Имя поля должно быть
  2.  Универсальным
  3.  Простым
  4.  Уникальным
  5.  Понятным

  1.  Оператор, вычисляющий количество выделенных записей в запросе
  2.  Avg
  3.  Var
  4.  Sum
  5.  Count

  1.  Сколько вершин имеет иерархическое дерево
  2.  Одну
  3.  Может иметь много вершин
  4.  Не имеет вершин вообще
  5.  Три

2 Далее в примерах используется Access 2003


 

А также другие работы, которые могут Вас заинтересовать

5039. Проектирование металлорежущих инструментов. Проектирование круглого радиального фасонного резца 618.5 KB
  Проектирование круглого радиального фасонного резца Назначение фасонных резцов Фасонный резец - инструмент, предназначенный главным образом для использования в условиях серийного и массового производств, где все больший удельный вес приобрет...
5040. Измерение длины волны излучения лазера интерференционным методом 138 KB
  Измерение длины волны излучения лазера интерференционным методом Цель работы: ознакомиться с принципами работы лазеров измерить длину волны излучения лазера и сравнить спектры его индуцированного и спонтанного излучений. Приборы и принадлежности: г...
5041. Определение длин волн излучения источников дискретного и непрерывного спектров 187 KB
  Определение длин волн излучения источников дискретного и непрерывного спектров Цель работы: градуировка спектроскопа по известному спектру неона, определение длин волн в спектре паров ртути и границ видимого спектра лампы накаливания. Приборы и прин...
5042. Исследование статических характеристик полупроводниковых диодов 129.5 KB
  Исследование статических характеристик полупроводниковых диодов. Цель работы Изучить устройство полупроводникового диода, физические процессы, происходящие в нем, характеристики, параметры, а также типы и применение полупроводниковых диодов...
5043. Кинематический анализ и синтез плоских рычажных, рычажных, кулачковых и зубчатых механизмов 509.5 KB
  Структурный анализ механизма. Число степеней свободы механизма определяем по формуле П. Л. Чебышева. где n- число подвижных звеньев механизма, p5- число кинематических пар пятого класса, p4- число Кинематических пар четвертого класса. В ...
5044. Изучение структуры углеродистых сталей после различных видов термической обработки 94 KB
  Изучение структуры углеродистых сталей после различных видов термической обработки Цель работы - изучение влияния закалки и отпуска на структуру и свойства углеродистых сталей. Оборудование, оснастка, приборы: электрические камерные печи...
5045. Определение длины световой волны при помощи дифракционной решетки 111.5 KB
  Определение длины световой волны при помощи дифракционной решётки Цель работы: определение с помощью дифракционной решётки длины световых волн в различных частях видимого спектра. Приборы и принадлежности: дифракционная решётка плоская шкала со щел...
5046. Определение эффективного коэффициента ослабления космических лучей 119.5 KB
  Определение эффективного коэффициента ослабления космических лучей Цель работы: определение эффективного коэффициента ослабления космических лучей в свинце. Приборы и принадлежности: установка для измерения интенсивности космических лучей ФПК...
5047. Исследование комбинационных логических схем 185 KB
  Исследование комбинационных логических схем. ЧАСТЬ 1. Исследование базового логического элемента ТТЛ. Построить передаточную характеристику. Реализовать с помощью ЛЭ 2И-НЕ схемы И, ИЛИ, НЕ...