1 / 15

Database Design

Database Design. Dr. M.E. Fayad, Professor Computer Engineering Department, Room #283I College of Engineering San José State University One Washington Square San José, CA 95192-0180 http://www.engr.sjsu.edu/~fayad. Lesson 7: Aggregation and Negation Queries. 2. Lesson Objectives.

zita
Download Presentation

Database Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Design Dr. M.E. Fayad, Professor Computer Engineering Department, Room #283I College of Engineering San José State University One Washington Square San José, CA 95192-0180 http://www.engr.sjsu.edu/~fayad SJSU -- CmpE

  2. Lesson 7: Aggregation and Negation Queries 2 SJSU – CmpE --- M.E. Fayad

  3. Lesson Objectives • Understand Aggregation and Negation Queries • Set Grouping • Average, Count, and Sum • Area and Volume • Min and Max • Negation • Stratified Datalog Queries 3 SJSU – CmpE --- M.E. Fayad

  4. Aggregation 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. Introduction 4 SJSU – CmpE --- M.E. Fayad

  5. R0(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. Set Grouping 5 SJSU – CmpE --- M.E. Fayad

  6. Suppose 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: Examples SJSU – CmpE --- M.E. Fayad

  7. Set 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). Set Grouping: Examples 7 SJSU – CmpE --- M.E. Fayad

  8. These 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. Average, Count, Sum 8 SJSU – CmpE --- M.E. Fayad

  9. Find 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): Average, Count, Sum: Examples 9 SJSU – CmpE --- M.E. Fayad

  10. The 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 plane Area Volume SJSU – CmpE --- M.E. Fayad

  11. Town 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. Area Volume: Example 11 SJSU – CmpE --- M.E. Fayad

  12. Find 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 edge Edge(x, y, min<t>) :-- Go (x, 0, y, t) Minimum and Maximum 12 SJSU – CmpE --- M.E. Fayad

  13. R0(x1,…,xk) :– not R(x1,…,xk). Example: Not_Covered(x, y) :– not Covered(x, y). Negation 13 SJSU – CmpE --- M.E. Fayad

  14. A 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()). Stratified Datalog Queries 14 SJSU – CmpE --- M.E. Fayad

  15. T/F: Set grouping operators are aggregate operators. Get, set, max, min are aggregate operators Both aggregation and negation require that the entire relation is unknown. Area operator is an aggregate operator that uses with three spatial attributes in the argument. Volume aggregate operator uses two variables as arguments Discussion Questions 15 SJSU – CmpE --- M.E. Fayad

More Related