Отношения, представления, выборки данных

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

Задания

Вариант 1. Фрагмент БД недвижимости.

Задание №1. Создание и заполнение отношений.

1. Отношение "Владельцы" (идентификатор, "Имя", "Телефон").
2. Отношение "Станции метро" (идентификатор, "Название").
3. Отношение "Квартиры" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Идентификатор	N			первичный ключ
Номер владельца	N			внешний ключ
Общая площадь	N			обязательное поле
Жилая площадь	N			обязательное поле
Количество комнат	N			обязательное поле
Размер кухни	N			обязательное поле
Этаж	N			обязательное поле
Всего этажей в доме	N
Ближайшая станция метро	N			внешний ключ
Цена	N			обязательное поле
Адрес	C			обязательное поле
Дата поступления	D			информации в БД

Задание №2. Выборка данных.

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

• трехкомнатных квартир, расположенных не на первом и последнем этажах, информация о которых поступила за последний месяц;
• владельцев и их квартир (идентификатор квартиры и адрес);
• квартир общей площадью не менее 80 м² не дороже 60000, расположенных вблизи станции метро "Китай–город".

Задание №3. Работа с представлениями.

Создать представления:

1. "Двухкомнатные квартиры" (все поля отношения "Квартиры").
2. "Владение квартирами" (имя владельца, количество квартир, общая площадь этих квартир).
3. "Владельцы однокомнатных квартир" (имя, телефон, площадь квартиры).

Вариант 2. Фрагмент БД деканата (преподаватели).

Задание №1. Создание и заполнение отношений.

1. Отношение "Дисциплины" ("Шифр дисциплины", "Название").
2. Отношение "Преподаватели" (идентификатор, "ФИО", "Кафедра").
3. Отношение "Сессия" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Факультет	C			обязательное поле
Курс	N			обязательное поле
Шифр дисциплины	N			ключевая комбинация полей
Группа	С
Экзаменатор	N			идентификатор, внешний ключ
Аудитория	N
Дата	D
Время	T

Задание №2. Выборка данных.

Проверить уникальность комбинации "Аудитория", "Дата", "Время". Определить дисциплины, по которым нет экзаменов.

Создать расписание экзаменов:

• для преподавателей двух кафедр;
• для одной произвольной группы.

Задание №3. Работа с представлениями.

Создать представления:

1. "Количество экзаменов" (группа, количество экзаменов).
2. "Количество экзаменов для факультетов АВТ и ФПМ" (название дисциплины, количество экзаменов на АВТ, количество экзаменов на ФПМ).
3. "Преподаватели кафедры ВСиС" (ФИО преподавателя).

Примечания:

1. Поля основного отношения с типами и ограничениями целостности приведены в таблице.
2. Для остальных отношений в скобках перечислены обязательные поля.
3. Типы полей обозначаются следующим образом: N – числовое, C – символьное, D – дата, T – время, L – логическое (имеющее значения.T. – истина и.F. – ложь).
4. Для некоторых представлений также в скобках перечислены поля.

Вариант 3. Фрагмент БД деканата (студенты).

Задание №1. Создание и заполнение отношений.

1. Отношение "Дисциплины" ("Шифр", "Название дисциплины").
2. Отношение "Студенты" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Номер зачётной книжки	N			первичный ключ
ФИО	C			обязательное поле
Год поступления	N			обязательное поле
Группа	С			обязательное поле
Курс	N			обязательное поле
Средний балл	N
Форма обучения	С			по умолчанию – дневная
Академический отпуск	L			по умолчанию –.F.

1. Отношение "Сессия" ("Группа", "Шифр дисциплины" (внешний ключ), "Дата", "Время", "Аудитория").

Задание №2. Выборка данных.

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

Создать упорядоченные списки:

• студентов групп ‘С’ второго курса (без находящихся в академическом отпуске);
• студентов, имеющих наибольший средний бал в своей группе.

Задание №3. Работа с представлениями.

Создать представления:

1. "Списки групп" для дневной формы обучения (группа, фамилия, номер зачётной книжки, средний балл).
2. "Успеваемость" (группа, средний балл по группе, минимальный средний балл, максимальный средний балл, разница (max-min)).
3. "Количество экзаменов" (группа, количество экзаменов).

Вариант 4. Фрагмент БД института (сотрудники).

Задание №1. Создание и заполнение отношений.

1. Отношение "Отделы" (идентификатор, "Название отдела").
2. Отношение "Научные направления" (идентификатор, "Название научной специальности").
3. Отношение "Сотрудники" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Табельный номер	N			первичный ключ
ФИО	C			обязательное поле
Должность	C			обязательное поле
Ученое звание	С
Шифр специальности	N			специальность по диплому
Научная специализация	С			внешний ключ
Номер отдела	С			внешний ключ

