# CTE Resource Center - Verso - Database Design and Management (Oracle) Task/Competency List

CTE Resource Center - Verso

Virginia’s CTE Resource Center

2019/2020 Competency-Based Task/Competency List for Database Design and Management (Oracle) (6660/36 weeks)

Tasks/competencies bordered in blue are considered optional when marked as such; they and/or additional tasks/competencies may be taught at the discretion of the school division. All other tasks are considered essential statewide and are required of all students.

Expand all

Ajax loader

Demonstrating Personal Qualities and Abilities

  1. Demonstrate creativity and innovation.
  2. Demonstrate critical thinking and problem solving.
  3. Demonstrate initiative and self-direction.
  4. Demonstrate integrity.
  5. Demonstrate work ethic.

Demonstrating Interpersonal Skills

  1. Demonstrate conflict-resolution skills.
  2. Demonstrate listening and speaking skills.
  3. Demonstrate respect for diversity.
  4. Demonstrate customer service skills.
  5. Collaborate with team members.

Demonstrating Professional Competencies

  1. Demonstrate big-picture thinking.
  2. Demonstrate career- and life-management skills.
  3. Demonstrate continuous learning and adaptability.
  4. Manage time and resources.
  5. Demonstrate information-literacy skills.
  6. Demonstrate an understanding of information security.
  7. Maintain working knowledge of current information-technology (IT) systems.
  8. Demonstrate proficiency with technologies, tools, and machines common to a specific occupation.
  9. Apply mathematical skills to job-specific tasks.
  10. Demonstrate professionalism.
  11. Demonstrate reading and writing skills.
  12. Demonstrate workplace safety.

Examining All Aspects of an Industry

  1. Examine aspects of planning within an industry/organization.
  2. Examine aspects of management within an industry/organization.
  3. Examine aspects of financial responsibility within an industry/organization.
  4. Examine technical and production skills required of workers within an industry/organization.
  5. Examine principles of technology that underlie an industry/organization.
  6. Examine labor issues related to an industry/organization.
  7. Examine community issues related to an industry/organization.
  8. Examine health, safety, and environmental issues related to an industry/organization.

Addressing Elements of Student Life

  1. Identify the purposes and goals of the student organization.
  2. Explain the benefits and responsibilities of membership in the student organization as a student and in professional/civic organizations as an adult.
  3. Demonstrate leadership skills through participation in student organization activities, such as meetings, programs, and projects.
  4. Identify Internet safety issues and procedures for complying with acceptable use standards.

Exploring Work-Based Learning

  1. Identify the types of work-based learning (WBL) opportunities.
  2. Reflect on lessons learned during the WBL experience.
  3. Explore career opportunities related to the WBL experience.
  4. Participate in a WBL experience, when appropriate.

Exploring Database Technologies

  1. Research the history of databases.
  2. List the major types of databases.
  3. Distinguish between a conceptual and a physical database model.
  4. Compare the structure of relational and non-relational database structures.
  5. Identify the characteristics of a relational database.
  6. Examine the database development life cycle.
  7. Research the future direction of database technologies.

Identifying Business Requirements

  1. Describe the process of modeling business requirements.
  2. Apply business concepts to the database model.
  3. Define entities among elements of significance.
  4. Define attributes of each entity.
  5. Select unique identifiers (UIDs).
  6. Define types of unique identifiers.
  7. Define business rules.

Examining Entity-Relationship Basics

  1. Analyze entities for relationships that exist among them.
  2. Distinquish among relationship types.
  3. Describe relationship transferability.
  4. Name relationships.
  5. Explain relationship optionality.
  6. Explain relationship degree/cardinality.

Applying Design Concepts to Database Models

  1. Identify elements of the graphic representation of a database model.
  2. Define drawing conventions for readability.
  3. Illustrate business rules in an entity-relationship model.
  4. Define the normalization process.
  5. Perform the normalization process.
  6. Resolve many-to-many relationships.
  7. Model hierarchical data.
  8. Model recursive relationships.
  9. Model exclusive relationships.
  10. Define relational-database terminology.
  11. Define a fact and dimension.
  12. Verbalize a diagram's relationship notation.

Transitioning from Design Concepts to Database Management

  1. Summarize the database-design process.
  2. Convert a conceptual design to a physical database model.
  3. Map simple entities, attributes, and primary keys.
  4. Identify data constraints.
  5. Map relationships to foreign keys.

Writing Structured Query Language (SQL) Statements

  1. Describe SQL.
  2. Distinguish among categories of SQL statements.
  3. Demonstrate the syntax for select statements (projection).
  4. Demonstrate methods for selecting columns and arithmetic expressions (selection).
  5. Incorporate column alias and literals in a SELECT statement.
  6. Describe operator precedence.
  7. Describe methods for displaying a table.

