Objectives: After completion of this assignment, you will be able to: Translate relational algebra expressions Express queries in relational algebra expression Use the various operations of relational...

Objectives:

After completion of this assignment, you will be able to:

• Translate relational algebra expressions


• Express queries in relational algebra expression


• Use the various operations of relational algebra

Total: 60 points (Online grade will be out of 60)

Upload your answers in a zipped folder (folder’s name containing your name) on Blackboard to Assignment #1 Submission.

Problem I: Relational Algebra (5 points each)

Suppose we have a relation Employees (SSN, name, department, salary).
For each of the following queries, either write the query in relational algebra and explain informally why your answer works, or explain why it cannot be written in relational algebra.
You may use sequences of assignments or expression trees if you like. You may use only the following operators of relational algebra: union, intersection, difference, selection, projection, products, joins (theta and natural), renaming.

1. Find all department(s) with at least two employees of different names.


2. Find the department(s) with the most people.


3. Find the names of employee(s) with the highest salary.


4. For each employee, find their name and their supervisor’s name.


5. Find the supervisor with the lowest salary


6. Find the number of supervisor in every department.

Problem 2: (5 points each)

Consider the following database that contains information about people, vacation locations, and when those people visited them.
The relations are:
• Person(ID, Name)
• Location(PlaceName, Country, MainAttraction)
• Visited(ID, PlaceName, Year).
You should assume that place names are unique in the world.
Consider the schema given above.
(a) (5 points) Give a primary key for each relation. Are there any relations for which there is an alternate candidate key which you have not chosen as the primary key? Why or why not?
(b) ( 5 points) State all referential integrity constraints (inclusion dependencies) that should hold on these relations.
( c) (5 points) Explain how the difference between an outer join and an inner join. What could be the use of each?
Explain what each of the following relational algebra queries would return:

1. p_PlaceName
   s_{Year(Visited)}
   


2. p_Name(Person) ? (p_ID(Person) - p_ID(Visited)))


3. p_Name(Person) ? (p_ID,_PlaceName(Visited ÷ (p_PlaceNameLocation)))