Math Problems Vault

Problem 274

Let the following attributes be given:

\left(A,\left\{ a_{1}, a_{2}, a_{3}\right\} \right), \quad (B, \omega ), \quad \left(C,\left\{ c_{1}, c_{2}, c_{3}\right\} \right), \quad (D, \omega ).

Let the relation $R$ with schema $(A, B, C)$ equal

R=\left\{ \left(a_{1}, 7, c_{1}\right),\left(a_{1}, 5, c_{2}\right),\left(a_{2}, 7, c_{1}\right),\left(a_{3}, 4, c_{3}\right)\right\} ,

and let the relation $S$ with schema $(C, D)$ equal

S=\left\{ \left(c_{1}, 2\right),\left(c_{2}, 8\right),\left(c_{2}, 7\right),\left(c_{1}, 4\right),\left(c_{2}, 5\right)\right\} .

Determine the results of the following relational algebra expressions:

?

(a)

$\left(R: A \approx a_{1}\right)$ ;

(b)

$R[B] \times S[D]$ ;

(c)

$R \rtimes S$ ;

(d)

$R[A, C] \bowtie R[C]$ ;

(e)

$R \underset {\ltimes }{R. C \not\asymp S. C \wedge R. B>S. D} S$ .

Problem 275

Determine whether the operations of filtering and projection are distributive over union, intersection, and difference, that is, whether the following equalities hold:

?

(a)

$(R \cup S: \Theta )=(R: \Theta ) \cup (S: \Theta )$ ;

(b)

$(R \cap S: \Theta )=(R: \Theta ) \cap (S: \Theta )$ ;

(c)

$(R \backslash S: \Theta )=(R: \Theta ) \backslash (S: \Theta )$ ;

(d)

$(R \cup S)[X]=R[X] \cup S[X]$ ;

(e)

$(R \cap S)[X]=R[X] \cap S[X]$ ;

(f)

$(R \backslash S)[X]=R[X] \backslash S[X]$ .

Problem 276

For the database in fig. 7 on the previous page, construct a relational algebra expression giving the list of surnames of the employees in whose rooms there is no equipment at all. Construct a formula of predicate logic defining the same relation.

Fig. 7: State of the database.

?

Problem 277

Let there be relations $R$ and $S$ with schemas $(A_{1}, \ldots , A_{n}, B_{1}, \ldots , B_{m})$ and $(B_{1}, \ldots , B_{m})$ respectively. The quotient $R / S$ of dividing the relation $R$ by the relation $S$ is the largest relation $Q$ with attributes $A_{1}, \ldots , A_{n}$ each of whose tuples $t$ , joined with every tuple $s \in S$ , belongs to $R$ , that is, $Q \times S \subseteq R$ . Construct a relational algebra expression equivalent to $R / S$ , and a formula of predicate logic defining this relation.

?

Problem 278

Write SQL queries and the corresponding formulas to obtain the following information from the database in fig. 7 on the preceding page, and compute the result:

?

(a)

find all employees with a salary greater than 5500;

(b)

find all departments in which there are employees with a salary greater than 8000;

(c)

compile a list of positions and the salaries received for them;

(d)

compile a list of employees of the sales department earning a salary from 6000 to 6500 and not working on the third floor;

(e)

compile a list of rooms where all employees receive a salary less than 7500.

Fig. 7: State of the database.

Problem 279

(a)(b)(c)

Determine which of the following integrity constraints hold for the state of the database in fig. 7 on p. 83:

?

(a)

$(\forall n)(\forall x)(\forall y)(\forall z)(\forall v)\left(\forall x_{1}\right)\left(\forall y_{1}\right)\left(\forall z_{1}\right)\left(\forall v_{1}\right)$

\begin{aligned} (\text{ Сотрудники }(n, x, y, z, v) & \wedge \text{ Сотрудники }\left(n, x_{1}, y_{1}, z_{1}, v_{1}\right) \rightarrow \\ & \left.\rightarrow x \approx x_{1} \wedge y \approx y_{1} \wedge z \approx z_{1} \wedge v \approx v_{1}\right) ; \end{aligned}

(b)

$(\forall n s)(\forall x)(\forall y)($ Комнаты $(n s, x, y) \rightarrow$

\rightarrow (\exists z)(\exists u)(\exists v)(\exists w) \text{ Сотрудники }(n s, z, u, v, w)) \text{; }

(c)

$(\forall x)(\forall y)(\forall z)(\forall v)(\forall o)($ Сотрудники $(x, y, z, v, o) \rightarrow$

\rightarrow 1000<o \wedge o<25000).

Fig. 7: State of the database.

Problem 280

(a)(b)(c)

Write formulas expressing the following integrity constraints for the database in fig. 7 on p. 83, determine which of them hold for the given state of it:

?

(a)

in the relation Комнаты (Rooms) the set of attributes (EmployeeNumber, RoomNumber) is a key;

(b)

for every person from the relation Сотрудники (Employees) a workplace is defined in the relation Комнаты (Rooms);

(c)

in the relation Комнаты (Rooms) the numbers of all rooms on the second floor are greater than 10 but less than 20, and the numbers of all rooms on the third floor are greater than 20.

Fig. 7: State of the database.