1 / 16

603 Database Systems

This lecture covers the fundamentals of relational calculus in database systems, focusing on querying employee data. Topics include the use of free and bound variables in domain relational calculus (DRC) with practical examples. Key queries involve retrieving employee information such as birthdates and addresses, as well as specific employee conditions in various departments. By the end of this lecture, students will understand the differences between relational calculus and SQL, and how to execute complex queries to extract meaningful data from databases.

duaa
Télécharger la présentation

603 Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. 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’) }

  3. 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.

  4. 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.

  5. 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’

  6. Relational Calculus Alternatively: { uv | EMPLOYEE (‘John’, ‘B’, ‘Smith’, t , u, v, w, x, y, z)}

  7. 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) }

  8. 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

  9. 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

  10. 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.

  11. 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.

  12. 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

  13. 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)

  14. 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 (| )

  15. 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 (| )

  16. 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

More Related