Oracle OLTP and OLAP Database Design

Course:  ORDBD
Duration:  5 Days [note]
Level:  I
Course Summary

This course is designed to help the student understand the principles of database design. During the course the student will be exposed to both transactional and analytic database design principles. During the first third of the course student will learn how to identify business processes and process data needs using data flow diagrams. Data flow diagrams will help the student understand a system's data needs and how to identify the screens and reports. Proper analysis of a system's processes and data result in well designed systems.

The student will also see how to translate the system's data needs into a relational model using the entity relational diagram and how to transform the entity relation model into a relational model. The student will also learn how to normalize the database ensuring a well-designed transactional database that eliminates redundancy, minimizes space, and insert/update/delete anomalies. The student will also be exposed to database software features that ensure database referential integrity.

The second third of the course covers analytic database design. This type of design focuses on information needs. The student will learn the principles of dimensional modeling and will understand the important differences between analytic and transactional database design. This course portion covers star schemas, cubes, fact and dimension tables, principles of data warehousing, and the Common Information Bus used to insulate data warehouses from transactional systems. Proper use of these concepts will dramatically increase user satisfaction and reduce report development time resulting in corporate savings. The students will actively design an actual dimensional model that can be used in their workplace as part of the course.

The final third of the course consists of implementing the database objects. The student will learn how to estimate database size, create schemas, tablespaces, tables, and indexes. The student will also see how to use database constraints to ensure referential integrity and assign privileges using the database control language. We will also cover tools such as materialized views, the merge statement, and the Insert All command that can be used stage and populate the dimensional database.

The course has several small projects where the student must identify requirements, create DFD's, ERD's, and implement the database. By the completion of this course, the student will have a great idea on the tasks need to design and implement an Oracle database.

« Hide The Details
Topics Covered In This Course

OLTP Design

  • Process modeling and data flow diagrams
  • Conceptual data modeling
  • Designing the physical database
  • Understanding Oracle database objects that store data
  • Understanding Oracle table constraints
  • Understanding schemas and synonyms

OLAP Design

  • Goals and requirements of a data warehouse
  • Data warehouse components
  • Dimensional modeling terminology
  • Dimensional modeling pitfalls
  • Dimensional model case studies

Implementing the design within Oracle

  • Create the tablespace for the Transformer Tracking system
  • Create the schema for the Transformer Tracking system
  • Assign privileges
  • Create the Transformer Tracking system tables and constraints
  • Discuss various Oracle indexes
  • Place indexes on the Transformer Tracking tables
  • Discuss ETL tools: materialized views, merge statement, insert all statement, SQL Loader, and PL/SQL bulk bind loads.
What You Can Expect

At the end of this course, delegates will be able to:

  • Document system requirements
  • Create a data flow document
  • Identify applications
  • Create a logical data model or ERD (OLTP)
  • Create a physical data model (OLTP)
  • Identify database constraints
  • Identify dimensions
  • Identify fact tables
  • Create a physical data model (OLAP)
  • Create the Oracle schemas
  • Create the tablespaces
  • Create the database tables and various constraints
  • Create the database indexes
  • Create simple extraction transformation and loading (ETL) scripts
Who Should Take This Course

New and intermediate systems analysts, architects, and systems engineers.

Recommended Prerequisites

SQL background would be helpful.

Training Style

Instructor led, projects, and hands-on.

« Hide The Details
Related Courses
Code Course Title Duration Level
Oracle BI Enterprise Edition (OBIEE) 11g: Reports and Dashboards
4 Days
Oracle Database 11g: Administer a Data Warehouse
5 Days
Oracle BI Enterprise Edition (OBIEE) 11g: Building Repositories
5 Days

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]