Operation is useful for a special kind of query that

This preview shows page 9 - 12 out of 25 pages.

operation is useful for a special kind of query that sometimes occurs in databaseapplications. An example is "Retrieve the names of employees who work onallthe projects that‘John Smith’ works on." To express this query using the DIVISION operation, proceed asfollows. First, retrieve the list of project numbers that ‘John Smith’ works on in the intermediaterelation SMITH_PNOS:SMITHσFNAME=’John’ AND LNAME=’Smith’(EMPLOYEE)SMITH_PNOSπPNO(WORKS_ONESSN=SSNSMITH)Next, create a relation that includes a tuple <PNO, ESSN> whenever the employee whose socialsecurity number is ESSN works on the project whose number is PNO in the intermediate relationSSN_PNOS:SSN_PNOSπESSN,PNO(WORKS_ON)Finally, apply the DIVISION operation to the two relations, which gives the desired employees’social security numbers:SSNS(SSN)SSN_PNOS ÷ SMITH_PNOSRESULTπFNAME, LNAME(SSNS * EMPLOYEE)Genarally the DIVISION operation is applied to 2 relations R(Z) ÷ S(X), where X is subset of Z.Let Y = Z - X (and hence Z = X D Y); that is, let Y be the set of attributes of R that are notattributes of S. The result of DIVISION is a relation T(Y) that includes a tuple t if tuples tRappear in R with tR[Y] = t, and with tR[X] = tSfor every tupletSin S. This means that, for a tuple tto appear in the result T of the DIVISION, the values in t must appear in R in combination witheverytuple in S.9
DIVISION operator where X = {A}, Y = {B}, and Z = {A, B}. Notice that the tuples (values) b1and b4 appear in R in combination with all three tuples in S; that is why they appear in theresulting relation T. All other values of B in R do not appear with all the tuples in S and are notselected: b2 does not appear with a2 and b3 does not appear with a1.The DIVISION operator can be expressed as a sequence of p,x, and operations as follows:T1πY(R)T2πY((SxT1) – RTT1 –T2The DIVISION operation. (a) Dividing SSN_PNOS by SMITH_PNOS. (b)TR÷S.Aggregate Functions:to specify mathematicalaggregate functionson collections of valuesfrom the database. Ex retrieving the average or total salary of all employees or the number ofemployee tuples. Common functions applied to collections of numeric values include SUM,AVERAGE, MAXIMUM, and MINIMUM. The COUNT function is used for counting tuples orvalues.Groupingthe tuples in a relation by the value of some of their attributes and then applying anaggregate function independently to each group.An example would be to group employee tuples by DNO, so that each group includes the tuplesfor employees working in the same department. We can then list each DNO value along with,say, the average salary of employees within the department.<grouping attributes><function list> (R)ρR(DNO, NO_OF_EMPLOYEES, AVERAGE_SAL)(DNOCOUNTSSN, AVERAGESALARY(EMPLOYEE))DNOCOUNTSSN, AVERAGESALARY(EMPLOYEE)COUNTSSN, AVERAGESALARY(EMPLOYEE)10
Recursive Closure Operations:This operation is applied to arecursive relationshipbetweentuples of the same type, such as the relationship between an employee and a supervisor. This

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 25 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
Spring
Professor
Srinivas
Tags
Relational model, Tuple, relation, tuples

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture