Question

employee(employee-id, employee-name, street, city)

works(employee-id, company-id, salary)

company(company-id, company-name, city)

manages(employee-id, manager-id)

Consider the data base above where the primary keys are in bold

For each of the questions give the following. (NOTICE the Relations on the given database it isn't like the ones that are commonly posted they are slightly different)

(1) a relational algebra expression,

(2) a tuple relational calculus expression, and

(3) a domain relational calculus expression.

f. Find all employees in the database who do not work for the First Bank Corporation.

g. Find all employees in the database who earn more than each employee of Small Bank Corporation.

h. Assume that the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located.

Answer 1

f.

1) Πe-name (σc-name ≠ 'First Bank Corporation' (works))

2) {t | ∃ w ∈ works (w[c-name] ≠ 'First Bank Corporation' ∧ t[e-name] = w[e-name])}

3 {<p>| ∃ c, s (<p,c,s> ∈ works ∧ c ≠ 'First Bank Corporation')}

g.

1) Πe-name (works) – (Πworks.e-name (works ⋈(works.salary ≤ works2.salary ∧ works2.c-name = 'Small Bank Corporation') ρworks2(works)))

2) {t | ∃ w ∈ works (t[e-name] = w[e-name] ∧ ∀ s ∈ works (s[c-name] = 'Small Bank Corporation' ⇒ w[salary] > s[salary]))}

3) { <p>| ∃ c, s ( <p,c,s> ∈ works ∧ ∀ p2, c2, s2 ( <p2,c2,s2>∈ works ∧ c2 = 'Small Bank Corporation' ⇒ s > s2))}

h.

1) Πc-name (company ÷ (Πcity(σc-name = 'Small Bank Corporation' (company))))

2) {t | ∀s ∈ company(s[c-name] = 'Small Bank Corporation' ⇒ ∃ r ∈ company (t[c-name] = r[c-name] ∧ r[city] = s[city]))}

3) { <co>| ∀ co2, ci2 ( <co2.ci2>∈ company ∧ co2 = 'Small Bank Corporation' ⇒ <co2,ci2>∈ company)}​