The Relational Model (how the data is stored)

  • The Relational Model used in the Relational Algebra examples:

Query 1: a very simple projection query

  • Query:

    • Find the fname and lname of all employees

  • Relation(s) that contain the necessary information to answer the query:

    • Empoyee

  • Solution:

    • πfname,lname (employee)

Query 2: combining selection and projection operators - (use in the correct order !!)

  • Query:

    • Find the fname and lname of employees that earn > 50000

  • Relation(s) that contain the necessary information to answer the query:

    • Empoyee

  • Solution:

    • πfname,lname ( σsalary > 50000 (employee) )

Question:   can we switch the order of the operations ???

Query 2: combining selection and projection operators - (use in the correct order !!)

  • Query:

    • Find the fname and lname of employees that earn > 50000

  • Relation(s) that contain the necessary information to answer the query:

    • Empoyee

  • Note:   this solution is wrong

    • σsalary > 50000 ( πfname,lname (employee) )

    because:

    • σsalary > 50000 uses attribute salary and the output of πfname, lname do not contain salary !!!

Query 3: a conjunction (AND) query

  • Query:

    • Find the fname and lname of employees in department 4 that earn > 50000

  • Relation(s) that contain the necessary information to answer the query:

    • Empoyee

  • Solution:

    • πfname,lname ( σdno = 4 ∧ salary > 50000 (employee) )

Query 4: a join query

  • Query:

    • Find fname and lname of all employees working in the "Research" department that earn more than $50,000

  • Relation(s) that contain the necessary information to answer the query:

    • Employee (provides fname, lname, salary and dno info)
    • Department (provides dnumber and dname (= 'Research') info)

  • Step 1:   combine the information from Employee and Department using a join (⋈) 

      πfn,ln ( σdname='Res' ∧ sal > 50000 ( Emp ⋈dno=dnumber Dept )  )

Query 4: a join query

  • Query:

    • Find fname and lname of all employees working in the "Research" department that earn more than $50,000

  • Relation(s) that contain the necessary information to answer the query:

    • Employee (provides fname, lname, salary and dno info)
    • Department (provides dnumber and dname (= 'Research') info)

  • Step 2:   now we can apply the selection conditions 

      πfn,ln ( σdname='Res' ∧ sal > 50000 ( Emp ⋈dno=dnumber Dept )  )

Query 4: a join query

  • Query:

    • Find fname and lname of all employees working in the "Research" department that earn more than $50,000

  • Relation(s) that contain the necessary information to answer the query:

    • Employee (provides fname, lname, salary and dno info)
    • Department (provides dnumber and dname (= 'Research') info)

  • Step 3 (final):   project out the attributes that we need 

      πfn,ln ( σdname='Res' ∧ sal > 50000 ( Emp ⋈dno=dnumber Dept )  )

How to create a temporary relation and renaming attributes

  • A temporary relation can be created with an assignment operation (=)

    Example:

    • JS = σ Fname='John' && LName='Smith' (employee)

    will create a temporary relation named JS which is the output (result) of:

    • σ Fname='John' && LName='Smith' (employee)

  • The attribute name(s) in the temporary relation can also be changed:

    • JS(x) = π superssn ( σ Fname='John' && LName='Smith' (employee) )

    changes the attribute name superssn to x

Query 5: how to use a temporary relation in queries

  • Query:

    • Find fname and lname of John Smith's supervisor

  • Solution:

    Problem:   attribute name superssn is ambiguous

Query 5: how to use a temporary relation in queries

  • Query:

    • Find fname and lname of John Smith's supervisor

  • Solution:   rename the attribute name in temporary relation to avoid ambiguity

     

Query 6: how to formulate a "negation" query

  • Query:

    • Find fname and lname of all employees that have dependents

  • Solution:

Query 6: how to formulate a "negation" query

  • Query:

    • Find fname and lname of all employees that do not have any dependents

  • Wrong solution:

Query 6: how to formulate a "negation" query

  • Query:

    • Find fname and lname of all employees that do not have any dependents

  • Example that illustrates why the query is wrong: (Employee 33344555 has no dependent)

Query 6: how to formulate a "negation" query

  • Query:

    • Find fname and lname of all employees that do not have any dependents

  • The correct solution: