dbis-relational-calculus

DBIS Relational Calculus

This set of classes is used to define objects of the relational model (as a result of a conversion from an ER-diagram)

Features

• Create expressions of tuple and domain calculus in python.
• Validate these expressions on a basic level.
• Convert these expressions to Text in LaTeX math mode.
• Convert these expressions to queries in SQLite.

Installation

Install via pip:

pip install dbis-relational-calculus

Basic usage

Both tuple and domain calculus were developed to be as similar as possible to each other, and to the mathematical notation. You will find, that the programming style between tuple and domain calculus is in most ways identically.

The only difference between both stems from what variables express in each of the relational calculus notations:

• In the tuple calculus, a variable represents a tuple of a relation (i.e. a row). Therefore, we here have to define the relation (type), which the variable stems from ($x \in R$).
• In the domain calculus, a variable represents an entry of a relation in a specific column. Multiple variables combined make up for a whole tuple in a specific relation ($R(x, y, z)$).

General constructs

Construct	Description	Tuple Calculus	Domain Calculus
TupleCalculus	Defines an expression in the tuple calculus.	:heavy_check_mark:	:x:
DomainCalculus	Defines an expression in the domain calculus.	:x:	:heavy_check_mark:
Result	Defines a result of an expression in the respective calculus.	:heavy_check_mark:	:heavy_check_mark:
Variable	Defines a variable in the respective relational calculus.	:heavy_check_mark:	:heavy_check_mark:
Tuple	Defines a tuple in the domain calculus.	:x:	:heavy_check_mark:
Forall	Defines a universal quantification in the respective relational calculus.	:heavy_check_mark:	:heavy_check_mark:
Exists	Defines an existential quantification in the respective relational calculus.	:heavy_check_mark:	:heavy_check_mark:
And	Defines a conjunction in the respective relational calculus.	:heavy_check_mark:	:heavy_check_mark:
Or	Defines a disjunction in the respective relational calculus.	:heavy_check_mark:	:heavy_check_mark:
Not	Defines a negation in the respective relational calculus.	:heavy_check_mark:	:heavy_check_mark:
Equals	Defines an equality in the respective relational calculus.	:heavy_check_mark:	:heavy_check_mark:
GreaterEquals	Defines a greater-or-equal comparison in the respective relational calculus.	:heavy_check_mark:	:heavy_check_mark:
GreaterThan	Defines a greater-than comparison in the respective relational calculus.	:heavy_check_mark:	:heavy_check_mark:
LessEquals	Defines a less-or-equal comparison in the respective relational calculus.	:heavy_check_mark:	:heavy_check_mark:
LessThan	Defines a less-than comparison in the respective relational calculus.	:heavy_check_mark:	:heavy_check_mark:

Specific usages

Tuple Calculus

Atoms

The comparison operators can be used to bound/set the values of a variables attribute, or compare two different variables attributes. We can access the attributes of a variable in two ways: Either as a tuple[Variable, str] or using the __getitem__ syntax. Thus, the following lines are equivalent:

assert var["some_attribute"] == (var, "some_attribute")

eq = Equals((x, 'some_attribute'), "Hello!")
gt = GreaterThan(5, y['other_attribute'])
le = LessEquals((z, 'other_attribute'), 7.2)
lt = LessThan(a['some_attribute'], (b, 'other_attribute'))

Since a variable being returned cannot be quantified, we still must somehow tell the formula, which relation the variable stems from. For this reason, Variable is also an atom in the tuple calculus. See the examples down below.

x = Variable('x', 'some_relation')

Quantifiers

When a variable is not being returned, it has to be quantified. This is done by using the Forall and Exists construct. sub_formula here refers to a formula generated by using other atoms, quantifiers, and connectives.

forall = Forall(x, sub_formula)
exists = Exists(x, sub_formula)

If one wants to quantify multiple variables using the same quantification type at once, one can do so efficiently by using sets:

foralls = Forall({x,y,z}, sub_formula)
exists = Exists({x,y,z}, sub_formula)

Connectives

The connectives can be used to combine atoms, quantifiers, and connectives.

and_formula = And(sub_formula_1, sub_formula_2)
or_formula = Or(sub_formula_1, sub_formula_2)
not_formula = Not(sub_formula)

Examples

Example 1

• Relation People has three attributes: name, age, and height.

from relational_calculus.tuple_calculus import *

# define the variable used
p = Variable('p', 'People')

# define the expression
tc = TupleCalculus(
	# return every attribute of p
	Result([p]),
	# attribute height of p has to be greater than 190
	And(
		p,
		GreaterThan(p['height'], 190)
	)
)

# check if the expression is valid
is_correct = tc.verify()

# get latex math mode string representation
latex = str(tc)

# convert the expression into an SQLite query
query = tc.to_sql()

tc will now return every attribute of p (that is name, age, and height) for tuples in People that have a height greater than 190.

Example 2

• Relation Student has two attributes: name, student_id.
• Relation Lecture has three attributes: name, lecture_id, lecture_type, location.
• Relation Professor has two attributes: name, professor_id.
• Relation teaches has two attributes: lecture_id, professor_id.
• Relation listens has two attributes: student_id, lecture_id.

