Oracle Data Modeling and Relational Database Design Ed 2.1

• O Course Overview
• Course Objectives O-2
• Agenda: Day 1 O-4
• Agenda: Day 2 O-5
• Agenda: Day 3 O-6
• Agenda: Day 4 O-7
• Oracle SQL Developer Data Modeler O-8
• Appendices in the Course O-10
• Additional Resources O-11

Introduction to Modeling

• Objectives
• Why Model?
• Modeling: A Practical Example
• Database and Application Development Life Cycle
• Process Modeling
• Logical Data Modeling
• Database Design
• Database Generation
• Data Type Model
• Quiz
• Approaches to Modeling
• Top-Down Modeling
• Bottom-Up Modeling
• Targeted Modeling
• Quiz
• Summary
• Practice 1-1 Overview: Identify the Modeling Approach

Documenting the Business Background

• Objectives
• Documenting the Business Direction
• Components of a Business Direction Statement
• Business Objective
• Assumption
• Critical Success Factor
• Key Performance Indicator
• Problem
• Devising Business Direction Objectives and Actions
• Quiz
• Summary
• Practice 2-1 Overview: Identify Types of Business Direction Information

Building a Process Model (Data Flow Diagram)

• Objectives
• What Is a Process Model?
• Benefits of Data Flow Diagrams
• Components of a Data Flow Diagram
• Process
• External Agent
• Information Store
• Information Flow
• Quiz
• Events
• Analyzing Event Responses
• Quiz
• Class Practice: Create a Data Flow Diagram
• Summary
• Practice 3-1 Overview: Create a Data Flow Diagram

Using Oracle SQL Developer Data Modeler to Create Your Data Flow Diagram

• Objectives
• Running Oracle SQL Developer Data Modeler on Linux
• Running Oracle SQL Developer Data Modeler on Windows
• Main Window
• Setting User Preferences
• Setting User Preferences: Data Modeler
• Building a Data Flow Diagram
• Editing the Diagram Layout
• Adding and Reusing Process Events
• Saving Your Model
• Opening a Saved Model
• Summary
• Practice 4-1 Overview: Build a Data Flow Diagram in Oracle SQL Developer
• Data Modeler

Validating Your Data Flow Diagram

• Objectives
• DFD Rules: Process
• DFD Rules: External Agents
• DFD Rules: Information Store
• DFD Rules: Information Flow
• Design Rules in Oracle SQL Developer Data Modeler
• Quiz
• Types of Processes
• Primitive Process
• Composite Process
• Transformation Task Process
• Process Decomposition
• Decomposition Guidelines
• Quiz
• Summary
• Practice 5-1 Overview: Decompose a Process in Your Data Flow Diagram

Identifying Entities and Attributes

• Objectives
• What Is a Logical Data Model?
• Benefits of Creating an Entity Relationship Diagram
• Components of an Entity Relationship Diagram
• Entity
• Entity Types
• Entities and Instances
• Entities Represent Sets
• Quiz
• Attributes
• Attribute Characteristics
• Class Practice: Identify Entities and Attributes
• Summary
• Practice 6-1 Overview: Identify Entities and Attributes
• Practice 6-2 Overview: Identify Entities and Attributes

Identifying Relationships

• Objectives
• Lesson Agenda
• Relationships
• Components of a Relationship
• Relationships: Additional Examples
• Quiz
• Class Practice: Define Business Rules
• Relationship Types
• Many-to-One and One-to-Many Relationships
• Many-to-Many Relationships
• One-to-One Relationships
• Recursive Relationships
• Quiz
• Lesson Agenda
• Using a Relationship Matrix
• Determining a Relationship’s Existence
• Naming the Relationship
• Determining the Relationship’s Cardinality
• Validating the Relationship
• Quiz
• Class Practice: Build a Relationship Matrix
• Summary
• Practice 7-1 Overview: Analyze and Model Relationships
• Practice 7-2 Overview: Analyze and Model Relationships

Assigning Unique Identifiers

• Objectives
• Lesson Agenda
• Unique Identifier
• Unique Identifier: Examples
• Identifying Relationships
• Identifying Relationships with Multiple Entities
• Non-Identifying Relationship
• Lesson Agenda
• Primary and Secondary Unique Identifiers
• Searching for Unique Identifiers
• Quiz
• Class Practice: Specify Unique Identifiers
• Summary
• Practice 8-1 Overview: Identify Unique Identifiers
• Practice 8-2 Overview: Identify Unique Identifiers

