-- -----------------------------------------------------
-- Table RETAILDB.CUSTOMER
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.CUSTOMER (
customerId INTEGER NOT NULL ,
customerName VARCHAR(50) NOT NULL ,
PRIMARY KEY (customerId) ,
UNIQUE INDEX customerName_UNIQUE (customerName ASC) );
-- -----------------------------------------------------
-- Table RETAILDB.CATEGORY
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.CATEGORY (
categoryCode VARCHAR(5) NOT NULL ,
categoryName VARCHAR(50) NOT NULL ,
PRIMARY KEY (categoryCode) ,
UNIQUE INDEX categoryName_UNIQUE (categoryName ASC) );
-- -----------------------------------------------------
-- Table RETAILDB.PRODUCT
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.PRODUCT (
productCode VARCHAR(10) NOT NULL ,
productName VARCHAR(50) NOT NULL,
description VARCHAR(100) ,
category_categoryCode VARCHAR(5) NOT NULL ,
PRIMARY KEY (productCode) ,
UNIQUE INDEX productName_UNIQUE (productName ASC),
INDEX category_categoryCode_idx (category_categoryCode ASC) ,
CONSTRAINT category_categoryCode0
FOREIGN KEY (category_categoryCode)
REFERENCES RETAILDB.CATEGORY (categoryCode));
-- -----------------------------------------------------
-- Table RETAILDB.ITEM
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.ITEM (
itemCode VARCHAR(10) NOT NULL ,
itemName VARCHAR(50) NOT NULL,
unitPrice NUMERIC NOT NULL,
product_productCode VARCHAR(10) NOT NULL ,
PRIMARY KEY (itemCode) ,
UNIQUE INDEX itemName_UNIQUE (itemName ASC),
INDEX product_productCode_idx (product_productCode ASC) ,
CONSTRAINT product_productCode0
FOREIGN KEY (product_productCode)
REFERENCES RETAILDB.PRODUCT (productCode));
-- -----------------------------------------------------
-- Table RETAILDB.IMAGES
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.IMAGES (
ID VARCHAR(255) NOT NULL ,
GALLERY VARCHAR(255) ,
IMAGE VARBINARY(255) ,
PRIMARY KEY (ID));
-- -----------------------------------------------------
-- Table RETAILDB.INVOICE
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.INVOICE (
id VARCHAR(32) NOT NULL ,
customer_customerId INTEGER NOT NULL ,
invoiceNo INTEGER NOT NULL,
year INTEGER,
date TIMESTAMP,
vatPercentage INTEGER,
remarks VARCHAR(255),
PRIMARY KEY (id) ,
UNIQUE INDEX invoiceNo_UNIQUE (invoiceNo ASC),
INDEX customer_customerId_idx (customer_customerId ASC) ,
CONSTRAINT customer_customerId0
FOREIGN KEY (customer_customerId)
REFERENCES RETAILDB.CUSTOMER (customerId ));
-- -----------------------------------------------------
-- Table RETAILDB.INVOICEDETAIL
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.INVOICEDETAIL (
id VARCHAR(32) NOT NULL ,
invoice_id VARCHAR(32) NOT NULL,
item_itemCode VARCHAR(10) NOT NULL ,
quantity INTEGER NOT NULL,
PRIMARY KEY (id) ,
INDEX invoice_id_idx (invoice_id ASC) ,
CONSTRAINT invoice_id0
FOREIGN KEY (invoice_id)
REFERENCES RETAILDB.INVOICE (id ),
INDEX item_itemCode_idx (item_itemCode ASC) ,
CONSTRAINT item_itemCode0
FOREIGN KEY (item_itemCode)
REFERENCES RETAILDB.ITEM (itemCode));
-- -----------------------------------------------------
-- Table RETAILDB.XYZ
-- -----------------------------------------------------
-- Table RETAILDB.CUSTOMER
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.CUSTOMER (
customerId INTEGER NOT NULL ,
customerName VARCHAR(50) NOT NULL ,
PRIMARY KEY (customerId) ,
UNIQUE INDEX customerName_UNIQUE (customerName ASC) );
-- -----------------------------------------------------
-- Table RETAILDB.CATEGORY
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.CATEGORY (
categoryCode VARCHAR(5) NOT NULL ,
categoryName VARCHAR(50) NOT NULL ,
PRIMARY KEY (categoryCode) ,
UNIQUE INDEX categoryName_UNIQUE (categoryName ASC) );
-- -----------------------------------------------------
-- Table RETAILDB.PRODUCT
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.PRODUCT (
productCode VARCHAR(10) NOT NULL ,
productName VARCHAR(50) NOT NULL,
description VARCHAR(100) ,
category_categoryCode VARCHAR(5) NOT NULL ,
PRIMARY KEY (productCode) ,
UNIQUE INDEX productName_UNIQUE (productName ASC),
INDEX category_categoryCode_idx (category_categoryCode ASC) ,
CONSTRAINT category_categoryCode0
FOREIGN KEY (category_categoryCode)
REFERENCES RETAILDB.CATEGORY (categoryCode));
-- -----------------------------------------------------
-- Table RETAILDB.ITEM
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.ITEM (
itemCode VARCHAR(10) NOT NULL ,
itemName VARCHAR(50) NOT NULL,
unitPrice NUMERIC NOT NULL,
product_productCode VARCHAR(10) NOT NULL ,
PRIMARY KEY (itemCode) ,
UNIQUE INDEX itemName_UNIQUE (itemName ASC),
INDEX product_productCode_idx (product_productCode ASC) ,
CONSTRAINT product_productCode0
FOREIGN KEY (product_productCode)
REFERENCES RETAILDB.PRODUCT (productCode));
-- -----------------------------------------------------
-- Table RETAILDB.IMAGES
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.IMAGES (
ID VARCHAR(255) NOT NULL ,
GALLERY VARCHAR(255) ,
IMAGE VARBINARY(255) ,
PRIMARY KEY (ID));
-- -----------------------------------------------------
-- Table RETAILDB.INVOICE
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.INVOICE (
id VARCHAR(32) NOT NULL ,
customer_customerId INTEGER NOT NULL ,
invoiceNo INTEGER NOT NULL,
year INTEGER,
date TIMESTAMP,
vatPercentage INTEGER,
remarks VARCHAR(255),
PRIMARY KEY (id) ,
UNIQUE INDEX invoiceNo_UNIQUE (invoiceNo ASC),
INDEX customer_customerId_idx (customer_customerId ASC) ,
CONSTRAINT customer_customerId0
FOREIGN KEY (customer_customerId)
REFERENCES RETAILDB.CUSTOMER (customerId ));
-- -----------------------------------------------------
-- Table RETAILDB.INVOICEDETAIL
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.INVOICEDETAIL (
id VARCHAR(32) NOT NULL ,
invoice_id VARCHAR(32) NOT NULL,
item_itemCode VARCHAR(10) NOT NULL ,
quantity INTEGER NOT NULL,
PRIMARY KEY (id) ,
INDEX invoice_id_idx (invoice_id ASC) ,
CONSTRAINT invoice_id0
FOREIGN KEY (invoice_id)
REFERENCES RETAILDB.INVOICE (id ),
INDEX item_itemCode_idx (item_itemCode ASC) ,
CONSTRAINT item_itemCode0
FOREIGN KEY (item_itemCode)
REFERENCES RETAILDB.ITEM (itemCode));
-- -----------------------------------------------------
-- Table RETAILDB.XYZ
-- -----------------------------------------------------
No comments:
Post a Comment