Relational Algebra and SQL: Key Operations and Database Relationships

What is the theoretical foundation for SQL queries?

Relational Algebra

What does the SELECT operation do in relational algebra?

It returns rows from a relation.

What does the PROJECT operation do in relational algebra?

It returns columns from a relation.

What is the purpose of the JOIN operation in relational algebra?

It combines information from multiple tables.

What is the difference between UNION and INTERSECT in relational algebra?

UNION combines all rows from two tables excluding duplicates, while INTERSECT returns only the rows that appear in both tables.

What is required for two tables to perform a UNION operation?

The tables must have union-compatible columns.

What does the DIFFERENCE operation do in relational algebra?

It returns all rows in one table that do not appear in another table.

What is the Cartesian Product in relational algebra?

It pairs every row of one table with every row of another table.

What is a NATURAL JOIN?

It selects rows with common values in a common attribute.

What is the purpose of referential integrity in relational databases?

It ensures that foreign keys in a table correspond to primary keys in another table.

What does the DIVIDE operation do in relational algebra?

It retrieves information about one set of data associated with another set of data.

What is the relationship between students and dorm rooms in a relational schema?

It is a one-to-many relationship, where one dorm room can contain multiple students.

What is an example of a primary key in a student-dorm room relationship?

Student ID (Sno) can be a primary key.

What does it mean for columns to be union compatible?

Columns must have compatible data types to perform a UNION operation.

What is the significance of entity integrity in relational databases?

It requires that every relation has a primary key.

What is an INNER JOIN?

It returns only the matching rows from both tables.

What is a LEFT JOIN?

It returns all rows from the left table and the matched rows from the right table.

What is a RIGHT JOIN?

It returns all rows from the right table and the matched rows from the left table.

What is a LEFT OUTER JOIN?

It returns all rows from the left table and the matching rows from the right table, with NULLs for non-matching rows.