Definition: Database Elements

This is a work in progress to capture some of the terms often used in Data Warehousing and where possible Analogies and Metaphors to make it more practicle.

OLTP – Online Transaction Processing
OLAP – Online Analytical Processing
LDAP – Lightweight Directory Access Protocol
ODBC – Open Database Connectivity
SQL – Structured Query Language

•    First Normal Form (1NF)
•    Second Normal Form (2NF)
•    Third Normal Form (3NF)
•    Fourth Normal Form (4NF)
•    Fifth Normal Form (5NF)
•    Semantic Data Model
•    Logical Data Model
•    Physical Data Model

Database Tables – Databases contain a construct called a which holds columns that are associated with a common primary key — Customer ID and all the data fields related to a Customer would be held in a table called customer.

RDBMS Relational Database Management System — the engine that manages the data and conforms to relational database rules — I believe there are 12 identified by a fellow named Codd.

RDBMS Engines – contain highly complex optimizers, incredible business transformations on the SQL level including (but not limited to) object level transformation, in-database data mining engines, in-database data quality/cleansing/profiling plug-ins, statistical algorithms, hierarchical and drill-down

Views – views are logical contructs that are layered on top of tables .  Say a customer table has five columns Firstname, Lastname, Gender, DateofBirth, MaritalStatus  A view can be created that for all intents and purposes looks like a table but uses only threeof the tables columns  — CREATE VIEW V_Customer_Gender as  SELECT Firstname, Lastname, Gender from T_Customer

Aggregates – math function applied to a set of data that derives a rolled up result — SUM of all Sales, Average Purchase Price, Mean Cost of Goods

Cubes – a multidimensional array of facts refferences by a number of dimensions.  Dimensions might be (Date, Place, Customer, Product)  While Facts are Cost of Goods, Price of Purchase, Tax, …

Data Warehouse

Data Warehouse (Analogy – Big Box Store – Home Depot / Wal-Mart)

Datamart (Analogy – your corner store – 7-eleven/Beckers) –  A
database, or collection of databases, designed to help managers make
strategic decisions about their business. Whereas a data warehouse
combines databases across an entire enterprise, data marts are usually
smaller and focus on a particular subject or department. Some data
marts, called dependent data marts, are subsets of larger data

Data Stores – generic definition for anything that holds data –
databases, data marts, data warehouses, disk files, tapes, memory
sticks, SD, etc.

Operational Data Store  – (aka Operational Data Warehouse, ODW)  An
operational data store is a replication of the data from source systems
that is used to support Operational demand — traditionally the DW has
been percieved as a historical read only record of the businesses past
information and the operational data store is a current view of that
informations — smaller volume for fast access – possibly providing
some added value or transformations that are not provided by other
operational systems.  the concept of an ODS is vague in that some data
marts have evolved into Operational data stores by becoming the
upstream systems – suppliers of data — to supsequent operational
systems — in some cases these ODS become mission critical and yet do
not have the backup and DRP required of mission critical applications.

Enterprise Data Warehouse (EDW) – focuses on delivering strategic
decision support. Having a single source of truth for understanding key
performance indicators with sophisticated what-if analysis for
developing business strategy. Data mining techniques further refine
business strategy via advanced customer segmentation, acquisition and
retention models, product mix optimization, pricing models, and many
other similar applications. The traditional data warehouse is typically
used by decision-makers in areas such as marketing, finance, and
strategic planning.  What makes an Enterprise Data Warehouse (these
attributes apply to overall design philosophy as well as to underlying

  1. Single version of the truth
  2. Multiple subject areas (Finance, Sales, Marketing, and so on) representing areas of interest both for groups and individuals
  3. Normalized design
  4. Mission-critical environment
  5. Scalability across several dimensions

Analogy – SuperCentre with a collection of big box stores

Working Data Stores

Federated Data Warehouse –

Active Data Warehouse – ("real-time data warehousing and analytics")
provides an integrated information repository to drive strategic and
tactical decision support within and organization.  By extending the
functionality of the traditional data warehouse into the realm of
tactical decision making. Tactical decision support leverages the
integrated source of data in the warehouse by deploying timely,
cleansed information to decision-makers throughout an organization. Add
the pieces from the Teradata User Group.

VLDB – Very Large Database

VLDW – Very Large Data Warehouse

Paradigm Shift in EDW – paradigm is shifting, the need to move data
from "all sources" into an integrated business model that houses both
current and historical views of consistent data is being sharply
focused by business acumen to a single statement of fact.

Leave a Reply

captcha *