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

                                         производится

 

IV. “Чеки” - “Товары”

Необходимо  хранить информацию о кассовых чеках  с указанием кассира осуществившего продажу, а также о том, какие  товары были проданы по каждому чеку. Здесь реализуется связь «Многие-ко-многим» (М:N)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. РАЗРАБОТКА СХЕМЫ ДАННЫХ

 

Схему реляционной  базы данных изобразим в виде таблиц и связей между ними. При этом таблицы будут являться реализацией  сущностей, а поля таблицы - свойствами сущностей.

Ввиду того, что между сущностями "Чеки" и "Товар" существует связь "многие-ко-многим", информация о заказчиках каждого проекта будет храниться в отдельной таблице.

Помимо этого, выделим из перечисленных в таблице 1 свойств такие, которые будут  уникальным образом идентифицировать каждый экземпляр сущности (запись в таблице). С учетом выше изложенного  схему данных исследуемой предметной области представим на рисунке 1.

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

Типы  integer not null, varchar(*) not null, numeric(*,*) not null означают, что поля могут быть длинными целыми числами, не содержащими NULL. Тип varchar(*) означает, что поля содержат строку символов переменной длины. Тип date означает, что поля содержат календарную дату. Тип money означает что поле содержит денежный тип данных.

Поля, выделенные на схеме данных ключом, будут являться первичными ключами (PRIMARY KEY) таблиц. Поля оканчивающиеся на “_id”, будут являться внешними ключами, и будут иметь связи с другими таблицами.

 

 

 

Рисунок 1 - Логическая схема реляционной базы данных.

 

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

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

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

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

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

CREATE TABLE TOVAR

(ID COUNTER NOT NULL, 

NAZV VARCHAR(32),

GRUPPY_ID INTEGER,

PRIMARY KEY (ID),

FOREIGN KEY (GRUPPY_ID) REFERENCES GRUPPY);

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

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

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

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

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

CREATE TABLE TIP

(ID INTEGER NOT NULL, NASV VARCHAR(20) NOT NULL,

PRIMARY KEY (ID));

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

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

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

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

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

CREATE TABLE GRUPPY

(ID COUNTER NOT NULL, 

NAZV VARCHAR(32),

TIP_ID INTEGER,

PRIMARY KEY (ID),

FOREIGN KEY (TIP_ID) REFERENCES TIPY);

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

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

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

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

CREATE TABLE ISGOTOVITEL

(ID COUNTER NOT NULL, 

NAZV VARCHAR(20),

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,

PRIMARY KEY (ID),

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) Таблица  TIPY (Типы):

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

INSERT INTO TIPY (ID, NASV) (10, “Промышленный”);

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

DELETE

FROM TIPY

WHERE ID=10;

 

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

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

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

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

INSERT INTO GRUPPY (ID, NASV, TIP_ID) (5,"Одежда",7);

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

DELETE

FROM GRUPPY

WHERE ID=5;

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

UPDATE GRUPPY

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

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

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

INSERT INTO ISGTOVITEL (ID, NASV,ADRESS) (7, “ОАО фирма Молоко”, " 396650, Воронежская область, г. Россошь, ул. Л. Толстого, 53");

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

DELETE

FROM ISGTOVITEL

WHERE ID=7;

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

UPDATE ISGTOVITEL

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

WHERE ID=7;

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

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

INSERT INTO KASSIR (ID, FIO) (2, "Воеводкина Мария Петровна");

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

 

DELETE

FROM KASSIR

WHERE ID=3;

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

UPDATE KASSIR

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

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

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

INSERT INTO CHEKI (ID, NOMER, DATA, SUMMA, KASSIR_ID) (1, "101010", "06.03.2013", "1 500", 4);

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

DELETE

FROM CHEKI

WHERE KASSIR_ID=3;

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

UPDATE CHEKI

SET SUMMA=SUMMA*0.95

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

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

INSERT INTO CH_TOV (CHECK_ID, TOVAR_ID, KOLICH) ( 1, 3, "20");

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

DELETE

FROM CH_TOV