Задание №2. Выборка данных.

Создать упорядоченные списки:

• научных кадров по каждой специальности (с указанием научного звания);
• научных направлений, по которым нет специалистов.

Определить, в каком отделе (отделах) работает больше всего сотрудников.

Задание №3. Работа с представлениями.

Создать представления:

1. "Сотрудники отдела “Информационные технологии”" (все поля отношения "Сотрудники").
2. "Качественный состав научных сотрудников отделов" (отдел, количество профессоров, количество доцентов).
3. "Научные кадры" (отдел, научная специализация, количество сотрудников).

Вариант 5. Фрагмент БД библиотеки (журнальные публикации).

Задание №1. Создание и заполнение отношений.

1. Отношение "Журналы" (идентификатор, "Название", "Издатель").
2. Отношение "Рубрикаторы" ("Шифр", "Название рубрики").
3. Отношение "Публикации (статьи)" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Индекс журнала	N			ключевая комбинация полей
Автор(ы)	C
Название	C
Год выпуска	N			обязательное поле
Номер выпуска	N			обязательное поле
Шифр рубрики	C			внешний ключ
Страницы	С
Примечание	C			раздел журнала

Задание №2. Выборка данных.

Создать упорядоченные списки:

• публикаций за последние 3 года (по журналам и годам);
• публикаций по рубрикам;
• публикаций по разделам "Базы данных" и СУБД;
• рубрик, по которым нет публикаций.

Задание №3. Работа с представлениями.

Создать представления:

1. "Публикации за текущий год" (все поля отношения "Публикации").
2. "Авторы, публиковавшиеся более одного раза" (все поля отношения "Публикации").
3. "Состав по рубрикам" (рубрика, количество публикаций всего, количество публикаций за последние три года).

Вариант 6. Фрагмент БД отдела кадров.

Задание №1. Создание и заполнение отношений.

1. Отношение "Отделы" ("Номер отдела", "Название отдела").
2. Отношение "Сотрудники" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Табельный номер	N			первичный ключ
ФИО	C			обязательное поле
Пол	C			по умолчанию – мужской
Дата рождения	D
Образование	C			высшее, среднее, начальное
Номер отдела	N			внешний ключ
Должность	C			обязательное поле

1. Отношение "Дети" (внешний ключ к отношению "Сотрудники", "Имя", "Дата рождения").

Задание №2. Выборка данных.

Создать упорядоченные списки:

• сотрудников с детьми от 3 до 12 лет включительно;
• отделов, в которых нет сотрудников.

Посчитать количество сотрудников с разными уровнями образования.

Задание №3. Работа с представлениями.

Создать представления:

1. "Отделы и сотрудники" (поля обоих отношений без повторов).
2. "Бездетные сотрудники" (все поля отношения "Сотрудники").
3. "Образовательный уровень сотрудников" (уровень образования, количество мужчин, количество женщин).

Вариант 7. Фрагмент БД библиотеки (книги).

Задание №1. Создание и заполнение отношений.

1. Отношение "Издательства" (идентификатор, "Название", "Адрес").
2. Отношение "Рубрикаторы" ("Шифр рубрики", "Название рубрики").
3. Отношение "Каталог книг" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Рубрикатор	C			ключевая комбинация полей
Шифр	C
Автор(ы)	C			обязательное поле
Название	C			обязательное поле
Место издания	C
Шифр издательства	C			внешний ключ
Год издания	N
Количество страниц	N
Примечание	C			по умолчанию – учебник

Задание №2. Выборка данных.

Посчитать, сколько книг не имеют комментариев. Создать списки:

• поступлений за последние 3 года (по рубрикам, авторам, годам);
• рубрик, по которым нет книг;
• автор – название издательства, в котором автор издавался.

Задание №3. Работа с представлениями.

Создать представления:

1. "Первая – последняя публикации" (автор, год выхода первой публикации, год выхода последней публикации).
2. "Учебники и учебные пособия" (все поля отношения "Каталог книг").
3. "Состав по рубрикам" (рубрика, количество книг до 1980 года издания, количество книг после 1980 года издания).

Вариант 8. Фрагмент БД больницы.

Задание №1. Создание и заполнение отношений.

1. Отношение "Больничные палаты" ("Номер палаты", "Количество коек", "Отделение").
2. Отношение "Врачи" (идентификатор, "ФИО врача", "Отделение", "Специализация").
3. Отношение "Пациенты" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Регистрационный №	N			первичный ключ
ФИО	C			обязательное поле
Пол	C			по умолчанию – женский
Номер полиса	C
Дата поступления	D			обязательное поле
Номер палаты	N			внешний ключ
Лечащий врач	N			внешний ключ
Диагноз	C
Дата выписки	D			заполняется при выписке пациента

