-- This is a version of table creation script
-- Stoney Gan

CREATE TABLE "MR_APPL"."RESPONSIBLE_ENTITY_TBL" 
(	"RESPONSIBLE_ENTITY_ID" 	NUMBER(10) NOT NULL, 
	"RESPONSIBLE_ENTITY_DESC" 	VARCHAR2(256) NOT NULL, 
	"AUTHORITY"				VARCHAR2(512) NOT NULL,
	"CONTACT_STREET_ADDRESS1" VARCHAR2(50) NOT NULL, 
	"CONTACT_STREET_ADDRESS2" VARCHAR2(50), 
	"CONTACT_CITY" 		 VARCHAR2(20) NOT NULL, 
	"CONTACT_STATE" 		CHAR(2) NOT NULL, 
	"CONTACT_ZIP" 		VARCHAR2(10) NOT NULL, 
	"CONTACT_COUNTRY" 	VARCHAR2(30) DEFAULT 'United States' NOT NULL, 
    	CONSTRAINT "responsible_entity_pk" PRIMARY KEY("RESPONSIBLE_ENTITY_ID")
)   
TABLESPACE LOCALADM_TS;
REVOKE ALL ON RESPONSIBLE_ENTITY_TBL FROM PUBLIC;
GRANT SELECT ON RESPONSIBLE_ENTITY_TBL TO PUBLIC;

CREATE TABLE "MR_APPL"."CONTACT_INFO_TBL" 
(	"CONTACT_INFO_ID" 	NUMBER(10) NOT NULL, 
	"RESPONSIBLE_ENTITY_ID" NUMBER(10) NOT NULL,
	"CONTACT_PERSON_FN" 	VARCHAR2(20) NOT NULL, 
	"CONTACT_PERSON_LN" 	VARCHAR2(20) NOT NULL, 
	"CONTACT_PERSON_ROLE"	VARCHAR2(30),
	"CONTACT_CITY" 		VARCHAR2(20) NOT NULL, 
	"CONTACT_STATE" 		CHAR(2) NOT NULL,
	"CONTACT_COUNTRY" 	VARCHAR2(30) DEFAULT 'United States' NOT NULL, 
	"CONTACT_HOME_PHONE"	VARCHAR2(18),
	"CONTACT_OFFICE_PHONE"	VARCHAR2(18),
	"CONTACT_CELL_PHONE"	VARCHAR2(18),
	"CONTACT_FAX_NUMBER"	VARCHAR2(18),
	"CONTACT_EMAIL"		VARCHAR2(30),
	"CONTACT_EMAIL2"		VARCHAR2(30),
    	CONSTRAINT "contack_pk" PRIMARY KEY("CONTACT_INFO_ID"),
	CONSTRAINT "RESPONSIBLE_ENTITY_FK" FOREIGN KEY("RESPONSIBLE_ENTITY_ID") 
    		REFERENCES "MR_APPL"."RESPONSIBLE_ENTITY_TBL"("RESPONSIBLE_ENTITY_ID")
)  
TABLESPACE LOCALADM_TS;
REVOKE ALL ON CONTACT_INFO_TBL FROM PUBLIC;
GRANT SELECT ON CONTACT_INFO_TBL TO PUBLIC;

-- DEPRECATED ON 05-15-2002 
--CREATE TABLE "MR_APPL"."BRAND_TBL" 
--(	"BRAND_ID" 			NUMBER(10) NOT NULL, 
--	"BRAND_SOURCE_URL" 	VARCHAR2(1024) NOT NULL, 
--	CONSTRAINT "brank_pk" PRIMARY KEY("BRAND_ID")
--)  
--TABLESPACE LOCALADM_TS;
--REVOKE ALL ON BRAND_TBL FROM PUBLIC;
--GRANT SELECT ON BRAND_TBL TO PUBLIC;


