Проектирование реляционной базы данных

ADRESS VARCHAR(35),

PRIMARY KEY (ID));

5) Таблица KASSIR (Кассир)

поле ID является обязательным для заполнения и первичным ключом.

поле FIO - строки переменной длины.

 С учетом перечисленных требований  оператор SQL, создающий таблицу выглядит следующим образом:

CREATE TABLE KASSIR

(ID COUNTER NOT NULL, 

FIO VARCHAR(32),

PRIMARY KEY (ID));

6) Таблица CHEKI (Чеки)

поля ID, KASSIR_ID не могут содержать NULL;

поля ID, KASSIR_ID, NOMER содержат целые числа.

поля ID являются первичным ключом, а KASSIR_ID внешним ключом и имеет связь с другими таблицами.

строка DATE имеет тип данных -календарные даты  DATE.

строка SUMMA имеет денежный тип MONEY.

С учетом перечисленных требований оператор SQL, создающий таблицу буде выглядеть следующим образом:

CREATE TABLE CHEKI

(ID COUNTER NOT NULL, 

NOMER INTEGER,

KASSIR_ID INTEGER,

DATA DATE,

SUMMA MONEY,

PRIMARY KEY (ID),

FOREIGN KEY (KASSIR_ID) REFERENCES KASSIR);

7) Таблица 7 CH_TOV (Чеки-Товары)

поля ID, CHECK_ID, TOVAR_ID не могут содержать NULL;

поля ID, CHECK_ID, TOVAR_ID, KOLICH содержат целые числа.

поля ID являются первичным ключом, а CHECK_ID, TOVAR_ID внешним ключом и имеют связь с другими таблицами.

С учетом перечисленных требований оператор SQL, создающий таблицу буде выглядеть следующим образом:

CREATE TABLE CH_TOV

( CHECK_ID INTEGER,

TOVAR_ID INTEGER,

KOLICH INTEGER,

FOREIGN KEY (CHECK_ID) REFERENCES CHEKI,

FOREIGN KEY (TOVAR_ID) REFERENCES TOVAR);

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4. ВЕДЕНИЕ БАЗЫ ДАННЫХ

 

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

Добавление новых записей в  таблицы производится при помощи оператора INSERT, удаление существующих записей - оператором DELETE, изменение - оператором UPDATE.  Для удобства пользователя можно свести эти операторы вместе для каждой таблицы базы данных. Для удобства пользователя можно свести эти операторы вместе для каждой таблицы базы данных:

1) Таблица TOVAR (Товары):

добавление новой записи

INSERT INTO TOVAR (ID,  NAZV, GRUPPY_ID, PRICE)

VALUES (1, 'кефир', 3, '57' )

удаление существующей записи

DELETE

FROM TOVAR

WHERE ID=1;

изменение существующей записи

UPDATE TOVAR

SET PRICE=PRICE*1.25

2) Таблица TIP (Типы):

добавление новой записи

INSERT INTO TIP (ID, NASV)

VALUES (10, 'Промышленный');

удаление существующей записи

DELETE

FROM TIP

WHERE ID=10;

 

изменение существующей записи

UPDATE TIP NASV='Продуктовый' ID=11;

3) Таблица  GRUPPY (Группы)

добавление новой записи

INSERT INTO GRUPPY (ID, NAzV, TIP_ID)

VALUES (5,'Одежда',7);

удаление существующей записи

DELETE

FROM GRUPPY

WHERE ID=5;

- изменение существующей записи

UPDATE GRUPPY

SET ID=3, NASV='Ткани';

4) Таблица ISGTOVITEL ( Изготовитель)

добавление новой записи

INSERT INTO ISGOTOVITEL (ID, NASV,ADRESS)

VALUES (7, 'ОАО фирма Молоко', '396650, Воронежская область, г. Россошь, ул. Л. Толстого, 53');

удаление существующей записи

DELETE

FROM ISGOTOVITEL

WHERE ID=7;

изменение существующей записи

UPDATE ISGOTOVITEL

SET NASV='ОАО Молвест', ADRESS='394016, г. Воронеж, ул. 45 Стрелковой дивизии, 259'

WHERE ID=7;

5) Таблица KASSIR (Кассир)

добавление новой записи

INSERT INTO KASSIR (ID, FIO)

VALUES (2, 'Воеводкина Мария Петровна');

изменение существующей записи

UPDATE KASSIR

SET ID=4, FIO='Меньшикова Светлана Вячеславовна';

удаление существующей записи

DELETE

FROM KASSIR

WHERE ID=4;

6) Таблица CHEKI (Чеки)

добавление новой записи

INSERT INTO CHEKI (ID, NOMER, DATA, SUMMA, KASSIR_ID)

VALUES (1, '101010', '06.03.2013', '1 500', 4);

удаление существующей записи

DELETE

FROM CHEKI

WHERE KASSIR_ID=4;

- изменение существующей записи

UPDATE CHEKI

SET SUMMA=SUMMA*0.95

7) Таблица 7 CH_TOV (Чеки-Товары)

добавление новой записи

INSERT INTO CH_TOV (CHECK_ID, TOVAR_ID, KOLICH)

