|
Logical Querying of Relational Databases
Luminita Pistol 1, Radu Bucea-Manea-Tonis2
1 Spiru Haret University
2 Hyperion University
Abstract. This paper aims to demonstrate the usefulness of formal logic and Lambda Calculus in database programming. After a short introduction in propositional and first order logic, we implement dynamically a small database and translate some SQL queries in filtered java 8 streams, enhanced with Tuples facilities from jOOλ library.
Keywords: logic query, propositional logic, predicate, relational database
JEL Codes: M15
1. Introduction
A database is a set of basic axioms corresponding to base relations and tuples plus deductive axioms or inference rules. Tuples are for the relationships what are nouns for sentences, each denote a true particular sentence [Date, 2005].
A logical query is the action of evaluating a Boolean expression concerning tuples and relations. Boolean operators in propositional logic are:
Table1: Boolean Operators
|
Operator name and meaning |
Example |
|
negation (non) |
-φ |
|
conjunction (and) |
(φ & ψ) |
|
disjunction (or) |
(φ | ψ) |
|
implication (if ..., then ...) |
(φ -> ψ) |
|
equivalence (if and only if…) |
(φ <-> ψ) |
A basic axiom is equivalent to a tuple of a database or a predicate. The predicate value is a function of truth that has a set of parameters. It should not be assigned a value to a database in order to determine the database predicate to take the truth value FALSE [Date, 2005]
Table 2: Basic axiom table
|
Parent |
Child |
|
Caninae |
Canis |
|
Canis |
Canis lupus |
Corresponding to the example above, we can construct an open formula with two occurrences of the variable x:
Grandparent(x) <- Parent (x) & ( Child(x) <-> Parent(y))
By placing an existential quantifier ∃ before x (“for some x”) and an universal quantifier ∀ before y (“for all y”), we can bind these variables, as may be seen bellow [Bird, 2009]:
∃x. ∀y. Grandparent(x) <- Parent (x) & (Child(x) <-> Parent(y))
1.1. Advantages of logical querying:
· Uniform representation of operations and dependency constraints;
· Improved semantics of the original data model;
· Improve SQL facilities making possible to negate a where clause if we keep in mind the formal logic rules [StackOverflow, 2016,]:
A & B & (D | E) ↔ ⌐ ( A & B & ( D | E ) ) ↔ ⌐A | ⌐B | (⌐D & ⌐E )
2. Case study
Suppose that in our database the following scheme has been defined [Moshe, 2006]:
Student (name, dorm, major, GPA),
Professor (name, dept, salary, year hired)
Chair (dept, name)
We create a dynamic structure for this as the following:
Studenti = new ArrayList<Student>();
Profesori = new ArrayList<Professor>();
Decani = new ArrayList<Chair>();
Decani.add(new Chair("Iosipescu","Math"));
Decani.add(new Chair("Radulescu","CS"));
Profesori.add(new Professor("Georgescu","CS",5000,1999));
Profesori.add(new Professor("Iosipescu","Math",3000,2004));
Profesori.add(new Professor("Radulescu","CS",7000,2000));
Profesori.add(new Professor("Marinescu","Math",6000,1998));
Studenti.add(new Student("Ionescu", "A5", "CS", 9.5));
Studenti.add(new Student("Marinescu", "A3", "Math", 9.0));
Studenti.add(new Student("Popescu", "A4", "CS", 8.5));
Studenti.add(new Student("Vasilescu", "A5", "Math", 7.5));
2.1. List the name and dorm of Math students with a GPA of at least 8.0:
List<Student> result = db.Studenti.stream().filter(s -> s.major.equals("Math") && s.GPA>=8.0).collect(Collectors.toList());
2.2. List the names of faculty members with a salary to 5000, who were hired after 1990:
List<Professor> result1 = db.Profesori.stream().filter(p -> p.salary<=5000 && p.year>=1990).collect(Collectors.toList());
2.3. List the names of faculty whose salary is higher than their chair’s salary:
db.Profesori.stream()
.sorted((p1, p2) -> Long.compare(p1.salary, p2.salary))
.flatMap(v1 -> db.Decani.stream()
.filter(v2 -> Objects.equals(v1.dept, v2.dept) && db.Profesori.stream()
.anyMatch(t -> v1.salary>t.salary && t.name.equals(v2.name)))
.map(v2 -> tuple(v1.name, v2.name)))
.forEach(System.out::println);
2.4. List the names of faculty members whose salary is highest in their department:
db.Profesori.stream().filter(p->db.Profesori.stream().anyMatch(t->t.salary<p.salary && t.dept.equals(p.dept))).forEach(p->{System.out.println("name=" + p.name);});
We have employed the jOOλ library [GitHub, 2016], making the following mappings [Fusco, 2015]:
INNER JOIN - flatMap() with filter()
WHERE - filter()
GROUP BY - collect()
HAVING - filter()
SELECT - map()
The results are the following:
name=Marinescu dorm=A3//1
name=Georgescu
name=Iosipescu//2
(Marinescu, Iosipescu)//3
name=Radulescu
name=Marinescu//4.
3. Conclusions
There are advantages. Evaluating expressions and functional programming has already given us the support for a declarative way of parsing collections of objects. Since relational databases cease way to noSQL ones, we have to discover a good substitute for SQL language. Beginning with Java 8 lambda expressions, streams and method references, we have to search no more...
4. References
[1] C.J. Date, Baze de date, Editura Plus, 2005, ISBN:973-861-90-1-7
[2] S Bird, E. Klein, E. Loper, Natural Language Processing With Python, Published by O’Reilly Media, Inc., 2005 Gravenstein Highway North, Sebastopol, CA 95472, 2009, ISBN: 978-0-596-51649-9 http://victoria.lviv.ua/html/fl5/NaturalLanguageProcessingWithPython.pdf
[3] Moshe Y. Vardi, I. Barland, B. McMahan, Logic and Database Queries, August 31, 2006, https://www.cs.rice.edu/~tlogic/Database/all-lectures.pdf
[4] GitHub, 2016, https://github.com/jOOQ/jOOL
[5] M. Fusco, Common SQL Clauses and Their Equivalents in Java 8 Streams, 1 Mar 2015, https://blog.jooq.org/2015/08/13/common-sql-clauses-and-their-equivalents-in-java-8-streams/
[6] Stack Overflow, SQL WHERE condition, not equal to?, 2016 http://stackoverflow.com/questions/6156979/sql-where-condition-not-equal-to