«А. П. ЛАЩЕНКО, Т. В. КИШКУРНО ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ И СУБД ACCESS 2007 Рекомендовано учебно-методическим объединением учреждений высшего образования Республики Беларусь по экономическому образованию в качестве ...»

Учреждение образования

«БЕЛОРУССКИЙ ГОСУДАРСТВЕННЫЙ

ТЕХНОЛОГИЧЕСКИЙ УНИВЕРСИТЕТ»

А. П. ЛАЩЕНКО, Т. В. КИШКУРНО

ПРОЕКТИРОВАНИЕ

БАЗ ДАННЫХ И СУБД

ACCESS 2007

Рекомендовано

учебно-методическим объединением

учреждений высшего образования Республики Беларусь по экономическому образованию в качестве лабораторного практикума для студентов учреждений высшего образования по специальностям 1-25 01 07 «Экономика и управление на предприятии», 1-25 01 08 «Бухгалтерский учет», 1-26 02 02 «Менеджмент», 1-26 02 03 «Маркетинг» по дисциплине «Компьютерные информационные технологии»

Минск УДК 004.65(076.5) ББК 32.973я Л Рецензенты:

кафедра методов оптимального управления БГУ (доктор физико-математических наук, профессор, заведующий кафедрой А. И. Калинин);

кандидат технических наук, доцент, заведующий кафедрой информационных технологий БГЭУ М. Н. Садовская Все права на данное издание защищены. Воспроизведение всей книги или ее части не может быть осуществлено без разрешения учреждения образования «Белорусский государственный технологический университет».

Лащенко, А. П.

Л32 Проектирование баз данных и СУБД Access 2007 :

лабораторный практикум для студентов специальностей 1-25 01 07 «Экономика и управление на предприятии», 1-25 01 08 «Бухгалтерский учет», 1-26 02 02 «Менеджмент», 1-26 02 03 «Маркетинг» по дисциплине «Компьютерные информационные технологии» / А. П. Лащенко, Т. В. Кишкурно. – Минск : БГТУ, 2011. – 120 с.

ISBN 978-985-530-116-6.

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

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

УДК 004.65(076.5) ББК 32.973я ISBN 978-985-530-116-6 © УО «Белорусский государственный технологический университет», © Лащенко А. П., Кишкурно Т. В.,

ВВЕДЕНИЕ

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

Разрабатываются и внедряются автоматизированные информационные системы для обслуживания различных сфер деятельности.

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

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

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

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

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

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

Для управления экономическими объектами разрабатываются и внедряются автоматизированные информационные системы.

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

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

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

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

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

• централизованное хранение информации;

• хранение данных независимо от программы их обработки;

• возможность использования одних и тех же данных для решения различных задач;

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

Тогда же и появилось первое упоминание о БД.

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

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

Такую библиотеку подпрограмм назвали СУБД. Ее основные функции: определение данных (описание структуры БД), их обработка и управление ими. В настоящее время существуют различные СУБД – MS SQL Server, MySQL, Interbase, Oracle, DB2, Paradox, FoxPro и множество других, менее известных.

В большинстве случаев БД используются для создания автоматизированных информационных систем.

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

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

При создании БД или информационных систем можно выделить ряд этапов.

1. Постановка задачи. На этом этапе формулируются цели и задачи создаваемой информационной системы.

2. Анализ предметной области. На этом этапе описываются информационные объекты с указанием их характеристик. В результате строится концептуальная информационная модель предметной области.

3. Нормализация отношений в информационной модели.

На этом этапе анализируются полученные на втором шаге объекты и устраняются некоторые информационные аномалии (нарушения).

В результате получается нормализованная информационная модель предметной области.

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

5. Разработка интерфейса. На этом этапе проектируются экранные формы и отчеты для ввода и представления информации.

6. Разработка дополнительных модулей обработки информации.

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

7. Тестирование и отладка информационной системы. На этом этапе происходит актуализация БД (ее заполнение реальной информацией), отладка дополнительных модулей обработки информации.

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

9. Эксплуатация.

Первые три этапа относятся к так называемому «бумажному»

проектированию, т. е. выполняются без использования компьютера, хотя существуют специальные программные средства (caseтехнологии, не относящиеся к СУБД, например, ERwin или Rational Rose), которые позволяют автоматизировать эти операции.

От того, насколько правильно и тщательно выполнены первые три шага, зависит успех всей разработки рассматриваемой БД.

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

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

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

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

1. Поле – наименьшая единица памяти, обрабатываемая СУБД.

2. Физическая запись – упорядоченная совокупность фиксированного количества полей. Две физические записи однотипны, если совпадают по составу полей.

3. Файл – совокупность однотипных записей.

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

5. Индексный файл – структурированная совокупность записей, на которой реализуется какой-либо метод доступа к данным;

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

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

