What is the Oracle7 Server?

Database Tables

Here is a simple table, such as you might find in an Oracle database:

CNUM	FNAME	LNAME	ADDRESS
4005	Julia	Peel	197 Myrtle Court, Brisbane, CA
4007	Terry	Subchak	2121 Oriole Way, Boston, MA
4008	Emilio	Lopez	31D San Bruno Ave. SF, CA
4011	Kerry	Lim	455 32nd St. #45, Brinton, KY

Table 4 - 1. Customers

Each row of this table describes one person, and each column has one type of information about that person. Note the column cnum. This is simply a number we generate to distinguish the customers from one another, as names are not necessarily unique. As you will see shortly when we discuss SQL, you refer to data in a relational database by its content, not by such things as where it is stored. Therefore, every table must have an identifying group of one or more columns whose values, taken as a set, are always different for every row of the table. This group, in this case the single column cnum, is called the primary key of the table. Locally generated numbers, as in this example, are a common and easy way to create primary keys.

Foreign Keys

1. You could fit all the phone numbers for a given person into a single column in a single row, in which case it would be difficult to access the phone numbers independently.

2. You could create a column for each type of phone number, in which case you would have to redesign your table each time a new type arose. This also could create an unwieldy number of mostly empty columns.

3. You could enter a new row for each phone number, in which case each such row would consist of redundant information except for the new phone number. This approach would be error-prone and waste space.

CNUM	PHONE	TYPE
4005	375-296-8226	home
4005	375-855-3778	beeper
4008	488-255-9011	home
4011	577-936-8554	home
4008	488-633-8591	work

Table 4 - 2. Customers_Phone

Notice that in this table cnum is not the primary key; it identifies the customer and therefore is the same for each phone number associated with a given customer. What, then, is the primary key? The combination of cnum and phone. If we list the same number for the same person twice, we really have made a duplicate entry and should eliminate one anyway.

The cnum column does have a special function, however, because it defines the relationship between Customers_Phone and Customers by associating each phone number with a customer. We say that it references the cnum column in Customers. A group of one or more columns, such as this, that references another group is known as a foreign key. The group of columns a foreign key references is called its parent key or its referenced key. Each foreign key value references a specific row in the table containing the parent key. Clearly, then, all sets of values in the foreign key have to be present once and only once in the parent key (although they may be present any number of times in the foreign key itself, as above) for the reference to be both meaningful and unambiguous. For that reason, the parent key must be either a primary key (the usual case) or another group of columns that is unique, which is known as a unique key.

Oracle can make sure that all primary and unique keys stay unique and that all foreign key references are valid; this is called maintaining referential integrity. For more information on foreign and parent keys, see Chapter 7 of the Oracle7 Server Concepts Manual and "CONSTRAINT clause" in Chapter 4 of the Oracle7 SQL Reference.

Although Oracle tables may look similar to, and often be presented as, HTML tables, there is a subtle but important difference. In HTML, tables are a way of presenting data to the user. The data itself may not actually have a tabular structure or any structure at all. For example, since not all browsers can interpret graphics, you could represent a bar graph in HTML as a table.

In the Oracle RDBMS, tables are how all data is structured and stored. Once the data from the tables is retrieved by other processes, however, those processes can reformat it so that it is not presented to the users as tables at all. For example, a very simple two-column table might consist of graphics (Oracle can store graphics, sound, video, or any binary value in a table using the datatype LONG RAW) in one column and a name for each graphic in the other. An application could use the names to request the graphics it wants from the database and present the graphics to the user, never revealing that they came from a table.

Users, Connections, Privileges, and Roles

The name under which you connect to Oracle7--your Oracle username-- is associated with a number of privileges, which are the rights to perform various actions. Privileges can be granted and revoked dynamically, and sometimes you can grant privileges you have received to other users as well. Some privileges apply to specific database objects--these are called object privileges. Others are more general in nature--these are called system privileges. For more information on privileges, see "GRANT" and "REVOKE" in Chapter 4 of the Oracle7 Server SQL Reference.

Roles are groups of privileges that are granted and revoked as single units. They simplify complex privilege assignments. They also differ from simple privileges in that they are not always in force. You can be granted a role, but have it disabled, in which case you cannot use the privileges the role contains until you enable it. For more information on roles, see "CREATE ROLE", "ALTER ROLE", and "GRANT" in the Oracle7 Server SQL Reference.

User actions can also be constrained by profiles. These control such things as how many simultaneous connections you can have and how long they can last. Profiles are beyond the scope of this manual. For more information on profiles, see "CREATE PROFILE" in the Oracle7 Server SQL Reference.

Distributed Databases

The software that enables distributed databases to communicate with one another is SQL*Net, an Oracle product that enables communication between various servers that possibly are on different platforms and using different network protocols. For more information on SQL*Net, see Understanding SQL*Net.