Relational Algebra

This assignment is ungraded and should not be submitted. The questions review key concepts from the reading for this module.

Help Policy

Authorized Resources

Any

Notes

You may jointly work on this assignment with classmates

Assignment

Answer the following questions from Fundamentals of Database Management Systems (Gillenson, 2011).

1. Describe the purpose and capabilities of (Question 5.12):

   1. The relational Select operator

      The select operator (\(\sigma\)) retrieves the tuple(s) that satisfy a predicate from a relation.

   2. The relational Project operator

      The project operator (\(\pi\)) retrieves the specified attributes from a relation.

   3. The relational Join operator

      A relational join integrates (i.e., combines) data from two relations.

      The Cartesian product (\(\times\)) returns all possible combinations of tuples from both relations. A natural join (\(\bowtie\)) combines tuples using common attributes where the values of those attributes are equivalent.

2. Consider the General Hardware Corp. relational database that follows.

   SALESPERSON

   Salesperson Number,* Salesperson Name, Commission Percentage, Year of Hire, Office Number

   CUSTOMER

   Customer Number,* Customer Name, Salesperson Number, HQ City

   CUSTOMER EMPLOYEE

   Customer Number,* Employee Number,* Employee Name, Title

   PRODUCT

   Product Number,* Product Name, Unit Price

   SALES

   Salesperson Number,* Product Number,* Quantity

   OFFICE

   Office Number,* Telephone, Size (sq. ft.)

   Using the informal relational command language described in this chapter and the formal relational algebra covered in class, write commands to (Exercise 5.5):

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

      Project the Product Name and Unit Price from the PRODUCT relation.

      \[\pi_{\text{Product Name, Unit Price}}(\text{PRODUCT})\]

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

      Select rows from the CUSTOMER EMPLOYEE relation where the Customer Number is 2198. Project the Employee Name and Title over that result.

      \[\pi_{\text{Employee Name, Title}}( \sigma_{\text{Customer Number} = 2198}(\text{CUSTOMER EMPLOYEE}) )\]

   3. Retrieve the record for office number 1284.

      Select the row from the OFFICE relation with Office Number 1284.

      \[\sigma_{\text{Office Number} = 1284}(\text{OFFICE})\]

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

      Select the rows from the CUSTOMER relation where the HQ City is Los Angeles.

      \[\sigma_{\text{HQ City = Los Angeles}}(\text{CUSTOMER})\]

   5. Find the size of office number 1209.

      Select the row from the OFFICE relation with Office Number 1209. Project the size from that result.

      \[\pi_{\text{Size}}( \sigma_{\text{Office Number} = 1209}(\text{OFFICE}) )\]

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

      Select the row(s) from the SALESPERSON relation where the Office Number is 1209. Project the Salesperson Name from that result.

      \[\pi_{\text{Salesperson Name}}( \sigma_{\text{Office Number} = 1209}(\text{SALESPERSON}) )\]

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

      Join the SALES and PRODUCT relations using the Product Number as the join fields. Select the row(s) where the Salesperson Number is 361 from that result. Project the Product Name and Quantity from that result.

      \[\pi_{\text{Product Name, Quantity}}( \sigma_{\text{Salesperson Number} = 361}( \text{SALES} \bowtie \text{PRODUCT} ) )\]

3. For each of the following, describe in words what the query is trying to accomplish. (Exercise 5.7)

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

      Retrieve the records for the employees of customer 2198.

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

      List the employee number and name of all employees of customer 2198.

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

      Retrieve the record of product number 21765.

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

      Find the price of product 21765.

   5. 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.

      List the names of (salesperson) Baker’s customers.

   6. 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.

      Find the number of pliers that each salesperson has sold.