VALUES ( 1, 3, '20');

- удаление существующей записи

DELETE

FROM CH_TOV

WHERE CHECK_ID=3;

- изменение существующей записи

UPDATE CH_TOV

SET CHECK_ID=6, TOVAR_ID=1, KOLICH="5";

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5 ВЫБОР ИНФОРМАЦИИ  ИЗ БАЗЫ ДАННЫХ

 

Для выбора информации из базы данных используется оператор SELECT.

1) Вывести список товаров  по убыванию их стоимости.

SELECT TOVAR.ID, NASV, PRICE

FROM TOVAR

ORDER BY PRICE DESC;

2)  Вывести список  чеков с суммой больше 10000.

SELECT ID, NOMER, SUMMA

FROM CHEKI

WHERE SUMMA>10000;

3) Вывести номера чеков,  для которых не указано хотя  бы одно количество товара.

SELECT ID, NOMER

FROM CH_TOV

WHERE KOLICH IS NULL;

4) Вывести список изготовителей,  являющихся акционерными обществами (в название встречается «АО» (ОАО, ЗАО и т.п.)

SELECT NASV

FROM ISGOTOVITEL

WHERE NASV Like "*ОАО*";

5) Вывести  список товаров с указанием  групп товаров. Отсортировать по группам товаров в алфавитном порядке.

SELECT T.NASV, G.NASV

FROM TOVAR AS T, GRUPPY AS G

WHERE GRUPPY_ID=G.ID

ORDER BY T.NASV, G.NASV;

6) Вывести  список изготовителей, производящих  дорогостоящие товары (цена>10000).

SELECT I.NASV, T.NASV, T.PRICE

FROM TOVAR AS T, ISGOTOVITEL AS I

WHERE (T.ISGOTOVITEL_ID=I.ID) And PRICE>10000

ORDER BY I.ISGOTOVITEL;

7) Вывести  группы товаров, которые имеются  в наличии на складе.

SELECT T.NASV, G.NASV, T.KOLICHESTVO

FROM TOVAR AS T, GRUPPY AS G

WHERE (T.GRUPPY_ID=G.ID) AND KOLICHESTVO is NULL

ORDER BY G.NASV;

8) Вывести  типы товаров, выпускаемые определенным  изготовителем.

SELECT TI.ID, TI.NASV, TO.NASV, I.NASV

FROM ISGOTOVITEL AS I, TOVAR AS [TO], TIP AS TI, GRUPPY AS G

WHERE (TO.ISGOTOVITEL_ID=I.ID) And (TO.GRUPPY_ID=G.ID) And (G.TIP_ID=TI.ID) And I.NASV='ООО Доктор Редди';

9)Вычислить  минимальную, среднюю и максимальную  суммы продаж по чекам.

SELECT MIN(SUMMA), MAX(SUMMA), AVG(SUMMA)

FROM CHEKI;

10) Вывести  сумму продаж для каждого кассира.

SELECT C.SUMMA, K.FIO, COUNT(*)

FROM KASSIR AS K, CHEKI AS C

WHERE (C.KASSIR_ID=K.ID)

GROUP BY C.SUMMA, K.FIO;

11)Вывести  количество товаров каждого типа, имеющихся на складе.

SELECT T.NASV, TO.KOLICHESTVO, TO.NASV, COUNT(TO.KOLICHESTVO) AS KOL_GR

FROM TOVAR AS TO, GRUPPY AS G, TIP AS T

WHERE (TO.GRUPPY_ID=G.ID) and (G.TIP_ID=T.ID)

GROUP BY TO.KOLICHESTVO, T.NASV, TO.NASV;

12)Вывести  группы товаров, общее количество  проданных товаров в которых  превышает 100 единиц.

SELECT G.NASV, SUM(KOLICH) AS KOL

FROM GRUPPY AS G, CH_TOV AS CT, TOVAR AS T

WHERE (T.GRUPPY_ID=G.ID) AND (CT.TOVAR_ID=T.ID)

GROUP BY G.NASV

HAVING (SUM(KOLICH)>100);

13) Вывести  группы товаров, средняя стоимость  товаров в которых превышает  среднюю стоимость, вычисленную  по всем товарам.

Основная идея решения задания заключается в следующем. Необходимо для

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

Запрос, возвращающий требуемую информацию, будет состоять из главного

запроса и вложенного запроса. В  главном запросе объединяем таблицы  GRUPPY  и TOVAR, группируем результирующую таблицу по названию группы.

Для отбора групп применяем предложение HAVING, в котором используем многократное сравнение вида ">=ALL", левым операндом которого является средняя цена товаров в группе, а правым операндом является вложенный запрос, возвращающий среднюю цену товаров по всем товарам.

Запрос возвращает название группы товаров и среднюю цену, вычисленную по данной группе.

В режиме SQL запрос будет выглядеть следующим образом:

SELECT G.NASV, AVG(T.PRICE) AS CENA

FROM GRUPPY AS G, TOVAR AS T

WHERE (T.GRUPPY_ID=G.ID)

GROUP BY G.NASV

HAVING (AVG(T.PRICE))>=ALL(SELECT AVG(PRICE) AS CENA

 FROM TOVAR);

