Using the Classic Models database (for the UML model) and Classic Models relatio

Question

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 all Customers that are located in the same state as an Office

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

3. List all Customers whose Order was shipped within three days of being ordered

4. List all Customers, their service reps and the Offices that the service reps work in

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

6. List the Employee first and last names and Customer contacts first and last names

7. List all Orders where the quantity of a product orders is greater than the quantity of that product on hand

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

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. OrderDetails: 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 OrderDetails quantityOrdered priceEach orderLineNumber Products productCode productName ProductLines productLine textDescription htmlDescription image Showcase> productScale productVendor productDescription quantitylnStock buyPrice MSRP contains Orders rderDate requiredDate shippedDate Employees -is supervised by> lastName firstName extension email reportsTo:Employee jobTitle 0..1 Customers Place» 1.status omments customerName contactLastName contactFirstName phone addressLine1 addressLine2 -supervises> Service> 1..1 Payments checkNumber paymentDate 0..1 Remit> Offices 1amount ty phone addressLine1 addressLine2 state state postalCode ountry creditLimit 1..1 house ountry postalCode territory 1..1

Explanation / Answer

1. List all Customers that are located in the same state as an Office
select distinct c.* from customers c --select all the customer details
join employees e on e.employeeNumber=c.SalesRepEmployeeNumber --join customers with emoloyee to get the office code
join offices o on o.officeCode=e.officeCode--join the office code of all employees with office table
where o.officeCode=e.officeCode // to make sure we only get customers who are only belonging to the same state as office

2. List all Customers who have ordered Products where the vendor is “Classic Metal Creations”
select distinct c.* from customers c--select all the customer details
join Orders o on o.customerNumber=c.customerNumber--to get the products we have to go through by joining Orders table
join OrderDetails od on od.orderNumber=o.orderNumber--join Orders which has relation only with order details table
join Products p on p.productcode=od.productCode--Product details are fetched with product code
where p.productVendor='Classic Metal Creations'--the only condition specified is filtered here vendor is “Classic Metal Creations”

3. List all Customers whose Order was shipped within three days of being ordered
select *,datediff(day,orderdate,shippedDate) orderduration --difference of orderdate and shippeddate gives the shipment time
from (select distinct c.*,o.* from customers c--select all the customer details
join Orders o on o.customerNumber=c.customerNumber--to get the order details we have to go through by joining Orders table
) custorders
where orderduration<3--shipped within three days of being ordered

4. List all Customers, their service reps and the Offices that the service reps work in
select distinct c.* from customers c --select all the customer details
join employees e on e.employeeNumber=c.SalesRepEmployeeNumber --join customers with emoloyee to get the office code
join offices o on o.officeCode=e.officeCode--join the office code of all employees with office table
where o.officeCode=e.officeCode // to make sure we only get customers who are only belonging to the same state as office

5. List the Employee and their Customers even if the Employee is not working with a Customer
select distinct c.* from customers c --select all the customer details
left join employees e on e.employeeNumber=c.SalesRepEmployeeNumber
--by applying left join we would get all the customer information and employee details would be null for this case

6. List the Employee first and last names and Customer contacts first and last names
select distinct c.firstname,c.lastname,e.firstname,e.lastname --get frst name and lastnmaes of both employees and customers
from customers c --select all the customer details
join employees e on e.employeeNumber=c.SalesRepEmployeeNumber

7. List all Orders where the quantity of a product orders is greater than the quantity of that product on hand
select o.* from Orders o --to get the products we have to go through by joining Orders table
join OrderDetails od on od.orderNumber=o.orderNumber--join Orders which has relation only with order details table
join Products p on p.productcode=od.productCode--Product details are joined to get the product details
where p.quanittyInStock>od.quantityordered--to get the product quantity greater than order quantity

8. List the Employee LastName and FirstName that work in Japan
select LastName,FirstName --get lastname and firstname in employee table
from Employee e join offices o on o.officeCode=e.officeCode --join with offices which holds the country details for employees
where o.country='Japan'--filter the country that work in Japan

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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