Oracle Data Modeling and Relational Database Design

Total time

Oracle Data Modeling and Relational Database Design

Oracle University
Logo Oracle University

Need more information? Get more details on the site of the provider.

Starting dates and places

There are no known starting dates for this product.

Description

This course covers the Data Modeling and Database Development process and the models that are used at each phase of the lifecycle. You 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. You create an Entity Relationship Diagram (ERD) and a Data Flow Diagram (DFD) in this phase of the course. You learn many techniques to revise and enhance your ERD such as normalizing your model, adding recursive and exclusive relationships, identifying entity hierarchies (subtypes/supertypes) and defining and using User Defined Data Types (Data Types Model). After th…

Read the complete description

Frequently asked questions

There are no frequently asked questions yet. If you have any more questions or need help, contact our customer service.

This course covers the Data Modeling and Database Development process and the models that are used at each phase of the lifecycle. You 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. You create an Entity Relationship Diagram (ERD) and a Data Flow Diagram (DFD) in this phase of the course. You learn many techniques to revise and enhance your ERD such as normalizing your 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, you map the objects and engineer the logical model to a relational model. You then optimize the Relational Design using several denormalization techniques and add some additional objects such as indexes and views to the Physical Design. Once the Relational Design has been validated, you can create physical model objects such as databases, tablespaces, tables, and several others.. You also generate the DDL for your database. You work with design rules that can be applied to check and enforce the integrity and consistency of your designs. You also work with rules sets, custom design rules, custom libraries, and custom transformations. You also learn how to import from the data dictionary and export your model. You use the reverse engineer tool to create the logical model from your physical model. Finally, you compare and merge models and synchronize the Data Dictionary with changes in a model. In addition you learn how Data Modeler provides integrated support for using the Subversion versioning and source control system with Data Modeler designs.

The course is designed to be workshop based where many group discussions take place to develop the models and then SQL Developer Data Modeler tool is used to document the results of what is discussed.

Learn To:
  • Identify the types of models
  • Develop a process model (Data Flow Diagram)
  • Develop a logical data model ( Entity Relationship Diagram)
  • Use advanced data modeling techniques
  • Evaluate a Relational design
  • Create the Physical Model, add several Physical Model objects, and generate the DDL

Audience
  • Application Developers
  • System Analysts
  • Database Administrators
  • Business Analysts
  • Data Modelers
  • Database Designers

Course Topics 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
  • Opening and Saving the Process Model
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
  • Determining a Relationship’s Existence
  • Naming the Relationship
  • Determining the Relationship’s Cardinality
  • Validating the Relationship
Assign Unique Identifiers
  • Identify unique identifiers for entities and relationships
  • Identifying Relationships with Multiple Entities
  • Non-Identifying Relationships
  • Primary and Secondary Unique Identifiers
  • Searching for Unique Identifiers
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
  • Supplement the ERD with Useful Information
  • Create Reports
Normalize Your Data Model
  • Define Normalization
  • 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
  • Map the logical model into a relational model
  • Map exclusive relationships to foreign keys
  • Map subtypes to tables
  • Apply general options and setting the Compare/Copy options
  • View the mapping comparison and synchronize deleted objects
  • Utilize the SQL Developer Data Modeler tool
Analyze your Relational Model
  • Modify table properties according to requirements
  • Discover foreign keys
  • Create an index
  • Use the Table to View wizard
  • Use the View to Table wizard
  • Specify volume properties
  • Define spatial properties and column groups
  • Create views
Denormalize Your Design to Increase Performance
  • Recognize when denormalization techniques can be used in your relational model
  • What Is Denormalization?
  • Storing Derivable Values
  • Pre-Joining Tables
  • Hard-Coded Values
  • Keeping Details with the Master Table
  • Repeating Current Detail with the Master Table
  • Hierarchy Level Indicator
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
  • Selecting a Database, "Create" Selection, and DDL Script
  • Assigned to Users, "Drop" Selection, and Name Substitution
  • Including Table Scripts, Masking Oracle Errors, and Using Find
  • DDL Preferences
  • Design Rules
  • Working With Rule Sets and Custom Rules
  • Working With Libraries and Transformations
Alter an Existing Design
  • Using Import to Create a Model
  • Importing an Existing Database and Domains
  • Creating a Logical Data Model from Your Relational Model
  • Reviewing and Making Changes to Your Logical Model
  • Forward Engineering to a New Relational Model
  • Comparing Your Relational Model Changes With What is in the Database
  • Synchronizing the Data Dictionary With Changes in a Model
  • Running the Generated DDL Script in Oracle SQL Developer and Confirming the Change
Working in a Collaborative Environment
  • Describe Version Control and its benefits
  • Work With Data Modeler and Subversion
  • Use Subversion With a Design

Course Objectives
  • Create an Entity Relationship Diagram by identifying entities, attributes, relationships and constraints from a set of requirements
  • Normalize the Entity Relationship Diagram to third Normal form
  • Enhance the Entity Relationship Diagram to utilize several data modeling techniques
  • Create a Data Flow Diagram by identifying processes, external agents, information stores and information flows that show how the information flows and how it is being transformed
  • Engineer the Entity Relationship Model into an initial relational database design
  • Optimize the Relational Database Design
  • Complete the Physical Model and generate the DDL
  • Use Oracle SQL Developer Data Modeler to document all the concepts learned throughout the course

There are no reviews yet.

Share your review

Do you have experience with this course? Submit your review and help other people make the right choice. As a thank you for your effort we will donate $1.- to Stichting Edukans.

There are no frequently asked questions yet. If you have any more questions or need help, contact our customer service.