CREATE TABLE "MADANG"."LOCATIONS" 

   ( "LOCATION_ID" NUMBER(4,0), 

"STREET_ADDRESS" VARCHAR2(40 BYTE), 

"POSTAL_CODE" VARCHAR2(12 BYTE), 

"CITY" VARCHAR2(30 BYTE) CONSTRAINT "LOC_CITY_NN" NOT NULL ENABLE, 

"STATE_PROVINCE" VARCHAR2(25 BYTE), 

"COUNTRY_ID" CHAR(2 BYTE), 

CONSTRAINT "LOC_ID_PK" PRIMARY KEY ("LOCATION_ID")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"  ENABLE, 

CONSTRAINT "LOC_C_ID_FK" FOREIGN KEY ("COUNTRY_ID")

  REFERENCES "MADANG"."COUNTRIES" ("COUNTRY_ID") ENABLE

   ) SEGMENT CREATION IMMEDIATE 

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ;

 


   COMMENT ON COLUMN "MADANG"."LOCATIONS"."LOCATION_ID" IS 'Primary key of locations table';

 

   COMMENT ON COLUMN "MADANG"."LOCATIONS"."STREET_ADDRESS" IS 'Street address of an office, warehouse, or production site of a company.

Contains building number and street name';

 

   COMMENT ON COLUMN "MADANG"."LOCATIONS"."POSTAL_CODE" IS 'Postal code of the location of an office, warehouse, or production site 

of a company. ';

 

   COMMENT ON COLUMN "MADANG"."LOCATIONS"."CITY" IS 'A not null column that shows city where an office, warehouse, or 

production site of a company is located. ';

 

   COMMENT ON COLUMN "MADANG"."LOCATIONS"."STATE_PROVINCE" IS 'State or Province where an office, warehouse, or production site of a 

company is located.';

 

   COMMENT ON COLUMN "MADANG"."LOCATIONS"."COUNTRY_ID" IS 'Country where an office, warehouse, or production site of a company is

located. Foreign key to country_id column of the countries table.';

 

   COMMENT ON TABLE "MADANG"."LOCATIONS"  IS 'Locations table that contains specific address of a specific office,

warehouse, and/or production site of a company. Does not store addresses /

locations of customers. Contains 23 rows; references with the

departments and countries tables. ';

 


  CREATE INDEX "MADANG"."LOC_CITY_IX" ON "MADANG"."LOCATIONS" ("CITY") 

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ;

 


  CREATE INDEX "MADANG"."LOC_COUNTRY_IX" ON "MADANG"."LOCATIONS" ("COUNTRY_ID") 

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ;

 


  CREATE INDEX "MADANG"."LOC_STATE_PROVINCE_IX" ON "MADANG"."LOCATIONS" ("STATE_PROVINCE") 

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ;

 



CREATE TABLE "MADANG"."COUNTRIES" 

   ( "COUNTRY_ID" CHAR(2 BYTE) CONSTRAINT "COUNTRY_ID_NN" NOT NULL ENABLE, 

"COUNTRY_NAME" VARCHAR2(40 BYTE), 

"REGION_ID" NUMBER, 

CONSTRAINT "COUNTRY_C_ID_PK" PRIMARY KEY ("COUNTRY_ID") ENABLE, 

CONSTRAINT "COUNTR_REG_FK" FOREIGN KEY ("REGION_ID")

  REFERENCES "MADANG"."REGIONS" ("REGION_ID") ENABLE

   ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" 

 PCTTHRESHOLD 50;

 


   COMMENT ON COLUMN "MADANG"."COUNTRIES"."COUNTRY_ID" IS 'Primary key of countries table.';

 

   COMMENT ON COLUMN "MADANG"."COUNTRIES"."COUNTRY_NAME" IS 'Country name';

 

   COMMENT ON COLUMN "MADANG"."COUNTRIES"."REGION_ID" IS 'Region ID for the country. Foreign key to region_id column in the departments table.';

 

   COMMENT ON TABLE "MADANG"."COUNTRIES"  IS 'country table. Contains 25 rows. References with locations table.';