1. Атрибут (элемент данных) – наименьшая поименованная единица информации с определенным типом, идентифицируемая СУБД. Обычно соответствует полю на физическом уровне.

2. Логическая запись – фиксированная совокупность элементов данных. Две логические записи однотипны, если состоят из одинаковых совокупностей элементов данных.

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

4. Схема БД – совокупность отношений с установленными связями и ограничениями целостности.

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

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

1) не может быть двух сотрудников с одним и тем же табельным номером;

2) не может быть так, чтобы один и тот же инвентарный номер соответствовал различному оборудованию.

Эти поля являются первичными ключами. Запись из отношения «Сотрудники» нельзя удалить, если с ней связана запись из отношения «Рабочее место». То же самое справедливо и для отношения «Оборудование».

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

Сотрудники Оборудование Рабочее место В обязательном порядке должны быть проиндексированы ключевые поля записей:

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

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

• индексные файлы для табельного и инвентарного номеров в третьем файле.

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

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

в одном приложении информация была изменена, а в другом – нет.

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

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

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

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

Сбои бывают двух видов: логические и физические.

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

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

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

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

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

1.3.3. Мобильность прикладного программного обеспечения Определение 1. Прикладной программой в БД зовется программа пользователя, взаимодействующая с БД посредством СУБД.

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

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

В настоящее время используется трехуровневая модель организации БД, предложенная в 1975 г. комитетом по стандартизации ANSI (American National Standards Institute) (рис. 1.3).

Внешний уровень Внутренний уровень Рис. 1.3. Трехуровневая модель организации БД Одна и та же БД имеет различные уровни описания.

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

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

Внутренний уровень поддерживает представление БД в памяти компьютера.

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

1.3.4. Секретность данных Традиционно в БД авторизация доступа выполняется за счет аппарата внешних схем: при входе пользователь вводит имя группы и пароль. В описании схемы присутствуют ограничения на доступ к данным (в виде SQL-команд). Также применяется шифрование на физическом уровне. Например, в СУБД Clarion пароль является ключом шифра.

Информация не существует без материальных носителей.

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

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

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

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

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

1.4.1. Плоские (двойные) файлы Определение 4. Объекты прикладной области однотипны, если характеризуются одинаковым набором атрибутов (имеют одинаковую семантику).

Определение 5. Табличное представление информации называется плоским файлом, если выполнены следующие условия:

1) таблица имеет наименование и заголовок в виде наименований атрибутов;

2) содержимым одной строки таблицы является информация об одном объекте данного класса (и, следовательно, в таблице не может быть совпадающих строк);

3) содержимое одного столбца зовется доменом – областью определения атрибута (содержимое столбца всегда однотипно).

Пример 1:

студенческого билета студента группы Информация о классе «Студент» представлена в виде плоского (двойного) файла.

В этом примере рассмотрен класс материальных объектов, однако атрибуты его нематериальны. Можно для атрибута «Факультет» взять материальное представление: кабинет декана или секретаря, но это будет уже другой класс объектов, и ему соответствуют уже материальные объекты.

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

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

В первом примере любой атрибут является поисковым ключом.

Определение 7. Для выбранного класса объектов атрибут является первичным ключом, если одно его значение служит для идентификации одного объекта данного класса.

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

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

В первом примере сцепленным ключом может быть «ФИО студента» + «Факультет». Для реализации поисковых и первичных ключей на физическом уровне используются индексные файлы.

Ключевые атрибуты (первичный и поисковый) – основа для установления целостности в БД.

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

1.5.1. Иерархическая модель В иерархической модели связи между данными можно представить с помощью корневого дерева. На рис. 1.4 показан пример организации данных по иерархической модели.

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

Организовать более сложные связи в такой модели невозможно.

Например, если исполнителю необходимо участвовать в нескольких проектах, то потребуется создание дополнительной БД.

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

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

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

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

Недостатками сетевой модели являются сложность ее понимания для обычного пользователя и большие объемы памяти компьютера на хранение данных.

1.5.3. Реляционная модель Реляционная модель данных была предложена американским математиком Э. Ф. Коддом, который в 1970 г. впервые сформулировал ее основные понятия. В настоящее время это самая распространенная модель данных.

В основе реляционной модели данных лежит понятие «отношение» (от англ. relation). Отношение отображает некоторый объект, который характеризуется набором атрибутов, a каждый атрибут – набором допустимых значений, называемым доменом.

Создатель реляционной модели Э. Ф. Кодд использовал термин «отношение» как синоним слова «таблица».

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

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

1) каждое значение атрибута, содержащееся на пересечении строки и столбца, должно быть атомарным, т. е. не расчленяться на несколько значений;

2) значения в столбце должны быть однородными;

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

4) каждый столбец имеет уникальное имя;

5) последовательность столбцов в таблице несущественна;

6) последовательность строк в таблице несущественна.

В качестве примера реляционной таблицы приведем таблицу «Клиенты».

