• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 4/19/21

Test your knowledge on relational algebra operations

Log in or subscribe for free to enjoy all this course has to offer!

Evaluated skills

  • Manipulate data using relational algebra operations
  • Question 1

    You are the administrator of your company’s database. This database contains the relation "employees," which lists everyone who works for the company. The first few rows of this table are as follows:
     
    last_name first_name work_phone office_number identifier department
    Dawoud Malik 019829 27 2893 human resources
    Ericson John 019499   2983 marketing
    Stevens Aisha 019283   1829 marketing
    Costanza George null 51 2993 maintenance
    [...] [...] [...] [...] [...] [...]

    You receive a call from the Human Resources Department. They tell you, “Our software isn’t working. I need a list with first and last names of all the employees who work in the Marketing Department as soon as possible." What operations will you execute on the employee relation in order to generate this list?

    • [Projection (employee, department = "marketing")] then

      [Restriction (employee, last name, first name)]

    • [Restriction (employee, last name, first name)], then

      [Projection (employee, department = "marketing")]

    • [Restriction (employee, department = "marketing")], then

      [Projection (employee, last name, first name)]

    • [Projection (employee, last name, first name)], then

      [Restriction (employee, department = "marketing")]

  • Question 2

    New employees have been hired and began working this very morning. Since their software program is still not working, the Human Resources Department sends you a spreadsheet file containing information about the new employees. You import this file into a temporary table called "new_employee_temp" that is of the same schema as the “employees” table. How can you make sure that the file they sent you does not contain employees who are already present in the database?

    • Execute each of the four groups of operations proposed below.
    • Find the answer to this question: "How can you make sure that the file they sent you does not contain employees who are already present in the database?"

     Which three of the four answers below will answer this question?

    Careful, there are several correct answers.
    • Test whether  employee union new_employee_temp  contains at least one row.

    • Test whether  employee intersection new_employee_temp  contains no rows.

    • Test whether  employee difference (employee difference new_employee_temp)  contains no rows.

    • Test whether  new_employee_temp difference (new_employee_temp difference employee)  contains no rows.

  • Question 3

    You also have access to information about employee offices in the “office” table, whose first few rows are as follows:

    id computer nb_monitors type_screen chair_type plant floor
    13 False 0 cathode ray tube old chair True attic
    01 True 3 LCD executive chair True 2
    03 True 2 LCD executive chair True 1
    12 False 0 LCD armchair True 1
    51 True 1 cathode ray tube stool False basement

    The manager says, “It’s been a while since we heard from our intern, George Costanza. Can you tell me what floor he works on?"

    Execute each of the groups of operations proposed below. One of them will give you the answer to your manager’s question. Which one?

    • [Restriction(employee, last_name = "Costanza" and first_name = "George") Intersection office]

    • [Restriction(Join(employee, office, employee.office_number = office.id), last_name= "Costanza" and first_name = "George")]

    • [office Intersection Restriction(employee, last_name= "Costanza" and first_name = "George" and employee.office_number = office.id)]