Consider the following relational database for the Quality Appliance Manufacturing Co. The database is designed to track the major appliances (refrigerators, washing machines, dishwashers, etc.) that Quality manufactures. It also records information about Quality’s suppliers, the parts they supply, the buyers of the finished appliances, and the finished goods inspectors. Note the following facts about this environment:

• Suppliers are the companies that supply Quality with its major components, such as electric motors, for the appliances. Supplier number is a unique identifier.

• Parts are the major components that the suppliers supply to Quality. Each part comes with a part number but that part number is unique only within a supplier. Thus, from Quality’s point of view, the unique identifier of a part is the combination of part number and supplier number.

• Each appliance that Quality manufactures is given an appliance number that is unique across all of the types of appliances that Quality makes.

• Buyers are major department stores, home improvement chains, and wholesalers. Buyer numbers are unique.

• An appliance may be inspected by several inspectors. There is clearly a many-to-many relationship between appliances and inspectors.

• There are one-to-many relationships between suppliers and parts (Supplier Number is a foreign key in the PART table), parts and appliances

(Appliance Number is a foreign key in the PART table), and appliances and buyers (Buyer Number is a foreign key in the APPLIANCE table).

Analyze each of the following situations and, using the physical database design techniques discussed in this chapter, state how you would modify the logical design shown to improve performance or otherwise accommodate it.

a. The Appliance Type attribute in the APPLIANCE table indicates whether an appliance is a refrigerator, washing machine, etc. Refrigerator records are accessed much more frequently than those for the other appliance types and there are strict response time requirements for accessing them.

b. There is a frequent and very hig- priority need to quickly retrieve detailed data about an appliance together with detailed data about who bought it.

c. Because of the large number of people trying to access the PART table and the fast response time needed, the PART table has become a bottleneck and the required response time is not being achieved.

d. Assume that the Buyer Name attribute in the BUYER table is unique. There is a high-priority need to quickly retrieve the following data about appliances: appliance number, appliance type, date of manufacture, and buyer name.

e. In the APPLIANCE table, there is a much more frequent need with strict response time requirements to access the price data (of course together with the appliance number) than to access the rest of the data in the table.

Transcribed Image Text:

SUPPLIER Table Supplier Supplier Number Name City Country Telephone PART Table Part Number Supplier Number Part Type Cost Appliance Number