Создание и редактирование таблиц.

Практическое занятие 1

СОЗДАНИЕ И РЕДАКТИРОВАНИЕ ТАБЛИЦ.

СОРТИРОВКА И ПОИСК ДАННЫХ

Цель занятия

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

 

1.2. Основные понятия и определения

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

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

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

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

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

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

Связь - способ, которым связана информация о разных классах объектов.

 

Краткие сведения о СУБД MS Access

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

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

На компьютерах в учебных классах кафедры информатики установлена версия MS Access 2010, входящая в состав пакета Microsoft Office 2010.

 

Определение ключевых полей

 

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

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

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

· снимается проблема обновления внешних ключей в связанных таблицах, так как первичный ключ типа “счетчик” не может быть изменен;

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

· поле типа “счетчик” имеет относительно небольшую длину (8 байтов), что позволяет экономно использовать память в связанных таблицах;

· упрощаются и унифицируются связи между таблицами.

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

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

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

Если к моменту сохранения создаваемой таблицы ключевое поле не определено, то Access предлагает и в случае подтверждения автоматически создает первичный ключ, вставив перед первым указанным в структуре полем поле Код и задав для него тип “счетчик”.

Определение индексов

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

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

Уникальные индексы следует создавать по тем полям или их комбинациям (составные индексы), которые не должны повторяться при вводе данных в таблицу, если такая уникальность не обеспечивается первичным ключом таблицы. Например, таблица Студенты в базе данных Успеваемость студентов имеет ключевое поле Код типа “счетчик”, а поле НомерЗачетки является неключевым. Очевидно, что по номеру зачетки может производиться поиск и сортировка данных, и этот атрибут может использоваться в различных запросах к БД. Поэтому по полю НомерЗачетки следует создать индекс, причем он должен быть уникальным во избежание появления в таблице Студенты нескольких записей о студентах с одним и тем же номером зачетки.

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

 

1.8. Установка связей между таблицами

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

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

Рис. 1.3. Окно индексов Access

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

· запись в таблицу со стороны "многие" такого значения внешнего ключа, которого нет в таблице со стороны "один";

· удаление из таблицы со стороны “один” записи с таким ключом, который присутствует как внешний ключ в таблице со стороны “многие”.

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

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

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

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

Изменение структуры таблиц

 

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

Для изменения имени поля нужно перейти в строку с этим полем в окне конструктора таблицы и внести изменения в столбце Имя поля.

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

Для удаления поля нужно установить курсор на строку с его именем и выполнить команду Удалить строки из контекстного меню или выделить строку с полем и нажать клавишу Delete.

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

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

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

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

 

Редактирование связей

 

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

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

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

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

Сортировка данных

 

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

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

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

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

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

.

Поиск и замена данных

С увеличением количества записей поиск определенной информации усложняется. Access помогает значительно упростить и ускорить этот процесс.

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

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

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

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

 

Порядок проведения занятия

1. Запустить MS Access.

2. Создать новую базу данных в файле с именем УспеваемостьСтудентов.

3. Создать в режиме Конструктора следующую структуру таблицы Факультеты:

 

Имя поля	Тип поля	Свойства поля
КодФакультета	Счетчик	Ключевое поле
НаименованиеФак	Текстовый	Размер – 5; обязательное; индексированное (совпадения не допускаются)
Декан	Текстовый	Размер – 25; обязательное; неиндексированное

 

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

5. Убедиться в невозможности изменения ключевого поля Код.

6. Отсортировать таблицу по возрастанию поля НаименованиеФак.

7. Создать в режиме Конструктора следующую структуру таблицы Группы:

 

Имя поля	Тип поля	Свойства поля
КодГруппы	Счетчик	Ключевое поле
НомерГруппы	Текстовый	Размер – 7; обязательное; индексированное (совпадения не допускаются); маска ввода - >LLL9999 (3 прописные буквы и 4 цифры)
КодФакультета	Числовой	Размер – длинное целое; обязательное; индексированное (допускаются совпадения); формат – фиксированный с 0 дес. знаков
ГодСоздания	Числовой	Размер – целое; обязательное; индексированное (допускаются совпадения); формат – фиксированный с 0 дес. знаков; маска ввода – 9999 (4 цифры); значение по умолчанию – 2014; условие на значение - Between 2010 And 2015; сообщение об ошибке – Недопустимый год создания группы

 

8. Добавить таблицы Факультеты и Группы в схему данных базы.

9. С помощью Мастера подстановок создать подстановку с комбинированным списком в поле КодФакультета из таблицы Факультеты (см. п. 1.9).

10. Открыть окно индексов (см. п. 1.7) и убедиться в наличии всех индексов, заданных при формировании структуры таблицы.

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

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

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

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

 

15. Создать в режиме Конструктора следующую структуру таблицы Студенты:

 

