EX03 CISC3000 Exercise 03

Exercise #03 - This exercise reviews Chapter 02.

Question 1

Correct
Mark 1.00 out of 1.00
Flag question

Question text

Consider the following relational database:
employee (ID, person name, street, city)
works (ID, company name, salary)
company (company name, city)

What is the appropriate primary key for employee?
Select one or more:
a. **ID**
b. person name
c. street
d. city

Feedback

The correct answer is: ID

Question 2

Correct
Mark 1.00 out of 1.00
Flag question

Question text

Consider the following relational database:
employee (ID, person name, street, city)
works (ID, company name, salary)
company (company name, city)

What is the appropriate primary key for works?
Select one or more:
a. company name
b. ID
c. salary

Feedback

The correct answer is: ID

Question 3

Correct
Mark 1.00 out of 1.00
Flag question

Question text

Consider the following relational database:
employee (ID, person name, street, city)
works (ID, company name, salary)
company (company name, city)

What is the appropriate primary key for company?
Select one or more:
a. city
b. company name

Feedback

The correct answer is: company name

Question 4

Complete
Mark 3.00 out of 3.00
Flag question

Question text

Consider the following relational database:
employee (ID, person_name, street, city)
works (ID, company_name, salary)
company (company_name, city)
Give an expression in the relational algebra to express each of the following queries:
a. Find the names of all employees who live in city “Miami”.
b. Find the names of all employees whose salary is greater than $100,000.
c. Find the names of all employees who live in “Miami” and whose salary is greater than $100,000.
a. Find the names of all employees who live in city “Miami”.
project name ( select city = “Miami” (employee) )

b. Find the names of all employees whose salary is greater than $100,000.
project name ( select salary >= 100000 ( employee x works ) )

c. Find the names of all employees who live in “Miami” and whose salary is greater than $100,000.
project name ( select city = “Miami” and salary >= 100000 (employee x works) )

Feedback

Question 5

Complete
Mark 1.00 out of 2.00
Flag question

Question text

Consider the following relational database:
employee (ID, person_name, street, city)
works (ID, company_name, salary)
company (company_name, city)
Give an expression in the relational algebra to express each of the following queries:
a. Find the ID and name of each employee who does not work for “BigBank”.
b. Find the ID and name of each employee who earns at least as much as every employee in the database.
_

<> -> not
a. Find the ID and name of each employee who does not work for “BigBank”.
project ID, name ( select company_name <> “BigBank” (works x employee) )

b. Find the ID and name of each employee who earns at least as much as every employee in the database.
project ID, name (select having sum(salary) <= all ( sum(salary) (works x employee)
_

Feedback

Comments

Comment:
b. to find who earns more, so the symbol is wrong

Question 6

Complete
Mark 0.00 out of 4.00
Flag question

Question text

Consider the following relational database:
employee (ID, person_name, street, city)
works (ID, company_name, salary)
company (company_name, city)

Give an expression in the relational algebra to express each of the following queries:

a. Find the ID and name of each employee who works for “BigBank”.
b. Find the ID, name, and city of residence of each employee who works for “BigBank”.
c. Find the ID, name, street address, and city of residence of each employee who works for “BigBank” and earns more than $10000.
d. Find the ID and name of each employee in this database who lives in the same city as the company for which she or he works.
a. Find the ID and name of each employee who works for “BigBank”.
project ID, person_name ( select company_name == “BigBank” (works x employee) )

b. Find the ID, name, and city of residence of each employee who works for “BigBank”.
project ID, person_name, city (select company_name == “BigBank” (works x employee))

c. Find the ID, name, street address, and city of residence of each employee who works for “BigBank” and earns more than $10000.
project ID, name, street, city ( select company_name == “BigBank” and salary > 10000 (works x employee))

d. Find the ID and name of each employee in this database who lives in the same city as the company for which she or he works.
project ID, name ( select employee.city == company.city (employee x company))

Feedback

Comments

Comment:

a. ΠID,person_name(
  employee ⋈ employee.id = works.id(σcompany_name="BigBank"(works)))ΠID,person_name(employee⋈employee.id=works.id(σcompany_name="BigBank"(works)))
b.ΠID,person_name,city(employee⋈employee.id=works.id(σcompany_name="BigBank"(works)))ΠID,person_name,city(employee⋈employee.id=works.id(σcompany_name="BigBank"(works)))
c.ΠID,person_name,street,city(σ(company_name="BigBank"∧salary>10000)(works⋈employee.id=works.idemployee))ΠID,person_name,street,city(σ(company_name="BigBank"∧salary>10000)(works⋈employee.id=works.idemployee))
d.ΠID,person_name(σemployee.city=company.city(employee⋈employee.ID=works.IDworks⋈works.company_name=company.company_namecompany))