Запрос на создание таблицы

 

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

2. Перейдите в режим Конструктора для этого запроса и преобразуйте его в запрос для сохранения результатов в виде отдельной таблицы с именем Студенты без стипендии. Для этого нужно в режиме Конструктора запросов ввести команду Запрос/Создание таблицы.

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

 

Запрос на обновление записей

 

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

5. Перейдите в режим Конструктора для этого запроса и преобразуйте его в запрос на обновление данных. В строке Обновление задайте новое значение для поля Город (например, перепишите его с заглавной буквы или введите сокращенное название города и т.п.).

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

6. Создайте запрос-обновление для таблицы Студенты, выбрав всех студентов определенного курса и измените курс на другой.

 

 Запрос на удаление записей

 

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

 

Перекрестный запрос

 

8. Создайте перекрестный запрос для таблицы Студенты, включив в него поля Город, Факультет и вычисляемое поле для подсчета количества студентов (функция Count по полю Номер зачетки). Поле Факультет выберите в качестве заголовка строк, поле Город - заголовка столбцов, а вычисляемое поле с количеством студентов – как значения.

Выполните запрос и проанализируйте результат.

 

Требования к отчету

 

Отчет о проделанной работе должен содержать:

1. - название работы, ее задачи и последовательность выполнения;

2. - ответы на контрольные вопросы.

 

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

 

1. Типы запросов в Access. Как выбрать тип запроса?

2. Как создать запрос на обновление части данных в таблице?

3. Как удалить часть данных в таблице?

4. Какие проблемы могут возникнуть при использовании запросов удаления?

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

6. Как посмотреть результат запроса?

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

 

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

ТЕМА: ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ. РАЗРАБОТКА ER-МОДЕЛИ. СОЗДАНИЕ БД, РАЗРАБОТКА ЗАПРОСОВ К БД.

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

Вопросы, подлежащие изучению

 

1. Основные понятия и определения баз данных. БД реляционного типа.

2. Принципы построения БД реляционного типа. Инфологическое проектирование и модель «Сущность-связь».

3. Создание реляционной БД в среде СУБД Access.

4. Построение запросов к БД.

 

Задание

 

1. Просмотреть п. 4.4. Предварительно изучить основные понятия, определения и способы работы с БД в среде Access. Изучить на примере способы: создания БД, работы с записями БД, формирования различных запросов в БД, создания формы для ввода данных в БД.

2. Выбрать вариант задания (п. 4.5.) и выполнить следующие действия:

• изучить предметную область проектируемой БД и уточнить задание, определив данные, которые необходимо хранить в БД;
• разработать модель «Сущность-связь» - ER-модель проектируемой БД (для этого необходимо выявить необходимый набор сущностей-таблиц, определить требуемый набор атрибутов для каждой сущности, определить ключи и связи между сущностями, формализовать связи);
• создать новую БД в Access;
• создать три таблицы, соответствующие сущностям ER модели БД;
• создать схему БД, определив две связи типа «один-ко-многим» между двумя сущностями-таблицами и таблицей-связкой, а затем заполнить таблицы данными (заполнять необходимо сначала основные таблицы, а затем таблицу сущность-связку в режиме Конструктора таблиц, причем количество данных в таблицах должно обеспечивать выдачу не менее 3-5 записей по запросу задания);
• создать запросы либо с помощью Конструктора запросов либо с помощью соответствующего Мастера запросов, а затем их выполнить;
• создать форму для ввода данных в БД и с ее помощью ввести несколько записей в БД, а затем их выполнить;
• просмотреть запросы в режиме SQL.

 

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

1. Тема и название работы. Индивидуальное задание на разработку БД.

2. Формализация и уточнение задания.

3. ER модель БД.

4. Структуры таблиц БД. Содержимое таблиц БД.

5. Формы для ввода данных в БД.

6. Запросы и результаты запросов к БД (запросы представить в визуальном виде (в форме QBE) и на языке SQL).

Пример разработки ER-модели БД «Экзамены»

 

БД должна содержать список студентов и результаты экзаменов каждого студента.

 

Уточнение задания.

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

Сущности:

Опишем сущности на языке инфологического моделирования:

СТУДЕНТЫ (НомерЗачетки, Фамилия, Имя, Отчество, Факультет, Курс, Группа, ДатаРождения, Стипендия)

ЭКЗАМЕНЫ (Предмет, Оценка, Дата сдачи)

Ключи:

В сущности СТУДЕНТЫ ключевой атрибут – НомерЗачетки.

