Задание База данных «Абитуриенты» MS Access (1 вариант).
База данных должна содержать следующие элементы:
Таблица «Специальности»: шифр специальности; специальность.
Таблица «Анкета»: номер анкеты; шифр специальности; Ф.И.О.; дата рождения; оконченное среднее учебное заведение (наименование, номер); дата окончания; знак отличия (золотая (серебряная) медаль или красный диплом); город; адрес; телефон.
Таблица «Дисциплины»: шифр дисциплины; наименование дисциплины.
Таблица «Результаты экзаменов»: номер анкеты; шифр дисциплины; оценка.
Поле со списком – таблица «Анкета», поле Знак отличия.
Поле подстановки – Шифр специальности в таблице «Анкета», источник таблица «Специальности».
Запросы на выборку:
1. Абитуриенты, окончившие школу с золотой медалью.
2. Абитуриенты, поступающие на специальность «Электроснабжение» и проживающие в Новокузнецке и Сургуте.
3. Абитуриенты, окончившие школу с золотой медалью и сдавшие
экзамен по математике на оценку «5».
Запрос с вычисляемым полем
Возраст абитуриента на текущую дату (таблица «Анкета»).
Групповой запрос:
Количество анкет абитуриентов по каждой специальности
Запрос с параметром:
Абитуриенты, поступающие на специальность N
Многотабличный отчет по анкетам, сгруппированный по специальности, сортировка по ФИО студента.
Проанализируем предметную область решаемой задачи и разработаем логическую структуру базы данных. Совокупность таблиц представлена на следующем рисунке.
Данные в таблицах не указаны..
На основе полученной совокупности таблиц составим логическую структурную схему проектируемой базы данных «Абитуриенты».
Структура таблиц базы данных.
Таблицы «Специальности» и «Дисциплины» – это справочники-классификаторы, которые содержат информацию соответственно о специальностях и дисциплинах. Таблица «Специальности» является главной по отношению к таблице «Анкета» (поле связи «Шифр специальности»). Таблица «Дисциплины» является главной по отношению к таблице «Результаты экзаменов» (поле связи «Шифр дисциплины»).
Таблица «Анкета» - это тоже справочник, в котором хранятся данные абитуриентов. Она главная по отношению к таблице «Результаты экзаменов» (поле «Номер анкеты») и подчиненная «Специальностям» («Шифр специальности»).
Таблица «Результаты экзаменов» содержит сведения об оценках абитуриентов по различным дисциплинам, в этой базе данных является основной, то есть в ней должно быть не менее 20 записей (во второй части работы). Ключевого поля в этой таблице можно не делать, хотя по правилам оно-таки должно быть. Если оно все-таки нужно, то можно сделать составной ключ («Номер анкеты» + «Шифр дисциплины») или ввести еще одно поле, например, «Код экзамена»).
Ключевые поля в логической схеме выделены курсивом.
Дальше описываем каждое поле всех таблиц, показываем ключевые поля и поля связи в подчиненной таблице.
Затем нужно привести таблицы с типами данных каждого поля, размерами, обязательностью, полями подстановки, условиями на значение и сообщениями об ошибках.
Составляем таблицу межтабличных связей.
Открываем программу MS Access и создаем базу данных.
Создаем таблицы «Специальности» и «Дисциплины».
Затем делаем таблицу «Анкета». В поле «Шифр специальности» делаем столбец подстановки, источник – таблица «Специальности». Выбрать для показа лучше столбец «Название», удобнее работать с текстовыми данными, чем запоминать все шифры. В поле «Знак отличия» делаем также столбец подстановки, но источником будет набор фиксированных значений – золотая медаль, серебряная медаль, красный диплом. В полях, где тип данных – дата/время, формат поля лучше выбирать «Краткий формат даты».
Для поля «Телефон» лучше использовать такое свойство поля, как маска ввода. Вот, например, я для разных вариантов данных создал несколько масок, с ними удобней работать.
В частности, для сотового телефона, в качестве маски будет выражение:
После создания таблиц идем в схему данных и настраиваем связи. Кликаем на каждую связь (они там уже должны быть, поскольку мы делали столбцы подстановки, Access уже сам связал таблицы) и ставим галочки на Обеспечение целостности данных, Каскадное удаление и Каскадное обновление. Получается вот такая схема:
Вносим нужно количество записей, дописываем отчет.
Переходим ко второй части работы. Дополняем записи и начинаем делать запросы.
Первый запрос – абитуриенты, закончившие школу с золотой медалью.
Затем идем в конструктор запросов и выбираем таблицу «Анкета». Выбираем все поля, кроме шифра специальности, и ставим условия отбора:
Или в режиме SQL это будет:
Выбираем таблицы «Анкета» и «Специальности», в условии отбора ставим:
Третий запрос: Абитуриенты, окончившие школу с золотой медалью и сдавшие экзамен по математике на оценку «5».
Вычисляемый запрос: Возраст абитуриента на текущую дату.
Выбираем таблицу «Анкета». Поля – ФИО, дата рождения.
Сохраняем запрос. Затем делаем вычисляемое поле – правая кнопка мыши, выбираем Построить, затем
Жмем ОК. В заголовке поля вместо Выражение 1 пишем Возраст. В данном случае возраст мы считаем как разницу в годах между двумя датами: сегодняшней (текущей) и датой рождения. Текущая дата вычисляется с помощью функции Date (текущая системная дата) или Now (текущая дата и время). Разница между датами вычисляется с помощью функции DateDiff. Ее синтаксис:
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
interval – строковое выражение, обозначает единицы времени, в которых нужно вычислить разницу дат. Например,
yyyy – год.
m - месяц
d – день.
date1, date 2 – даты, между которыми нужно найти разницу.
Два других аргумента функции являются необязательными. Это первый день недели и первая неделя года. В нашем случае они неважны.
Появилось вычисляемое поле с результатом.
Запрос с групповыми операциями – Количество анкет абитуриентов по каждой специальности.
В конструкторе запросов выбираем две таблицы.
Затем нажимаем кнопку Итоги. Под всеми полями в строке «Групповая операция» появилось слово «Группировка». У поля «Номер анкеты» меняем его на «Count» (количество).
Вот как это все выглядит
Запрос с параметром. Нужно вывести ФИО абитуриентов, поступающих на специальность N.
В конструкторе выбираем две таблицы – Анкета и Специальности. Поля – ФИО и Название специальности.
Для установки параметра нажмем кнопку Параметры и введем в поле Параметр слово «Специальность». В условии отбора пишем выражение:
Like ([Специальность]+"*")
Это значит, что будут отбираться те записи, которые начинаются с символов, указанных в параметре плюс любое количество любых символов.
Вот что получается
И, наконец, отчет по анкетам, сгруппированный по специальности, сортировка по ФИО студента.
Описание, как создать многотабличный отчет, есть в нулевом варианте. Я сейчас приведу только запрос и сам отчет.
Многотабличный запрос
Делаем дальше по описанию. Группировка по специальностям, сортировку выбираем по ФИО. Поскольку никаких числовых данных у нас нет, то итогов делать не надо. Хотя можно было бы посчитать количество анкет, например. На каждую специальность и всего. Это можно сделать, нажав кнопку Итоги или в самом конструкторе отчетов.
Оформляем, как нравится. Затем сохраняем, изменяем оформление в конструкторе, настраиваем ширину полей, заголовки и прочее.
Затем делаем простую форму для внесения записей в таблицу «Анкеты» - это выполняется простым нажатием кнопки Разделенная форма или Форма, или несколько элементов.
И последняя форма – основная, на ней будут расположены кнопки для запуска созданных объектов базы данных. Создание этой формы было описано в нулевом варианте.
База данных должна содержать следующие элементы:
Таблица «Специальности»: шифр специальности; специальность.
Таблица «Анкета»: номер анкеты; шифр специальности; Ф.И.О.; дата рождения; оконченное среднее учебное заведение (наименование, номер); дата окончания; знак отличия (золотая (серебряная) медаль или красный диплом); город; адрес; телефон.
Таблица «Дисциплины»: шифр дисциплины; наименование дисциплины.
Таблица «Результаты экзаменов»: номер анкеты; шифр дисциплины; оценка.
Поле со списком – таблица «Анкета», поле Знак отличия.
Поле подстановки – Шифр специальности в таблице «Анкета», источник таблица «Специальности».
Запросы на выборку:
1. Абитуриенты, окончившие школу с золотой медалью.
2. Абитуриенты, поступающие на специальность «Электроснабжение» и проживающие в Новокузнецке и Сургуте.
3. Абитуриенты, окончившие школу с золотой медалью и сдавшие
экзамен по математике на оценку «5».
Запрос с вычисляемым полем
Возраст абитуриента на текущую дату (таблица «Анкета»).
Групповой запрос:
Количество анкет абитуриентов по каждой специальности
Запрос с параметром:
Абитуриенты, поступающие на специальность N
Многотабличный отчет по анкетам, сгруппированный по специальности, сортировка по ФИО студента.
Проанализируем предметную область решаемой задачи и разработаем логическую структуру базы данных. Совокупность таблиц представлена на следующем рисунке.
Данные в таблицах не указаны..
На основе полученной совокупности таблиц составим логическую структурную схему проектируемой базы данных «Абитуриенты».
Структура таблиц базы данных.
Таблицы «Специальности» и «Дисциплины» – это справочники-классификаторы, которые содержат информацию соответственно о специальностях и дисциплинах. Таблица «Специальности» является главной по отношению к таблице «Анкета» (поле связи «Шифр специальности»). Таблица «Дисциплины» является главной по отношению к таблице «Результаты экзаменов» (поле связи «Шифр дисциплины»).
Таблица «Анкета» - это тоже справочник, в котором хранятся данные абитуриентов. Она главная по отношению к таблице «Результаты экзаменов» (поле «Номер анкеты») и подчиненная «Специальностям» («Шифр специальности»).
Таблица «Результаты экзаменов» содержит сведения об оценках абитуриентов по различным дисциплинам, в этой базе данных является основной, то есть в ней должно быть не менее 20 записей (во второй части работы). Ключевого поля в этой таблице можно не делать, хотя по правилам оно-таки должно быть. Если оно все-таки нужно, то можно сделать составной ключ («Номер анкеты» + «Шифр дисциплины») или ввести еще одно поле, например, «Код экзамена»).
Ключевые поля в логической схеме выделены курсивом.
Дальше описываем каждое поле всех таблиц, показываем ключевые поля и поля связи в подчиненной таблице.
Затем нужно привести таблицы с типами данных каждого поля, размерами, обязательностью, полями подстановки, условиями на значение и сообщениями об ошибках.
Составляем таблицу межтабличных связей.
Открываем программу MS Access и создаем базу данных.
Создаем таблицы «Специальности» и «Дисциплины».
Затем делаем таблицу «Анкета». В поле «Шифр специальности» делаем столбец подстановки, источник – таблица «Специальности». Выбрать для показа лучше столбец «Название», удобнее работать с текстовыми данными, чем запоминать все шифры. В поле «Знак отличия» делаем также столбец подстановки, но источником будет набор фиксированных значений – золотая медаль, серебряная медаль, красный диплом. В полях, где тип данных – дата/время, формат поля лучше выбирать «Краткий формат даты».
Для поля «Телефон» лучше использовать такое свойство поля, как маска ввода. Вот, например, я для разных вариантов данных создал несколько масок, с ними удобней работать.
В частности, для сотового телефона, в качестве маски будет выражение:
"+7("000")"000"-"00"-"00;0;В таблице «Результаты экзаменов» делаем поля подстановки «Номер анкеты» и «Шифр дисциплины» из соответствующих таблиц.
После создания таблиц идем в схему данных и настраиваем связи. Кликаем на каждую связь (они там уже должны быть, поскольку мы делали столбцы подстановки, Access уже сам связал таблицы) и ставим галочки на Обеспечение целостности данных, Каскадное удаление и Каскадное обновление. Получается вот такая схема:
Вносим нужно количество записей, дописываем отчет.
Переходим ко второй части работы. Дополняем записи и начинаем делать запросы.
Первый запрос – абитуриенты, закончившие школу с золотой медалью.
Затем идем в конструктор запросов и выбираем таблицу «Анкета». Выбираем все поля, кроме шифра специальности, и ставим условия отбора:
Или в режиме SQL это будет:
SELECT Анкета.[Номер анкеты], Анкета.ФИО, Анкета.[Дата рождения], Анкета.СУЗ, Анкета.[Дата окончания], Анкета.[Знак отличия], Анкета.Город, Анкета.Адрес, Анкета.ТелефонВторой запрос: Абитуриенты, поступающие на специальность «Электроснабжение» и проживающие в Новокузнецке и Сургуте.
FROM Анкета
WHERE (((Анкета.СУЗ) Like ("*Школа*")) AND ((Анкета.[Знак отличия])="Золотая медаль"));
Выбираем таблицы «Анкета» и «Специальности», в условии отбора ставим:
Третий запрос: Абитуриенты, окончившие школу с золотой медалью и сдавшие экзамен по математике на оценку «5».
Вычисляемый запрос: Возраст абитуриента на текущую дату.
Выбираем таблицу «Анкета». Поля – ФИО, дата рождения.
Сохраняем запрос. Затем делаем вычисляемое поле – правая кнопка мыши, выбираем Построить, затем
Жмем ОК. В заголовке поля вместо Выражение 1 пишем Возраст. В данном случае возраст мы считаем как разницу в годах между двумя датами: сегодняшней (текущей) и датой рождения. Текущая дата вычисляется с помощью функции Date (текущая системная дата) или Now (текущая дата и время). Разница между датами вычисляется с помощью функции DateDiff. Ее синтаксис:
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
interval – строковое выражение, обозначает единицы времени, в которых нужно вычислить разницу дат. Например,
yyyy – год.
m - месяц
d – день.
date1, date 2 – даты, между которыми нужно найти разницу.
Два других аргумента функции являются необязательными. Это первый день недели и первая неделя года. В нашем случае они неважны.
Появилось вычисляемое поле с результатом.
Запрос с групповыми операциями – Количество анкет абитуриентов по каждой специальности.
В конструкторе запросов выбираем две таблицы.
Затем нажимаем кнопку Итоги. Под всеми полями в строке «Групповая операция» появилось слово «Группировка». У поля «Номер анкеты» меняем его на «Count» (количество).
Вот как это все выглядит
Запрос с параметром. Нужно вывести ФИО абитуриентов, поступающих на специальность N.
В конструкторе выбираем две таблицы – Анкета и Специальности. Поля – ФИО и Название специальности.
Для установки параметра нажмем кнопку Параметры и введем в поле Параметр слово «Специальность». В условии отбора пишем выражение:
Like ([Специальность]+"*")
Это значит, что будут отбираться те записи, которые начинаются с символов, указанных в параметре плюс любое количество любых символов.
Вот что получается
И, наконец, отчет по анкетам, сгруппированный по специальности, сортировка по ФИО студента.
Описание, как создать многотабличный отчет, есть в нулевом варианте. Я сейчас приведу только запрос и сам отчет.
Многотабличный запрос
Делаем дальше по описанию. Группировка по специальностям, сортировку выбираем по ФИО. Поскольку никаких числовых данных у нас нет, то итогов делать не надо. Хотя можно было бы посчитать количество анкет, например. На каждую специальность и всего. Это можно сделать, нажав кнопку Итоги или в самом конструкторе отчетов.
Оформляем, как нравится. Затем сохраняем, изменяем оформление в конструкторе, настраиваем ширину полей, заголовки и прочее.
Затем делаем простую форму для внесения записей в таблицу «Анкеты» - это выполняется простым нажатием кнопки Разделенная форма или Форма, или несколько элементов.
И последняя форма – основная, на ней будут расположены кнопки для запуска созданных объектов базы данных. Создание этой формы было описано в нулевом варианте.