How are relations and relational algebra used in databases, and in what ways do the use of these formal systems facilitate database design and implementation?

Before we examine the formal concepts of relations and relational algebra, we will consider the practical issues that arise when operating on data in the form of tables (or relations).

A Simple Database

Consider a database that might be maintained by the Computer Science Department of the people and machines in the laboratories. The database contains information about the workstations, printers, servers, and registered users. Here are tables that describe the various parts of the database:

  Workstation Table
  Name    Room    Mem   Proc  Monitor
  coke     633   16384  SP4   color17      
  bass     633    8124  SP2   color19      
  bashful  633    8124  SP1   b/w
  tab      628    8124  SP4   color17
  crush    628   16384  SP4   color17      
  Printer Table
  Name    Room    Status    Res.    Color    Speed    
  chaucer  737      up      600       N        12
  keats    706      up      300       N         6
  poe      707     down     300       N         6
  dali     737      up      600       Y         6
  uglab    633      up      600       N        12
  Server Table
  Name    Status    MB Disk    MB Avail  # Users
  diamond   up       5120       1024        30
  emerald   up      12228       2048       122
  graphite  down     5120          0        30
  ruby      up       8192        512        60
  frito     up      12228       4096       100
  User Table
  Login    Name        Status   Idle  Shell  Sever
  aaron    A. Cohn     UG      29:41  csh    UG
  brown    C. Brown    Faculty 10:24  csh    diamond
  bukys    L. Bukys    Staff    0:00  bsh    slate
  jli      J. Inka     UG       0:00  bsh    UG
  leblanc  T. LeBlanc  Faculty  1:06  csh    ruby
  poulos   A. Poulos   Grad     2:01  csh    ruby
  rfk      R. Kemp     UG       1:11  csh    UG


Operations on the Database

Given a database consisting of this information, what operations might we want to perform?

Some lookup operations will be trivial, such as "how long has user bukys been idle?" This operation simply searches the user table for login "bukys" and returns the corresponding "Idle" field.

A slightly more complex operation is "how many servers are currently up and running"? This operation must scan every entry in the server table, check the status field, and increment a counter each time the status is "up".

Some operations might be pretty complex however, because they span multiple tables in the database. For example, suppose we want a list of all the users who have been idle less than an hour, and whose server is currently down. Here's how that operation might be implemented:

There are other ways to get the same information; We could iterate over each server that is down, looking for users of that server.

How can Relations and Relational Algebra Help?