Understanding Division Operator in Relational Algebra || Lesson 52 || DBMS || Learning Monkey || скачать в хорошем качестве

Understanding Division Operator in Relational Algebra || Lesson 52 || DBMS || Learning Monkey ||

Here we will discuss Understanding Division Operator in Relational Algebra. Understanding Division Operator in Relational Algebra: Division Operator ( ): Division operator A B is applied if and only if 1. Attributes of B is a subset of A. 2. The relation returned by the division operator will return those tuples from relation A which are associated with every B's tuple. 3. Most RDBMS implementations with SQL as a primary query language do not directly implement division. We will try to understand the division operator by considering the following queries. 1. Retrieve the names of the employees who work on all the projects that 'JOHN SMITH' works on. In order to write the query, we should have the employee table and works_on table. Because the details of John Smith were stored in the employee table and to get the project in which the employees are working we should have works_on table of the company database. On this table first, we have to fetch the SSN of john smith (123456789) from the employee table and from works_on table we have to get the projects (1, 2) in which john smith is working. Now we have to identify the employees (453453453) who are working on all the projects in which john smith is working. We will evaluate step by step the relational algebraic queries involved. STEP 1: Fetch all the details of the employee whose Fname = 'John' and Lname = 'Smith'. SMITH Fname = 'John' AND Lname = 'Smith' (Employee) STEP 2: Fetch the projects in which John Smith is working. SMITH_PNOS Pno (Works_on (Essn = ssn) SMITH) STEP 3: Project ESSN, PNO from works_on. SSN_PNOS (Essn, Pno) (WORKS_ON) Why we have projected the Essn, Pno? That we will soon understand clearly. STEP 4: Fetch the employees who work on all the projects john smith works. SSNS (SSN) SSN_PNOS  SMITH_PNOS Here SSN_PNOS is obtained because the attributes of SSN_PNOS should be a subset of SMITH_PNOS. If we apply the division operator as the above the relation returned by the division operator will return those tuples from relation SSN_PNOS which are associated with every SMITH_PNOS's tuple. The values in SMITH_PNOS are (1, 2) the division operator will return those tuples of SSN_PNOS where the attributes of SSN_PNOS other than pno should be the same for every value of SMITH_PNOS. The resultant attribute of the SSNS table is renamed as SSN from the statement. STEP 5: Fetch the Fname and Lname of the employees. RESULT  Fname, Lname (SSNS * EMPLOYEE) Here we are applying the natural join on SSNS and EMPLOYEE and we will project the Fname and Lname of those employees. Link for our website: https://learningmonkey.in Follow us on Facebook @   / learningmonkey   Follow us on Instagram @   / learningmonkey1   Follow us on Twitter @   / _learningmonkey   Mail us @ learningmonkey01@gmail.com