Data Modeling and Relational Database Design

Description: This course enables technical and business users of any relational database to create graphical data models that capture business requirements and rules, and then transform those models into logical and physical database designs. The graphical models promote understanding and ensure compliance with user specifications; the logical and physical diagrams facilitate designs that optimize performance.The workshop style of this class employs real-world examples and emphasizes flexible solutions that accommodate a variety of business problems and diverse system architectures.

Duration: 4 Days

Learn to:

• Construct graphical data models that capture information requirements and business rules
• Identify patterns in data and data usage that will impact your production environment
• Recognize which design options will bring the greatest speed to your applications
• Create logical and physical database designs that take advantage of your system’s capabilities
• Build maintainable databases that perform predictably

Audience:

• Data Architects
• Database Administrators
• Application Developers
• Business Project Managers
• Business and Systems Analysts

Suggested Prerequisite:

• Basic understanding of relational database concepts

Topics:

Graphical Data Modeling

• How graphical models lead to better database design
• Identifying relevant data and relationships between data
• Techniques for including business rules and constraints in the model
• Choosing Subtypes and Supertypes for understanding and performance
• Identifying the potential for redundant data and relationships
• Capturing data changes over time and journaling
• Master-Detail, Bill-Of-Material and advanced modeling

Database Design

• Mapping the graphical model to logical and physical database design
• Implementing exclusive relationships
• Storage and performance considerations in subtyping and supertyping
• Data redundancy: How and when to Normalize and Denormalize your design
• Ensuring referential integrity
• Choosing Primary Keys, Foreign Keys, Indexes, Views and other database objects
• Table partitioning for accessibility
• Distributed environments and advanced database design considerations

Building a Process Model (Data Flow Diagram)

• What Is a Process Model?
• Why Create a DFD?
• Components of a Data Flow Diagram
• Events
• Analyzing Event Responses

Using Oracle SQL Developer Data Modeler to Create Your Process Model (Data Flow Diagram)

• Downloading and Installing Oracle SQL Developer Data Modeler
• Oracle SQL Developer Data Modeler Main Window Components
• Building a Data Flow Diagram
• Editing the Diagram Layout
• Adding and Reusing Process Events
• Saving Your Model
• Opening a Saved Model

Validating Your Process Model (Data Flow Diagram)

• DFD Rules
• Design Rules in Oracle SQL Developer Data Modeler
• Types of Processes
• Process Decomposition
• Decomposition Guidelines

Identifying Entities and Attributes

• What Is a Logical Data Model?
• Why Create an ERD?
• Components of an Entity Relationship Diagram
• Attributes
• Attribute Characteristics

Identify Relationships

• Relationships
• Components of a Relationship
• Relationships: Additional Examples
• Relationship Types
• Using a Relationship Matrix
• Determining a Relationship’s Existence
• Naming the Relationship
• Determining the Relationship’s Cardinality

Assign Unique Identifiers

• Unique Identifiers
• Unique Identifier Examples
• Identifying Relationships
• Identifying Relationships with Multiple Entities
• Non-Identifying Relationships
• Primary and Secondary Unique Identifiers
• Searching for Unique Identifiers

Using Oracle SQL Developer Data Modeler to Create the Entity Relationship Diagram

• Building an Entity Relationship Diagram
• Specifying Logical Model General Option
• Modifying Model Properties
• Notation Types
• Editing a Diagram Layout
• What Is a Subview?
• Creating a Subview
• What Is a Display?

Validating your Entity Relationship Diagram

• ERD Checklist
• Attribute Rules
• Distinguishing Attributes and Entities
• Attribute Optionality
• Adding Additional Information to the ERD
• Creating Reports

Normalizing your Data Model

• What Is Normalization?
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Normalization Example

Validating Relationships

• Resolving M:M Relationships
• Modeling Hierarchical Data
• Examining Recursive Relationships
• Resolving a M:M Recursive Relationships
• Modeling Exclusive Relationships
• Creating an Exclusive Relationship in Oracle SQL Developer Data Modeler
• Entity Type Hierarchies
• Modeling Subtypes in Oracle SQL Developer Data Modeler

Adding and Using Data Types

• Attribute Data Types
• Logical Type
• Types Administration
• Domain
• Adding a Check Constraint to a Domain
• Adding Ranges or Value Lists to a Domain
• Preferred Logical Types and Domains
• Creating Domains from Logical Types

Put It All Together

• Build an ERD from a Case Study

Map Your Entity Relationship Diagram to a Relational Database Design

• Why Create a Relational Model?
• Review: Database Design
• Relational Database Overview
• Terminology Mapping
• Naming Conventions
• Naming Restrictions with Oracle
• Ensuring That Your Logical Data Model Is Complete
• Mapping Simple Entities

Engineering Your Entity Relationship Diagram to a Relational Database Design in Oracle SQL Developer Data Modeler

• Relational Model and Relational Model Diagram Preferences
• Reviewing Table Properties
• Previewing the DDL for a Table
• Preferences: Classification Types
• Assigning a Classification Type to One Table
• Changing the Color for Classified Tables
• Changing the Prefix for Classified Tables
• Assigning Classification Types to Multiple Tables

Defining Your Physical Model

• 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 Segment Templates (Storage)
• Associating Physical Objects with Your Table

Generating Your Database

• Database Generation
• Generating DDL
• DDL Preferences
• DDL/Migration General Options
• Design Rules
• Working With Rule Sets
• Working With Custom Rules
• Working With Libraries

Altering an Existing Design

• Approaches to Modeling
• SUsing Import to Create a Model
• Importing an Existing Database
• Importing Domains
• 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

Working in a Collaborative Environment

• The Benefits of Version Control
• Working With Data Modeler and Subversion
• Pending Changes
• Basic Workflow: Using Subversion with a Design
• Maintaining Versions