Relational Calculus Essay Example for Free

Social Calculus Essay Presentation Procedural Query language question particular includes giving a bit by bit procedure of getting the inquiry result e.g., social variable based math utilization calls for point by point information on the administrators included hard for the utilization of non-specialists Declarative Query language inquiry determination includes giving the intelligent conditions the outcomes are required to fulfill simple for the utilization of non-specialists Prof P Sreenivasa Kumar, Department of CSE, IITM. 1 TRC †a definitive question language Tuple variable †related with a connection ( called the range connection ) †¢ takes tuples from the range connection as its qualities †¢ t: tuple variable over connection r with plot R(A,B,C ) t. A represents estimation of section An and so on TRC Query †fundamental structure: { t1.Ai1, t2.Ai2,†¦tm.Aim | ÃŽ ¸ } predicate math articulation including tuple factors t1, t2,†¦, tm, tm+1,†¦,ts determines the condition to be fulfilled Prof P Sreenivasa Kumar, Department of CSE, IITM. 2 A model TRC question understudy (rollNo, name, degree, year, sex, deptNo, consultant ) office (deptId, name, hod, telephone ) Obtain the rollNo, name of all young lady understudies in the Maths Dept (deptId = 2) {s.rollNo,s.name| student(s)^ s.sex=‘F’^ s.deptNo=2} qualities required in the outcome This predicate is genuine at whatever point estimation of s is a tuple from the understudy connection, bogus in any case by and large, if t is a tuple variable with extend connection r, r( t ) is taken as a predicate which is valid if and just if the estimation of t is a tuple in r Prof P Sreenivasa Kumar, Department of CSE, IITM. 3 General type of the condition in TRC questions Nuclear articulations are the accompanying: 1. r ( t ) - valid if t is a tuple in the connection case r 2. t1. Man-made intelligence t2 .Aj compOp is one of {, ≠¥, =, ≠} 3. t.Ai c is a steady of proper sort Composite articulations: 1. Any nuclear articulation 2. F1 ∠§ F2 ,, F1 ∠¨ F2 ,  ¬ F1 where F1 and F2 are articulations 3. (∀t) (F), (âˆÆ't) (F) where F is an articulation and t is a tuple variable Free Variables Bound Variables †evaluated factors Prof P Sreenivasa Kumar, Department of CSE, IITM. 4 Translation of the question in TRC All conceivable tuple assignments to the free factors in the inquiry are thought of. For a particular task, if the articulation to one side of the vertical bar assesses to valid, that mix of tuple qualities would be utilized to create a tuple in the outcome connection. While delivering the outcome tuple, the estimations of the characteristics for the relating tuple factors as determined on the left half of the vertical bar would be utilized. Note: The main free factors are the ones that appear to one side of the vertical bar Prof P Sreenivasa Kumar, Department of CSE, IITM. 5 Model TRC inquiries Get the rollNo, name of all young lady understudies 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 variable d: existentially bound tuple variable Existentially or all around measured tuple factors can be utilized on the RHS of the vertical bar to indicate question conditions Attributes of free (or unbound ) tuple factors can be utilized on LHS of vertical bar to determine qualities required in the outcomes Prof P Sreenivasa Kumar, Department of CSE, IITM. 6 Model Relational Scheme understudy (rollNo, name, degree, year, sex, deptNo, counsel) office (deptId, name, hod, telephone) educator (empId, name, sex, startYear, deptNo, telephone) course (courseId, cname, credits, deptNo) enlistment (rollNo, courseId, sem, year, grade) instructing (empId, courseId, sem, year, classRoom) preRequisite (preReqCourse, courseID) Prof P Sreenivasa Kumar, Department of CSE, IITM. 7 Q2 Q3 Q4 Q5 Model questions in TRC (1/5) 1)Determine the divisions that don't have any young lady understudies understudy (rollNo, name, degree, year, sex, deptNo, counselor) division (deptId, name, hod, telephone) {d.name|department(d) ^  ¬(âˆÆ' s)(student(s) ^ s.sex =‘F’ ^ s.deptNo = d.deptId) Prof P Sreenivasa Kumar, Department of CSE, IITM. 8 Models inquiries in TRC (2/5) Blueprint 2)Obtain the names of courses enlisted by understudy 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 CSE, IITM. 9 Models questions in TRC (3/5) Diagram 3)Get the names of understudies who have scored ‘S’ in all subjects they have selected. Accept that each understudy is taken a crack at any rate one course. {s.name | student(s) ^ (∀e)(( enrollment(e) ^ e.rollNo = s.rollNo) â†' e.grade =‘S’)} individual P with all S grades: for enlistment tuples not having her move number, LHS is bogus for enlistment tuples having her move number, LHS is valid, RHS likewise obvious so the ramifications is valid for all e tuples individual Q with some non-S grades: for enlistment tuples not having her move number, LHS is bogus for enlistment tuples having her move number, LHS is valid, however RHS is bogus for in any event one tuple. So the ramifications isn't valid for at any rate one tuple. Prof P Sreenivasa Kumar, Department of CSE, IITM. 10 Models questions in TRC (4/5) Outline 4) Get the names of understudies who have taken at any rate one course instructed by their counsel {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 offices whose HODs are instructing at any rate 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 CSE, IITM. 11 Models inquiries in TRC (5/5) Blueprint 6)Determine the understudies who are enlisted for each course educated by Prof Ramanujam. Expect that Prof Ramanujam educates in any event one course. 1. {s.rollNo | understudy (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 CSE, IITM. 12 Issue with unhindered utilization of Negation What is the consequence of the question: {s.rollNo |  ¬ student(s)} ? Unbounded answers !! Hazardous TRC articulation : Any articulation whose outcome utilizes â€Å"constants/values† that don't show up in the examples of any of the database relations. Hazardous articulations are to be stayed away from while determining TRC questions. Prof P Sreenivasa Kumar, Department of CSE, IITM. 13 Expressive intensity of TRC and Relational Algebra It very well may be indicated that both Tuple Relational Calculus and Relational Algebra have the equivalent expressive force An inquiry can be defined in (safe) TRC if and just on the off chance that it tends to be planned in RA Both can not be utilized to figure inquiries including transitive conclusion locate all immediate or circuitous pre-requirements of a course discover all subordinates of a particular worker and so forth. Prof P Sreenivasa Kumar, Department of CSE, IITM.