База данных Access "Сведения о клиентах и заказах"

Задание:

1) Спроектировать базу данных (БД) согласно варианту задания. 
2) Написать запросы SQL на создание всех спроектированных таблиц.
3) С помощью 2-х запросов SQL изменить структуру двух таблиц (например, добавить или удалить какой-либо атрибут), показать результаты изменения.
4) Внести данные в таблицы в режиме Таблица -> Открыть (не менее 10 кортежей в каждой таблице).
5) Создайте запросы SQL, которые будут использоваться для манипулирования данными:
7) Сформулировать 2 подзапроса (однотабличный и многотабличный), создайте их с помощью языка SQL и покажите результаты их выполнения.
8) Сформулировать 2 многотабличных запроса, создать их на SQL и показать результаты их выполнения.

    Скачать базу данных в Access

Проектирование базы данных

Рассмотрим основное отношение, соответствующее базе данных "Сведения о клиентах и заказах".

Код клиента, Название, Имя, Фамилия, Адрес счета, Город, Регион, Индекс, Страна, Должность, Телефон, Факс, Сумма долга, Код заказа, Код клиента, Код сотрудника, Номер заказа, Дата заказа, Модель, Серийный номер, Описание, Дата завершения, Ставка налога.

Данное отношение находится в первой нормальной форме, поскольку все его атрибуты являются неделимыми.

Определим атрибуты и их типы значений:

Атрибут

Тип атрибута

Формат атрибута

Код клиента

Числовой

Длинное целое

 Название

Текстовый

30

 Имя

Текстовый

15

 Фамилия

Текстовый

15

 Адрес счета

Текстовый

20

 Город

Текстовый

15

 Регион

Текстовый

15

 Индекс

Текстовый

6

 Страна

Текстовый

15

 Должность

Текстовый

20

 Телефон

Текстовый

10

 Факс

Текстовый

10

 Сумма долга

Денежный

Денежный

 Код заказа

Числовой

Длинное целое

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

Числовой

Длинное целое

 Номер заказа

Числовой

Длинное целое

 Дата заказа

Дата

Дата/время

 Модель

Текстовый

10

 Серийный номер

Текстовый

8

 Описание

Текстовый

50

 Дата завершения

Дата

Дата/время

  Ставка налога.

Числовой

Длинное целое

Используя метод нормальных форм, нормализуем данное отношение путем декомпозиции его на несколько отношений

Данное отношение находится в первой нормальной форме, т.к. все атрибуты являются неделимыми. Устраним частичную зависимость и переведем это отношение во вторую нормальную форму путем декомпозиции основного отношения:
1) Код клиента, Название, Адрес счета, Город, Регион, Индекс, Страна, Телефон, Факс, Сумма долга.
2) Код сотрудника. Имя, Фамилия, Должность.
3) Номер заказа, Дата заказа, Код сотрудника, Код клиента, Модель, Серийный номер, Дата завершения, Ставка налога.
4) Модель, Описание.
Данные отношения, кроме третьего, находятся в третьей нормальной форме. Отношение 3 находится во второй нормальной форме, поскольку неключевой атрибут Модель зависит от атрибута Серийный номер. Устраним частичную зависимость и переведем это отношение в третью нормальную форму путем декомпозиции отношения 3 на два отношения:

1. Номер заказа, Дата заказа, Код сотрудника, Код клиента, Серийный номер, Дата завершения, Ставка налога.
2. Серийный номер, Модель.

Получим следующие таблицы:
1) Код клиента, Название, Адрес счета, Город, Регион, Индекс, Страна, Телефон, Факс, Сумма долга.
2) Код сотрудника. Имя, Фамилия, Должность.
3) Номер заказа, Дата заказа, Код сотрудника, Код клиента, Серийный номер, Дата завершения, Ставка налога.
4) Серийный номер, Модель.
5) Модель, Описание.

Определение ключей и создание связей схемы связей.

