2) Модель Данных
MAKER
MAKERID MAKERF MAKERCITY MAKERSTATUS
MD
MAKERID
THINGLID DETAILID DETAILCOUNT
1
N
1
N
THING
THINGID THINGDESC THINGCITY THINGSALE
DETAIL
DETAILID DETAILDESC DETAILCOLOR DETAILWEIGHT DETAILCITY DETAILSALE
1 N
|
ОБОЗНАЧЕНИЯ | |
|
MAKERID |
Уникальный номер поставщика |
|
MAKERF |
Фамилия поставщика |
|
MAKERCITY |
Город поставщика |
|
MAKERSTATUS |
Рейтинг поставщика |
|
DETAILID |
Уникальный номер детали |
|
DETAILDESC |
Название детали |
|
DETAILCOLOR |
Цвет детали |
|
DETAILWEIGHT |
Вес детали |
|
DETAILCITY |
Город детали |
|
DETAILSALE |
Стоимость детали |
|
DETAILCOUNT |
Количество деталей |
|
THINGID |
Уникальный номер изделия |
|
THINGDESC |
Название изделия |
|
THINGCITY |
Город изделия |
|
THINGSALE |
Стоимость изделия |
Модель данных состоит из четырёх таблиц: MAKER(Поставщики),THING(Изделия),DETAIL(Детали),MD(Поставляемые детали).
Связь между таблицами осуществляется через таблицу MD(Поставляемые детали).
3) Запросы на создание таблиц. Результаты создания.
Запрос на создание таблиц:
create table maker
(makerid int,
makerf char(15),
makercity char(15),
makerstatus int);
create table md
(makerid int,
thingid int,
detailid int,
detailcount int);
create table detail
(detailid int,
detaildesc char(15),
detailcolor char(15),
detailweight int,
detailcity char(15),
detailsale int);
create table thing
(thingid int,
thingdesc char(15),
thingcity char(15),
thingsale int);
Результаты:
таблица MAKER
|
MAKERID |
MAKERF |
MAKERCITY |
MAKERSTATUS |
|
|
|
|
|
таблица MD
|
MAKERID |
THINGID |
DETAILID |
DETAILCOUNT |
|
|
|
|
|
таблица DETAIL
|
DETAILID |
DETAILDESC |
DETAILCOLOR |
DETAILWEIGHT |
DETAILCITY |
DETAILSALE |
|
|
|
|
|
|
|
таблица THING
|
THINGID |
THINGDESC |
THINGCITY |
THINGSALE |
|
|
|
|
|
*На момент создания данные в таблицах отсутствуют.
4) Запросы на обновление и редактирование таблиц.
INSERT
(Заносим данные в таблицы)
insert
into maker
values(1,'Rasskazov','Alexsandrov',23);
insert
into maker
values(2,'Abdulin','Dserjinsk',22);
insert
into maker
values(3,'Abasov','Kamushin',25);
insert
into maker
values(4,'Temnov','Hilimunsunt',44);
insert
into maker
values(5,'Degtev','Bryansk',34);
insert
into maker
values(6,'Zagainov','Kyrgan',9);
------------------------------------------------
insert
into thing
values(1,'Shlicerez','Orel',79);
insert
into thing
values(2,'Perforator','Bryansk',41);
insert
into thing
values(3,'Benzopila','Bryansk',79);
insert
into thing
values(4,'Electroagregat','Kamushin',76);
insert
into thing
values(5,'Motor','Hilimunsunt',79);
------------------------------------------------
insert
into detail
values(4,'Rum-bolt','LightBlue',24,'Bryansk',22);
insert
into detail
values(2,'Grover','Red',19,'Bryansk',30);
insert
into detail
values(3,'Excentric','Green',16,'Bryansk',27);
insert
into detail
values(4,'Gaika','Blue',22,'Bryansk',15);
insert
into detail
values(5,'Spilka','Blue',20,'Bryansk',23);
------------------------------------------------
insert
into md
values(1,4,4,266);
insert
into md
values(1,1,3,412);
insert
into md
values(1,4,2,443);
insert
into md
values(1,2,5,298);
insert
into md
values(1,3,1,230);
insert
into md
values(1,2,1,497);
insert
into md
values(1,5,1,555);
insert
into md
values(1,1,1,417);
insert
into md
values(1,4,1,156);
------------------------------------------------
insert
into md
values(2,2,4,311);
insert
into md
values(2,3,3,333);
insert
into md
values(2,5,2,428);
insert
into md
values(2,3,5,256);
insert
into md
values(2,1,1,430);
insert
into md
values(2,2,1,105);
insert
into md
values(2,5,1,103);
insert
into md
values(2,3,1,436);
insert
into md
values(2,4,1,490);
------------------------------------------------
insert
into md
values(3,3,4,327);
insert
into md
values(3,3,3,419);
insert
into md
values(3,4,2,302);
insert
into md
values(3,3,5,405);
insert
into md
values(3,2,1,237);
insert
into md
values(3,5,1,224);
insert
into md
values(3,4,1,442);
insert
into md
values(3,1,1,112);
insert
into md
values(3,3,1,312);
------------------------------------------------
insert
into md
values(4,2,4,496);
insert
into md
values(4,4,3,197);
insert
into md
values(4,2,2,519);
insert
into md
values(4,5,5,401);
insert
into md
values(4,3,1,498);
insert
into md
values(4,5,1,255);
insert
into md
values(4,2,1,325);
insert
into md
values(4,4,1,559);
insert
into md
values(4,1,1,354);
------------------------------------------------
insert
into md
values(5,3,4,354);
insert
into md
values(5,3,3,109);
insert
into md
values(5,3,5,165);
insert
into md
values(5,3,1,376);
insert
into md
values(5,5,2,160);
insert
into md
values(5,3,2,268);
insert
into md
values(5,2,2,395);
insert
into md
values(5,4,2,413);
insert
into md
values(5,1,2,176);
------------------------------------------------
insert
into md
values(6,4,4,150);
insert
into md
values(6,5,3,569);
insert
into md
values(6,2,5,566);
insert
into md
values(6,2,1,314);
insert
into md
values(6,3,2,295);
insert
into md
values(6,5,2,505);
insert
into md
values(6,2,2,561);
insert
into md
values(6,1,2,156);
insert
into md
values(6,4,2,396);
commit;
Для отображения результатов операции вставки данных пишем поочередно 4 запроса:
select * from maker;
Результат:
|
MAKERID |
MAKERF |
MAKERCITY |
MAKERSTATUS |
|
1 |
'Rasskazov' |
'Alexsandrov' |
23 |
|
2 |
'Abdulin' |
'Dserjinsk' |
22 |
|
3 |
'Abasov' |
'Kamushin' |
25 |
|
4 |
'Temnov' |
'Hilimunsunt' |
44 |
|
5 |
'Degtev' |
'Bryansk' |
34 |
|
6 |
'Zagainov' |
'Kyrgan' |
9 |
select * from md;
Результат:
|
MAKERID |
THINGID |
DETAILID |
DETAILCOUNT |
|
1 |
4 |
4 |
266 |
|
1 |
1 |
3 |
412 |
|
1 |
4 |
2 |
443 |
|
1 |
2 |
5 |
298 |
|
1 |
3 |
1 |
230 |
|
1 |
2 |
1 |
497 |
|
1 |
5 |
1 |
555 |
|
1 |
1 |
1 |
417 |
|
1 |
4 |
1 |
156 |
|
2 |
2 |
4 |
311 |
|
2 |
3 |
3 |
333 |
|
2 |
5 |
2 |
428 |
|
2 |
3 |
5 |
256 |
|
2 |
1 |
1 |
430 |
|
2 |
2 |
1 |
105 |
|
2 |
5 |
1 |
103 |
|
2 |
3 |
1 |
436 |
|
2 |
4 |
1 |
490 |
|
3 |
3 |
4 |
327 |
|
3 |
3 |
3 |
419 |
|
3 |
4 |
2 |
302 |
|
3 |
3 |
5 |
405 |
|
3 |
2 |
1 |
237 |
|
3 |
5 |
1 |
224 |
|
3 |
4 |
1 |
442 |
|
3 |
1 |
1 |
112 |
|
3 |
3 |
1 |
312 |
|
4 |
2 |
4 |
496 |
|
4 |
4 |
3 |
197 |
|
4 |
2 |
2 |
519 |
|
4 |
5 |
5 |
401 |
|
4 |
3 |
1 |
498 |
|
4 |
5 |
1 |
255 |
|
4 |
2 |
1 |
325 |
|
4 |
4 |
1 |
559 |
|
4 |
1 |
1 |
354 |
|
5 |
3 |
4 |
354 |
|
5 |
3 |
3 |
109 |
|
5 |
3 |
5 |
165 |
|
5 |
3 |
1 |
376 |
|
5 |
5 |
2 |
160 |
|
5 |
3 |
2 |
268 |
|
5 |
2 |
2 |
395 |
|
5 |
4 |
2 |
413 |
|
5 |
1 |
2 |
176 |
|
6 |
4 |
4 |
150 |
|
6 |
5 |
3 |
569 |
|
6 |
2 |
5 |
566 |
|
6 |
2 |
1 |
314 |
|
6 |
3 |
2 |
295 |
|
6 |
5 |
2 |
505 |
|
6 |
2 |
2 |
561 |
|
6 |
1 |
2 |
156 |
|
6 |
4 |
2 |
396 |
select * from detail;
Результат:
|
DETAILID |
DETAILDESC |
DETAILCOLOR |
DETAILWEIGHT |
DETAILCITY |
DETAILSALE |
|
1 |
'Rum-bolt' |
'LightBlue' |
24 |
'Bryansk' |
22 |
|
2 |
'Grover' |
'Red' |
19 |
'Bryansk' |
30 |
|
3 |
'Excentric' |
'Green' |
16 |
'Bryansk' |
27 |
|
4 |
'Gaika' |
'Blue' |
22 |
'Bryansk' |
15 |
|
5 |
'Spilka' |
'Blue' |
20 |
'Bryansk' |
23 |
select * from thing;
Результат:
|
THINGID |
THINGDESC |
THINGCITY |
THINGSALE |
|
1 |
'Shlicerez' |
'Orel' |
79 |
|
2 |
'Perforator' |
'Bryansk' |
41 |
|
3 |
'Benzopila' |
'Bryansk' |
79 |
|
4 |
'Electroagregat' |
'Kamushin' |
76 |
|
5 |
'Motor' |
'Hilimunsunt' |
79 |