Oracle Data Modelling & Relational Database Design

Course code: OD01

4 days £1,840.00

This is a practical course covering the Data Modeling and Database Development process and the models that are used at each phase of the lifecycle.

Course content >

Who Should Attend?




Course Contents

This is a practical course covering the Data Modeling and Database Development process and the models that are used at each phase of the lifecycle. Participants use several real life examples to document business requirements, the flow of information through a particular process and what information needs to be captured to accomplish the business rules. Students create an Entity Relationship Diagram (ERD) and a Data Flow Diagram (DFD) in this phase of the course. Participants learn many techniques to revise and enhance their ERD such as normalizing the model, adding recursive and exclusive relationships, identifying entity hierarchies (subtypes/supertypes) and defining and using User Defined Data Types (Data Types Model). After the models have been validated, students map the objects and engineer the logical model to a relational model. The Relational Design is then optimized using many de-normalization techniques and adding some additional objects such as indexes and views to the Physical Design. Once the Relational Design has been validated, the Physical Model can be used to add all physical properties and finally generate the DDL to create the database objects.

Training Course Contents

Introduction to Modeling

  • List the reasons why modeling is important
  • Describe the phases of the Database and Application Development Lifecycle
  • Identify which modeling approach to use for a given situation

Document the Business Background

  • Define and identify business objectives, assumptions, critical success factors, key performance indicators and problems
  • Establish Business Direction Objectives

Build a Process Model (Data Flow Diagram)

  • List the reasons why process modeling is useful
  • Describe the components of a Data Flow Diagram
  • Build a Data Flow Diagram

Use SQL Developer Data Modeler to Create Your Data Flow Diagram

  • Load and set the default options for Oracle SQL Developer Data Modeler
  • Build a Data Flow Diagram using Oracle SQL Developer Data Modeler
  • Edit the Layout of your Data Flow Diagram

Validate Your Data Flow Diagram

  • Validate a DFD based on set of DFD Rules
  • Identify different types of processes
  • Decompose Processes into Primitive Processes

Identify Entities and Attributes

  • Identify and Diagram Entities
  • Identify and Diagram Attributes

Identify Relationships

  • Create a relationship between two entities
  • Model relationships using a relationship matrix

Assign Unique Identifiers

  • Identify unique identifiers for entities and relationships

Use Oracle SQL Developer Data Modeler to Create an Entity Relationship Diagram

  • Examine the General Options for Logical Data Modeling
  • Build an ERD in Oracle SQL Developer Data Modeler
  • Edit the Layout of your ERD
  • Create a Subview and Display

Validate Your Entity Relationship Diagram

  • Apply Diagram Layout and Attribute Rules
  • Distinguish Entities from Attributes
  • Evaluate Attribute Optionality
  • Define Naming Standards, Glossary and Abbreviations
  • Supplement the ERD with Useful Information

Normalize Your Data Model

  • Normalize your ERD to third normal form

Validate Relationships

  • Resolve M:M Relationships
  • Model Hierarchical Data
  • Examine Recursive Relationships
  • Model Exclusive Relationships
  • Model Entity Type Hierarchies
  • Model Data Over Time

Add and Use Data Types

  • Create different types of data types
  • Build a Data Type model
  • Analyze various relationships between structured types on your data type model
  • Assign data types to the attributes in your logical data model

Put It All Together

  • Build an ERD from a Case Study

Map Your Entity Relationship Diagram to a Relational Database Design

  • Describe why a database design is needed
  • Decide on naming conventions and rules
  • Perform a mapping between a logical and relational model
  • Utilize the SQL Developer Data Modeler facility

Analyze your Relational Model

  • Modify each Tables properties according to requirements
  • Determine when to create an Index or View

Denormalize Your Design to Increase Performance

  • Recognize when denormalization techniques can be used in your relational model

Define Your Physical Model

  • Create objects in a physical model
  • Refine relational model objects in the physical model

Generate Your Database

  • Generate DDL for your Database

Alter an Existing Design

  • Import from the Data Dictionary
  • Reverse Engineering to Create the Logical Model
  • Compare and Merge models
  • Export your model
  • Analyze your Model by running Data Modeler Reports

Create a Multidimensional Model

  • Describe each multidimensional object
  • Import a Model with Dimensions
  • Generate a Multidimensional Model
  • Review and Modify the Relational Model
  • Export the Multidimensional Model to an Oracle AW
Contact us for more information