Определим первичные и внешние ключи в таблицах.
В 1-ой таблице первичный ключ - Код клиента.
Во 2-ой таблице первичный ключ- Код сотрудника.
В 3-ей таблице первичный ключ- Номер заказа. Внешние ключи - Код сотрудника, Код клиента, Серийный номер.
В 4-ой таблице первичный ключ- Серийный номер, внешний ключ - Модель.
В 5-ой таблице первичный ключ- Модель.
Создадим схему связей между атрибутами таблиц для обеспечения целостности БД.


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

Схема связей базы данных о клиентах и заказах

Создание файла базы данных в СУБД Microsoft Access 2003.

Создадим файл спроектированной БД в СУБД Microsoft Access 2003. Для этого запустим программу Microsoft Access 2003, выберем в окне команду Создать файл, затем в окне Создание файла выберем пункт Новая база данных. 

Запросы SQL на создание всех спроектированных таблиц.


1. CREATE TABLE Клиенты ([Код клиента] INT PRIMARY KEY, Название CHAR(30) NOT NULL, [Адрес счета] CHAR(20), Город CHAR(15), Регион CHAR(15), Телефон CHAR(10), Факс CHAR(10), Индекс CHAR(6), Страна CHAR(15));

2. CREATE TABLE Сотрудники ([Код сотрудника] INT PRIMARY KEY, Имя CHAR(15) NOT NULL, Фамилия CHAR(15) NOT NULL, Должность CHAR(20));

3. CREATE TABLE Товары ([Серийный номер] CHAR(8) PRIMARY KEY, Модель CHAR(10) NOT NULL);

4. CREATE TABLE Модели (Модель CHAR(10) PRIMARY KEY, Описание CHAR(50));

5. CREATE TABLE Заказы ([Код заказа] INT PRIMARY KEY, [Дата заказа] DATE, [Код сотрудника] INT, [Код клиента] INT, [Серийный номер] CHAR(8), [Дата завершения] DATE, [Ставка налога] INT);

Изменение структуры таблиц с помощью SQL запроса

Из таблицы клиенты удалим атрибуты Регион и Факс.
ALTER TABLE Клиенты DROP COLUMN Факс, Регион;

Добавим в таблицу Модели атрибут Выпуск.
ALTER TABLE Модели ADD COLUMN Выпуск INT);

Внесение данных в таблицы.

В окне базы данных выберем объект Таблицы, установим курсор на нужную таблицу и нажмем кнопку Открыть. В режиме Таблицы введем записи в таблицы:

Пример заполнения таблицы Клиенты

Пример заполнения таблицы Заказы

Пример заполнения таблицы Модели

Пример заполнения таблицы Сотрудники

Пример заполнения таблицы Товары

Запросы на языке SQL для манипулирования данными в базе MS Access

1) Запрос на добавление:
INSERT INTO Заказы ( [Номер заказа], [Дата заказа], [Код сотрудника], [Код клиента], [Серийный номер], [Дата завершения], [Ставка налога] )
VALUES (11, #02/22/2009#, 8, 9, '31548722 ', #02/22/2009#, 19);

2) Запрос на удаление:
DELETE [Код сотрудника]
FROM Сотрудники
WHERE [Код сотрудника]=7;

3) Запрос на обновление:
UPDATE Заказы SET Заказы.[Ставка налога] = [Заказы]![Ставка налога]+1;

4) Запрос на создание таблицы «Сотрудники и заказанные модели» на основе имеющихся:
SELECT Сотрудники.Фамилия, Сотрудники.Имя, Товары.Модель, Модели.Описание INTO [Сотрудники и заказанные модели]
FROM (Модели INNER JOIN Товары ON Модели.Модель = Товары.Модель) INNER JOIN (Сотрудники INNER JOIN Заказы ON Сотрудники.[Код сотрудника] = Заказы.[Код клиента]) ON Товары.[Серийный номер] = Заказы.[Серийный номер];

Результат запроса на создание таблицы

Запросы SQL для выборк данных в базе MS Access

Создадим 4 запроса на выборку (с обязательным использованием агрегатных функций и сортировки данных).

