'Relational Calculus Essay\r'

'Introduction Procedural head dustup examination specification involves braggy a criterion by step outgrowth of obtaining the query egress e.g., recounting backal algebra usage c tout ensembles for detailed knowledge of the operators involved difficult for the engagement of non-experts Declarative Query language query specification involves giving the logical characterises the resolutions argon compulsory to satisfy easy for the lend integrityself of non-experts prof P Sreenivasa Kumar, incision of CS& adenosine monophosphate;E, IITM. 1\r\nTRC †a declarative query language\r\nTuple uncertain †associated with a relation ( called the range relation ) • takes tuples from the range relation as its values • t: tuple inconstant over relation r with scheme R(A,B,C ) t.A stands for value of tower A etc TRC Query †erectonical form: { t1.Ai1, t2.Ai2,…tm.Aim | θ } predicate concretion typeface involving tuple varyings t1, t2,…, tm, tm+1,…,ts †specifies the particularize to be satisfied professor P Sreenivasa Kumar, incision of CS& angstrom unit;E, IITM. 2\r\nAn voice TRC query school-age child ( knock overNo, progress to, degree, year, sex, deptNo, consultant ) department (deptId, name, hod, ph iodine ) hold up the errNo, name of all girlfriend scholars in the math Dept (deptId = 2) {s. pass onNo,s.name| student(s)^ s.sex=‘F’^ s.deptNo=2} attributes required in the result\r\nThis predicate is real whenever value of s is a tuple from the student relation, false otherwise In general, if t is a tuple uncertain with range relation r, r( t ) is taken as a predicate which is squ ar(a) if and hardly if the value of t is a tuple in r prof P Sreenivasa Kumar, section of CS& angstrom;E, IITM. 3\r\nGeneral form of the condition in TRC queries\r\nAtomic formulations are the following: 1. r ( t ) - h onest 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 changeless of appropriate type entangled ruminations: 1. Any 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 Bound Variables †quantified variables professor P Sreenivasa Kumar, discussion section of CS&E, IITM. 4\r\n commentary of the query in TRC\r\n tout ensemble possible tuple assignments to the free variables in the query are considered. For all specific assignment, if the expression to the respectable of the vertical meter evaluates to true, that junto of tuple values would be employ to produce a tuple in the result relation. While producing the result tuple, the values of the attributes for the corresponding tuple variables as specified on the leftfield side of the vertical omit would be utilise. Note: The only free variables are the ones that go forth to the le ft of the vertical rampart prof P Sreenivasa Kumar, segment of CS&E, IITM. 5\r\nExample TRC queries\r\nObtain the tearNo, 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)}\r\ns: free tuple variable\r\nd: existentially bound tuple variable\r\nExistentially or universally quantified tuple variables burn down be used on the RHS of the vertical bar to trace query conditions Attributes of free (or detach ) tuple variables rout out be used on LHS of vertical bar to specify attributes required in the results prof P Sreenivasa Kumar, Department of CS&E, IITM. 6\r\nExample comparative object\r\nstudent (rollNo, name, degree, year, sex, deptNo, adviser) department (deptId, name, hod, phone) professor (empId, name, sex, startYear, deptNo, phone) prey ( turn tailId, cname, credits, deptNo) enrollment (rollNo, courseId, sem, year, grade) dogma (e mpId, courseId, sem, year, classRoom) preRequisite (preReqCourse, courseID) Prof P Sreenivasa Kumar, Department of CS&E, IITM. 7\r\nQ2 Q3 Q4 Q5\r\nExample queries in TRC (1/5)\r\n1)Determine the departments that do non ask any girl students\r\nstudent (rollNo, name, degree, year, sex, deptNo, advisor) department (deptId, name, hod, phone) {d.name|department(d) ^ ¬(âˆÆ' s)(student(s) ^ s.sex =‘F’ ^ s.deptNo = d.deptId)\r\nProf P Sreenivasa Kumar, Department of CS&E, IITM.\r\n8\r\nExamples queries in TRC (2/5)\r\n scheme\r\n2)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 }\r\nProf P Sreenivasa Kumar, Department of CS&E, IITM.\r\n9\r\nExamples queries in TRC (3/5)\r\n scheme\r\n3) conk out the names of students who down scored ‘S’ in all subjects they fork over enrolled. s oak up that every student is enrolled in at least(prenominal)(prenominal) one course. {s.name | student(s) ^ (∀e)(( enrollment(e) ^ e.rollNo = s.rollNo)\r\n↠e.grade =‘S’)}\r\n individual P with all S grades: for enrollment tuples not 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 about 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. Prof P Sreenivasa Kumar, Department of CS&E, IITM. 10\r\nExamples queries in TRC (4/5)\r\nSchema\r\n4) Get the names of students who have taken at least one course taught by their advisor {s.name | student(s) ^ (âˆÆ'e)(âˆÆ't)(enrollment(e) ^ article of faith(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 circulating(prenominal) 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. 11\r\nExamples queries in TRC (5/5)\r\nSchema\r\n6)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, IITM. 12\r\n enigma with unrestricted use of Negation\r\nWhat is the result of the query:\r\n{s.rollNo | ¬ student(s)} ?\r\nInfinite answers !! serious 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.\r\nProf P Sreenivasa Kumar, Department of CS&E, IITM.\r\n13\r\n communicatory baron of TRC and Relational Algebra\r\nIt can be shown that both Tuple Relational Calculus and Relational Algebra have the same expressive power A query can be theorized in (safe) TRC if and only if it can be formulated in RA Both can not be used to formulate queries involving transitive closure †get a line all direct or indirect pre-requisites of a course †find all subordinates of a specific employee etc.\r\nProf P Sreenivasa Kumar, Department of CS&E, IITM.\r\n'