Test: Relational Algebra - 2 - Computer Science Engineering (CSE) MCQ

Key Points

• To achieve the desired output from the given tables Dance and Music, the operation to apply is the union operation, represented by "∪". This operation combines all the unique rows from both tables without duplicating any rows.
• The union operation (∪) takes all the unique entries from both tables and combines them into one table. The entries from the Dance table and those from the Music table are all included, but because 'Mahira' and 'Sanjay' are in both tables, they are only listed once in the result, as per the principles of a union operation which does not include duplicates.

Therefore, the correct option is: ∪

Key Points

• Table A X Table B: This is the Cartesian product operation, which combines each row in Table A with each row in Table B. It results in a table with many more rows than either Table A or Table B, depending on their row counts. This operation does not match the provided Output.
• Table A ∪ Table B: This is the union operation, which combines all unique rows from both Table A and Table B. Duplicates are typically removed in a union operation, and the result will include rows unique to both tables and rows common to both tables but listed only once. The Output table listed does not match a union of Table A and Table B, as it does not include all unique entries from both tables.
• Table A - Table B: This is the difference operation, which returns rows that are in Table A but not in Table B. It essentially removes rows from Table A that have exact matches in Table B based on the columns specified. The Output table matches this description, as it shows rows from Table A that are not found in Table B.
• Table A ∩ Table B: This is the intersection operation, which returns rows that are common to both Table A and Table B. It essentially finds rows with the exact match across all columns specified in both tables. The Output table does not match an intersection of Table A and Table B because it excludes rows that are actually common to both tables (e.g., the rows for "Mahira" and "Sanjay").

Based on this analysis, the operation applied to obtain the given output is: Table A - Table B

Concept:
To maintain materialized view in RDBMS, we use Trigger.

Triggers:
Triggers are SQL statements that are run automatically whenever there is a change in the database. Triggers are run in reaction to certain table events (INSERT, UPDATE, or DELETE). These triggers serve to maintain the data's integrity by updating the database's data in a regular manner.

Syntax:
create trigger Trigger_name
(before | after)
[insert | update | delete] 
on [table_name]  
[for each row]  
[trigger_body]
Pointer and Clone objects are not related to RDBMS and those useful for programming language.

Hence the correct answer is Trigger.

In relational algebra, selection operations can be combined, and the order of these operations does not affect the final result when the operations are based on different predicates combined with a logical AND. Here's a short explanation of each option presented in your question:

• σ_P(σ_Q(e)) = σ_Q(σ_P(e))
  Valid. This shows that the order of applying selection predicates does not matter when you're effectively filtering by both conditions (P AND Q). It's like saying "get me all items that are red (P) and round (Q)" is the same as saying "get me all items that are round (Q) and red (P)."
• σ_P(σ_Q(e)) = σ_P ∪ _Q​(e)
  Not Valid. This suggests combining predicates P and Q through selection and then implies a union operation, which doesn't make sense in this context. Selection operations imply an AND relationship, not a UNION, which would correspond to an OR relationship.
• σ_P(σ_P(e)) = σ_P V _Q​(e)
  Not Valid. Applying the same predicate P twice is redundant and does not involve predicate Q. Additionally, the expression hints at a logical OR (possibly intended by "V") or another operation not applicable here.

Concept:​
Natural Join (* or ⋈):
Natural Join (⋈) We can perform a Natural Join only if there is at least one common attribute that exists between two relations. In addition, the attributes must have the same name and domain. Natural join acts on those matching attributes where the values of attributes in both the relations are the same.
The given relations schemas are,
R(A, B, C, D) and S(D, E, F) Here common attribute is D, upon matching attributes where the values of attributes in both the relations are the same. After matching the two relations the new relation becomes, RS (A, B, C, D, E, F).

Degree of schema:
The degree is the total number of attributes/fields of a relation/table and cardinality is the total number of tuples/rows of relation/table.
The resultant schema R*S =RS (A, B, C, D, E, F) 
So the number of attributes = 6.
Hence the correct answer is 6.

The number of tuples in a relation is known as cardinality
Example:

The number of tuples in the above relation is 4
The cardinality of Employee relation is 4.

For option (1) -  {2, 3, 4} ∈ A and {2, 3} ∈ B implies that {4} ⊆ A - B.
If {2, 3, 4\} is an element of set A and {2, 3} is an element of set B, it means that all elements of {2, 3} are also in set A.
Therefore, if you subtract the elements of B from A (A - B), you are left with {4}, and since {4} is a subset of itself, the statement is true.

For option (2) - {2, 3, 4} ⊆ A implies that 2 ∈ A and {3, 4} ⊆ A
If the set {2, 3, 4} is a subset of A, it means that all its individual elements are in A.
Therefore, statement 2 is true.

For option (3) -  A ∩ B  {2, 3, 4} implies that {2, 3, 4} ⊆ A and {2, 3, 4} ⊆ B.
The intersection of sets A and B, denoted as (A ∩ B), contains elements that are common to both sets. It does not necessarily mean that all elements of {2, 3, 4} are individually present in both A and B.
This statement is false. 

For option (4) -  A - B ⊇ {3, 4} and {1, 2} ⊆ B implies that {1, 2, 3, 4} ⊆ A ∪ B
If the difference of  A and B (A - B) contains {3, 4} and {1, 2} is a subset of B, it means that all these elements are in either A or B or both.
Therefore, the union of sets A and B (A ∪ B) contains all these elements, and the statement is true.

Hence option (3) is true.

Concept:-

Relational algebra:- Relational algebra is a procedural query language, which takes input as an instance of relations, further uses different operators to perform queries, and yields instances of relations as output. Relational algebra is performed recursively on relation and intermediate results are also considered relation.

The operations of relational algebra are as follows:-

• Selection
• Projection
• Union
• Set difference
• Cross product

Additional Information

• Selection:- It is a unary operator. It is used to select a subset of tuples of the relations that satisfy the selection condition.
• Projection:- It is also a unary operator. It is used to select the attributes or columns of a relation.
• Union (U):- It returns a relation containing all tuples that appear in either both or two specified relations.
• Set difference ( - ):- It is used to return a relation containing all tuples that appear in the first and not in the second of two specified relations.
• Cross product (X):- The cross product is also known as cartesian product or cross join. It is denoted by ' x '. The relation on which we are performing need not be union compatible. This operation is used to combine tuples from two relations.

Full natural outer join of R₁ and R₂ is :

Hence option 3 is correct:

Concept:
Cross product:
Cross product is a way of combining two relation instances. The resulting relation has a schema that contains each of the attributes in both relations being combined.

A cross product is represented by the following notation:
Employee x Parking
Example:

Employee x Parking:

Cross product grows quickly, there are N * M rows in the resulting relation (N = number in Employees, M = number in Parking), and the resulting relation has X + Y columns (attributes) in it (X = number of attributes in Employees, Y = number of attributes in Parking).
The given data,
R =4 rows
S=10 rows, 
R x Y = 40 rows
Hence the correct answer is 40 rows