Заключение. 
Разработка базы данных для автоматизированной системы управления "Продажа компьютерной техники"

Курсовая работа посвящена разработке базы данных магазина «ТехноТорг».

Решены следующие задачи:

• · Произведен системный анализ предметной области;
• · Построена инфологическая модель;
• · Построена реляционная модель;
• · Реализация базы данных в СУБД Oracle apex.

Разработана реляционная база данных, содержащая элементы автоматизации и обработки данных. В ее составе: 10 таблиц, 3 триггера.

Список литературы

• 1. Воронова Л.И. «Лабораторный практикум по дисциплине „базы данных“» — Москва 2010 г.
• 2. ГОСТ 2.105−95 Единая система конструкторской документации. Общие требования к текстовым документам
• 3. Гуриков С. Р.

  Введение

  в программирование на языке Visual C#: учебное пособие / С. Р. Гуриков. — М.: ФОРУМ: ИНФРА-М, 2013. — 448 с. — (Высшее образование. Бакалавриат).

• 4. Карпова Т. С. «Базы Данных» — Питер, 2002 г. — Режим доступа: http://biblioteka.cc/index.php?newsid=74 396
• 5. Компактная встраиваемая реляционная база данных SQLite: sqlite.org
• 6. Крёнке Д. «Теория и практика построения баз данных» — Питер, 2003 г.-800с.
• 7. Левченко Ольга: [Электронный ресурс] Статья «Microsoft SQL Server» — режим доступа: http://bourabai.kz/dbt/servers/MicrosoftSQLServer.htm
• 8. Работа с MS Word в C# [Электронный ресурс]. — Режим доступа: http://wladm.narod.ru/C_Sharp/comword.html.
• 9. Свободная реляционная СУБД MySQL: mysql.com
• 10. Система управления реляционными базами данных разработанная корпорацией Microsoft: https://www.microsoft.com/en-us/server-cloud/products/sql-server/#fbid=JQk2DSKWQLz
• 11. СУБД Oracle: http://www.oracle.com/technetwork/database/database-t.
• 12. Microsoft Access — реляционная СУБД: https://products.office.com/ru-RU/access

Приложение

Программный код Создание таблицы Клиенты.

create table CLIENT (.

ID_Client int PRIMARY KEY,.

FIO varchar (25) NOT NULL,.

Birth_Date date NOT NULL,.

Phone varchar (20) NOT NULL,.

Address varchar (50) NOT NULL,.

ZIP int NOT NULL);

Create Sequence ID_Cl Increment by 1 start with 1;

Insert into CLIENT (ID_Client, FIO, Birth_Date, Phone, Address, ZIP).

Values (ID_Cl.NextVal,'Shirokov V.N.', TO_DATE ('1980/01/01','YYYY/MM/DD'), '9 183 212 115', 'Moscow, Volgogradskaya, 31−15','111 352');

Insert into CLIENT (ID_Client, FIO, Birth_Date, Phone, Address, ZIP).

Values (ID_Cl.NextVal,'Malaxova O.U.', TO_DATE ('1990/11/22','YYYY/MM/DD'), '9 031 823 574', 'Moscow, Komsomolskaya, 64−12','196 182');

Insert into CLIENT (ID_Client, FIO, Birth_Date, Phone, Address, ZIP).

Values (ID_Cl.NextVal,'Nikolaev A.N.', TO_DATE ('1989/04/13','YYYY/MM/DD'), '9 032 183 131','Moscow, Zelenaya, 25−18','315 112');

Создание таблицы Продавцы.

create table SELLER (.

ID_Seller int PRIMARY KEY,.

FIO varchar (25) NOT NULL,.

Birth_Date date NOT NULL,.

Pasport int NOT NULL,.

Phone varchar (20) NOT NULL,.

Address varchar (50) NOT NULL,.

Date_Priema date NOT NULL);

Create Sequence ID_Sel Increment by 1 start with 1;

Insert into SELLER (ID_Seller, FIO, Birth_Date, Pasport, Phone, Address, Date_Priema).

Values (ID_Sel.NextVal,'Ivanov I.I.', TO_DATE ('1991/05/13','YYYY/MM/DD'),'6 115 348 243','9 051 237 816','Moscow, Aviamotornaya, 34−8', TO_DATE ('2015/02/05','YYYY/MM/DD'));

