5NF.sql

/*5NF Example*/

DROP TABLE R1;
DROP TABLE R2;
DROP TABLE R3;

CREATE TABLE R1
(MANUFACTURER VARCHAR2(25),
ITEM VARCHAR2(25),
PRIMARY KEY (MANUFACTURER,ITEM));

CREATE TABLE R2
(ITEM VARCHAR2(25),
SALESPERSON VARCHAR2(25),
PRIMARY KEY (ITEM,SALESPERSON));

CREATE TABLE R3
(MANUFACTURER VARCHAR2(25),
SALESPERSON VARCHAR2(25),
PRIMARY KEY (MANUFACTURER,SALESPERSON));
INSERT INTO R1 VALUES ('Kitchen World','Doughnut');
INSERT INTO R1 VALUES ('Kitchen World','Refridgerator');
INSERT INTO R1 VALUES ('House AllSorts Ltd','Refridgerator');

INSERT INTO R2 VALUES ('Refridgerator','Jennifer');
INSERT INTO R2 VALUES ('Doughnut','Charlotte');
INSERT INTO R2 VALUES ('Refridgerator','Charlotte');
INSERT INTO R3 VALUES ('Kitchen World','Jennifer');
INSERT INTO R3 VALUES ('Kitchen World','Charlotte');
INSERT INTO R3 VALUES ('House AllSorts Ltd','Charlotte');

Select * from R1,R2
WHERE R1.ITEM = R2.ITEM;

Select * from R1,R3
WHERE R1.MANUFACTURER = R3.MANUFACTURER;

Select t3.MANUFACTURER,t3.SALESPERSON,t1.ITEM
FROM R3 t3, R2 t2, R1 t1
WHERE t1.MANUFACTURER = t3.MANUFACTURER
AND t2.SALESPERSON = t3.SALESPERSON
AND t1.ITEM = t2.ITEM;