Материал: Rukovodstvo_k_laboratornym_rabotam_po_kursu_Bazy_Dannykh
Вариант 2
Создать две связанные таблицы: родительская - пациент, дочерняя – процедура.
В таблице пациент должны содержаться данные:
паспорт (десятиразрядное число);
ФИО пациента;
номер полиса;
пол (допустимые значения "муж", "жен");
телефон (должен начинаться с 2, 4, 5, 6, 7, 8),
в таблице процедура должны содержаться данные:
вид процедуры;
продолжительность (не менее 5, не более 20 мин.);
номер кабинета (допустимые значения 10, 11, 14, 15, 17, 18, 20, 22, 24, 25);
степень процедуры (от 1 до 5);
с применением дополнительных средств или нет.
Вариант 3
Создать две связанные таблицы: родительская - абонент, дочерняя - экземпляр книги.
В таблице абонент должны содержаться данные:
номер читательского билета (от 100 до 1500);
ФИО абонента;
телефон (6-ти разрядное число);
адрес абонента;
телефон;
паспорт.
В таблице экземпляр книги должны содержаться данные:
номер (от 1000);
название книги;
номер полки (от 10 до 100, исключая 55, 56, 88);
общее количество экземпляров (не больше 10);
вес экземпляра (от 100 до 1500).
Вариант 4
Создать две связанные таблицы: родительская - корпус, дочерняя- микросхема.
В таблице корпус должны содержаться данные:
материал корпуса (металл, пластмасса, керамика);
номер корпуса (10-ти разрядное число);
количество выводов (от 3 до 64);
температура допустимая (не больше 120);
вид (планар, DIP, FREE).
В таблице микросхема должны содержаться данные:
обозначение;
тип микросхемы;
вид (аналоговая, цифровая);
назначение (общее, специальное, военное);
тип исполнения (ТТЛ, ТТЛШ, КМОП).
Вариант 5
Создать две связанные таблицы: родительская - исполнитель, дочерняя – альбом.
В таблице исполнитель должны содержаться данные:
название исполнителя;
код исполнителя;
основной жанр исполнителя (ПОП, РОК, ДИСКО, РЭП, МЕТАЛ, ПАНК);
количество (если группа);
год с момента начала работы исполнителя.
В таблице альбом должны содержаться данные:
название альбома;
длительность;
жанр (перечень см. в табл. исполнитель);
количество композиций;
дата выпуска.
Вариант 6
Создать две связанные таблицы: родительская - автор, дочерняя – книга.
В таблице автор должны содержаться данные:
Номер автора;
ФИО автора;
Адрес;
дата рождения (не раньше 01.01.1925);
жанр (детектив, фантастика, эссе, драма, мелодрама, сатира);
статус (мэтр, начинающий, средний).
В таблице книга должны содержаться данные:
название;
жанр (см. выше);
кол. страниц;
номер издания (не больше 10);
издательство.
Вариант 7
Создать две связанные таблицы: родительская - клиент, дочерняя – заказ.
В таблице клиент должны содержаться данные:
паспорт (10-ти разрядное число, не начинающееся с 0);
адрес клиента;
телефон (6-ти разрядное число);
тип клиента (постоянный, периодический, одноразовый);
пол ("м", "ж").
В таблице заказ должны содержаться данные:
вид товара;
количество товара в заказе;
сумма заказа;
дата заказа;
дата доставки.
(клиент не может заказать два заказа в один день).
Вариант 8
Создать две связанные таблицы: родительская - работник, дочерняя - выполненная работа.
В таблице работник должны содержаться данные:
учетный номер работника;
паспорт (10-ти разрядное число);
пол ("мужской", "женский");
адрес;
возраст (не меньше 18 и не старше 65).
В таблице выполненная работа должны содержаться данные:
вид работы;
количество часов (от 1 до 12);
степень тяжести (легкая, средняя, тяжелая);
дата выполнения;
степень выполнения в %.
Вариант 9
Создать две связанные таблицы: родительская - студент, дочерняя: дисциплина.
В таблице студент должны содержаться данные:
номер зачетной книжки;
паспорт;
ФИО студента;
номер группы студента;
номер специальности (5-ти или 6-ти разрядное число).
В одной группе не должно быть однофамильцев.
В таблице дисциплина должны содержаться данные:
название дисциплины;
количество часов;
ФИО преподавателя;
направление (гуманитарное, техническое, естественное);
степень дисциплины (от 1 до 10).
Вариант 10
Создать две связанные таблицы: родительская - аптека, дочерняя – лекарство.
В таблице аптека должны содержаться данные:
номер аптеки;
адрес аптеки;
направление (общее, гомеопатическое, народно-медицинское);
количество работников (не меньше 5);
номер лицензии.
В таблице лекарство должны содержаться данные:
название;
страна выпуска;
вид (таблетки, капли, аэрозоль, мази, гели, кремы, ампулы, порошки, отвары);
цена (в разных аптеках может быть разная цена);
назначение (сердечные, желудочные, легочные, отхаркивающие, кожные).
Дополнительное ограничение: не могут повторяться название, страна выпуска и вид.
Вариант 11
Создать две связанные таблицы: родительская - фирма, дочерняя – предоставляемые услуги.
В таблице фирма должны содержаться данные:
название фирмы;
ФИО директора;
адрес фирмы;
количество работающих сотрудников больше (5);
специализация.
В таблице предоставляемые услуги должны содержаться данные:
название услуги;
стоимость услуги;
продолжительность услуги (больше 0);
количество сотрудников, обеспечивающих услугу;
вид оборудования, используемое при услуге.
Вариант 12
Создать две связанные таблицы: родительская - общежитие, дочерняя – студент.
В таблице общежитие должны содержаться данные:
номер общежития;
ФИО коменданта;
адрес;
наличие столовой;
количество компьютеров;
факультет (ФЭТ, РТФ, РКФ, ФСУ).
В таблице студент должны содержаться данные:
номер паспорта студента;
номер группы;
ФИО студента;
дата рождения;
стоимость платы за проживание (больше 300).
Контрольные вопросы
В чем заключается ссылочная целостность?
Привести типы ограничений при создании таблицы ORACLE?
Привести типы ссылочной целостности.
Какие ограничения определяются в ограничении таблицы?
Сколько первичных ключей можно определить для таблицы?
Имеет ли порядок создания родительской и дочерней таблиц?
В каком случае внешний ключ может допускать пустые значения?
Лабораторная работа №6 Создание сложных запросов с использованием подзапросов и агрегированных функций
Целью работы является формирование навыков создания запросов к базе данных. Следует изучить команду SELECT, агрегированные функции и использование подзапросов. При выборке данных могут быть использованы четыре связанные таблицы, находящиеся в схеме MAI:
таблица отделов OTD с первичным ключом nom_otd и полями:
nom_otd – номер отдела;
name_nach – фамилия начальника;
etaj – этаж, на котором находится отдел;
nom_tel – номер телефона отдела.
таблица сотрудников SOTR с первичным ключом nom_sotr и внешним ключом nom_otd и полями:
nom_sotr – номер сотрудника;
nom_otd – номер отдела, где работает сотрудник;
name1 – фамилия сотрудника;
name2 – имя сотрудника;
name3 – отчество сотрудника;
zarpl – зарплата сотрудника;
adres – адрес сотрудника;
city – город сотрудника.
Таблица детей сотрудников DETI с составным первичным ключом nom_sort,name_child и внешним ключом nom_sotr:
nom_sotr – номер сотрудника;
name_child – имя ребенка;
voz- возраст ребенка;
school – номер школы, где учится ребенок. Это поле может иметь пустое значение, если ребенок не учится в школе.
Таблица школ SHOOL с первичным ключом nomer и полями:
nomer – номер школы;
name_dir – фамилия директора школы;
phone - телефон школы.
Порядок выполнения
Выполнить запрос к базе данных в соответствии с вариантом, используя подзапрос и агрегированные (групповые) функции. Определить число строк при помощи функции COUNT(*), выдаваемых запросом. Составить отчет, в котором должна быть представлены запросы, определение количества строк и ответы на контрольные вопросы.
Связь таблиц показана на рис. 4
Рис. 4
Варианты работы
Вариант 1
Выбрать сотрудников, у которых в школе с номером 10 учатся более двух детей.
Вариант 2
Выбрать сотрудников имеющих 2 и более детей, учащихся в школе, где директор – Иванов В.П.
Вариант 3
Выбрать школы, в которых учатся дети сотрудников, работающих в отделе N 119.
Вариант 4
Выбрать сотрудников, имеющих более 3 детей, один из которых учится в школе.
Вариант 5
Выбрать отделы, в которых более 3 сотрудников не имеют детей.
Вариант 6
Выбрать отделы, сотрудники которых имеют более одного ребенка с возрастом 20 лет.
Вариант 7
Выбрать отделы, в которых сотрудники имеют детей в возрасте менее 7 лет учатся в школе.
Вариант 8
Выбрать сотрудников, имеющих только двух детей, один из которых учится в школе, а другой - нет.
Вариант 9
Выбрать детей, родители которых не имеют однофамильцев среди сотрудников.
Вариант 10
Выбрать сотрудников, которые получают зарплату выше средней зарплаты его отдела.
Вариант 11
Выбрать сотрудников, дети которых учатся в разных школах.
Вариант 12
Выбрать сотрудников, имеющих детей одного возраста.
Контрольные вопросы
В какой части опции WHERE оператора SELECT должен находиться подзапрос?
В чем преимущество подзапросов?
Какая реляционная операция выполняется при многотабличной выборке?
Какая логическая операция используется, если подзапрос возвращает несколько строк?
С какими функциями используется опция группировки?