Global Locations Table
Duty/Concept Area(s):Defining Database Constraints
Creating Additional Database Objects
Maintaining Database Security and System Security
Making Database Transactions
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.
- Write the CREATE TABLE statement to create the GLOBAL_LOCATIONS table.
- For each column, supply an appropriate data type and length.
- Set the date_opened to a default value of SYSDATE.
- Set the web address to have a default value of www.globalfastfood.com.
- Add a table-level constraint that creates a composite unique key between the phone and email.
- Describe the table.
- Query the data dictionary to display the constraint names, types, and delete_rule.
Big Question:What coding is required to create a table with default values and constraints?
- Which data types are appropriate for the required field?
- How is a default value coded?
- What is the proper syntax to add a table- or column-level constraint?
- How do you query the data dictionary to view information regarding constraints?
COM 3, COM.16
- Properly code the CREATE TABLE statement in SQL.
- Use a database application such as Access to create the table.
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.
- What is the best way to create a database to accomplish the task?
- What software should be used?
- How do you retrieve specific information from the database?
- How many tables will the database need?
- Will you need to create a data model?
- How will you enter information into the database?
- Do your NOT NULL restraints work?
- Do your foreign key relationships work?
- Do your primary key constraints work?
A.1, COM.13, COM.15, COM.16
- A script for creating each table
- A script for each table to insert information
- Any additional code used to complete the project, such as adding foreign keys
- NFL.com for sample statistics
- MySQL for possible database software.
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.
- 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.
- Create new users with usernames and passwords.
- Use GRANT and REVOKE commands to give privileges to database users.
- 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
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?
- How can you create a snapshot or window to data that only shows certain columns and rows from the base tables?
- How would you permanently delete the snapshot or tool you created in the previous questions when they are no longer needed, while preserving the integrity of the data in the base tables?
COM.1, COM.3, COM.13
- Students may work individually or in groups of two or three to accomplish the tasks in this project.
- Students may create an electronic presentation of their solutions.
Oracle Academy data tables available in Oracle Application Express online development environment.
- Pratt, Phillip J., and Mary Z. Last. A Guide to SQL 8E. 8th Ed. Boston: Cengage Learning, 2010.
- Rischert, Alice. Oracle SQL by Example. 4th Ed. Boston: Pearson Education Inc., 2010.