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. AUDIENCE:   New and intermediate systems analysts, architects, and systems engineers. PREREQUISITES:   SQL background would be helpful. DURATION:   5 days. Can be shortened by deleting topics. APPROACH:   Instructor led, projects, and hands-on. OBJECTIVES:   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 COURSE CONTENT:   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. JAP/09