WHERE CHECK_ID=3, TOVAR_ID=4;

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

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) Вывести группы  товаров, средняя стоимость товаров  в которых превышает среднюю  стоимость, вычисленную по всем  товарам.

Для выполнения задания будем использовать два  запроса: запрос SELECT и запрос having  с подзапросом select. Для выведения информации об группе товара будем использовать предложение SELECT, FROM , WHERE и две таблицы GRUPPY и TOVAR. В предложение WHERE создадим связь между этими таблицами. Через предложение group by осуществим группировку строк по группам. Для того что бы вывести информацию об группе, средняя стоимость товаров в которых превышает среднюю стоимость, вычисленную по всем товарам нужно в предложении HAVING сделать подсчет строк товаров и с подзапросом вычислить среднюю стоимость товаров. С учетом положений оператора 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))>(SELECT AVG(PRICE) AS CENA

 FROM TOVAR);

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

Для выполнения задания будем использовать два запроса: запрос SELECT и запрос having  с подзапросом select. Для выведения информации об группе товара будем использовать предложение SELECT, FROM, WHERE и две таблицы KASSIR и CHEKI. В предложение WHERE создадим связь между этими таблицами. Через предложение group by осуществим группировку

 

  строк по фио кассиров. Для того что бы вывести список кассиров с нулевой суммой продаж нужно в предложении HAVING сделать подсчет строк кассиров и с подзапросом вычислить минимальную сумму продаж товаров. С учетом положений оператора SQL будет выглядеть следующим образом:

SELECT K.FIO, MIN(SUMMA)

FROM KASSIR AS K, CHEKI AS C

WHERE C.KASSIR_ID=K.ID

GROUP BY K.ID, K.FIO

HAVING (MIN(SUMMA))=(SELECT MIN(SUMMA)

FROM CHEKI );

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

Для выполнения задания будем использовать два  запроса: запрос SELECT и запрос having  с подзапросом select. Для выведения информации об группе товара будем использовать предложение SELECT, FROM , WHERE и две таблицы CHEKI  и CH_TOV. В предложение WHERE создадим связь между этими таблицами. Через предложение group by осуществим группировку строк по номерам чеков. Для того что бы вывести номера и даты чеков, сумма которых не совпадает с суммарной стоимостью товаров, указанных в чеке нужно в предложении HAVING сделать подсчет строк номеров и с подзапросом вычислить сумму товаров в чеке. С учетом положений оператора SQL будет выглядеть следующим образом:

SELECT C.NOMER, C.DATA, Sum(C.SUMMA) AS [SUM]

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]))<>(SELECT SUM(SUMMA) FROM CHEKI)));

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

 

Для выполнения задания будем использовать два  запроса: запрос SELECT и запрос having  с подзапросом select. Для выведения информации об группе товара будем использовать предложение SELECT, FROM , WHERE и три таблицы ISGOTOVITEL, TOVAR и CH_TOV. В предложение WHERE создадим связь между этими таблицами. Через предложение group by осуществим группировку строк по изготовителям. Для того что бы вывести список изготовителей, товары которых были проданы на наибольшую сумму нужно в предложении HAVING сделать подсчет строк изготовителей   и с подзапросом вычислить максимальную сумму товаров. С учетом положений оператора SQL будет выглядеть следующим образом:

SELECT I.NASV, MX(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]))=(SELECT MAX(SUMMA)

FROM CHEKI )));

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ЗАКЛЮЧЕНИЕ

 

Эффективное развитие государства немыслимо  без систем управления. Современные  системы управления базируются на современных информационных технологиях, на комплексных системах обработки информации.

Современные системы компьютерного управления обеспечивают:

1. Определение тенденций изменения важных показателей;

2. Получение информации во времени без задержек;

3. Выполнение точного и полного анализа данных.

 

 

 

 

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

1. Роланд Фред. Основные концепции баз данных. Вильямс. 2002

2. Ульман Дж., Уидом Дж. Введение в системы баз данных. М. Лори. 2000.

3. Федоров Д., Елманова Н. Базы данных для всех. М. Компьютер-пресс, 2001.