Связи:

Один студент (один экземпляр сущности СТУДЕНТЫ) может сдавать несколько экзаменов, поэтому один студент связан со многими экзаменами. Один факт сдачи одного экзамена (один экземпляр сущности ЭКЗАМЕНЫ) связан только с одним студентом. Таким образом, связь между сущностями СТУДЕНТЫ и ЭКЗАМЕНЫ «один-ко-многим».

Формализация связей:

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

ЭКЗАМЕНЫ (НомерЗачетки, Предмет, Оценка, Дата сдачи)

Таким образом, ER-модель разрабатываемой БД выглядит как на рис. 4.3-1.

 

Рис. 4.3-1

 

 

Варианты заданий

Таблица 4.1.

№ вар.	Название БД	Таблицы	Примечание	Запросы
1	Банк	БАНК (код банка, название, адрес) КРЕДИТ (код банка, номер клиента, дата выплаты кредита, размер кредита). КЛИЕНТ (номер клиента, ФИО,  телефон клиента).	Банк имеет много клиентов и один клиент может брать кредиты в разных банках.	1.Выбрать банки, которые не имеют клиентов. 2.Выбрать клиентов, которые имеют задолженность более месяца.
2	ВУЗ	ПРЕПОДАВАТЕЛЬ (номер преподавателя, ФИО, адрес и должность преподавателя, ученая степень). ЗАНЯТИЯ (номер преподавателя, код дисциплины, номер аудитории, дата) ДИСЦИПЛИНА (код дисциплины, название, количество часов).	Один преподаватель может вести несколько дисциплин и одна дисциплина может вестись несколькими преподавателями.	1.Выбрать преподавателей, которые были "без работы" в определенный период времени. 2.Выбрать дисциплину, количество часов которой наибольшее.
3	Склад	ТОВАР (код товара, название товара, количество на складе, единица измерения, стоимость единицы товара). ПОСТАВКА (код поставщика, код товара, дата поставки, количество товаров в поставке). ПОСТАВЩИК (код поставщика,  ФИО).	Один и тот же товар может доставляться несколькими поставщиками и один и тот же поставщик может доставлять несколько видов товаров.	1.Выбрать поставщиков, которые поставляют данный товар. 2.Определить самый дешевый товар и соответствую-щего поставщика.
4	Производ-ство	ИЗДЕЛИЕ (код изделия, название изделия, объем выпуска). СПЕЦИФИКАЦИЯ МАТЕРИАЛОВ (код изделия, код материала, количество материала в спецификации изделия, дата установления спецификации). МАТЕРИАЛ (код материала, название, единица измерения материала, цена за единицу).	Одно изделие может содержать много типов материалов и один и тот же материал может входить в состав разных изделий.	1.Определить изделия, в которые входит соответствующий материал. 2.Вывести список изделий, которые не производились в 2000 г.
5	Сеть магазинов	ВЛАДЕЛЕЦ (номер владельца, ФИО, адрес, телефон владельца магазина, номер регистрации, дата регистрации). ВЛАДЕНИЕ (номер владельца, номер магазина, дата регистрации). МАГАЗИН (номер магазина, название, адрес магазина, профиль).	Один и тот же магазин может иметь несколько владельцев и один и тот же владелец может иметь в собственности много магазинов.	1.Определить владельцев, имеющих собственность в соответствующем районе. 2.Вывести список профилей магазинов, которыми владеет соответствующий предприниматель
6	Автомас-терские	АВТОМЕХАНИК (номер автомеханика, ФИО). НАРЯД (номер автомобиля, номер автомеханика, название, адрес ремонтной мастерской). АВТОМОБИЛЬ (номер, марка, год выпуска, ФИО автовладельца).	Один и тот же автомобиль может обслуживаться разными автомеханиками и один и тот же автомеханик может обслуживать несколько автомобилей.	1.Выбрать механиков, которые работают с автомобилями заданной марки. 2.Определить тех владельцев автомобилей, которых обслуживает данный  механик.
7	Деканат	ГРУППА (код группы, курс, количество студентов). СЕССИЯ (код группы, код дисциплины, ФИО преподавателя, вид контроля, дата). ДИСЦИПЛИНА (код дисциплины, название дисциплины, количество часов).	Одна группа изучает несколько дисциплин и одна дисциплина может преподаваться нескольким группам.	1.Определить преподавателя, который в сессию принимает экзамены у данной группы по данной дисциплине. 2.Определить группу, у которой число студентов наибольшее.
8	Организация	РАБОТА (шифр работы, название, трудоемкость). ПОРУЧЕНИЕ (шифр работы, табельный номер сотрудника,  плановая и реальная даты окончаний). СОТРУДНИК (табельный номер, ФИО сотрудника, должность).	Одна и та же работа может выполняться несколькими сотрудниками и один и тот же сотрудник может участвовать в нескольких работах.	1.Определить те работы в 2000 г., по которым сроки выполнения превышают один месяц. 2.Определить общее количество работ, находившихся на выполнении у некоторого сотрудника.
9	Поликлиника	ПАЦИЕНТ (номер, ФИО, дата рождения, социальный статус) ЛЕЧЕНИЕ (номер пациента,номер врача, диагноз, срок потери трудоспособности, дата начала лечения). ВРАЧ (номер врача, ФИО, должность, специализация).	Один и тот же пациент может лечиться у нескольких врачей и один врач может лечить несколько пациентов.	1.Определить те случаи, когда конкретное заболевание лечилось врачом конкретной специализации. 2.Вывести имена тех врачей, которые работают исключительно с пенсионерами.
10	Спорт	ВИД СПОРТА (название вида спорта, единица измерения, мировой рекорд и его дата). РЕЗУЛЬТАТ (название вида спорта, код спортсмена, показанный результат, дата проведения, место проведения). СПОРТСМЕН (код спортсмена, ФИО спортсмена, год рождения).	Один и тот же спортсмен может заниматься разными видами спорта	1.Вывести виды спорта для каждого спортсмена. 2.Вывести список спортсменов, превысивших мировой рекорд по данному виду спорта.
11	Сельскохо-зяйственные работы	ПРЕДПРИЯТИЕ (код предприятия, наименование, дата регистрации, вид собственности, число работников, основной вид продукции). ПОСТАВКА (код продукции, код предприятия, дата поставки, объем). ПРОДУКЦИЯ (код продукции, название продукции, единица измерения, закупочная цена).	Одно и то же предприятие может выпускать разные виды продукции и одна и та же продукция может выпускаться разными предприятиями	1.Вывести все данные по конкретному предприятию. 2.Вывести список продуктов и их дату поставки, для которых закупочная цена ниже заданной.
12	Городской транспорт	ВИД ТРАНСПОРТА (название вида транспорта, средняя скорость движения, количество машин в парке, стоимость проезда). МАРШРУТ (номер пути, название вида транспорта, количество остановок на маршруте, количество машин на маршруте). ПУТЬ (номер пути, начальный пункт пути, конечный пункт, расстояние).	Один и тот же вид транспорта может на разных маршрутах использовать разные пути следования и один и тот же путь может использоваться разными видами транспорта.	1.Вывести данные по маршруту, у которого максимальное количество видов транспорта. 2.Вывести данные по  маршрутам трамваев в порядке убывания их протяженности.
13	География	СТРАНА (название страны, регион, столица, площадь территории). НАСЕЛЕНИЕ (название страны, название национальности, количество людей данной национальности). НАЦИОНАЛЬНОСТЬ (название национальности, язык, общая численность).		1.Выбрать численность населения по всем странам. 2.Определить столицу той страны, где проживает более всего представителей конкретной национальности.
14	Домоуправление	КВАРТИРА (номер квартиры, номер дома, число жильцов, площадь). ОПЛАТА (номер квартиры, номер дома, вид оплаты, сумма оплаты, дата оплаты). ВИД ОПЛАТЫ (название вида оплаты, цена за единицу площади, цена за одного жильца).	В одной квартире используются разные виды оплаты.	1.Определить сумму месячной оплаты для данной  квартиры конкретного дома. 2.Определить задолженность по оплате конкретного вида конкретной квартиры.
15	Аэропорт	САМОЛЕТ (номер самолета, тип, число мест, скорость полета). РЕЙС (номер самолета, номер маршрута, дата и время вылета, дата и время прилета, количество проданных билетов). МАРШРУТ (номер маршрута, расстояние, пункт вылета, пункт назначения).	Один самолет может летать на разных маршрутах и по одному маршруту могут летать разные самолеты.	1.Определить время полета для конкретного самолета по конкретному маршруту. 2.Выбрать тип самолета, который чаще всего летает по конкретному маршруту.
16	Компьютеры	ФИРМА (номер фирмы, название и место размещения фирмы). РЫНОЧНОЕ ПРЕДЛОЖЕНИЕ (номер фирмы, номер конфигурации, объем партии, цена партии). КОНФИГУРАЦИЯ ПК (номер конфигурации, тип процессора, тактовая частота, объем ОЗУ, объем жесткого диска, дата выпуска).	Одна конфигурация ПК может изготавливать-ся разными фирмами и одна и та же фирма может собирать ПК разной конфигурации.	1.Определить фирму, которая представляет самую новую модель на базе конкретного типа процессора. 2.Выбрать модель с наибольшей тактовой частотой, которая выпускается в конкретном городе.
17	Личные данные	СТУДЕНТ (номер зачетной книжки, ФИО студента, дата рождения студента). ОБУЧЕНИЕ (название факультета, номер зачетной книжки, номер группы, сумма стипендии студента, год зачисления). ФАКУЛЬТЕТ (название факультета, число мест на факультете).	Один и тот же студент может обучаться на разных факультетах.	1.Определить количество студентов на конкретном факультете. 2.Определить суммарную стипендию конкретной группы.
18	Зоопарк	ВИД ЖИВОТНОГО (название вида животного, суточное потребление корма, семейство, континент обитания). РАЗМЕЩЕНИЕ (название вида животного, номер помещения, количество животных) ПОМЕЩЕНИЕ (номер помещения, наличие водоема, отопления в помещении).	Один и тот же вид животного может в зоопарке находиться в разных помещениях и в одном помещении может находиться несколько видов животных.	1.Определить суточное потребление корма обитателями конкретного помещения. 2.Определить общую численность представителей конкретного вида животных.
19	Шахматы	ШАХМАТИСТ (код спортсмена, ФИО, дата рождения, страна, спортивный разряд, рейтинг). УЧАСТИЕ (код спортсмена, код турнира, стартовый номер спортсмена в данном турнире, занятое место). ТУРНИР (код турнира,  страна, город, дата проведения, уровень турнира).	Один шахматист может участвовать в разных турнирах.	1.Выбрать турнир с самым высоким рейтингом участников. 2.Выбрать тех шахматистов, которые выиграли хотя бы один турнир течение конкретного года.
20	Судоходство	КОРАБЛЬ (название корабля, водоизмещение, порт приписки, капитан). ПОСЕЩЕНИЕ ПОРТА (название корабля, название порта, дата посещения порта, дата убытия, номер причала, цель посещения). ПОРТ (название порта, страна).	Один корабль может посещать несколько портов.	1.Выбрать список кораблей, посещавших конкретный город в конкретное время. 2.Определить страны, в которые никогда не приходят корабли с целью 'туризм'.
21	Научные конференции	УЧЕНЫЙ (имя ученого, организация, страна, ученая степень). УЧАСТИЕ (имя ученого, название конференции, тип участия, тема доклада, публикация (да/нет)). КОНФЕРЕНЦИЯ (название конференций, место проведения, дата).	Один ученый может участвовать в разных конференциях и с разными типами докладов. Тип участия: доклад, сообщение, стендовый доклад, оргкомитет и т.д.	1.Выбрать названия конференций, материалы которых не опубликованы. 2.Определить количество докторов наук в каждой конференции.
22	Программ-мные продукты	ПРОГРАММНЫЙ ПРОДУКТ (название программного продукта, версия, фирма, дата выпуска, прикладная область, стоимость лицензии). ИНСТАЛЛЯЦИЯ (название программного продукта, название пользователя,  стоимость инсталляции, дата инсталляции, количество лицензий при инсталляции). ПОЛЬЗОВАТЕЛЬ (название пользователя, регион, сфера применения).	Один и тот же программный продукт может инсталлироваться разными пользователями и один пользователь может инсталлировать разные продукты. Прикладная область: делопроизводство, коммерция, бизнес и т.д. Тип программного продукта: ОС, сервер приложений, СУБД, Web-сервер и т.п.	1.Вывести данные по прикладным областям. 2.Определить затраты на инсталляции программного обеспечения, сделанные конкретным пользователем в конкретном году.
23	Операцион-ная система	ПРОЦЕСС (идентификатор процесса, идентификатор владельца процесса, название процесса, приоритет, класс, идентификатор владельца). ПЛАНИРОВАНИЕ (идентификатор процесса, название ресурса,   запрошено/выделено). РЕСУРС (название ресурса, количество, цена за единицу).	Один и тот же процесс может задействовать много разных ресурсов и один и тот же ресурс может быть задействован разными процессами. Классы процессов : критический, серверный, нормальный.	1.Вывести данные по ресурсам для каждого процесса. 2.Определить владельца процесса, у которого "самые большие аппетиты" в ценовом выражении.
24	Добыча полезных ископаемых	ПОЛЕЗНОЕ ИСКОПАЕМОЕ (название полезного ископаемого, единица измерения, годовая потребность, цена за единицу). МЕСТОРОЖДЕНИЕ (название полезного ископаемого, название пункта,  название месторождения, запасы, способ разработки, годовая добыча). ПУНКТ (название пункта, пропускная способность путей сообщения данного пункта).	Из одного и того же пункта вывоза могут вывозиться разные полезные ископаемые, одно и то же полезное ископаемое может вывозиться с разных пунктов вывоза. Конкретное месторождение может использовать конкретный пункт вывоза конкретного полезного ископаемого.	1.Определить, какие ископаемые добываются в конкретном месторождении. 2.Выбрать список месторождений, в которых добывается конкретное ископаемое заданным способом разработки.
25	Автотранспортное предприятие	АВТОМОБИЛЬ (номер автомобиля, марка автомобиля, его техническое состояние, пробег, расход топлива). РЕЙС (номер автомобиля, табельный номер водителя,  дата выезда, дата прибытия, расстояние, расход горючего). ВОДИТЕЛЬ (табельный номер водителя, ФИО, дата рождения, оклад).	Один и тот же автомобиль может использоваться разными водителями и один водитель может использовать разные автомобили.	1.Выбрать автомобиль с наименьшим расходом горючего. 2.Вывести данные по всем рейсам, в которых участвовал данный автомобиль.
26	Театр	АКТЕР (номер актера, ФИО, звание, амплуа, пол). ПОСТАНОВКА НА РОЛЬ (номер актера, название роли, дата назначения на роль, тип роли, режиссер, номер состава);  РОЛЬ (название роли, тип роли, название пьесы).	На одну и ту же роль могут назначаться разные актеры. Амплуа: герой-любовник, инженю, злодей т.д., тип роли: главная, вторая, эпизод.	1.Определить список актеров конкретного режиссера. 2.Выбрать пьесы, в которых исполнителем  главной роли является конкретный актер.
27	Справочная аптек	ЛЕКАРСТВО (название лекарства, показания к использованию, противопоказания, производитель). НАЛИЧИЕ (название лекарства, номер аптеки,  тип, цена, количество, срок годности). АПТЕКА (номер аптеки, специализация аптеки, район, телефон).	Тип: таблетки, микстура, мазь и т.д.	1.Вывести ассортимент предлагаемых лекарств жителей конкретного района. 2.Определить, в каких аптеках дешевле всего конкретное лекарство.
28	Кулинария	БЛЮДО (название блюда, категория, рецепт, вес порции). СОСТАВ (название блюда, название продукта, количество, обработка). ПРОДУКТ (название продукта, категория, калорийность, цена за ед., ед. измерения).	Одно блюдо состоит из разных продуктов и один и тот же продукт может входить в состав разных блюд. Категория блюда: первое, гарнир, и т.д., продукта: мясо, мучное и т.д.	1.Определить, для каких блюд продукты данной категории предварительно подвергаются заданной обработке. 2.Определить данные по соответствую-щему блюду.
29	Изучение студентами дисциплин по выбору	СТУДЕНТ (номер зачетной книжки, ФИО студента, адрес, телефон). ВЫБОР (номер зачетной книжки, наименование дисциплины, деканат, адрес деканата). ДИСЦИПЛИНА (наименование дисциплины, количество лекционных часов, семинарских и лабораторных занятий, отметка о сдаче  дисциплины).	Каждый студент должен изучить несколько дисциплин по выбору и каждая дисциплина может изучаться несколькими студентами.	1.Получить список студентов, изучающих заданную дисциплину и сдавших ее. 2.Получить список дисциплин, изученных заданным студентом и объем дисциплины.
30	Отдел кадров	СОТРУДНИК (номер сотрудника, ФИО, образование, телефон, дата рождения). ЗАЧИСЛЕНИЕ (номер сотрудника, наименование подразделения, должность, дата зачисления, оклад). ПОДРАЗДЕЛЕНИЕ (наименование подразделения, количество штатных единиц, фонд заработной платы).	Один и тот же сотрудник может числиться в разных подразделе-ниях	1.Выбрать сотрудников, которые занимают инженерные должности, не имея высшего образования. 2.Определить, в каких подразделениях работает данный сотрудник.

 

 

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