-- DEPRECATED ON 05-15-2002 BUT i DO NOT LIKE THE IDEA.
-- put it back on 05-28-02
CREATE TABLE "MR_APPL"."NSDL_AUTHORITY_TBL" 
(	"NSDL_AUTHORITY" 			VARCHAR2(50) NOT NULL, 
	"RESPONSIBLE_ENTITY_ID" 	NUMBER(10) NOT NULL, 
  	CONSTRAINT "nsdl_authority_pk" PRIMARY KEY("NSDL_AUTHORITY"),
	CONSTRAINT "nsdl_authority_fk" FOREIGN KEY("RESPONSIBLE_ENTITY_ID")
		REFERENCES "MR_APPL"."RESPONSIBLE_ENTITY_TBL"("RESPONSIBLE_ENTITY_ID")
)
TABLESPACE LOCALADM_TS;
REVOKE ALL ON NSDL_AUTHORITY_TBL FROM PUBLIC;
GRANT SELECT ON NSDL_AUTHORITY_TBL TO PUBLIC;

CREATE TABLE "RECORD_CATEGORY_TBL"
(
	"CATEGORY_ID" 	NUMBER(3) NOT NULL,
	"CATEGORY_NAME"	VARCHAR2(512) NOT NULL,	
	CONSTRAINT "record_category_pk" PRIMARY KEY("CATEGORY_ID")
)	TABLESPACE MREC_TS;
	REVOKE ALL ON RECORD_CATEGORY_TBL FROM PUBLIC;
	GRANT ALL ON RECORD_CATEGORY_TBL TO PUBLIC;

CREATE TABLE "MR_APPL"."MREC_TBL" 
(	"MREC_ID" 		NUMBER(15) NOT NULL,
    	"MREC_CATEGORY_ID" 	NUMBER(3) NOT NULL, 
	"NSDL_CREATE_DATE" 	TIMESTAMP(6) NOT NULL, 
	"LAST_UPDATE_DATE" 	TIMESTAMP(6) NOT NULL, 
	"LAST_INGEST_DATE"	TIMESTAMP(6) NOT NULL,
	"ORIGINAL_CREATE_DATE"  TIMESTAMP(6),
	"ORIGINAL_MOD_DATE"	TIMESTAMP(6),
	"DELETE_FLAG" 	CHAR(1) DEFAULT 'N' NOT NULL, 
	"NATIVE_ID" 	VARCHAR2(30) NOT NULL, 
	"NSDL_UNIQUE_ID"	VARCHAR2(100) NOT NULL,
	"UPDATE_FLAG"	CHAR (1) DEFAULT 'N' NOT NULL,
    	CONSTRAINT "mrec_pk" PRIMARY KEY("MREC_ID"),
	CONSTRAINT "mrec_fk1" FOREIGN KEY("MREC_CATEGORY_ID") 
		REFERENCES "MR_APPL"."RECORD_CATEGORY_TBL"("CATEGORY_ID")
)  
TABLESPACE MREC_TS;
REVOKE ALL ON MREC_TBL FROM PUBLIC;
GRANT SELECT ON MREC_TBL TO PUBLIC;
CREATE UNIQUE INDEX "MR_APPL"."MREC_IDX1" 
    ON "MR_APPL"."MREC_TBL"("NSDL_UNIQUE_ID")
    TABLESPACE "INDX";

