AmazonDatabase.sql

CREATE TABLE USER_ROLES (
  userID int generated by default on null as identity, 
  streetName char(25), 
  city varchar(15), 
  zipCode char(5), 
  state char(2), 
  phoneNumber char(10), 
  firstName varchar(20), 
  minit char(1), 
  lastName varchar(20), 
  loginID int, 
  password varchar(20), 
  userRoleType char(1), 
  primary key (userID)
);
CREATE TABLE CUSTOMER (
  userID int not null, 
  walletID int, 
  uniqueCartID int, 
  primary key (userID)
);
CREATE TABLE SELLER (
  userID int not null, 
  taxID int, 
  stateTaxID int, 
  bankInfo varchar(50), 
  invoiceID int, 
  primary key (userID)
);
CREATE TABLE BUSINESS_INFORMATION (
  businessID int not null, 
  phoneNumber int not null, 
  Name varchar(50), 
  streetName varchar(30), 
  City varchar(20), 
  zipCode char(5), 
  State char(2), 
  userID int, 
  primary key (businessID)
);
CREATE TABLE DELIVERS_TO (
  userID int not null, 
  trackingID int not null, 
  departureTime date, 
  shippingDate date, 
  arrivalTime date, 
  primary key (userID, trackingID)
);
CREATE TABLE CUSTOMER_GIVES_FEEDBACK (
  userID int not null, 
  itemID int not null, 
  rating int not null, 
  feedback varchar(250), 
  primary key (userID, itemID, rating)
);
CREATE TABLE ORDERS (
  orderID int generated by default on null as identity, 
  isCancelled char(1), 
  dateOfOrder date, 
  paymentMethod varchar(10), 
  orderSummary varchar(1000), 
  grandTotal decimal(19, 4), 
  userID int, 
  trackingID int, 
  primary key (orderID)
);
CREATE TABLE ORDER_HISTORY (
  userID int not null, 
  orderID int not null, 
  dateOfOrder date, 
  paymentMethod varchar(10), 
  orderSummary varchar(1000), 
  grandTotal decimal(19, 4), 
  primary key (userID, orderID)
);
CREATE TABLE WALLET (
  walletID int generated by default on null as identity, 
  streetName varchar(25), 
  city varchar(15), 
  zipCode char(5), 
  state char(2), 
  userID int, 
  primary key (walletID)
);
CREATE TABLE CREDIT_CARD (
  cardID char(16) not null, 
  type varchar(10), 
  securityCode char(3), 
  expirationDate date, 
  cardHolderName varchar(26), 
  walletID int, 
  primary key (cardID)
);
CREATE TABLE SHIPPING (
  trackingID int generated by default on null as identity, 
  shippingMethod varchar(10), 
  streetName varchar(25), 
  city varchar(15), 
  zipCode char(5), 
  state char(2), 
  userID int, 
  primary key (trackingID)
);
CREATE TABLE ITEM (
  itemID int generated by default on null as identity, 
  itemPrice decimal(19, 4), 
  quantity int, 
  condition varchar(15), 
  name varchar(200), 
  sellerID int, 
  vendorID int, 
  primary key (itemID)
);
CREATE TABLE ORDER_HAS_ITEM (
  orderID int not null, 
  itemID int not null, 
  primary key (orderID, itemID)
);
CREATE TABLE WAREHOUSE (
  warehouseID int generated by default on null as identity, 
  name varchar(50), 
  streetName varchar(25), 
  city varchar(15), 
  zipCode char(5), 
  state char(2), 
  primary key (warehouseID)
);
CREATE TABLE WAREHOUSE_CONTAINS_ITEM (
  warehouseID int not null, 
  itemID int not null, 
  primary key (warehouseID, itemID)
);
CREATE TABLE INVOICE (
  invoiceID int generated by default on null as identity, 
  sellerID int, 
  orderID int, 
  primary key (invoiceID)
);
CREATE TABLE SHOPPING_CART (
  cartID int generated by default on null as identity, 
  paymentID int, 
  processingTotal decimal(19, 4), 
  totalPrice decimal(19, 4), 
  userID int, 
  orderID int, 
  primary key (cartID)
);
CREATE TABLE CART_HAS_ITEMS (
  cartID int not null, 
  itemID int not null, 
  primary key(cartID, itemID)
);
CREATE TABLE PAYMENT (
  paymentID int not null, 
  paymentMethod varchar(15), 
  userID int, 
  invoiceID int, 
  primary key (paymentID)
);
CREATE TABLE DEPARTMENT (
  departmentID int not null, 
  departmentName varchar(15), 
  categoryID int, 
  primary key (departmentID)
);
CREATE TABLE CATEGORIES (
  categoryID int generated by default on null as identity, 
  catalogName varchar(15), 
  departmentID int, 
  primary key(categoryID)
);
CREATE TABLE CATALOGS (
  catalogID int generated by default on null as identity, 
  catalogName varchar(15), 
  primary key(catalogID)
);
CREATE TABLE CATEGORIES_CONTAINS_CATALOGS (
  categoryID int not null, 
  catalogID int not null, 
  primary key(categoryID, catalogID)
);
CREATE TABLE VENDOR (
  vendorID int generated by default on null as identity, 
  vendorName varchar(15), 
  catalogID int, 
  primary key(vendorID)
);
ALTER TABLE 
  CUSTOMER 
