MHB Relation between Sets AUFTRAG & KUNDE

[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)