Optional filters in relational database

I’m trying to create a procedure in MySQL/MariaDB with optional filters for each participating table. Assuming the tables are $A, B, C, ldots$, each table may or may not be filtered, and the result is that of each table having their respective optional filters applied. $A$ and $B$ have the relation A (1) -> (*) B, and similarly, B (1) -> (*) C and so on.

With that, I’ve thought of the following function to filter & join each pair of tables:

/*
  F(): filter
  L: left side
  R: right side
*/

if(L.filter is NULL and R.filter is NULL) {
  return fullJoin(F(L), F(R))
} else if(L.filter not NULL and R.filter is NULL) {
  return leftJoin(F(L), F(R))
} else if(L.filter is NULL and R.filter not NULL) {
  return rightJoin(F(L), F(R))
} else { // both not NULL
  return innerJoin(F(L), F(R))
}

The filter function F() would be something like SELECT * FROM table WHERE input == NULL or input == column. The function is first applied to 4 tables as follows:

fn(fn(fn(A,B),C),D)

where the result of said function fn is considered to have a not NULL filter.

Additionally, I think the function fn can be packed into the following:

SELECT * FROM L FULL JOIN R 
WHERE (R.input == NULL || R.input = R.column)
AND (L.input == NULL || L.input = L.column)

Is my function correct?

databases – Insert operation for two tables in Relational Algebra

The given schema is:

Publisher(pid, name, location)
Book(bid, title, author, page, price)
Publish(bid, pid, publish_date)

The RA question/query is:

  1. Insert new record of book named “database” published by “pearson” in
    “2010-12-11” and written by “Patterson”.

What I have tried so far is this but I am not sure if it is correct:

Given :

Publisher.name = "pearson"
Publisher.publish_date = "2010-12-11"

Also Given,

Book.title = "database"
Book.author = "Patterson"

Assuming, the entry for this book is already done in Publish table, I created a temporary relation r1.

r1 ← π bid(σ name="pearson"  ∧ publish_date="2010-12-11" (Publisher ⋈ Publish))

From r1, I can get bid of the same book.

So, now can I write the following?

Approach 1: Book ← Book U { π bid(r1), "database", "Patterson" }

OR

Approach 2: Book ← Book U { r1 X {"database", "Patterson"} }

Are the RA expressions in Approach 1 and 2 valid? Do they(or any one of them) insert data accordingly? And is it fine to ignore value for other two attributes page and price?

What would be the right Relational Algebra query for the following question? Thank you in advance for any help/suggestions.

Relational Algebra: existential quantifiers

So i have the following question and template answer:

Question: List the names of managers who have at least one dependant.

Answer: {e.Fname, e.Lname | EMPLOYEE(e) AND (∃d)(∃t)(DEPARTMENT(d) AND DEPENDANT(t) AND e.Ssn = d.Mgrssn AND t.Essn = e.Ssn) }

My answer was exactly the same except that instead of the (∃d)(∃t)(…) section I said (∃d)(DEPARTMENT(d) AND e.Ssn = d.Mgrssn) AND (∃t)(DEPENDANT(t) AND t.Essn = e.Ssn).

My question is basically are these equivalent?

relational theory – How does DBMS ensure duration without flushing

I was watching this video, and at a certain point (don’t recall the exact minute) the speaker says that the DBMS doesn’t immediately flush the file when updates happen. He explains that IO operations are too slow to be performed every time you make a change.

So the question is Practically, how do they can ensure duration?

microservices – Solution design leveraging Graph and Relational database types

As part of my thousands of side projects one of them that i really got into made me think about leveraging a graph db.

Is it viable to leverage both a graph db and a relational db at the same time for the same data sets? Does it even make sense to approach the solution this way?

Thinking is i use nodes and relationships to demonstrate the connections between entities, and relational db to store the deeper detail about the entity.

e.g. purchasing cars from a garage in Graph DB
James (Node) -> BOUGHT_FROM (Relationship) -> Wild Mustangs (Garage)

same data in relational db would be stored as

Person_Table

column value
name James
age 27
email j@email.com

Garage_Table

column value
name Wild Mustangs
location LA
garage_no TR 21234

Hopefully this example makes sense.

normalization – Does relational design discourage splitting tables unnecessarily?

