-- Slide 438: (Violation of) BCNF and decomposition
--
-- - Relation R(A,B,C)
-- - A is key (A -> BC)
-- - Additional FD: B -> C
-- 
-- ┌───┬────┬────┐
-- │ A │  B │  C │
-- │ ─ │    │    │
-- ├───┼────┼────┤
-- │ 1 │ 10 │  5 │
-- │ 2 │ 20 │  6 │
-- │ 3 │ 10 │  ? │  <-- to preserve FD B -> C, we need ? = 5
-- └───┴────┴────┘

DROP TABLE R;
CREATE TABLE R(
	A INTEGER NOT NULL,
	B INTEGER,
	C INTEGER,
	PRIMARY KEY (A)
	-- FUNCTIONAL DEPENDENCY (B), (C)    --  not possible in SQL!
);

INSERT INTO R VALUES 
	(1, 10, 5),
	(2, 20, 6),
	(3, 10, 7);	
	--      ^
	-- this should be 5; we've wrecked the FD B -> C!

-- Check whether FD B -> C holds in R
SELECT   'FD B -> C has been violated'
FROM     R
GROUP BY B
HAVING   COUNT(DISTINCT C) > 1;

-- Remove offending tuple
DELETE FROM R
WHERE A = 3;




-- Now perform BCNF decomposition (split algorithm) for R:
-- 
-- FD B -> C is non-trivial and B does not contain a key
-- => decompose R into
--    (1) R1(A, B)
--    (2) R2(B, C)            because B+ = {B,C}
-- 
-- 
-- R1             R2         
-- ┌───┬────┐     ┌────┬────┐
-- │ A │  B │     │  B │  C │
-- │ ─ │    │     │ ── │    │
-- ├───┼────┤     ├────┼────┤
-- │ 1 │ 10 │     │ 10 │  5 │
-- │ 2 │ 20 │     │ 20 │  6 │
-- │ 3 │ 10 │     └────┴────┘
-- └───┴────┘



DROP TABLE R1;
DROP TABLE R2;

CREATE TABLE R1(
	A INTEGER NOT NULL,
	B INTEGER,
	PRIMARY KEY (A));

CREATE TABLE R2(
	B INTEGER NOT NULL,
	C INTEGER,
	PRIMARY KEY (B));

INSERT INTO R1
	SELECT A, B
	FROM   R;

INSERT INTO R2
	SELECT DISTINCT B, C
	FROM   R;



-- Try to insert the offending tuple (3,10,7):
INSERT INTO R1 VALUES
	(3, 10);
INSERT INTO R2 VALUES
	(10, 7);                   
	-- will fail due to key constraint in R2: FD B -> C protected!


-- Decomposition has been loss-less
-- (see Decomposition Theorem, slide 440)
-- 
-- R1             R2         
-- ┌───┬────┐     ┌────┬────┐
-- │ A │  B │     │  B │  C │
-- │ ─ │    │     │ ── │    │
-- ├───┼────┤  ⋈  ├────┼────┤  =   R
-- │ 1 │ 10 │     │ 10 │  5 │
-- │ 2 │ 20 │     │ 20 │  6 │
-- │ 3 │ 10 │     └────┴────┘
-- └───┴────┘
--
-- sch(R1) ∩ sch(R2) = {B}, and B is key in R2 => loss-less split
 

DROP VIEW VIRTUAL_R;
CREATE VIEW VIRTUAL_R(A,B,C) AS (
	SELECT  R1.A, R1.B, R2.C
	FROM    R1, R2
	WHERE   R1.B = R2.B);


SELECT * 
FROM   VIRTUAL_R;