Проектирование БД на языке SQL

                                   

                                        ФГОУ СПО Серпуховский Технический  Колледж

 

 

 

 

 

 

 

 

 

 

 

 

            

 

                   Отчёт по контрольной работе

                    по дисциплине базы данных

     на тему: «проектирование  БД на языке SQL».

 

                                                                                                           

                   

                                                                                                

 

 

 

 

 

 

 

 

 

 

 

                                                                                                    Выполнила:

                                                                                                                    студентка 4Э группы

                                                                                                                     Никонова Валентина                                                   

                                                                                                                     Проверила:

                                                                                                                     Черникова Л.В. 

 

                                                           

 

 

 

 

                                                                          

                  

                                                                           2012

 

 

 

                                                                СОДЕРЖАНИЕ

 

1. Описание структуры таблиц........................................................................3
2. Схема данных................................................................................................4
3. Операторы SQL для создания структуры таблиц первичных и внешних

ключей, индексов..........................................................................................5

4. SQL операторы для заполнения таблиц данными.....................................6
5. Запросы на естественном языке и в виде SQL операторов......................7
6. Определение набора прав для двух пользователей...................................7

 

 

                             

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

                                    Описание структуры таблиц.

1)  Единица измерения

Поле	Краткое наименование	Тип данных	Обязательное поле	Значение по умолчанию	Описание
Код единицы измерения	КОДЕИ	Числовое целое	Да	-	Уникальный индетификатор строки, ссылается таблица ТОВАР
Единица измерения	ЕИ	Текстовый	Да	-	Обозначение величины принятое для измерения  данной величины

2)  Товар

Поле	Краткое наименование	Тип данных	Обязательное поле	Значение по умолчанию	Описание
Код товара	КОДТОВАРА	Числовое целое	Да	-	Уникальный индетификатор строки, ссылается таблица ПОСТ
Наименование товара	НАИМТОВАРА	Текстовый	Да	-	Наименование товара
Код единицы измерения	КОДЕИ	Числовое целое	Да	-	Код единицы измерения

3)  Постава

Поле	Краткое наименование	Тип данных	Обязательное поле	Значение по умолчанию	Описание
Номер счёта фактуры	НОМЕРСФ	Числовое целое	Да	-	Индетификатор строки в таблице СФ
Код товара	КОДТОВАРА	Числовое целое	Да	-	Индетификатор строки в таблице ТОВАР
Количество	КОЛ	Числовое целое	Да	-	Численное измерение товаров
Цена	ЦЕНА	Денежный	Да	-	Сумма за товар

4)  Счёт-фактура

Поле	Краткое наименование	Тип данных	Обязательное поле	Значение по умолчанию	Описание
Номер счёт-фактуры	НОМЕРСФ	Числовое целое	Да	-	Уникальный индетификатор строки, ссылается таблица ПОСТ
Дата	ДАТА	Дата	Да	-	Дата, на которую оформлен счёт-фактура
ИНН покупателя	ИННПКП	Числовое целое	Да	«0»	ИНН покупателя
НДС	НДС	Числовое целое	Да	-	НДС

5)  Покупатель

Поле	Краткое наименование	Тип данных	Обязательное поле для заполнения	Значение по умолчанию	Описание
ИНН покупателя	ИННПКП	Числовое целое	Да	«0»	Уникальный индетификатор строки, ссылается таблица СФ
ФИО покупателя	ФИО	Текстовый	Да	-	Фамилия, имя, отчество покупателя
Адрес покупателя	АДРЕС	Текстовый	Да	-	Юридический адрес покупателя
Телефон покупателя	ТЕЛЕФОН	Текстовый	Да	-	Телефон покупателя

 

     

 

           Операторы SQL для создания структуры таблиц первичных и     

                                        внешних ключей, индексов

 

1) Единица измерения:

- создание структуры  таблицы:

CREATE TABLE ЕИ

(КОДЕИ SHORT NOT NULL UNIQUE, ЕИ VARCHAR NOT NULL);

- создание первичного  ключа:

ALTER TABLE ЕИ

ADD CONSTRAINT ЕИ_КОДЕИ

PRIMARY KEY (КОДЕИ);

- создание индексов:

CREATE INDEX ЕИ_INDEX

ON ЕИ (КОДЕИ, ЕИ).

 

2) Товар:

- создание структуры  таблицы:

CREATE TABLE ТОВАР

(КОДТОВАРА SHORT NOT NULL UNIQUE, НАИМТОВАРА VARCHAR NOT NULL, КОДЕИ SHORT NOT NULL);

- создание первичного  ключа:

ALTER TABLE ТОВАР

ADD CONSTRAINT ТОВАР_КОДТОВАРА

PRIMARY KEY (КОДТОВАРА);

- создание внешнего  ключа:

ALTER TABLE ТОВАР

ADD CONSTRAINT FK_ТОВАР_КОДЕИ

FOREIGN KEY (КОДЕИ)

REFERENCES ЕИ (КОДЕИ);

- создание индексов:

CREATE INDEX ТОВАР_INDEX

ON ТОВАР (КОДТОВАРА, НАИМТОВАРА, КОДЕИ).

 

3) Поставка:

- создание структуры  таблицы:

CREATE TABLE ПОСТ

(НОМЕР SHORT NOT NULL, КОДТОВАРА SHORT NOT NULL, КОЛ SHORT NOT NULL, ЦЕНА MONEY NOT NULL);

- создание внешнего  ключа:

1) ALTER TABLE ПОСТ

