create database if not exists decomposition;
use decomposition;

-- 1) Uvazujme tabulku ABC
drop table if exists ABC;
create temporary table ABC(A int, B int, C int);
insert ABC values ( 1, 2, 3), (31, 2, 33);
select * from ABC;

-- 1a) Navrhnite Lossless/bezstratovu dekompoziciu tabulky ABC
-- 1b) Navrhnite Lossy/stratovu dekompoziciu tabulky ABC

-- Riesenie
-- 1a) Navrhnite Lossless/bezstratovu dekompoziciu tabulky ABC
-- R(ABC) = R(AB) + R(AC)
drop table if exists AB;
create temporary table AB(A int, B int);
insert AB select A,B from ABC;
select * from AB;

drop table if exists AC;
create temporary table AC(A int, C int);
insert AC select A,C from ABC;
select * from AC;

select AB.A, B, C from AB join AC on AB.A=AC.A
except
select * from ABC;

-- 1b) Navrhnite Lossy/stratovu dekompoziciu tabulky ##ABC
-- R(##ABC) != R(##AB) + R(##BC)
drop table if exists AB;
create temporary table AB(A int, B int);
insert AB select A,B from ABC;
select * from AB;

drop table if exists BC;
create temporary table BC(B int, C int);
insert BC select B,C from ABC;
select * from BC;

select A, AB.B, C from AB join BC ON AB.B=BC.B
except
select * from ABC;