Answers to Review Questions

1. What is the difference between a database and a table?

A table, a logical structure that represents an entity set, is only one of the components of a database. The database is a structure that houses one or more tables and metadata. The metadata are data about data. Metadata include the data (attribute) characteristics and the relationships between the entity sets.

2. What does a database expert mean when (s)he observes that a database displays both entity integrity and referential integrity?

Entity integrity describes a condition in which all tuples within a table are uniquely identified by their primary key. The unique value requirement prohibits a null primary key value, because nulls are not unique.

Referential integrity describes a condition in which a foreign key value has a match in the corresponding table or in which the foreign key value is null. The null foreign key value makes it possible not to have a corresponding value, but the matching requirement on values that are not null makes it impossible to have an invalid value.

3. Why are entity integrity and referential integrity important in a database?

Entity integrity is important, because it means that a proper search for an existing tuple (row) will always be successful. And the failure to find a match on a row search will always mean that the row for which the search is conducted does not exist in that table. Referential integrity is important, because its existence ensures that it will be impossible to assign a non-existing foreign key value to a table. For example, the referential integrity enforcement in a SALESREP is assigned to CUSTOMER relationship means that it will be possible for a customer not have a sales rep assigned to him or her, but it will be impossible to assign non-existing sales rep to a customer.

4. A database manual notes that "the file contains two hundred records, each one of which contains nine fields." Use appropriate relational database terminology to "translate" the preceding statement.

Using the proper relational terminology, the statement may be translated to "the table -- or entity set -- contains two hundred rows -- or, if you like, two hundred tuples, or entities. Each of these rows contains nine attributes."

5. Use the small database shown in Figure Q2.5 to illustrate the difference between a natural JOIN, an equiJOIN, and an outerJOIN.

Figure Q2.5 The Database for Questions 5 - 7

Database name: CH2_QUESTIONS

Table name STUDENT Table name: PROFESSOR

(Note: These database tables are found in the CH2_QUESTIONS database on the Instructor’s CD.)

The natural JOIN process begins with the PRODUCT of the two tables:

|STU_CODE |PROF_CODE |PROF_CODE |DEPT_CODE |
|100278 | |1 |2 |
|128569 |2 |1 |2 |
|512272 |4 |1 |2 |
|531235 |2 |1 |2 |
|531268 | |1 |2 |
|553427 |1 |1 |2 |
|100278 | |2 |6 |
|128569 |2 |2 |6 |
|512272 |4 |2 |6 |
|531235 |2 |2 |6 |
|531268 | |2 |6 |
|553427 |1 |2 |6 |
|100278 | |3 |6 |
|128569 |2 |3 |6 |
|512272 |4 |3 |6 |
|531235 |2 |3 |6 |
|531268 | |3 |6 |
|553427 |1 |3 |6 |
|100278 | |4 |4 |
|128569 |2 |4 |4 |
|512272 |4 |4 |4 |
|531235 |2 |4 |4 |
|531268 | |4 |4 |
|553427 |1 |4 |4 |

Next, a SELECT is performed on the PRODUCT generated in the first step to yield only the rows for which the PROF_CODE values in the STUDENT table are matched in the PROF table. Because only the STUDENT table’s PROF_CODE values 1, 2, and 4 yield matches in the PROFESSOR table, the SELECT yields the following output:

|STU_CODE |PROF_CODE |PROF_CODE |DEPT_CODE |
|128569 |2 |2 |6 |
|512272 |4 |4 |4 |
|531235 |2 |2 |6 |
|553427 |1 |1 |2 |

Finally, a PROJECT is performed to produce the natural JOIN output by listing only a single copy of each attribute. The order in which the query output rows are shown is not relevant. If the output is to be listed by having the STU_CODE values in ascending order, this result can be generated through an “order by” specification in the query – remind the students that they can learn how that is done in Chapter 5, “Structured Query Language (SQL)”.

|STU_CODE |PROF_CODE |DEPT_CODE |
|128569 |2 |6 |
|512272 |4 |4 |
|531235 |2 |6 |
|553427 |1 |2 |

