Use Figure 6.17 above to answer the following questions: 5. In the General Hardw

ID: 3879637 • Letter: U

Question

Use Figure 6.17 above to answer the following questions:

5. In the General Hardware Corp. relational database of Figure 6.17:

a. How many foreign keys are there in each of the six relations?

b. List the foreign keys in each of the six relations.

6. Identify the relations that support many-to-many relationships, the primary keys of those relations, and any intersection data in the General Hardware Corp. database.

7. Consider the General Hardware Corp. relational database. Using the informal relational command language described in this lesson, write commands to:

a. List the product name and unit price of all of the products.

b. List the employee names and titles of all the employees of customer 2198.

c. Retrieve the record for office number 1284.

d. Retrieve the records for customers headquartered in Los Angeles.

e. Find the size of office number 1209.

f. Find the name of the salesperson assigned to office number 1209.

g. List the product name and quantity sold of each product sold by salesperson 361.

8. Consider the General Hardware Corp. relational database and the data stored in it, as shown in Figure 6.17. Find the answer to each of the following queries (written in the informal relational command language described in this lesson).

a. Select rows from the CUSTOMER EMPLOYEE relation in which Customer Number = 2198.

b. Select rows from the CUSTOMER EMPLOYEE relation in which Customer Number = 2198. Project Employee Number and Employee Name over that result.

c. Select rows from the PRODUCT relation in which Product Number = 21765.

d. Select rows from the PRODUCT relation in which Product Number = 21765. Project Unit Price over that result.

e. Join the SALESPERSON and CUSTOMER relations using the Salesperson Number attribute of each as the join fields. Select rows from that result in which Salesperson Name = Baker. Project Customer Name over that result.

f. Join the PRODUCT relation and the SALES relation using the Product Number attribute of each as the join fields. Select rows in which Product Name = Pliers. Project Salesperson Number and Quantity over that result.

(a) SALESPERSON relation (d) PRODUCT relation SolespersonSalesperson Commission Name Year ice Product Percentage Name Wrench Hammer Drill Baker 1995 2001 1998 2001 1284 1253 1209 1227 16386 19440 21765 24013 26722 186 Adams Dickens 15 17.50 26.25 b) CUSTOMER relation Customer Solesperson (e) SALES relation Number les Main St. Hardware Jane's Stores ABC Home Stores Acme Hardware Store Fred's Tool Stores XYZ Stores City Hardware Western Hardware Central Stores Um New York Chicago Los Angeles Los Angeles Uantity 0839 0933 1047 1525 1700 1826 2198 2267 186 19440 24013 26722 16386 19440 21765 24013 21765 26722 16386 21765 26722 tlanta Washington New York New York New York 186 186 186 186 170 688 1,745 2,529 1,962 186 C) CUSTOMER EMPLOYEE relation 361 3,729 Customer Emplovee Number Number 27498 30441 25270 30441 48285 33779 27470 30441 33779 35268 Name 361 2,738 Co-Owner Co-Owner VP Sales Sales Manager President Sales Manager President VP Sales VP Personnel Senior Accountant ) OFFICE relation Garcia 0933 0933 0933 1525 2198 2198 2198 2198 lelephone 901-555-4276 901-555-0364 901-555-7335 901-555-3108 ize Levy Morton Baker 1253 1227 1284 1209 120 120 Jones Garcia Continues)

Explanation / Answer

a and b

SALESPERSON ----- 1 foreign key , Office Number

CUSTOMER ----- 1 foreign key , Salesperson Number

CUSTOMER EMPLOYEE ----- 1 Foreign key, Customer Number

PRODUCT---- No foreign key

SALES----- 2 foreign keys, Salesperson Number,Product Number

OFFICE --- No foreign key

6. Relations with many to many relationships

SALES

1 SalespersonPerson Sales many ProductNumber and 1 ProductNumber is sold by many SalesPerson

So many to many relationship exists.

Composite Primary key ---- ( SalespersonNumber,ProductNumber)

SalespersonNumber 137 sold ProductNumber 19440,24013 and 26722 whereas ProductNumber 26722 is sold by SalespersonNumber 137,204 and 361.

a. Select rows from the PRODUCT relation . Project Product Name and Unit Price over that result.

Sql: Select ProductName,UnitPrice from Product;

b. Select rows from the CUSTOMER EMPLOYEE relation in which Customer Number = 2198. Project Employee Name and Title over that result.

Sql: Select EmployeeName,Title from CUSTOMEREMPLOYEE where CustomerNumber = 2198;

c. Select rows from the OFFICE relation in which Office Number = 1284.

Sql: Select * from OFFICE where OfficeNumber = 1284;

d. Select rows from the CUSTOMER relation in which HQ City = Los Angeles.

Sql: Select * from CUSTOMER where HQCity = 'Los Angeles';

e. Select rows from the OFFICE relation in which Office Number = 1209. Project Size(sq. ft.) over that result.

Sql: Select Size from OFFICE where OfficeNumber = 1209;

f. Select rows from the SALESPERSON relation in which Office Number = 1209. Project Salesperson Name over that result.

Sql : Select SalespersonName from SALESPERSON where OfficeNumber = 1209;

g. Join the SALESPERSON and SALES relations using the Salesperson Number attribute of each as the join fields. Select rows from that result in which Salesperson Number = 361. Project Product Name, Quantity over that result.

Select ProductName,Quantity from SALESPERSON inner join SALES on SALESPERSON.SalespersonNumber = SALES.SalespersonNumber where SALESPERSON.SalespersonNumber = 361;

Customer Number Employee Number Employee Name Title 2198 27470 Smith President 2198 30441 Jones VP Sales 2198 33779 Garcia VP Personnel 2198 35268 Kaplan Senoir Accountant

Navigate

Use Figure 5 to answer questions 5 through 10 concerning the effects of a price

Use Figure 7 to answer questions 7-9. For the counter program shown in Figure7,

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

Use Figure 6.17 above to answer the following questions: 5. In the General Hardw

Question

Explanation / Answer

Related Questions

Navigate