Insert into SELLER (ID_Seller, FIO, Birth_Date, Pasport, Phone, Address, Date_Priema).

Values (ID_Sel.NextVal,'Alekseeva N.D.', TO_DATE ('1987/04/21','YYYY/MM/DD'),'6 211 354 218','9 203 211 515','Moscow, Volgogradskaya, 7−64', TO_DATE ('2016/03/21','YYYY/MM/DD'));

Insert into SELLER (ID_Seller, FIO, Birth_Date, Pasport, Phone, Address, Date_Priema).

Values (ID_Sel.NextVal,'Smirnov N.I.', TO_DATE ('1990/09/21','YYYY/MM/DD'),'3 208 114 586','9 207 421 881','Moscow, Avtozavodskaya, 16−5', TO_DATE ('2015/01/17','YYYY/MM/DD'));

Insert into SELLER (ID_Seller, FIO, Birth_Date, Pasport, Phone, Address, Date_Priema).

Values (ID_Sel.NextVal,'Xoxlova I.V.', TO_DATE ('1987/02/01','YYYY/MM/DD'),'6 512 132 476','9 031 283 516','Tula, Biruzova, 13−17', TO_DATE ('2014/05/30','YYYY/MM/DD'));

Insert into SELLER (ID_Seller, FIO, Birth_Date, Pasport, Phone, Address, Date_Priema).

Values (ID_Sel.NextVal,'Krasnova E.A.', TO_DATE ('1992/08/29','YYYY/MM/DD'),'3 842 132 115','9 037 422 835','Moscow, Uznaya, 24−64', TO_DATE ('2016/09/24','YYYY/MM/DD'));

Создание таблицы Менеджеры.

create table MANAGER (.

ID_Manager int PRIMARY KEY,.

FIO varchar (25) NOT NULL,.

Birth_Date date NOT NULL,.

Pasport int NOT NULL,.

Phone varchar (20) NOT NULL,.

Address varchar (50) NOT NULL,.

Date_Priema date NOT NULL);

Create Sequence ID_Man Increment by 1 start with 1;

Insert into MANAGER (ID_Manager, FIO, Birth_Date, Pasport, Phone, Address, Date_Priema).

Values (ID_Man.NextVal,'Rublev A.I.', TO_DATE ('1984/01/13','YYYY/MM/DD'),'1 235 748 212','9 035 721 801','Moscow, Svobodnaya, 30−2', TO_DATE ('2014/12/13','YYYY/MM/DD'));

Insert into MANAGER (ID_Manager, FIO, Birth_Date, Pasport, Phone, Address, Date_Priema).

Values (ID_Man.NextVal,'Ivanova A.M.', TO_DATE ('1987/04/20','YYYY/MM/DD'),'6 117 841 211','9 213 151 694','Moscow, Bolshaya, 18−24', TO_DATE ('2015/01/17','YYYY/MM/DD'));

Insert into MANAGER (ID_Manager, FIO, Birth_Date, Pasport, Phone, Address, Date_Priema).

Values (ID_Man.NextVal,'Soboleva I.N.', TO_DATE ('1991/12/03','YYYY/MM/DD'),'3 151 821 322','9 037 541 118','Moscow, Svobodi, 21−24', TO_DATE ('2015/08/02','YYYY/MM/DD'));

Insert into MANAGER (ID_Manager, FIO, Birth_Date, Pasport, Phone, Address, Date_Priema).

Values (ID_Man.NextVal,'Vitko A.G.', TO_DATE ('1990/10/10','YYYY/MM/DD'),'6 215 312 418','9 203 182 111','Moscow, Pushkina, 31−21', TO_DATE ('2016/09/15','YYYY/MM/DD'));

Создание таблицы Категории.

create table CATEGORY (.

ID_Category int PRIMARY KEY,.

Name varchar (25) NOT NULL);

Create Sequence ID_Cat Increment by 1 start with 1;

Insert into CATEGORY (ID_Category, Name).

Values (ID_Cat.NextVal, 'Telephone');

Insert into CATEGORY (ID_Category, Name).

Values (ID_Cat.NextVal, 'Notebook');

Insert into CATEGORY (ID_Category, Name).

Values (ID_Cat.NextVal, 'Planshet');

Insert into CATEGORY (ID_Category, Name).

Values (ID_Cat.NextVal, 'Aksessuari');

Insert into CATEGORY (ID_Category, Name).

Values (ID_Cat.NextVal, 'PhotoCamera');

Insert into CATEGORY (ID_Category, Name).

Values (ID_Cat.NextVal, 'VideoCamera');

Создание таблицы Производитель.

create table MAKER (.

ID_Maker int PRIMARY KEY,.

Name varchar (25) NOT NULL,.

Country varchar (25) NOT NULL,.

Site varchar (50) NOT NULL,.

Phone int NOT NULL);

Create Sequence ID_Maker Increment by 1 start with 1;

Insert into MAKER (ID_Maker, Name, Country, Site, Phone).

Values (ID_Maker.NextVal, 'ASUS','China','asus.ru','88 001 002 787');

Insert into MAKER (ID_Maker, Name, Country, Site, Phone).

Values (ID_Maker.NextVal, 'Huawei','China','shop.huawei.ru','84 953 201 221');

Insert into MAKER (ID_Maker, Name, Country, Site, Phone).

Values (ID_Maker.NextVal, 'Lenovo','China','lenovo.com','861 058 868 888');

Insert into MAKER (ID_Maker, Name, Country, Site, Phone).

Values (ID_Maker.NextVal, 'Nikon','Japan','nikon.ru','84 952 216 912');

Insert into MAKER (ID_Maker, Name, Country, Site, Phone).

Values (ID_Maker.NextVal, 'Canon','Japan','canon.ru','84 952 585 601');

Insert into MAKER (ID_Maker, Name, Country, Site, Phone).

Values (ID_Maker.NextVal, 'Xiaomi','China','ru-xiaomi.com','84 991 109 938');

Insert into MAKER (ID_Maker, Name, Country, Site, Phone).

Values (ID_Maker.NextVal, 'PocketBook','Ukraine','pocketbook-int.com', '88 007 009 327');

Insert into MAKER (ID_Maker, Name, Country, Site Phone).

Values (ID_Maker.NextVal, 'Sony','Japan','sony-russia.com','84 951 252 446');

Создание таблицы Продажа товара.

create table CHEK_INFO (.

ID_Chek int PRIMARY KEY,.

ID_Seller int NOT NULL,.

Kolichestvo int NOT NULL);

Create Sequence ID_Chek Increment by 1 start with 1;

alter table CHEK_INFO.

ADD CONSTRAINT SellerFK FOREIGN KEY (ID_Seller) REFERENCES SELLER;

Insert into CHEK_INFO (ID_Chek, ID_Seller, Kolichestvo).

Values (ID_Chek.NextVal, '2','5000');

Insert into CHEK_INFO (ID_Chek, ID_Seller, Kolichestvo).

Values (ID_Chek.NextVal, '3','7200');

Создание таблицы Товары.

create table PRODUCT (.

ID_Product int PRIMARY KEY,.

Name varchar (30) NOT NULL,.

ID_Category int NOT NULL,.

ID_Maker int NOT NULL,.

Garanty varchar (15) NOT NULL,.

Price int NOT NULL);

Create Sequence ID_Pr Increment by 1 start with 1;

alter table PRODUCT.

ADD CONSTRAINT CategoryFK FOREIGN KEY (ID_Category) REFERENCES CATEGORY;

alter table PRODUCT.

ADD CONSTRAINT MakerFK FOREIGN KEY (ID_Maker) REFERENCES MAKER;

Insert into PRODUCT (ID_Product, Name, ID_Category, ID_Maker, Garanty, Price).

Values (ID_Pr.NextVal, 'ASUS GL552VX','2','1','2 year','57 000');

Insert into PRODUCT (ID_Product, Name, ID_Category, ID_Maker, Garanty, Price).

Values (ID_Pr.NextVal, 'Xiaomi Redmi 4','1','6','2 year','7900');

Insert into PRODUCT (ID_Product, Name, ID_Category, ID_Maker, Garanty, Price).

Values (ID_Pr.NextVal, 'Lenovo Vibe','1','3','1 year','5000');

Insert into PRODUCT (ID_Product, Name, ID_Category, ID_Maker, Garanty, Price).

Values (ID_Pr.NextVal, 'Huawei Nedia Pad','3','2','1 year','7200');

Создание таблицы Информация о чеке.

create table CHEK (.

ID_Chek int NOT NULL,.

ID_Product int NOT NULL,.

Summa int NOT NULL);

alter table CHEK ADD CONSTRAINT PK_CEK PRIMARY KEY (ID_Chek, ID_Product);

alter table CHEK.

ADD CONSTRAINT ChekFK FOREIGN KEY (ID_Chek) REFERENCES CHEK_INFO;

alter table CHEK.

ADD CONSTRAINT MProduct_FK FOREIGN KEY (ID_PRODUCT) REFERENCES PRODUCT;

Insert into CHEK (ID_Chek, ID_Product, Summa).

Values ('1', '3','1');

Insert into CHEK (ID_Chek, ID_Product, Summa).

Values ('2', '4','1');

Создание таблицы Доставка.

create table DELIVERY (.

ID_Delivery int PRIMARY KEY,.

Price_Delivery int NOT NULL,.

Address varchar (50) NOT NULL,.

Phone int NOT NULL,.

Date_Delivery date NOT NULL,.

ID_Manager int NOT NULL,.

ID_Order int NOT NULL);

Create Sequence ID_Del Increment by 1 start with 1;

alter table DELIVERY.

ADD CONSTRAINT ManagerFK FOREIGN KEY (ID_Manager) REFERENCES MANAGER;

Insert into DELIVERY (ID_Delivery, ID_Delivery, Address, Phone, Date_Delivery, ID_Manager, ID_Order).

Values (ID_Del.NextVal,'57 000','Moscow, Zelenya, 25−18', '9 032 183 131', TO_DATE ('2016/03/20','YYYY/MM/DD'),'3');

Insert into DELIVERY (ID_Delivery, ID_Delivery, Address, Phone, Date_Delivery, ID_Manager, ID_Order).

Values (ID_Del.NextVal,'7900','Moscow, Komsomolskaya, 64−12', '9 031 823 574', TO_DATE ('2017/04/20','YYYY/MM/DD'),'3');

Insert into DELIVERY (ID_Delivery, ID_Delivery, Address, Phone, Date_Delivery, ID_Manager, ID_Order).

Values (ID_Del.NextVal,'7900','Moscow, Volgogradskaya, 31−5','9 183 212 115', TO_DATE ('2017/04/13','YYYY/MM/DD'),'1');

Создание таблицы Заказы.

create table ORDERS (.

ID_Order int PRIMARY KEY,.

ID_Product int NOT NULL,.

ID_Client int NOT NULL,.

Kolichestvo int NOT NULL,.

Date_Order date NOT NULL,.

Summa int NOT NULL);

Create Sequence ID_Or Increment by 1 start with 1;

alter table ORDERS.

ADD CONSTRAINT ProductFK FOREIGN KEY (ID_Product) REFERENCES PRODUCT;

alter table ORDERS.

ADD CONSTRAINT ClientFK FOREIGN KEY (ID_Client) REFERENCES CLIENT;

alter table ORDERS.

ADD CONSTRAINT DeliveryFK FOREIGN KEY (ID_Delivery) REFERENCES DELIVERY;

Insert into ORDERS (ID_Order, ID_Product, ID_Client, Kolichestvo, Date_Order, Summa, ID_Delivery).

Values (ID_Or.NextVal,'2','3','1', TO_DATE ('2016/03/20','YYYY/MM/DD'),'57 000','1');

Insert into ORDERS (ID_Order, ID_Product, ID_Client, Kolichestvo, Date_Order, Summa, ID_Delivery).

Values (ID_Or.NextVal,'3','2','1', TO_DATE ('2017/03/31','YYYY/MM/DD'),'7900','2');

Insert into ORDERS (ID_Order, ID_Product, ID_Client, Kolichestvo, Date_Order, Summa, ID_Delivery).

Values (ID_Or.NextVal,'3','1', '1', TO_DATE ('2017/04/11','YYYY/MM/DD'),'7900','3');