# CTE Resource Center - Verso - Database Design and Management (Oracle) Instructional Scenarios

CTE Resource Center - Verso

Virginia’s CTE Resource Center

Instructional Scenarios

Global Locations Table

Duty/Concept Area(s):

Defining Database Constraints
Creating Additional Database Objects
Maintaining Database Security and System Security
Making Database Transactions

Scenario:

Global Fast Foods has been successful this past year and has opened several new stores. They need to add a table to their database to store information about each of their store’s locations. They want to store the following information: Id, name, date_opened, address, city, ZIP Code (extended format), phone (including area code), email, fax number, web address, manager_id, number_of_employees.

Requirements:

Big Question:

What coding is required to create a table with default values and constraints?

Focused Questions:

  1. Which data types are appropriate for the required field?
  2. How is a default value coded?
  3. What is the proper syntax to add a table- or column-level constraint?
  4. How do you query the data dictionary to view information regarding constraints?

SOL Correlation:

Mathematics
COM 3, COM.16

Project-Based Assessment:

Football Team Statistics

Duty/Concept Area(s):

Applying Concepts to Database Models
Transitioning from Design Concept to Database
Modifying and Managing Tables
Restricting and Sorting Data Using SQL

Scenario:

Your high school football team would like you to create a database to maintain records on player statistics for all team members. They would also like complete records of every game. Statistics need to be kept for each player on both offense and defense. Offensive statistics include passing attempts, complete passes, passing yards, interceptions, rushing yards, receiving yards, receptions, touchdowns, fumbles, field goals attempted, and field goals made. Defensive statistics include tackles, interceptions, and sacks.

Big Questions:

Focused Questions:

SOL Correlation:

Mathematics
A.1, COM.13, COM.15, COM.16

Project-Based Assessment:

Resources:

Creating Views & Database Security

Duty/Concept Area(s):

Creating and Managing Views
Maintaining Database Security and System Security

Scenario:

As the company’s database administrator (DBA) you are asked by management to provide access to data from the database. Since most employees do not need access to the entire database or entire tables, you are asked to provide a solution.

You have supervisors who need to look up information about the employees in their department but not in others. It is also not necessary for these supervisors to have access to every column of information about these employees. You need to give permission to certain users to query data but make no changes or updates. Since confidentiality and security are key concerns, you must find a way to provide access only to the required information.

You have also been given the task to create new users for the database and assign roles and permissions to current users.

Requirements

  1. Create a simple tool for a manager to see the names, salary, department, and hire dates for their assigned employees without giving them access to the base tables.
  2. Create new users with usernames and passwords.
  3. Use GRANT and REVOKE commands to give privileges to database users.
  4. Assign privileges and rights to the new users created above. Be specific and assign rights to certain tables and restrict some users to only certain columns in a table.
    • Possible privilege and rights commands
    • SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, ALL
  5. Use the proper SQL commands to remove privileges and rights from certain users.

Big Question:

How can you provide access to selected data in the database to employees who need it while maintaining security and restricting access to parts of the data tables?

Focused Questions:

SOL Correlation:

English
10.5, 11.5
Mathematics
COM.1, COM.3, COM.13

Project-Based Assessment:

Note: This scenario can be used with many of the data tables provided in the Application Express environment through the Oracle Academy. Students can also use other data tables they have or create their own tables and populate them with data to use with this scenario.

Resources:

Online:
Oracle Academy data tables available in Oracle Application Express online development environment.

Textbooks: