Difference between Data ware house modelling vs. Operational database modelling.

18views

written 7.9 years ago by

teamques10 ★ 68k

Many case tools are available for data modelling.

EAST tools are used for creating the logical and the physical schema for particular database management systems (DBMS).

OLTP (OPERATIONAL DATABASE MODELLING)	OLAP (DATA WAREHOUSE MOELLING)
OLTP systems focus is on individual database transaction.	Data warehouse focus is on how executive users like managers view business.
An OLTP system maintains micro-level transactions.	Data warehouse shows business trends and operations.
Data at detail level necessary to run the business.	Information concentrates on business processes.
Suitable only for transaction level.	Business measures the process, can be calculated.
Data consistency, non-redundancy and efficient storage of data.	To study measures of many business dimensions.
It contains current data.	It contains current as well as historic data.
It has more no. of users.	It has less no. of users.
Access frequency of data is high.	Access frequency of data is low.
It is called as OLTP assuming that operation system.	It is called OLAP assuming that information system.
ER model is used.	Dimensional modelling is used.
It provides transaction throughput.	It provides query throughput.
Allows read/write operations.	Only reads and rarely writes.
Provides high performance.	Provides high flexibility.

Product :-

Create table product (prod_id number(2) primary key, prod_name varchar2(20), prod_desc varchar2(20), prod_value number(3)) ;

Time:-

Create table time (Time_id number(2) primary key, Day number(2), Quarter number(1); Year number(4));

Location:-

Create table Location (Loc_id number(2) primary key, pin_code number(3), city varchar2(20), state varchar2(20));

Sales:-

Create table sales (sales man_id number(2) references department (salesman_id),prod_id number (2) references product(prod_id), time number (2) references time(time_id), loc_id number(2) references.

Location (loc_id), quantity number(3), unit_prize number(7,2)

Advantages of star schema.

Simple data warehouse schema.
Easy to navigate between the tables due to less number of joins.
It is most suitable for query processing.

Disadvantages

It occupies more memory-space.
Tables are not in normalized form.

ADD COMMENT EDIT