Oracle OLTP and OLAP Database Design

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.