u4-ermodelling.sql

drop table production;
drop table product;

/* 1a

Erzeugnis <1 Fertigung n> Zwischenerzeugnis
*/

/* 1b

- jedes Teil wird als Zwischenerzeugnis aufgefasst
  (keine seperates Modell für Teile, d.h. auch keine Relationen)
*/

-- 1c

-- results and parts are considered the *same*
create table product (
  id number primary key,
  name varchar2(30),
  description varchar2(300)
);

create table production (
  result_id number references product(id),
  required_part_id number references product(id),
  
  primary key (result_id, required_part_id)
);

-- 1d (only test data, creation see above)

-- products
insert into product values (1,  'Mainboard', '...');
insert into product values (2,  'CPU type #1', '...');
insert into product values (3,  'CPU type #2', '...');
insert into product values (4,  'RAM 4GB', '...');
insert into product values (5,  'RAM 8GB', '...');
insert into product values (6,  'Tower', '...');
insert into product values (7,  'Mainboard w/ CPU and RAM #1', '...');
insert into product values (8,  'Mainboard w/ CPU and RAM #2', '...');
insert into product values (9,  'Cooler #1', '...');
insert into product values (10, 'Computer #1', '...');
insert into product values (11, 'Computer #2', '...');
insert into product values (12, 'Homegrown CPU', '...');
insert into product values (13, 'CPU Cache', '...');
insert into product values (14, 'Many Transistors', '...');
insert into product values (15, 'Black Magic', '...');
insert into product values (16, 'Homegrown Mainboard', '...');
insert into product values (17, 'Homegrown Computer', '...');

-- productions
insert into production values (7, 1);
insert into production values (7, 2);
insert into production values (7, 4);

insert into production values (8, 1);
insert into production values (8, 3);
insert into production values (8, 5);

insert into production values (10, 7);
insert into production values (10, 6);
insert into production values (10, 9);

insert into production values (11, 8);
insert into production values (11, 6);
insert into production values (11, 9);

insert into production values (12, 13);
insert into production values (12, 14);
insert into production values (12, 15);

insert into production values (16, 1);
insert into production values (16, 12);
insert into production values (16, 5);

insert into production values (17, 16);
insert into production values (17, 9);
insert into production values (17, 6);

-- 2a

select p.id
from product p
where p.id not in (select result_id from production);

-- 2b

/*select p.result_id
from production p
where p.result_id not in (select pp.id
                          from product pp, production pr
                          where pp.id = pr.required_part_id);*/

select count(*)
from product p
where p.id in (select result_id from production)
  and p.id not in (select required_part_id from production);

select p.id, p.name
from product p
where p.id in (select result_id from production)
  and p.id not in (select required_part_id from production);

-- 2c

select count(*)
from product p
where p.id in (select result_id from production)
  and p.id in (select required_part_id from production);

select p.id, p.name
from product p
where p.id in (select result_id from production)
  and p.id in (select required_part_id from production);

-- 3a

/* -- only supported from oracle 11g
  with step (result_id, required_part_id) as (
  select result_id, required_part_id
  from production
  where result_id = 10
  union all
  select result_id, required_part_id
  from step s, production p
  where s.required_part_id = p.result_id
) select distinct name
  from step s, product p
  where s.required_part_id = p.id;
*/

select id, name
from product p, production pr
where p.id = pr.required_part_id
  and p.id in (select result_id from production)
connect by prior required_part_id = result_id
start with result_id = 17;

-- 3b

select id, name, level
from product p, production pr
where p.id = pr.required_part_id
  and p.id in (select result_id from production)
connect by prior required_part_id = result_id
start with result_id = 17;

-- 3c

select count(*)
from product p, production pr
where p.id = pr.required_part_id
  and p.id not in (select result_id from production)
connect by prior required_part_id = result_id
start with result_id = 17;