9 - The Relational Model and Relational Algebra

Answers to exercises

What reasons does Codd give for adopting the relational model?

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.

What is a domain? Why is it useful?

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.

What is a simple relational database?

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

Why do we need an entity integrity rule and referential integrity rule?

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.

What is meant by the term “a primitive set of operations?”

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.

12.

Use relational algebra to solve the following queries

12a.

List all donors.

donor;

12c.

List the phone numbers of donor number 106 and 125.

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

12e.

List the donors who have made a donation every year.

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

12g.

List the names of donors whose last name is Watson and live in Athens, GA.

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