• Assigned: Feb 21
• Due: Mar 5 at 10:00AM
• Worth 3.75% of your grade

Solution released!

Use this template

(2 points each, 6 points total)

Consider the simplified Employee Stock Ownership Database below (primary keys are in bold):

• Person(ssn, companyid, salary, managerid)

This is an employee table. companyid is a foreign key which points to Company. We assume one employee can only work at one company. managerid is a foreign key which points to another Person record .

• Company(companyid, companyname, location)

• Holding(ssn, companyid, sharenum)

Holding table describes an employee(ssn) owns sharenum stocks of companyid.

Construct relational algebra for the following queries:

• Q1: Find the ssn of the persons who work at Google(companyid = 601) and hold more than 500 sharenum of stock from Facebook(companyid = 700).

• Q2: Find the ssn of the persons who own all the different kinds of stocks his/her manager owns. (Note: Each companyid represents a different kind of stock, we do not care about the sharenum of stocks).

• Q3: Find the ssn of the persons who own at least three different kinds of stocks.

(2 point each, 10 points total)

T1

T2

Write the result table for the relational algebra expressions in a similar with T1 and T2:

Note: Here, we assume the attributes whose values are all numbers are the same type: integers and all the letters are the same type: char. If the result table is empty, write the schema of the table.

1. π_A,B(T1)

2. T1 × π_A(T1)

3. T1 ⨝_T1.B=T2.C T2

4. T1 − (T1 ∩ T2)

5. T1 ⨝_T1.A<T2.B (σ_B<=2(T2))

(2 points each, 6 points total)

Here are three relationship, (primary keys are in bold):

• Store(storeid, s_name, employee_number, city)

• Goods(g_id, g_name, price)

• Supply(storeid, g_id)

First, describe the meaning of following relational algebra expressions in one or two sentences. Second, translate the following relational algebra expressions in SQL. Make sure your SQL can be executed.

1. π_{storeid, s_name}(σ_{employee_number<=100 or city = "New York"}(Store))

2. π_{s_name}(((σ_{g_name = "pencil"}Goods) ⨝ Supply) ⨝ Store)

3. π_{s_name, city}((Supply/π_{g_id}(σ_{storeid='0808'}(Supply)))⨝ Store)