160 likes | 289 Views
603 Database Systems. Senior Lecturer: Laurie Webster II, M.S.S.E.,M.S.E.E., M.S.BME, Ph.D., P.E. Lecture 16 A First Course in Database Systems. Relational Calculus. u v. EMPLOYEE. FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO. DRC Select:
E N D
603 Database Systems Senior Lecturer: Laurie Webster II, M.S.S.E.,M.S.E.E., M.S.BME, Ph.D., P.E. Lecture 16 A First Course in Database Systems
Relational Calculus u v EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO DRC Select: {<a,b,c> | <a,b,c> p a = } Query: Retrieve the birthdate and address of the employee whose name is ‘John B. Smith’. Free variablesBound variables {uv | (q) (r) (s) (t) (w) (x) (y) (z) (EMPLOYEE (qrstuvwxyz) and q = ‘John’ and r = ‘B’ and s = ‘Smith’) }
Relational Calculus Free variablesBound variables {uv | (q) (r) (s) (t) (w) (x) (y) (z) (EMPLOYEE (qrstuvwxyz) and q = ‘John’ and r = ‘B’ and s = ‘Smith’) } - We need ten variables for the EMPLOYEE relation, one to range over the domain of each attribute in order. Very Important!!!! - Of the ten variables q, r. s, ……, z, only u and v are free.
Relational Calculus Free variablesBound variables {uv | (q) (r) (s) (t) (w) (x) (y) (z) (EMPLOYEE (qrstuvwxyz) and q = ‘John’ and r = ‘B’ and s = ‘Smith’) } We first specify the requested attributes, BDATE, and ADDRESS, by domain variables u for BDATE and v for ADDRESS.
Relational Calculus Free variablesBound variables {uv | (q) (r) (s) (t) (w) (x) (y) (z) (EMPLOYEE (qrstuvwxyz) and q = ‘John’ and r = ‘B’ and s = ‘Smith’) } Secondly, we specify the condition for selecting a tuple following the bar ( | ) - qrs…..z be a tuple of EMPLOYEE with values for q(FNAME) and s(LNAME) be q=‘John’, r=‘B’ and s=‘Smith’
Relational Calculus Alternatively: { uv | EMPLOYEE (‘John’, ‘B’, ‘Smith’, t , u, v, w, x, y, z)}
Relational Calculus Q1: Retrieve the name and address of all employees who work for the ‘Research’ department. Q1:{qrs | (z) (l) (m) (EMPLOYEE (qrstuvwxyz) DEPARTMENT (lmno) and l = ‘Research’ and m = z) }
Relational Calculus DNAME DNUMBER MGRSSN MGRSTARTDATE l m n o Queries in DRC DEPARTMENT EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO q r s t u v w x y z
Relational Calculus Q1:{qrs | (z) (l) (m) (EMPLOYEE (qrstuvwxyz) DEPARTMENT (lmno) and l = ‘Research’ and m = z) } selection condition z = DNO join condition m = z l = ‘Research’ relate two domain variables from two m = DNUMBER relations. selection condition relates a domain variable to a constant
Relational Calculus Note: SQL does not include a universal quantifier. SELECT… FROM…. WHERE EXISTS (SELECT * FROM R AS X WHERE P(X) not (x) => NOT EXISTS is how SQL supports universal quantification.
Relational Calculus DOMAIN RELATIONAL CALCULUS (DRC): Domain relational calculus (DRC) differs from the tuple calculus in the type of variables used in the formulas: rather than having variables range over the tuples, the variables range over single values from domains of attributes.
Relational Calculus Query: For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name , birthdate, and address. Query(DRC): {iksuv | ( j) (PROJECT(hijk) and ( t) (EMPLOYEE(qrstuvwxyz) and ( m) ( n) (DEPARTMENT (lmno) and k=m and n=t and j=‘Stafford’)))} PROJECT PNAME PNUMBER PLOCATION DNUM h i j k
Relational Calculus Query(DRC): {iksuv | ( j) (PROJECT(hijk) and ( t) (EMPLOYEE(qrstuvwxyz) and ( m) ( n) (DEPARTMENT (lmno) and k=m and n=t and j=‘Stafford’)))} i = project number (PNUMBER) k = department number (DNUM) s = last name (LNAME) u = birthdate (BDATE) v = address (ADDRESS) m = department number (DNUMBER) n = manager’s SSN (MGRSSN) t = employee SSN (SSN)
Relational Calculus Query: For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name , birthdate, and address. i = project number (PNUMBER) k = department number (DNUM) s = last name (LNAME) u = birthdate (BDATE) v = address (ADDRESS) List iksuv ==> left of bar (| )
Relational Calculus Query(DRC): {iksuv| ( j) (PROJECT(hijk) and ( t) (EMPLOYEE(qrstuvwxyz) and ( m) ( n) (DEPARTMENT (lmno) and k=m and n=t and j=‘Stafford’)))} i = project number (PNUMBER) k = department number (DNUM) s = last name (LNAME) u = birthdate (BDATE) v = address (ADDRESS) List iksuv ==> left of bar (| )
Relational Calculus SSN Query:Find the names of employees who have no dependents. Query(DRC): {qs| t) EMPLOYEE(qrstuvwxyz) and (not ( l ) (DEPENDENT(lmnop) and t = l)))} ESSN DEPENDENT ESSN DEPENDENT_NAMES SEX BDATE RELATIONSHIP l m n o p