Распределение сотрудников по отделам

Код отдела	Код сотрудника

Чтобы было легче воспринимать новый материал на семинарах, мы для наглядности будем строить базу данных для книжного магазина, состоящую только из двух таблиц. В первой таблице будут храниться сведения о клиентах, во второй таблице будет содержаться информация о продажах книг этим клиентам. Эта база данных будет часто использоваться нами на последующих семинарах при объяснении нового материала.

Загрузите Microsoft Access, создайте файл Книжный магазин. Давайте с помощью конструктора построим таблицу Клиенты:

Имя поля	Тип данных	Описание
Код клиента	Счетчик	Код клиента (порядковый номер клиента)
Фамилия	Текстовый	Фамилия клиента
Имя	Текстовый	Имя клиента
Отчество	Текстовый	Отчество клиента
Почтовый индекс	Текстовый	Почтовый индекс
Город	Текстовый	Город
Адрес	Текстовый	Название улицы, номер дома, номер квартиры
Телефон	Текстовый	Телефон клиента
Профессия	Текстовый	Профессия клиента
Тематика	Текстовый	Книги какой тематики интересуют клиента

Полю Код клиентададим статус ключа.

Дайте имя таблице Клиенты.

С помощью конструктора постройте таблицу Продажа:

Имя поля	Тип данных	Описание
Номер	Счётчик	Порядковый номер покупки
Код клиента	Числовой	Порядковый номер клиента (личный номер в таблице Клиенты)
Дата продажи	Дата/время	Дата продажи
Автор	Текстовый	Фамилия автора
Название	Текстовый	Название книги
Цена	Денежный	Стоимость книги
Количество	Числовой	Количество купленных экземпляров

Эта таблица отражает процесс продажи книг клиентам из таблицы Клиенты.В ней указаны дата покупки, личный номер клиента, автор, название, цена купленной книги, а так же количество экземпляров. Номер – ключевое поле.

Таким образом, мы имеем две таблицы. В таблице Клиенты (родительская таблица) поле Код клиента (тип счётчик) является первичным ключом, все его значения различны. В таблице Продажа (дочерняя таблица) поле Код клиента имеет числовой тип и является внешним ключом. Эти данные могут принимать только те значения, которые принимает поле Код клиента в таблице Клиентыи могут повторяться несколько раз.

Правила внешних ключей

Для выполнения ограничения целостности по ссылке в реляционной базе данных необходимо соблюдать два правила:

1. Каскадное удаление

2. Ограниченное удаление

Каскадное удаление: при удалении записей из родительской таблицы удаляются все записи из дочерней таблицы, соответствующие данной записи в родительской таблице по внешнему ключу.

Ограниченное удаление: при попытке удаления записей из родительской таблицы проверяется наличие в дочерней таблице записей, соответствующих этой записи в родительской таблице по внешнему ключу. Если такие записи в дочерней таблице имеются, то удаление отвергается.

Пример 9

Клиенты

Код клиента	Фамилия	Имя	Отчество	Телефон	Адрес	Профессия	Тематика
	Иванов	Пётр	Иванович		Ленинский пр., д.55, кВ.77	Экономист	Изобразительное искусство
	Петров	Павел	Сергеевич		Ломоносовский пр., д.3, кВ.7	Врач	Английский язык
…	…	…	…	…	…	…	…

Продажа

Номер	Код клиента	Дата	Автор	Название	Цена	Количество
		10.05.2014	Пушкин А.С.	Сказки
		15.06.2014	Лермонтов М.Ю.	Стихи
		17.07.2015	Толстой Л.Н.	Война и мир
		23.09.2015	Пушкин А.С.	Стихи

Из таблицы Клиенты удаляется клиент Петров (с номером 2). В результате каскадного удаления из таблицы Продажа удаляются все записи, в которых содержатся покупки клиента Петрова (с номером 2):

Клиенты

Код клиента	Фамилия	Имя	Отчество	Телефон	Адрес	Профессия	Тематика
	Иванов	Пётр	Иванович		Ленинский пр., д.55, кВ.77	Экономист	Изобразительное искусство
…	…	…	…	…	…	…	…

Продажа

Номер	Код клиента	Дата	Автор	Название	Цена	Количество
		10.05.2014	Пушкин А.С.	Сказки
		17.07.2015	Толстой Л.Н.	Война и мир

А теперь войдём в режим Схема данных и создадим связь между таблицами по полю Код клиента. В режиме Таблицы выполните команду

