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.

  

a. PATIENT Relation 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

b. INVOICE Relation 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

Figure: PATIENT and INVOICE Relations

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.