The equiJOIN's results depend on the specified condition. For instance, if the equiJOIN specifies that ALL STUDENTS FOR WHOM THE ADVISOR CODE IS 2 are to be listed, the output will be

|STU_CODE |PROF_CODE |DEPT_CODE |
|128569 |2 |6 |
|531235 |2 |6 |

In the outerJOIN, the unmatched pairs would be retained and the values that do not have a match in the other table would be left null. Therefore, the will yield these results:

|STU_CODE |PROF_CODE |DEPT_CODE |
|100278 | | |
|128569 |2 |6 |
|512272 |4 |4 |
|531235 |2 |6 |
|531268 | | |
|553427 |1 |2 |
| |3 |6 |

Microsoft Access uses two outer join options to make it easy to find unmatched pairs. For example, its outer join selections, generated from its QBE (Query By Example) query generator, are particularly effective. Note, for example, the selected left outer join option in Figure Q2.5A1 and look at its output in Figure Q2.5A2 to see that professor 3 does not have any student advisees.

Figure Q2.5A1 The Left Outer Join QBE properties selection

If you select the option 2 in Figure Q2.5A1’s QBE option screen, you’d get the output shown in Figure Q2.5A2. Note that you can now easily detect that professor number 3 does not have any student advisees assigned to him or her. If you select the option 3 in Figure Q2.5A1’s QBE option screen, you’d get the output shown in Figure Q2.5A3. The latter output makes it easy to detect that students 100278 and 531268 do not yet have an advisor assigned to them.

Figure Q2.5A2 Figure Q2.5A1’s Left Outer Join Output

Figure Q2.5A3 The Right Outer Join Output

6. Draw the basic Entity Relationship diagram for the database shown in Figure Q2.5.

The Chen and Crow’s Foot E-R diagrams are shown next:

The Chen ERD

The Crow’s Foot ERD

7. Draw the relational schema for the database shown in Figure Q2.5.

The relational schema is shown next:

8. Suppose that you have the Entity Relationship model shown in Figure Q2.8:

FIGURE Q2.8 The ERD For Question 8

How would you convert this model into an Entity Relationship model that displays only 1:M relationships? (Make sure that you draw the revised entity relationship model.)

Because the relational database model does not support the M:N relationship between entities, we must convert the M:N relationship into a set of 1:M relationships that are linked through a composite or bridge entity. The name composite entity is based on the fact that the linking table contains at least the primary keys of each of the tables that it connects.

The Chen model will look like this:

The Crow’s Foot model yields this solution to question 8:

9. What are homonyms and synonyms, and why should they generally be avoided in database design?

Homonyms appear when more than one attribute has the same name. Synonyms exist when the same attribute has more than one name. Avoid both to avoid inconsistencies. For example, suppose we check the database for a specific attribute such as NAME. If NAME refers to customer names as well as to sales rep names, a clear case of a homonym, we have created a problem, because it is no longer clear which entity the NAME belongs to.

Also, it is difficult to keep track of foreign keys, especially during the database design process, if they are named differently from the primary keys they point to. Using REP_NUM as the foreign key in the CUSTOMER table to reference the primary key REP_NUM in the SALESREP table is much clearer than naming the CUSTOMER table's foreign key SLSREP. The proliferation of different attribute names to describe the same attributes will also make the data dictionary more cumbersome to use.

Some data RDBMSes let the data dictionary check for homonyms and synonyms to alert the user to their existence, thus making their use less likely. For example, if a CUSTOMER table contains the (foreign) key REP_NUM, the entry of the attribute REP_NUM in the SALESREP table will either cause it to inherit all the characteristics of the original REP_NUM, or it will reject the use of this attribute name when different characteristics are declared by the user.

