RDBMS and Oracle database administration

Learning objective

After completing this topic, you should be able to identify the basic concepts involved in RDBMS and Oracle database administration.

Exercise overview

Suppose you're a trainee database administrator (DBA) and you need to familiarize yourself with relational database fundamentals, as well as the issues involved in setting up and maintaining an Oracle 10g database.

In this exercise, you're required to identify the concepts and issues involved in setting up an Oracle database.

This involves the following tasks:

• defining relational database structure and integrity constraints
• choosing an Oracle database product and sequencing DBA tasks
• identifying tasks that can be performed with SQL

Task 1: Defining database structure and integrity constraints

As part of your overall understanding of relational databases, you need to recognize the components that make up a relational database. You need to understand the role of keys in creating relationships between database tables. And you need to recognize the role of integrity constraints in a relational database.

Step 1 of 3

Which components make up a relational database?

Options:

1. Columns
2. Fields
3. Tables
4. Text files
5. Rows

Result

Fields and tables - the latter consisting of columns and rows - are relational database components.

Option 1 is correct. Columns arrange data into specific data types, and always have their own name.

Option 2 is correct. A field, or cell, can be defined as a single column of a single row, or the intersection point of a column and row.

Option 3 is correct. Unlike a flat-file database, a relational database system is composed of multiple two-dimensional tables.

Option 4 is incorrect. Flat-file databases are composed of text files, but relational databases are composed of two-dimensional tables.

Option 5 is correct. A table consists of rows of data, which can be arranged into one or more columns. Each row contains data related to a specific subject.

Step 2 of 3

Match the different items to the correct description of their role in database tables.

Options:

1. A Field that does not allow duplicate or null values
2. A collection of data items related to a subject
3. A field in one table that points to a related field in another table

Targets:

1. Foreign key
2. Primary key
3. Row

Result

A foreign key is a field in one table that points to a related field in another table, a primary key is a field in a table that is referenced by a field in another table, and a collection of data items related to a subject is a row.

The primary key must be a unique identifier.

A row in a table is a collection of data items related to a subject.

A foreign key is so named because it points to values in a different, or foreign, table.

Step 3 of 3

You can use integrity constraints to define business rules in a database.

Which tasks can you perform with integrity constraints?

Options:

1. Add rows to a table
2. Disallow duplicate values
3. Disallow nulls
4. Require column values to match the primary key in a related table

Result

You can use integrity constraints to disallow duplicate values and nulls in a column, and require column values to match the primary key in a related table.

Option 1 is incorrect. You would use a SQL statement, not a constraint, to add rows to a table.

Option 2 is correct. The UNIQUE constraint does not allow duplicate values to be entered in the column to which it's applied.

Option 3 is correct. The NOT NULL constraint does not permit nulls, or empty entries, in the column to which it's applied.

Option 4 is correct. The FOREIGN KEY constraint requires each value in a column to match a value in a related table's primary key.

Task 2: Choosing an Oracle product and sequencing DBA tasks

You're ready to begin installing an Oracle database, but first you need to identify the correct Oracle product for your organization's needs. You also need to sequence the tasks that you, as a DBA, will need to perform once you've created the database.

Step 1 of 2

You have a large amount of data to store, you need to be able to search and retrieve data, and you need to avail of data warehousing. Due to budget constraints, you want a product that meets only your immediate needs.

Which Oracle product best suits your requirements?

Options:

1. Oracle Applications
2. Oracle Collaboration Suite
3. Oracle Database
4. Oracle Developer Suite

Result

Oracle Database is the product that best meets your requirements.

Option 1 is incorrect. Oracle Applications is an e-business suite for managing and automating processes across an organization. You do not require this at present.

Option 2 is incorrect. Oracle Collaboration Suite is a system for integrating communications data and file sharing, which you do not need in this case.

Option 3 is correct. Oracle Database enables the storage and retrieval of large amounts of data, and supports data warehousing.

Option 4 is incorrect. You would use Oracle Developer Suite to develop business-specific applications, which is not required in this case.

Step 2 of 2

You've installed the Oracle software and planned, created, and opened the database.

Rank the remaining DBA tasks in order of their priority.

Options

Option	Description
A	Implement database design
B	Enroll users
C	Monitor performance
D	Back up database

Result

Correct ranking

Option	Description
D	Back up database In addition to the initial backup upon database creation, you should schedule regular backup operations.
B	Enroll users Once you've backed up the database, you can enroll users and assign them the appropriate roles and privileges.
A	Implement database design Having enrolled database users, you implement the planned database design by creating tablespaces and database objects.
C	Monitor performance You can use Oracle's database resource management feature to monitor and control resource allocation among groups of users.

Task 3: Identifying tasks that can be performed with SQL

Having created your database, you can now use SQL to perform various tasks. Here you need to identify the tasks that can be performed using SQL.

Step 1 of 1

Identify the types of tasks you can use SQL to perform.

Options:

1. Add or delete table rows
2. Create and alter database objects
3. Manage database backup and recovery
4. Query data

Result

You can use SQL to add or delete rows, to create and alter database objects, and to query data.

Option 1 is correct. You can also use SQL to update table rows.

Option 2 is correct. You can also use SQL to drop database objects.

Option 3 is incorrect. You use Enterprise Manager to manage backup and recovery.

Option 4 is correct. SQL uses a set of standalone statements for retrieving database information.