clojure-relational-algebra.clj

;; Examples from: http://www.databasteknik.se/webbkursen/relalg-lecture/index.html

(def employees   (table connection-info :employees))
(def departments (table connection-info :departments))


;;== Projection ==

;; SELECT salary FROM employees
@(project employees #{:salary})
;; ({:salary 100} {:salary 300})

;; SELECT nr, salary FROM employees
@(project employees #{:nr :salary})
;; ({:nr 1 :salary 100} {:nr 5 :salary 300} {:nr 7 :salary 100})


;;== Selection ==

;; SELECT * FROM employees WHERE salary < 200
@(select employees (< :salary 200))
;; ({:nr 1 :name "John" :salary 100} {:nr 7 :name "Tom" :salary 100})

;; SELECT * FROM employees WHERE salary < 200 AND nr >= 7
@(select employees (both (< :salary 200) (>= :nr 7)))
;; ({:nr 7 :name "Tom" :salary 100})


;;== Relational Algebra Statements ==

;; SELECT name, salary FROM employees WHERE salary < 200
@(-> (select employees (< :salary 200))
     (project #{:name :salary}))
;; ({:name "John" :salary 100} {:name "Tome" :salary 100})

;;== Joins ==

;; SELECT * FROM employees JOIN departments ON dept = dnr
@(join employees departments {:dept :dnr})
;; ({:enr 1 :ename "Bill"  :dept "A" :dnr "A" :dname "Marketing"}
;;  {:enr 2 :ename "Sarah" :dept "C" :dnr "C" :dname "Legal"}
;;  {:enr 3 :ename "John"  :dept "A" :dnr "A" :dname "Marketing"})


;;== Natural Joins == (assuming :dept had the same name in both tables)

;; SELECT * FROM employees JOIN departments USING(dept)
@(join employees departments :dept)
;; ({:enr 1 :ename "Bill"  :dept "A" :dname "Marketing"}
;;  {:enr 2 :ename "Sarah" :dept "C" :dname "Legal"}
;;  {:enr 3 :ename "John"  :dept "A" :dname "Marketing"})


;;== Renaming ==

;; SELECT * FROM employees as employees(enr, ename, dept)
;;   JOIN departments as departments(dnr, dname) ON dept = dnr
@(join (rename employees   {:nr :enr, :name :ename})
       (rename departments {:nr :dnr, :name :dname})
       {:dept :dnr})

;; SELECT * FROM employees JOIN departments ON dept = departments.nr
@(join employees departments {:dept :departments.nr})


;;== Aggregate Functions == NOTE: I'm not sure about the syntax for this

;; SELECT sum(salary) FROM employees
@(aggregate employees #{:sum:salary})
;; ({:sum 500})

;; SELECT count(salary) FROM employees
@(aggregate employees #{:count:salary})
;; ({:count 3})

;; SELECT count(distinct salary) FROM employees
@(aggregate (project employees #{:salary}) #{:count:salary})
;; ({:count 2})

;; SELECT sum(salary) FROM employees GROUP BY dept
@(aggregate employees #{:sum:salary} [:dept]) 
;; ({:dept "A" :sum 200} {:dept "C" :sum 300})

;; SELECT sum(salary), count(*) FROM employees GROUP BY dept
@(aggregate employees #{:sum:salary :count:*} [:dept]) 
;; ({:dept "A" :sum 200 :count 2} {:dept "C" :sum 300 :count 1})


;;== Hierarchies ==

;; name of John's manager:
;; SELECT b.name FROM employees p
;;   JOIN employees b ON p.mgr = b.nr
;;   WHERE p.name = "John"
@(-> (select (rename employees :p) (= :name "John"))
     (join (rename employees :b) {:p.mgr :b.nr})
     (project :b.name))
;; ({:name "Bob"})

;; name of John's manager's manager:
;; SELECT ob.name FROM employees p
;;   JOIN employees b  ON p.mgr = b.nr
;;   JOIN employees ob ON b.mgr = ob.nr
;;   WHERE p.name = "John"
@(-> (select (rename employees :p) (= :name "John"))
     (join (rename employees :b)  {:p.mgr :b.nr})
     (join (rename employees :ob) {:b.mgr :o.nr})
     (project :ob.name))
;; ({:name "Gretchen"})

;; name of John's manager and his manager's manager
;; (SELECT b.name FROM employees p
;;    JOIN employees b ON p.mgr = b.nr
;;    WHERE p.name = "John")
;; UNION
;; (SELECT ob.name FROM employees p
;;    JOIN employees b  ON p.mgr = b.nr
;;    JOIN employees ob ON b.mgr = ob.nr
;;    WHERE p.name = "John")
@(let [m (-> (select (rename employees :p) (= :name "John"))
             (join (rename employees :b) {:p.mgr :b.nr}))]
   (union (project m :b.name)
          (-> (join m (rename employees :ob) {:b.mgr :o.nr})
              (project :ob.name))))