Using Oracle SQL Developer Data Modeler to Create an Entity

• Relationship Diagram
• Objectives
• Lesson Agenda
• Building an Entity Relationship Diagram
• Specifying Logical Model General Option
• Specifying Logical Model Diagram Defaults
• Modifying Model Properties
• Notation Types
• Lesson Agenda
• Editing a Diagram Layout: Moving an Object
• Editing a Diagram Layout: Redrawing Lines
• Editing a Diagram Layout: Moving a Relationship Line
• Editing a Diagram Layout: Adding an Elbow
• Editing a Diagram Layout: Showing Levels of Detail
• Editing a Diagram Layout: Resizing Multiple Objects
• Editing a Diagram Layout: Aligning Objects
• Editing a Diagram Layout: Resize Objects to Visible
• Editing a Diagram Layout: Auto Layout Functionality
• Editing a Diagram Layout: Sorting of Attributes in Entities
• Editing a Diagram Layout: Display Comments in RDBMS
• Editing a Diagram Layout: Deprecating Entities
• Editing a Diagram Layout: Deprecating Attributes
• Simple Search Functionality
• Advanced and Global Search Functionality
• Lesson Agenda
• What Is a Subview?
• Creating a Subview
• Lesson Agenda
• What Is a Display?
• Creating a Display
• Opening and Saving a Model
• Exporting a Model
• Importing a Model
• Quiz
• Summary
• Practice 9-1 Overview: Build an ERD in Oracle SQL Developer Data Modeler

Validating Your Entity Relationship Diagram

• Objectives
• Lesson Agenda
• ERD Checklist
• Attribute Rules
• Distinguishing Attributes and Entities
• Attribute Optionality
• Adding Additional Information to the ERD
• Lesson Agenda
• Data Modeler Reports
• Generating Data Modeler Reports as RTF, HTML, or PDF Formats
• Producing Data Modeling Metadata Reports
• Steps to Produce Data Modeler Reports
• Creating a SYSTEM Database Connection in Oracle SQL Developer
• Creating a New User for Reporting in Oracle SQL Developer
• Creating a Connection for the New Reporting User in Oracle SQL Developer
• Exporting Your Model to the Reporting Schema in Oracle SQL Developer
• Data Modeler
• Running Data Modeler Reports in Oracle SQL Developer
• Quiz
• Summary
• Practice 10-1 Overview: Develop and Validate Your ERD

Normalizing Your Data Model

• Objectives
• Lesson Agenda
• What Is Normalization?
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Quiz
• Lesson Agenda
• Normalization Example: Unnormalized Data
• Normalization Example: Transforming to First Normal Form
• Normalization Example: Transforming to Second Normal Form
• Normalization Example: Transforming to Third Normal Form
• Summary
• Practice 11-1 Overview: Normalize an ERD
• Practice 11-2 Overview: Validate an ERD for Normalization

Validating Relationships

• Objectives
• Lesson Agenda
• Resolving M:M Relationships
• Quiz
• Modeling Hierarchical Data
• Examining Recursive Relationships
• Resolving an M:M Recursive Relationship
• Quiz
• Lesson Agenda
• Modeling Exclusive Relationships
• Creating an Exclusive Relationship in Oracle SQL Developer Data Modeler
• Quiz
• Entity Type Hierarchies
• Modeling Subtypes in Oracle SQL Developer Data Modeler
• Representing Entity Type Hierarchies
• Changing Preferences for Box-in-Box Presentation
• Quiz
• Model Data over Time
• Summary
• Practice 12-1 Overview: Resolve M:M Relationships
• Practice 12-2 Overview: Model Hierarchical Data
• Practice 12-3 Overview: Model Hierarchical Data and Recursive
• Relationships
• Practice 12-4 Overview: Examine Exclusive Relationships
• Practice 12-5 Overview: Examine Exclusive Relationships

Adding and Using Data Types

• Objectives
• Lesson Agenda
• Attribute Data Types
• Logical Type
• Types Administration
• Domain
• Adding a Check Constraint to a Domain
• Adding a Range or Value List to a Domain
• Preferred Logical Types and Domains
• Creating Domains from Logical Types
• Lesson Agenda
• Data Type Model
• Distinct Type
• Structured Type
• Using Distinct Types in a Structured Type
• Collection Type
• Building a Data Type Model
• Assigning Data Types to an Attribute
• Quiz
• Summary
• Practice 13-1 Overview: Create and Assign Data Types

