|
Note: This article ( named
Create Oracle tables for the source schema
) was taken from
www.in-oracle.com.
My examples are mainly created on the following tables (created in
PAUL_DW schema, but we can use another schema as well):
SALES, PRODUCTS, CUSTOMERS, REGIONS, PRODUCT_TYPES, COUNTRIES
Here are their definition:
ALTER TABLE PAUL_DW.COUNTRIES
DROP 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 USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX PAUL_DW.COUNTRIES_PK ON PAUL_DW.COUNTRIES
(COUNTRY_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE PAUL_DW.COUNTRIES ADD (
CONSTRAINT COUNTRIES_PK
PRIMARY KEY
(COUNTRY_ID)
USING INDEX PAUL_DW.COUNTRIES_PK);
ALTER TABLE PAUL_DW.PRODUCT_TYPES
DROP 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 USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX PAUL_DW.PRODUCT_TYPES_PK ON PAUL_DW.PRODUCT_TYPES
(PRODUCT_TYPE_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE PAUL_DW.PRODUCT_TYPES ADD (
CONSTRAINT PRODUCT_TYPES_PK
PRIMARY KEY
(PRODUCT_TYPE_ID)
USING INDEX PAUL_DW.PRODUCT_TYPES_PK);
ALTER TABLE PAUL_DW.REGIONS
DROP 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 USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX PAUL_DW.REGIONS_PK ON PAUL_DW.REGIONS
(REGION_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE PAUL_DW.REGIONS ADD (
CONSTRAINT REGIONS_PK
PRIMARY KEY
(REGION_ID)
USING INDEX PAUL_DW.REGIONS_PK);
ALTER TABLE PAUL_DW.CUSTOMERS
DROP 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 USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX PAUL_DW.CUSTOMERS_PK ON PAUL_DW.CUSTOMERS
(CUSTOMER_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE PAUL_DW.CUSTOMERS ADD (
CONSTRAINT CUSTOMERS_PK
PRIMARY KEY
(CUSTOMER_ID)
USING INDEX PAUL_DW.CUSTOMERS_PK);
ALTER TABLE PAUL_DW.CUSTOMERS ADD (
CONSTRAINT CUSTOMERS_REGION_FK
FOREIGN KEY (CUSTOMER_REGION_ID)
REFERENCES PAUL_DW.REGIONS);
ALTER TABLE PAUL_DW.SALES
DROP 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 USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX PAUL_DW.SALES_PK ON PAUL_DW.SALES
(SALE_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE PAUL_DW.SALES ADD (
CONSTRAINT SALES_PK
PRIMARY KEY
(SALE_ID)
USING INDEX PAUL_DW.SALES_PK);
ALTER TABLE PAUL_DW.SALES ADD (
CONSTRAINT SQLES_CUSTOMER_FK
FOREIGN KEY (CUSTOMER_ID)
REFERENCES PAUL_DW.CUSTOMERS,
CONSTRAINT SQLES_PRODUCT_FK
FOREIGN 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;
Note: This is done just for create a quick example. In real life, the
logical model is more complex to serve a real situation.
Note: This article ( named
Create Oracle tables for the source schema
) was taken from
www.in-oracle.com.
|