CS Free MCQ Bank

[ Option A ]

A Cartesian Product between two relations (tables) pairs every row of the first table with every row of the second table. So the total number of tuples is:
Total Tuples = (Number of tuples in X) × (Number of tuples in Y)
Here, Number of tuples in X = 4 and Number of tuples in Y = 3. This means every row of X will pair with every row of Y, producing 4×3 = 12 total tuples.

[ Option C ]

In relational algebra, the set-difference operation (−) allows us to retrieve tuples that are present in one relation but not in another. It is written as R−S, returns all tuples that are in R but not in S.
This operation requires both relations to be Union-Compatible, meaning they must have the same set of attributes with compatible domains.

[ Option B ]

In relational algebra and SQL, the Union (U) operation combines tuples from two relations and removes duplicates. However, for a union operation to be valid, both relations must be Union-Compatible. Two relations are said to be union-compatible if they satisfy the following conditions:

• Same number of attributes (columns).
• Same data types for corresponding attributes.
• Same order of attributes.

If these conditions are not met, the union operation cannot be performed, and the system produces a Syntax Error.

• Remember, attribute names can differ, as long as their positions and data types match.

[ Option B ]

To find which operations give the same output, we first identify the common tuples in both relations. The only matching rows between Student and Faculty are Cyber (300) and CIVIL (2000). This means the intersection Student ∩ Faculty contains exactly these two tuples.

Now, expression (B) calculates (Student ∩ Faculty) ∪ (Faculty ∩ Student). Since both intersections are the same, their union also gives the same two tuples: Cyber (300) and CIVIL (2000). Expression (D) directly performs Student ∩ Faculty, which naturally results in the same pair of tuples. Hence, B and D produce identical outputs.

[ Option D ]

A Join is a relational database operation that combines rows from two tables based on a related attribute. It helps in retrieving connected information stored across multiple tables. 
Among different types of joins, the Natural Join is a special type that automatically matches rows based on all columns with the same name and then merges them, removing duplicate columns in the output.

In this question, the natural join between R1 and R2 is performed on the common attributes Q and R. First, we identify matching pairs. In R1, the pairs (q, r) appear twice, and in R2, the same pair appears in three different rows. Each matching pair from R1 combines with all matching rows in R2, giving 2×3=6 tuples. 
Additionally, the pair (e, f) in R1 matches one row in R2, giving 1 more tuple. The other pair (i, j) has no match, so it contributes zero. Adding them together, the Natural Join produces 6+1+0 = 7 tuples.
 

[ Option D ]

In Relational Query Optimization, heuristics are used to reduce the size of intermediate results and improve efficiency. Standard heuristics include performing selection operations as early as possible, applying projections early to reduce columns, and avoiding Cartesian products. 

Applying Projections Late is contrary to optimization principles because it increases the number of attributes in intermediate results, leading to higher computational cost.

[ Option C ]

The operation that requires two relations as input and needs these two relations to have a common attribute to form a new relation is the Natural Join operation.
Natural join automatically joins two relations based on all attributes with the same name and compatible data types in both relations. It matches and combines rows where the common attribute values are equal.

Relation Student:

Relation Marks:

Natural Join (Student ⋈ Marks) : The join matches rows where RollNo is common.

[ Option B ]

In query optimization, expression equivalence rules help the DBMS simplify queries without changing their final result. Some operations like union (∪) and intersection (∩) are both commutative and associative.

• Commutative means their order does not affect the result. For example, A ∪ B = B ∪ A and A ∩ B = B ∩ A.
• Associative means their grouping does not matter. For example, (A ∪ B) ∪ C=A ∪ (B ∪ C) and (A ∩ B) ∩ C = A ∩ (B ∩ C).

The Projection (π) operation distributes over union, meaning π(A ∪ B) = π(A) ∪ π(B).
The Set Difference (−) is not commutative because A − B ≠ B − A.

[ Option C ]

In relational algebra, every operation such as SELECT, PROJECT, UNION, JOIN, etc. takes one or more relations as input and produces a new relation as output. This property is known as closure, which allows operations to be nested.

• Row represents a single tuple.
• Attribute represents a column.

[ Option C ]

Relational algebra is a set of basic operations used to manipulate data in relational databases. These includes:

• The selection (σ), which filters rows based on conditions.
• The  projection (π), which chooses specific columns.
• The union (U), combining rows from two relations, removing duplicates.
• The set difference (−), finding rows present in one relation but not in another.
• The Cartesian product (×), pairing rows from two relations.
• The rename (ρ), which changes relation or column names.

These operations take relations as input and produce new relations as output, forming the foundation for querying data efficiently and systematically.

[ Option A ]

[ Option C ]

Unary operators operate on a single relation (table) and produce a relation as output.

• Select (σ): Retrieves tuples (rows) from a relation that satisfy a specified condition. It filters rows based on predicates.
• Project (π): Extracts specified columns (attributes) from a relation, removing duplicates by default.
• Rename (ρ): Renames a relation or its attributes. Useful for clarity or avoiding conflicts in operations.

Binary operators work on two relations (tables) and produce a new relation as output.

• Union (∪): Combines tuples from two relations, removing duplicates. Both relations must have the same schema.
• Set Difference (-): Returns tuples present in the first relation but not in the second.
• Cartesian Product (×): Combines tuples from two relations in every possible pair, creating a relation with all attributes of both.
• Join (⋈): Combines related tuples from two relations based on a condition, essentially a filtered cartesian product.

[ Option D ]

Relational algebra provides a set of basic operations used to manipulate relations:

• The select (σ) is used to choose rows that satisfy a condition.
• The set difference (−) finds tuples present in one relation but not in another.
• The rename (ρ) is used to change the name of a relation or its attributes.

The grouping commonly associated with SQL (GROUP BY) and aggregate functions.