В таблице реляционной БД (БД, построенной по реляционной модели) столбцы называют полями, а строки – записями. Одно или несколько полей, значения которых в каждой записи таблицы однозначно ее идентифицируют, называют ключевым полем. В таблице «Клиенты» таковым может быть поле «Код клиента» или поле «Клиент». В реляционной БД между таблицами устанавливаются связи, которые делают их более информативными, чем они являются по отдельности. Связь устанавливается посредством связи ключевых полей, содержащих общую информацию для обеих таблиц.

Пусть таблица R1 связывается с таблицей R2. Тогда таблица R называется основной, а таблица R2 – подчиненной. Ключевое поле основной таблицы называется первичным ключом, а подчиненной – внешним ключом. Одна запись основной таблицы может быть связана с одной или несколькими записями подчиненной таблицы.

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

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

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

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

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

Представителем логического описания информационной модели предметной области является модель «сущность-связь»

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

Определение 9. Элементом данных (атрибутом) называется атомарное (неделимое) данное с определенным типом и наименованием.

Пример. В прикладной области отдела кадров «ФИО сотрудника» не является атрибутом, т. к. нарушена атомарность и требуется вместо него использовать отдельно «Фамилия сотрудника», «Имя сотрудника» и «Отчество сотрудника». Однако в другой прикладной области, например, железнодорожной кассе, такой атрибут допустим, поскольку основным показателем являются паспортные данные.

Примеры неправильных атрибутов.

• «Цех». Этот атрибут не обладает однозначной семантической интерпретацией. Возможно, разработчик имел в виду номер цеха, его наименование, продукцию или что-то еще.

• «Средняя зарплата отдела». При заполнении значения данного атрибута пользователь должен сам подсчитать его значение.

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

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

• «Стаж работы». Тоже изменяемая величина, и атрибут необходимо заменить на пару атрибутов «Стаж работы до приема»

и «Дата приема на работу».

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

Связи всегда направлены и имеют следующие типы:

1. 1 : 1, или связь «один-к-одному», – одному значению первого элемента данных соответствует одно значение второго элемента.

Пример данной связи приведен на рис. 1.6: «Номер читательского билета» – «Номер зачетки».

2. М : 1, или связь «много-к-одному», – множеству значений первого элемента данных соответствует одно значение второго элемента. Пример данной связи приведен на рис. 1.7: «Табельный номер» – «Должность сотрудника».

3. 1 : М, или связь «один-ко-многим», – одному значению первого элемента данных соответствует множество значений второго.

Пример данной связи приведен на рис. 1.8: «Должность сотрудника» – «Табельный номер».

4. М : М, или связь «много-ко-многим», – множеству значений первого элемента данных соответствует множество значений второго элемента данных. Пример данной связи приведен на рис. 1.9:

«Должность сотрудника» – «Оклад сотрудника».

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

ОСНОВЫ ПРОЕКТИРОВАНИЯ

РЕЛЯЦИОННЫХ БАЗ ДАННЫХ

Проще всего представить себе БД в виде таблицы с некоторым фиксированным числом столбцов и достаточно большим неопределенным числом строк. Простейший пример БД – телефонный справочник. Его поля фиксированы: «Фамилия», «Имя», «Отчество», «Номер телефона». Набор записей телефонного справочника не является постоянным. Телефонный справочник может непрерывно пополняться, записи в нем могут изменяться при смене телефонного номера.

Что останется в БД, если удалить из нее все записи? В ней останутся только названия полей, а точнее говоря, структура БД, которую определяет совокупность полей и их свойства.

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

Таким образом, создание БД состоит как бы из двух этапов:

создания ее структуры и наполнения структуры информацией.

И ту, и другую функцию выполняет СУБД.

Проектирование данных БД представляет собой процесс отображения исследуемых явлений реального мира в виде данных в памяти ЭВМ (рис. 2.1).

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

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

При проектировании БД необходимо обеспечить соблюдение комплекса требований:

• целостность БД – требование полноты и непротиворечивости данных;

• многократное использование данных;

• быстрый поиск и получение информации по запросам пользователей;

• простота обновления данных;

• минимизация избыточности данных;

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

Проектирование БД осуществляется в три этапа:

1) концептуальное проектирование;

2) логическое проектирование;

3) физическое проектирование.

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

• определение сущностей и их документирование;

• определение связей между сущностями и их документирование;

• создание модели предметной области;

• определение атрибутов и их документирование;

• определение значений атрибутов и их документирование;

• определение первичных ключей для сущностей и их документирование.

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

• выбор модели данных;

• определение набора таблиц и их документирование;

• нормализация таблиц;

• определение требований поддержки целостности данных и их документирование.

Цель этапа физического проектирования – описание конкретной реализации БД, размещаемой во внешней памяти компьютера.

Для ее достижения выполняются следующие процедуры:

• проектирование таблиц БД средствами выбранной СУБД;

