Open Framework, Information Management Strategy & Collaborative Governance | Data & Social Methodology - MIKE2.0 Methodology
Wiki Home
Collapse Expand Close

Members
Collapse Expand Close

To join, please contact us.

Improve MIKE 2.0
Collapse Expand Close
Need somewhere to start? How about the most wanted pages; or the pages we know need more work; or even the stub that somebody else has started, but hasn't been able to finish. Or create a ticket for any issues you have found.

Implemented Database Deliverable Template

From MIKE2.0 Methodology

Share/Save/Bookmark
Jump to: navigation, search
Under construction.png
This article is currently Under Construction. It is undergoing major changes as it is in the early stages of development. Users should help contribute to this article to get it to the point where is ready for a Peer Review.
This deliverable template is used to describe a sample of the MIKE2.0 Methodology (typically at a task level). More templates are now being added to MIKE2.0 as this has been a frequently requested aspect of the methodology. Contributors are strongly encouraged to assist in this effort.
Deliverable templates are illustrative as opposed to fully representative. Please help add examples to this template that are representative of the proposed output.

The database is the heart of the information management environment and as such will need to be implemented as soon as possible for the surrounding processes to work from. The Implemented Database task delivers the implemented, working database.

The following sub-tasks are necessary to implement the database:

  • Allocate physical space
  • Load system tables
  • Create physical tables
  • Create metadata tables
  • Database security enabled

Examples

Example 1 - Sample schema for a web statistics data warehouse

Schema

CREATE SEQUENCE Batch_Num_Seq

INCREMENT BY 1

START WITH 1

;

DROP TABLE Rejected CASCADE CONSTRAINTS;

CREATE TABLE Rejected (

Table_Name VARCHAR2(30) NOT NULL,

Rejected_Record VARCHAR2(4000) NOT NULL,

Error_Code VARCHAR2(20) NULL,

Error_Text VARCHAR2(255) NULL,

Source_System VARCHAR2(100) NOT NULL,

Date_Created DATE NOT NULL,

Batch_Number INTEGER NOT NULL

)

  TABLESPACE SMALL_DATA

DROP TABLE B_Log_File CASCADE CONSTRAINTS;

CREATE TABLE B_Log_File (

Batch_Number INTEGER NOT NULL,

File_Dir VARCHAR2(255) NOT NULL,

Input_File VARCHAR2(255) NOT NULL,

SearchLog_Ind SMALLINT NOT NULL,

NavLog_Ind SMALLINT NOT NULL,

Date_Created DATE NOT NULL

)

  TABLESPACE SMALL_DATA

ALTER TABLE B_Log_File

ADD ( PRIMARY KEY (Batch_Number, File_Dir, Input_File)

USING INDEX

  TABLESPACE SMALL_INDEX ) ;

DROP TABLE Cust_address CASCADE CONSTRAINTS;

CREATE TABLE Cust_address (

Cust_address_Key INTEGER NOT NULL,

Address_Id INTEGER NOT NULL,

Customer_Id INTEGER NOT NULL,

Locality_Code VARCHAR2(100) NULL,

State_Code VARCHAR2(100) NULL,

Post_Code VARCHAR2(100) NULL,

Effective_Start_Date DATE NOT NULL,

Effective_End_Date DATE NOT NULL,

Current_Validity_Indicator CHAR(1) NOT NULL,

Version_Number INTEGER NOT NULL,

Latest_Version_Indicator CHAR(1) NOT NULL,

Record_Start_Date DATE NOT NULL,

Record_End_Date DATE NOT NULL,

Date_Created DATE NOT NULL,

Data_Quality_Indicator CHAR(1) NOT NULL,

Batch_Number INTEGER NOT NULL,

SCD_CRC32 INTEGER NOT NULL

)

  TABLESPACE SMALL_DATA

CREATE INDEX IDX_Address_Id ON Cust_address

(

Address_Id ASC

)

  TABLESPACE SMALL_INDEX

;

ALTER TABLE Cust_address

ADD ( PRIMARY KEY (Cust_address_Key)

USING INDEX

  TABLESPACE SMALL_INDEX ) ;

CREATE INDEX IDX_Hdng_Code ON Heading_Market

(

Heading_Code ASC

)

  TABLESPACE SMALL_INDEX

;

DROP TABLE Result_Location CASCADE CONSTRAINTS;

CREATE TABLE Result_Location (

Result_Location_Key INTEGER NOT NULL,

Search_Result_Key INTEGER NOT NULL,

Country_Key INTEGER NULL,

State_Key INTEGER NULL,

Book_Area_Key INTEGER NULL,

Post_Code_Key INTEGER NULL,

Locality_Key INTEGER NULL,

Date_Created DATE NOT NULL,

Data_Quality_Indicator CHAR(1) NOT NULL,

Batch_Number INTEGER NOT NULL

)

  TABLESPACE LARGE_DATA

DROP TABLE Search_Result CASCADE CONSTRAINTS;

CREATE TABLE Search_Result (

Search_Result_Key INTEGER NOT NULL,

Search_Event_Key INTEGER NOT NULL,

Search_Result_Type VARCHAR2(20) NOT NULL,

Customer_Solution_Key INTEGER NULL,

Appearance_Type_Key INTEGER NULL,

Classification_Key INTEGER NULL,

Date_Created DATE NOT NULL,

Data_Quality_Indicator CHAR(1) NOT NULL,

Batch_Number INTEGER NOT NULL

)

  TABLESPACE LARGE_DATA

