Banner

 

9 - The Relational Model and Relational Algebra

Answers to exercises

1.
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.

3.
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.

5.
What is a simple relational database?

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

7.
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.

9.
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.

11.
Take a microcomputer relational database package and examine how well it satisfies Codd's rules.

This answer is based on Microsoft Access.

Rule Obey Comment
1 The information rule Yes All data appear to be stored as values in a table in Access.
2 Guaranteed access Yes All values in a database are addressable by specifying a table name, column name, and the primary key of the row in which it is stored.
3 Systematic treatment of null values Yes It appears as though Access uses a distinct representation for all unknown or inappropriate data.
4 Active on-line catalog of the relational model Yes MS Access System Objects consist of several tables that give important and useful information about your database. MSysObjects gives comprehensive information about database objects (i.e., queries, forms etc.) and may be queried with SELECT.
5 Comprehensive data sublanguage rule Yes Access uses SQL and its own GUI QBE to support data definition, data manipulation, security and integrity constraints, and transaction processing operations.
6 View updating rule Yes You can make changes to a view created by some queries that will update the database.
7 High level insert, update, and delete Yes You can perform high level functions.
8 Physical data independence Yes An Access database applications program can run without changes even if a different storage device is used or if an index is created.
9 Logical data independence Yes An Access database application program is not affected when information that preserves changes to base tables changes. That is, if a column is added to a table or a new table is added to a database, it does not impact the application programs.
10 Integrity independence Yes Integrity constraints can be defined.
11 Distribution independence N/A Since Access is not a distributed DBMS, this rule is not applicable.
12 Nonsubversion rule ? This is hard to judge unless you have programming skills in a language such as Visual Basic, which can operate on an Access database. You could write a program to test whether you can insert a row that does not obey the referential integrity constraint when this constraint is active. If you succeed, the nonsubversion rule is broken.
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";

This page is part of the promotional and support material for Data Management (sixth edition) by Richard T. Watson
For questions and comments please contact the author

Date revised: 19-Oct-2016