9 - The Relational Model and Relational Algebra

Codd supports the adoption of the relational model because it is a practical tool for increasing the productivity of MIS departments. Codd has three reasons for adopting the relational model. First, other database models force the application programmer to code at a low level of structural detail. As a result, application programs are complicated and take longer to write and debug. Second, the relational model allows multiple records to be processed at one time. Third, the relational model allows users to make ad hoc queries. This means users can write and execute their queries without the help of the MIS department.

The major components of the relational model are:

Data structures -- the building blocks of the relational model. They include domains, relations, relational database, primary key, candidate keys, alternate keys, and foreign keys.

Integrity rules -- there are two types of integrity rules, entity integrity, and referential integrity.

Operators used to retrieve, derive, or modify data -- there are eight relational algebra operations: restrict, project, product, union, intersect, difference, join, and divide.

A domain is a set of values that must posses the same data type. It is all the legal values of an attribute. It is useful because it defines what comparisons are permissible. Only attributes drawn from the same domain should be compared. For instance, you would not want to compare the first name of a customer to the cost of a product. A domain could also be useful in some situations (e.g. state codes) to have the DBMS automatically check data validity.

The cardinality of a relation is its number of rows. You can think of this as the number of instances or records in a specific table. The degree of a relation is the number of columns or fields in a specific table. Cardinality changes frequently, degree does not.

A simple relational database consists of a collection of tables that may be related to one another.

A primary key is a relation’s unique identifier. It is the column or columns that are unique to each row in a table. It guarantees that each row of a relation can be uniquely addressed. An alternate key is a potential primary key in that it could be used to uniquely identify the rows in the table but another column or set of columns was selected to be the primary key.

Entity integrity ensures that each instance of an entity described by a relation is identifiable in some way. To ensure this, all components of a primary key of a relation must not be null. We need this rule because it would not always be possible to uniquely identify a row in a relation if the unique identifier contained nulls.

Referential integrity ensures that all foreign keys must have matching primary keys. This rule of integrity is important because it means that all relations can be determined. If there were a foreign key that did not match a primary key it would not be possible to determine the relation between the record of the foreign key and that of the primary key.

Union compatible means that two relations have the same column names, in the same order, and drawn from the same domains. The relational operations that require union compatibility are: union, intersect, and difference.

A primitive set of operations is the minimal set of relational operations needed to express all eight relational operators. Only five of the eight operators are required to express all eight. They are: restrict, project, product, union, and difference.

A fully relational database provides complete support for the three components of a relational database system: structures, integrity rules, and a manipulation language. A database can be relationally complete in terms of relational algebra and still fail to be fully relational because it does not support structures or integrity rules.

This answer is based on Microsoft Access.

donor; 

donor [dlname, dfname]; 

donor [donor, dphone] WHERE donor.donorno = '106' OR donor.donorno = '125'; 

donor_amounts := gift JOIN donor WHERE gift.donorno = donor.donorno; 

((gift[donorno, year] DIVIDEBY year[year]) JOIN donor) [dlname, dfname]; 

donor[dlname, dfname] WHERE donor.dstate = "Georgia" OR
                donor.dstate = "North Carolina"; 

donor[dlname, dfname] WHERE donor.dstate = "Georgia" AND
                donor.dcity = "Athens" AND donor.dlname =
                "Watson";