zaka

CREATE TABLE BUYER(
BUYER_ID VARCHAR2(20) NOT NULL PRIMARY KEY,
NAME VARCHAR2(20),
LOCATION VARCHAR2(20),
TRANSACTION_ID NUMBER,
);

CREATE OR REPLACE VIEW BUYER_CHECK(NAME, LOCATION, TRANSACTION_ID) AS
(SELECT B.NAME,B.LOCATION,B.TRACTION_ID FROM BUYER "B";


CREATE SEQUENCE Buyer_Primary_Key
INCREMENT BY 5
START WITH 1
MAXVALUE 1000000
NOCACHE
NOCYCLE;


CREATE OR REPLACE TRIGGER BUYER_ON_INSERT
  BEFORE INSERT ON BUYER
  FOR EACH ROW
BEGIN
  SELECT Buyer_Primary_Key.nextval
  INTO :new.BUYER_ID
  FROM dual;
END;

INSERT INTO BUYER VALUES('','ZAKA','DHAKA','00001111');
INSERT INTO BUYER VALUES('','UDOY','RAJSHAHI','00001122');
INSERT INTO BUYER VALUES('','SHAHIR','CUMILLA','00001133');
INSERT INTO BUYER VALUES('','RAFA','DHAKA','00001144');
INSERT INTO BUYER VALUES('','RIDAY','CHITTAGONG','00001155'); 



CREATE TABLE Personal
( BUYER_ID VARCHAR2(20) NOT NULL PRIMARY KEY,
  DISCOUNT NUMBER,
  PAYMENT_TYPE VARCHAR2(20),
  E-MAIL VARCHAR2(100),
  BALANCE NUMBER,
  PHONE NUMBER
  );
  
  
 CREATE OR REPLACE VIEW personal_Buyer(Buyer_ID, DISCOUNT,PAYMENT_TYPE,E_MAIL,BALANCE,PHONE_NO) AS
 SELECT BUYER_ID,DISCOUNT,PAYMENT_TYPE,E-MAIL,BALANCE,PHONE FROM PERSONAL
 WHERE BALANCE>0
 WITH CHECK OPTION CONSTRAINT BALANCE_CHECK;


CREATE SEQUENCE PERSONAL_BUYER_ID
INCREMENT BY 10
START WITH 1
MAXVALUE 1000000
CACHE 100
NOCYCLE;


CREATE OR REPLACE TRIGGER PERSONAL_ON_INSERT
  BEFORE INSERT ON PERSONAL
  FOR EACH ROW
BEGIN
  SELECT PERSONAL_BUYER_ID.nextval
  INTO :new.BUYER_ID
  FROM dual;
END;


CREATE OR REPLACE TRIGGER BUYER_ON_BAL
  BEFORE INSERT 
  ON PERSONAL
BEGIN
  IF BALANCE<0
  THEN
  RAISE_APPLICATION_ERROR(2019,'LOW BALANCE');
  END IF;
END;



INSERT INTO PERSONAL VALUES('','5%','CARD','zaka.spacesoft@gmail.com','500000','01706******');
INSERT INTO PERSONAL VALUES('','5%','bkash','shahir@gmail.com','500000','01716******');
INSERT INTO PERSONAL VALUES('','5%','ON_PAYMENT','rafa@gmail.com','500000','01726******');
INSERT INTO PERSONAL VALUES('','5%','CARD','udoy@gmail.com','500000','01736******');
INSERT INTO PERSONAL VALUES('','5%','ROCKET','riday@gmail.com','500000','01746******');



CREATE TABLE INDUSTRIAL
(
BUYER_ID VARCHAR2(20) NOT NULL PRIMARY KEY,
CONTACT_PHONE NUMBER NOT NULL,
BANK_ACCOUNT NUMBER,
INVENTORY VARCHAR2(20),
CONTACT_EMAIL VARCHAR2(20),

);

CREATE OF REPLACE VIEW INDUSTRIAL_BUYER("BUYER_ID","PHONE_NO","ACCOUNT_NO","INVENTORY",E_MAIL")
AS
SELECT BUYER_ID,CONTACT_PHONE,BANK_ACCOUNT,INVENROTY,CONTACT_EMAIL FROM INDUSTRIAL;

CREATE SEQUENCE INDUSTRIAL_BUYER_ID
INCREMENT BY 10
START WITH 1
MAXVALUE 1000000
CACHE 100
NOCYCLE;


CREATE OR REPLACE TRIGGER INDUSTRIAL_ON_INSERT
  BEFORE INSERT ON INDUSTRIAL
  FOR EACH ROW
BEGIN
  SELECT INDUSTRIAL_BUYER_ID.nextval
  INTO :new.BUYER_ID
  FROM dual;
END;

INSERT INTO INDUSTRIAL VALUES   ('','01706******','10000005','Narayangang','zaka@gmail.com');
INSERT INTO INDUSTRIAL VALUES   ('','01733******','10000040','Dhaka','aka@gmail.com');
INSERT INTO INDUSTRIAL VALUES   ('','01744******','10000055','Gazipur','faka@gmail.com');
INSERT INTO INDUSTRIAL VALUES   ('','01755******','10000050','Dhaka','kaka@gmail.com');
INSERT INTO INDUSTRIAL VALUES   ('','01766******','10000010','Narayangang','raka@gmail.com');

CREATE TABLE SELLS
(
SELLER_ID VARCHAR2(20) NOT NULL,
BUYER_ID VARCHAR2(20) NOT NULL,

 CONSTRAINT SELLS_TABLE_EMPLOYEE_FK FOREIGN KEY(BUYER_ID) REFERENCES BUYER(BUYER_ID) ON DELETE CASCADE,
  CONSTRAINT SELLS_TABLE_PK PRIMARY KEY(SELLER_ID,BUYER_ID) );

);


CREATE OR REPLACE VIEW SELLS_BUY_INFO("SELLER_ID","LOCATION","BUYER_NAME","LOCATION","TRANSACTION_ID", "QUANTITY","PHONE") AS
(SELECT SELLER_ID,S.LOCATION,B.NAME,B.LOCATION,B.TRANSACTION_ID,QUANTITY FROM SELLER "S" JOIN  SELLS "R" USING(SELLER_ID) JOIN BUYER "B" USING(BUYER_ID));


INSERT INTO PAPER_TABLE VALUES('121','10001');
INSERT INTO PAPER_TABLE VALUES('121','10002');
INSERT INTO PAPER_TABLE VALUES('121','10003');
INSERT INTO PAPER_TABLE VALUES('121','10047');
INSERT INTO PAPER_TABLE VALUES('121','1005');