Introducing RDBMS

Learning objective

After completing this topic, you should be able to recognize the characteristics of a relational database system.

1. Introducing relational databases and RDBMSs

A relational database management system (RDBMS) is a system in which data is managed in related tables. Relational databases have become the standard database type in business today.

The standard language used to manipulate relational databases is Structured Query Language (SQL). The Oracle RDBMS was the first commercial implementation of SQL.

Unlike a flat-file database, a relational database system is composed of multiple two-dimensional tables. Arranging data in tables enables you to search for specific data more effectively, analyze data comparatively, and sort data in a particular data category.

A database table is composed of three elements.

• column
• field or cell
• row

column

A column contains data of a specific data type, and has its own name. For example, a row of data in an Employees table could be divided into columns such as name, ID, and salary.

field or cell

A field or cell can be defined as a single column of a single row, or the intersection point of a column and row. For example, a field or cell in an Employees table could contain a given employee's ID number.

row

A table consists of rows of data, or records, which can be arranged into one or more columns. Each row contains data related to a specific subject. For example, a row of data in an Employees table would represent a single employee.

Question

Match the components of a database table to their descriptions.

Options:

1. Field or cell
2. Row
3. Column

Targets:

1. Has a name and a data type associated with it
2. A single column of a single row
3. A collection of related data items

Answer

A column has a name and a data type associated with it. A field or cell is a single column of a single row. And a row is a collection of related data items.

For example, a cell in a customer table could contain a given customer's telephone number.

For example, a row of data in a customer table might represent a single customer.

For example, a column of data called Total Purchases in a customer table might hold a numeric data type.

2. Relationships between tables

Relational databases work by creating relationships between tables using columns that are common to more than one table. Relational databases are typically composed of many tables linked in this way.

In a relational database, tables that are related by a given column may share the same column values.

Instead of each table duplicating these values, each field in the column can contain a value, known as an identifier, that refers to a separate table containing the relevant data.

Suppose you have two tables, Customer and City. Each customer in the table must be associated with a city, but instead of listing each customer's city in the Customer table, the fields of the City column are identifiers that point to values in the City table.

Each entry in the Customer table points to only one row in the City table.

In relational database terms, an identifier is a key field. In the Customer table, the fields in the City column are known as the foreign key, as they point to values in a different, or foreign, table.

And in the City table, the primary keys are the fields to which the foreign key in the Customer table is pointing. The primary key field must always contain a value, and it must be unique.

Question

Suppose you have two tables, Employees and Department. Because every employee must be assigned to a department, each value in the Department column of the Employees table points to a value in the Department table.

Which statements accurately reflect the relationship between the tables?

Options:

1. Each entry in the Employees table points to only one row in the Department table
2. The Department table contains the primary key
3. The Employees table contains the foreign key
4. The key field in the Department table doesn't need to have a value

Answer

Each entry in the Employees table points to only one row in the Department table. The Department table contains the primary key, and the Employees table contains the foreign key.

Option 1 is correct. The primary key, in this case in the Department table, must contain a unique value so that each identifier in the Employees table points to a value.

Option 2 is correct. The primary key is the field in the Department table that is referenced by the identifier in the Employees table.

Option 3 is correct. This is because the Department fields of the Employees table point to values in another table.

Option 4 is incorrect. The primary key field must always contain a value, and it must be unique.

3. Integrity constraints

You can ensure that a relational database contains only valid data by implementing integrity constraints.

These are rules or constraints, specified at database creation and enforced by RDBMS, that must remain true in order to preserve database integrity.

An example of integrity constraints at work would be to implement a constraint in an automobile component parts database that demanded that every vehicle entered into the database had four wheels as standard.

This would ensure that data for motorcycles would not be entered into the database in error.

Oracle implements integrity constraints by including them as part of the table's definition in the data dictionary. All applications accessing the table must abide by the rules defined in the constraints.

Thus, if you need to change a constraint for any reason, you need only change it once in the database and not at application level.

There are five types of constraints supported by Oracle Database 10g.

• CHECK
• FOREIGN KEY
• NOT NULL
• PRIMARY KEY
• UNIQUE

CHECK

The CHECK constraint ensures that a value cannot be entered if it does not satisfy the logical expression of the constraint. This can be used to ensure data integrity and validity.

FOREIGN KEY

The FOREIGN KEY constraint requires each value in a column or columns to match a value in a related table's UNIQUE or PRIMARY KEY. It is also used to define what should happen to dependent data if the data referenced by it is altered.

NOT NULL

The NOT NULL constraint does not permit nulls, or empty entries, in the columns of a table. However, it doesn't prevent duplicate values from being entered.

PRIMARY KEY

The PRIMARY KEY constraint does not allow duplicate values or nulls (empty entries) in columns.

UNIQUE

The UNIQUE constraint does not allow duplicate values to be entered in columns. However, it doesn't prevent nulls being entered.

Question

Match the five constraints used by Oracle Database 10g to their uses.

Options:

1. CHECK
2. FOREIGN KEY
3. NOT NULL
4. PRIMARY KEY
5. UNIQUE

Targets:

1. Allows only values that satisfy the logical expression of the constraint
2. Requires column values to match the values in a related table's UNIQUE or PRIMARY KEY
3. Does not allow nulls in the columns of a table
4. Does not permit duplicate values or nulls in columns
5. Does not allow duplicate values to be entered in columns

Answer

The constraints have been correctly matched to their uses.

The CHECK constraint can be used to ensure data integrity and validity.

The FOREIGN KEY constraint is also used to define what should happen to dependent data if the data referenced by it is altered.

The NOT NULL constraint does not prevent duplicate values being entered.

The primary key field must contain a value, and it must be unique.

However, the UNIQUE constraint does not prevent nulls being entered in columns.

Summary

A relational database management system (RDBMS) is a system whereby data is managed in multiple, two-dimensional tables and manipulated using Structured Query Language (SQL). A database table is composed of rows, columns, and cells or fields.

Relationships between tables are created using columns that are common to more than one table. These contain values, or identifiers, that refer to values in separate tables. Fields containing identifiers are called the foreign key, and the fields referenced by them are called the primary key. The primary key field must always contain a unique value.

You can ensure that a relational database contains only valid data by implementing integrity constraints, which are rules that must remain true in order to preserve database integrity. Constraints supported by Oracle Database 10g include PRIMARY KEY, FOREIGN KEY, and NOT NULL.