Course Date | Start Time | End Time | Time Zone | Location | Days | Price | |
---|---|---|---|---|---|---|---|
Call for In Class or Live Virtual Dates | 4 | $4,000 USD | Purchase |
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