Relational Database Technology

Objectives

This tutorial covers the following objectives:

• Define and give an example of a relational database.
• Identify table-key terms, including row, column, field, primary key, and foreign key.
• Relate the importance of databases to everyday life.

Purpose

• Databases are part of our everyday lives even though most of the time we don’t even think about them.
• If you have ever made an airline reservation, used an ATM machine, or made a mobile-phone call, you’ve used a database.
• In fact, many cities use intelligent traffic guiding system databases to control stoplights.
• So the next time you’re waiting at a red light, it may be a database that is responsible for your delay!
• In this lesson, you will learn more about databases and how they’re organized and created.

Some interesting databases found on the internet:

• Roller coaster database – lots of information and searchable
• Online music database that links to many other databases
• Power Rangers database
• List of 1,000 museums – great exploring

Relational Database Example

• A relational database allows tables to be related by means of a common field.
• As few as two tables can be considered a relational database if they share a common field.

• Realistically, databases used in business have many tables, each table sharing a common field with another table.
• The “countries” table shown above is one of several tables in the Employees database and just one example of the many tables that will be used in this course.
• To understand how important databases have become in today’s world, consider the following statistics:
  • Currently 20% of the world’s data resides in RDBMSs.
  • In the next two years, databases are expected to grow larger than 100 terabytes.
  • A database this big would be able to store 100,000 copies of the Encyclopedia Britannica or 200,000 hours of music or about 10 billion web pages.
• Some of the top 10 world’s largest databases using the Oracle RDBMS are:
  • France Telecom, 29.2TB — a communications company (a TB is a terabyte equivalent to 1,000 gigabytes)
  • Amazon.com with, 13 TB — selling books and merchandise
  • The Claria Corporation,12TB — Internet behavioral marketing company tracking Internet user behavior

Review Key Terms

• Let’s review the following key terms:
  • table — basic storage structure
  • column — one kind of data in a table
  • row — data for one table instance
  • field — the one value found at the intersection of a row and a column
  • primary key — unique identifier for each row
  • foreign key — column that refers to a primary-key column in another table

Properties of Tables

• There are six properties of tables in a relational database:
  • Property 1: Entries in columns are single-valued
  • Property 2: Entries in columns are of the same kind
  • Property 3: Each row is unique
  • Property 4: Sequence of columns is insignificant
  • Property 5: Sequence of rows is insignificant
  • Property 6: Each column has a unique name

Accessing Data in an RDBMS

• A relational database-management system (RDBMS) organizes data into related rows and columns.
• To access the data in a database, you do not need to know where the data is located physically, nor do you need to specify an access route to the tables.
• You simply use structured query language (SQL) statements and operators.

Communicating with Databases

• Working with the database is very similar to calling up and talking to a friend on the phone.
  • First, you must choose a method to communicate (the phone).
  • Once connected, you ask your friend a question (a query).
  • In response to your question, your friend answers (return of data).
• Pretty simple, and most of us are experts at this.
• In this class, our method of communication with the database will be through Oracle Application Express.
• When you ask a question using SQL, the application will return an answer.

Communicating With Databases

• As shown in the diagram, communicating with an RDBMS is accomplished by entering a SQL statement in Oracle Application Express.

• The request is then sent to the Oracle Server (a database running on a computer), the request is processed and the data returned is displayed.
• In very large database systems, many users, servers, and tables make up the RDBMS.

Categories of SQL Statements

• SQL statements are grouped into several categories depending on the functions they perform.
• During this course, you will learn how to use SQL to execute these statements.
• The data retrieval statement retrieves data from the database using the keyword SELECT.

There are four main categories of SQL statements:

• Data manipulation language (DML)
• Data definition language (DDL)
• Transaction control language (TCL)
• Data control language (DCL)

• Data manipulation language (DML)
  • DML statements begin with INSERT, UPDATE, DELETE, or MERGE and are used to modify the table data by entering new rows, changing existing rows, or removing existing rows.
• Data definition language (DDL)
  • DDL statements create, change, and remove data structures from the database.
  • The keywords CREATE, ALTER, DROP, RENAME, and TRUNCATE begin DDL statements.
• Transaction control language (TCL)
  • TCL statements are used to manage the changes made by DML statements.
  • Changes to the data are executed using COMMIT, ROLLBACK, and SAVEPOINT.
  • TCL changes can be grouped together into logical transactions.
• Data control language (DCL)
  • DCL keywords GRANT and REVOKE are used to give or remove access rights to the database and the structures within it.

Terminology

Key terms used in this tutorial included:

• Data control language (DCL)
• Data definition language (DDL)
• Data manipulation language (DML)
• Field
• Foreign key
• RDBMS
• Primary key
• Relational database
• Row
• Table
• Transaction control (TCL)

Summary

In this tutorial, you should have learned how to:

• Define and give an example of a relational database
• Identify table-key terms, including row, column, field, primary key, and foreign key
• Relate the importance of databases to everyday life