Knowing Just Enough about Relational Databases

Building a system in Oracle or some other relational database product does not automatically make it a relational database. Similarly, you can design a perfectly good relational database and implement it in something other than a relational database product. We discuss two important areas:

• What do people mean by relational database?

• What is the Oracle relational database product?

What makes a database "relational"?

When a database is described as relational, it has been designed to conform (at least mostly) to a set of practices called the rules of normalization. A normalized database is one that follows the rules of normalization.

For example, in an organization, you have employees who work in specific departments. Each employee and department has a number and a name. You could organize this information as shown in Table 1.

Table 1: Sample Employee Information

EmpNo	Ename	DeptNo	DeptName
101	Abigail	10	Marketing
102	Bob	20	Purchasing
103	Carolyn	10	Marketing
104	Doug	20	Purchasing
105	Evelyn	10	Marketing

If you structure your data this way and make certain changes to it, you'll have problems. For example, deleting all the employees in the Purchasing department will eliminate the department itself. If you change the name of the Marketing department to "Advertising," you would need to change the record of each employee in that department.

Using the principles of relational databases, the Employee and Department data can be restructured into two separate tables (DEPT and EMP), as shown in Tables 2 and 3.

Table 2: A Sample Relational DEPT Table

DeptNo	DeptName
10	Marketing
20	Purchasing

Table 3: A Sample Relational EMP Table

EmpNo	EName	DeptNo
101	Abigail	10
102	Bob	20
103	Carolyn	10
104	Doug	20
105	Evelyn	10

By using this structure, you can examine the EMP table to find out that Doug works in department 20. Then you can check the DEPT table to find out that department 20 is Purchasing. You might think that Table 1 looks more efficient. However, retrieving the information you need in a number of different ways is much easier with the two-table structure. Joining the information in the two tables for more efficient retrieval is exactly the problem that relational databases were designed to solve.

When the tables are implemented in the database, the information in the two tables is linked by using special columns called foreign keys. In the example, the DeptNo column is the foreign key linking the Department and Employee tables.

Tables 4 and 5 show another common database structure, namely a purchase order (PURCH_ORDER table) for an item and the information details associated with the purchase order (PURCH_ORDER_DTL table).

Table 4: A Sample Relational PURCH_ORDER Table

PO_Nbr	Date
450	12/10/2006
451	2/26/2006
452	3/17/2006
453	6/5/2006

Table 5: A Sample Relational PURCH_ORDER_DTL Table

PO_Nbr	Line_Nbr	Item	Qty	Price
450	1	Hammer	1	$10.00
451	1	Screwdriver	1	$8.00
451	2	Pliers	2	$6.50
451	3	Wrench	1	$7.00
452	1	Wrench	3	$7.00
452	2	Hammer	1	$10.00
453	1	Pliers	1	$6.50

A purchase order can include many items. Table 5 shows that Purchase Order 451 includes three separate items. The link (foreign key) between the tables is the Purchase Order Number.

Understanding basic database terminology

A database consists of tables and columns, as described in the preceding section. There are some other terms you need to know in order to understand how databases work. A database is built in two stages. First you create a logical data model to lay out the design of the database and how the data will be organized. Then you implement the database according to the physical data model, which sets up the actual tables and columns. Different terminology applies to the elements of the logical and physical designs. In addition, relational database designers use different words from object-oriented (OO) database designers to describe the database elements. Table 6 shows the words used in each of these cases.

Table 6: Database Design Terminology

Logical/Relational	Logical/Object-Oriented	Physical Implementation
Entity	Class	Table
Attribute	Attribute	Column
Instance	Object	Row

The definitions of the words in Table 6 are as follows:

• Entity: An entity corresponds to something in the real world that is of interest and that you want to store information about. Examples of entities include things such as departments within an organization, employees, or sales. Each specific department or employee is considered an instance of that entity. For example, in Table 3, Doug is an instance of the entity Employee. (In the OO world, Doug would be an object in the Employee class.)

• Attribute: This word is used in both relational and OO databases to represent information about an entity instance or an object that will be tracked. An example of an attribute might be the birth date or Social Security number of an employee.

• Entities (classes), their attributes, and instances (objects): These are implemented in the database as tables, columns, and rows respectively.

One additional important concept to understand when dealing with relational databases is the primary key. A primary key uniquely identifies a specific instance of an entity. No two instances of an entity can have the same primary key. The values of all parts of the primary key must never be null. The most common types of primary keys in relational databases are ID numbers. For example, in Table 3, the EmpID can be the primary key. Sometimes more than one attribute (or sets of attributes) can be used as a primary key. These attributes are called candidate keys, one set of which must be designated as the primary key.

dummies

Source:http://www.dummies.com/how-to/content/knowing-just-enough-about-relational-databases.html