Relational Model of Databases

Relational Model of Databases

As an example of how data can be organized conceptually, we shall describe the relational data model. In this conceptual model, the data in the database is viewed as being organized into a series of relations or tables of data which are associated in ways defined in the data dictionary. A relation consists of rows of data with columns containing particular attributes. The term "relational" derives from the mathematical theory of relations which provides a theoretical framework for this type of data model. Here, the terms "relation" and data "table" will be used interchangeably. Table 14-2 defines one possible relation to record unit cost data associated with particular activities. Included in the database would be one row (or tuple) for each of the various items involved in construction or other project activities. The unit cost information associated with each item is then stored in the form of the relation defined in Table 5-2.

This entry summarizes the unit costs associated with construction of 12" thick brick masonry walls, as indicated by the item DESCRIPTION. The ITEM_CODE is a numerical code identifying a particular activity. This code might identify general categories as well; in this case, 04.2 refers to general masonry work. ITEM_CODE might be based on the MASTERFORMAT or other coding scheme. The CREW_CODE entry identifies the standard crew which would be involved in the activity. The actual composition of the standard crew would be found in a CREW RELATION under the entry 04.2-3, which is the third standard crew involved in masonry work (04.2). This ability to point to other relations reduces the redundancy or duplication of information in the database. In this case, standard crew number 04.2-3 might be used for numerous masonry construction tasks, but the definition of this crew need only appear once.

WORK_UNIT, OUTPUT and TIME_UNIT summarize the expected output for this task with a standard crew and define the standard unit of measurement for the item. In this case, costs are given per thousand bricks per shift. Finally, material (MATL_UNIT_COST) and installation (INSTCOSTS) costs are recorded along with the date (DATEMCOS and DATEICOS) at which the prices were available and entered in the database. The date of entry is useful to insure that any inflation in costs can be considered during use of the data.

The data recorded in each row could be obtained by survey during bid preparations, from past project experience or from commercial services. For example, the data recorded in the Table 5-2 relation could be obtained as nationwide averages from commercial sources.

An advantage of the relational database model is that the number of attributes and rows in each relation can be expanded as desired. For example, a manager might wish to divide material costs (MATL_UNIT_COST) into attributes for specific materials such as cement, aggregate and other ingredients of concrete in the unit cost relation defined in Table 5-2. As additional items are defined or needed, their associated data can be entered in the database as another row (or tuple) in the unit cost relation. Also, new relations can be defined as the need arises. Hence, the relational model of database organization can be quite flexible in application. In practice, this is a crucial advantage. Application systems can be expected to change radically over time, and a flexible system is highly desirable.

With a relational database, it is straightforward to issue queries for particular data items or to combine data from different relations. For example, a manager might wish to produce a report of the crew composition needed on a site to accomplish a given list of tasks. Assembling this report would require accessing the unit price information to find the standard crew and then combining information about the construction activity or item (eg. quantity desired) with crew information. However, to effectively accomplish this type of manipulation requires the definition of a "key" in each relation.

In Table 5-2, the ITEMCODE provides a unique identifier or key for each row. No other row should have the same ITEMCODE in any one relation. Having a unique key reduces the redundancy of data, since only one row is included in the database for each activity. It also avoids error. For example, suppose one queried the database to find the material cost entered on a particular date. This response might be misleading since more than one material cost could have been entered on the same date. Similarly, if there are multiple rows with the same ITEMCODE value, then a query might give erroneous responses if one of the rows was out of date. Finally, each row has only a single entry for each attribute.

The ability to combine or separate relations into new arrangements permits the definition of alternative views or external models of the information. Since there are usually a number of different users of databases, this can be very useful. For example, the payroll division of an organization would normally desire a quite different organization of information about employees than would a project manager. By explicitly defining the type and organization of information a particular user group or application requires, a specific view or subset of the entire database can be constructed. This organization is illustrated in Fig. 5-1 with the DATA DICTIONARY serving as a translator between the external data models and the database management system.

Behind the operations associated with querying and manipulating relations is an explicit algebraic theory. This algebra defines the various operations that can be performed on relations, such as union (consisting of all rows belonging to one or the other of two relations), intersection (consisting of all rows belonging to both of two relations), minus (consisting of all rows belonging to one relation and not another), or projection (consisting of a subset of the attributes from a relation). The algebraic underpinnings of relational databases permits rigorous definitions and confidence that operations will be accomplished in the desired fashion.