Имя поля	Тип поля	Свойства поля
КодСтудента	Счетчик	Ключевое поле
НомерЗачетки	Текстовый	Размер – 10; обязательное; индексированное (совпадения не допускаются)
ФИО	Текстовый	Размер – 25; обязательное; индексированное (допускаются совпадения)
БаллыЕГЭ	Числовой	Размер – целое; обязательное; индексированное (допускаются совпадения); формат – фиксированный с 0 дес. знаков; маска ввода – 999 (3 цифры);
КодГруппы	Числовой	Размер – длинное целое; обязательное; индексированное (допускаются совпадения); формат – фиксированный с 0 дес. знаков
ДатаРождения	Дата/время	Краткий формат даты; маска ввода формируется конструктором; обязательное; индексированное (допускаются совпадения)
Город	Текстовый	Размер – 20; обязательное; индексированное (допускаются совпадения); по умолчанию - Москва
Стипендия	Денежный	0 дес. знаков; маска ввода – 9 999р.; обязательное; индексированное (допускаются совпадения); по умолчанию - 0

 

16. Добавить таблицу Студенты в схему данных базы.

17. С помощью Мастера подстановок создать подстановку с комбинированным списком в поле КодГруппы из таблицы Группы (см. п. 1.9).

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

19. Открыть окно схемы данных и убедиться в появлении связи между таблицами Группы и Студенты.

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

21. Перевести таблицу Студенты в режим таблицы и ввести 10-15 записей, выбирая из комбинированного списка различные группы.

22. Отсортировать таблицу по номерам зачетных книжек.

23. Найти в таблице Студенты все записи, удовлетворяющие следующим условиям:

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

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

24. Найти записи для студентов, проживающих в Москве, и очистить поле Город в этих записях с помощью операции Поиск и замена (см. п. 1.14).

 

25. Создать в режиме Конструктора следующую структуру таблицы Дисциплины:

 

Имя поля	Тип поля	Свойства поля
КодДисциплины	Счетчик	Ключевое поле
НазваниеДисц	Текстовый	Размер – 30; обязательное; индексированное (совпадения не допускаются)
Часы	Числовой	Размер – целое; необязательное; неиндексированное; формат – фиксированный с 0 дес. знаков; маска ввода – 999 (3 цифры);

 

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

27. Отсортировать таблицу по возрастанию поля НазваниеДисц.

28. Создать в режиме Конструктора следующую структуру таблицы Оценки:

 

Имя поля	Тип поля	Свойства поля
КодСтудента	Числовой	Размер – длинное целое; обязательное; индексированное (допускаются совпадения); формат – фиксированный с 0 дес. знаков
КодДисциплины	Числовой	Размер – длинное целое; обязательное; индексированное (допускаются совпадения); формат – фиксированный с 0 дес. знаков
Оценка	Числовой	Размер – байт; обязательное; индексированное (допускаются совпадения); формат – фиксированный с 0 дес. знаков; маска ввода – 9 (1 цифра); условие на значение - Between 2 And 5; сообщение об ошибке – Недопустимая оценка

 

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

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

31. Добавить таблицы Дисциплины и Оценки в схему данных базы.

32. С помощью Мастера подстановок создать подстановку с комбинированным списком в поле КодСтудента из таблицы Студенты (см. п. 1.9). Для удобства идентификации студентов при вводе данных использовать в подстановке поля НомерЗачетки и ФИО таблицы Студенты.

33. С помощью Мастера подстановок создать подстановку с комбинированным списком в поле КодДисциплины из таблицы Дисциплины (см. п. 1.9).

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

35. Двойным щелчком по каждой из этих связей дважды открыть окно Изменение связей и установить флажки Обеспечение целостности данных, если это не было сделано в п. 32-33. Для связи Студенты-Оценки установить флажок Каскадное удаление связанных записей, чтобы при удалении записи о студенте из таблицы Студенты автоматически удалялись оценки этого студента из таблицы Оценки.

36. Перевести таблицу Оценки в режим таблицы и ввести 10-15 записей, выбирая из комбинированных списков различных студентов и различные дисциплины.

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

 

 

Рис. 1.4. Окно схемы данных

Содержание отчета

1. Тема и цель занятия.

2. Скриншоты всех таблиц в режиме конструктора и в режиме таблицы.

3. Скриншот схемы базы данных.

1.17. Контрольные вопросы

1. Что понимают под классом объектов, свойствами объектов?

2. Типы связей "один-к-одному", "один-ко-многим" и “многие-ко-многим”: как они реализуются в реляционных базах данных? Как устанавливаются связи в СУБД Access?

3. Что такое ключевое поле? Какие требования предъявляются к нему?

4. Как в СУБД Access определяется ключевое поле?

5. Для чего используются индексы таблиц? Как определяются индексы в Access?

6. Перечислите типы данных, поддерживаемые Access.

7. Для чего и как используется Мастер подстановок?

8. Каковы возможности Access по изменению структуры таблиц?

9. Как отсортировать или отобрать записи по одному полю? По нескольким полям?

10. Как произвести поиск и замену данных в Access?

 

 

Практическое занятие 1

СОЗДАНИЕ И РЕДАКТИРОВАНИЕ ТАБЛИЦ.

СОРТИРОВКА И ПОИСК ДАННЫХ

Цель занятия

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

 

1.2. Основные понятия и определения

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

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

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

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

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

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

Связь - способ, которым связана информация о разных классах объектов.