Задание №2. Выборка данных.

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

Создать упорядоченные списки:

• пациентов по палатам с указанием ФИО врача и диагноза;
• количества занятых мест в каждой палате.

Задание №3. Работа с представлениями.

Создать представления:

1. "Специализация больницы" (диагноз, количество пациентов-мужчин, количество пациентов-женщин).
2. "Текущие пациенты отделения “Хирургия”" (все поля отношения "Пациенты").
3. "Общее количество мест" (отделение, количество мест).

Вариант 9. Фрагмент БД торгового предприятия.

Задание№1. Создание и заполнение отношений.

1. Отношение "Поставщики" (идентификатор, "Название", "Адрес").
2. Отношение "Товары" (идентификатор, "Название", "Категория товара").
3. Отношение "Поставки" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Шифр поставки	N			первичный ключ
Индекс поставщика	C			внешний ключ
Индекс товара	C			внешний ключ
Единица измерения	C			'кг', 'шт', 'уп' (по умолчанию – 'кг')
Количество товара	N			обязательное поле
Цена единицы товара	N
Дата поставки	D

Задание №2. Выборка данных.

Создать список поставщиков, от которых нет поставок.

Создать упорядоченные списки:

• поставщиков, от которых есть поставки, с адресами;
• названий товаров, которые есть в наличии:
• товаров, чей остаток меньше 100 кг.

Задание №3. Работа с представлениями.

Создать представления:

1. "Товары на складе" (соединение отношений "Поставки", "Товары" и "Поставщики").
2. "Поставщики и категории товаров" (поставщик, категория).
3. "Общая стоимость товаров" (название товара, общая стоимость).

Вариант 10. Фрагмент БД складского предприятия.

Задание№1. Создание и заполнение отношений.

1. Отношение "Поставщики" (идентификатор, "Название ", "Телефон").
2. Отношение "Поставки" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Шифр поставки	N			ключевая комбинация полей
Индекс товара	C
Индекс поставщика	C			внешний ключ
Единица измерения	C			значения 'шт', 'кг', 'уп', 'кор' (по умолчанию – 'шт')
Вес единицы товара	N
Количество товара	N			обязательное поле
Номер склада	N
Номер линии	С
Дата поставки	D			обязательное поле

1. Отношение "Заказы" (первичный ключ (идентификатор, "Индекс товара"), "Единица измерения", "Количество").

Задание №2. Выборка данных.

Создать упорядоченный список товаров с наименованиями и телефонами поставщиков. Для конкретного заказа выдать список местоположения заказанных товаров на складах (с учётом количества товара в заказе и на складе). Посчитать общий вес конкретного заказа.

Задание №3. Работа с представлениями.

Создать представления:

1. "Товары на складе" (соединение отношений "Поставки" и "Поставщики").
2. "Недостаток товаров": для заказа, который не может быть выполнен из-за недостатка товара, выдать список пунктов этого заказа, которые не могут быть выполнены.
3. "Поставки и поставщики" (поставщик, индекс поставки).

Вариант 11. Фрагмент БД адвоката.

Задание №1. Создание и заполнение отношений.

1. Отношение "Статьи УК" (идентификатор, "Статья", "Минимальный срок", "Максимальный срок").
2. Отношение "Клиенты" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Номер дела	N			ключевая комбинация полей
ФИО	C
Дата рождения	D
Дата начала дела	D			обязательное поле
Номер камеры	N
Размер гонорара	N
Срок	N			по приговору
Дата окончания дела	D

1. Отношение "Статьи" (внешний ключ ("Номер дела", "ФИО"), внешний ключ "Номер статьи").

Задание №2. Выборка данных.

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

Создать упорядоченные (по дате начала дела) списки:

• подзащитных (по делам);
• клиентов, которых адвокат защищал по разным делам;
• подзащитных, обвиняемых по статьям, максимальный срок по которым не меньше 10 лет.

Задание №3. Работа с представлениями.

Создать представления:

1. "Эффективность защиты" (дело, ФИО, максимальный срок минус срок по приговору, срок по приговору минус минимальный срок).
2. "Текущие подзащитные" (все поля отношения "Клиенты").
3. "Количество несовершеннолетних клиентов по статьям".

Вариант 12. Фрагмент БД гостиницы.

Задание №1. Создание и заполнение отношений.

1. Отношение "Стоимость мест" ("Класс", "Стоимость места").
2. Отношение "Номера" (идентификатор, "Класс" (внешний ключ), "Количество мест", "Количество забронированных мест").
3. Отношение "Постояльцы" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Номер паспорта	С			первичный ключ
Занимаемый номер	N			внешний ключ
ФИО	C			обязательное поле
Пол	С			по умолчанию – мужской
Организация	С
Дата вселения	D			обязательное поле
Дата выселения	D			заполняется при выезде постояльца