from relational_calculus.tuple_calculus import *

# define the variable used
s = Variable('s', 'Student')
l = Variable('l', 'Lecture')
p = Variable('p', 'Professor')
t = Variable('t', 'teaches')
li = Variable('li', 'listens')

# define the expression
tc = TupleCalculus(
	# Return everything of p, followed by l.name, l.lecture_type and p.name
	Result([s, l["name"], l["lecture_type"], p["name"]]),
	# Formula
	And(
		And(And(s, l), p),
		Exists(
			{t, li},
			And(
				Equals(t["lecture_id"], li["lecture_id"]),
				And(
					And(
						Equals(li["student_id"], s["student_id"]),
						Equals(li["lecture_id"], l["lecture_id"])
					),
					And(
						Equals(t["professor_id"], p["professor_id"]),
						Equals(t["lecture_id"], l["lecture_id"])
					)
				)
			)
		)
	)
)

# check if the expression is valid
is_correct = tc.verify()

# get latex math mode string representation
latex = str(tc)

# convert the expression into an SQLite query
query = tc.to_sql()

tc will now return the all students names and their student_ids who listen to a lecture name of type lecture_type and are taught by a professor with name.

Domain Calculus

Atoms

The comparison operators can be used to bound/set the values of a variable, or compare two different variables.

eq = Equals(x, "Hello!")
gt = GreaterThan(5, y)
le = LessEquals(z, 7.2)
lt = LessThan(a, b)

Variables are no atoms in the domain calculus. However, they still need to be typed. Since they only represent columns, we need multiple variables to make up a whole relation. We can also make initialize a column with a certain value. Then, all tuples must have that value in that specific column. Additionally, if one does not care for a single column, placeholders can be used:

:warning: Make sure that the tuple object received the same amount of variables, placeholders and values as the actually used relation consists of.

t1 = Tuple("some_relation",  [x, y, z])
t1 = Tuple("some_relation",  [x, y, "Hello!"])
t1 = Tuple("some_relation",  [x, 1.5, z])
t1 = Tuple("some_relation",  [x, y, None]) # placeholder

Quantifiers

When a variable is not being returned, it has to be quantified. This is done by using the Forall and Exists construct. sub_formula here refers to a formula generated by using other atoms, quantifiers, and connectives.

forall = Forall(x, sub_formula)
exists = Exists(x, sub_formula)

If one wants to quantify multiple variables using the same quantification type at once, one can do so efficiently using sets.

foralls = Forall({x,y,z}, sub_formula)
exists = Exists({x,y,z}, sub_formula)

Connectives

The connectives can be used to combine atoms, quantifiers, and connectives.

and_formula = And(sub_formula_1, sub_formula_2)
or_formula = Or(sub_formula_1, sub_formula_2)
not_formula = Not(sub_formula)

Examples

Example 1

• Relation People has three attributes: name, age, and height.

from relational_calculus.domain_calculus import *

# define the variable used
name = Variable('name')
age = Variable('age')
height = Variable('height')

# define the expression
dc = DomainCalculus(
	# Return
	Result([name, age, height]),
	# Formula
	And(
		Tuple("People", [name, age, height]),
		GreaterThan(height, 190)
	)
)

# check if the expression is valid
is_correct = dc.verify()

# get latex math mode string representation
latex = str(dc)

# convert the expression into an SQLite query
query = dc.to_sql()

dc will now return every tuple (name, age, height) for People that have a height greater than 190.

Example 2

• Relation Student has two attributes: name, student_id.
• Relation Lecture has three attributes: name, lecture_id, lecture_type, location.
• Relation Professor has two attributes: name, professor_id.
• Relation teaches has two attributes: lecture_id, professor_id.
• Relation listens has two attributes: student_id, lecture_id.

from relational_calculus.domain_calculus import *

# define the variable used
s_name = Variable('s_name')
student_id = Variable('student_id')
l_name = Variable('l_name')
lecture_id = Variable('lecture_id')
lecture_type = Variable('lecture_type')
p_name = Variable('p_name')
professor_id = Variable('professor_id')


# define the expression
dc = DomainCalculus(
	# Return
	Result([s_name, student_id, l_name, lecture_type, p_name]),
	# Formula
	Exists(
		{lecture_id, professor_id},
		And(
			And(
				And(
					Tuple("Student", [s_name, student_id]),
					Tuple("Lecture", [l_name, lecture_id, lecture_type, None])
				),
				And(
					Tuple("Professor", [p_name, professor_id]),
					Tuple("teaches", [lecture_id, professor_id])
				)
			),
			Tuple("listens", [student_id, lecture_id])
		)
	)
)

# check if the expression is valid
is_correct = dc.verify()

# get latex math mode string representation
latex = str(dc)

# convert the expression into an SQLite query
query = dc.to_sql()

dc will now return the all students names and their student_ids who listen to a lecture name of type lecture_type and are taught by a professor with name.