Oracle Database Foundations

Basic knowledge of databases

Oracle Database Foundations – Logitrain course material

• Database Concepts
• Relational Database Concepts
• Gathering Requirements for Database Design
• Using Conceptual Data Modeling
• Using Unique Identifiers, Primary and Foreign Keys
• Documenting Business Requirements and Rules
• Using Attributes
• Identifying Relationships
• Identifying Hierarchical, Recursive, and Arc Relationships
• Validating Data Using Normalization
• Mapping Primary, Composite Primary and Foreign Keys
• Using Data Definition Language (DDL)
• Defining and using Basic Select statements
• Defining Table Joins
• Types of Databases Models
• Defining Levels of Data Abstraction
• The Language of Database and Data Modeling
• Defining a Table in a Database
• Defining Instance and Schema in Relational Databases
• Data Modeling – Creating the Physical Model
• Creating Physical Data Models
• Defining Supertype and Subtype Entity Relationships
• Using Unique Identifiers (UIDs)
• Resolving Many to Many Relationships and Composite Unique Identifiers
• Tracking Data Changes Over Time
• Mapping the Physical Model
• Mapping Entities, Columns and Data Types
• Introduction to SQL
• Using Structured Query Language (SQL)
• Using Data Manipulation Language (DML) and Transaction Control Language (TCL)
• Displaying Sorted Data

This course is likely to add to the employment-related skills of the participants. The skills developed are likely to be used in the course of being an employee or working in a business.

• Database administrators
• Database developers
• Candidates seeking Oracle Database certifications

• Describe the components of a database system
• Explain the purpose of a database
• Describe the characteristics of a relational database
• Explain the importance of relational databases in business
• List the major transformations in database technology
• Gather requirements to implement a database solution
• Explain business rules
• Describe a conceptual data model
• Explain the components of a conceptual/logical model
• Identify unique identifiers and a corresponding primary key
• Define composite and compound primary keys
• Define relationships and corresponding foreign keys
• Define barred relationships and the corresponding primary keys
• Explain the importance of clearly communicating and accurately capturing database information requirements
• Identify structural business rules
• Identify procedural business rules
• Identify business rules that must be enforced by additional programming (eg SQL)
• Describe attributes for a given entity
• Identify and provide examples of instances
• Distinguish between mandatory and optional attributes
• Distinguish between volatile and nonvolatile attributes
• Explain  one-to-one,  one-to-many, and many-to-many relationships
• Identify the optionality necessary for a relationship
• Identify the cardinality necessary for a relationship
• Identify nontransferable relationships
• Name a relationship
• Create ERDish sentences to represent ERDs
• Create ERDs to represent ERDish sentences
• Define a hierarchical relationship
• Define a recursive relationship
• Define an arc relationship
• Identify  UIDs in a hierarchical, recursive and arc relationship model
• Construct a model using recursion and hierarchies
• Identify similarities and differences in an arc relationship and a supertype/subtype entity
• Define the purpose of normalization
• Define the rules of First, Second, and Third Normal Forms
• Apply the rules of First, Second, and Third Normal Form
• Identify primary keys from an ERD
• Identify which ERD attributes would make candidate primary keys
• Describe the purpose of a foreign key in an Oracle Database
• Identify foreign keys from an ERD
• Describe the relationship between primary keys, composite primary keys, and foreign keys in an Oracle Database
• Describe the purpose of DDL
• Use DDL to manage tables and their relationships
• Identify the connection between an ERD and a Relational Database using SQL SELECT statements
• Build a SELECT statement to retrieve data from an Oracle Database table
• Use the WHERE clause to the SELECT statement to filter query results
• Describe the different types of joins and their features
• Use joins to retrieve data from multiple tables
• Describe types of database models (relational, object oriented, flat, network…)
• Compare the differences between the different types of databases
• Define the terminology used for database storage
• Describe levels of data abstraction used in relational databases
• Describe the structure of a single table
• Examine examples of an entity and a corresponding table
• Examine examples of an attribute and a corresponding column
• Explain instances and schemas in a relational database
• Create a physical data model
• Compare conceptual and  physical data models
• Describe an example of an entity
• Define supertype and subtype entities
• Implement rules for supertype and subtype entities
• Define the types of unique identifiers
• Select a unique identifier using business rules
• Define a candidate unique identifier
• Define an artificial unique identifier
• Resolve a many-to-many relationship using an intersection entity
• Identify the variations of unique identifiers after creation of an intersection entity
• Define a barred relationship
• Identify composite unique identifiers
• Explain necessity of tracking data changes over time
• Identify data that changes over time
• Identify the changes in unique identifiers after adding the element of time to an ERD
• Map entities to identify database tables to be created from an ERD
• Identify column data types from an ERD
• Identify common data types used to store values in a relational database
• Explain the relationship between a database and SQL
• Describe the purpose of  DML
• Use DML to manage data in tables
• Use TCL to manage transactions
• Use the ORDER BY clause to sort SQL query results