create table SUPPLIERS ( 
    SUPPLIER_ID number not null constraint suppliers_pk primary key, 
    NAME varchar2(45) not null 
)

create table SUPPLIER_SITES ( 
    SITE_ID number not null constraint supplier_sites_pk primary key, 
    NAME varchar2(45) not null, 
    SUPPLIER_ID number not null 
)

ALTER TABLE SUPPLIER_SITES ADD CONSTRAINT SUPPLIER_SITES_FK1 FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIERS (SUPPLIER_ID) 

create table SUPPLIER_AGREEMENTS ( 
    AGREEMENT_NO varchar2(30) not null constraint supplier_agreements_pk primary key, 
    SUPPLIER_ID number not null 
)

ALTER TABLE SUPPLIER_AGREEMENTS ADD CONSTRAINT SUPPLIER_AGREEMENTS_FK1 FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIERS (SUPPLIER_ID) 

create table SUPPLIER_DELIVERIES ( 
    DELIVERY_ID number not null constraint supplier_deliveries_pk primary key, 
    AGREEMENT_NO varchar2(30) not null, 
    SUPPLIER_SITE_ID number not null, 
    DELIVERY_DATE date not null 
)

ALTER TABLE SUPPLIER_DELIVERIES ADD CONSTRAINT SUPPLIER_DELIVERIES_FK1 FOREIGN KEY (AGREEMENT_NO) REFERENCES SUPPLIER_AGREEMENTS (AGREEMENT_NO) 

ALTER TABLE SUPPLIER_DELIVERIES ADD CONSTRAINT SUPPLIER_DELIVERIES_FK2 FOREIGN KEY (SUPPLIER_SITE_ID) REFERENCES SUPPLIER_SITES (SITE_ID) 

INSERT INTO SUPPLIERS(SUPPLIER_ID, NAME) VALUES(1, 'SUPPLIER 1') 

INSERT INTO SUPPLIERS(SUPPLIER_ID, NAME) VALUES(2, 'SUPPLIER 2') 

INSERT INTO SUPPLIER_SITES(SITE_ID, NAME, SUPPLIER_ID) VALUES(1, 'SUPPLIER 1 SITE A', 1) 

INSERT INTO SUPPLIER_SITES(SITE_ID, NAME, SUPPLIER_ID) VALUES(2, 'SUPPLIER 2 SITE B', 2) 

INSERT INTO SUPPLIER_AGREEMENTS(AGREEMENT_NO, SUPPLIER_ID) VALUES('111', 1) 

INSERT INTO SUPPLIER_AGREEMENTS(AGREEMENT_NO, SUPPLIER_ID) VALUES('222', 2) 

INSERT INTO SUPPLIER_DELIVERIES(DELIVERY_ID, AGREEMENT_NO, SUPPLIER_SITE_ID, DELIVERY_DATE) VALUES(1, '111', 2, TRUNC(SYSDATE))