Evolution of BI

Many people talk about BI and data warehouse things.
Let me give you a little information about the evolution of Business Intelligence (BI)

• Executive Information System (EIS)
• Decision Support System (DSS)
• Data Warehousing (DW) and Business Intelligence (BI)

Create table with primary and foreign key constraint

Referencing one Column in other table
Template:
CREATE TABLE schema.table_name
(
col1 dataType,
col2 dataType,
col3 dataType,
col.. dataType,
CONSTRAINT Constraint_name PRIMARY KEY(column_Name),
CONSTRAINT Constraint_name FOREIGN KEY (Column_Name) REFERENCES table_name(column_name_in_the_reference_table)
);


Sample:
1. Create the master table
CREATE TABLE myschema.kategori
(
kategoriid NUMBER(5),
katoriname VARCHAR2(30),
description VARCHAR2(50),
CONSTRAINT pkkategori PRIMARY KEY (kategoriid)
);

2. Create the child table
CREATE TABLE myschema.judulvideo
(
videotittleid NUMBER(5),
videotittlename VARCHAR2(50),
kategoriid NUMBER(5),
description VARCHAR2(50),
CONSTRAINT pkjudulvideo PRIMARY KEY(videotittleid),
CONSTRAINT fkjudulvideo FOREIGN KEY (kategoriid) REFERENCES kategori(kategoriid)
);

• Referencing two or more columns in other table (if the primary key has 2 or more columns)
  

CREATE TABLE schema.table_name
(
col1 dataType,
col2 dataType,
col3 dataType,
col.. dataType,
CONSTRAINT Constraint_name PRIMARY KEY(column_Name),
CONSTRAINT Constraint_name FOREIGN KEY (Column_Name1,Column_Name2 ) REFERENCES table_name(Column_Name1,Column_Name2 )
);


CREATE TABLE myschema.sewaheader
(
sewaid NUMBER(5),
memberid NUMBER(5),
videoid NUMBER(5),
videotittleid NUMBER(5),
tanggalsewa DATE DEFAULT SYSDATE,
tanggalkembali DATE,
jlhvideo NUMBER(3),
totalharga NUMBER,
jlhbayar NUMBER,
CONSTRAINT pksewaheader PRIMARY KEY(sewaid),
CONSTRAINT fksewaheader1 FOREIGN KEY(memberid) REFERENCES members(memberid),
CONSTRAINT fksewaheader2 FOREIGN KEY(videoid,videotittleid) REFERENCES video(videoid,videotittleid)
);

Create table with primary key constraint

Template:
CREATE TABLE myschema.table_name
(
col1 dataType,
col2 dataType,
col... dataType ,
CONSTRAINT Constraint_name PRIMARY KEY(column_Name)
);


Example:
CREATE TABLE myschema.judulvideo
(
videotittleid NUMBER(5),
videotittlename VARCHAR2(50),
kategoriid NUMBER(5),
description VARCHAR2(50),
CONSTRAINT pkjudulvideo PRIMARY KEY(videotittleid)
);


Or the Other way, you can try this
First, create the table
CREATE TABLE myschema.table_name
(
col1 dataType,
col2 dataType,
col... dataType ,
);

CREATE TABLE kategori
(
kategoriid NUMBER(5),
katoriname VARCHAR2(30),
description VARCHAR2(50)
)
TABLESPACE etiket_tbl;

Then Alter the table to add the primary key constraint
ALTER TABLE myschema.table_name
ADD CONSTRAINT constraint_name PRIMARY KEY(column_name);

ALTER TABLE myschema.kategori
ADD CONSTRAINT pkkategori
PRIMARY KEY (kategoriid)
USING INDEX
TABLESPACE etiket_idx;

Create Table

• Create table without define the primary key at the beginning

CREATE TABLE myschema.kategori
(
kategoriid NUMBER(5),
katoriname VARCHAR2(30),
description VARCHAR2(50)
)
TABLESPACE etiket_tbl;

• Adding the primary constraint into table

ALTER TABLE myschema.kategori
ADD CONSTRAINT pkkategori
PRIMARY KEY (kategoriid)
USING INDEX
TABLESPACE etiket_idx;

Create Table Template

Simple (without partition)
CREATE TABLE schema.table_name
( col1 dataType,
col2 dataType,
..... );

With partition
CREATE TABLE schema.table_name
( col1 dataType,
col2 dataType,
..... );
PARTITION BY RANGE( col1, col2 )
( PARTITION p1 VALUES LESS THAN (...,... ) TABLESPACE p1,
PARTITION p2 VALUES LESS THAN (...,... ) TABLESPACE p2);




DBA Responsibilities

People who manage and administering the database called database administrator (DBA).
The responsibilities of DBA can include the following tasks:

• Installing and upgrading the oracle database server and application tools
• Allocating system storage and planning future storage requirements for the database system
• Creating primary database storage structures (tablespaces) after application developers have designed an application
• Creating primary objects (tables, views, indexes) once application developers have designed an application
• Modifying the database structure, as necessary, from information given by application developers
• Enrolling users and maintaining system security
• Ensuring compliance with Oracle license agreements
• Controlling and monitoring user access to the database
• Monitoring and optimizing the performance of the database
• Planning for backup and recovery of database information
• Maintaining archived data on tape
• Backing up and restoring the database
• Contacting Oracle for technical support