• проектирование физической организации БД;

• разработка стратегии защиты БД.

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

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

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

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

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

Класс сущностей описывается перечнем свойств сущностей, составляющих этот класс.

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

Пример класса сущностей СТУДЕНТ и конкретного экземпляра сущности показан на рис. 2.2.

Класс сущностей СТУДЕНТ Экземпляр сущности Рис. 2.2. Класс сущностей и экземпляр сущности Взаимоотношения сущностей выражаются связями. Различают классы связей и экземпляры связей. Классы связей – это взаимоотношения между классами сущностей, а экземпляры связи – взаимоотношения между экземплярами сущностей.

Класс связей может затрагивать несколько классов сущностей. Число классов сущностей, участвующих в связи, называется степенью связи (n = 2, 3,...). Так, например, класс сущностей СТУДЕНТ связан с классом сущностей ЭКЗАМЕН связью «сдает».

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

В качестве примера связи третьей степени можно указать связь «родители» между тремя классами сущностей МАТЬ, ОТЕЦ, РЕБЕНОК.

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

Атрибут – поименованное свойство (характеристика) сущности. Атрибут представляет собой информационное отображение свойства сущности. Атрибут объекта принимает конкретное значение из множества допустимых значений. Так, например, для сущности СТУДЕНТ атрибут «Фамилия» у конкретного экземпляра сущности принимает конкретное значение «Иванов».

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

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

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

Так, рассмотрим класс сущностей ФАКУЛЬТЕТ, представленный совокупностью атрибутов («Название», «Номер») и класс сущностей РАСПИСАНИЕ ЭКЗАМЕНОВ НА ФАКУЛЬТЕТ, представленный совокупностью атрибутов («Название экзамена 1», «Дата экзамена 1», «Название экзамена 2», «Дата экзамена 2», «Название экзамена 3», «Дата экзамена 3»). Для представления связи «экзамены» (тип связи 1 : 1) в совокупность атрибутов РАСПИСАНИЕ ЭКЗАМЕНОВ НА ФАКУЛЬТЕТ можно включить атрибут «Название факультета».

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

Пример. В таблице «Поставки товаров» есть сведения о товарах, их цене, количестве и стоимости, а также о поставщиках, их адресах и расчетных счетах:

Стол 12 000 100 1 200 000 Пинскдрев 226000, Брестская Стул 6 000 800 4 800 000 Орбита 220111, Минская Кресло 20 000 200 4 000 000 Столиндрев 226100, Брестская Диван 30 000 80 2 400 000 Пинскдрев 226000, Брестская Анализируя структуру таблицы, необходимо, прежде всего, отметить, что в ней имеется повторяющаяся информация о поставщике. Кроме того, стоимость товара является избыточной информацией, т. к. всегда может быть получена на основе цены товара и его количества. Далее, атрибуты «Адрес» и «Счет»

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

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

Процесс создания оптимальной схемы отношений для реляционной БД строго формализован и называется нормализацией БД.

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

Цели нормализации следующие:

• исключить дублирование информации;

• исключить избыточность информации;

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

• упростить и ускорить поиск информации в БД.

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

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

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

Товар Цена Количество Поставщик Индекс Область Город Счет Стол 12 000 100 Пинскдрев 226000 Брестская Пинск Кресло 20 000 200 Столиндрев 226100 Брестская Столин Диван 30 000 80 Пинскдрев 226000 Брестская Пинск Чтобы таблица соответствовала второй нормальной форме, необходимо, чтобы она уже находилась в первой нормальной форме и все неключевые поля полностью зависели от ключевого.

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

непосредственно связано с поставляемым товаром (полем «Товар»), а поля «Индекс», «Область», «Город» и «Счет» характеризуют только самого поставщика. Поэтому, удовлетворяя второму правилу нормализации, необходимо разбить (или разложить) исходную таблицу на две – соответственно «Товары» и «Поставщики».

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

Заметим, что на роль ключевого поля таблицы «Поставщики»

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

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

Анализируя таблицу «Поставщики», можно заметить, что поля «Область» и «Город» являются зависимыми от поля «Индекс», и поэтому эта таблица не находится в третьей нормальной форме.

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

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

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

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

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

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

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

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

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

Составной: «Фамилия» + «Имя» + «Отчество»? Но полных однофамильцев не так уж мало, кроме того, люди иногда меняют фамилии. Номер паспорта? Проведенная не так давно смена паспортов показала непостоянность этого параметра. К тому же они иногда теряются – люди получают новые. Взять за основу уникальный табельный номер работника (ведь для того его и придумали)?

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

Руководство отдела кадров решило, что теперь табельный номер должен быть не числовой, а текстовый и нести в себе некую дополнительную информацию, что-нибудь вроде 12345/45-КАУ-07Б.

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

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

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

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

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