14) Вывести  список кассиров с нулевой  суммой продаж.

Основная идея решения задания  заключается в следующем. Необходимо

вывести список тех кассиров, сумма  продаж которых  равна выбранному значению, в нашем случае нулю.

Запрос, возвращающий требуемую информацию, будет состоять из главного

запроса и вложенного запроса. В  главном запросе используем таблицу KASSIR. Во вложенном запросе используем таблицу CHEKI для нахождения нулевой суммы продаж. Запрос возвращает ФИО сотрудника с нулевым уровнем продаж.

В режиме SQL запрос будет выглядит следующим образом:

SELECT K.FIO

FROM KASSIR AS K

WHERE ID IN (SELECT KASSIR_ID

FROM CHEKI

WHERE SUMMA IS NULL);

15) Вывести  номера и даты чеков, сумма  которых не совпадает с суммарной стоимостью товаров, указанных в чеке.

Основная идея решения задания  заключается в следующем. Необходимо

вывести номера чеков, в которых  сумма чека не совпадает  с суммарной  стоимостью товаров в чеке.

Запрос, возвращающий требуемую информацию, будет состоять из главного

запроса и вложенного запроса. В  главном запросе объединяем таблицы  CHEKI  и CH_TOV,  группируем результирующую таблицу по номеру чека и дате.

Для отбора сумм, указанных в чеках  применяем предложение HAVING, в котором  используем выражение для проверки равенства сумм в чеках и суммарной стоимости товаров , указанных в чеках "NOT IN", левым операндом которого является сумма в чеке, а правым операндом является вложенный запрос, возвращающий суммарную стоимость товаров, указанных в чеке.

Запрос возвращает номер и дату чека.

В режиме SQL запрос будет выглядеть следующим образом:

SELECT C.NOMER, C.DATA

FROM CHEKI AS C, CH_TOV AS CT

WHERE (CT.CHECK_ID=C.ID)

GROUP BY C.NOMER, C.DATA, C.ID

HAVING (Sum(C.SUMMA)) NOT IN (SELECT SUM(SUMMA) FROM CHEKI);

16) Вывести  список изготовителей, товары  которых были проданы на наибольшую  сумму.

Основная идея решения задания  заключается в следующем. Необходимо

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

Запрос, возвращающий требуемую информацию, будет состоять из главного

запроса и вложенного запроса. В  главном запросе объединяем таблицы  ISGOTOVITEL, TOVAR  и   CH_TOV,  группируем результирующую таблицу по изготовителю.

Для отбора изготовителей применяем предложение HAVING, в котором используем многократное сравнение вида ">=ALL", левым операндом которого является максимальная сумма продажи, указанная в чеке, а правым операндом является вложенный запрос, возвращающий   изготовителей, товары которых включены в чеки с максимальной суммой продажи.

В режиме SQL запрос будет выглядеть следующим образом:

SELECT I.NASV, MAX(C.SUMMA) AS [MAX-SUMMA]

FROM ISGOTOVITEL AS I, TOVAR AS T, CH_TOV AS CT, CHEKI AS C

WHERE (CT.CHECK_ID=C.ID) AND (CT.TOVAR_ID=T.ID) AND (T.ISGOTOVITEL_ID=I.ID)

GROUP BY I.NASV, I.ID

HAVING (MAX(C.SUMMA))>=ALL(SELECT  MAX(SUMMA)

FROM  TOVAR AS T, CH_TOV AS CT, CHEKI AS C

WHERE (CT.CHECK_ID=C.ID) AND (CT.TOVAR_ID=T.ID)

GROUP BY T.ISGOTOVITEL_ID );

 

ЗАКЛЮЧЕНИЕ.

 

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

 

СПИСОК ИСПОЛЬЗУЕМЫХ ИСТОЧНИКОВ

 

1. Татаренко, С.И. Базы данных: Учебное пособие / С.И. Татаренко, В. А. Шиганцов. - Тамбов: ТГТУ, 2004. - 80 с.

2. Дьяков, И.А. Базы данных: Язык SQL: Учеб. пособие / И.А. Дьяков. - Тамбов: Изд-во ТГТУ, 2004. - 80 с.

3. Громов, Ю.Ю. Управление данными: учеб. пособие/ Ю.Ю. Громов, О.Г. Иванова, В.Н. Точка. - Тамбов: ТГТУ, 2009. - 80 с.

4. Дунаев, В.В. Базы данных. Язык SQL / В.В. Дунаев. - 2-е изд., доп. и перераб. - СПб.: БХВ-Петербург, 2007. - 320 с.: ил.

5. Малыгин, Е.Н. Разработка реляционных баз данных с использованием Microsoft Access / Е.Н. Малыгин, А.Б. Борисенко, С.В. Карпушкин. - Тамбов: ТГТУ, 2003. - 54с.

6. Гордиенко, А.В. Основы работы с СУБД Microsoft Access 2002: Метод. указания к лаб. работам по дисциплине "Базы данных" / А.В. Гордиенко, Н.А. Мясникова. - Новочеркасск: ЮРГТУ, 2004. - 19 с.

20