The DataBase Course

Exercise No. 2

Relational Algebra

Due on November 25th by midnight

 

1. In the given database, suppliers supply certain parts for certain projects. The information about who supplies what to which projects is found in the shipments table. The tables are:

Suppliers(s#,sname,status,city)
Parts(p#,pname,color,weight,city)
Projects(j#,jname,city)
Shipments(s#,p#,j#)

The s#,p#,j#,s# attributes are key attributes. Express the following queries in relational algebra:

• Get all pairs of part numbers that are supplied by the same supplier. Do not include both pairs (a,b) and (b,a) in your answer.
• Which part numbers are heavier than p1?
• Which projects (give project numbers) are made entirely out of red parts?
• For which colors are all the parts of that color supplied by s5?
• What are the names of the projects which are located in a city where no supplier is located?
• Get supplier numbers for suppliers who supply the same part to all projects.

The queries should be clearly explained!

 

2. The formal definition of the Selection (delta) operator only allows us to use basic conditions for filtering relations: Only a1=a2 or a1>a2 conditions are allowed. Prove that using only these conditions, and the other relational algebra operators, we can represent these conditions:

a1 < a2
a1 <= a2
a1 >= a2
not (condition1)
(condition1) and (condition2)
(condition1) or (condition2)

where a1,a2 are attributes and condition1,condition2 are any (possibly complex) conditions.

 

 

3. For each of the following operators - union, cartesian product, substraction, natural (eqvi) join, semi join and division - decide whether:

• Is it commotative: R1 x R2 = R2 x R1
• Is it associate: (R1 x R2) x R3 = R1 x (R2 x R3)
• Does it have a neutral relation, that is a relation Ro such that for every relation R,
  R x Ro = R
• Is there a funtion F whose domain and range are relations, such that for every R,
  R x F(R) = R

Explain your answers shortly. Formal proofs are not required.