Рассмотрим особенности такого решения на задаче хранения адресов работников предприятия. Требуется разработать структуру для хранения атрибутов: «Улица», «Номер дома», «Квартира».

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

Первая проблема – как занести информацию о доме №10/2?

А о доме №10/2-Б? В номере квартиры, как в числовом типе, тоже нельзя быть уверенным. Например, встречается нумерация с использованием этажа – «5-15». Допустим, на этаже 50 квартир, 551-й квартиры в таком доме нет, а дальше идет сразу 601-я на 6-м этаже. Поэтому, хотя многие и привыкли считать, что номер квартиры – число, его нельзя хранить в числовом поле.

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

«Номера дома и квартиры», соответственно поправив размерность.

Теперь обратим внимание на поле «Название улицы» – как многие уже вспомнили, кроме улиц бывают еще переулки, площади, проспекты. И чтобы отличить переулок Лазо от улицы Лазо, придется заносить также информацию о типе улицы. Куда? В поле, которое предназначено для хранения названия улицы.

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

4. Максимально контролируйте правильность вводимых данных. Людям свойственно делать ошибки. Компьютер оперирует точными значениями. Поэтому две строки «Иванов» и « Иванов»

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

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

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

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

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

хранятся название и цена, а в «Продажах» – дата и количество.

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

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

Цена-то теперь указана другая. Месяц назад в отчете о продажах за месяц получилась одна сумма, сегодня в отчете за тот же месяц – другая. Очевидное решение – хранить цену в таблице о продажах.

Поскольку там есть поле «Дата продажи», то цена будет указана индивидуально для каждой даты. Можно предложить более сложный вариант – вынести цену в отдельный справочник, добавив туда поле «Дата начала действия цены».

Это позволит менять цену с точностью до минуты. Минуту назад торговали по одной цене, сейчас – по другой. Случай кажется довольно нестандартным, пока мы не вспомним о бирже и скорости изменения котировок акций. Заметьте, что из таблицы продаж исчезло поле «Код названия». Все правильно, оно теперь доступно через таблицу «Справочник цен на товары». Мы всегда можем по строке из таблицы «Продажи» узнать название проданного товара, составив соответствующий запрос к трем таблицам.

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

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

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

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

Программа Microsoft (MS) Access 2007 является преемником MS Access 2003, а также членом дружного семейства MS Office 2007. Ряд новых возможностей связан с родовыми чертами MS Office 2007, остальные же свойства связаны с диалектическим законом перехода количества в качество, проявившимся в процессе долгой эволюции от MS Access 2003 к MS Access 2007.

Прежде чем устанавливать MS Office 2007, выберите выпуск программного пакета, в состав которого входит MS Access 2007, из следующего списка:

• MS Office Professional 2007;

• MS Office Professional Plus 2007;

• MS Office Enterprise 2007.

MS Access 2007 является частью MS Office System, так что базовые объекты интерфейса – меню, панели инструментов, диалоговые окна – работают точно так же, как в других продуктах Office 2007 или других приложениях для MS Windows.

3.1. Новые функциональные возможности В программе Access 2007 используется новый интерфейс пользователя, который обеспечивает высокую эффективность и производительность работы. Главным элементом интерфейса является особая панель – Лента инструментов, заменившая все меню и панели инструментов, которые были в предыдущих версиях программ Office.

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

• основные (Главная, Создание, Внешние данные, Работа с базами данных), на которых собраны команды и инструменты, доступные в любой момент;

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

Начинается работа в программе Access 2007 со страницы Приступая к работе с Microsoft Office Access.

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

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

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

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

Это позволяет легко видеть все объекты, с которыми вы работаете, и не терять из виду те, которые закрыты другими. Однако остается возможность вернуться к старому, оконному способу отображения объектов.

Новые форматы БД. Формат БД программы Access 2007 –.accdb (и производные форматы.accde,.accdt,.accdc и.accdr) – обеспечивает поддержку новых возможностей программы. При этом старые файлы (.mdb и др.), как и их упраздненные возможности, продолжают полноценно работать в новой версии программы. Более того, в Access 2007 вы можете создавать БД и проекты Access в старых форматах. Работа с БД в формате.accdb в более ранних версиях программы не обеспечивается.

Новый режим представления форм и отчетов – режим Макета. Он позволяет редактировать структуру формы или отчета вместе с данными. Здесь легко добавлять элементы, выравнивать и форматировать их, применяя специально предназначенные для этого средства. Элементы управления можно объединять в макеты двух типов: в столбик или ленточный. Средства редактирования применяются сразу ко всему макету.

Тематические шаблоны нового типа. Новые шаблоны БД доступны непосредственно со страницы Приступая к работе с Microsoft Office Access. Они позволяют быстро создавать структурно законченные тематические БД формата Access 2007. Каждая БД включает в себя таблицы, формы, отчеты, запросы, макросы и межтабличные связи, но не заполнена никакими данными.

