Relational Calculus Essay

Introduction Procedural motion language dubiousness specification involves giving a step by step handle of obtaining the interview result e.g., resemblanceal algebra usage c anys for detailed knowledge of the operators heterogeneous difficult for the habit of non-experts Declarative Query language query specification involves giving the logical chequers the results are required to satisfy easy for the use of non-experts prof P Sreenivasa Kumar, incision of CS&E, IITM. 1TRC a revelatory query languageTuple variable associated with a relation ( c each(prenominal)ed the range relation ) takes tuples from the range relation as its value t tuple variable over relation r with scheme R(A,B,C ) t.A stands for value of column A etc TRC Query basic form t1.Ai1, t2.Ai2,tm.Aim predicate calculus expression involving tuple variables t1, t2,, tm, tm+1,,ts specifies the condition to be satisfied professor P Sreenivasa Kumar, plane section of CS&E, IITM. 2An example TRC query disciple (rollNo, name, degree, year, sex, deptNo, consultant ) incision (deptId, name, hod, phone ) achieve the rollNo, name of all girl students in the Maths Dept (deptId = 2) s.rollNo,s.name student(s) s.sex=F s.deptNo=2 attri entirelyes required in the resultThis predicate is square whenever value of s is a tuple from the student relation, false otherwise In general, if t is a tuple variable with range relation r, r( t ) is taken as a predicate which is true if and only if the value of t is a tuple in r professor P Sreenivasa Kumar, Department of CS&E, IITM. 3 usual form of the condition in TRC queriesAtomic expressions are the following 1. r ( t ) -true if t is a tuple in the relation instance r 2. t1. Ai t2 .Aj compOp is one of , , =, 3. t.Ai c c is a constant of subdue type Composite expressions 1. all atomic expression 2. F1 F2 ,, F1 F2 , F1 where F1 and F2 are expressions 3. (t) (F), (t) (F) where F is an expression and t is a tuple variable Free Variables Bou nd Variables quantified variables Prof P Sreenivasa Kumar, Department of CS&E, IITM. 4Interpretation of the query in TRCAll possible tuple assignments to the rid variables in the query are considered. For any(prenominal) specific assignment, if the expression to the right of the upended bar evaluates to true, that combination of tuple values would be used to produce a tuple in the result relation. tour producing the result tuple, the values of the attributes for the corresponding tuple variables as specified on the left hand side of the vertical bar would be used. Note The only melt variables are the ones that appear to the left of the vertical bar Prof P Sreenivasa Kumar, Department of CS&E, IITM. 5Example TRC queriesObtain the rollNo, name of all girl students in the Maths Dept s.rollNo,s.name student(s) s.sex=F ( d)(department(d) d.name=Maths d.deptId = s.deptNo)s free tuple variabled existentially bound tuple variableExistentially or universally quantified tuple vari ables locoweed be used on the RHS of the vertical bar to specify query conditions Attributes of free (or unbound ) tuple variables stern be used on LHS of vertical bar to specify attributes required in the results Prof P Sreenivasa Kumar, Department of CS&E, IITM. 6Example Relational Schemestudent (rollNo, name, degree, year, sex, deptNo, advisor) department (deptId, name, hod, phone) professor (empId, name, sex, startYear, deptNo, phone) lam (courseId, cname, credits, deptNo) enrollment (rollNo, courseId, sem, year, grade) teaching (empId, courseId, sem, year, classRoom) preRequisite (preReqCourse, courseID) Prof P Sreenivasa Kumar, Department of CS&E, IITM. 7Q2 Q3 Q4 Q5Example queries in TRC (1/5)1)Determine the departments that do non deliver any girl studentsstudent (rollNo, name, degree, year, sex, deptNo, advisor) department (deptId, name, hod, phone) d.namedepartment(d) ( s)(student(s) s.sex =F s.deptNo = d.deptId)Prof P Sreenivasa Kumar, Department of CS&E, IITM.8Ex amples queries in TRC (2/5)Schema2)Obtain the names of courses enrolled by student named Mahesh c.name course(c) (s) (e) ( student(s) enrollment(e) s.name = Mahesh s.rollNo = e.rollNo c.courseId = e.courseId Prof P Sreenivasa Kumar, Department of CS&E, IITM.9Examples queries in TRC (3/5)Schema3)Get the names of students who have scored S in all subjects they have enrolled. Assume that every student is enrolled in at to the lowest degree one course. s.name student(s) (e)(( enrollment(e) e.rollNo = s.rollNo) e.grade =S)person P with all S grades for enrollment tuples non having her roll number, LHS is false for enrollment tuples having her roll number, LHS is true, RHS also true so the implication is true for all e tuples person Q with or so non-S grades for enrollment tuples not having her roll number, LHS is false for enrollment tuples having her roll number, LHS is true, but RHS is false for at least one tuple. So the implication is not true for at least one tuple. Pro f P Sreenivasa Kumar, Department of CS&E, IITM. 10Examples queries in TRC (4/5)Schema4) Get the names of students who have taken at least one course taught by their advisor s.name student(s) (e)(t)(enrollment(e) teaching(t) e.courseId = t.courseId e.rollNo = s.rollNo t.empId = s.advisor 5) Display the departments whose HODs are teaching at least one course in the current semester d.name department(d) (t)(teaching(t) t.empid = d.hod t.sem = odd t.year = 2008) Prof P Sreenivasa Kumar, Department of CS&E, IITM. 11Examples queries in TRC (5/5)Schema6)Determine the students who are enrolled for every course taught by Prof Ramanujam. Assume that Prof Ramanujam teaches at least one course. 1. s.rollNo student (s) 2. (c)(course (c) 3. ((t),(p)( teaching(t) professor(p) 4. t.courseId = c.courseId 5. p.name = Ramanujam 6. p.empId = t.empId )) 7. (e) (enrollment(e) 8. e.courseId = c.courseId 9. e.rollNo = s.rollNo) 10. ) 11. Prof P Sreenivasa Kumar, Department of CS&E, IIT M. 12Problem with unrestricted use of NegationWhat is the result of the querys.rollNo student(s) ?Infinite answers Unsafe TRC expression Any expression whose result uses constants / values that do not appear in the instances of any of the database relations. Unsafe expressions are to be avoided while specifying TRC queries.Prof P Sreenivasa Kumar, Department of CS&E, IITM.13Expressive power of TRC and Relational AlgebraIt can be shown that both Tuple Relational Calculus and Relational Algebra have the alike expressive power A query can be suppose in (safe) TRC if and only if it can be formulated in RA Both can not be used to formulate queries involving transitive closure find all direct or indirect pre-requisites of a course find all subordinates of a specific employee etc.Prof P Sreenivasa Kumar, Department of CS&E, IITM.