Page 15
A database is a collection of data and the applications
that use the data. Without the applications, you cannot
collect and store data; without the data, you have no reason to
use your applications. Oracle7 really combines the
applications and data by enabling you to define
procedures (code) and triggers within the relational database
management system (RDBMS or Oracle7). Actions that occur
in the database itself execute these triggers and procedures.
Page 16
NOTE |
This definition of a database surprised me when I first discovered it. I had always thought the database consisted of the data and the objects that hold the data. I never thought of the applications as part of the database. In my mind, they were external to the database because it only consisted of the data. I like this definition because it shows the interdependence of the applications and the data. |
This chapter discusses the database objects that reside in Oracle7 or any other RDBMS. To successfully design database applications in Oracle Forms 4.5 or any other product, it is important to understand "what's under the hood." The setup of the data affects the way your applications work and how they ultimately support your users. A badly designed database causes badly designed applications.
NOTE |
Oracle Forms 4.5 is the product used to develop and execute GUI forms. The forms are used by an operator to manage and view the data. |
If you have a good background in relational database design, you may want to move on to Chapter 3. If you are new to database design and terminology, this chapter gives you the background to understand the engine that drives the applications. This chapter explains what a relational database is, what its components are, and how to design the tables.
The first installment of the Employee Information System Project appears at the end of this chapter. This installment introduces you to the project requirements. The goal of this installment is to develop a model of the database tables. This model contains the specifications for the database objects you will create in the second installment at the end of Part II.
A relational database is a set of tables or holders of records that are "related" to each other by a common value. Think of two index card files that you have in a cabinet. The first file contains a record for each employee. This information consists of data unique to the employee. It contains a payroll number, address, phone number, department number, and a Social Security Number. The second file contains a record for each tool purchase the employee made. Records in this table contain the employee's payroll number and information pertinent to the tool purchase. Each of the two files contains different information, but are "related" through their one common piece of data, payroll number (see Figure 2.1).
FIG. 2.1
Two sets of employee
index cards containing
tool information.
Page 17
Figure 2.1 shows two sets of index cards that are related to each other. The tool purchase records are sorted by employee number. If you want to find the tool records for "Palinski," you must first go to the employees file, identify the payroll number, and find the tool records that contain the payroll number. Without the common payroll number values on the records of each file, you could not use the two files together, and the tool purchase file would need to contain information from the employees file. To retrieve records from the tool purchase file by using the last name of the employee, each record in the tool purchase file needs to have the information in the related employees file. This causes redundant data to exist in the files. It is much more efficient to use the two files together to store and retrieve information.
When the data is placed in a relational database management system (rdbms) such as Oracle7, the structure is much the same. The index card files would be tables, the records in the files would be called records or rows, and each data value on the record would be called an item or field. The two tables would be "related" by a payroll_number field that existed in each table.
Before relational databases, database developers used hierarchical data structures. They consist of a file comprised of segments. Each segment is comparable to a table in a relational database, except the segment does not contain a common value with its parent and children segments. The parent knows the children through the use of a pointer. In Figure 2.2, the parent segment contains the unique information about the employee. The children segments contain records for tool purchases and glasses. Because the segments do not contain common values with their parents, they must use positional pointers to determine where the record exists. A pointer exists for each child record, telling the parent the disk location of the record.
FIG. 2.2
A hierarchical data structure.
NOTE |
A parent segment contains records that must exist before the records in a child segment can be created. For example, an employee record must exist before employee tool purchase records can be created.n |
This type of structure works fairly well until you have to add items to the segment. This causes problems; old database structures and software are very positional. In a sequential file, the programmer knows exactly where each record starts and stops. As Figure 2.3 shows, the first record begins at position 1 and ends at position 68. The next record begins at position 69 and ends at position 136. Each subsequent record begins 67 positions or bytes later. The code is written with this positioning in mind.