Putting It All Together

• Objectives
• Practice 14-1 Overview: Develop and Validate Your ERD
• Practice 14-2 Overview: Develop and Validate Your ERD (Optional)
• Summary

Mapping Your Entity Relationship Diagram to a Relational Database Design

• Objectives
• Lesson Agenda
• Benefits of Creating a Relational Model
• Review: Database Design
• Relational Database: Overview
• Terminology Mapping
• Naming Conventions
• Naming Restrictions with Oracle Database
• Ensuring That Your Logical Data Model Is Complete
• Lesson Agenda
• Mapping Simple Entities
• Naming Entities
• Engineering Entities
• Mapping Attributes to Columns
• Mapping Attributes to Columns: Column Names
• Engineering Attributes
• Reviewing the Glossary
• Adding the Glossary as the Naming Standard
• Mapping Attributes to Columns with the Glossary
• Applying Name Abbreviations
• Mapping Unique Identifiers to Primary Keys
• Engineering Unique Identifiers
• Mapping Relationships to Foreign Keys
• Defining Naming Templates
• Applying Templates to One Table
• Applying Templates to the Relational Model
• Managing Prefixes
• Quiz
• Practice 15-1 Overview: Create an Initial Relational Model
• Lesson Agenda
• Mapping Exclusive Relationships to Foreign Keys
• Engineering Exclusive Relationships
• Mapping Subtypes to Tables
• Applying General Options
• Setting Compare/Copy Options
• Viewing the Mapping Comparison
• Synchronizing Deleted Objects
• Identifying Overlapping and Folding Keys
• Summary
• Practice 15-2 Overview: Forward-Engineer a Model

Analyzing Your Relational Model

• Objectives
• Lesson Agenda
• Relational Model and Relational Model Diagram Preferences
• Reviewing Table Properties
• Previewing the DDL for a Table
• Assigning a Classification Type to One Table
• Changing the Color for Classified Tables
• Assigning Classification Types to Multiple Tables
• Reviewing Column Properties
• Column Auto Increment
• Discovering Foreign Keys
• Defining a Unique Constraint
• Defining Indexes
• Defining a Table-Level Constraint
• Table To View Wizard
• View To Table Wizard
• Specifying Valid Time Dimensions
• Specifying Volume Properties
• Lesson Agenda
• Defining Spatial Properties
• Defining Column Groups
• Creating Views
• Using table_template
• Masking Data
• Redaction Policy
• Creating Masking Template
• TSDP Policy
• Creating Sensitive Type
• Creating TSDP Policy
• Deprecating Tables and Columns
• Clear Source Stamp
• Quiz
• Summary
• Practice 16-1 Overview: Analyze Your Relational Model

Defining Your Physical Model

• Objectives
• What Is a Physical Model?
• Creating a Physical Model
• RDBMS Administration
• RDBMS Administration: Changing the Default RDBMS Sites
• Creating Physical Model Objects
• Adding a User
• Adding Storage Templates
• Associating Physical Objects with Your Table
• Propagating Properties to Other Physical Objects
• Partitioning a Table
• Creating a Materialized View
• Cloning a Database
• Quiz
• Summary
• Practice 17-1 Overview: Create a Physical Model

Generating Your Database

• Objectives
• Lesson Agenda
• Database Generation
• Generating DDL: Selecting a Database
• Generating DDL: ‘Create’ Selection
• Generating DDL: DDL Script
• Generating DDL: Assigned to Users
• Generating DDL: ‘Drop’ Selection
• Generating DDL: Name Substitution
• Generating DDL: Including Table Scripts
• Generating DDL: Including SQL*Plus Prompt Command in Generated DDL
• Generating DDL: Masking Oracle Errors
• Generating DDL: Using Find
• Code Folding in DDL File Editor
• DDL Preferences
• IDENTITY Column
• Lesson Agenda
• Design Rules
• Working with Rule Sets
• Working with Custom Rules
• Working with Libraries
• Working with Transformations
• Summary
• Practice 18-1 Overview: Generate DDL

Altering an Existing Design