Note: Like most rules, the homonym/synonym avoidance rule is sometimes broken to fit a perceived need. In fact, we have seen (too many) applications in which a table must employ two foreign keys to reference one attribute in another table. For example, an aircraft charter company may schedule a flight crew of two pilots to fly an airplane, one to fly as pilot in command and one as copilot. Any of the company's pilots may be scheduled either as pilot in command or as copilot. Therefore, the SCHEDULE entity might be implemented to require both PILOT and COPILOT attributes. Yet the PILOT table contains only one row for each pilot's attributes, so the SCHEDULE entity must use two foreign keys to access the same PILOT entity. (We have used this approach in problems 29-31 CH2_AVIA_CO aviation database as an illustration.) While this solution works, it is unnecessary and, worse, it limits the crew assignments. For example, what happens when a crew is composed of more than just a pilot and copilot? Should we then add a third – or a fourth, a fifth, and so on -- synonym in the SCHEDULE table? A much better solution – one that avoids synonyms -- would not require structural changes to accommodate changing crew requirements. Such a solution would use a composite entity to link the crew and the airplane to the assignment schedule. Although we will revisit this critical design issue in Chapter 3, it is not too early to introduce this example as the proverbial eye opener.

10. How would you implement a 1:M relationship in a database composed of two tables? Give an example.

Suppose that we have a car entity and an owner entity. Further suppose that it is reasonable to assume that: • a car is owned just by one person (owner) • a person can own more than one car.

The relationships we have just described may be represented by the following E-R model:

Chen model

Crow’s Foot model

An example of this relationship is shown next. Note that the "many" side of the relation (the CAR entity) contains the foreign key, which is the CUSTOMER entity's primary key.

Table name: CUSTOMER

Table name: CAR

Figure Q2.10 Question 10's Relational Schema

11. Identify and describe the components of the database table shown in Figure Q2.11, using correct terminology. Use your knowledge of the naming conventions to identify the table's probable foreign key(s).

FIGURE Q2.11 The EMPLOYEE Table for Question 11

Table name: EMPLOYEE Database name: CH2_QUESTIONS

Figure Q2.11's database table contains:

• one entity set: EMPLOYEE.

• five attributes: EMP_NUM, EMP_LNAME, EMP_INIT, EMP_FNAME, DEPT_CODE and JOB_CODE.

• ten entities: the workers Friedman, Olansky, Fontein, and Cruazona.

• one primary key: the attribute EMP_NUM because it identifies each row uniquely.

• two foreign keys: the attribute DEPT_CODE, which probably references a department to which the employee is assigned and the attribute JOB_CODE which probably references another table in which you would find the description of the job and perhaps additional information pertaining to the job.

12. Suppose that you are using the following a database composed of the two tables shown in Figure Q2.12:

Figure Q2.12 The Database For Question 12

Table name: DIRECTOR Database name: CH2_QUESTIONS

Table name: PLAY

a. Identify the primary keys.

DIR_NUM is the DIRECTOR table's primary key.
PLAY_CODE is the PLAY table's primary key.

b. Identify the foreign key.

The foreign key is DIR_NUM, located in the PLAY table. Note that the foreign key is located on the "many" side of the relationship between director and play. (Each director can direct many plays ... but each play is directed by only one director.)

c. Draw the Entity Relationship model.

Chen model

Crow’s Foot model

d. Draw the relational schema to show the relationship between DIRECTOR and PLAY.

e. Suppose you wanted quick lookup capability to get a listing of all the plays directed by a given director. What table would be the basis for the index table, and what would be the index key?

The PLAY table would be the basis for the appropriate index table. The index key would be the attribute DIR_NUM.

f. What would be the conceptual view of the index table described in part e? Depict the contents of the (conceptual) index table.

| | |
|DIR_NUM |Index key (pointers to the PLAY table) |
| | |
|100 |4 |
| | |
|101 |2, 5, 7 |
| | |
|102 |1, 3, 6 |

Each DIR_NUM entry in the index file contains an ordered sequence of record numbers pointing to the corresponding rows in the PLAY table. For example, the director number "100" has only one play to match row 4 in the PLAY table. Note that the number 4 represents the fourth record in the PLAY table, not the play whose primary key is "4".

-----------------------
[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]