СУБД Access

Задание:

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

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

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

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

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

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

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

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

Данное отношение находится в первой нормальной форме, т.к. все атрибуты являются неделимыми. Устраним частичную зависимость и переведем это отношение во вторую нормальную форму путем декомпозиции основного отношения:
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 Заказы.[Номер заказа];

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

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

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

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

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

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

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

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

Скачано с www.znanio.ru