Restricting and Sorting Data Using SQL

  1. Restrict data, using the WHERE clause.
  2. Define comparison operators (e.g., =, >, <, >=, <=, <>, !=)
  3. Restrict data, using the BETWEEN ... AND and IN clauses.
  4. Restrict data, using wildcards and patterns within the LIKE condition.
  5. Demonstrate the use of the ESCAPE character with wildcard characters.
  6. Restrict (or specify) data containing nulls, using the IS (NOT) NULL clause.
  7. Demonstrate the use of two or more conditional statements using logical operators (i.e., AND, OR, NOT).
  8. Sort data by using ResultSet with the ORDER BY clause.

Performing Single-Row Functions

  1. Explain the concept of functions.
  2. Distinguish between the two categories of functions in SQL.
  3. Demonstrate restricting data using ANY and ALL.
  4. Define single-row functions.
  5. Describe the types/categories of single-row functions.
  6. Demonstrate the use of character, number, and date functions in SELECT statements.
  7. Describe the use of conversion functions.
  8. Demonstrate the use of null character handling.
  9. Demonstrate the use of conditional expressions.

Using JOIN Tables

  1. Describe the concept of joining data from two or more tables.
  2. Demonstrate the use of ANSI joins.
  3. Demonstrate the use of Oracle proprietary joins.

Aggregating Data Using GROUP Functions

  1. Define group/aggregate functions.
  2. Describe how to write a query containing group functions.
  3. Construct SQL code by applying a GROUP BY clause that uses column, alias, and number.
  4. Construct code by applying a HAVING clause.
  5. Demonstrate additional functionality of the GROUP BY clause.

Applying Advanced Data Selection Techniques

  1. Describe the types of problems that subqueries can solve.
  2. Define subqueries.
  3. Construct a single-row subquery.
  4. Construct a multi-row subquery.
  5. Construct a correlated subquery.
  6. Write a multiple-column subquery.
  7. Explain the behavior of subqueries when null values are retrieved.

Applying Data Manipulation Language

  1. Describe data manipulation language (DML).
  2. Describe integrity constraints.
  3. Insert rows into a table.
  4. Update data within a table.
  5. Delete rows in a table.
  6. Construct a MERGE statement.
  7. Construct a multi-table insert statement.

Modifying and Managing Tables

  1. Describe data definition language (DDL).
  2. Create a table.
  3. Create a table, using a subquery.
  4. Describe the data dictionary.
  5. Define common data types.
  6. Describe date/time data types.
  7. Write code to alter table definitions.
  8. Write SQL code to manipulate column definitions, using DROP, RENAME, and TRUNCATE commands.
  9. Differentiate among TRUNCATE, DROP, and DELETE.
  10. Describe FLASHBACK.

Defining Database Constraints

  1. Describe the necessity for database constraints.
  2. Distinguish between column-level and table-level constraints.
  3. List the types of constraints and their applications.
  4. Write a table- and column-level constraint.
  5. View constraints with table definition and the data dictionary.

Creating and Managing Views

  1. Describe a view.
  2. Create a view.
  3. Write SQL code to retrieve data through a view.
  4. Write SQL code to alter the definition of a view.
  5. Manipulate tables, using the INSERT, UPDATE, and DELETE commands through a view.
  6. Write SQL code to remove a view.
  7. Create an inline view.
  8. Describe the procedures for performing a top-n analysis.

Creating Additional Database Objects

  1. Describe database objects and their uses.
  2. Define a sequence object.
  3. Create a sequence.
  4. Use a sequence.
  5. Modify a sequence.
  6. Describe the index.
  7. Describe issues that affect the decision to create an index.
  8. Create an index.
  9. Create private and public synonyms.
  10. Describe the type of information available in the data dictionary.
  11. Create SQL code to retrieve information from the data dictionary.

Maintaining Database Security and System Security

  1. Define object privileges.
  2. Construct an object privilege.
  3. Describe roles.
  4. Define system privileges.
  5. Describe system privileges that can be granted to a user.
  6. Define a database link.
  7. Create a new database user.
  8. Drop a database user.

Making Database Transactions

  1. Define Transaction Control Language (TCL).
  2. Describe the importance of transaction control to businesses.
  3. Demonstrate the use of SAVEPOINT, ROLLBACK, and COMMIT.

Preparing for Industry Certification

  1. Describe the process and requirements for obtaining industry certifications related to the Database Design and Management (Oracle) course.
  2. Identify testing skills and strategies for a certification examination.
  3. Demonstrate ability to successfully complete selected practice examinations.
  4. Complete an industry certification examination representative of skills learned in this course (e.g., MOS, MTA, IC3).