Задание №2. Выборка данных.

Создать запрос на выдачу счета на оплату номера.

Создать упорядоченные списки:

• свободных номеров (по классу и по номерам) с указанием общего количества мест в номере;
• полностью забронированных номеров.

Задание №3. Работа с представлениями.

Создать представления:

1. "Постояльцы, проживающие в гостинице в данное время" (все поля отношения "Постояльцы").
2. "Полностью занятые номера" (номер, количество мест).
3. "Номера со свободными местами" (пол, номер, количество свободных мест).

Вариант 13. Фрагмент БД организации.

Задание №1. Создание и заполнение отношений.

1. Отношение "Отделы" (идентификатор, "Название отдела").
2. Отношение "Проекты" (идентификатор, "Название", "Дата начала", "Дата завершения").
3. Отношение "Сотрудники" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Идентификатор	N			ключевое поле
ФИО	C			обязательное поле
Должность	C			обязательное поле
Пол	C			по умолчанию – 'ж'
Оклад	N
Номер отдела	N			внешний ключ
Номер проекта	N			внешний ключ
Руководитель проекта	L			по умолчанию –.F.

Задание №2. Выборка данных.

Создать упорядоченные списки:

• сотрудников по отделам;
• неоконченных проектов (по дате начала) с фамилиями руководителей.

Проверить, что дата начала проекта меньше, чем дата его завершения. Проверить, что у каждого проекта только один начальник.

Задание №3. Работа с представлениями.

Создать представления:

1. "Сотрудники, работающие в отделе №2" (все поля отношения "Сотрудники").
2. "Заработная плата" (ФИО, оклад–13%). Руководителю проекта полагается надбавка к окладу (20%).
3. "Проекты по отделам" (название проекта, ФИО руководителя, название отдела (в котором работает руководитель), количество сотрудников (работающих над данным проектом)).

Вариант 14. Фрагмент БД школы (учителя).

Задание №1. Создание и заполнение отношений.

1. Отношение "Дисциплины" ("Шифр дисциплины", "Название").
2. Отношение "Учителя" (идентификатор, "ФИО", "Предмет").
3. Отношение "Сессия" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Направление	С			обязательное поле
Год обучения	N			обязательное поле
Шифр дисциплины	N			ключевая комбинация полей
Буква класса	С
Экзаменатор	N			идентификатор, внешний ключ
Аудитория	N
Дата	D
Время	T

Задание №2. Выборка данных.

Проверить уникальность комбинации "Аудитория", "Дата", "Время". Определить дисциплины, по которым нет экзаменов.

Создать расписание экзаменов:

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

Задание №3. Работа с представлениями.

Создать представления:

1. "Количество экзаменов" (класс, количество экзаменов).
2. "Количество экзаменов для направлений естественнонаучных и гуманитарных дисциплин " (название дисциплины, количество экзаменов на естественнонаучном направлении, количество экзаменов на гуманитарном направлении).
3. "Преподаватели математики" (ФИО преподавателя).

Примечания:

1. Поля основного отношения с типами и ограничениями целостности приведены в таблице.
2. Для остальных отношений в скобках перечислены обязательные поля.
3. Типы полей обозначаются следующим образом: N – числовое, C – символьное, D – дата, T – время, L – логическое (имеющее значения.T. – истина и.F. – ложь).
4. Для некоторых представлений также в скобках перечислены поля.

Вариант 15. Фрагмент БД отдела кадров.

Задание №1. Создание и заполнение отношений.

1. Отношение "Отделы" ("Номер отдела", "Название отдела").
2. Отношение "Сотрудники" (основное):

Содержимое поля	Тип	Длина	Дес.	Примечание
Табельный номер	N			первичный ключ
ФИО	C			обязательное поле
Пол	C			по умолчанию – мужской
Дата рождения	D
Образование	C			высшее, среднее, начальное
Номер отдела	N			внешний ключ
Должность	C			обязательное поле

1. Отношение "Дети" (внешний ключ к отношению "Сотрудники", "Имя", "Дата рождения").

Задание №2. Выборка данных.

Создать упорядоченные списки:

• сотрудников с детьми от 3 до 12 лет включительно;
• отделов, в которых нет сотрудников.

Посчитать количество сотрудников с разными уровнями образования.

Задание №3. Работа с представлениями.

Создать представления:

1. "Отделы и сотрудники" (поля обоих отношений без повторов).
2. "Бездетные сотрудники" (все поля отношения "Сотрудники").
3. "Образовательный уровень сотрудников" (уровень образования, количество мужчин, количество женщин).

Дата добавления: 2015-05-10