Question

Great Food Restaurants (GFR), is a national chain of restaurants with locations in all 50 states. The GFR relational database consists of nine tables as shown below. Note the following information about GFR and its database:

• Each restaurant has a unique restaurant number.
• Restaurant size is measured in square feet. Restaurant capacity is the number of patrons the restaurant can seat at one time.
• Suppliers supply food to GFR restaurants. Supplier name is unique in the SUPPLIER table.
• GFR has an aggressive marketing campaign in which it tracks each customer as they patronize GFR restaurants. Customer number is unique across the country.
• In the VISIT table, the Bill is the amount of money the customer spent in a particular GFR restaurant on a particular day. Assume that a customer can go to a particular restaurant only once in a given day.
• Employee number is unique across the GFR chain. In terms of employment, GFR is interested in keeping track only of the GFR restaurant that an employee currently works in.
• State names and region names are unique.
• Inspector numbers are only unique within a state.
• Assume that all states score restaurant inspections on the same 1-100 scale.

RestNum

Address

City

State

Size

Capacity

AnnualSales

RESTAURANT

SuppName

City

State

Phone

YearFounded

SUPPLIER

RestNum

SuppName

Food Type

Date

Cost

FOODDELIVERY

CustNum

Name

CellPhone

City

State

CUSTOMER

RestNum

CustNum

Date

Bill

VISIT

EmpNum

EmpName

YearHired

JobTitle

Salary

RestNum

EMPLOYEE

State

Region

InspectionOfficePhone

InspectionCommissionerName

STATE

State

InspectorNum

InspectorName

InspectorEmail

INSPECTOR

RestNum

State

InspectorNum

Date

Score

INSPECTION

There are 6,000 records in the RESTAURANT table.

There are 200 records in the SUPPLIER table.

There are 1.5 million records in the FOODDELIVERY table.

There are 450,000 records in the CUSTOMER table.

There are 15 million records in the VISIT table.

There are 500,000 records in the            EMPLOYEE table.

There are 50 records in the STATE table.

There are 300 records in the INSPECTOR table.

There are 98,000 records in the INSPECTION table.

Questions

1. What would you do to improve the performance of queries to find a customer by name or would you do nothing? Why?

2. What would you do to improve the performance of queries to find a supplier by phone number or would you do nothing? Why?

3. What would you do to improve the performance of queries requiring a list of the complete records of every GFR restaurant in Tennessee together with a list of their employees, including employee number, name, and salary or would you do nothing? Why?

4. What would you do to improve the performance of a query that seeks the salary of the highest paid employee of any GFR restaurant in Tennessee or would you do nothing? Why?

Answer 1

1. What would you do to improve the performance of queries to find a customer by name or would you do nothing? Why?

Answer: Finding a customer by name could be a tricky task as the resturant which is spread in alomst 50 states can have the record of the multiple customer with the same name. Hence, to improve the performance of queries, we can further ask for more unique and specific information like 'CustNum' in CUSTOMER table. And if the customer somehow unable to provide correct information, we can go ahead and make use of 'name', 'email' and 'cellphone' in CUSTOMER table which will narrow down the result.

2. What would you do to improve the performance of queries to find a supplier by phone number or would you do nothing? Why?

Answer: Just like we narrow downed the result by providing further information, similary here, with the help of SUPPLIER table, we will provide 'city' and 'state' of the supplier we want to find. Futhermore, if we have more specific information like 'SuppName' which is unique in the SUPPLIER table, we can simply use it go get the perfect result.

3. What would you do to improve the performance of queries requiring a list of the complete records of every GFR restaurant in Tennessee together with a list of their employees, including employee number, name, and salary or would you do nothing? Why?

Answer: Here, we will use the concept of 'LEFT JOIN' on table RESTAURANT and EMPLOYEE and the key connceting both the table is 'RestNum'. To do that we will break down the problem statement like this-

Columns to be selected: All from RESTAURANT table and employee number, name, and salary from EMPLOYEE table. i.e.

select restaurant.* , employee.empnum , employee.empname , employee.salary

Left Join RESTAURANT ON EMPLOYEE: from RESTAURANT LEFT JOIN EMPLOYEE

Join Condition: It will help in joining the tables. The conditon has to be evaluated to be TRUE to combine the tables. i.e. ON restaurant.RestNum=employee.RestNum

Additional condition: We want the information of restaurants in only Tennesse. hence, we need to provide another condition which is as follows WHERE state= 'Tennessee'

Now, combine the query-

SELECT restaurant.* , employee.empnum , employee.empname , employee.salary

FROM RESTAURANT LEFT JOIN EMPLOYEE

ON restaurant.RestNum=employee.RestNum

WHERE state= 'Tennessee';

4. What would you do to improve the performance of a query that seeks the salary of the highest paid employee of any GFR restaurant in Tennessee or would you do nothing? Why?

Answer: To obtain the salary of th heighest paid employee of ANY GFR restaurant in Tennessee we will use the EMPLOYEE and RESTUARANT tables.

We could have used only EMPLOYEE table to get the heightest salary but the condition which is given is that it has to be in Tennessee only. And we dont have states specified in the EMPLOYEE table. Hence, we are going to take help from the RESTUARANT table and the key that connectes both the tables is 'RestNum'.

Columns to be selected: Employee name, number and salary from EMPLOYEE table

select employee.empnum , employee.empname , employee.salary

Left Join EMPLOYEE ON RESTAURANT: from EMPLOYEE LEFT JOIN RESTAURANT

Join Condition: It will help in joining the tables. The conditon has to be evaluated to be TRUE to combine the tables. i.e. ON restaurant.RestNum=employee.RestNum

Condition: to find highest salary we will arrange the salary in descending order and pick the result topmost salary. To obtain that, we can write-

ORDER BY salary desc

LIMIT 1

Additional condition: We want the information of restaurants in Tennesse only. hence, we need to provide the condition which is as follows WHERE state= 'Tennessee'

Hence, the query is-

select employee.empnum , employee.empname , employee.salary

from EMPLOYEE LEFT JOIN RESTAURANT

ON restaurant.RestNum=employee.RestNum

WHERE state= 'Tennessee'

ORDER BY salary desc

LIMIT 1;