|
|
Проектирование структуры таблиц12 Пусть требуется разработать базу данных для предметной области "Университет". Структура реляционной базы данных всегда разрабатывается таким образом, чтобы каждая таблица, которая в ней находится, не содержала избыточной информации. Например, в базе данных "Университет" необходимо хранить данные об оценках, которые получил каждый студент по той или иной учебной дисциплине. Также, нужно каким-то образом хранить названия дисциплин, по которым эти оценки ставятся. Кроме того, необходимо хранить фамилию, имя и отчество преподавателей с указанием дисциплины, которые ведёт каждый из них. Если эта информация будет храниться в одной таблице, то очевидно нерациональное использование памяти компьютера, так как для каждого студента придётся хранить в соответствующей записи названия дисциплин и фамилии преподавателей, которые будут повторяться много раз. Поэтому информацию необходимо разбить на несколько таблиц, взаимосвязанных между собой, а именно: Таблица "Студенты", содержащая информацию о студенте, в частности уникальный код, который будет использоваться при хранении оценок; Таблица "Преподаватели", содержащая информацию о преподавателе, в частности уникальный код, используемый при хранении учебных дисциплин; Таблица "Дисциплины", содержащая информацию о учебной дисциплине, в частности уникальный код преподавателя, который ведет занятия по этой дисциплине, а также уникальный код дисциплины, используемый при хранении оценок; Таблица "Успеваемость", содержащая информацию об оценках студентов, в частности уникальный код для каждой записи с оценкой, код студента, код дисциплины и оценка, которая получена данным студентом по этой дисциплине. Таким образом, структура таблиц, используемых в базе данных "Университет", будет выглядеть следующим образом (таблицы 4.1 – 4.4):
Таблица 4.1 Структура таблицы "Студенты"
Таблица 4.2 Структура таблицы "Преподаватели"
Таблица 4.3 Структура таблицы "Дисциплины"
Таблица 4.4 Структура таблицы "Успеваемость"
Для созданных таблиц необходимо установить связи, чтобы, например, по фамилии студента в таблице "Студенты" отобрать все записи с его оценками из таблицы "Успеваемость". Как видно из таблиц 4.1 – 4.4, в разрабатываемой структуре данных существуют следующие взаимосвязи: 1) таблица "Студенты", поле Код Студента → таблица "Успеваемость", поле Код Студента; 2) таблица "Преподаватели", поле Код Преподавателя → таблица "Дисциплины", поле Код Преподавателя; 3) таблица "Дисциплины", поле Код Дисциплины → таблица "Успеваемость", поле Код Дисциплины. Модель базы данных "Университет" представлена на рис. 4.1. Таблицы "Студенты" и "Преподаватели" являются главными. Таблица "Дисциплины" является подчинённой по отношению к таблице "Преподаватели", так как содержит внешний ключ «Код преподавателя». Таблица "Успеваемость" является подчинённой как по отношению к таблице "Дисциплины", так и по отношению к таблице "Студенты", так как в ней содержатся внешние ключи "Код студента" и "Код дисциплины".
Рис. 4.1. Модель базы данных "Университет"
Создавать структуру таблиц удобнее всего с помощью конструктора. Для этого необходимо: 1. В окне базы данных открыть вкладку "Таблицы" и дважды щёлкнуть по команде "Создание таблицы в режиме конструктора" (рис 4.2).
Рис. 4.2. Окно базы данных "Университет"
В окне конструктора необходимо ввести структуру таблицы "Студенты" (рис. 4.3).
Рис 4.3. Окно конструктора таблицы "Студенты"
В графу "Имя поля" вводятся названия полей, а в графу "Тип данных" – соответствующий тип данных. В нижней части окна конструктора находится раздел "Свойства поля". Здесь можно задать размер поля, формат и маску ввода. В соответствии с таблицами 4.1 – 4.4 необходимо создать структуру таблиц в базе данных, с учётом того, что поля "Код студента" таблицы "Студенты", "Код преподавателя" таблицы "Преподаватели", "Код дисциплины" таблицы "Дисциплины" и "Код оценки" таблицы "Успеваемость" являются ключевыми. Ключевое поле задаётся следующим образом: поместить курсор на нужное поле и нажать на кнопку После создания структуры таблиц необходимо установить связи между таблицами. Лучше это сделать с помощью Мастера подстановок. Рассмотрим создание связи между таблицами "Преподаватели" и "Дисциплины". Для этого нужно: 1) Открыть подчинённую таблицу "Дисциплины" в конструкторе и поставить курсор на поле, являющееся внешним ключом ("Код преподавателя"). В качестве типа данных выбрать "Мастер подстановок". Появится окно "Создание подстановки" (рис. 4.4).
Рис. 4.4. Окно Мастера подстановок
На первом шаге по умолчанию выбрана опция "Объект "столбец подстановки" будет использовать значения из таблицы или запроса". Не изменяя ничего, нажать кнопку "Далее". На втором шаге необходимо выбрать таблицу, с которой необходимо связать текущую таблицу, в данном случае – "Преподаватели" и нажать на кнопку "Далее". На третьем шаге нужно указать имя ключевого поля таблицы, с которой устанавливается связь. В таблице "Преподаватели" ключевым является поле "Код преподавателя". Необходимо выделить имя этого поля и нажать на кнопку . Если по ошибке было выделено другое имя поля, выбор можно отменить, нажав кнопку На четвёртом шаге можно выбрать порядок сортировки поля. Нажать на кнопку "Далее". На пятом шаге можно нажать на кнопку "Готово". Появится окно сообщения о сохранении таблицы "Дисциплины". Нужно нажать на кнопку "Да", после чего будет создана связь «один ко многим» между таблицами «Преподаватели» и "Дисциплины". Аналогичным образом необходимо установить связи между таблицами "Дисциплины" – "Успеваемость" и "Студенты" – "Успеваемость". После создания всех связей в схеме данных нужно включить опцию "Обеспечение целостности данных". Для этого нужно щёлкнуть по значку "Схема данных"
Рис. 4.5 Окно Схема данных
Формы можно создавать тремя способами: - с помощью мастера; - с помощью конструктора; - используя автоформы. Самый простой способ – это использование автоформ. Чтобы создать автоформу, нужно выделить одну из главных таблиц и выбрать пункт "Автоформа" в меню "Вставка". Появится составная форма, с помощью которой можно заполнять сразу две таблицы. Например, можно создать две составные формы по таблицам "Студенты" и "Преподаватели".
Рис. 4.6 Составная форма по таблице "Студенты"
А для таблицы "Дисциплины" можно создать простую форму. Для этого, в разделе "Таблицы" окна базы данных выделяем таблицу "Дисциплины" и выбираем пункт "Форма" в меню "Вставка". Таким образом, будет создано три формы: "Дисциплины", "Преподаватели" и "Студенты". С помощью форм требуется заполнить все таблицы базы данных, а именно, главные таблицы "Преподаватели" и "Студенты" должны содержать по десять записей, а подчинённые "Дисциплины" и "Успеваемость" – по двадцать записей. Работа с запросами
Рассмотрим создание запроса на выборку дисциплин, которые ведёт преподаватель Воронин. Для этого необходимо: 1) В окне базы данных "Университет" выбрать объект "Запросы". При двойном щелчке по команде "Создание запроса в режиме конструктора", появится окно добавления таблиц. 2) Необходимо добавить две таблицы: "Дисциплины" и "Преподаватели". Добавлять поля в запрос можно с помощью двойного щелчка по именам поля в таблицах, находящихся в верхней части окна конструктора запроса. Для данного запроса добавить поля: "Фамилия", "Имя", "Отчество" из таблицы "Преподаватели", а также поле "Название" из таблицы " Дисциплины". В строке "Условие отбора" поля "Фамилия" указать фамилию преподавателя "Воронин". Общий вид запроса на выборку показан на рис. 4.7.
Рис. 4.7 Окно конструктора запроса на выборку дисциплин,
Создадим запрос на выборку дисциплин, у которых количество часов меньше 102. Запрос строится на основе таблиц "Дисциплины" и "Преподаватели". Окно конструктора запроса приведено на рисунке 4.8.
Рис. 4.8 Окно конструктора запроса на выборку дисциплин
Создадим запрос на выборку студентов, у которых по дисциплине "Высшая математика" оценка "5" (рис. 4.9).
Рис. 4.9 Окно конструктора запроса на выборку студентов,
Создадим запрос на выборку успеваемости студентки Чудновой, то есть, необходимо указать, какие оценки и по каким дисциплинам (включая фамилию преподавателя) имеет студент (рис. 4.10).
Рис. 4.10 Окно конструктора запроса на выборку оценок,
Создадим параметрический запрос, который отбирает предметы, с количеством часов менее указанного значения при запуске запроса. При создании параметрического запроса будет использован уже имеющийся запрос "Дисциплины (меньше 102 часов)". Необходимо выделить этот запрос, скопировать его в буфер обмена, выбрав пункт "Копировать" в меню "Правка", а затем, вставить его посредством команды "Вставить" из меню "Правка". Можно выделить следующие действия при создании запроса: 1) .В появившемся окне указать имя нового запроса, например, "Дисциплины (параметрический)". 2). Открыть запрос в конструкторе. В строке "Условие отбора" поля "Количество часов" вместо выражения "<102" написать: "<[Введите количество часов]". 3). При последующих запусках запроса будет появляться окно (рис. 4.11), в котором нужно указать количество часов. Результатом выполнения запроса будет список дисциплин, у которых количество часов меньше указанного значения.
Рис. 4.11 Окно ввода значений для параметрического запроса
Рассмотрим создание запроса с вычисляемым полем из таблицы "Студенты", который будет рассчитывать возраст студентов, исходя из их даты рождения. Для расчета возраста студентов воспользуемся встроенными функциями Round () – округление до целого, и Date () – сегодняшняя дата. Для этого требуется выполнить следующие действия: 1). Дважды щёлкнуть по команде "Создать запрос в режиме конструктора". 2). В окне конструктора добавить таблицу "Студенты" и выбрать двойным щелчком мыши следующие поля: "Фамилия", "Имя", "Отчество", "Дата рождения". 3). В следующем за "Датой рождения" поле щёлкнуть правой кнопкой мыши по строке "Поле" и выбрать пункт "Построить". Появится окно "Построитель выражений" (рис. 4.12). В верхней части окна необходимо написать выражение: Возраст: Round((Date()-[Студенты]![Дата рождения])/365) и нажать на кнопку "ОК".
Рис. 4.12 Окно Построитель выражений
4). Закрыть окно конструктора и сохранить запрос под именем "Расчет возраста". Результатом выполнения запроса будет список студентов с рассчитанным возрастом в поле "Возраст". Создадим запрос на обновление записей в таблице, который изменяет Фамилию преподавателя с "Устинова" на "Руденко". В этом случае можно выделить следующие действия: 1). Выбрать команду "Создание запроса в режиме конструктора". 2). В открывшемся окне конструктора добавить таблицу "Преподаватели". 3). Добавить в запрос поле "Фамилия", дважды по нему щёлкнув. 4). Выбрать в меню "Запрос" пункт "Обновление". 5). Для поля "Фамилия" в строке "Условие отбора" написать фамилию, которую нужно сменить – "Устинова", а в строке "Обновление" – фамилию, на которую необходимо поменять – "Руденко" (рис. 4.13).
Рис. 4.13 Окно конструктора запроса на обновление записей
6). Сохранить запрос под названием "Обновление" и закрыть. 7). Запустить запрос на обновление. Появится сообщение о том, что будет изменена одна запись. На вопросы ответить утвердительно. 8). Открыть таблицу "Преподаватели" и убедиться, что запись обновлена. Создадим запрос на добавление записей в уже имеющуюся таблицу "Студенты". Перед построением запроса на добавление, необходимо создать таблицу "Новые студенты", из которой данные будут добавляться в таблицу "Студенты". Для этого необходимо: 1). Перейти к объектам "Таблицы", щёлкнуть правой кноп-кой мыши по таблице "Студенты" и выбрать пункт "Копировать". 2). Щёлкнуть правой кнопкой мыши по свободному месту в окне базы данных и выбрать пункт "Вставить". Должно появиться окно "Вставка таблицы", в котором нужно указать имя для новой таблицы (например, "Новые студенты"), выбрать опцию "только структура", и нажать "ОК". 3). Новую таблицу необходимо заполнить тремя записями. После этого можно переходить к построению запроса. 4). Перейти к объектам "Запросы" и выбрать команду "Создание запроса в режиме конструктора". 5). В окно конструктора добавить таблицу "Новые студенты". 6). В построитель запроса добавить все поля, кроме "Код студента". 7). В меню "Запрос" выбрать пункт "Добавление". В появившемся окне нужно выбрать таблицу "Студенты" (рис. 4.14) и нажать "ОК".
Рис. 4.14. Окно "Добавление" с выбранной таблицей "Студенты"
8). Сохранить запрос под именем "Запрос на добавление" и закрыть конструктор. Затем запустить созданный запрос. Появится сообщение о том, что в таблицу "Студенты" добавятся три записи. На вопросы ответить утвердительно. Сформируем запрос на создание таблицы, который отбирает всех студентов преподавателя Малаховой. Последовательность действий при создании запроса будет следующей: 1). Создать запрос в режиме конструктора на основе таблиц "Преподаватели", "Студенты", "Дисциплины" и "Успеваемость". Таблица "Успеваемость" в данном запросе нужна лишь для установления связи между таблицами "Преподаватели" и "Студенты". 2). Добавить в запрос следующие поля: "Фамилия", "Имя", "Отчество" из таблицы "Преподаватели", поле "Название" из таблицы "Дисциплины" и поля "Фамилия", "Имя", "Отчество" из таблицы "Студенты". Зададим условие отбора для поля "Фамилия" таблицы "Преподаватели" как показано на рис. 4.15.
Рис. 4.15. Окно конструктора запроса на создание таблицы
3). В меню "Запрос" выбрать пункт "Создание таблицы". 4). Сохранить запрос под именем "Студенты Малаховой". Закрыть окно конструктора. 5). Запустить запрос двойным щелчком мыши. Появится сообщение о том, что будет создана новая таблица на основе запроса. На вопросы ответить утвердительно. 6). Перейти к объекту "Таблицы" и убедиться, что новая таблица появилась в этом разделе. Создадим перекрёстный запрос, который будет отображать успеваемость (оценки) всех студентов. Для начала необходимо создать простой запрос на выборку оценок студентов. Последовательность действий при создании запроса будет следующей: 1). Создать запрос на выборку в режиме конструктора, добавив в запрос следующие поля: "Фамилия", "Имя", "Отчество" из таблицы "Студенты", поле "Название" из таблицы "Дисциплины" и "Оценки" из таблицы "Успеваемость" (рис. 4.16). Сохранить запрос под именем "Оценки". Закрыть окно конструктора.
Рис. 4.16. Окно конструктора запроса на выборку оценок
2). В окне базы данных нажать кнопку "Создать" и в окне "Новый запрос" выбрать пункт "Перекрёстный запрос" 3). В окне "Создание перекрёстных таблиц" в разделе "Показать" выбрать опцию "Запросы". 4). В появившемся списке запросов выбрать запрос "Оценки" и нажать "Далее". 5). Необходимо выбрать название поля, которое будет использоваться в качестве заголовков строк. В нашем случае это поле "Фамилия". Нажать "Далее". 6). Выбрать название поля, которое будет использоваться в качестве заголовков столбцов, например, "Название". Нажать "Далее". 7). Определим, какие данные будут отображаться на пере-сечении строк и столбцов, и какие вычисления необходимо провести с этими значениями. Пусть в данном перекрёстном запросе на пересечении строк (фамилии) и столбцов (названия предметов) отображаются средние оценки, полученные студентами. 8). Необходимо выбрать поле "Оценка" и функцию "Среднее". Нажать "Далее" и сохранить запрос под именем "Оценки_перекрёстный". Открыв запрос, можно убедиться, что записи в нем отобра-жаются в виде сводной таблицы успеваемости каждого студента по всем предметам (рис 4.18).
Рис. 4.18 Окно результата перекрёстного запроса
Создание отчётов Рассмотрим создание отчёта "Преподаватели", отображающего предметы, которые ведёт преподаватель и количество часов. Источником данных для отчёта будет являться запрос, созданный на основе запроса "Дисциплины (меньше 102 часов)". Однако чтобы в отчёте отображались данные по всем предметам (без ограничений по количеству часов) необходимо выполнить следующие действия: 1). Скопировать запрос "Дисциплины (меньше 102 часов)" (Меню "Правка", "Копировать") и вставить его под именем "Дисциплины и преподаватели". 2). Открыть запрос с помощью конструктора и удалить условие отбора "<102" в поле "Количество часов". 3). Перейти к объекту "Отчёты" и нажать на кнопку "Создать". В окне "Новый отчёт" выбрать пункт "Мастер отчётов". В качестве исходных данных для отчёта необходимо выбрать запрос "Дисциплины и преподаватели". Нажать на кнопку "ОК". 4). В окне "Создание отчётов" необходимо с помощью кнопки с двойной стрелкой переместить все доступные поля запроса в раздел "Выбранные поля" и нажать "Далее" (рис. 4.19).
Рис. 4.19. Окно создания отчёта. Выбор полей
5). В следующем окне выбрать строку "Преподаватели", тем самым ФИО преподавателя в отчёте будет на верхнем уровне (рис. 4.20). Нажать "Далее".
Рис. 4.20. Окно создания отчёта. Определение уровней
6). В трёх следующих окнах, ничего не меняя, нажать "Далее". 7). Выбрать стиль оформления отчёта "Деловой" и нажать "Готово". Полученный отчёт представлен на рис. 4.21.
Рис. 4.21. Общий вид отчёта "Преподаватели"
Рассмотрим создание отчёта "Студенты", в котором отображается информация об оценках студентов по дисциплинам. Последовательность действий при создании отчета будет следующей: 1). Перейти к объекту "Отчёты" и нажать на кнопку "Создать". В окне "Новый отчёт" выбрать пункт "Мастер отчётов". В качестве исходных данных для отчёта необходимо выбрать уже созданный запрос "Оценки". Нажать "ОК". 2). Появится окно "Создание отчётов", в котором необходимо с помощью кнопки с двойной стрелкой переместить все доступные поля запроса в раздел "Выбранные поля" и нажать "Далее". 3). В следующем окне выбрать строку "Студенты", тем самым ФИО студентов в отчёте будет на верхнем уровне. Нажать "Далее". 4). В трёх следующих окнах, ничего не меняя, нажать "Далее". 5). Выбрать стиль оформления отчёта "Деловой" и нажать "Готово". Полученный отчёт "Студенты" представлен на рис. 4.22
Рис. 4.22. Общий вид отчёта "Студенты"
ВАРИАНТЫ ЗАДАНИЙ
В качестве задания на лабораторную работу необходимо создать базу данных, содержащую не менее трех таблиц, по предложенной предметной области. Главные таблицы базы данных должны содержать не менее 10 записей, а подчиненные – не менее 20. Каждая таблица должна иметь не менее четырех полей, причем, хотя в двух таблицах должны присутствовать числовые поля или поля дата/время.
Вариант 1 Создать базу данных, содержащую сведения о станках механического цеха, инструментах и рабочих, обслуживающих станки.
Вариант 2 Создать базу данных, содержащую информацию о рабочих и начальниках различных участков механосборочного цеха.
Вариант 3 Создать базу данных, содержащую сведения о транспортном оборудовании, используемом в цехах предприятия и об операторах, работающих с этим оборудованием.
Вариант 4 Создать базу данных, содержащую информацию о поставщиках комплектующих изделий, которые входят в состав узлов, собираемых на данном предприятии.
Вариант 5 Создать базу данных, содержащую сведения о продукции, производимой различными цехами предприятия и о технологическом оборудовании, задействованном в ее изготовлении.
Вариант 6 Создать базу данных, содержащую сведения о сборочных единицах, собираемых из различных компонентов рабочими сборочного цеха.
Вариант 7 Создать базу данных, содержащую информацию о предприятиях, которые закупают продукцию, производимую различными цехами данного машиностроительного завода.
Вариант 8 Создать базу данных, содержащую сведения о метрологическом оборудовании, используемом для контроля качества деталей, производимых в механическом цехе и о персонале, выполняющим контроль.
Вариант 9 Создать базу данных, содержащую информацию о физическом износе оборудования, расположенном в различных цехах предприятия, и о рабочих, осуществляющих ремонт данного оборудования.
Вариант 10 Создать базу данных, содержащую сведения о хранимой на складе предприятия продукции и складском оборудовании, используемом для работы с этой продукцией. СПИСОК РЕКОМЕНДУЕМОЙ ЛИТЕРАТУРЫ 6.1 Основная литература 6.1.1 Митрофанова А.Е. Microsoft Access 2007 учебное пособие [Электронный ресурс]: для студентов экономических специальностей А. Е. Митрофанова, И. А. Соколов; ГОУ ВПО «Кузбас. гос. техн. ун-т», Каф. вычислит. техники и информ. технологий – Кемерово, 2010.
6.2 Дополнительная литература 6.2.1. Виллариал Б. Программирование Access в примерах: пер. с англ. – М.: КУДИЦ-ОБРАЗ, 2003. – 496 с. 6.2.2. Воронова И. Е., Воронов Р. Н. Основы работы в Microsoft Access: Методические указания к лабораторным работам по дисциплине "Информатика" / сост. Воронова И. Е., Воронов Р. Н. – Кемерово: КузГТУ, 2008. – 29 с. 6.2.3. Крюкова В. В., Жемчужин В. О. Проектирование, создание и использование баз данных MS Access: Методические указания к лабораторной работе по дисциплине "Информационные системы в экономике" для студентов экономических специальностей / сост. Крюкова В. В., Жемчужин В. О. – Кемерово: КузГТУ, 2006. – 12 с. 6.2.4. Райордан Р. Основы реляционных баз данных: пер с англ. – М.: Издательско-торговый дом "Русская редакция", 2001. – 384 с.
СОДЕРЖАНИЕ
12 Не нашли, что искали? Воспользуйтесь поиском по сайту: ©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.
|
на панели инструментов. Слева от имени поля появится изображение ключа.
. После этого нажать на кнопку "Далее".
, находящемуся в панели инструментов. Откроется одноимённое окно. Правой кнопкой мыши щёлкнуть по связи и выбрать в появившемся меню "изменить связь". В открывшемся окне "Изменение связей" поставить галочку около пункта "Обеспечение целостности данных", и нажать "OK". Рядом с таблицами должны появиться "1" и "∞". После редактирования всех связей, окно "Схема данных" (рис. 4.5) можно закрыть и сохранить изменения.
