The Object-Relational Model

• Extensibility. You can extend the capability of the database server by defining new data types (and the access methods and functions to support them) and user-defined routines (UDRs) that allow you to store and manage images, audio, video, large text documents, and so forth.

Informix, as well as third-party vendors, package some data types and their access methods into DataBlade modules, or shared class libraries, that you can add on to the database server, if they suit your needs. DataBlade modules enable you to store non-traditional data types such as two-dimensional spatial objects (lines, polygons, ellipses, and circles) and to access them through R-tree indexes. A DataBlade might also provide new types of access to large text documents, including phrase matching, fuzzy searches, and synonym matching.

You can also extend the database server on your own, by using the features of the Universal Server that enable you to add data types and access methods. For more information, see Extending INFORMIX-Universal Server: Data Types.

You can create UDRs in Stored Procedure Language (SPL) and the C programming language to encapsulate application logic or to enhance the functionality of Universal Server. For more information, see Chapter 14, "Creating and Using SPL Routines."

• Complex Types. You can define new data types that combine one or more existing data types. Complex types enable greater flexibility in how you organize data at the level of columns and tables. For example, with complex types you can define columns that contain collections of values of a single type and columns that contain multiple component types. For information about complex types, see Chapter 10, "Understanding Complex Data Types."
• Inheritance. You can define objects (types and tables) that acquire the properties of other objects and add new properties that are specific to the object that you define. For information about inheritance, see "What Is Inheritance?".

Tables

(1 of 2)

stock_num	manu_code	description	unit_price	unit	unit_descr
. . .	. . .	. . .	. . .	. . .	. . .
1	HRO	baseball gloves	250.00	case	10 gloves/case
1	HSK	baseball gloves	800.00	case	10 gloves/case
1	SMT	baseball gloves	450.00	case	10 gloves/case
2	HRO	baseball	126.00	case	24/case
3	HSK	baseball bat	240.00	case	12/case
4	HSK	football	960.00	case	24/case
4	HRO	football	480.00	case	24/case
5	NRG	tennis racquet	28.00	each	each
. . .	. . .	. . .	. . .	. . .	. . .
313	ANZ	swim cap	60.00	case	12/box

A table represents all that is known about one entity, one type of thing that the database describes. The example table, stock, represents all that is known about the merchandise that is stocked by a sporting-goods store. Other tables in the demonstration database represent such entities as customer and orders.

Think of a database as a collection of tables. To create a database is to create a set of tables. The right to query or modify tables can be controlled on a table-by-table basis, so that some users can view or modify some tables but not others.

Columns

In the object-relational model, each column of a table can stand for one attribute or multiple attributes. For example, you might create a single column that contains all the address-related attributes. Such a column might contain distinct attributes for street, city, state, and zip code data. For information about creating columns that contain multiple attributes, see "Using a Named Row Type to Create a Column". A column can also stand for a collection of values within a single row of a table. For information about creating columns that contain collections, see "Collection Data Types".

Rows

Tables, Rows, and Columns

Now you understand that the object-relational model is a way of organizing data to reflect the world. It uses the following simple corresponding relationships:

table = entity	A table represents all that the database knows about one subject or kind of thing.
column = attribute(s)	A column represents one or more features, characteristics, or facts that is true of the table subject.
row = instance	A row represents one individual instance of the table subject.

Some rules apply about how you choose entities and attributes, but they are important only when you are designing a new database. (Chapter 8 and Chapter 9 cover database design.) The data model in an existing database is already set. To use the database, you need to know only the names of the tables and columns and how they correspond to the real world.

Operations on Tables

When you select data from a table, you are choosing certain rows and ignoring others. For example, you can query the stock table by asking the database management system to "select all rows in which the manufacturer code is HRO and the unit price is between 100.00 and 200.00."

When you project from a table, you are choosing certain columns and ignoring others. For example, you can query the stock table by asking the database management system to "project the stock_num, unit_price, and unit_descr columns."

A table contains information about only one entity; when you want information about multiple entities, you must join their tables. You can join tables in many ways. (The join operation is the subject of Chapter 3, "Composing Advanced SELECT Statements.")

Figure 1-4 Illustration of Selection and Projection

See Figure 1-4.