CREATE TABLE "AGGREGATION_INFO_TBL"
(
	"MREC_ID"		NUMBER(15) NOT NULL,
	"AGGREGATION_CATEGORY_ID" 	NUMBER(3) NOT NULL,
	"BRAND_URL"		VARCHAR2(512) NOT NULL,
	"AUTHORITY"			VARCHAR2(1024) NOT NULL,
	"RESPONSIBLE_ENTITY_ID"	NUMBER(10) NOT NULL,
	"CONTACT_INFO_ID"		NUMBER(10) NOT NULL,	
	CONSTRAINT "aggregation_info_pk" PRIMARY KEY("MREC_ID"),
	CONSTRAINT "aggregation_info_fk1" FOREIGN KEY("MREC_ID")
		REFERENCES "MR_APPL"."MREC_TBL"("MREC_ID"),
	CONSTRAINT "aggregation_info_fk2" FOREIGN KEY("AGGREGATION_CATEGORY_ID")
		REFERENCES "MR_APPL"."RECORD_CATEGORY_TBL"("CATEGORY_ID"),
	CONSTRAINT "aggregation_info_fk3" FOREIGN KEY("RESPONSIBLE_ENTITY_ID")
		REFERENCES "MR_APPL"."RESPONSIBLE_ENTITY_TBL"("RESPONSIBLE_ENTITY_ID"),
	CONSTRAINT "aggregation_info_fk4" FOREIGN KEY("CONTACT_INFO_ID")
		REFERENCES "MR_APPL"."CONTACT_INFO_TBL"("CONTACT_INFO_ID")
)	TABLESPACE MREC_TS;
	REVOKE ALL ON AGGREGATION_INFO_TBL FROM PUBLIC;
	GRANT ALL ON AGGREGATION_INFO_TBL TO PUBLIC;

CREATE TABLE "MR_APPL"."ELEMENT_NAME_TBL" 
(	"ELEMENT_NAME_ID" 	NUMBER(3) NOT NULL, 
	"ELEMENT_NAME_TEXT" 	VARCHAR2(50) NOT NULL, 
	CONSTRAINT "element_name_pk" PRIMARY KEY("ELEMENT_NAME_ID")
)  
TABLESPACE MREC_TS;
REVOKE ALL ON ELEMENT_NAME_TBL FROM PUBLIC;
GRANT SELECT ON ELEMENT_NAME_TBL TO PUBLIC;


CREATE TABLE "MR_APPL"."ELEMENT_TYPE_TBL" 
(	"ELEMENT_TYPE_ID" 	NUMBER(3) NOT NULL, 
	"ELEMENT_TYPE_TEXT" 	VARCHAR2(50) NOT NULL, 
	CONSTRAINT "element_type_pk" PRIMARY KEY("ELEMENT_TYPE_ID")
)  
TABLESPACE MREC_TS;
REVOKE ALL ON ELEMENT_TYPE_TBL FROM PUBLIC;
GRANT SELECT ON ELEMENT_TYPE_TBL TO PUBLIC;

-- has to be dba to create type
--CREATE TYPE ELEMENT_VALUE_TY AS OBJECT
--(
--VALUE_STR1		VARCHAR2(4000),
--VALUE_STR2		VARCHAR2(4000)
--);

CREATE TABLE "MR_APPL"."ELEMENT_TBL" 
(	"ELEMENT_ID" 	NUMBER(20) NOT NULL, 
	"MREC_ID" 		NUMBER(15) NOT NULL, 
	"ELEMENT_NAME_ID" 	NUMBER(3) NOT NULL, 
	"ELEMENT_TYPE_ID" 	NUMBER(3),
    	"ELEMENT_SCHEME" 	VARCHAR2(50), 
	"ELEMENT_VALUE" 	ELEMENT_VALUE_TY NOT NULL, 
	"ELEMENT_LANGUAGE" VARCHAR2(50),
	"ELEMENT_SEQ" 	NUMBER(3) DEFAULT 0 NOT NULL, 
    	CONSTRAINT "element_pk" PRIMARY KEY("ELEMENT_ID"),
	CONSTRAINT "element_fk" FOREIGN KEY("MREC_ID") 
		REFERENCES "MR_APPL"."MREC_TBL"("MREC_ID"),
	CONSTRAINT "element_fk2" FOREIGN KEY("ELEMENT_NAME_ID") 
		REFERENCES "MR_APPL"."ELEMENT_NAME_TBL"("ELEMENT_NAME_ID"), 
	CONSTRAINT "element_fk3" FOREIGN KEY("ELEMENT_TYPE_ID") 
		REFERENCES "MR_APPL"."ELEMENT_TYPE_TBL"("ELEMENT_TYPE_ID") 
)  
TABLESPACE MREC_TS;
REVOKE ALL ON ELEMENT_TBL FROM PUBLIC;
GRANT SELECT ON ELEMENT_TBL TO PUBLIC;

