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 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-1.
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 1-2 and 1-3.
Table 1-2 A Sample Relational DEPT Table | DeptNo | DeptName | |
| 10 | Marketing | |
| 20 | Purchasing | |
Table 1-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-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 1-4 and 1-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 1-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 | |
A purchase order can include many items. Table 1-5 shows that Purchase Order 451 includes three separate items. The link (forei...