Некоторое количество шаблонов входит в установку программы Access 2007.

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

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

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

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

Готовые шаблоны полей. Пользователи могут не утруждать себя созданием полей и заданием их свойств – в программе Access 2007 есть панель Шаблоны полей, в которой имеется 70 готовых полей в 7 различных категориях. Выберите поле в панели и перетащите его в таблицу, которую вы создаете. Такой способ гарантирует единообразие полей. Поэтому его удобно принимать для обязательного применения при совместной деятельности в составе рабочей группы.

Разделенная форма. В Access 2007 добавлен новый тип форм, который создается автоматически – разделенная форма.

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

Новый тип данных «Вложение». В Access 2007 добавлен новый тип данных – «Вложение». В полях такого типа информация хранится в двоичной кодировке и сжатом виде. Таким образом в БД удобно сохранять изображения, графику, файлы программ Office или любые другие типы файлов. В одной записи может быть несколько полей с этим типом данных. В каждом поле могут храниться несколько файлов. Из своей ячейки файл легко открывается и обрабатывается в Windows в той программе, в которой он был создан (или к которой он приписан). В такие поля удобно, например, сбрасывать промежуточные файлы при совместной работе над проектом.

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

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

Каждый объект Access 2007 имеет два или более способов представления. Например, вы просматриваете данные из таблицы в режиме Таблицы и определяете, как эти данные отображаются в режиме Конструктора.

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

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

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

Страницы доступа к данным, макросы и модули значительно расширяют возможности Access.

Страницы доступа к данным позволяют просматривать и манипулировать информацией из вашей БД через Интранет или Интернет.

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

Модули – это программы на Microsoft Visual Basic for Applications (VBA). В то время как макросы могут автоматизировать многие действия, VBA может использоваться для выполнения задач, которые слишком сложны для выполнения с помощью макросов.

3.3. Физическая структура данных Для создания физической структуры данных следует указать тип данных для каждого поля таблицы. Различные СУБД могут хранить и обрабатывать разные типы данных. В Access 2007 используются типы данных, представленные в табл. 3.1.

