Using the Classic Models database (for the UML model) and Classic Models relation scheme (for the relation scheme model) write the relational algebra statement for the following queries:

1. List the names of all Customers that are in the same state as one of our Offices.

2. List the names of all Customers who have ordered Products where the vendor is Classic Metal Creations.

3. List the names of all Customers whose Order was shipped within three days of being ordered. Assume that you can subtract one date from another to get the number of days between two dates in Relational Algebra.

4. List the names of all Customers, their service rep and the Office that the service rep for that customer works in.

5. List the Employee first and last name, and their Customers name even if the Employee is not working with a Customer.

6. List all the possible statuses for an order.

7. List the Order Number for all Orders in the On Hold status where the quantity of a product ordered is greater than the quantity of that product on hand.

8. List the Employee LastName and FirstName that work in Japan.

9. List the productLine, the ProductName and the quantityOrdered for all products ordered during the month of July. You will need a sigma that checks for month(orderDate) = July.

10. List the customerName, the paymentDate, and the amount on all payments that exceeded $1000.

11. List the productLine for all products ordered by customers from the State of Louisiana.

UML Diagram

Relation Scheme

The MySQL sample database schema consists of the following tables: Customers: stores customer's data. Products: stores a list of scale model cars. ProductLines: stores a list of product line categories. Orders: stores sales orders placed by customers. Order Details: stores sales order line items for each sales order. Payments: stores payments made by customers based on their accounts. Employees: stores all employee information as well as the organization structure such as who reports to whom. Offices: stores sales office data. Order Details -quantityOrdered -priceEach -orderline Number Showcase> ProductLines -productLine -textDescription -htmlDescription -image Products -productCode -productName -productScale -productVendor -productDescription -quantityInStock -buyPrice -MSRP 1..1 contains -is supervised by> Employees -lastName -firstName -extension -email -reportsTo:Employee -jobTitle Orders -order Date -requiredDate -shippedDate -status -comments 0..1 Place> 1..* -supervises> Service> 0..1 Customers -customerName -contactLastName -contactFirstName -phone -addressLine1 -addressLine2 -city -state -postalCode -country -creditLimit Remit> Payments -checkNumber -paymentDate -amount 1. * Offices -city -phone -addressLine1 -addressLine2 -state -country postalCode |-territory 1..1 ProductLines -productLine -textDescription -htmlDescription -image Products -productCode -productName -productScale -productVendor -productDescription -quantityInStock -buyPrice -MSRP 1..1 contains -is supervised by> Employees -lastName -firstName -extension -email -reportsTo:Employee -jobTitle Orders -order Date -requiredDate -shippedDate -status -comments 0..1 Place> 1..* -supervises> Service> 0..1 Customers -customerName -contactLastName -contactFirstName -phone -addressLine1 -addressLine2 -city -state -postalCode -country -creditLimit Remit> Payments -checkNumber -paymentDate -amount 1. * Offices -city -phone -addressLine1 -addressLine2 -state -country postalCode |-territory 1..1