• Objectives
• Lesson Agenda
• Approaches to Modeling
• Using Import to Create a Model
• Importing an Existing Database
• Importing Domains
• Quiz
• Lesson Agenda
• Creating a Logical Data Model from Your Relational Model
• Reviewing and Making Changes to Your Logical Model
• Checking the Design Rules
• Forward Engineering to a New Relational Model
• Comparing Your Relational Model Changes with What Is in the Database
• Compare Mapping
• Previewing the DDL
• Comparing and Merging Two Models
• Exporting Your Model
• Exporting to a Data Modeling Design
• Exporting and Importing Preferences, Connections, and Recent Designs
• Quiz
• Lesson Agenda
• Synchronizing the Data Dictionary with Changes in a Model
• Synchronizing the Data Dictionary with Changes in a Model: Example
• Overview
• Modifying the SALARY Column and Synchronizing Data Dictionary with
• the Model
• Reviewing the Change and Setting Some Additional Options
• Reviewing the Generated DDL Script
• Backup Strategy Options
• Running the Generated DDL Script in Oracle SQL Developer and
• Confirming the Change
• Summary
• Practice 19-1 Overview: Re-Engineer the HR Schema

Working in a Collaborative Environment

• Objectives
• Lesson Agenda
• Collaborative Environment: Example
• What Is Subversion?
• Benefits of Working in a Collaborative Environment
• Creating a Subversion (SVN) Connection
• Creating a Remote Directory
• Adding a Design Model to the Repository
• Making Changes to the Versioned Model
• Reviewing Pending Changes
• Synchronizing Changes
• Resolving Conflicts Between Users
• Reverting Changes
• Reviewing and Comparing Versions in the Repository
• Quiz
• Lesson Agenda
• Branching
• Creating and Working with a Branch
• Creating a Remote Connection and Directory in a Subversion Repository
• Saving a Model in the Subversion Repository
• Creating a Branch Based on the Model
• Committing Changes to the Repository (Trunk)
• Merging Changes into the Branch
• Summary
• Practice 20 Overview: Working in a Collaborative Environment
• A Using SQL Developer
• Objectives A-2
• What Is Oracle SQL Developer? A-3
• Specifications of SQL Developer A-4
• SQL Developer 4.0 Interface A-5
• Creating a Database Connection A-7
• Browsing Database Objects A-10
• Displaying the Table Structure A-11
• Browsing Files A-12
• Finding Database Objects A-13
• Creating a Schema Object A-15
• Creating a New Table: Example A-16
• Using the SQL Worksheet A-17
• Executing SQL Statements A-21
• Saving SQL Scripts A-22
• Executing Saved Script Files: Method 1 A-23
• Executing Saved Script Files: Method 2 A-24
• Formatting the SQL Code A-25
• Using Snippets A-26
• Using Snippets: Example A-27
• Using Recycle Bin A-28
• Debugging Procedures and Functions A-29
• Database Reporting A-30
• Creating a User-Defined Report A-31
• Search Engines and External Tools A-32
• Setting Preferences A-33
• Resetting the SQL Developer Layout A-34
• Data Modeler in SQL Developer A-35
• Summary A-36
• B Creating a Multidimensional Model
• Objectives B-2
• What Is a Multidimensional Model? B-3
• Measures B-4
• Measure Types B-5
• Dimensions B-6
• Sharing Dimensions B-7
• Hierarchy B-8
• Hierarchy: Example B-10
• Level B-11
• Types of Hierarchies B-12
• Attributes B-13
• Dimensional Model Summarized B-14
• Quiz B-15
• Steps to Build a Multidimensional Model in Oracle SQL Developer
• Data Modeler B-17
• Importing a Database with Dimensions B-18
• Reverse Engineering Your Model B-21
• Creating Your Multidimensional Model B-23
• Reviewing Your Multidimensional Model B-24
• Reviewing Multidimensional Object Properties B-25
• Modifying Properties for the Time Dimension B-26
• Reviewing Properties of Multidimensional Object Components B-27
• Reviewing Detailed Properties of Object Components B-28
• Creating New Multidimensional Objects B-29
• Impact Analysis B-30
• Creating an Oracle AW B-31
• Exporting the Multidimensional Model B-32
• Summary B-33
• C Denormalizing Your Design to Increase Performance
• Objectives C-2
• What Is Denormalization? C-3
• Storing Derivable Values C-4
• Pre-Joining Tables C-5
• Hard-Coded Values C-6
• Keeping Details with the Master Table C-8
• Repeating Current Detail with the Master Table C-9
• END_DATE Columns C-10
• CURRENT_INDICATOR Column C-11
• Hierarchy Level Indicator C-12
• Short Circuit Keys C-13
• Quiz C-14
• Summary C-16
• Appendix C Overview: Denormalize Your Relational Model C-17