MySQL Script Sample

DROP DATABASE IF EXISTS DB1;

CREATE DATABASE DB1;
USE DB1;

DROP TABLE IF EXISTS COMPONENT;
DROP TABLE IF EXISTS AREA;
DROP TABLE IF EXISTS ITEM;

CREATE TABLE AREA
(
AREA_ID SMALLINT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(50),
PRIMARY KEY (AREA_ID)
);

CREATE TABLE ITEM
(
ITEM_ID SMALLINT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(50),
PRIMARY KEY (ITEM_ID)
);

CREATE TABLE COMPONENT
(
COMPONENT_ID INT NOT NULL AUTO_INCREMENT,
AREA_ID SMALLINT,
ITEM_ID SMALLINT,
NAME VARCHAR(50),
UNIT VARCHAR(50),
IN_STOCK INT,
PRIMARY KEY (COMPONENT_ID),
FOREIGN KEY (AREA_ID) REFERENCES AREA(AREA_ID) ON DELETE SET NULL,
FOREIGN KEY (ITEM_ID) REFERENCES ITEM(ITEM_ID) ON DELETE SET NULL
);
!