 |
Вывести на экран информацию обо всех группах и обучающихся в них студентах
|
|
|
|
Лабораторная работа №3
Тема:
Межтабличные связи.
Цель работы:
Научиться создавать и работать с различными видами соединений.
Теоретическая часть:
Операция соединения
Соединение – это группа бинарных операций реляционной алгебры. Для выполнения данной операции необходимо, чтобы таблицы были совместимы по соединению, то есть содержали общий атрибут одного типа и вида данных. В данной лабораторной работе рассматриваются три вида соединения: симметричное, левостороннее и правостороннее. Симметричное соединение – это сцепление каждой записи главной таблицы с каждой записью зависимой при условии, что значения полей первичного ключа главной таблицы равны значениям полей связанного внешнего ключа зависимой таблицы. Такой вид соединения используется для построения запросов, когда в результат включаются поля из нескольких связанных отношений. В левостороннее соединение включаются все записи из левого отношения и связанные записи из правого. Там, где связанных записей нет, поля заполняются значениями null. Правостороннее соединение – аналог левостороннего, если таблицы поменять местами. Эти виды соединения применяются для нахождения ошибок заполнения таблиц базы данных, либо когда нужно получить полный набор записей одной из таблиц. В MS Access 2010 вид соединения можно определить на схеме данных.
Целостность данных
Целостность данных – это непротиворечивость данных в базе данных. Существует два вида целостности данных – категорная и ссылочная целостность. Категорная целостность данных обеспечивается в том случае, если в отношении нет повторяющихся записей. Другими словами можно сказать, что в любом отношении должен быть первичный ключ, который не может принимать null-значения. Если в таблице невозможно явно выделить первичный ключ, а составной ключ является неудобным, MS Access по умолчанию создает дополнительное поле с уникальными идентификаторами, которое и будет выполнять роль первичного ключа.
|
|
|
Ссылочная целостность данных обеспечивается в том случае, если для каждой записи внешней таблицы есть соответствующая ей запись в главной, связанная по внешнему ключу. Если условно считать главную таблицу правой, а зависимую – левой, то при левом внешнем соединении записи зависимой таблицы не должны сцепляться с nullзначениями. Ссылочная целостность данных накладывает ограничения на ввод данных в зависимую таблицу. При попытке ввода записи с внешним ключом, значение которого не входит в набор значений первичного ключа главной таблицы, СУБД выдает ошибку и не допускает ввод записи. Так, например, нельзя назначить зарплату сотруднику, которого нет в штате.
Каскадное обновление и удаление
Если между отношениями обеспечивается ссылочная целостность данных, СУБД MS Access позволяет задать каскадное обновление и удаление записей. Каскадное обновление осуществляется следующим образом: если изменить значение первичного ключа в главной таблице, то автоматически изменяться все связанные с ним значения внешнего ключа в зависимой. Например, если изменить код отдела в списке подразделений, то автоматически этот код изменится и у всех работающих в нем сотрудников. Каскадное удаление происходит в случае, если удаляется запись из главной таблицы. Тогда все связанные с ней записи в зависимой таблице также удаляются. Эта функция удобна в тех случаях когда, например, нужно удалить из штатного расписания уволенного сотрудника. Но стоит помнить, что иногда опция каскадного удаления может привести к нежелательным результатам. Например, если между таблицами клиентов и заказов, которые они делали, включить каскадное удаление, то при попытке удалить из базы данных информацию о клиенте, будет утеряна вся информация о его заказах, что может привести к невозможности подсчета принесенных фирме денежных средств. В MS Access каскадное обновление и удаление включаются при изменении связи между таблицами на схеме данных.
|
|
|
Задание:
1. Открыть базу данных, созданную в предыдущих лабораторных работах.
2. В схеме данных последовательно задать левое и правое внешние соединения.
3. Сделать выборки, предложенные в варианте задания и объяснить результат.
4. В схеме данных изменить соединение на симметричное.
5. Обеспечить целостность данных и посмотреть ограничения к вводу данных в зависимую таблицу.
6. Последовательно включить каскадное обновление и удаление.
7. Выполнить обновление записей в главной таблице и объяснить полученный результат.
8. Выполнить удаление записей из главной таблицы и объяснить полученный результат.
Порядок выполнения:
| Пускà Все программыà Microsoft Officeà Microsoft Access 2010 | 
|
| Файлà Открыть | 
|
| На вкладке Конструктор нажмите кнопку Схема данных | 
|
| Добавьте созданные таблицы и соедините ключевые атрибуты | 
|
| В окне Изменение связей нажмите кнопку Объединение и выберите второй пункт | 
|
| Щелкните правой кнопкой мыши на заголовке схемы данных и выберите Сохранить | 
|
Запустите MS Access
Откройте созданную базу
Откройте схему данных
Соедините таблицы по внешнему ключу
В свойствах соединения укажите левое внешнее соединение
Сохраните схему данных
| СозданиеàКонструктор запросов | 
|
| Щелкните правой кнопкой по связи на схеме данных и выберите Изменить связь | 
|
| СозданиеàКонструктор запросов |
|
| Щелкните правой кнопкой по связи на схеме данных и выберите Изменить связь | 
|
| В окно Изменение связи выберите Обеспечение целостности данных | 
|
| Откройте таблицу и введите несколько новых записей | 
|
| В окно Изменение связи выберите Каскадное обновление связанных записей | 
|
| Измените значения первичного ключа в главной таблице | 
|
| В окно Изменение связи выберите Каскадное удаление связанных записей | 
|
| Выделите запись правой кнопкой мыши и нажмите Удалить запись | 
|
Создайте запрос для проверки действия связи
|
|
|
Измените вид соединения на правое внешнее
Создайте запрос для проверки действия связи
Измените соединение на симметричное
Задайте обеспечение целостности данных
Добавьте несколько записей в зависимую таблицу
Включите каскадное обновление
Обновите несколько записей в главное таблице
Включите каскадное удаление
Удалите несколько записей из главной таблицы
Варианты заданий:
1 вариант:
Вывести на экран информацию обо всех авторах и их книгах при условии, что:
1) если в библиотеке нет ни одной книги данного автора, оставить пустое поле; 2) если для хранящейся в библиотеке книги нет данных об авторе, оставить пустое поле.
2 вариант:
Вывести на экран информацию обо всех книгах и работе абонемента при условии, что:
1) если книга ни разу не была востребована, оставить пустое поле;
2) если в абонементе была выдана книга, не зарегистрированная в библиотеке, оставить пустое поле.
3 вариант:
Вывести на экран информацию обо всех читателях и работе абонемента при условии, что: 1) если читатель ни разу не пользовался абонементом, оставить пустое поле;
2) если в абонементе была выдана книга читателю, не зарегистрированному в библиотеке, оставить пустое поле.
4 вариант:
Вывести на экран информацию обо всех читателях и наложенных на них штрафах при условии, что:
1) если читателю не было назначено ни одного штрафа, оставить пустое поле;
2) если штраф был назначен читателю, не зарегистрированному в библиотеке, оставить пустое поле.
5 вариант:
Вывести на экран информацию обо всех товарах и их размещении при условии, что: 1) если товар не был размещен ни на одном из складов, оставить пустое поле; 2) если на складе размещен незарегистрированный товар, оставить пустое поле.
6 вариант:
Вывести на экран информацию обо всех складах и размещении товаров при условии, что:
1) если на складе не размещается товар, оставить пустое поле;
2) если товар размещается на незарегистрированном в базе данных складе, оставить пустое поле.
|
|
|
7 вариант:
Вывести на экран информацию о товарах и заказах, в которые они входят при условии, что:
1) если товар ни разу не был заказан, оставить пустое поле;
2) если был заказан товар, не зарегистрированный в базе данных, оставить пустое поле.
8 вариант:
Вывести на экран информацию обо всех заказах и их содержимом при условии что:
1) если заказ не содержит ни одной позиции, оставить пустое поле;
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) если лаборатория относится к кафедре, которой нет в базе данных, оставить пустое поле.
Пример выполнения 16 варианта:
1. Исходные таблицы
Группы (главная таблица)
| Номер_группы (PK) | Макс_кол_во_чел | Код_факультета | Куратор_группы |
| П-317 | 35 | 230105 | 21 |
| П-327 | 30 | 230105 | 17 |
| П-228 | 30 | 230105 | 25 |
| ГБОУ СПО МКУНТ | ПЦК №7 «Информатика и вычислительная техника» | Смирнов Е.М. | ||
| Э-317 | 30 | 230101 | 24 | |
| П-436 | 30 | 230105 | 21 |
Студенты (зависимая таблица)
| Код_студ (PK) | Фамилия | Имя | Отчество | Дата_р | Адрес | Телефон | Ном_гр |
| 15 | Иванов | Иван | Дмитриевич | 1991 | Москва | 8-995-633-56-4P | П-327 |
| 16 | Петров | Вячеслав | Сергеевич | 1991 | Видное | 8-744-451-45-U7 | П-327 |
| 17 | Сидоров | Владимир | Петрович | 1992 | Москва | 8-447-211-23-V2 | П-228 |
| 19 | Кузнецов | Иван | Петрович | 1990 | Видное | 8-755-524-51-8E | П-436 |
| 23 | Титова | Вероника | Максимовна | 1991 | Москва | 8-996-544-62-2T | Э-317 |
| 24 | Иванов | Артем | Михайлович | 1990 | Кашира | 8-332-542-36-Y2 | П-337 |
2. Левое внешнее соединение
Вывести на экран информацию обо всех группах и обучающихся в них студентах
|
|
|
|
|
|
|
|
|
|
| ||||||||||||||||||||||||||||
| П-317 | 35 | 230105 | 21 | |||||||||||||||||||||||||||||||||||
| П-327 | 30 | 230105 | 17 | 15 | Иванов | Иван | Дмитриевич | 1991 | Москва | 8-995-633-56-4P | ||||||||||||||||||||||||||||
| П-327 | 30 | 230105 | 17 | 16 | Петров | Вячеслав | Сергеевич | 1991 | Видное | 8-744-451-45-U7 | ||||||||||||||||||||||||||||
| П-228 | 30 | 230105 | 25 | 17 | Сидоров | Владимир | Петрович | 1992 | Москва | 8-447-211-23-V2 | ||||||||||||||||||||||||||||
| Э-317 | 30 | 230101 | 24 | 23 | Титова | Вероника | Максимовна | 1991 | Москва | 8-996-544-62-2T | ||||||||||||||||||||||||||||
| П-436 | 30 | 230105 | 21 | 19 | Кузнецов | Иван | Петрович | 1990 | Видное | 8-755-524-51-8E |
| Номер_группы (PK) | Макс_кол_во_чел | Код_факультета | Куратор_группы |
| П-317 | 35 | 230105 | 21 |
3. Правое внешнее соединение
|
|
|
