- 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
A | B | C |
---|---|---|
1 | x | a |
2 | y | c |
2 | y | b |
2 | z | c |
T2
B | C | D |
---|---|---|
1 | x | c |
2 | y | c |
3 | x | a |
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.
-
πA,B(T1)
-
T1 × πA(T1)
-
T1 ⨝T1.B=T2.C T2
-
T1 − (T1 ∩ T2)
-
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.
-
πstoreid, s_name(σemployee_number<=100 or city = "New York"(Store))
-
πs_name(((σg_name = "pencil"Goods) ⨝ Supply) ⨝ Store)
-
πs_name, city((Supply/πg_id(σstoreid='0808'(Supply)))⨝ Store)