5.58 Examine the subscription form shown in Figure 5-53. Using the structure of this form,
do the following:
A. Create a model with one entity. Specify the identifier and attributes.
B. Create a model with two entities, one for customer and a second for subscription.
Specify identifiers, attributes, relationship name, type, and cardinalities.
C. Under what conditions do you prefer the model in A to that in B?
D. Under what conditions do you prefer the model in B to that in A?
5.62 Figure 5-57 shows the specifications for single-stage air compressor products. Note that
there are two product categories that are based on Air Performance: The Amodels are at
125 pounds per square inch of pressure, and the E models are at 150 pounds per square
inch of pressure. Using the structure and example data items in this list, do the following:
A. Create a set of exclusive subtypes to represent these compressors. The supertype
will have attributes for all single-stage compressors, and the subtypes will have attributes for products having the two different types of Air Performance. Assume that there might be additional products with different types of Air Performance.Specify the entities, identifiers, attributes, relationships, type of category cluster, and possible determinant.
B. Figure 5-58 shows a different model for the compressor data. Explain the entities, their type, the relationship, its type, and its cardinality. How well do you think this model fits the data shown in Figure 5-57?
C. Compare your answer in part A with the model in Figure 5-58. What are the essential differences between the two models? Which do you think is better?
D. Suppose you had the job of explaining the differences in these two models to a highly motivated, intelligent end user. How would you accomplish this?
CHAPTER 6 EXERCISES:
6.30 Describe how to represent a mixed entity relationship. Give an example other than one in this chapter.
6.31 Describe how to represent a supertype/subtype entity relationship. Give an example other than one in this chapter.
6.32 Describe two ways to represent a 1:1 recursive relationship. Give an example other than one in this chapter.
CHAPTER 7 EXERCISES
7.29 Write an SQL statement to join EMPLOYEE and ASSIGNMENT and include all rows of EMPLOYEE in your answer, regardless of whether they have an ASSIGNMENT. Run this statement.
7.33 Write an SQL statement to create a view named FinanceEmployeePhoneView that shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE.FirstName as EmployeeFirstName, and EMPLOYEE.Phone as EmployeePhone for employees who work in the Finance department. Run this statement, and then test the view with an SQL SELECT statement.
7.35 Write an SQL statement to create a view named EmployeeProjectAssignmentView that shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE.FirstName as EmployeeFirstName, EMPLOYEE.Phone as EmployeePhone, and PROJECT.Name as ProjectName. Run this statement, and then test the view with an SQL SELECT statement.
CHAPTER 8 EXERCISES
8.49 Suppose that tables T1 and T2 have a 1:N relationship, with T2 as the child. Show the
SQL statements necessary to remove table T1. Make your own assumptions about the
names of keys and foreign keys.
8.52 Suppose that the table EMPLOYEE has a 1:N relationship to the table PHONE_NUMBER.
Further suppose that the key of EMPLOYEE is EmployeeID and the columns of PHONE_NUMBER are PhoneNumberID (a surrogate key), AreaCode, LocalNumber, and EmployeeID (a foreign key to EMPLOYEE). Write all SQL statements necessary to redesign this database so that it has just one table.
Explain the difference between the result of Project Question 8.51 and the result of this question.