CREATE TABLE "MR_APPL"."METADATA_FORMAT_TBL"
(
	METADATA_FORMAT	VARCHAR2(20) NOT NULL,
	CONSTRAINT "metadata_format_pk" PRIMARY KEY ("METADATA_FORMAT")
) TABLESPACE OAISERVER_TS;
REVOKE ALL ON METADATA_FORMAT_TBL FROM PUBLIC;
GRANT SELECT ON METADATA_FORMAT_TBL TO PUBLIC;

CREATE TABLE "MR_APPL"."XML_SCHEMA_TBL"
(
	SCHEMA_ID	NUMBER(3) NOT NULL,
	SCHEMA_NAME	VARCHAR2(50) NOT NULL,
	SCHEMA_NAMESPACE	VARCHAR2(1024),
	SCHEMA_LOCATION	VARCHAR2(1024) NOT NULL,
	SCHEMA_VERSION	VARCHAR(20),
	SCHEMA_CONTENT	SYS.XMLTYPE,
	CONSTRAINT "xml_schema_pk" PRIMARY KEY ("SCHEMA_ID")
) TABLESPACE OAISERVER_TS;
REVOKE ALL ON XML_SCHEMA_TBL FROM PUBLIC;
GRANT SELECT ON XML_SCHEMA_TBL TO PUBLIC;


CREATE TABLE "MR_APPL"."NSDL_XML_TBL" 
(	"NSDL_UNIQUE_ID"		VARCHAR2(100) NOT NULL, 
	"MREC_ID" 			NUMBER(15) NOT NULL,
	"METADATA_FORMAT" 	VARCHAR2(10) DEFAULT 'DC' NOT NULL,
	"HARVESTABLE_FLAG" 	CHAR(1) DEFAULT 'Y' NOT NULL,
	"DELETE_FLAG"		CHAR(1) DEFAULT 'N' NOT NULL,
	"OAI_MOD_DATE"		TIMESTAMP(6) NOT NULL,
	"SCHEMA_ID"			NUMBER(3) NOT NULL,
	"NATIVE_XML"		SYS.XMLTYPE NOT NULL,
   	CONSTRAINT "other_xml_pk" PRIMARY KEY("NSDL_UNIQUE_ID"),
	CONSTRAINT "other_xml_fk1" FOREIGN KEY("MREC_ID") 
    		REFERENCES "MR_APPL"."MREC_TBL"("MREC_ID"),
	CONSTRAINT "other_xml_fk2" FOREIGN KEY("METADATA_FORMAT") 
    		REFERENCES "MR_APPL"."METADATA_FORMAT_TBL"("METADATA_FORMAT"),
	CONSTRAINT "other_xml_fk3" FOREIGN KEY ("SCHEMA_ID")
		REFERENCES "MR_APPL"."XML_SCHEMA_TBL"("SCHEMA_ID")
)  
  ORGANIZATION INDEX TABLESPACE MREC_TS
  PCTTHRESHOLD 20
  OVERFLOW TABLESPACE OVERFLOW_TS;
REVOKE ALL ON NSDL_XML_TBL FROM PUBLIC;
GRANT SELECT ON NSDL_XML_TBL TO PUBLIC;
CREATE UNIQUE INDEX nsdl_xml_idx1 on NSDL_XML_TBL("MREC_ID", "METADATA_FORMAT") tablespace indx;

