4NF.sql

/* 4NF Example*/


DROP TABLE ONE;
DROP TABLE TWO;

CREATE TABLE ONE
(MANUFACTURER VARCHAR2(25) NOT NULL,
ITEM VARCHAR(25) NOT NULL,
PRIMARY KEY (MANUFACTURER,ITEM));

CREATE TABLE TWO
(MANUFACTURER VARCHAR2(25) NOT NULL,
DELIVERY_AREA VARCHAR(25) NOT NULL,
PRIMARY KEY (MANUFACTURER,DELIVERY_AREA));

INSERT INTO ONE VALUES ('Kitchen World','Kettle');
INSERT INTO ONE VALUES ('Kitchen World','Refrigerator');
INSERT INTO ONE VALUES ('BigCookeryProducts.com','Refrigerator');
INSERT INTO ONE VALUES ('BigCookeryProducts.com','Food Processor');
INSERT INTO TWO VALUES ('Kitchen World','North Yorkshire');
INSERT INTO TWO VALUES ('Kitchen World','Tyne and Wear');
INSERT INTO TWO VALUES ('BigCookeryProducts.com','North Yorkshire');
INSERT INTO TWO VALUES ('BigCookeryProducts.com','Tyne and Wear');
INSERT INTO TWO VALUES ('BigCookeryProducts.com','County Durham');

SELECT o.MANUFACTURER, o.ITEM, t.DELIVERY_AREA
FROM ONE o, TWO t
WHERE t.MANUFACTURER = o.MANUFACTURER;