Relation between Sets AUFTRAG & KUNDE

[mathmari]

2 : We have written this with the expression $\pi_{\text{NAME}}(\text{KUNDE} \ltimes \text{AUFTRAG})$ which can be written in relational calculus with the expression $\{y \mid \exists x_1, x_2, x_3\left (\text{KUNDE} \ltimes \text{AUFTRAG}(x_1, x_2, x_3)\land y_1=x_1\land y_2=x_3\right \}$, right? Now we have to write also the left join in relational calculus, or not? :unsure:

Do we write $\text{KUNDE} \ltimes \text{AUFTRAG}(x_1, x_2, x_3)$ in relational calculus as $$\{y_1,y_2,y_3 \mid \exists x_1,x_2,x_3 \left [\text{KUNDE}(y_1,y_2, y_3) \land \left (\text{AUFTRAG}(x_1, x_2, x_3)\land \left (y_1=x_1\lor y_2=x_2\lor y_3=x_3\right )\right )\right ]\}$$ or how can we write that there must be a common column in the two tables? :unsure:

 

[I like Serena]

As for the tuple calculus I found ine the notes the below example :

So at 1 we have then $$\{(k.KUNDNR, k.ORT)\mid \left (\text{KUNDE}(k)\right \}$$ or not?

Yep. Looks correct to me. (Nod)
You do have a superfluous parenthesis open before KUNDE though.

Do we write $\text{KUNDE} \ltimes \text{AUFTRAG}(x_1, x_2, x_3)$ in relational calculus as $$\{y_1,y_2,y_3 \mid \exists x_1,x_2,x_3 \left [\text{KUNDE}(y_1,y_2, y_3) \land \left (\text{AUFTRAG}(x_1, x_2, x_3)\land \left (y_1=x_1\lor y_2=x_2\lor y_3=x_3\right )\right )\right ]\}$$ or how can we write that there must be a common column in the two tables?

I believe you already have it in relational calculus. That is, if I understand correctly what is meant by "relational calculus".
Perhaps you meant in tuple calculus? (Wondering)

If so, then following the example that you gave, I believe we can make it:
$$\{(k.NAME) \mid \text{KUNDE}(k) \land \exists a(\text{AUFTRAG}(a)\land k.KUNDNR=a.KUNDNR)\}$$

 

[mathmari]

I believe you already have it in relational calculus. That is, if I understand correctly what is meant by "relational calculus".
Perhaps you meant in tuple calculus? (Wondering)

Ah can we use $\text{KUNDE} \ltimes \text{AUFTRAG}$ in the relational calculus? Do we nont have to write this left join also in relational calculus? Or is this allowed to use that? :unsure:

 

[I like Serena]

Ah can we use $\text{KUNDE} \ltimes \text{AUFTRAG}$ in the relational calculus? Do we nont have to write this left join also in relational calculus? Or is this allowed to use that?

I guess we have to find the definitions of relational calculus and tuple calculus, and perhaps some examples.
Do you perchance have notes on them somewhere? (Wondering)

I found relational algebra that shows sets with tuples and also the use of e.g. $\text{KUNDE} \ltimes \text{AUFTRAG}$.

 

[mathmari]

I guess we have to find the definitions of relational calculus and tuple calculus, and perhaps some examples.
Do you perchance have notes on them somewhere? (Wondering)

I found relational algebra that shows sets with tuples and also the use of e.g. $\text{KUNDE} \ltimes \text{AUFTRAG}$.

I am looking these notes (they are in german). From pdf-page 20 we have relational calculus and from pdf-page 23 we have tuple calculus.

 

[I like Serena]

I am looking these notes (they are in german). From pdf-page 20 we have relational calculus and from pdf-page 23 we have tuple calculus.

It says that the following is "Bereichs-Kalkül", which I think means Domain Calculus.

And it says that the following is tuple calculus.

It doesn't seem to refer to joins and such ($\Join$), so perhaps those are not intended to be used.

Either way, I guess we need to express each problem both as Domain Calculus and as Tuple Calculus.

 

[mathmari]

So do we have the following ?

1. Create an expression that lists all possible combinations of customer numbers (KUNDNR) and locations (ORT).

Domain Calculus : $\{kundnr, ort\mid \exists name\left (\text{KUNDE}(kundnr, name, ort)\right \}$
Tuple calculus : $\{(k.KUNDNR, k.ORT)\mid \left (\text{KUNDE}(k)\right \}$
2. Create an expression that lists all customer names (NAME) with at least one order (Auftrag).

Domain Calculus : $\{name \mid \exists auftragnr, datum, kundnr \left [\text{KUNDE}(kundnr, name, ort) \land \left (\text{AUFTRAG}(auftragng,datum, kundnr)\right )\right ]\}$
Tuple calculus : $\{(k.NAME) \mid \text{KUNDE}(k) \land \exists a(\text{AUFTRAG}(a)\land k.KUNDNR=a.KUNDNR)\}$
3. Create an expression that lists all order numbers (AUFTRAGNR) with customers from Gießen or Wetzlar.

Domain Calculus : $\{auftragnr \mid \exists kundnr \left [\text{KUNDE}(kundnr, name, ort) \land \left (\text{AUFTRAG}(auftragng,datum, kundnr)\right )\right ]\}$
Tuple calculus : $\{(a.AUFTRAGNR) \mid \text{AUFTRAG}(a) \land \exists k(\text{KUNDE}(k)\land k.KUNDNR=a.KUNDNR\land \left (k.ORT=\text{Gießen} \lor k.ORT=\text{Wetzlar}\right ))\}$
4. Create an expression that lists all customer numbers (KUNDNR) without an order (Auftrag).

Domain Calculus : $\{kundnr \mid \nexists auftragnr \left [\text{KUNDE}(kundnr, name, ort) \land \left (\text{AUFTRAG}(auftragng,datum, kundnr)\right )\right ]\}$
Tuple calculus : $\{(k.KUNDNR) \mid \text{KUNDE}(k) \land \exists a(k.KUNDNR\neq a.KUNDNR)\}$
5. Create an expression that lists all customer numbers (KUNDNR) that are not assigned to any order (Auftrag) and all customer numbers (KUNDNR) to which no name (NAME) is assigned.

Domain Calculus : $\{kundnr \mid\nexists auftragnr \left [\text{KUNDE}(kundnr, name, ort) \land \left (\text{AUFTRAG}(auftragng,datum, kundnr)\right )\right ] \land \nexists name \left [\text{KUNDE}(kundnr, name, ort) \right ]\}$
Tuple calculus : $\{(k.KUNDNR) \mid \text{KUNDE}(k) \land \exists a(k.KUNDNR\neq a.KUNDNR)\}$ How do we write the second condition?
:unsure:

 

[I like Serena]

3. Create an expression that lists all order numbers (AUFTRAGNR) with customers from Gießen or Wetzlar.

Domain Calculus : $\{auftragnr \mid \exists kundnr \left [\text{KUNDE}(kundnr, name, ort) \land \left (\text{AUFTRAG}(auftragng,datum, kundnr)\right )\right ]\}$
Tuple calculus : $\{(a.AUFTRAGNR) \mid \text{AUFTRAG}(a) \land \exists k(\text{KUNDE}(k)\land k.KUNDNR=a.KUNDNR\land \left (k.ORT=\text{Gießen} \lor k.ORT=\text{Wetzlar}\right ))\}$

I'm missing reference to the $\text{Ort}$ in the Domain Calculus version. (Worried)

4. Create an expression that lists all customer numbers (KUNDNR) without an order (Auftrag).

Domain Calculus : $\{kundnr \mid \nexists auftragnr \left [\text{KUNDE}(kundnr, name, ort) \land \left (\text{AUFTRAG}(auftragng,datum, kundnr)\right )\right ]\}$
Tuple calculus : $\{(k.KUNDNR) \mid \text{KUNDE}(k) \land \exists a(k.KUNDNR\neq a.KUNDNR)\}$

There seems to be an unbound $datum$ in the Domain Calculus version. (Worried)

In the tuple calculus version, won't there usually always be some order that is for a different client so that the condition is always satisfied when it shouldn't be? (Shake)

5. Create an expression that lists all customer numbers (KUNDNR) that are not assigned to any order (Auftrag) and all customer numbers (KUNDNR) to which no name (NAME) is assigned.

Domain Calculus : $\{kundnr \mid\nexists auftragnr \left [\text{KUNDE}(kundnr, name, ort) \land \left (\text{AUFTRAG}(auftragng,datum, kundnr)\right )\right ] \land \nexists name \left [\text{KUNDE}(kundnr, name, ort) \right ]\}$
Tuple calculus : $\{(k.KUNDNR) \mid \text{KUNDE}(k) \land \exists a(k.KUNDNR\neq a.KUNDNR)\}$ How do we write the second condition?

I think we need the union of the customers without order, combined with the customers for which no name is available.
In other words, they should be combined with a logical or $(\lor)$ instead of a logical and $(\land)$.

Furthermore, there are a couple of unbound variables, and the definition requires that there are none. (Worried)

The tuple calculus version can be written exactly the same as the domain calculus version - just with a different notation.

 

[mathmari]

3.Give an expression that lists all order numbers (AUFTRAGNR) with customers from Gießen or Wetzlar.

Domain Calculus : $$\{auftragnr \mid \exists kundnr ,name , ort,datum \left [\text{KUNDE}(kundnr, name, ort) \land \text{AUFTRAG}(auftragnr,datum, kundnr)\land \left (ort =\text{Gießen}\lor ort =\text{Wetzlar}\right )\right ]\}$$
Tuple calculus : $$\{(a.AUFTRAGNR) \mid \text{AUFTRAG}(a) \land \exists k(\text{KUNDE}(k)\land k.KUNDNR=a.KUNDNR\land \left (k.ORT=\text{Gießen} \lor k.ORT=\text{Wetzlar}\right ))\}$$
4. Create an expression that lists all customer numbers (KUNDNR) without an order (Auftrag).

Domain Calculus : $$\{kundnr \mid \exists auftragnr, name, ort, datum \left [\text{KUNDE}(kundnr, name, ort) \land \neg \left (\text{AUFTRAG}(auftragng,datum, kundnr)\right )\right ]\}$$
Tuple calculus : $$\{(k.KUNDNR) \mid \text{KUNDE}(k) \land \forall a(k.KUNDNR\neq a.KUNDNR)\}$$
5. Create an expression that lists all customer numbers (KUNDNR) that are not assigned to any order (Auftrag) and all customer numbers (KUNDNR) to which no name (NAME) is assigned.

Domain Calculus : $$\{kundnr \mid\exists name, ort, auftragnr, datum \left [\text{KUNDE}(kundnr, name, ort) \land \left (\neg\text{AUFTRAG}(auftragng,datum, kundnr) \lor \neg \text{KUNDE}(kundnr, name, ort)\right ) \right ]\}$$
Tuple calculus : $$\{(k.KUNDNR, a.KUNDNR) \mid \left [\text{KUNDE}(k) \land (k.KUNDNR\neq a.KUNDNR)\right ] \lor\left [ AUFTRAG(a) \land \exists k(a.KUNDNR\neq a. KUNDNR)\right ] \} $$
Are the expressions now correct? :unsure:

 

[I like Serena]

4. Create an expression that lists all customer numbers (KUNDNR) without an order (Auftrag).

Domain Calculus : $$\{kundnr \mid \exists auftragnr, name, ort, datum \left [\text{KUNDE}(kundnr, name, ort) \land \neg \left (\text{AUFTRAG}(auftragng,datum, kundnr)\right )\right ]\}$$

Suppose we have KUNDE(1, 'Ernst', 'Wetzlar') and AUFTRAG(10, 2021.01.01, 1) and also AUFTRAG(11, 2021.01.01, 2).
Then there exists an Auftrag that does not match the Kunde, and we find 'Ernst' even though he does have an Auftrag. (Shake)

Tuple calculus : $$\{(k.KUNDNR) \mid \text{KUNDE}(k) \land \forall a(k.KUNDNR\neq a.KUNDNR)\}$$

Where does $a$ come from? (Wondering)

 

[mathmari]

Suppose we have KUNDE(1, 'Ernst', 'Wetzlar') and AUFTRAG(10, 2021.01.01, 1) and also AUFTRAG(11, 2021.01.01, 2).
Then there exists an Auftrag that does not match the Kunde, and we find 'Ernst' even though he does have an Auftrag. (Shake)

Should it be $\{kundnr \mid \exists name, ort \ \forall auftragnr, datum \left [\text{KUNDE}(kundnr, name, ort) \land \neg \left (\text{AUFTRAG}(auftragng,datum, kundnr)\right )\right ]\}$ ? :unsure:

Where does $a$ come from? (Wondering)

Ah it should be $\{(k.KUNDNR) \mid \text{KUNDE}(k) \land \forall a(AUFTRAG(a)\land k.KUNDNR\neq a.KUNDNR)\}$,or not? :unsure:

 

[I like Serena]

Should it be $\{kundnr \mid \exists name, ort \ \forall auftragnr, datum \left [\text{KUNDE}(kundnr, name, ort) \land \neg \left (\text{AUFTRAG}(auftragng,datum, kundnr)\right )\right ]\}$ ?

Ah it should be $\{(k.KUNDNR) \mid \text{KUNDE}(k) \land \forall a(AUFTRAG(a)\land k.KUNDNR\neq a.KUNDNR)\}$,or not?

Yep. (Nod)

And we had the same problem in question 5.

 

[mathmari]

And we had the same problem in question 5.

So we we have in question 5 the following ?

Domain Calculus : \begin{align*}\{&kundnr \mid \\ &\exists name, ort \ \forall auftragnr, datum \left [\text{KUNDE}(kundnr, name, ort) \land \neg\text{AUFTRAG}(auftragnr,datum, kundnr)\right ] \\ & \lor \exists auftragnr, datum \ \forall name, ort \left [\text{AUFTRAG}(auftragnr,datum, kundnr)\land \neg \text{KUNDE}(kundnr, name, ort)\right ) ]\}\end{align*}
Tuple Calculus : \begin{align*}\{(k.KUNDNR, a.KUNDNR) &\mid \left [\text{KUNDE}(k) \land \forall a(AUFTRAG(a) \land k.KUNDNR\neq a.KUNDNR)\right ] \\ & \lor\left [ AUFTRAG(a) \land \land \forall k(KUNDE(k)(a.KUNDNR\neq a. KUNDNR)\right ] \}\end{align*}

:unsure:

 

[I like Serena]

So we we have in question 5 the following ?

Tuple Calculus : \begin{align*}\{(k.KUNDNR, a.KUNDNR) &\mid \left [\text{KUNDE}(k) \land \forall a(AUFTRAG(a) \land k.KUNDNR\neq a.KUNDNR)\right ] \\ & \lor\left [ AUFTRAG(a) \land \land \forall k(KUNDE(k)(a.KUNDNR\neq a. KUNDNR)\right ] \}\end{align*}

The result would consist of tuples like $\{ (1,1), (1,3), \ldots\}$. But shouldn't it be a set of KUNDNR like $\{1,3,\ldots\}$?

Which $a.KUNDNR$ do we even have in the first part?

There also seem to be a couple of typos in the last part of the expression. (Worried)

 

[mathmari]

The result would consist of tuples like $\{ (1,1), (1,3), \ldots\}$. But shouldn't it be a set of KUNDNR like $\{1,3,\ldots\}$?

Which $a.KUNDNR$ do we even have in the first part?

There also seem to be a couple of typos in the last part of the expression. (Worried)

Should it maybe be as follows?
\begin{align*}\{(k.KUNDNR) &\mid \left [\text{KUNDE}(k) \land \forall a(AUFTRAG(a) \land k.KUNDNR\neq a.KUNDNR)\right ] \\ & \lor\left [ \exists a \ AUFTRAG(a) \land \forall k\ KUNDE(k)\
(k.KUNDNR\neq a. KUNDNR)\right ] \}\end{align*}

:unsure:

 

[I like Serena]

Should it maybe be as follows?
\begin{align*}\{(k.KUNDNR) &\mid \left [\text{KUNDE}(k) \land \forall a(AUFTRAG(a) \land k.KUNDNR\neq a.KUNDNR)\right ] \\ & \lor\left [ \exists a \ AUFTRAG(a) \land \forall k\ KUNDE(k)\
(k.KUNDNR\neq a. KUNDNR)\right ] \}\end{align*}

Doesn't the $k.KUNDNR$ only apply to the first half to the expression?
The second half has a $\forall k$, so it does not tie to any particular $k.KUNDNR$ does it? (Worried)

 

[mathmari]

Doesn't the $k.KUNDNR$ only apply to the first half to the expression?
The second half has a $\forall k$, so it does not tie to any particular $k.KUNDNR$ does it? (Worried)

Ok... But how can we write this then? At the second part we want to check if the KUNDNR of Auftrag is nont included in KUNDE, or not? I got stuck right now. :unsure:

 

[I like Serena]

Ok... But how can we write this then? At the second part we want to check if the KUNDNR of Auftrag is nont included in KUNDE, or not? I got stuck right now.

How about something like:
\begin{align*}\{KUNDNR &\mid \exists k\left (\text{KUNDE}(k)
\land k.KUNDNR=KUNDNR \land \ldots\right)\lor
\exists a\left (\text{AUFTRAG}(a)
\land a.KUNDNR=KUNDNR \land \ldots\right)
\}\end{align*}

 

[mathmari]

How about something like:
\begin{align*}\{KUNDNR &\mid \exists k\left (\text{KUNDE}(k)
\land k.KUNDNR=KUNDNR \land \ldots\right)\lor
\exists a\left (\text{AUFTRAG}(a)
\land a.KUNDNR=KUNDNR \land \ldots\right)
\}\end{align*}

Ah you mean :
\begin{align*}\{KUNDNR \mid & \exists k\left (\text{KUNDE}(k)
\land k.KUNDNR=KUNDNR \land \forall a(AUFTRAG(a) \land k.KUNDNR\neq a.KUNDNR)\right)\\ &\lor
\exists a\left (\text{AUFTRAG}(a)
\land a.KUNDNR=KUNDNR \land \forall k\ KUNDE(k)\
(k.KUNDNR\neq a. KUNDNR)\right)
\}\end{align*} right? :unsure:

 

[I like Serena]

Yep. (Sun)

 

[mathmari]

We consider the relations AUFTRAG and KUNDE and create an SQL expression for each of the below.

1. Create an expression that lists all possible combinations of customer numbers and locations related to KUNDE.
2. Create an expression that lists all customer names with at least one order.
3. Create an expression that lists all order numbers with customers from Gießen or Wetzlar.
4. Create an expression that lists all customer numbers without an order.I have done the following :

Expression for 1 :
SELECT DISTINCT KUNDNR, ORT
FROM KUNDEExpression for 2 :
SELECT DISTINCT NAME
FROM KUNDE
WHERE k.KUNDNR = a.KUNDNRExpression for 3 :
SELECT DISTINCT AUFTRAGNR
FROM AUFTRAG
WHERE a.KUNDNR = k.KUNDNR AND (k.ORT = Gießen OR k.ORT = Wetzlar)Expression for 4 :
SELECT DISTINCT KUNDNR
FROM KUNDE
WHERE k.KUNDNR != a.KUNDNRAre the expressions correct? :unsure:

 

[I like Serena]

1. Create an expression that lists all possible combinations of customer numbers and locations related to KUNDE.
Expression for 1 :
SELECT DISTINCT KUNDNR, ORT
FROM KUNDE

That will list the customers with the location that is registered for them. That would be 1 record for each customer.
However, it seems that the question asks for each possible combination of each existing customer with each existing location.

Expression for 2 :
SELECT DISTINCT NAME
FROM KUNDE
WHERE k.KUNDNR = a.KUNDNR

Expression for 3 :
SELECT DISTINCT AUFTRAGNR
FROM AUFTRAG
WHERE a.KUNDNR = k.KUNDNR AND (k.ORT = Gießen OR k.ORT = Wetzlar)

Expression for 4 :
SELECT DISTINCT KUNDNR
FROM KUNDE
WHERE k.KUNDNR != a.KUNDNR

The WHERE clause is a condition. Your WHERE clauses refer to k and a, but they are not defined anywhere. (Worried)

 

[mathmari]

That will list the customers with the location that is registered for them. That would be 1 record for each customer.
However, it seems that the question asks for each possible combination of each existing customer with each existing location.

Ah ok.. So do we not have to use "DISTINCT" or just in an other way? :unsure:

The WHERE clause is a condition. Your WHERE clauses refer to k and a, but they are not defined anywhere. (Worried)

Ah yes... So should it be as follows?

Expression for 2 :
SELECT DISTINCT NAME
FROM KUNDE k, AUFTRAG a
WHERE k.KUNDNR = a.KUNDNR

Expression for 3 :
SELECT DISTINCT AUFTRAGNR
FROM AUFTRAG a, KUNDE k
WHERE a.KUNDNR = k.KUNDNR AND (k.ORT = Gießen OR k.ORT = Wetzlar)

Expression for 4 :
SELECT DISTINCT KUNDNR
FROM KUNDE k, AUFTRAG a
WHERE k.KUNDNR != a.KUNDNR :unsure:

 

[I like Serena]

It should be in another way - at least if I understand the question correctly.
They seem to be asking for a cartesian product.
Then again, it does seem to be a bit too advanced given the other questions. :unsure:

DISTINCT has no effect here. That is because KUNDNR is unique in KUNDE. It is a so called primary key.

Questions 2 and 3 look fine now. (Nod)

For question 4 we will also get customers that do have an order. So that is not correct. (Shake)

 

[mathmari]

It should be in another way - at least if I understand the question correctly.
They seem to be asking for a cartesian product.
Then again, it does seem to be a bit too advanced given the other questions. :unsure:

DISTINCT has no effect here. That is because KUNDNR is unique in KUNDE. It is a so called primary key.

Do we maybe write :

Expression for 1 :
SELECT KUNDE.kundnr, KUNDE.ort
FROM KUNDE

:unsure:

Questions 2 and 3 look fine now. (Nod)

So we need in these cases "DISTINCT", right? :unsure:

For question 4 we will also get customers that do have an order. So that is not correct. (Shake)

Ah we have to write that this inequality holds for each a in AUTRAG, don't we? :unsure:

 

[I like Serena]

Do we maybe write :

Expression for 1 :
SELECT KUNDE.kundnr, KUNDE.ort
FROM KUNDE

That is effectively the same as before.

I think it might be:
SELECT k1.kundnr, k2.ort
FROM KUNDE k1, KUNDE k2
:unsure:

So we need in these cases "DISTINCT", right?

In question 2, yes, we can expect that the same customer has multiple orders, so their name will get listed multiple times.
DISTINCT then ensures each name is listed only once.

In question 3 it is not be necessary. Orders are unique. So if we list the AUFTRAGNR's, we will already have at most one of each.

Ah we have to write that this inequality holds for each a in AUTRAG, don't we?

Indeed. (Sweating)

 

[mathmari]

That is effectively the same as before.

I think it might be:
SELECT k1.kundnr, k2.ort
FROM KUNDE k1, KUNDE k2
:unsure:

So that we consider two different columns? :unsure:

In question 2, yes, we can expect that the same customer has multiple orders, so their name will get listed multiple times.
DISTINCT then ensures each name is listed only once.

In question 3 it is not be necessary. Orders are unique. So if we list the AUFTRAGNR's, we will already have at most one of each.

Indeed. (Sweating)

So do we have to write the following ? Expression for 1 :
SELECT k1.kundnr, k2.ort
FROM KUNDE k1, KUNDE k2 Expression for 2 :
SELECT DISTINCT NAME
FROM KUNDE k, AUFTRAG a
WHERE k.KUNDNR = a.KUNDNRExpression for 3 :
SELECT DISTINCT AUFTRAGNR
FROM AUFTRAG a, KUNDE k
WHERE a.KUNDNR = k.KUNDNR AND (k.ORT = Gießen OR k.ORT = Wetzlar)Expression for 4 :
SELECT DISTINCT KUNDNR
FROM KUNDE k
WHERE k.KUNDNR != ALL
$ \ \ \ \ \ $(SELECT KUNDNR
$ \ \ \ \ \ $FROM AUFTRAG)
:unsure:

 

[I like Serena]

Looks right to me. (Nod)