Question

5. [Extended Relational Algebra] (30) Consider the following schema:

Books(bid, title, year)

Students(sid, sname, age, major)

Authors(aname, address)

borrows(bid, sid, data)

writtenBy(bid, aname),

content(bid, keyword)

Give natural language description for the following relational algebra.

-Q1: σmajor = ’CS’(Students)

-Q2: πsname (σage>35(Students)) – πsname(σMajor=’CS’(Students))

-Q3: πsname(σ Students.sid=borrows.sid(σmajor=’CS’(Students) × borrows))

-Q4: πsname(Students) − πS1.sname(σS1.Age>S2.Age(ρ(S1, Students)×ρ(S2, Students)))

-Q5: Books ⋈ (πbId(σKeyword=’database’(content )) ∩ πbId(σKeyword=’programming’(content)))

Answer 1

Q1)answer) List of Student details where major='CS'
Q2)answer)List of snames from student where age>35 and no student have major='cs'
Q3)answer)List of snames where major=cs from student who borrows book
Q4)answer)List of snames whose age is minimum
sample example:
CREATE TABLE Persons (
age int,
sname varchar(255),
  
);
INSERT INTO Persons (age,sname)
VALUES (12,'k');
INSERT INTO Persons (age,sname)
VALUES (40,'a');
INSERT INTO Persons (age,sname)
VALUES (30,'v');INSERT INTO Persons (age,sname)
VALUES (20,'y');
INSERT INTO Persons (age,sname)
VALUES (12,'a');
select s1.sname from Persons s1,Persons s2 where s1.age>s2.age ; this is eqaul to πS1.sname(σS1.Age>S2.Age(ρ(S1, Students)×ρ(S2, Students)))  

this return all snames except minimum age sname

πsname(Students) − πS1.sname(σS1.Age>S2.Age(ρ(S1, Students)×ρ(S2, Students)))

here we are subtracting πS1.sname(σS1.Age>S2.Age(ρ(S1, Students)×ρ(S2, Students))) all from all snames

so we get minimum sname.

Q5)answer)all books having both keywords 'database' and 'programming'

πbId(σKeyword=’database’(content )) ∩ πbId(σKeyword=’programming’(content))) here we are selecting all bid's who have both keywords 'database' and 'programming'