Purdue School of Engineering and Technology

Purdue School of Engineering and Technology

Database Implementation and Administration

CIT 47900 / 3 Cr.

(Class 2, Lab 2; or Class 3). Extends knowledge of database concepts. Topics include physical database design, client/server implementation and database administration. Given a logical database design, students develop physical database structures and implement a database application. Students carry out database design, construction, administration, and programming activities using client/server technology.

  • Oracle SQL Plus

Course Outcomes (What are these?)

  • Install, configure, and administer an Oracle 10g database (CIT i, l)
  • Create, configure and manage database structures including tablespaces, control files, indexes, tables, and users (CIT i, j)
  • Administer database recovery, backup, and concurrency controls (CIT j, k)

CIT Student Outcomes (What are these?)

(i)  An ability to use current techniques, skills, and tools necessary for computing practice.

(j)   An ability to use and apply current technical concepts and practices in the core information technologies

(k)  An ability to identify and analyze user needs and take them into account in the selection, creation, evaluation and administration of computer-based systems.

(l)   An ability to effectively integrate IT-based solutions into the user environment.

  • Oracle Architecture
  • Database Administrator Tools
  • Creating a Database and the Oracle Instance
  • Data Dictionary Views
  • Basic Storage Concepts and Settings
  • Database Queries
  • Table Management
  • Data Integrity Constraints and Index Management
  • Data Management
  • Security Management
  • Performance Monitoring
  • Proactive Maintenance
  • Backup and Recovery
Principles of Undergraduate Learning (PULs)

3. Integration and Application of Knowledge

4. Intellectual Depth, Breadth, and Adaptiveness

What You Will Learn

Oracle Architecture

  • Learn about Oracle 10g architecture and key Oracle 10g software
  • Look at the ORACLASS database used in exercises throughout the book
  • Discover differences between Oracle 10g client and server installation options
  • Learn how to use the Oracle Universal Installer
  • Examine why to use OFA (Optimal Flexible Architecture

Database Administrator Tools

  • Identify the main DBA tools in the Oracle 10g software suite
  • Configure Oracle Net Services to connect to the database
  • Examine Oracle database instance architecture
  • Examine Oracle database memory architecture
  • Examine Oracle database process architecture
  • Examine Oracle database connection management architecture
  • Start using the Enterprise Manager
  • Go through a brief introduction to the Database Control

Creating a Database and the Oracle Instance

  • Learn the steps for creating a database
  • Understand the prerequisites for creating a database
  • Configure initial settings for database creation
  • Create, start, and stop a database instance
  • Learn the basics of managing configuration parameter files
  • Learn the purpose and location of the alert log and trace files

Data Dictionary Views

  • Examine tablespaces and datafiles
  • Understand how the control file, datafiles, redo log files, and archive log files are linked
  • Examine advanced database architectures, including OMF, partitioning, replication, standby, and grids
  • Manage and multiplex control files
  • Use OMF to manage control files
  • Create new control files
  • View control file data
  • Learn to describe redo log files, groups, and members
  • Manage redo log groups and members
  • List useful dynamic performance views

Basic Storage Concepts and Settings

  • Differentiate between logical and physical structures
  • Create many types of tablespaces
  • Configure and view storage for tablespaces and datafiles
  • Use and manage undo data
  • Learn to describe and configure diagnostic (trace) files

Database Queries

  • Learn about different types of queries
  • Cover basic SQL functions and pseudocolumns available in Oracle database
  • Discover facts about NULL values, the DUAL table, and the DISTINCT clause
  • Learn about filtered, sorted, and aggregated queries
  • Discuss advanced queries including joins, subqueries, and other specialized queries

Table Management

  • Describe the different types of tables and their storage methods
  • Create relational and temporary tables
  • Examine datatypes
  • Create tables containing VARRAYs and nested tables
  • Create object and partitioned tables
  • View database object metadata in SQL*Plus, Enterprise Manager console, and the Database Control
  • Create tables with large object (LOB) columns
  • Create index-organized tables
  • Analyze tables to collect statistics of data in a table
  • Understand the tasks involved in table management including table storage structure
  • Changing table columns
  • Redefine a table when the table is online
  • Specialized table changes including flashback and transparent encryption
  • Understand the tasks involved in table management
  • Use data dictionary views to find information about tables and underlying structures

Data Integrity Constraints and Index Management

  • Learn the types and the uses of constraints
  • Examine the syntax and options for creating constraints
  • Work with practical examples of creating, modifying, and dropping constraints
  • Query database dictionary views to monitor constraints
  • Learn the types of indexes Oracle offers and when to use each type
  • Understand how to create each type of index
  • Determine which data dictionary views contain information on indexes
  • Find out how to monitor index usage and when to drop an index
  • Learn how to modify, rebuild, and coalesce an index
  • Learn the basics about views, sequences, and synonyms

Data Management

  • Discover changing data using Data Manipulation (DML) statements
  • Describe syntax for, and use the INSERT statement
  • Describe syntax for, and use the UPDATE statement
  • Describe syntax for, and use the DELETE statement
  • Describe and learn how to control transactions

Security Management

  • Create, modify, and remove users
  • Discover when and how to create, use, and drop profiles
  • Manage passwords
  • View information about users, profiles, passwords, and resources
  • Identify and manage system and object privileges
  • Grant and revoke privileges to users
  • Understand auditing capabilities and practice using auditing commands
  • Discover when and why to use roles
  • Learn how to create, modify, and remove roles
  • Learn how to assign roles
  • Examine data dictionary views of roles
  • Assign roles and privileges using the Enterprise Management console

Performance Monitoring

  • Discover performance and performance monitoring
  • Learn about different tools used for performance monitoring
  • Learn about gathering statistics
  • Discover invalidated database objects
  • Find out about performance metrics
  • Examine and change performance metrics
  • React to performance issues
  • Learn about locks

Proactive Maintenance

  • Learn more about the Database Control
  • Discover how to use advice performance tools
  • Learn about the segment advisor
  • Learn about undo management and the undo management advisor
  • Learn about the memory advisor
  • Learn about the SQL access advisor
  • Learn about the SQL tuning advisor
  • Learn more about the changing and using baseline metrics

Backup and Recovery

  • Discover the difference between backup, restore, and recovery
  • The difference between cold and hot backups
  • Learn about different tools used for backup and recovery
  • Learn about different types of failure that create a need to recover a database
  • Learn about different backup strategies
  • Learn about essential configuration as applied to making a database fully recoverable
  • Learn about the specific backup and recovery scenarios