Databases Test 3

To retrieve the name and address of all students who study in the Science department, first select the Science department using 'select' operator and then join the result with the 'Student' table on the condition where Snumber = Sno.

Finally, a project operation is applied to get name and address of the students.

Therefore, the expression must be \(∏_{name, address}(σ_{Dname ='Science'}(Department ⋈_{Snumber = Sno} (Student))\).

A = 10 and C = 7

Output:

Therefore, sum = 10 + 7 + 13 + 2 = 32

• Relational algebra is a procedural query language, which takes instances of relations as input and yields instances of relations as output. It uses operators (selection, projection, union etc) to perform queries.
• Relational calculus consists of two calculi, the tuple relational calculus and the domain relational calculus, that are part of the relational model for databases and provide a declarative way to specify database queries
• Relational algebra, Domain relational calculus restricted to safe expression and Tuple relational calculus restricted to safe expression have the same expressive power.

Confusion Points:

Relation algebra’s expressive power is not equal to Domain relational calculus or Tuple relational calculus unless they are restricted to safe operation.

Example

a is a key

P

m is a key

Q

\(P~⨝~Q\equiv {{\sigma }_{P.a=Q.a}}\left(P\times Q \right)\)

Maximum tuple possible = |Q| = 2

From above example:

Since |P| = 70 and |Q| = 42

 ∴ Maximum tuple possible = 42

Let T₁= \(Π_{Demo.salary} (σ_{Demo.salary<d.salary} (Demo×ρ_d (Demo)))\) gives those salaries in the demo relation for which a larger salary appears somewhere in the demo relation (renamed as d). The result contains all salaries except the largest one. 

The query \(Π_{salary} (Demo)-Π_{Demo.salary} (σ_{Demo.salary<d.salary} (Demo×ρ_d (Demo)))\) then performs the set difference and returns the largest salary.

Therefore, the row returned will be:

Important Points:

95000 is largest hence it not present in T₁

if salary of original table is subtracted with T₁ then output is

Therefore, the row returned will be:

Let us take example to prove:

R:

S:

A U B

R – S

S – R

R ∩ S ≡ R – (R – S) ≡ S – (S – R) ≡ ((R U S – (S – R)) – (R – S)

R U S – ((S – R) – (R – S))

Hence R U S – ((S – R) – (R – S)) is not equivalent to R ∩ S 

Query: D1 → \({{\rm{\Pi }}_{Test \;ID,\; TestSeries\;Name}}\left( {{\sigma _{SME\;age > 23 \;\wedge\; SME\; age < 26}}\left( {SME} \right)} \right)\)

Output: 

The number of rows: 3

Query: D2 → \({{\rm{\Pi }}_{Test\;ID,TestSeries\;Name}}\left( {{\sigma _{Ratings > 9.1 \wedge Ratings < 9.4}}\left( {SME} \right)} \right)\)

Output:

The number of rows: 4

Query: D → D2 - D1

Output:

D contains 3 rows.

Sailors:

Reserves:

Boats:

 Given query: π_sname((σcolor= ‘red’Boats) ⨝ Reserves ⨝ Sailors) 

This query first find the boats having red color than matches B.Bid with R.Sid where it finds equal than matches R.Sid with S.Sid  , at that point finds the name of sailors with same sid.

Here it results in sailor SA, SC, and SD.

This query matches with:

SELECT S.sname

FROM Sailors S, Reserves R, Boats B

WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’

The above query finds the names of all instructors in the ComputerScience department together with the course titles of all the courses that the instructors teach.

The result of the given query is:

Therefore, the number of tuples returned = 4

Query: TEMP ← ΠDepartment_Name(σ Employee_Name = ''ED" (Employee_Record))

Output:

Query: RESULT ← Employee_Record ÷ TEMP
Output

So, the number of rows returned by RESULT is 5.