COSC$4401-$ Database Theory and Practice

Spring $2024$

Homework $1$

Due $2/4/2024$

Figure $1.$ Employee database.

$1)$ Consider the Employee database of Figure $1.$ What is the appropriate primary key for each relation?

$2)$ Consider the Employee database of Figure $1.$ Give a relational algebra expression to perform each of the following queries:

$-$ Find the name of each employee who lives in city $$Dallas$$

$-$ Find the name of each employee who lives in $$Dallas$$ and whose salary is greater than $$100000$

$-$ Find the name of each employee who does not work for $$BigDallas$$

$$

Figure $2.$ Bank database.

$3)$ Consider the Bank database of Figure $2.$ What is the appropriate primary key for each relation?

$4)$ Consider the Bank database of Figure $2.$ Give a relational algebra expression to perform each of the following queries:

$-$ Find the name of each branch located in $$Houston$.$

$-$ Find the ID of each borrower who has a loan in branch $$Downtown$$

$-$ Find the name of each borrower who has a loan in branch $$Downtown$$