# IoD exercise 1

### Group members: [Name, matriculation number], [name, matriculation number], [name, matriculation number]
#### Insert all group members by double-clicking on this cell.

## Exercise 1.1 (Database Architecture) (5 pts)

1\. Suppose you query a database. How is the query processed with respect to the five layers of the database architecture? Describe what happens in each layer.

### [ Please replace this text with your answer. ]

2\. Explain the term 'indexing' and why buffers are useful in databases.

### [ Please replace this text with your answer. ]

3\. What does data independence mean and why is an important feature of database systems?

### [ Please replace this text with your answer. ]

## Exercise 1.2 (Query Languages) (16 pts)

For the Chinook database ([schema](chinook.png)), formulate the following queries as expressions in both relational algebra and SQL:

1. Find the ID and the name of all tracks where the artist is also the composer.
2. Find the ID, last name and first name of all employees who report to employees which have been hired after themselves.
3. Find the ID, invoice date, and total amount (attribute Total) of the invoice with the highest total amount in Germany.
4. Find the managers of employees supporting Brazilian customers.

**Note**
To run your SQL statements against the included database, you may need to use quotation marks for table and column names:

SELECT t."coolColumn" FROM "TopTable" t

Relational algebra:

Query 1
### [ Please replace this text with your answer. ]

Query 2
### [ Please replace this text with your answer. ]

Query 3
### [ Please replace this text with your answer. ]

Query 4
### [ Please replace this text with your answer. ]

SQL:

# Start the code with '%sql'. For linebreaks, insert \ at the end of line.
# Query 1
# YOUR CODE HERE
raise NotImplementedError

# Query 2
# YOUR CODE HERE
raise NotImplementedError

# Query 3
# YOUR CODE HERE
raise NotImplementedError

# Query 4
# YOUR CODE HERE
raise NotImplementedError

## Exercise 1.3 (TRC, DRC) (9 pts)

Suppose you have the following relations:<br>
- `employee(empid, name, salary)` which contains for every employee their name and their salary,
- `flights(flightno, from, to, distance, departtime, arrivaltime)` which contains for every flight the flight number, the start location and destination, the travel distance as well as the departure and arrival time.
- `aircraft(aircraftid, manufacturer, model, range)` which contains an id for every aircraft, its manufacturer, model and the maximum distance it can travel without refueling,
- `certified(empid, aircraftid)` which indicates which employee(s) is/are certified to fly which aircraft.

<br>Formulate the following queries as expressions in tuple relational calculus (TRC) and domain relational calculus (DRC):<br>
1. Find the names of employees who are certified to fly aircraft manufactured by Boeing.
2. Find the aircraft ids of all aircraft that can be used on at least one non-stop flight (i.e. where the
`aircraft.range > flights.distance`) from Vancouver to Tokyo.
3. Find the names of pilots who can operate planes with a range greater than 3000 miles but are not certified on any aircraft manufactured by Boeing.
4. Find the employee id’s of the employees who make the highest salary.

### [ Please replace this text with your answer. ]