Assignment

Part 1

- Answer in your own words, and reference sources

1. Describe the characteristics a table must have to be considered a relation.

2. Define the term domain, and explain the significance of the domain integrity constraint to a relation.

3. Using entity-attribute notation, give an original example of a table that is not a proper relation. Explain what makes it not a relation.

4. Under what circumstances can a relation have duplicate rows?

5. Define the term non-unique key and give an original example using entity-attribute notation.

6. Give an original example of a relation with a unique composite key, using entity-attribute notation.

7. What is a surrogate key, and under what circumstances would you use one?

8. Why are the values of surrogate keys often hidden from users on forms, queries, and reports?

9. Explain the term foreign key and give an original example, using entity-attribute notation.

10. Define the term referential integrity constraint and give an example of one.

11. How does a referential integrity constraint contribute to database integrity?

Part 2

1. Explain how entities are transformed into tables.

2. Explain how attributes are transformed into columns. What column properties do you take into account when making the transformations?

3. Why is it necessary to apply the normalization process to the tables created according to your answer to question 1?

4. What is denormalization?

5. When is denormalization justified?

6. Explain the problems that unnormalized tables have for insert, update, and delete actions.

Part 3

Describe in your own words the purpose of the two business rules:

Finally, assume the folowing rules, which are called business rules:

* If an EMPLOYEE row is to be deleted and that row is connected to any

ASSIGNMENT, the EMPLOYEE row deletion will be disallowed.

* If a PROJECT row is deleted, then all the ASSIGNMENT rows that are connected to the deleted PROJECT row will also be deleted.

The business sense of these rules is as follows:

* If an EMPLOYEE row is deleted (for example, if the employee is transferred), then someone must take over that employee's assignments. Thus, the application needs someone to reassign assignments before deleting the employee row.

* If a PROJECT row is deleted, then the project has been canceled, and it is unnecessary to maintain records of assignments to that project.

These rules are typical business rules.