CREATE TABLE Classification_Relship (

Classification_Rel_Key INTEGER NOT NULL,

Classification_Rel_Type VARCHAR2(20) NOT NULL,

Child_Classification_Key INTEGER NOT NULL,

Parent_Classification_Key INTEGER NOT NULL,

Parent_Classification_Code VARCHAR2(20) NOT NULL,

Child_Classification_Code VARCHAR2(20) NOT NULL,

Effective_Start_Date DATE NOT NULL,

Effective_End_Date DATE NOT NULL,

Current_Validity_Indicator CHAR(1) NOT NULL,

Version_Number INTEGER NOT NULL,

Latest_Version_Indicator CHAR(1) NOT NULL,

Record_Start_Date DATE NOT NULL,

Record_End_Date DATE NOT NULL,

Date_Created DATE NOT NULL,

Data_Quality_Indicator CHAR(1) NOT NULL,

Batch_Number INTEGER NOT NULL,

SCD_CRC32 INTEGER NOT NULL

)

  TABLESPACE SMALL_DATA

DROP TABLE State_Locality_Relship CASCADE CONSTRAINTS;

CREATE TABLE State_Locality_Relship (

State_Locality_Rel_Key INTEGER NOT NULL,

State_Key INTEGER NOT NULL,

Locality_Key INTEGER NOT NULL,

State_Short_Name VARCHAR2(100) NULL,

Locality_Id INTEGER NOT NULL,

Effective_Start_Date DATE NOT NULL,

Effective_End_Date DATE NOT NULL,

Current_Validity_Indicator CHAR(1) NOT NULL,

Version_Number INTEGER NOT NULL,

Latest_Version_Indicator CHAR(1) NOT NULL,

Record_Start_Date DATE NOT NULL,

Record_End_Date DATE NOT NULL,

Date_Created DATE NOT NULL,

Data_Quality_Indicator CHAR(1) NULL,

Batch_Number INTEGER NOT NULL,

SCD_CRC32 INTEGER NOT NULL

)

  TABLESPACE SMALL_DATA

CREATE TABLE Search_Type (

Search_Type_Key INTEGER NOT NULL,

Search_Type VARCHAR2(100) NULL,

Search_Indicator CHAR(1) NULL,

Browse_Indicator CHAR(1) NULL,

Effective_Start_Date DATE NOT NULL,

Effective_End_Date DATE NOT NULL,

Current_Validity_Indicator CHAR(1) NOT NULL,

Version_Number INTEGER NOT NULL,

Latest_Version_Indicator CHAR(1) NOT NULL,

Record_Start_Date DATE NOT NULL,

Record_End_Date DATE NOT NULL,

Date_Created DATE NOT NULL,

Data_Quality_Indicator CHAR(1) NOT NULL,

Batch_Number INTEGER NOT NULL

)

  TABLESPACE SMALL_DATA

ALTER TABLE Search_Type

ADD ( PRIMARY KEY (Search_Type_Key)

USING INDEX

  TABLESPACE SMALL_INDEX ) ;

DROP TABLE Batch_Audit_Link CASCADE CONSTRAINTS;

CREATE TABLE Batch_Audit_Link (

Link_Key INTEGER NOT NULL,

Stage_Key INTEGER NOT NULL,

Batch_Number INTEGER NOT NULL,

Job_Name VARCHAR2(50) NOT NULL,

Stage_Name VARCHAR2(50) NOT NULL,

Link_Name VARCHAR2(50) NOT NULL,

Record_Count INTEGER NOT NULL

)

  TABLESPACE SMALL_DATA

CREATE INDEX XIF1Batch_Audit_Link ON Batch_Audit_Link

(

Stage_Key ASC

)

  TABLESPACE SMALL_INDEX

;

ALTER TABLE Batch_Audit_Link

ADD ( PRIMARY KEY (Link_Key)

USING INDEX

  TABLESPACE SMALL_INDEX ) ;

DROP TABLE Batch_Audit_Stage CASCADE CONSTRAINTS;

CREATE TABLE Batch_Audit_Stage (

Stage_Key INTEGER NOT NULL,

Job_Key INTEGER NOT NULL,

Batch_Number INTEGER NOT NULL,

Job_Name VARCHAR2(50) NOT NULL,

Stage_Name VARCHAR2(50) NOT NULL,

Stage_Type VARCHAR2(20) NOT NULL,

Start_Time DATE NOT NULL,

End_Time DATE NOT NULL,

Stage_Status VARCHAR2(20) NOT NULL

)

  TABLESPACE SMALL_DATA

CREATE INDEX XIF1Batch_Audit_Stage ON Batch_Audit_Stage

(

Job_Key ASC

)

  TABLESPACE SMALL_INDEX

;

ALTER TABLE Batch_Audit_Stage

ADD ( PRIMARY KEY (Stage_Key)

USING INDEX

  TABLESPACE SMALL_INDEX ) ;

DROP TABLE Batch_Audit_Job CASCADE CONSTRAINTS;

CREATE TABLE Batch_Audit_Job (

Job_Key INTEGER NOT NULL,

Batch_Number INTEGER NOT NULL,

Job_Name VARCHAR2(50) NOT NULL,

Business_Date DATE NULL,

Start_Time DATE NOT NULL,

End_Time DATE NOT NULL

)

  TABLESPACE SMALL_DATA

ALTER TABLE Batch_Audit_Job

ADD ( PRIMARY KEY (Job_Key)

USING INDEX

  TABLESPACE SMALL_INDEX ) ;
Wiki Contributors
Collapse Expand Close