Questions

Filter

Subject: DBMS

Topic: Relational Algebra

Consider the relation:

student(sid, sname, address, gender, age)

 

Write relational algebra(RA) for the following:

  1. To display name of student whose age is greater than 15.
  2. To remove the record of the student who are from Kathmandu.
  3. To update address of student to Kathmandu whose sid is 'S1101'.
View Answer

Use the following schema to write relational algebra statements for the questions that follow. The primary keys are underlined in the schema:

STUDENT(Ssn, Name, Major, DOB)
COURSE(Cnum, Cname, Dept)
ENROLL(Ssn, Cnum, Semester, Grade)
BOOK_ADOPTION(Cnum, Semester, ISBN)
TEXTBOOK(ISBN, Title, Publisher, Author)

 

Specify the following queries in relational algebra:

  1. List the course number taken by all students named 'John Smith' in winter 1999 (i.e., Semester = 'W99').
  2. Produce a list of textbooks (include Course#, ISBN, Title) for courses offered by the Computer Science Department (i.e., Dept = 'CS').
  3. List any department that has its adopted books published by 'BC Publishing'.
  4. Find the names of students who have not been enrolled in any course.
  5. Find all the textbooks that were authored by Mike Wilkins.
View Answer

Consider the following relational database:

  • Employee(empid, gender, name, dob, salary, address, deptno, start_date)
  • Department(dpetno, name)
  • Students(std_id, gender, name, dob, projectno, deptno)
  • Project(projectno, name, location)


Write Relational algebra expression for the following:

  1. Find employee name and address who works in "xyz company".
  2. Delete all employees whose salary is greater than Rs. 1,00,000.
  3. Update department name to "SitaGroup" where student "Sita" is involved.
  4. Find the average salary of all employees.
  5. Insert student record named "Gopal Shrestha" for department "Ram Group".
View Answer

Consider the following relational database schema consisting of the four relation schemas.

  • passenger (pid, pname, pgender, pcity)
  • agency (aid, aname, acity)
  • bus (bid, bdate, time, src, dest)
  • booking (pid, aid, bid, bdate)

 

Answer the following question using relational algebra queries:

  1. Give the details of all buses from Dhangadhi to Kathmandu.
  2. Find the name of passengers who booked at least one bus.
  3. Find the bus number for the passenger with pid p04 for bus to butwal before 20/05/2021.
  4. Find the name of passenger who has not booked any bus.
  5. Find the details of all male passengers associated with Ugratara Agency.
View Answer

Consider the relational database:

  • Students (Roll, SName, SAddress, SContact, SFee)
  • Teachers (TID, TName, TSalary, TAddress)
  • Teaches (Roll, TID)

 

Write the relational algebra for the following:

  1. Find the Roll of all students who are taught by teacher "Raman".
  2. Delete all students who are from Biratnagar.
  3. Increase the fee of students who are from Kathmandu by 2%.
  4. Find the name of teacher who lives in Kathmandu and get salary greater than Rs. 100000.
  5. Change the name of student to Sushila whose roll number is 10.
View Answer

Consider the following database, where primary keys are underlined.

  • Supplier(supplier_id, supplier_name, city)
  • Supplies(supplier_id, part_id, quantity)
  • Parts(part_id, part_name, color, weight)

 

Write relation algebra and SQL expressions for each of the following queries.

  1. Find the name of all suppliers located in the city "Kathmandu" that supplies part 'P01'.
  2. Find the name of all parts supplied by "RD Traders".
  3. Find the name of all parts that are supplied in quantity greater than 300.
  4. Find the number of parts supplied by "S02".
  5. Find the number of parts supplied by each supplier.
View Answer

Consider the following schema of the relational database.

  • Publisher(pid, name, location)
  • Book(bid, title, author, page, price)
  • Publish(bid, pid, publish_date)

 

Write Relational Algebra expressions for the following:

  1. Find title of book published by Addison_wisely.
  2. Update the author name to "MNP" of book "introduction database".
  3. Find the publish date of book "Database Management".
View Answer

Consider the following schema of the relational database.

  • Publisher(pid, name, location)
  • Book(bid, title, author, page, price)
  • Publish(bid, pid, publish_date)

 

Write Relational Algebra expressions for the following:

  1. Find title of book published by Addison_wisely.
  2. Update the author name to "MNP" of book "Introduction to Programming".
  3. Find the publish date of book "Software Engineering".
  4. Delete all books published by "Mc_graw_hill".
  5. Insert new record of book named "Database" published by "Pearson" in "2010-12-11" and written by "Patterson".
View Answer

Consider the relational database:

  • Account(acc_number, branch_name, balance)
  • Depositor(cust_name, acc_number)
  • Branch(branch_name, city)


Write the Relational Algebra for the following:

  1. Find the name of all depositors who have opened at 'XYZ' branch.
  2. Delete all accounts at branches located in Kathmandu.
  3. Insert information in the database specifying that Jhon has Rs 13,000 in account 006000134T at the Lalitpur branch.
  4. Increase the interest by 2% to the account with a balance over Rs 10,000.
  5. Change the name of the customer to Sushila whose account number is 005000142T.
View Answer