Homework 1 Problem 1 (15 points)
Consider the employee database of Figure 1. Give an expression in the relational algebra to express each of the following queries:
a. Find the name of each employee who works for ¡°BigBank¡±.
b. Find the name and city of residence of each employee who works for ¡°BigBank¡±.
c. Find the name, street address, and city of residence of each employee who works for
¡°BigBank¡± and earns more than $10000.
d. Find the name of each employee in this database who lives in the same city as the company for
which she or he works.
Problem 2 (5 points)
Figure 2: Bank database
Consider the bank database of Figure 2.
Give an expression in the relational algebra for each of the following queries:
a. Find each loan number with a loan amount greater than $10000
b. Find the ID of each depositor who has an account with a balance greater than $6000.
c. Find the ID of each depositor who has an account with a balance greater than $6000 at the
¡°Uptown¡± branch.
Problem 3: (35 points)
Write the following queries in relational algebra, using the university schema (see schema in Lecture 02, it is published on Canvas).
a. Find the ID and name of each instructor in the Physics department.
b. Find the ID and name of each instructor in a department located in the building ¡°Watson¡±. c. Find the ID and name of each student who has taken at least one course in the ¡°Comp. Sci.¡±
department.
d. Find the ID and name of each student who has taken at least one course section in the year
e. Find the ID and name of each student who has not taken any course section in the year 2018.
Problem 4: (35 points)
The database for an airline keeps track of customers and their reservations, flights and their status, seat assignments on individual flights, and the schedule and routing of future flights.
E-R diagram is below.
Please provide a set of relational schemas, and a list of constraints, including primary-key and foreign-key constraints.