script
DESCRIPTION
scriptdwTRANSCRIPT
Here are their definition:ALTER TABLE PAUL_DW.COUNTRIESDROP PRIMARY KEY CASCADE;
DROP TABLE PAUL_DW.COUNTRIES CASCADE CONSTRAINTS;
CREATE TABLE PAUL_DW.COUNTRIES(COUNTRY_ID NUMBER(20),COUNTRY_OFFICIAL_NAME VARCHAR2(100 BYTE))TABLESPACE USERSPCTUSED 0PCTFREE 10INITRANS 1MAXTRANS 255STORAGE (PCTINCREASE 0BUFFER_POOL DEFAULT)LOGGINGNOCOMPRESSNOCACHENOPARALLELMONITORING;
CREATE UNIQUE INDEX PAUL_DW.COUNTRIES_PK ON PAUL_DW.COUNTRIES(COUNTRY_ID)LOGGINGTABLESPACE USERSPCTFREE 10INITRANS 2MAXTRANS 255STORAGE (PCTINCREASE 0BUFFER_POOL DEFAULT)NOPARALLEL;
ALTER TABLE PAUL_DW.COUNTRIES ADD (CONSTRAINT COUNTRIES_PKPRIMARY KEY(COUNTRY_ID)USING INDEX PAUL_DW.COUNTRIES_PK);ALTER TABLE PAUL_DW.PRODUCT_TYPESDROP PRIMARY KEY CASCADE;
DROP TABLE PAUL_DW.PRODUCT_TYPES CASCADE CONSTRAINTS;
CREATE TABLE PAUL_DW.PRODUCT_TYPES(PRODUCT_TYPE_ID NUMBER(10),PRODUCT_TYPE_NAME VARCHAR2(200 BYTE))TABLESPACE USERSPCTUSED 0PCTFREE 10INITRANS 1MAXTRANS 255STORAGE (PCTINCREASE 0BUFFER_POOL DEFAULT)LOGGINGNOCOMPRESSNOCACHENOPARALLELMONITORING;
CREATE UNIQUE INDEX PAUL_DW.PRODUCT_TYPES_PK ON PAUL_DW.PRODUCT_TYPES(PRODUCT_TYPE_ID)LOGGINGTABLESPACE USERSPCTFREE 10INITRANS 2MAXTRANS 255STORAGE (PCTINCREASE 0BUFFER_POOL DEFAULT)NOPARALLEL;
ALTER TABLE PAUL_DW.PRODUCT_TYPES ADD (CONSTRAINT PRODUCT_TYPES_PKPRIMARY KEY(PRODUCT_TYPE_ID)USING INDEX PAUL_DW.PRODUCT_TYPES_PK);ALTER TABLE PAUL_DW.REGIONSDROP PRIMARY KEY CASCADE;
DROP TABLE PAUL_DW.REGIONS CASCADE CONSTRAINTS;
CREATE TABLE PAUL_DW.REGIONS(REGION_ID NUMBER(3),REGION_NAME VARCHAR2(200 BYTE),REGION_COUNTRY VARCHAR2(200 BYTE))TABLESPACE USERSPCTUSED 0PCTFREE 10INITRANS 1MAXTRANS 255STORAGE (INITIAL 64KNEXT 1MMINEXTENTS 1MAXEXTENTS UNLIMITEDPCTINCREASE 0BUFFER_POOL DEFAULT)LOGGINGNOCOMPRESSNOCACHENOPARALLELMONITORING;
CREATE UNIQUE INDEX PAUL_DW.REGIONS_PK ON PAUL_DW.REGIONS(REGION_ID)LOGGINGTABLESPACE USERSPCTFREE 10INITRANS 2MAXTRANS 255STORAGE (INITIAL 64KNEXT 1MMINEXTENTS 1MAXEXTENTS UNLIMITEDPCTINCREASE 0BUFFER_POOL DEFAULT)NOPARALLEL;
ALTER TABLE PAUL_DW.REGIONS ADD (CONSTRAINT REGIONS_PKPRIMARY KEY(REGION_ID)USING INDEX PAUL_DW.REGIONS_PK);ALTER TABLE PAUL_DW.CUSTOMERSDROP PRIMARY KEY CASCADE;
DROP TABLE PAUL_DW.CUSTOMERS CASCADE CONSTRAINTS;
CREATE TABLE PAUL_DW.CUSTOMERS(CUSTOMER_ID NUMBER(20),CUSTOMER_NAME VARCHAR2(50 BYTE),CUSTOMER_REGION_ID NUMBER(3),CUSTOMER_TEL NUMBER(15))TABLESPACE USERSPCTUSED 0PCTFREE 10INITRANS 1MAXTRANS 255STORAGE (PCTINCREASE 0BUFFER_POOL DEFAULT)LOGGINGNOCOMPRESSNOCACHENOPARALLELMONITORING;
CREATE UNIQUE INDEX PAUL_DW.CUSTOMERS_PK ON PAUL_DW.CUSTOMERS(CUSTOMER_ID)LOGGINGTABLESPACE USERSPCTFREE 10INITRANS 2MAXTRANS 255STORAGE (PCTINCREASE 0BUFFER_POOL DEFAULT)NOPARALLEL;
ALTER TABLE PAUL_DW.CUSTOMERS ADD (CONSTRAINT CUSTOMERS_PKPRIMARY KEY(CUSTOMER_ID)USING INDEX PAUL_DW.CUSTOMERS_PK);
ALTER TABLE PAUL_DW.CUSTOMERS ADD (CONSTRAINT CUSTOMERS_REGION_FKFOREIGN KEY (CUSTOMER_REGION_ID)REFERENCES PAUL_DW.REGIONS);ALTER TABLE PAUL_DW.SALESDROP PRIMARY KEY CASCADE;
DROP TABLE PAUL_DW.SALES CASCADE CONSTRAINTS;
CREATE TABLE PAUL_DW.SALES(SALE_ID NUMBER(20),PRODUCT_ID NUMBER(22),CUSTOMER_ID NUMBER(20),QUANTITY NUMBER(20),PRICE NUMBER(12,2))TABLESPACE USERSPCTUSED 0PCTFREE 10INITRANS 1MAXTRANS 255STORAGE (PCTINCREASE 0BUFFER_POOL DEFAULT)LOGGINGNOCOMPRESSNOCACHENOPARALLELMONITORING;
CREATE UNIQUE INDEX PAUL_DW.SALES_PK ON PAUL_DW.SALES(SALE_ID)LOGGINGTABLESPACE USERSPCTFREE 10INITRANS 2MAXTRANS 255STORAGE (PCTINCREASE 0BUFFER_POOL DEFAULT)NOPARALLEL;
ALTER TABLE PAUL_DW.SALES ADD (CONSTRAINT SALES_PKPRIMARY KEY(SALE_ID)USING INDEX PAUL_DW.SALES_PK);
ALTER TABLE PAUL_DW.SALES ADD (CONSTRAINT SQLES_CUSTOMER_FKFOREIGN KEY (CUSTOMER_ID)REFERENCES PAUL_DW.CUSTOMERS,CONSTRAINT SQLES_PRODUCT_FKFOREIGN KEY (PRODUCT_ID)REFERENCES PAUL_DW.PRODUCTS);Here is the code to populate these tables:Insert into COUNTRIES(COUNTRY_ID, COUNTRY_OFFICIAL_NAME)Values(1, 'Country 1');Insert into COUNTRIES(COUNTRY_ID, COUNTRY_OFFICIAL_NAME)Values(2, 'Country 2');Insert into COUNTRIES(COUNTRY_ID, COUNTRY_OFFICIAL_NAME)Values(3, 'Country 3');COMMIT;Insert into PRODUCT_TYPES(PRODUCT_TYPE_ID, PRODUCT_TYPE_NAME)Values(1, 'fruits');Insert into PRODUCT_TYPES(PRODUCT_TYPE_ID, PRODUCT_TYPE_NAME)Values(2, 'vegetables');Insert into PRODUCT_TYPES(PRODUCT_TYPE_ID, PRODUCT_TYPE_NAME)Values(3, 'electronical equipments');Insert into PRODUCT_TYPES(PRODUCT_TYPE_ID, PRODUCT_TYPE_NAME)Values(4, 'clothes');COMMIT;Insert into REGIONS(REGION_ID, REGION_NAME, REGION_COUNTRY)Values(1, 'Region 1 C1', 'Country 1');Insert into REGIONS(REGION_ID, REGION_NAME, REGION_COUNTRY)Values(2, 'Region 2 C1', 'Country 1');Insert into REGIONS(REGION_ID, REGION_NAME, REGION_COUNTRY)Values(3, 'Region 3 C1', 'Country 1');Insert into REGIONS(REGION_ID, REGION_NAME, REGION_COUNTRY)Values(4, 'Region 1 C2', 'Country 2');Insert into REGIONS(REGION_ID, REGION_NAME, REGION_COUNTRY)Values(5, 'Region 1 C3', 'Country 3');Insert into REGIONS(REGION_ID, REGION_NAME, REGION_COUNTRY)Values(6, 'Region 2 C3', 'Country 3');COMMIT;Insert into CUSTOMERS(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)Values(1, 'Mr. King', 1, 8003456744);Insert into CUSTOMERS(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)Values(2, 'John Smith', 1, 345345344);Insert into CUSTOMERS(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)Values(3, 'Adams', 2, 345634534);Insert into CUSTOMERS(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)Values(4, 'Brown', 3, 234234242);Insert into CUSTOMERS(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)Values(5, 'Allan', 4, 53534534);Insert into CUSTOMERS(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)Values(6, 'Elena', 5, 435345345);Insert into CUSTOMERS(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)Values(7, 'Steve', 6, 345345345);Insert into CUSTOMERS(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)Values(8, 'Maria', 6, 2345234224);Insert into CUSTOMERS(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)Values(9, 'John', 1, 34534534);COMMIT;Insert into PRODUCTS(PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE_ID, PRODUCT_COUNTRY_ID)Values(1, 'apples', 1, 1);Insert into PRODUCTS(PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE_ID, PRODUCT_COUNTRY_ID)Values(2, 'apples', 1, 2);Insert into PRODUCTS(PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE_ID, PRODUCT_COUNTRY_ID)Values(3, 'apples', 1, 3);Insert into PRODUCTS(PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE_ID, PRODUCT_COUNTRY_ID)Values(4, 'grapes', 1, 1);Insert into PRODUCTS(PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE_ID, PRODUCT_COUNTRY_ID)Values(5, 'cabbage', 2, 1);Insert into PRODUCTS(PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE_ID, PRODUCT_COUNTRY_ID)Values(6, 'carrot', 2, 1);Insert into PRODUCTS(PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE_ID, PRODUCT_COUNTRY_ID)Values(7, 'HDD Seagate', 3, 2);COMMIT;Insert into SALES(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)Values(1, 1, 1, 1, 10);Insert into SALES(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)Values(2, 1, 2, 2, 1);Insert into SALES(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)Values(3, 2, 4, 20, 1);Insert into SALES(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)Values(4, 3, 3, 1, 1);Insert into SALES(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)Values(5, 3, 5, 1, 1);Insert into SALES(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)Values(6, 4, 4, 2, 2);Insert into SALES(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)Values(7, 5, 6, 11, 1);Insert into SALES(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)Values(8, 6, 1, 11, 2);Insert into SALES(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)Values(9, 7, 7, 12, 1);Insert into SALES(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)Values(10, 7, 8, 1, 1);Insert into SALES(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)Values(11, 2, 9, 2, 2);Insert into SALES(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)Values(12, 5, 4, 3, 1);Insert into SALES(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)Values(13, 3, 6, 3, 2);Insert into SALES(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)Values(14, 1, 5, 2, 1);COMMIT;