mid2revisionandasssociationrules(ii).pptVIP

Mid 2 revision and Asssociation Rules (II) Prof. Sin-Min Lee Department of Computer Science Example Data Instance Natural Join and Intersection Natural join: special case of join where ? is implicit – attributes with same name must be equal: STUDENT ? Takes ′ STUDENT ?STUDENT.sid = Takes.sid Takes Intersection: as with set operations, derivable from difference Division A somewhat messy operation that can be expressed in terms of the operations we have already defined Used to express queries such as “The fids of faculty who have taught all subjects” Paraphrased: “The fid’s of professors for which there does not exist a subject that they haven’t taught” Division Using Our Existing Operators All possible teaching assignments: Allpairs: NotTaught, all (fid,subj) pairs for which professor fid has not taught subj: Answer is all faculty not in NotTaught: Division: R1 ? R2 Requirement: schema(R1) ? schema(R2) Result schema: schema(R1) – schema(R2) “Professors who have taught all courses”: What about “Courses that have been taught by all faculty”? Division Goal: Produce the tuples in one relation, r, that match all tuples in another relation, s r (A1, …An, B1, …Bm) s (B1 …Bm) r/s, with attributes A1, …An, is the set of all tuples a such that for every tuple b in s, a,b is in r Can be expressed in terms of projection, set difference, and cross-product Division (cont’d) Division - Example List the Ids of students who have passed all courses that were taught in spring 2000 Numerator: StudId and CrsCode for every course passed by every student: ?StudId, CrsCode (?Grade? ‘F’ (Transcript) ) Denominator: CrsCode of all courses taught in spring 2000 ?CrsCode (?Semester=‘S2000’ (Teaching) ) Result is numerator/denominator Relational Calculus Important features: Declarative formal query languages for relational model Based on the branch mathematical logic known as predicate calculus Two types of RC: 1) tuple relational calculus 2) domain relational calculus A s