It’s been a long time since I studied relational design, but I had a vague memory that it encourages not splitting a table unnecessarily. For instance, given the functional dependencies

K -> A
K -> B
K -> C

my assumption was that the “best” schema is just {KABC} and not something like {KAB, KC} or even {KA, KB, KC}. At least in practice that is how I’ve seen database designers implement the table.

However, a quick refresher on Wikipedia indicates that the normalization formalism

  • doesn’t make any statement in the direction of obtaining a “minimal schema”,
  • 6NF would even require {KA, KB, KC}. Since 6NF implies the other normal forms, it implies that it is even impossible for them to make such a minimal requirement.

I’m a bit confused that I got this wrong all the time. Does the notion of “obtaining a minimal number of tables” really play no role in formal relational design, and it is just common practice?

Design relation for relational database (to import from below csv table) to query using REST API

I have this table where I am trying to make relationship between exercise and muscle group to find list of exercises for a muscle group, and also to request a list of muscle groups for an exercise. However I am having trouble to find what sort of keys should I use. Was thinking nested approach but that might be an overkill. Any suggestions/thoughts?

enter image description here

I wanted to create a separate table for boolen/junction table with bool valus, YES/NO but not sure how to leverage on this.

enter image description here

database theory – What do I call a relational schema without the constraints?

It is common in db theory to define a ‘relation schema’ as consisting of a relation name, the (names of the) attributes, as well as constraints on the relation. A ‘database schema’ is then a set of such relation schemas.

An instance of the database schema consists of a finite set of tuples for each of the relations, which satisfies all of the constraints.

Now what I need is the notion of a relation schema, and sets of such without the constraints. I want to add the constraints separately, and then be able to talk about how some instance of the (constraint-less) schema satisfies the constraints or not. E.g. define ‘legal instances’ as instances that satisfy the constraints.

Is it defined like that somewhere in the literature? What are the common names for a db schema sans constraints?

How attributes helps us to allow column interchange in a relational database?

A relation on two sets A and B is defined as the subset of the cartesian product A X B = {(a,b), a $in$ A and b $in$ B}.

A database is also a relation and each entry is an ordered tuple respecting the cartesian product.

Now, interchanging the rows and the columns of a database does not change it. I can understand that a relation is just a set of tuples (called as row) so the order of elements in a set does not matter and hence row interchange will not pose a problem. But column interchange conflicts with the idea that a particular entry of the database is an ordered tuple.

So, attributes are used to tackle this problem. This is what I was told (verbatim):

Example table:

       Name    Roll No.

       Alex       2

       Fred       5

       Nate       7

=> Fix a set C also called the index set ( here it can be {1,2} or {Name, Roll No.} )

=> Consider:: {A i | i $in$ C}, A i is the “type” of the attribute i (here that is A 1 = String and A 2 = Integer).

=> The cartesian product: $Pi$ i A i is seen as the collection of functions f : C $to$ $cup$ i A i such that f ( i ) $in$ A i

I was told that this definition of the cartesian product will allow us not to worry about the position of the column ‘Name’, etc. But I did not understand why. I would appreciate any kind of help and I apologize in advance for bad/incorrect formatting.

relational algebra – Check if a relation is reflexive, symmetric and transitive

I want to better understand how this actually works, as my solutions are
sometimes not 100% correct.

I have the following relation:

Check if the following relation is reflexive, symmetric, and/or
transitive:

$$ R_1 = { (x,y) mid x,y in mathbb{R}, x=0 land y geq 0 }. $$

so by that

$$ R_1 = { 00, 01, 02,03,04,05,06,07,08,09,010, dots, 0R_+ } $$

Basically $R_1$ is 0 and any $R_+$ number.

It is not reflexive, as $(a,a)$ is not in $R_1$. I only have 00, but
not 11 or 22 and so on.

It is also not symmetric, as I don’t have 01 and 10 or 02 or 50 and
05. So $xRy$ and $yRx$ are not true for $R_1$.

As for the transitivity, well, if $xRy$ and $yRz$ then $xRz$. Well,
this one is hard to understand. I could use 00 as an example: If $y = 0$
and $z geq 0$ then $xRz$ would work. So I would say it is transitive.

Can anybody confirm if this would be correct? If not, i would really
appreciate a correct approach then for this task.