Лабораторная работа

Лабораторная работа №28 MS Access. Создание межтабличных связей. Сортировка и фильтрация данных. Цель работы: привить у студентов навыки создания базы данных, слздания межтабличных связей. Научить сортировать и фильтровать данные. Краткие теоретические сведения Сразу   после   запуска   Access   на   экране   появляется   окно,   в   котором можно установить флажок  Новая база данных  и затем нажать кнопку OK. После этого на экране появится окно  Файл новой базы данных, в котором надо   ввести   название   новой   базы   данных,   выбрать   папку,   где   она   будет храниться и затем нажать кнопку Создать. После этого на экране появится окно базы данных, в котором перечислены объекты базы данных:  Таблицы, Запросы, Формы, Отчеты, Макросы, Модули. Планирование баз данных. Перед созданием новой базы данных обязательно необходимо хорошо продумать следующие вопросы: 1 Какие   данные   будут   храниться   в   БД   и   как   их   организовать наилучшим образом? ­ Это позволит определить, какие потребуются таблицы и какие связи необходимо организовать между ними. 2 Какие действия с данными необходимо будет производить в процессе эксплуатации   БД?   ­   это   позволит   определить,   какие   потребуются формы. 3 Какие   документы   необходимо   будет   выводить   на   печать?   ­   Это позволит определить, какие потребуются отчеты. Таблицы   и   связи   между   ними   представляют   собой   так   называемую модель   данных,   которая   является   основой   любой   БД.   В   теории   БД  которые существуют   так   называемые   правила  нормализации   данных, позволяют и непоследовательность модели данных. противоречивость избыточность,       устранить   При проектировании таблиц базы данных рекомендуется: 1 Избегать повторения информации Если информация повторяется то логичнее разбить информацию в две таблицы, задать ключевые поля и связать таблицы по ключевым полям. 2 Избегать повторяющихся групп Повторяющаяся группа ­ это столбцы, которые повторяются в пределах одной  и  той  же   строки   для   хранения  нескольких   значений  данных  одного вида.  Например, мы храним таблицу со списком зарегистрированных фирм, и в   ней   10   столбцов   с   наименованиями   фирм­учредителей.   Но   количество учредителей чаще всего меньше 10­ти и поэтому многие поля в этой таблице будут пустыми. С другой стороны, если когда­нибудь придется зарегистрировать фирму с   количеством   учредителей   больше   10­ти,   то   придется   заводить   новый столбец. Если наша таблица связана с другими, и мы уже разработали много форм и отчетов, то такая ситуация повлечет за собой переделку всей базы данных. 3 Каждая   таблица   должна   описывать   одну   сущность   реального мира Не   следует   смешивать,   например,   в   одной   таблице   сведения   о сотрудниках фирмы и заключенных ими договорах. 4 Там, где это возможно, следует  использовать коды (первичные ключи) Первичные ключи помогают связывать таблицы. 5 Справочную   информацию   следует   помещать   в   отдельные таблицы. Создание таблиц в Access. Для создания новой таблицы в базе данных необходимо в окне базы данных   выбрать   объект  Таблица,   а   затем   нажать   кнопку   Создать.   В появившемся окне будет предложено выбрать один из следующих способов создания: 6 Режим таблицы 7 Конструктор 8 Мастер таблиц 9 Импорт таблиц 10Связь с таблицами Конструктор таблиц. Чтобы   добавить   поле,   в   верхней   части   окна   таблицы   в   режиме конструктора следует ввести имя поля и определить его тип.  Имя поля должно содержать не более 64 символов и может включать любые комбинации букв, цифр и пробелов, а также специальных символов, за исключением точки, восклицательного знака, надстрочного символа и прямых скобок.   Имя   не   должно   начинаться   с   пробела   и   содержать   управляющие символы. Тип   данных  определяет,   какого   вида   данные   допускается   вводить   в поле. Уникальная метка, называемая ключом, используется для определения каждой   записи   таблицы.   Подобно   тому,   как   номерной   знак   однозначно определяет автомобиль, ключ определяет запись. Ключевые поля в таблицах используются для создания межтабличных связей. Чтобы определить ключ, необходимо выделить строку с описанием нужного поля и нажать пиктограмму Ключ. Для   задания   свойства   поля   надо   выбрать   его   в   верхней   части   окна конструктора таблиц и в нижней части окна ввести значения этого свойства или выбрать его из списка. По   окончании   описания   полей   таблицы,   необходимо   закрыть   окно конструктора. Для   наполнения   таблицы   данными   откройте   ее   в   режиме   таблицы (двойной   щелчок   по   значку   таблицы   в   окне   база   данных)   и   внесите информацию, соответственно типам данных каждого поля. От того, как новая таблица связана с остальными, зависит, какое из ее полей следует назначить первичным ключом. В теории баз данных известны 4 варианта   называемых   обычно отношениями.  связей   между   двумя   таблицами, Связь Один­к­одному. Каждой записи первой таблицы соответствует не больше   одной   записи  второй  таблицы  и   наоборот.   Соответствие   записей устанавливается   в   результате   поиска   в   поле,   являющегося  первичным ключом  одной   из   таблиц,   значения   поля,   называемого  внешним   ключом второй таблицы. Связь  Многие­к­одному.   Любой   записи  второй  таблицы   может соответствовать любое количество записей первой таблицы, но не наоборот. В   этом   случае   ключевое   поле  первой  таблицы   будет  внешним   ключом,   и повторяющиеся значения в нем допускаются. Связь  Один­ко­многим.   Первичный   ключ  первой  таблицы   (поле, содержащее уникальные значения), связывается с внешним ключом  второй таблицы (значения поля могут повторяться). При этом каждой записи первой таблицы может соответствовать несколько записей второй. Можно сказать, что «один­ко­многим» ­ это «многие­к­одному» наоборот. Связь  Многие­ко­многим.   Каждой   записи   одной   таблицы   может соответствовать   любое   количество   записей   другой   таблицы   и   наоборот. Соответственно,   поля  обеих  таблиц,   по   которым   осуществляется   связь, являются внешними ключами и могут содержать повторяющиеся значения. Создание межтабличных связей. Для   создания   межтабличных   связей   в   СУБД   MS   Access   существует команда  Сервис   –   Схема   данных,   а   также   на   панели   инструментов расположена одноименная пиктограмма  Схема данных. В   окне   схемы   данных   связи   можно   создавать   путем   перетаскивания полей из одной таблицы в другую. При   создании   межтабличной   связи   очень   важно,  из   какой   таблицы   в какую перетаскивается поле. Поля надо перетаскивать из таблицы со стороны «один» в таблицу со стороны «многие». При этом в окне  Изменение связей главная   таблица   (со   стороны   «один»)   окажется   слева   под   заголовком Таблица/запрос,   а   таблица   со   стороны   «многие»   ­   справа   под   заголовком Связанная   таблица/запрос.   При   попытке   сделать   наоборот   мы   получим сообщение об ошибке. На созданной в схеме связи следует щелкнуть правой кнопкой мыши, чтобы установить дополнительные параметры: в окне Изменение связей щелкнуть на кнопке Объединение. Появится окно Параметры объединения. Если необходимо, например, чтобы в дальнейшем при   объединении   данных   таблиц   отображались   все   записи   таблицы «Пациенты»,   независимо   от   того,   имеется   ли   соответствующая   запись   в таблице «Посещения», надо выбрать 2­й тип объединения. В   диалоговом   окне  Изменение   связей  есть   очень   полезный   флажок   – Обеспечение   целостности   данных.  Следует   установить   его,   чтобы   Access отказывалась   сохранять   в   подчиненной   таблице   записи,   относящиеся   к несуществующей записи в главной таблице. Щелкните   на   кнопке   Создать,   чтобы   новая   связь   появилась   в   окне Схема данных. Связи между таблицами в Access можно создавать разными способами. На   первых   порах   удобнее   всего   использовать   команду  Вставка   ­   Поле подстановки в режиме конструктора для вызова мастера создания связей. ЗАДАНИЕ I Создание таблиц базы данных Создадим базу данных, содержащую сведения о студентах вашего потока. Для этого выполним следующие действия: 1. Запустим программу MS Access. 2. При запуске появится диалоговое окно, в котором надо выбрать строку Новая база данных. 3. В окне  Файл новой базы данных  указать имя новой БД­  ДЕКАНАТ  и сохранить в папке ПР13. 4. Нажмите   кнопу  Создать.  В   появившемся   окне  База   данных активизировать вкладку Таблицы и щелкнуть на кнопке Создать. 5. Создать   таблицу,   воспользовавшись  Конструктором.   В   окне  Новая таблица выбрать пункт Конструктор и подтвердить выбор. 6. Определить поля таблицы. В появившемся окне создать поля базы данных, согласно следующей таблице. Поле Номер Фамилия Имя Отчество Дата  рождения Группа Адрес Тип поля Счетчик Текстовое Текстовое Текстовое Дата Текстовое Текстовое Размер поля 15 10 15 Краткий  формат 7 20 7. Для   ввода   типа   поля   использовать   значок   контекстного   меню,   который появляется при установке курсора в столбец Тип данных. 8. Определить  первичный  ключ  для таблицы.  В  данной  таблице  ключевым является поле Номер. Чтобы сделать поле ключевым, нужно выделить его и выбрать из меню  Правка  команду  Ключевое поле  или нажать кнопку Ключевое поле на панели инструментов. 9. Закрыть   заполненную   таблицу.   При   закрытии   сохраните   ее   под   именем Студенты. II Ввод и редактирование данных 10.В окне База данных появилось имя сохраненной таблицы. Для того, чтобы вводить данные, надо открывать ее в режиме таблицы. Щелкнуть на кнопку Открыть. 11.Занести в таблицу 6 – 7 записей. Для поля Группа использовать номера 56, 57, 58. Отредактировать введенные данные в таблицу: заменить во второй записи фамилию. 12.В поле Дата рождения изменить в первой записи год рождения. 13.Удалить   последнюю   запись   в   таблице.   Для   этого   нужно   выделить   ее: установить курсор мыши к левой границе таблицы до изменения его в виде стрелки, направленной вправо, щелкнуть мышью и нажать клавишу Delete. 14.Добавить еще две записи. 15.Сохранить таблицу и закрыть ее. III Создание многотабличной БД 1. Создать таблицы СЕССИЯ и СТИПЕНДИЯ, используя ту же технологию, что   и   при   создании   таблицы   СТУДЕНТЫ   в   пункте   1.   Атрибуты   поля Номер таблицы СЕССИЯ должны быть такими же, как атрибуты этого же поля таблицы СТУДЕНТЫ. Состав полей и их свойства следующие: СЕССИЯ Признак ключа Поле  Номер Ключ Оценка 1 Оценка 2 Оценка 3 Оценка 4 Результат СТИПЕНДИЯ Признак ключа Поле  Ключ Результат Процент Тип поля Числовое Числовое Числовое Числовое Числовое Текстовое Размер поля Фиксированный Фиксированный Фиксированный Фиксированный 5 Тип поля Текстовое Числовое Размер поля 5 Процентный 2. Заполнить таблицы данными; оценки в записи ввести на свое усмотрение так,   чтобы   в   записях   присутствовали   разные   комбинации   оценок   из четырех групп: Хор. За две 4 и более Неуд. За удовл. и  неудовл. 3. В   поле  Результат  данные   заносить   в   соответствии   с   представленной таблицей, например, если в записи три оценки 5 и одна оценка 4, то в результат занести хор1. Хор1. 5 5 5 4 Отл 5 5 5 5 4. Поле Процент заполнить в соответствии со следующей таблицей: Результат Неуд. Хор. Хор1. Отл. 5. Сохранить обе таблицы и закрыть их. IV Установление связей между таблицами 6. В   окне  База   данных   Деканат  должны   быть   имена   трех   таблиц: СТУДЕНТЫ,   СЕССИЯ,   СТИПЕНДИЯ.   Для   установления   связей выполнить команду Сервис – Схема данных. Процент 0,00% 100,00% 200,00% 300,00% 7. В появившемся окне выполнить добавление всех трех таблиц в схему. 8. Установить связи между таблицами СТУДЕНТЫ и СЕССИЯ. Для этого протащить указатель мыши от поля  Номер  таблицы СТУДЕНТЫ к полю Номер таблицы СЕССИЯ при нажатой клавише мыши. 9. В   появившемся   диалоговом   окне  Связи  активизировать   значок Обеспечение   целостности   данных,   отношение   «Один   к   одному», активизировать   значки  Каскадное   обновление   связанных   полей  и Каскадное удаление связанных полей. Прочитать встроенную справку об этих значках. Нажать кнопку Создать. 10.Установить связь между таблицами СТИПЕНДИЯ и СЕССИЯ. Для этого протащить указатель мыши от поля Результат  таблицы СТИПЕНДИЯ к полю Результат таблицы СЕССИЯ. Здесь отношение «Один ко многим». 11.Закрыть окно Схема данных, при выходе сохранить связи. Задание для самостоятельной работы студентов 1. Добавьте в базу данных  Деканат  таблицу ФАКУЛЬТЕТЫ, содержащую следующую информацию: Шифр специальности Название Название факультета 05435 03565 07867 04564 04565 специальности «Менеджмент» «Маркетинг» «Юриспруденция» «Правоведение» «Таможенное дело» Экономический Экономический Юридический Юридический Юридический 2. Добавьте в таблицу СТУДЕНТЫ поле шифр специальности и заполните его соответствующими данными из таблицы ФАКУЛЬТЕТЫ. Свяжите таблицы СТУДЕНТЫ и ФАКУЛЬТЕТЫ. Сохраните базу данных Деканат. V Поиск, сортировка и отбор данных 1. Откройте таблицу СТУДЕНТЫ в режиме формы. 2. Осуществите поиск студента по полю Фамилия. Для этого:  установите   курсор   в   строку   поля,   по   которому   будет   осуществляться поиск, то есть Фамилия  выполните команду Правка – Найти или нажмите пиктограмму Найти. 3.Закрыть окно формы. 4.Откройте таблицу СТУДЕНТЫ. 5.Отсортируйте записи таблицы в алфавитном порядке по полю  Фамилия. Для этого:  установите курсор в поле Фамилия;  выполните   команду  Записи   –   Сортировка   –   По   возрастанию  или воспользоваться соответствующей пиктограммой  6.Используя фильтр, вывести на экран список студентов группы БУА­12. Для этого:  Выполнить   команду  Записи   –   Изменить   фильтр  или   использовать пиктограмму  Установить курсор в поле Группа, и из контекстного меню выбрать номер нужной группы  Выполнить   команду  Фильтр   –   Применить   фильтр  или   использовать соответствующую пиктограмму  Чтобы   убрать   фильтр,   воспользуйтесь   командой  Записи   –   Удалить фильтр или применить ту же пиктограмму.  Закрыть таблицу. 7.Открыть таблицу СТУДЕНТЫ в режиме формы и выполнить тот же фильтр: отобрать студентов группы БУА­12. Для этого выполните действия:  В поле  Формы  найти запись, которая содержит индекс нужной группы – БУА­12  Выполнить   команду  Записи   –   Фильтр   –   Фильтр   по   выделенному  или воспользоваться соответствующей пиктограммой на панели инструментов 8.Удалить фильтр Контрольные вопросы 1. Назовите основные элементы окна Access. 2. Перечислите основные объекты окна базы данных. 3. Какие режимы работы используются для работы с таблицей? 4. Что такое Конструктор в СУБД Access? 5. Для чего служит ключевое поле? 6. Что такое счетчик? 7. Какой   тип   данных   следует   использовать   для   создания   поля, содержащего рисунки? 8. Что такое маска ввода? Какие знаки используются для работы с маской? 9. Объясните для чего необходимо связывать таблицы при работе с базами данных? 10. Перечислите виды связей между таблицами базы данных. 11.Назовите   обязательные   условия   при   создании   связей   между главной и подчиненной таблицами? 12. Как вы понимаете связь «Один к одному»? 13. Что означает связь «Один ко многим»? 14. Что такое связь «Многие ко многим»?         15.Что такое целостность данных?