Math Problems Vault

Problem 143

For the database from example 35 on p. 162, construct a relational algebra expression giving the list of surnames of employees in whose rooms there is no equipment. Construct a formula of predicate logic defining the same relation.

Example 35: the database has schema $\left\{ \text{Employees}(\text{Number}, \text{FullName}, \text{Department}, \text{Position}, \text{Salary}), \text{Rooms}(\text{EmployeeNumber}, \text{Floor}, \text{RoomNumber}), \text{Equipment}(\text{Floor}, \text{RoomNumber}, \text{Name})\right\}$ , with its state given by:

?

Problem 144

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 called the largest relation $Q$ with attributes $A_{1}, \ldots , A_{n}$ , each tuple $t$ of which, joined with each 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 145

Write SQL queries and corresponding formulas to obtain the following information from the database in example 35 on p. 162, and compute the result:

Example 35: the database has schema $\left\{ \text{Employees}(\text{Number}, \text{FullName}, \text{Department}, \text{Position}, \text{Salary}), \text{Rooms}(\text{EmployeeNumber}, \text{Floor}, \text{RoomNumber}), \text{Equipment}(\text{Floor}, \text{RoomNumber}, \text{Name})\right\}$ , with its state given by:

?

(a)

find all employees with a salary greater than 5500;

(b)

find all departments that have 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 who receive a salary from 6000 to 6500 and do not work on the third floor;

(e)

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

Problem 146

Determine which of the integrity constraints given in example 44 on the preceding page hold for the state of the database from example 35 on p. 162.

Example 35: the database has schema $\left\{ \text{Employees}(\text{Number}, \text{FullName}, \text{Department}, \text{Position}, \text{Salary}), \text{Rooms}(\text{EmployeeNumber}, \text{Floor}, \text{RoomNumber}), \text{Equipment}(\text{Floor}, \text{RoomNumber}, \text{Name})\right\}$ , with its state given by:

Example 44: three integrity constraints for the database of example 35:

the key constraint on Employees (attribute Number is a key): $(\forall n)(\forall x)(\forall y)(\forall z)(\forall v)(\forall x_{1})(\forall y_{1})(\forall z_{1})(\forall v_{1})(\text{Employees}(n, x, y, z, v) \wedge \text{Employees}(n, x_{1}, y_{1}, z_{1}, v_{1}) \rightarrow x \approx x_{1} \wedge y \approx y_{1} \wedge z \approx z_{1} \wedge v \approx v_{1})$ ;
the reference constraint (every employee who has a room in Rooms must occur in Employees): $(\forall n s)(\forall x)(\forall y)(\text{Rooms}(n s, x, y) \rightarrow (\exists z)(\exists u)(\exists v)(\exists w) \text{Employees}(n s, z, u, v, w))$ ;
the value constraint (every employee’s salary lies in the interval $(1000, 25000)$ ): $(\forall x)(\forall y)(\forall z)(\forall v)(\forall o)(\text{Employees}(x, y, z, v, o) \rightarrow 1000 < o \wedge o < 25000)$ .

?

Problem 147

(a)(b)(c)

Write formulas expressing the following integrity constraints for the database from example 35 on p. 162, and determine which of them hold for its given state:

Example 35: the database has schema $\left\{ \text{Employees}(\text{Number}, \text{FullName}, \text{Department}, \text{Position}, \text{Salary}), \text{Rooms}(\text{EmployeeNumber}, \text{Floor}, \text{RoomNumber}), \text{Equipment}(\text{Floor}, \text{RoomNumber}, \text{Name})\right\}$ , with its state given by:

?

(a)

in the relation Rooms, the set of attributes (EmployeeNumber, RoomNumber) is a key;

(b)

for every person in the relation Employees, their 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.