# Assignment 3. Relational Algebra and Indexing

\$35.00

## Description

Objectives
In this assignment, you need to complete two tasks. In Task 1, you will be trained to write relational algebra queries. In Task 2, you will be trained to create indexes to speed up query performance.

In addition, you will get familiar with LaTeX (the de facto standard for the communication and publication of scientific documents), and learn more features about jupyter notebook.

Task 1. Relational Algebra (10 points)
You will use the similar bank database with A2. But with more tables and colomns. The database has 7 tables. The following shows their schemas.

Customer = {customerID, firstName, lastName, brithDate, income}
Account = {accNumber, type, balance, branchNumberFK-Branch}
Owns = {customerIDFK-Customer, accNumberFK-Account}
Transactions = {transNumber, accNumberFK-Account, amount, date, description}
Employee = {sin, firstName, lastName, salary, startDate, branchNumberFK-Branch}
PersonalBanker = {customerIDFK-Customer, sinFK-Employee}
Branch = {branchNumber, branchName, street, numberEmployees, managerSINFK-Employee, budget}
Write relational algebra queries to answer questions (1) to (10). Your answer to each question should consist of a single relational algebra query. You may use input relation names to differentiate between attributes with the same name in the results of a join or Cartesian product (such as referring to Employee.firstName or Customer.firstName in the results of the Cartesian product of Employee and Customer).

Preliminary
To write a relational algebra query in a cell, please first switch the cell to the Markdown cell. Then, you can type LaTeX equations in the cell. Here is a list of common symbols used by relational algebras.

Selection ( σ )
Projection ( π )
Union ( ∪ )
Intersect ( ∩ )
Set Difference ( − )
Cross Product ( × )
Rename ( ρ )
Join ( ⋈ )
Conjunction ( ∧ )
Disconjunction ( ∨ )
Greater Than or Equal To ( ≥ )
Less Than or Equal To ( ≤ )
Next, I am going to show you some RA queries. You can use them as training examples to learn how to write an RA query using latex equations.

Example 1. sID of all students who have earned some grade over 80 and some grade below 50. (See Slide 34)

πsID(σgrade>80(Took))∩πsID(σgrade<50(Took)) Example 2. Student number of all students who have taken CMPT 354 (See Slide 35) πsID(σOffering.oID=Took.oID∧cNum=354(Offering×Took)) Example 3. The names of all students who have passed a breadth course (grade >= 60 and breadth = True) with Martin (See Slide 44)

Queries (1 point per question)
1. The customer IDs, first names, last names and incomes of customers who have an account at a branch with a budget no more than \$1,000,000

2. The first and last names of customers who were born after 1st of January 1985 and whose income is less than \$75,000.

3. The SINs, first and last names and salaries of employees who are both personal bankers and managers

4. The customer IDs of customers who own a joint account (an account that is owned by more than one customer)

5. The customer IDs of customers whose accounts have no transactions with amounts of which the absolute value is less than \$5,000 (i.e. all their transactions are either greater than or equal to \$5,000 or less than or equal to -\$5,000).

6. The branch names of branches that employ at least one employee whose last name is “Martin”, and at least one employee whose last name is “Wang”; note that branch name is not a candidate key

7. The SINs and salaries of employees who earn more than the manager of their branch

8. The first names, last names and birth dates of customers who own an account in the Vancouver branch and the first names, last names and start dates of employees who work in the Vancouver branch (i.e. one query that returns one list of first and last names and dates of these 2 groups of people)

9. The customer IDs of customers who have personal bankers in either the Vancouver or Metrotown branches (note that the personal bankers must be distinct employees as an employee only works at one branch)

10. You are to find the SINs, and first and last names of employees who own an account in the branch in which they work.

In this task, you will be asked to i) select suitable indexes to speed up query performance and ii) examine the query plan of an SQL query.

We are going to use a new database called flights.db. You can find it in A3.zip. In the database, there is a single table, called FLIGHTS. The following shows its schema:

FLIGHTS (fid, year, month_id, day_of_month, day_of_week_id, carrier_id, flight_num, origin_city, origin_state, dest_city, dest_state, departure_delay, taxi_out, arrival_delay, canceled, actual_time, distance)

Note that this task only needs to use four attributes: carrier_id, origin_city, actual_time, and dest_city.

Questions
1. Consider the following queries:

sqlite
(a): SELECT DISTINCT carrier_id
FROM Flights