1. Выберем информацию о заказах и клиентах на телевизоры 2010 года выпуска:
SELECT Заказы.[Номер заказа], Заказы.[Дата заказа], Клиенты.Название, Заказы.[Серийный номер]
FROM (Модели INNER JOIN Товары ON Модели.Модель = Товары.Модель) INNER JOIN (Клиенты INNER JOIN Заказы ON Клиенты.[Код клиента] = Заказы.[Код клиента]) ON Товары.[Серийный номер] = Заказы.[Серийный номер]
WHERE (((Модели.Выпуск)=2007))
ORDER BY Заказы.[Номер заказа];

Запрос1
Номер заказаДата заказаНазваниеСерийный номер
8 19.02.2009 Фостергруп 40110057
9 22.02.2009 Мвидео 36985014

2. Подсчитаем количество заказанных товаров по клиентам и отсортируем в порядке возрастания количества:
SELECT Клиенты.[Код клиента], Клиенты.Название, Count(Заказы.[Серийный номер]) AS Количество
FROM Клиенты INNER JOIN Заказы ON Клиенты.[Код клиента] = Заказы.[Код клиента]
GROUP BY Клиенты.[Код клиента], Клиенты.Название
ORDER BY Count(Заказы.[Серийный номер]);

Запрос2
Код клиентаНазваниеКоличество
9 ТехноЦентр 1
8 Наномир 1
5 Мвидео 1
4 Фостергруп 1
3 Телерынок 1
1 ТелеСтиль 1
2 Плазмацентр 2

3. Выберем из таблицы Заказы номера заказов и серийные номера товаров, а так же вычислим количество дней исполнения заказа:
SELECT Заказы.[Номер заказа], Заказы.[Серийный номер], Заказы![Дата завершения]-Заказы![Дата заказа] AS [Количество дней]
FROM Заказы
ORDER BY Заказы.[Номер заказа];

Запрос3
Номер заказаСерийный номерКоличество дней
2 18012205 1
3 36512001 0
5 21777745 3
6 11446804 1
7 13241114 0
8 40110057 5
9 36985014 2
11 31548722 0

4. Выберем данные по сотрудникам с должностями ст. продавец или продавец 1 кат. и подсчитаем количество оформленных ими заказов:
SELECT Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Должность, Count(Заказы.[Номер заказа]) AS [Количество заказов]
FROM Сотрудники INNER JOIN Заказы ON Сотрудники.[Код сотрудника] = Заказы.[Код клиента]
GROUP BY Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Должность
HAVING (Сотрудники.Должность="ст. продавец" OR Сотрудники.Должность="продавец 1кат.");

Запрос4
ФамилияИмяДолжностьКоличество заказов
Балышев Николай продавец 1кат. 2
Плюснин Александр продавец 1кат. 1
Шатова Мария продавец 1кат. 1

 


Создание однотабличного подзапроса на языке SQL:

С помощью однотабличного подзапроса выберем из таблицы Заказы данные о заказах, сделанных клиентами из города Тверь. Выбор кодов клиента по городу осуществим в виде подзапроса.
SELECT Заказы.[Номер заказа], Заказы.[Дата заказа], Заказы.[Код клиента]
FROM Заказы
WHERE (Заказы.[Код клиента]) In (SELECT [Код клиента] FROM Клиенты WHERE (Клиенты.Город="Тверь"));

ПодЗапрос1
Номер заказаДата заказаКод клиента
5 16.02.2009 1
6 19.02.2009 2
7 19.02.2009 2
11 22.02.2009 9

 

 

Создание многотабличного подзапроса SQL:

Выберем серийные номера и модели товаров из таблицы Товары, с последней датой заказа:

SELECT Товары.[Серийный номер], Товары.Модель, Заказы.[Дата заказа]
FROM Товары INNER JOIN Заказы ON Товары.[Серийный номер] = Заказы.[Серийный номер]
WHERE (Заказы.[Дата заказа]) In (SELECT Max([Дата заказа])
FROM Заказы);

ПодЗапрос2
Серийный номерМодельДата заказа
36985014 UE26B600W 22.02.2009
31548722 LE32B750U 22.02.2009