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

Expected duration
30–45 minutes
Deadline
None (no explicit submission is required)
Points
None

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 (solutions):

  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.