Oracle Data Modeling and Relational Database Design
Starting dates and places
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…
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
- Define and identify business objectives, assumptions, critical success factors, key performance indicators and problems
- Establish Business Direction Objectives
- List the reasons why process modeling is useful
- Describe the components of a Data Flow Diagram
- Build a 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 a DFD based on set of DFD Rules
- Identify different types of processes
- Decompose Processes into Primitive Processes
- Identify and Diagram Entities
- Identify and Diagram Attributes
- 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
- Identify unique identifiers for entities and relationships
- Identifying Relationships with Multiple Entities
- Non-Identifying Relationships
- Primary and Secondary Unique Identifiers
- Searching for Unique Identifiers
- 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
- Apply Diagram Layout and Attribute Rules
- Distinguish Entities from Attributes
- Evaluate Attribute Optionality
- Supplement the ERD with Useful Information
- Create Reports
- Define Normalization
- Normalize your ERD to third normal form
- Resolve M:M Relationships
- Model Hierarchical Data
- Examine Recursive Relationships
- Model Exclusive Relationships
- Model Entity Type Hierarchies
- Model Data Over Time
- 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
- Build an ERD from a Case Study
- 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
- 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
- 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
- Create objects in a physical model
- Refine relational model objects in the physical model
- 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
- 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
- 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
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.