CREATE TABLE "MR_APPL"."OAI_XML_TBL" 
(	"NSDL_UNIQUE_ID"		VARCHAR2(100) NOT NULL,
	"MREC_ID" 			NUMBER(15) NOT NULL,
	"METADATA_FORMAT" 	VARCHAR2(10) DEFAULT 'DC' NOT NULL,
	"HARVESTABLE_FLAG" 	CHAR(1) DEFAULT 'Y' NOT NULL,
	"NATIVE_XML"		SYS.XMLTYPE NOT NULL,
  	"SCHEMA_ID"			NUMBER(3) NOT NULL,
   	CONSTRAINT "oai_xml_pk" PRIMARY KEY("NSDL_UNIQUE_ID"),
	CONSTRAINT "oai_xml_fk1" FOREIGN KEY("MREC_ID") 
    		REFERENCES "MR_APPL"."MREC_TBL"("MREC_ID"),
	CONSTRAINT "aoi_xml_fk2" FOREIGN KEY("METADATA_FORMAT") 
    		REFERENCES "MR_APPL"."METADATA_FORMAT_TBL"("METADATA_FORMAT"),
	CONSTRAINT "oai_xml_fk3" FOREIGN KEY ("SCHEMA_ID")
		REFERENCES "MR_APPL"."XML_SCHEMA_TBL"("SCHEMA_ID")
)  
  ORGANIZATION INDEX TABLESPACE OAISERVER_TS
  PCTTHRESHOLD 20
  OVERFLOW TABLESPACE OVERFLOW_TS;
  REVOKE ALL ON OAI_XML_TBL FROM PUBLIC;
  GRANT SELECT ON OAI_XML_TBL TO PUBLIC;
  CREATE UNIQUE INDEX oai_xml_idx1 on OAI_XML_TBL("MREC_ID", "METADATA_FORMAT") tablespace indx;

 
CREATE TABLE "MR_APPL"."LINK_TYPE_TBL" 
(	"LINK_TYPE_ID" 	NUMBER(3) NOT NULL, 
	"LINK_TYPE_NAME" VARCHAR2(50) NOT NULL,
    	CONSTRAINT "link_type_pk" PRIMARY KEY("LINK_TYPE_ID")
)
TABLESPACE MREC_TS;
REVOKE ALL ON LINK_TYPE_TBL FROM PUBLIC;
GRANT SELECT ON LINK_TYPE_TBL TO PUBLIC;

CREATE TABLE "MR_APPL"."NSDL_LINK_TBL" 
(	"PARENT_ID" 	NUMBER(20) NOT NULL, 
	"CHILD_ID" 		NUMBER(20) NOT NULL, 
	"LINK_TYPE_ID" 	NUMBER(3) NOT NULL, 
	"LINK_SEQ" 		NUMBER(4) DEFAULT 1 NOT NULL,
    	CONSTRAINT "nsdl_link_pk" PRIMARY KEY("PARENT_ID", "CHILD_ID","LINK_SEQ"),
	CONSTRAINT "nsdl_link_fk1" FOREIGN KEY("LINK_TYPE_ID")
    		REFERENCES "MR_APPL"."LINK_TYPE_TBL"("LINK_TYPE_ID"), 
    	CONSTRAINT "nsdl_link_fk2" FOREIGN KEY("PARENT_ID") 
    		REFERENCES "MR_APPL"."MREC_TBL"("MREC_ID"), 
    	CONSTRAINT "nsdl_link_fk3" FOREIGN KEY("CHILD_ID") 
    		REFERENCES "MR_APPL"."MREC_TBL"("MREC_ID") 
)  
TABLESPACE NSDLLINK_TS;
REVOKE ALL ON NSDL_LINK_TBL FROM PUBLIC;
GRANT SELECT ON NSDL_LINK_TBL TO PUBLIC;


CREATE TABLE "MR_APPL"."UPDATE_LOG_TBL" 
(	"MREC_ID" 			NUMBER(15) NOT NULL, 
	"NSDL_UPDATE_DATE" 	TIMESTAMP(6), 
	"UPDATE_USER" 		VARCHAR2(30) NOT NULL, 
  	CONSTRAINT "update_log_pk" PRIMARY KEY("MREC_ID", "NSDL_UPDATE_DATE"),
	CONSTRAINT "update_log_fk" FOREIGN KEY("MREC_ID") 
    		REFERENCES "MR_APPL"."MREC_TBL"("MREC_ID")
)
TABLESPACE LOCALADM_TS;
REVOKE ALL ON UPDATE_LOG_TBL FROM PUBLIC;
GRANT SELECT ON UPDATE_LOG_TBL TO PUBLIC;

