html> Lectures of Dennis Shasha



A Strong Working Knowledge of K


Dennis Shasha
Courant Institute of Mathematical Sciences
Department of Computer Science
New York University
shasha@cs.nyu.edu
http://cs.nyu.edu/cs/faculty/shasha/index.html



Unit 3: Tables and Database Facilities





Topics




Constructing Tables, method 1




Constructing Tables, method 2



The K (data and function) tree



Summary of Using the Tree



Using Tables




Partitioning (Group by) and Tables




Exercise





Names Having the Median Salary



Exercise: Assemble a Table with Grouping




Exercise: FIFO Accounting


Cost of Goods Sold: solution


More Exercises




Sales Solutions

/ I. Find all sales by salesagent fred.
i: & sale.salesagent = `fred / Find the indexes of sales by fred.
+sale[;i] / the initial + puts it in record format

/ II. Find the sum of sales by district.
part: = sale.district
+(?sale.district; +/'sale.amount[part])

/ III. Find the sum of sales by district of fred.
i: & sale.salesagent = `fred
part: = sale.district[i] 
   / Among those in i, find groups with the same district.
+(?sale.district[i]; +/' sale.amount[i][part])


/ IV. Find the top three sales per district.
/ If there are fewer than three sales in a district,
/ then don't give any of them.
top:{[number; district]
  i: & sale.district = district / find the indexes in that district
  amounts: sale.amount[i] / find the sales there
  orderedvec: amounts[> amounts] / sort by amount
  :[number < #orderedvec / depending on number in result, take the top
        district, ,orderedvec[!number]
        district, ,orderedvec]}


top[3]'?sale.district


/ V. Find the sum of sales by district and sales agent
/  select salesagent, district sum(sale)
/ from sale
/ group by salesagent, district
part: = sale.salesagent,' sale.district / need the each to get all pairs
groupers: ? sale.salesagent ,' sale.district
groupers ,' +/'sale.amount[part]

/ VI. Find pairs of salesagents who work in the same district.
/ select s1.district, s1.salesagent, s2.salesagent
/ from sale s1, sale s2
/ where s1.district = s2.district
findcross:{[district]
 i: & sale.district = district
 all: sale.salesagent[i] ,\:/: sale.salesagent[i]
 allnodups: ?,/all
 district ,/: allnodups}

findcross'?sale.district


Exercise: Most of Relational Algebra



Importing Large Text Files to Tables