Relational Database Background

Texis, as most recent DBMSs, is based on the relational data model. The fundamental organizational structure for data in the relational model is the relation. A relation is a two-dimensional table made up of rows and columns. Each relation, also called a table, stores data about entities. These entities are objects or events on which an organization chooses to collect data. Patients, doctors, services, and insurance carriers are examples of entities.

The columns in a relation represent characteristics (attributes, fields, or data items of an entity, such as patient identification number, patient name, address, etc. The rows (called tuples in relational jargon) in the relation represent specific occurrences (or records) of a patient, doctor, insurance group number, service rendered, etc. Each row consists of a sequence of values, one for each column in the table.

In addition, each row (or record) in a table must be unique. The primary key of a relation is the attribute or attributes whose value uniquely identifies a specific row in a relation. For example, a Patient identification number (ID) is normally used as a primary key for accessing a patient's hospital records. A Customer ID number can be the primary key in a business.

Over the years, many different sets of terms have been used interchangeably when discussing the relational model. Table here lists these terms and shows their relationship.

Figure here illustrates two relations. The first one depicts patients and the second represents outstanding patient invoices. A row in the PATIENT relation represents a particular patient, while a row in the INVOICE relation represents a patient invoice. Thus, a relation provides a structure for storing data about some entity within the organization. In fact, a database in the relational model consists of several relations, each representing a different entity.

PATIENT ID  PATIENT NAME    ADDRESS            CITY         STATE

  107         Pryor           1 Ninigret Ave     Quonsett     RI
  111         Margolis        3 Chester Ave      Westerley    RI
  112         Frazier         7 Conch Rd         New London   CT
  123         Chen            163 Namcock Rd     Attleboro    MA
  128         Steckert        14 Homestead       Norwich      CT

INVOICE NO      DATE             AMOUNT             PATIENT ID

  71115           11/01/92         255.00             112
  71116           11/03/92         121.25             123
  71117           11/08/92         325.00             111
  71118           11/08/92          48.50             112
  71119           11/10/92          88.00             107
  71120           11/12/92         245.40             111
  71121           11/15/92         150.00             112
  71122           11/17/92         412.00             128
  71123           11/22/92         150.00             112

An important characteristic of the relational model is that records stored in one table can be related to records stored in other tables by matching common data values from the different tables. Thus data in different relations can be tied together, or integrated. For example, in Figure here, invoice 71115 in the INVOICE relation is related to Patient 112, Frazier, in the Patient relation because they both have the same patient ID. Invoices 71118, 71121, and 71123 are also related to Patient 112.

A database in the relational model is made up of a collection of interrelated relations. Each relation represents data (to the users of the database) as a two-dimensional table. The terms relation and table are interchangeable. For the remainder of the text, the term table will be used when referring to a relation.

Access to data in the database is accomplished in two ways. The first way is by writing application programs written in procedural languages such as C that add, modify, delete, and retrieve data from the database. These functions are performed by issuing requests to the DBMS. The second method of accessing data is accomplished by issuing commands, or queries, in a fourth-generation language (4GL) directly to the DBMS to find certain data. This language is called a query language, which is a nonprocedural language characterized by high-level English-like commands such as UPDATE, DELETE, SELECT, etc. Structured Query Language (SQL, also pronounced "Sequel") is an example of a nonprocedural query language.