Homechevron_rightEngineeringchevron_rightRPSC Programmerchevron_rightProgrammer DBMSchevron_rightConsider a join&nbsp;<span style="font family:calibri,sans...

Consider a join ...

  • Q. Consider a join \(X_1 ⋈_ \theta X_2\). Let \(A_1\) and \(A_2\) be sets of attributes from \(X_1\) and \(X_2\)respectively. Let \(A_3\) be the attributes of \(X_1\) that are involved in join condition \(\theta\) but are not in \(A_1 \cup A_2\), and let \(A_4\) be attributes of \(X_2\) that are involved in join condition \(\theta\) but are not in \(A_1 \cup A_2\). What is the optimized version of the relational algebra expression \(\Pi _{A_1 \cup A_2}(X_1 ⋈_ \theta X_2)\)?
  • filter_dramaExplanation
    Answer is : C

    Theta join operations are commutative. 

    \(X_1 ⋈_ \theta X_2\)\(X_2 ⋈_ \theta X_1\)

    Theta joins are associative in the following manner:

    \((X_1 ⋈_ {\theta _1} X_2) ⋈_ {\theta _2 \wedge \theta _3} X_3\) = \(X_1 ⋈_ {\theta _1 \wedge \theta _3} (X_2 ⋈_ {\theta _2} X_3)\)

     

Discussion

    No one started the discussion yet. Break the ice and start the conversation.
    Please Login to be part of the discussion.

Similar Questions

  • 1.

    The following relation records the age of 500 employees of a company, where empNo (Indicating the employee number) is the key:

    empAge(empNo, age)

    Consider the following relational algebra expression:

    πempNo(empAge ⋈ (age>age1) ρempNo 1, age1(empAge))

    What does the above expression generate?

  • filter_dramaExplanation
    Answer is : B

    Answer: Option 2

    Explanation:

    Consider the empAge(empNo, age) Table

    empNo

    age

    100

    22

    101

    21

    102

    23

    103

    24

    104

    25

    105

    26

    200

    22

    201

    21

    202

    23

    203

    24

    204

    25

    205

    26

    206

    27

    We run the following Query on above Table 

    IIempNo(empAge ⋈ (age>age1) ρempNo 1, age1(empAge))

    We get Output Table as 

    empNo

    100

    102

    103

    104

    105

    200

    202

    203

    204

    205

    206

    Hence the Output is "Employees whose age greater than at least one employee" or "Employees whose age is no minimum".

  • 2.

    Consider the relational schema given below, where eId of the relation dependent is a foreign

    key referring to empId of the relation employee. Assume that every employee has at least one

    associated dependent in the dependent relation.

    Employee (empId, empName, empAge)

    Dependent(depId, eId, depName, depAge)

    Consider the following relational algebra query:

    ΠempId(employee)-ΠempId (employee⋈(empId = eID)∧(empAge ≤ depAge)dependent)

    The above query evaluates to the set of empIds of employees whose age is greater than that of
  • filter_dramaExplanation
    Answer is : D

    Explanation:

    Example:

    Employee Relation:

    EmpID

    empName

    empAge

    e1

    A

    20

    e2

    B

    25

    e3

    C

    24

     

    Dependent Relation:

    depID

    eID

    depName

    Age

    d1

    e1

    X

    30

    d2

    e1

    Y

    25

    d3

    e1

    Z

    20

    d4

    e2

    P

    18

    d5

    e2

    P

    17

    d7

    e3

    Z

    35

     

    R1=ΠempId (employee⋈(empId = eID)∧(empAge ≤ depAge)dependent)

    Output : e1,e3

    R2=ΠempId(employee)

    Output: e1,e2,e3

    R = R2 - R1= {e1,e2,e3} - {e1,e3} = e2

    • e2 is output that is saying that set of empIds of employees whose age is greater than that of all of his/her dependents.
  • 3.

    The following functional dependencies hold for relations R(A, B, C) and S(B, D, E)

    B → A,

    A → C

    The relation R contains 200tuples and the relation S contains 100tuples. What is the maximum number of tuples possible in the natural join \(R \bowtie S\) ?

  • filter_dramaExplanation
    Answer is : A
    The correct answer is option 1

    Explanation:

    For given relation R1(A, B, C) and R2(B, D, E), functional dependencies are given only for relation R1, not for R2 And the candidate key for R1 is B, so all values must be unique in R1. To get the maximum number of tuples in output, there can be two possibilities.

    1) All 100 values of B in R2 are the same and there is an entry in R1 that matches with this value. In this case, we get 100 tuples in output.

    Example:

    Consider an example, suppose there is two tables R1(A, B, C) consisting of 5 tuples and R2( B, D, E) consisting of 4 tuples only.

    B

    A

    C

    1

    E1

    P1

    2

    E2

    P2

    3

    E3

    P3

    4

    E4

    P4

    5

    E5

    P5

     

    B

    D

    E

    1

    M1

    K1

    1

    M2

    K2

    1

    M3

    K3

    1

    M4

    K4


    Natural join of R1 and R2 (R1 * R2) gives: 4

    2) All 100 values of B in R2 are different and these values are present in R1 also. In this case, also we get 100 tuples.

    Example:

    Consider an example, suppose there is two tables R1(A, B, C) consists of 5 tuples and R2( B, D, E) consists of 4 tuples only.

    B

    A

    C

    1

    E1

    P1

    2

    E2

    P2

    3

    E3

    P3

    4

    E4

    P4

    5

    E5

    P5

     

    B

    D

    E

    1

    M1

    K1

    2

    M2

    K2

    3

    M3

    K3

    4

    M4

    K4


    Natural join of R1 and R2 (R1 * R2) gives: 4

  • 4.

    Which is/are the correct statement given about a relation?

    I. In relational algebra, selection may return duplicate tuples.

    II. In relational algebra, projection will never return duplicate column.
  • filter_dramaExplanation
    Answer is : B

    In relational algebra, selection and projection will never return duplicate entries.

    Symbol

    Name

    Example

    σ

    selection

    σA = 500(R) → Return rows whose A attribute is equal to 500 and no duplicates are allowed

    projection

    B(R) → Output the column B and no duplicate are allowed

  • 5.

    Consider two relational schemas: 

    emp(ID, name, address, phone number, deptID)

    department(ID, managerID, deptname, location)

    What does the following relational expression perform?

    \(π_{(name, managerID)} (σ_{emp.deptID=department.ID} (σ_{deptname="Marketing"}(emp × department)))\)

  • filter_dramaExplanation
    Answer is : A
    The expression retrieves name of all employees in the Marketing department along with their manager ID.

Programmer DBMSTopics

leaderboardLeaderboard
  • Rahul Kumar

    191 Points

  • VIKRAM JEET

    54 Points

  • GEETHIKA CHOWDARY

    53 Points

  • sunita saini

    52 Points

  • Zain

    49 Points