ADD 
  CONSTRAINT FKCust_wallet FOREIGN KEY(walletID) REFERENCES WALLET(walletID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  CUSTOMER 
ADD 
  CONSTRAINT FKCust_shopping FOREIGN KEY(uniqueCartID) REFERENCES SHOPPING_CART(cartID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  SELLER 
ADD 
  CONSTRAINT FKSeller_invoice FOREIGN KEY(invoiceID) REFERENCES INVOICE(invoiceID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  CUSTOMER_GIVES_FEEDBACK 
ADD 
  CONSTRAINT FKCust_user FOREIGN KEY(userID) REFERENCES CUSTOMER(userID) ON DELETE CASCADE;
ALTER TABLE 
  CUSTOMER_GIVES_FEEDBACK 
ADD 
  CONSTRAINT FKCust_item FOREIGN KEY(itemID) REFERENCES ITEM(itemID) ON DELETE CASCADE;
ALTER TABLE 
  BUSINESS_INFORMATION 
ADD 
  CONSTRAINT FKBus_user FOREIGN KEY(userID) REFERENCES SELLER(userID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  DELIVERS_TO 
ADD 
  CONSTRAINT FKDelv_user FOREIGN KEY(userID) REFERENCES CUSTOMER(userID) ON DELETE CASCADE;
ALTER TABLE 
  DELIVERS_TO 
ADD 
  CONSTRAINT FKDelv_track FOREIGN KEY(trackingID) REFERENCES SHIPPING(trackingID) ON DELETE CASCADE;
ALTER TABLE 
  ORDER_HISTORY 
ADD 
  CONSTRAINT FKHist_user FOREIGN KEY(userID) REFERENCES CUSTOMER(userID) ON DELETE CASCADE;
ALTER TABLE 
  ORDER_HISTORY 
ADD 
  CONSTRAINT FKHist_order FOREIGN KEY(orderID) REFERENCES ORDERS(orderID) ON DELETE CASCADE;
ALTER TABLE 
  WALLET 
ADD 
  CONSTRAINT FKWalletUser FOREIGN KEY(userID) REFERENCES CUSTOMER(userID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  CREDIT_CARD 
ADD 
  CONSTRAINT FKCardWallet FOREIGN KEY(walletID) REFERENCES WALLET(walletID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  SHIPPING 
ADD 
  CONSTRAINT FKShipUser FOREIGN KEY(userID) REFERENCES CUSTOMER(userID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  ORDERS 
ADD 
  CONSTRAINT FKOrderUser FOREIGN KEY(userID) REFERENCES CUSTOMER(userID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  ORDERS 
ADD 
  CONSTRAINT FKOrderShip FOREIGN KEY(trackingID) REFERENCES SHIPPING(trackingID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  ITEM 
ADD 
  CONSTRAINT FKItemSeller FOREIGN KEY(sellerID) REFERENCES SELLER(userID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  ITEM 
ADD 
  CONSTRAINT FKItemVendor FOREIGN KEY(vendorID) REFERENCES VENDOR(vendorID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  ORDER_HAS_ITEM 
ADD 
  CONSTRAINT FKOHOrder FOREIGN KEY(orderID) REFERENCES ORDERS(orderID) ON DELETE CASCADE;
ALTER TABLE 
  ORDER_HAS_ITEM 
ADD 
  CONSTRAINT FKOHItem FOREIGN KEY(itemID) REFERENCES ITEM(itemID) ON DELETE CASCADE;
ALTER TABLE 
  WAREHOUSE_CONTAINS_ITEM 
ADD 
  CONSTRAINT FKWCWare FOREIGN KEY(warehouseID) REFERENCES WAREHOUSE(warehouseID) ON DELETE CASCADE;
ALTER TABLE 
  WAREHOUSE_CONTAINS_ITEM 
ADD 
  CONSTRAINT FKWCItem FOREIGN KEY(itemID) REFERENCES ITEM(itemID) ON DELETE CASCADE;
ALTER TABLE 
  INVOICE 
ADD 
  CONSTRAINT FKInvoiceSeller FOREIGN KEY(sellerID) REFERENCES SELLER(userID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  INVOICE 
ADD 
  CONSTRAINT FKInvoiceOrder FOREIGN KEY(orderID) REFERENCES ORDERS(orderID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  SHOPPING_CART 
ADD 
  CONSTRAINT FKSCU FOREIGN KEY (userID) REFERENCES CUSTOMER(userID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  SHOPPING_CART 
ADD 
  CONSTRAINT FKSCO FOREIGN KEY (orderID) REFERENCES ORDERS(orderID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  SHOPPING_CART 
ADD 
  CONSTRAINT FKSCP FOREIGN KEY (paymentID) REFERENCES PAYMENT(paymentID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  CART_HAS_ITEMS 
ADD 
  CONSTRAINT FKCTC FOREIGN KEY (cartID) REFERENCES SHOPPING_CART(cartID) ON DELETE CASCADE;
ALTER TABLE 
  CART_HAS_ITEMS 
ADD 
  CONSTRAINT FKCTT FOREIGN KEY (itemID) REFERENCES ITEM(itemID) ON DELETE CASCADE;
ALTER TABLE 
  PAYMENT 
ADD 
  CONSTRAINT FKPID FOREIGN KEY (userID) REFERENCES CUSTOMER(userID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  PAYMENT 
ADD 
  CONSTRAINT FKPIID FOREIGN KEY (invoiceID) REFERENCES INVOICE(invoiceID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  DEPARTMENT 
ADD 
  CONSTRAINT FKDID FOREIGN KEY (categoryID) REFERENCES CATEGORIES(categoryID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  CATEGORIES 
ADD 
  CONSTRAINT FKCDID FOREIGN KEY (departmentID) REFERENCES DEPARTMENT(departmentID) ON DELETE 
SET 
  NULL;
ALTER TABLE 
  CATEGORIES_CONTAINS_CATALOGS 
ADD 
  CONSTRAINT FKCCID FOREIGN KEY(categoryID) REFERENCES CATEGORIES(categoryID) ON DELETE CASCADE;
ALTER TABLE 
  CATEGORIES_CONTAINS_CATALOGS 
ADD 
  CONSTRAINT FKCCCID FOREIGN KEY(catalogID) REFERENCES CATALOGS(catalogID) ON DELETE CASCADE;
ALTER TABLE 
  VENDOR 
ADD 
  CONSTRAINT FKVID FOREIGN KEY(catalogID) REFERENCES CATALOGS(catalogID) ON DELETE 
SET 
  NULL;

  
/*Stored Procedures*/
CREATE 
OR REPLACE PROCEDURE Add_Credit_Card(
  cardID in char, type in varchar, securityCode in char, 
  expirationDate in date, cardHolderName in varchar, 
  walletID in int
) AS BEGIN INSERT INTO CREDIT_CARD 
VALUES 
  (
    cardID, type, securityCode, expirationDate, 
    cardHolderName, walletID
  );
END add_credit_card;
CREATE 
OR REPLACE PROCEDURE Update_Item_Price (
  itemNum in item.itemID % type, newItemPrice in item.itemPrice % type
) AS BEGIN 
UPDATE 
  ITEM 
SET 
  itemPrice = newItemPrice 
WHERE 
  itemID = itemNum;
END Update_Item_Price;

/*Triggers*/
AFTER 
  INSERT 
  or 
UPDATE 
  ON ORDERS FOR EACH ROW DECLARE User_id CUSTOMER.userID % TYPE;
BEGIN 
SELECT 
  userID INTO User_id 
FROM 
  SHOPPING_CART 
WHERE 
  orderID = : new.orderID;
INSERT INTO ORDER_HISTORY(
  userID, orderID, dateOfOrder, paymentMethod, 
  orderSummary, grandTotal
) 
VALUES 
  (
    User_id, : new.orderID, : new.dateOfOrder, 
    : new.paymentMethod, : new.orderSummary, 
    : new.grandTotal
  );
END;
CREATE 
OR REPLACE TRIGGER Clear_Cart 
AFTER 
  INSERT ON ORDERS FOR EACH ROW DECLARE cartNum SHOPPING_CART.cartID % TYPE;
BEGIN 
SELECT 
  cartID INTO cartNum 
FROM 
  SHOPPING_CART 
WHERE 
  userID = : new.userID;
DELETE FROM 
  SHOPPING_CART 
WHERE 
  userID = : new.userID;
DELETE FROM 
  CART_HAS_ITEMS 
WHERE 
  cartID = cartNum;
END;