CREATE TABLE "MR_APPL"."OAI_SET_TBL"
(	"OAI_SET_ID"	NUMBER(10) NOT NULL,
	"OAI_SET_DESC"	VARCHAR2(1000),
	"NSDL_AUTHORITY"	VARCHAR2(50),
	"OAI_SET_NAME"	VARCHAR2(1024),
	"OAI_SET_SPEC"	VARCHAR2(1024),
	CONSTRAINT "oai_set_pk" PRIMARY KEY("OAI_SET_ID"),
	CONSTRAINT "oai_set_fk" FOREIGN KEY("NSDL_AUTHORITY")
		REFERENCES "MR_APPL"."NSDL_AUTHORITY_TBL"("NSDL_AUTHORITY")
) 
TABLESPACE OAISERVER_TS;
REVOKE ALL ON OAI_SET_TBL FROM PUBLIC;
GRANT ALL ON OAI_SET_TBL TO PUBLIC;

CREATE TABLE "MR_APPL"."OAI_LINK_TBL" 
(	"OAI_SET_ID" 	NUMBER(10) NOT NULL, 
	"MREC_ID" 		NUMBER(10) NOT NULL, 
	"LINK_TYPE_ID" 	NUMBER(15) NOT NULL, 
	"LINK_SEQ" 		NUMBER(4) DEFAULT 1 NOT NULL,
    	CONSTRAINT "oai_link_pk" PRIMARY KEY("OAI_SET_ID", "MREC_ID","LINK_SEQ"),
	CONSTRAINT "oai_link_fk1" FOREIGN KEY("OAI_SET_ID") 
    		REFERENCES "MR_APPL"."OAI_SET_TBL"("OAI_SET_ID"), 
	CONSTRAINT "oai_link_fk3" FOREIGN KEY("MREC_ID") 
    		REFERENCES "MR_APPL"."MREC_TBL"("MREC_ID"),
	CONSTRAINT "oai_link_fk2" FOREIGN KEY("LINK_TYPE_ID")
    		REFERENCES "MR_APPL"."LINK_TYPE_TBL"("LINK_TYPE_ID") 
) 
TABLESPACE OAISERVER_TS;
REVOKE ALL ON OAI_LINK_TBL FROM PUBLIC;
GRANT SELECT ON OAI_LINK_TBL TO PUBLIC;

CREATE TABLE "MR_APPL"."OAISET_DESC_TBL"
(	
	"OAISET_DESC_ID"	NUMBER(15),
      "OAI_SET_ID"	NUMBER(10) NOT NULL,
	"OAISET_DESC"	SYS.XMLTYPE,
	"DESC_SCHEMA_ID" NUMBER(5),
	CONSTRAINT "oaiset_desc_pk" PRIMARY KEY("OAISET_DESC_ID"),
	CONSTRAINT "oaiset_desc_fk1" FOREIGN KEY("OAI_SET_ID")
		REFERENCES "MR_APPL"."OAI_SET_TBL"("OAI_SET_ID"),
	CONSTRAINT "oaiset_desc_fk2" FOREIGN KEY("DESC_SCHEMA_ID")
		REFERENCES "MR_APPL"."XML_SCHEMA_TBL"("SCHEMA_ID")
) TABLESPACE OAISERVER_TS;
REVOKE ALL ON OAISET_DESC_TBL FROM PUBLIC;
GRANT ALL ON OAISET_DESC_TBL TO PUBLIC;