Работа с базами данных → Схема данных

В окне Добавление таблицы добавьте таблицы Клиенты и Продажа. Нажмите клавишу Добавить.

Чтобы установить нужный вид связи между таблицами щелкните на поле Код клиента таблицы Клиенты, перетащите и опустите его на поле Код клиента в таблице Продажа. Когда вы отпустите кнопку мыши, откроется диалоговое окно Изменение связей.

Строка уже заполнена именами полей. Поскольку не имеет смысла хранить данные для несуществующих клиентов, установите «галочку» в позиции Обеспечение целостности данных. В этом случае Microsoft Access не позволит добавить в таблицу Продажа запись с «несуществующим» кодом клиента. Microsoft Access также не разрешит удалить из таблицы Клиенты запись, для которой имеются покупки в таблице Продажа.

После того, как мы установим «галочку» в позиции Обеспечение целостности данных, активизируются ещё два параметра: Каскадное обновление связанных полей и Каскадное удаление связанных записей. Если вы установите «галочки» в позиции Каскадное обновление связанных полей, то при изменении значения первичного ключа в родительской таблице автоматически обновятся все значения внешних ключей в дочерней таблице.

Замечание. Когда значения первичного ключа задаются счётчиком, они не могут изменяться после первоначального присваивания. Всё, что сказано выше, относится к тому случаю, когда первичный ключ имеет числовой или текстовый тип.

Если вы установите «галочку» в позиции Каскадное удаление связанных записей, то при удалении записи из родительской таблицы, Microsoft Access автоматически удалит все соответствующие записи в дочерней таблице. Например, если вы удалите некоторого клиента в таблице Клиенты, то Microsoft Access в этом случае удалит все покупки этого клиента в таблице Продажа.

После нажатия клавиши Создать, Microsoft Access создаст связь и нарисует линию между таблицами, указывающую на наличие связи. В том случае, когда мы укажем необходимость обеспечения целостности данных, Microsoft Access добавит на концах линии у таблицы Клиенты цифру 1, а на другом конце, у таблицы Продажа - символ бесконечности «∞».

Замечание 1. Чтобы удалить связь, щёлкните мышкой на соответствующей линии и через контекстное меню удалите связь.

Замечание 2. Если вы хотите изменить уже существующую связь, два раза щёлкните мышкой на нужной линии, чтобы снова открыть диалоговое окно Изменение связей.

Откройте таблицу Клиентыи внесите в неё 7 записей.

Откройте таблицу Продажаи внесите в неё 15 записей. Не забудьте, что в поле с именем Код клиента заносятся личные номера клиентов, (те номера, которые имеют клиенты в таблице Клиенты). Эти номера могут повторяться несколько раз, (в зависимости от того, сколько разных книг клиент купил в этом магазине). Например, если клиент, имеющий Код клиента равный 2 купил три разные книги, то в таблице Продажа будет три записи, отражающие покупки этого клиента. Названия книг вводятся без кавычек.

А теперь посмотрим, как создавать запросы, когда нам нужно получить информацию одновременно из двух таблиц. Например, мы хотим узнать, кто из клиентов покупал книги Пушкина. Чтобы выполнить этот запрос, нужно войти в конструктор запросов и добавить обе таблицы Клиенты и Продажа. Вы видите, что эти таблицы соединены между собой по ключевому полю Код клиента. Далее в строке Поле нужно выбрать из таблицы Клиенты поля Фамилия, Имя, Отчество, а из таблицы ПродажаполяАвтори Название. В строке Условие отбора укажите: “Пушкин А.С.”.

Для выполнения запроса, как обычно, нажмите кнопку Выполнить.

Задание

Для таблиц Продажа и Клиенты создайте 7 запросов, для каждого запроса в мастере постройте отчёт.

1. Какие книги купил клиент <ФИО>?
2. Кто купил книгу <Название>?
3. Кто покупал книги <Дата продажи>?
4. Кто покупал книги с ценой более 700 рублей?
5. Когда покупал книги клиент <ФИО>?
6. Кто покупал книги в период с <Дата 1> по <Дата 2>?
7. Книги каких авторов покупал клиент <ФИО>?

Замечание. Обозначения <ФИО>, <Дата продажи>,<Название> означают то, что в запросах вам нужно указать конкретные значения по соответствующим полям вашей таблицы. Например, шестой запрос может выглядеть так: