НАЦІОНАЛЬНИЙ АВІАЙЦІЙНИЙ УНІВЕРСИТЕТ
Факультет кібербезпеки, комп’ютерної та програмної інженерії
Кафедри інженерії програмного забезпечення
КУРСОВА РОБОТА
з дисципліни “Бази даних”
на тему: База даних підрозділів побутової техніки різних фірм
Прийняв: Виконав:
Старший викладач студент 3 курсу
Марченко О.І. групи ПІ-323
напрям 121 ‘інженеріяПЗ’
Бруль М.К.
Київ – 2021
Зміст
Опис предметної області……………………………………....3-3
Бізнес правила………………………………………………..…3-3
Набір сутностей…………………………………………..……..3-3
Набір атрибутів………………………………………………....4-5
ER-модель…………………………………………………….…5-5
Реалізація БД…………………………………………………...6-16
Запити…………………………………………………………..16-22
Висновок……………………………………………………….23-23
Основна частина
Опис предметної області
Фірма виготовляє побутову техніку різних підрозділів, а саме для: кухні, ванної, дому та сантехніка. Магазин може зробити оптове замовлення товару від виробника або людина може замовити сама , для цього оформлюється замовлення, після відправляється до магазину чи людині.
Фірми:
Назва
Країна
Місто
Контактний телефон
Кухня:
Виробник
Код виробу
Тип виробу
Модель
Початок виробництва
К-ть виробів за рік
Опис
Ціна за шт.
Ванна кімната:
Виробник
Код виробу
Тип виробу
Модель
Початок виробництва
К-ть виробів за рік
Опис
Ціна за шт.
Дім:
Виробник
Код виробу
Тип виробу
Модель
Початок виробництва
К-ть виробів за рік
Опис
Ціна за шт.
Сантехніка:
Виробник
Код виробу
Тип виробу (мікрохвильовка тощо)
Модель
Початок виробництва
К-ть виробів за рік
Опис
Ціна за шт.
ER-модель
Створюємо БД, що була спроектована
Створення бд:
CREATE TABLE FIRMS
( FirmsName varchar2(255) not null,
Country varchar2(255) not null,
City varchar2(255) not null,
ContactNumber number(20) not null
);
create table Kitchen
( ManufacName_FK varchar2(255) not null,
ProductID_PK number not null,
TypeK varchar2(255) not null,
Model varchar2(255) not null,
StartYear date,
CountPerYear number,
Describe varchar2(255),
Price number not null
);
create table Bathroom(
ManufacName_FK varchar2(255) not null,
ProductID_PK number not null,
TypeB varchar2(255) not null,
Model varchar2(255) not null,
StartYear date,
CountPerYear number,
Describe varchar2(255),
Price number not null
);
create table House(
ManufacName_FK varchar2(255) not null,
ProductID_PK number not null,
TypeH varchar2(255) not null,
Model varchar2(255) not null,
StartYear date,
CountPerYear number,
Describe varchar2(255),
Price number not null
);
create table Sanitary(
ManufacName_FK varchar2(255) not null,
ProductID_PK number not null,
TypeSE varchar2(255) not null,
Model varchar2(255) not null,
StartYear date,
CountPerYear number,
Describe varchar2(255),
Price number not null
);
create table Ordering(
OrderID_PK number not null,
ProductID_FK number not null,
ManufacName_FK varchar2(255) not null,
WareCount number not null
);
create table OrderShipping(
OrderID_FK number not null,
CustomerName varchar2(255) not null,
CustomerPhone number(20) not null
);
/*ключі*/
alter table FIRMS add PRIMARY KEY (FirmsName);
alter table Kitchen add PRIMARY KEY (ProductID_PK);
alter table Bathroom add PRIMARY KEY (ProductID_PK);
alter table House add PRIMARY KEY (ProductID_PK);
alter table Sanitary add PRIMARY KEY (ProductID_PK);
alter table Ordering add PRIMARY KEY (OrderID_PK);
alter table Kitchen add CONSTRAINT FK_ManfName FOREIGN key(ManufacName_FK) REFERENCES FIRMS(FirmsName);
alter table Bathroom add CONSTRAINT FK_ManfName22 FOREIGN key(ManufacName_FK) REFERENCES FIRMS(FirmsName);
alter table House add CONSTRAINT FK_ManfName11 FOREIGN key(ManufacName_FK) REFERENCES FIRMS(FirmsName);
alter table Sanitary add CONSTRAINT FK_ManfName33 FOREIGN key(ManufacName_FK) REFERENCES FIRMS(FirmsName);
alter table Ordering add CONSTRAINT FK_ProdID1 foreign key(ProductID_FK) references Kitchen(ProductID_PK);
alter table Ordering add CONSTRAINT FK_ProdID2 foreign key(ProductID_FK) references Bathroom(ProductID_PK);
alter table Ordering add CONSTRAINT FK_ProdID3 foreign key(ProductID_FK) references House(ProductID_PK);
alter table Ordering add CONSTRAINT FK_ProdID4 foreign key(ProductID_FK) references FIRMS(FirmsName);
alter table Ordering add CONSTRAINT FK_ManufacName FOREIGN key(ManufacName_FK) references FIRMS(FirmsName);
alter table OrderShipping add CONSTRAINT FK_OrderID2 foreign key(OrderID_FK) references Ordering(OrderID_PK);
/*додавання елементів*/
insert into FIRMS values('Bosch', 'Germany', 'Gunzengausen', 499831560);
insert into FIRMS values('Gorenje','Ukraine', 'Kyiv', 0800300024);
insert into FIRMS values('Phillips', 'Nederlang', 'Amsterdam', 31205977777);
select * from FIRMS;
INSERT INTO Kitchen (MANUFACNAME_FK, PRODUCTID_PK, TYPEK, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Bosch', '33322', 'Плита', 'Jj1', TO_DATE('2019-10-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5201', 'Жарится варится все что угодно и кто угодно', '246')
INSERT INTO Kitchen (MANUFACNAME_FK, PRODUCTID_PK, TYPEK, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Bosch', '33323', 'Микроволновка', 'Zm', TO_DATE('2019-11-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '6109', 'Разогреет тарелку', '51')
INSERT INTO Kitchen (MANUFACNAME_FK, PRODUCTID_PK, TYPEK, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Bosch', '33324', 'Кофеварка', 'Bodryachek', TO_DATE('2020-01-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5381', 'Бодрит с утра,возмодет стук в сердечке', '40')
INSERT INTO Kitchen (MANUFACNAME_FK, PRODUCTID_PK, TYPEK, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Bosch', '33325', 'Кипятильник', 'Coil', TO_DATE('2019-09-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9002', 'Кипятит воду', '3')
INSERT INTO Kitchen (MANUFACNAME_FK, PRODUCTID_PK, TYPEK, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Gorenje', '11111', 'Бойлер', 'Heat', TO_DATE('2020-01-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '3412', 'Економит горячею воду', '310')
INSERT INTO Kitchen (MANUFACNAME_FK, PRODUCTID_PK, TYPEK, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Gorenje', '11112', 'Стиралка', 'WearClean', TO_DATE('2020-01-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '3204', 'Стирает вещи', '220')
INSERT INTO Kitchen (MANUFACNAME_FK, PRODUCTID_PK, TYPEK, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Gorenje', '11113', 'Микроволновка', 'Jas2', TO_DATE('2020-01-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '3520', 'Греет еду', '70')
INSERT INTO Kitchen (MANUFACNAME_FK, PRODUCTID_PK, TYPEK, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Phillips', '55551', 'Пылесос', 'Susd', TO_DATE('2018-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5010', 'Хорошо убирает крошки с пола', '200')
INSERT INTO Kitchen (MANUFACNAME_FK, PRODUCTID_PK, TYPEK, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Phillips', '55552', 'Фен', 'Aeq1', TO_DATE('2019-02-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '6103', 'Сушит волосы', '35')
INSERT INTO Bathroom (MANUFACNAME_FK, PRODUCTID_PK, TYPEB, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Bosch', '12221', 'Бойлер', 'Css', TO_DATE('2020-02-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5021', 'Греет воду', '500')
INSERT INTO Bathroom (MANUFACNAME_FK, PRODUCTID_PK, TYPEB, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Bosch', '12222', 'Тример', 'Llw', TO_DATE('2020-02-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '4012', 'Бреет киви', '80')
INSERT INTO Bathroom (MANUFACNAME_FK, PRODUCTID_PK, TYPEB, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Bosch', '12223', 'Фен', 'Asadasd', TO_DATE('2020-05-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '3700', 'Сушит волосы', '180')
INSERT INTO Bathroom (MANUFACNAME_FK, PRODUCTID_PK, TYPEB, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Gorenje', '12231', 'Бойлер', 'Aqqn', TO_DATE('2017-10-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '3900', 'Заваривает чай', '400')
INSERT INTO Bathroom (MANUFACNAME_FK, PRODUCTID_PK, TYPEB, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Gorenje', '12232', 'Фен', 'Vddd', TO_DATE('2019-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2000', 'Просто фен', '90')
INSERT INTO Bathroom (MANUFACNAME_FK, PRODUCTID_PK, TYPEB, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Gorenje', '12233', 'Плойка', 'Kkkk1', TO_DATE('2020-02-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '3900', 'Выравнивает волосы', '200')
INSERT INTO Bathroom (MANUFACNAME_FK, PRODUCTID_PK, TYPEB, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Phillips', '12251', 'Плойка', 'Lqqq', TO_DATE('2018-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2100', 'Старая плойка', '90')
INSERT INTO Bathroom (MANUFACNAME_FK, PRODUCTID_PK, TYPEB, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Phillips', '12252', 'Фен', 'Ksssq', TO_DATE('2020-01-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '3100', 'Нагоняет ветер', '180')
INSERT INTO House (MANUFACNAME_FK, PRODUCTID_PK, TYPEH, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Phillips', '77771', 'Пылесос', 'Smoker', TO_DATE('2010-01-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '4021', 'Убирает грязь', '80')
INSERT INTO House (MANUFACNAME_FK, PRODUCTID_PK, TYPEH, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Phillips', '77772', 'Пылесос', 'SmokerV8', TO_DATE('2020-01-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '4078', 'Помогает в уборке', '230')
INSERT INTO House (MANUFACNAME_FK, PRODUCTID_PK, TYPEH, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Phillips', '77773', 'Утюг', 'ASqwe', TO_DATE('2018-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '3609', 'Жарит еду', '160')
INSERT INTO House (MANUFACNAME_FK, PRODUCTID_PK, TYPEH, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Gorenje', '77710', 'Пароочиститель', 'Drag', TO_DATE('2019-08-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '4019', 'Чистит пар', '150')
INSERT INTO House (MANUFACNAME_FK, PRODUCTID_PK, TYPEH, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Gorenje', '77711', 'Робот-пылесос', 'Kiborg', TO_DATE('2020-01-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '6028', 'Помогает пылесосить', '200')
INSERT INTO House (MANUFACNAME_FK, PRODUCTID_PK, TYPEH, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Gorenje', '77712', 'Швейная машинка', 'Agq1', TO_DATE('2020-02-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2091', 'Можно шить', '305')
INSERT INTO House (MANUFACNAME_FK, PRODUCTID_PK, TYPEH, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Bosch', '88812', 'Пылесос', 'V8tt', TO_DATE('2020-06-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '3012', 'Убирает пол', '310')
INSERT INTO House (MANUFACNAME_FK, PRODUCTID_PK, TYPEH, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Bosch', '88813', 'Утюг', 'Kk1', TO_DATE('2020-03-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '4010', 'Делает ровными шмотки', '250')
INSERT INTO Sanitary (MANUFACNAME_FK, PRODUCTID_PK, TYPESE, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Bosch', '20010', 'Счётчик воды(Х)', 'bm1', TO_DATE('2016-02-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9003', 'Счётчик холодной воды', '20')
INSERT INTO Sanitary (MANUFACNAME_FK, PRODUCTID_PK, TYPESE, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Bosch', '20011', 'Счётчик воды(Г)', 'bm2', TO_DATE('2016-02-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9003', 'Счётчик горячей воды', '20')
INSERT INTO Sanitary (MANUFACNAME_FK, PRODUCTID_PK, TYPESE, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Bosch', '20012', 'Счётчик газа', 'gm1', TO_DATE('2016-02-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9003', 'Счётчик газа', '22')
INSERT INTO Sanitary (MANUFACNAME_FK, PRODUCTID_PK, TYPESE, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Gorenje', '30010', 'Счётчик воды(Х)', 'ke1', TO_DATE('2017-06-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '7000', 'Счётчик холодной воды', '18')
INSERT INTO Sanitary (MANUFACNAME_FK, PRODUCTID_PK, TYPESE, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Gorenje', '30011', 'Счётчик воды(Г)', 'ke2', TO_DATE('2017-06-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '7000', 'Счётчик горячей воды', '18')
INSERT INTO Sanitary (MANUFACNAME_FK, PRODUCTID_PK, TYPESE, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Gorenje', '30012', 'Фильтр воды', 'tyt2', TO_DATE('2018-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '6012', 'Фильтр для воды', '30')
INSERT INTO Sanitary (MANUFACNAME_FK, PRODUCTID_PK, TYPESE, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Phillips', '40010', 'Труба ', 'www2', TO_DATE('2019-02-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '3000', 'Водопроводная труба', '10')
INSERT INTO Sanitary (MANUFACNAME_FK, PRODUCTID_PK, TYPESE, MODEL, STARTYEAR, COUNTPERYEAR, DESCRIBE, PRICE) VALUES ('Phillips', '40011', 'Счётчик тепла', 'eee21', TO_DATE('2017-02-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8000', 'Счётчики отопления помещения', '15')
INSERT INTO Ordering (ORDERID_PK, PRODUCTID_FK, MANUFACNAME_FK, WARECOUNT) VALUES ('228337', '33322', 'Bosch','38')
INSERT INTO Ordering (ORDERID_PK, PRODUCTID_FK, MANUFACNAME_FK, WARECOUNT) VALUES ('311332', '88812', 'Bosch', '15')
INSERT INTO OrderShipping (ORDERID_FK, CUSTOMERNAME, CUSTOMERPHONE) VALUES ('777111', 'Фокус', '4031212')
INSERT INTO OrderShipping (ORDERID_FK, CUSTOMERNAME, CUSTOMERPHONE) VALUES ('228337', 'Фокстрот', '4031641')
INSERT INTO OrderShipping (ORDERID_FK, CUSTOMERNAME, CUSTOMERPHONE) VALUES ('311332', 'Алло', '4031902')
/*запити*/
select*from Firms
select*from Kitchen
select*from Bathroom
select*from House
select*from Sanitary
select*from Ordering
select*from OrderShipping
select h.manufacname_fk as Производитель, h.typeh as Тип, h.model as Модель, h.price as "Цена за шт. (в $)"
from house h
inner join Firms
on firms.firmsname = h.manufacname_fk
and h.productid_pk = 77710;
select k.typek, k.describe, k.price
from kitchen k
join firms
on firms.firmsname = k.manufacname_fk
and k.model = 'Coil'
and k.manufacname_fk = 'Bosch';
select distinct h.firmsname
from firms h
join firms
on firms.firmsname = firms.firmsname
order by
h.firmsname asc;
select DISTINCT *
from sanitary se, firms e, house h, bathroom b, firms ef, kitchen k
where
k.manufacname_fk = ef.firmsname
and se.manufacname_fk = ef.firmsname
and e.firmsname = ef.firmsname
and h.manufacname_fk = ef.firmsname
and b.manufacname_fk = ef.firmsname
and k.manufacname_fk = 'Bosch'
and se.manufacname_fk = 'Bosch'
and h.manufacname_fk = 'Bosch'
and b.manufacname_fk = 'Bosch';
select DISTINCT *
from house, bathroom, kitchen, sanitary, firms
where
house.manufacname_fk = firms.firmsname
and firms.firmsname = bathroom.manufacname_fk
and firms.firmsname = kitchen.manufacname_fk
and firms.firmsname = sanitary.manufacname_fk
and sanitary.manufacname_fk = 'Bosch' and (sanitary.startyear > to_date('01.01.2020'))
AND kitchen.manufacname_fk = 'Bosch' and (kitchen.startyear > to_date('01.01.2020'))
and house.manufacname_fk = 'Bosch' and (house.startyear > to_date('01.01.2020'))
and bathroom.manufacname_fk = 'Bosch' and (bathroom.startyear > to_date('01.01.2020'));
select *
from bathroom b11, bathroom b22
where b11.price = (select max(b1.price)from bathroom b1 where b1.manufacname_fk = 'Bosch')
and
b22.price = (select min(b2.price) from bathroom b2 where b2.manufacname_fk = 'Bosch');
select manufacname_fk, model, startyear, describe, price
from house
where
typeh = 'Пылесос'
and ( manufacname_fk = 'Phillips');
select distinct (select count(manufacname_fk) from sanitary where manufacname_fk = 'Bosch') as Bosch,
(select count(manufacname_fk) from sanitary where manufacname_fk = 'Bosch') as Gorenje,
(select count(manufacname_fk) from sanitary where manufacname_fk = 'Phillips') as Phillips
from sanitary;
select manufacname_fk, model, startyear, describe, price
from house
where
typeh = 'Утюг'
and ( manufacname_fk = 'Bosch');
select manufacname_fk, model, startyear, describe, price
from house
where
typeh = 'Швейная машинка'
and ( manufacname_fk = 'Gorenje');
select manufacname_fk, model, startyear, describe, price
from Sanitary
where
typese = 'Труба'
and ( manufacname_fk = 'Phillips');
select manufacname_fk, model, startyear, describe, price
from Kitchen
where
typek = 'Микроволновка'
and ( manufacname_fk = 'Bosch');
select manufacname_fk, model, startyear, describe, price
from Kitchen
where
typek = 'Микроволновка'
and ( manufacname_fk = 'Gorenje');
select manufacname_fk, model, startyear, describe, price
from Kitchen
where
typek = 'Бойлер'
and ( manufacname_fk = 'Gorenje');
Висновок: У цій курсовій роботі я закріпив пройдений курс з Баз даних, де на практиці створили власне сховище даних. Застосували отримані навички з опису предметної області, розробки концепт системи, побудували ER-модель і реалізували базу за допомогою мови sql та СУБД. Зімітували її практичне використання шляхом написання запитів.