CREATE TABLE "MREC_ABOUT_TBL" 
(
  	"MREC_ABOUT_ID"	NUMBER(20) NOT NULL,
	"MREC_ID"		NUMBER(15) NOT NULL,
 	"MREC_ABOUT"	SYS.XMLTYPE NOT NULL,
  	CONSTRAINT "mrec_about_pk" PRIMARY KEY("MREC_ABOUT_ID"),
	CONSTRAINT "oaiset_desc_fk" FOREIGN KEY("MREC_ID")
		REFERENCES "MR_APPL"."MREC_TBL"("MREC_ID")
)TABLESPACE MREC_TS;
REVOKE ALL ON MREC_ABOUT_TBL FROM PUBLIC;
GRANT ALL ON MREC_ABOUT_TBL TO PUBLIC;


CREATE TABLE "RECORD_CATEGORY_TBL"
(
	"CATEGORY_ID" 	NUMBER(3) NOT NULL,
	"CATEGORY_NAME"	VARCHAR2(512) NOT NULL,	
	CONSTRAINT "record_category_pk" PRIMARY KEY("CATEGORY_ID")
)	TABLESPACE MREC_TS;
	REVOKE ALL ON RECORD_CATEGORY_TBL FROM PUBLIC;
	GRANT ALL ON RECORD_CATEGORY_TBL TO PUBLIC;

CREATE TABLE "AGGREGATION_INFO_TBL"
(
	"MREC_ID"		NUMBER(15) NOT NULL,
	"AGGREGATION_CATEGORY_ID" 	NUMBER(3) NOT NULL,
	"BRAND_URL"		VARCHAR2(512) NOT NULL,
	"AUTHORITY"			VARCHAR2(1024) NOT NULL,
	"RESPONSIBLE_ENTITY_ID"	NUMBER(10) NOT NULL,
	"CONTACT_INFO_ID"		NUMBER(10) NOT NULL,	
	CONSTRAINT "aggregation_info_pk" PRIMARY KEY("MREC_ID"),
	CONSTRAINT "aggregation_info_fk1" FOREIGN KEY("MREC_ID")
		REFERENCES "MR_APPL"."MREC_TBL"("MREC_ID"),
	CONSTRAINT "aggregation_info_fk2" FOREIGN KEY("AGGREGATION_CATEGORY_ID")
		REFERENCES "MR_APPL"."RECORD_CATEGORY_TBL"("CATEGORY_ID"),
	CONSTRAINT "aggregation_info_fk3" FOREIGN KEY("RESPONSIBLE_ENTITY_ID")
		REFERENCES "MR_APPL"."RESPONSIBLE_ENTITY_TBL"("RESPONSIBLE_ENTITY_ID"),
	CONSTRAINT "aggregation_info_fk4" FOREIGN KEY("CONTACT_INFO_ID")
		REFERENCES "MR_APPL"."CONTACT_INFO_TBL"("CONTACT_INFO_ID")
)	TABLESPACE MREC_TS;
	REVOKE ALL ON AGGREGATION_INFO_TBL FROM PUBLIC;
	GRANT ALL ON AGGREGATION_INFO_TBL TO PUBLIC;

CREATE TABLE "MR_APPL"."NSDL_OAISET_TBL"
(	"OAI_SET_ID"	NUMBER(10) NOT NULL,
	"OAI_SET_DESC"	VARCHAR2(1000),
	"NSDL_AUTHORITY"	VARCHAR2(50),
	"OAI_SET_NAME"	VARCHAR2(1024),
	"OAI_SET_TAG"	VARCHAR2(512),
	"OAI_SET_SPEC"	VARCHAR2(1024),
	CONSTRAINT "nsdl_oaiset_pk" PRIMARY KEY("OAI_SET_ID"),
	CONSTRAINT "nsdl_oaiset_fk" FOREIGN KEY("NSDL_AUTHORITY")
		REFERENCES "MR_APPL"."NSDL_AUTHORITY_TBL"("NSDL_AUTHORITY")
) 
TABLESPACE OAILINK_TS;
REVOKE ALL ON NSDL_OAISET_TBL FROM PUBLIC;
GRANT ALL ON NSDL_OAISET_TBL TO PUBLIC;


