Database DesignPowerPoint PresentationSlide 3IntroductionSet GroupingSet Grouping: ExamplesSlide 7Average, Count, SumAverage, Count, Sum: ExamplesArea VolumeArea Volume: ExampleMinimum and MaximumNegationStratified Datalog QueriesDiscussion Questions2003SJSU -- CmpE L7-S1 Aggregation & NegationDatabase Design Dr. M.E. Fayad, ProfessorComputer Engineering Department, Room #283I College of EngineeringSan José State UniversityOne Washington SquareSan José, CA 95192-0180 http://www.engr.sjsu.edu/~fayad2003SJSU – CmpE --- M.E. Fayad L7-S2 Aggregation & Negation2Lesson 7:Aggregation and NegationQueries2003SJSU – CmpE --- M.E. Fayad L7-S3 Aggregation & Negation Lesson ObjectivesObjectives3 Understand Aggregation and Negation Queries Set Grouping Average, Count, and Sum Area and Volume Min and Max Negation Stratified Datalog Queries2003SJSU – CmpE --- M.E. Fayad L7-S4 Aggregation & NegationAggregation operators are operators that take in a set of values and return a single value.Negation of a relation with arity k returns a constraint representation of complement of the relation. An arity is the number attributes of a relation. It also called a dimension.Both aggregation and negation require that the entire relation be completely known.Apply only to input relations.4Introduction2003SJSU – CmpE --- M.E. Fayad L7-S5 Aggregation & NegationR0(y1,…,ym) :– R(x1,…xn) where each yi is either xj or <xj>. < > is similar to GROUP BY in SQL.–The operator is restricted to cases where at least the attributes referred to by the regular variables in the head are constants.–The operator first group all tuples in R into groups where these variables have the same values.–Then for each group it takes the union of the column of attributes referred to by <> bracketed variables in the head.5Set Grouping2003SJSU – CmpE --- M.E. Fayad L7-S6 Aggregation & NegationSuppose Can_Send_message (x, y) be true if person x can send a message to person y using mobile phones.Suppose we want to find for each person x the set of persons that x can reach. Use set grouping operator as following: Reach(x, <y>) :– Can_Send_Message(x, y).To find the set of mobile phone users, we can use:Mobile_Phone-Users (<x>) :-- Mobile_Phone (x, A)--- A is Mobile_Phone Area which is defined.Set Grouping: Examples2003SJSU – CmpE --- M.E. Fayad L7-S7 Aggregation & NegationSet grouping operators can be applied of intentional set constants. Example: Find the areas with at least one mobile phone user:Area_with_Mobile_Phones (<A>) :-- Mobile_Phone (x, A).7Set Grouping: Examples2003SJSU – CmpE --- M.E. Fayad L7-S8 Aggregation & NegationThese are aggregate operators and they are like the set grouping operators.The syntax is similar to the set grouping operators, with each <> bracketed variable in the head preceded by one key word: avg, count, or sum.In this case, the column of attributes referred to by bracketed variables in the head must be constants.8Average, Count, Sum2003SJSU – CmpE --- M.E. Fayad L7-S9 Aggregation & NegationFind each person x the number of persons that x can reach:Reach(x, count<y>) :– Can_Send_Message(x, y).Find the number of mobile phone users in San Jose SJ_Mobile_Phone-User(x): Num_SJ_Users (count<x>):-- SJ_Mobile_Phone-User(x): 9Average, Count, Sum: Examples2003SJSU – CmpE --- M.E. Fayad L7-S10 Aggregation & NegationThe area aggregate operator works on relations that represent maps.When we use this area aggregate operator, we assume in such relation that the two distinguished attributes, call the spatial attributes.The spatial attributes are relational variables x, y on which we place linear constraints.The other attributes are nonspatial attributes are constants.The relation describes which attributes hold for regions of the planeArea Volume2003SJSU – CmpE --- M.E. Fayad L7-S11 Aggregation & NegationTown relation represent a map and it has x, y which are spatial attributes.Find the total area of each town on the map:Town-Area (t, area <x, y>) :-- town (t, x, y)The volume aggregate operator is like the area operator except that it takes three variables as arguments.11Area Volume: Example2003SJSU – CmpE --- M.E. Fayad L7-S12 Aggregation & NegationFind the max profit possible given for each acre of corn, rye, sunflower, and wheat yields a profit of 30, 25, 8, and 15 respectively. Profit(max<30x1+25x2+8x3+15x4>) :– Crops(x1, x2, x3, x4).Find the minimum time between each pair of town connected by a single edgeEdge(x, y, min<t>) :-- Go (x, 0, y, t)12Minimum and Maximum2003SJSU – CmpE --- M.E. Fayad L7-S13 Aggregation & NegationR0(x1,…,xk) :– not R(x1,…,xk).Example: Not_Covered(x, y) :– not Covered(x, y).13Negation2003SJSU – CmpE --- M.E. Fayad L7-S14 Aggregation & NegationA query is evaluated and its output (semantics) will be an input to another query.Example: Faster(x, y, min<t>) :– Travel(x, y, t). Travel is the output of a query Qtravel. Now it is also the input to the above query Qfastest. The entire query is written Qfastest(Qtravel()).14Stratified Datalog Queries2003SJSU – CmpE --- M.E. Fayad L7-S15 Aggregation & NegationT/F:1. Set grouping operators are aggregate operators.2. Get, set, max, min are aggregate operators3. Both aggregation and negation require that the entire relation is unknown.4. Area operator is an aggregate operator that uses with three spatial attributes in the argument.5. Volume aggregate operator uses two variables as arguments15 Discussion
View Full Document
Unlocking...