ADD CONSTRAINT FK_ПОСТ_КОДТОВАРА

FOREIGN KEY (КОДТОВАРА)

REFERENCES ТОВАР (КОДТОВАРА);

2) ALTER TABLE ПОСТ

ADD CONSTRAINT FK_ПОСТ_НОМЕРСФ

FOREIGN KEY (НОМЕРСФ)

REFERENCES СФ (НОМЕРСФ);

- создание индексов:

CREATE INDEX ПОСТ_INDEX

ON ПОСТ (НОМЕРСФ, КОДТОВАРА, КОЛ,  ЦЕНА).

 

4) Счёт-фактура:

- создание структуры  таблицы:

CREATE TABLE СФ

(НОМЕРСФ SHORT NOT NULL UNIQUE, ДАТА DATE NOT NULL, ИНН SHORT NOT NULL, НДС SHORT NOT NULL);

- создание первичного  ключа:

ALTER TABLE СФ

ADD CONSTRAINT СФ_НОМЕРСФ

PRIMARY KEY (НОМЕРСФ);

 

- создание внешнего  ключа:

ALTER TABLE СФ

ADD CONSTRAINT FK_СФ_ИННПКП

FOREIGN KEY (ИННПКП)

REFERENCES ПКП (ИННПКП);

- создание индексов:

CREATE INDEX СФ_INDEX

ON СФ (НОМЕРСФ, ДАТА, ИННПКП, НДС).

 

5) Покупатель:

- создание структуры  таблицы:

CREATE TABLE ПКП

(ИННПКП SHORT NOT NULL UNIQUE, ФИО VARCHAR NOT NULL, АДРЕС VARCHAR NOT NULL,

ТЕЛЕФОН VARCHAR NOT NULL);

- создание первичного  ключа:

ALTER TABLE ПКП

ADD CONSTRAINT ПКП_ИННПКП

PRIMARY KEY (ИННПКП);

- создание индексов:

CREATE INDEX ПКП_INDEX

ON ПКП (ИННПКП, ФИО, АДРЕС, ТЕЛЕФОН).

 

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

 

1) Единица измерения:

INSERT INTO ЕИ

VALUES (1, "КГ");

INSERT INTO ЕИ

VALUES (2, "ШТ");

INSERT INTO ЕИ

VALUES (3, "М");

 

2) Товар:

INSERT INTO ТОВАР

VALUES (10, "МУКА", "1");

INSERT INTO ТОВАР

VALUES (20, "СТОЛ", "2");

INSERT INTO ТОВАР

VALUES (30, "ТКАНЬ", "3");

 

3) Поставка:

INSERT INTO ПОСТ

VALUES (111, "10", "3", "150");

INSERT INTO ПОСТ

VALUES (222, "20", "7", "200");

INSERT INTO ПОСТ

VALUES (333, "30", "5", "500");

 

4) Счёт-фактура:

INSERT INTO СФ

VALUES (111, "15.12.2012", "8090", "18");

INSERT INTO СФ

VALUES (222, "16.09.2012", "8100", "10");

INSERT INTO СФ

VALUES (333, "08.04.2011", "8110", "18");

 

 

 

 

 

5) Покупатель:

INSERT INTO ПКП

VALUES (8090, "ПЕТРОВ ПЁТР ПЕТРОВИЧ", "Г.СЕРПУХОВ", "496-19-147");

INSERT INTO ПКП

VALUES (8100, "ИВАНОВ ИВАН ИВАНОВИЧ", "Г.ПРОТВИНО", "496-90-150");

INSERT INTO ПКП

VALUES (8110, "СИДОРОВ СЕМЁН СЕМЁНОВИЧ", "Г.ЧЕХОВ", "496-20-220");

 

                Запросы на естественном языке и в виде SQL операторов

 

1) Удалить из таблицы ТОВАР не поступивший на склад

DELETE ТОВАР.НАИМТОВАРА

FROM ТОВАР

WHERE (((ТОВАР.НАИМТОВАРА)=[ВВЕДИТЕ НАЗВАНИЕ  ТОВАРА]));

 

2) Рассчитать стоимость всех товаров и общее количество поступивших товаров:

SELECT Sum(ПОСТ.ЦЕНА) AS [Sum-ЦЕНА], Sum(ПОСТ.КОЛ) AS [Sum-КОЛ]

FROM ПОСТ;

 

3) Увеличить цену всех товаров  на 18%:

UPDATE ПОСТ SET ЦЕНА = ЦЕНА*0.18;

 

4) Выбрать из таблицы ПКП все  адреса и ФИО покупателей:

SELECT ФИО, АДРЕС

FROM ПКП;

 

5) Выбрать все ФИО из таблицы  ПКП начинающиеся с «ПЕТРОВ»

SELECT *

FROM ПКП

WHERE ФИО LIKE "ПЕТРОВ*";

 

                       Определение набора прав для двух пользователей

 

Предположим, зарегистрированы такие пользователи как BOSS – начальник; РD– сотрудник.

1. FULLER – добавление, удаление и редактирование всех данных.
2. READR – просмотр данных.

 

CREATE ROLE FULLER

CREATE ROLE READR

 

GRANT ALL ON ПКП TO FULLER

GRANT ALL ON СФ TO FULLER

 

GRANT ALL ON ЕИ TO READR

GRANT ALL ON ТОВАР TO READR

GRANT ALL ON ПОСТ TO READR

 

GRANT FULLER TO РD

GRANT READR TO BOSS