BCNF.sql

/*BCNF Example*/

DROP TABLE MANUFACTURED_ITEMS;
DROP TABLE STORE_CUSTOMERS;

CREATE TABLE STORE_CUSTOMERS
(MANUFACTURER_NAME VARCHAR2(25) NOT NULL,
CUSTOMER_NAME VARCHAR(25) NOT NULL,
PRIMARY KEY (MANUFACTURER_NAME,CUSTOMER_NAME));

CREATE TABLE MANUFACTURED_ITEMS
(MANUFACTURER_NAME VARCHAR2(25),
ITEM_NAME VARCHAR2(25) NOT NULL,
PRIMARY KEY(MANUFACTURER_NAME));

INSERT INTO STORE_CUSTOMERS VALUES ('Kitchen World','Ben');
INSERT INTO STORE_CUSTOMERS VALUES ('House AllSorts LTD','Ben');
INSERT INTO STORE_CUSTOMERS VALUES ('Kitchen World','Fred');
INSERT INTO STORE_CUSTOMERS VALUES ('BigCookeryProducts.com','Fred');
INSERT INTO STORE_CUSTOMERS VALUES ('House AllSorts LTD','Alex');
INSERT INTO MANUFACTURED_ITEMS VALUES ('Kitchen World','Kettle');
INSERT INTO MANUFACTURED_ITEMS VALUES ('House AllSorts LTD','Refridgerator');
INSERT INTO MANUFACTURED_ITEMS VALUES ('BigCookeryProducts.com','Refridgerator');

select a.MANUFACTURER_NAME, a.CUSTOMER_NAME, b.ITEM_NAME
from STORE_CUSTOMERS a, MANUFACTURED_ITEMS b
WHERE a.MANUFACTURER_NAME = b.MANUFACTURER_NAME;

DELETE FROM STORE_CUSTOMERS
WHERE CUSTOMER_NAME = 'Ben'
AND MANUFACTURER_NAME = 'Kitchen World';

UPDATE STORE_CUSTOMERS
SET MANUFACTURER_NAME = 'Kitchen World'
WHERE CUSTOMER_NAME = 'Ben'
AND MANUFACTURER_NAME = 'House AllSorts LTD';

select a.MANUFACTURER_NAME, a.CUSTOMER_NAME, b.ITEM_NAME
from STORE_CUSTOMERS a, MANUFACTURED_ITEMS b
WHERE a.MANUFACTURER_NAME = b.MANUFACTURER_NAME;