CREATE TABLE "MR_APPL"."NSDL_OAILINK_TBL" 
(	"OAI_SET_ID" 	NUMBER(10) NOT NULL, 
	"MREC_ID" 		NUMBER(10) NOT NULL, 
	"LINK_TYPE_ID" 	NUMBER(15) NOT NULL, 
	"LINK_SEQ" 		NUMBER(4) DEFAULT 1 NOT NULL,
    	CONSTRAINT "nsdl_oailink_pk" PRIMARY KEY("OAI_SET_ID", "MREC_ID","LINK_SEQ"),
	CONSTRAINT "nsdl_oailink_fk1" FOREIGN KEY("OAI_SET_ID") 
    		REFERENCES "MR_APPL"."OAI_SET_TBL"("OAI_SET_ID"), 
	CONSTRAINT "nsdl_oailink_fk3" FOREIGN KEY("MREC_ID") 
    		REFERENCES "MR_APPL"."MREC_TBL"("MREC_ID"),
	CONSTRAINT "nsdl_oailink_fK2" FOREIGN KEY("LINK_TYPE_ID")
    		REFERENCES "MR_APPL"."LINK_TYPE_TBL"("LINK_TYPE_ID") 
) 
TABLESPACE OAILINK_TS;
REVOKE ALL ON NSDL_OAILINK_TBL FROM PUBLIC;
GRANT SELECT ON NSDL_OAILINK_TBL TO PUBLIC;

CREATE TABLE XML_PARSE_ELEMENT_TBL
(
  ELEMENT_ID      NUMBER(6)  NOT NULL,
  XML_RECORD_ID   NUMBER(15) NOT NULL,
  ELEMENT_NAME    VARCHAR2(50) NOT NULL,
  ELEMENT_VALUE   VARCHAR2(4000) NOT NULL,
  CONSTRAINT "xml_parse_element_pk" PRIMARY KEY (ELEMENT_ID)
) TABLESPACE MREC_TS;
REVOKE ALL ON XML_PARSE_ELEMENT_TBL FROM PUBLIC;
GRANT SELECT ON XML_PARSE_ELEMENT_TBL TO PUBLIC;

CREATE TABLE XML_PARSE_ATTRIBUTE_TBL
(
  ATTRIBUTE_ID  NUMBER(6) NOT NULL,
  ELEMENT_ID    NUMBER(6) NOT NULL,
  ATTRIBUTE_NAME VARCHAR2(50) NOT NULL,
  ATTRIBUTE_VALUE VARCHAR2(4000),
  CONSTRAINT "xml_parse_attribute_pk" PRIMARY KEY (ATTRIBUTE_ID),
  CONSTRAINT "xml_parse_attribute_fk" FOREIGN KEY (ELEMENT_ID )
    REFERENCES "XML_PARSE_ELEMENT_TBL"(ELEMENT_ID) 
) TABLESPACE MREC_TS;
REVOKE ALL ON XML_PARSE_ATTRIBUTE_TBL FROM PUBLIC;
GRANT SELECT ON XML_PARSE_ATTRIBUTE_TBL TO PUBLIC;

CREATE TABLE XML_ELEMENT_MAP_TBL
(
  ELEMENT_MAP_ID  NUMBER(6) NOT NULL,
  TABLE_NAME  VARCHAR2(40) NOT NULL,
  TABLE_COLUMN_NAME VARCHAR2(50) NOT NULL,
  XML_ENTITY_NAME   VARCHAR2(50) NOT NULL,
  CONSTRAINT "xml_element_map_pk" PRIMARY KEY (ELEMENT_MAP_ID)
) TABLESPACE MREC_TS;
REVOKE ALL ON XML_ELEMENT_MAP_TBL FROM PUBLIC;
GRANT SELECT ON XML_ELEMENT_MAP_TBL TO PUBLIC;