Текстовый Алфавитно-цифровые знаки. Использу- До 255 знаков Поле МЕМО Алфавитно-цифровые знаки или форма- До 1 Гб знаков или тированный текст. Используется для 2 Гб памяти Числовой Используется для хранения числовых 1, 2, 4 или 8 байт данных, используемых в вычислениях, (или 16 байт, когда за исключением денежных значений поле используется Дата/время Используется для хранения значений 8 байт Денежный Используется для хранения денежных 8 байт Счетчик Уникальное числовое значение, которое 4 или 16 байт, если при добавлении новой записи автомати- используется для Логический Используется для полей, которые могут 1 бит (8 бит = содержать одно из двух значений, напри- = 1 байт) Поле объекта Используется для хранения OLE-объек- До 1 Гб OLE тов других программ приложений MS Вложение Рисунки, изображения, двоичные фай- Для сжатых вложелы, файлы MS Office. Стандартный тип ний – 2 Гб. Для неданных для сохранения цифровых изо- сжатых – примербражений но 700 Кб Гиперссылка Используется для хранения гиперссылок До 1 Гб знаков или вызова веб-страниц и на объекты Access 2 Гб памяти Мастер под- Используется для запуска мастера под- На основе таблицы становок становок, с помощью которого можно или запроса – разсоздать поле, позволяющее выбрать мер привязанного значение из другой таблицы, запроса столбца. На основе или списка значений, используя поле значения – размер При вводе типа данных одновременно можно задать и свойства поля (табл. 3.2), не совпадающие со значением, присваиваемым Access 2007 по умолчанию. С помощью значений свойств полей можно управлять отображением данных, предотвращать ввод неверных значений, задавать значения по умолчанию, ускорять поиск и сортировку, а также управлять другими функциональными характеристиками и внешним видом полей.

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

Индексированное поле Ускорение доступа к данным в этом поле путем Концептуальное проектирование Цель работы: приобрести навыки анализа предметной области;

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

Рассмотрим задачу о зачислении абитуриентов на бюджетные места в некоторый вуз. Абитуриенты сдают экзамены на один или несколько факультетов вуза. Известно расписание экзаменов:

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

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

Рассмотрим несколько вариантов модели.

Вариант 1.

Представим всю информацию как характеристики одного объекта экзаменационной оценки:

Видно, что информация об абитуриенте дублируется, т. к. при внесении информации о новой оценке мы должны заново вносить уже введенную ранее информацию по абитуриенту (фамилию, имя, отчество, номер аттестата, дату выдачи аттестата). При вводе одной и той же информации можно допустить ошибки, а значит БД перейдет в противоречивое состояние. Предположим, что Сергеев Сергей Петрович сдал экзамен по математике на факультет ВМК на оценку 5. Мы внесли информацию об этом (математика, 22 июля 2003 года, ВМК, Сергеев, Сергей, Петрович, 123123, 21 июня 2003 года, 5) в нашу БД. Через некоторое время данный абитуриент сдает информатику. Мы вносим информацию (информатика, 25 июля 2003 года, ВМК, Сергеев, Сергей, Петрович, 123123, 22 июня 2003 года, 5). При вводе была допущена ошибка – мы неправильно ввели дату выдачи аттестата. Таким образом, наша БД дает противоречивую информацию: по одним данным аттестат был получен Сергеевым 21 июня, по другим – 22 июня.

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

Вариант 2.

Представим эту информацию как характеристики двух объектов – абитуриента и оценки:

Между двумя сущностями должна существовать связь (абитуриент получает оценки).

АБИТУРИЕНТ ОЦЕНКА.

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

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

Вариант 3.

Между тремя сущностями существует две связи:

АБИТУРИЕНТ ОЦЕНКА;

АБИТУРИЕНТ ЭКЗАМЕН.

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

Реляционная модель будет представлена следующими отношениями:

Экзамены (Код экзамена, Предмет, Факультет, Дата).

Абитуриенты (Код абитуриента, Фамилия, Имя, Отчество, Номер аттестата, Дата выдачи аттестата).

Оценки (Код экзамена, Код абитуриента, Значение оценки).

Поля Код экзамена и Код абитуриента в таблице Оценки являются полями для реализации связи с соответствующими таблицами.

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

Интерфейс СУБД Access Цель работы: научиться запускать и работать с основными объектами СУБД Access 2007; ознакомиться со средой.

Система MS Access 2007 входит в профессиональный программный комплекс MS Office и представляет собой мощное средство для работы с БД.

В программе Access 2007 можно создавать БД следующих версий:

• Access 2000 (формат файла *.mdb);

• Access 2002–2003 (формат файла *.mdb);

• Access 2007 (формат файла *.accdb).

Открывать и преобразовывать можно БД, созданные и в более ранних версиях.

Файлы БД отличаются от файлов других приложений MS Office. К ним предъявляются особые требования по безопасности.

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

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

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

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

При запуске MS Access 2007 на экране появится стартовое окно Приступая к работе с Microsoft Office Access, показанное на рис. 3.1.

С помощью данного окна можно выбрать, в каком ключе вы будете работать:

• создавать новую БД «с нуля»;

• открыть уже созданную ранее БД;

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

Рис. 3.1. Окно Приступая к работе с Microsoft Office Access Для создания новой БД нужно выбрать Новая база данных и щелкнуть по появившейся кнопке «Создать», предварительно определив местоположение файла вашей БД на диске компьютера и задав ему имя. Выбрать одну из недавно открывавшихся БД можно в области Открыть последнюю базу данных, которая находится в правой части окна.

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

При открытии ранее созданной БД появится Предупреждение системы безопасности, говорящее о необходимости нажать кнопку «Параметры» и задать настройки для корректного открытия базы (рис. 3.2). В появившемся диалоговом окне Параметры безопасности Microsoft Office следует перевести переключатель в положение Включить это содержимое и затем активизировать кнопку «ОК».

Рис. 3.2. Предупреждение системы безопасности Вверху окна Microsoft Office Access 2007 (рис. 3.3) располагается Лента инструментов, а над ней – Панель быстрого доступа.

Центральную часть занимает окно открытой БД с ее элементами.

Внизу располагается Строка состояния.

Панель быстрого доступа Вкладки ленты инструментов На Ленте инструментов располагаются следующие вкладки:

• Главная – данная вкладка доступна по умолчанию и содержит команды, позволяющие выбрать режим представления БД (Таблицы или Конструктора), вырезать / вставить / скопировать данные с одного места на другое, задать шрифтовое оформление, произвести некоторые основные операции с записями в БД, а также фильтрацию и сортировку данных.

• Создание – на этой вкладке размещены команды создания всевозможных элементов, объектов БД – таблиц, форм, отчетов и т. п.

• Внешние данные – команды данной вкладки призваны обеспечить преобразование данных из БД, например, в таблицы Excel и наоборот – импорт данных из источников различного происхождения.

• Работа с базами данных – предназначена для производства команд различного рода общих работ с объектами БД, такие как команды отображения схемы данных, показа зависимостей между объектами, анализа данных и т. п.

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

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

По умолчанию на данной панели размещено всего три инструмента:

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

Рис. 3.4. Настройка Панели быстрого доступа По левому краю окна Access 2007 имеется один специфический элемент, называемый Областью переходов. По умолчанию Область перехода находится в свернутом виде – видно одно ее название.

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

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

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

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

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

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

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

Таблицы должны содержать сведения:

• о контингенте студентов (Студенты) – фамилия, номер зачетной книжки, дата рождения, фотография, мобильный телефон, номер группы;

• об учебном плане (Дисциплина) – наименование дисциплины, фамилия преподавателя, семестр обучения;

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

• о группе (Группа) – номер группы, фамилия старосты, количество студентов, количество проживающих в общежитии, количество минчан.

Конечно, в реальной БД гораздо больше полей, но для учебного примера рассмотрим только эти.

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

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

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

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

3.6.2. Элементы объекта «Таблица»

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

Таблица состоит из:

• записей (строк). Каждая запись содержит данные только об одном объекте (студенте), дублирование записей запрещается;

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

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

3.6.3. Создание таблицы базы данных в режиме Таблицы После открытия БД Access автоматически активизирует заготовку новой таблицы (рис. 3.6) только с одним именем поля – Код.

Рис. 3.6. Создание таблицы на основе пустой таблицы Если ввести значение в пустой столбец, то появится новое имя Поле1. Access 2007 автоматически будете определять тип данных в зависимости от того, что вы будете вводить. Не распознанным данным присваивается тип «Текстовый».

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

Переименовывать, удалять, вставлять и добавлять поля можно с помощью команд вкладки Режим таблицы группы Поля и столбцы (рис. 3.7).

Рис. 3.7. Группа инструментов Поля и столбцы MS Access 2007 имеет набор встроенных шаблонов, позволяющих значительно сократить время, затрачиваемое на создание полей. Можно выбрать поле из списка области Шаблоны полей.

Для этого на вкладке Режим таблицы в группе Поля и столбцы щелкните кнопку Новое поле.

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

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

Рис. 3.8. Создание таблицы в режиме Конструктор таблиц При первом открытии таблицы в режиме Конструктор появляется диалоговое окно Сохранение (рис. 3.9), где нужно задать имя таблицы, описывающее содержащиеся в ней данные (в нашем случае Студенты). Имя таблицы может содержать до 64 символов (букв или цифр) включая пробелы.

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

Имя поля состоит из комбинации букв, цифр, пробелов и специальных символов за исключением «.», «!», «'», «[ ]». Максимальная длина имени – 64 символа с учетом пробелов. Для элементов управления, которые могут быть расположены на формах и отчетах БД, – не более 255 символов.

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

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

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

Для таблицы Студенты зададим следующие поля и их типы (табл. 3.3, рис. 3.11).

Свойства полей и типы данных таблицы «Студенты»

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

Можно было бы выбрать в качестве ключевого поле Фамилия.

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

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

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

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

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

3.6.5. Создание таблицы на основе шаблона Если вас не устраивает «медленный» способ создания структуры таблицы с последующим добавлением требуемых полей, то можно создать ее с помощью шаблона таблицы. Мы уже упоминали шаблоны для создания БД и полей. Теперь возвращаемся к ним, но уже на уровне объекта БД – таблицы.

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

Необходимо выбрать шаблон, структура которого наиболее близка к структуре вашей таблицы. Например, для создания таблицы Адреса студентов, состоящей из полей Фамилия, Индекс, Город, Адрес, Домашний телефон и Мобильный телефон, можно выбрать шаблон Контакты. Используя контекстное меню (щелчком ПКМ по имени поля), можно удалять, переименовывать, вставлять поля. В результате появится таблица (рис. 3.12).

3.6.6. Создание таблицы с помощью импорта внешних данных В Access 2007 предусмотрена такая возможность как импорт информации из других источников, таких как электронных таблиц Excel, контактов Outlook, текстового редактора Word либо из ранее созданной БД. При импорте данных в Access создается их копия в новой или существующей таблице; при этом исходный файл не изменяется. Причем можно импортировать как всю базу, так и отдельные ее объекты.

Допустим, в деканате существует список всех студентов факультета, созданный в электронной таблице Excel (рис. 3.13).

Рис. 3.13. Лист Excel со списком студентов факультета Нам требуется перенести список фамилий студентов в БД, т. е. импортировать содержимое листа Excel в существующую или новую таблицу Access.

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

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

Затем необходимо открыть БД Access, в которой будут храниться импортируемые данные. На вкладке Внешние данные в группе Импорт выберите команду Excel (рис. 3.14).

В появившемся диалоговом окне в поле Имя файла с помощью кнопки Обзор необходимо указать имя файла Excel, который содержит данные для импорта, и способ сохранения импортируемых данных:

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

• Добавить копию записей в конец таблицы – чтобы добавить данные в существующую таблицу. Этот вариант недоступен, если БД не содержит таблиц.

• Создать связанную таблицу для связи с источником данных – если требуется хранить данные в листах Excel, но иметь возможность использовать удобные функции запросов и отчетов Access.

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

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

Рис. 3.15. Импортированная из Excel таблица Аналогичным образом можно импортировать данные и из других источников.

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

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

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

• удалить поле можно, используя клавишу «Del» либо контекстное меню.

3.7. Практическая работа № Организация связей между таблицами Цель работы: изучить реальные отношения между таблицами;