path: root/assignments/exam/README.md

# Practice Exam
## 1

A real estate company uses a database to store information about customers, property, and contracts.
The following relations are used in the database:

Customer:
  * Customer number (unique)
  * name
  * mailing address
  * Balance
  * lawyer name
  * Discount

Contract:

  * Customer number (unique)
  * Agent number (unique)
  * Property_ID (one per contract)
  * Date of contract
  * Property addresses
  * Property value
  * Type
  * Number of rooms
  * Land size
  * Built size
  * Tax value

The following functional dependencies apply:

* customer_no -> name
* customer_no -> mail_address
* customer_no -> balance
* customer_no -> lawyer_name
* customer_no -> discount
* {customer_no, Agent_no} -> propert_id
* {customer_no, Agent_no} -> date_contract
* property_id -> property_address
* property_id -> value
* property_id -> type
* property_id -> number_rooms
* property_id -> land_size
* property_id -> built_size
* property_id -> tax_value

Transform these relations into 3NF (please use only the attributes described above, and do not add any new attributes).

Normal forms:

1. 0 multi-valued attributes
2. 0 functional dependencies
3. 0 transitive dependencies

Before:

```text
* Customers(_id_, name, mail_address, balance, lawyer_name, discount)
* Contract(_id_, customer_id, agent_id, date_of_contract, property_address, property_value, type, number_of_rooms, land_size, built_size, tax_value)
```

After:

```text
* customers(_id_, name, mail_address, balance, lawyer_name, discount)
* contracts(_id_, _customer_id_, _agent_id_, _property_id_, date_of_contract)
* properties(_id_, address, value, type, number_of_rooms, land_size, built_size, tax_value)
```

## 2

Draw a class diagram for the following situation.

* An airline company has a number of planes.
* The attributes of a plane include plane_id (unique), name, and vendor.
* The airline company serves many destinations.
* The attributes of a destination include destination_id (unique), name, and flight_date.
* Each plane flies to one or more destinations;
  * or it may be used as back-up;
  * or it may be under maintenance, and therefore will not fly to any destination.
* For each plane undergoing maintenance, the company records the name of the technician and the type of maintenance performed.
* A destination may be served by one or more planes.
* Each plane’s service charges vary by the number of destinations the plane serves.
* The airline company maintains records of the service charges for each plane when it flies to a certain destination.
* At the end of each year, the airline company applies a depreciation percentage to each plane.
* The airline company calculates each plane’s percent depreciation based on the amount of service charges and the total hours of flying for that plane.

```text
planes(_id_, name, vendor)
destinations(_id_, name, flight_date)
maintenance(_id_, technician_id, plane_id, type)
```

```text
-----------
| Planes   |
-----------
| + id     |
| + name   |
| + vendor |<---------------------
-----------|                     |
| + calculateServiceChargeForFlightTo(destination)          |
-----------                      |
    |                            |
 has_many                        |
    |                            |
    V                            |
-----------------                |
| maintenance   |                |
-----------------                |
| id            |                A
| technician_id |                |
| plane_id      |-- belongs to --|
| type          |                |
-----------------                |
|               |                |
-----------------                |
      |                          |
  belongs_to                     |
      |                          |
      V                          |
-----------------                |
| technician    |                |
-----------------                |
| id            |                |
-----------------                |
|               |                |
-----------------                |
                                 |
---------------                  |
| destination |                  |
---------------                  A
| id          |                  |
| name        | ---belongs to ---|
| plane_id    |                  |
| flight_date |                  |
---------------                  |
|             |                  |
---------------                  |
      A                          |
      |                          |
  belongs_to                     |
      |                          |
------------------               |
| service charge |               A
------------------               |
| id             |               |
| plane_id       | -- belongs to |
| destination_id |
------------------
|                |
------------------
```

## 3

Consider the following three relations:

```text
Manager(name, address, specialization, salary)
Employee(name, address, rank, salary)
Project (proj_num, emp_name, manager_name, duration)
```

Write the SQL queries corresponding to the following questions.

a. Find the number of different employees who are assigned to projects.

```sql
SELECT count(distinct(e.name))
FROM Employee e
WHERE e.name IN (SELECT DISTINCT(emp_name) FROM PROJECT);
```
or

```sql
SELECT count(distinct(e.name))
FROM Employee e
INNER JOIN Project p on p.emp_name = e.name;
```

b. Find the managers' average salary.

```sql
SELECT AVG(m.salary)
FROM Manager m;
```

c. List the name and number of projects supervised by each manager.

```sql
SELECT p.manager_name, count(p.proj_numb)
FROM Project p
GROUP BY p.manager_name;
```

d. Show the names and salaries of managers who supervise employees of rank "beginner" in ascending
order of salary (use a subquery).

```sql
SELECT m.name, m.salary
FROM Manager m
WHERE m.name in (
  SELECT p.manager_name
  FROM Project p
  INNER JOIN Employee e on e.name = p.emp_name
  WHERE e.rank = 'beginner'
)
ORDER BY m.salary ASC;
```

or

```sql
SELECT m.name, m.salary
FROM Manager m
WHERE m.name in (
  SELECT p.manager_name
  FROM Project p
  WHERE p.emp_name IN (
    SELECT e.name
    FROM Employee e where e.rank = 'beginner'
  )
)
ORDER BY m.salary ASC;
```

e. Show the names and salaries of managers who supervise employees of rank "beginner" in
descending order of salary (do not use a subquery).

```sql
SELECT m.name, m.salary
FROM Manager m
INNER JOIN Project p on p.manager_name = m.name
INNER JOIN Employee e on e.name = p.emp_name
WHERE e.rank = 'beginner'
ORDER BY m.salary ASC;
```

f. Show the names and salaries of supervisors who supervise more than 20 employees.

```sql
SELECT m.name, m.salary
FROM Manager m
WHERE m.name IN (
  SELECT p.manager_name
  FROM Project p
  GROUP BY p.manager_name
  HAVING COUNT(p.manager_name) > 20
):
```

## 4

Consider the following database.

```text
TRAVEL-AGENT (name, age, salary)
CUSTOMER (name, departure_city, destination, journey_class)
TRANSACTION (number, cust_name, travel_agent_name, amount_paid)
```

a. Write a SQL query to display the names of all travel agents who arranged trips for customer "John Smith".

```sql
SELECT ta.name
FROM "Travel-Agent" ta
WHERE ta.name IN (
  SELECT DISTINCT(travel_agent_name)
  FROM TRANSACTION t
  WHERE t.cust_name = 'John Smith'
);
```

b. Define indexes on selected attributes to speed up your query. Justify your selections.

```sql
CREATE INDEX ON "Travel-Agent" (name); -- Joining on travel_agent.name
CREATE INDEX ON Transaction (cust_name); -- filtering by cust_name
```

## 5

Suggest an appropriate recovery technique for each of the following situations.

a. You were working from home and updating a database at work when the transaction was aborted.

Rollback the transaction and try again.

b. A customer service representative entered an incorrect data price for a customer transaction.
Several weeks after the accounting department has processed the transaction,
the customer returned and discovered the mistake.

Add a new compensating transaction to fix the mistake.

## 6

Contrast two-tier architecture with three-tier architecture.

Two tier architectures

```text
|--------|
| Server | * storage logic
|--------|
| Client | * processing logic
|        | * presentation logic
|--------|

|--------|
| Server | * storage logic
|        | * processing logic
|--------|
| Client | * presentation logic
|--------|
```

3 tier architecture

```text
|--------|
|   db   | * storage logic
|--------|
|  app   | * processing logic
|--------|
| client | * presentation logic
|--------|
```

## 7

a. Briefly contrast transient data with periodic data in data warehousing.

> Transient data: in which changes to existing records are written over previous records, thus destroying the previous data content.
> Periodic data: data that are never physically altered or deleted once they have been added to the store.

b. Briefly discuss the characteristics of a surrogate key, as used in a data warehouse or data mart.

Surrogate Key

Every key used to join the fact table with a dimension table should be a surrogate (system assigned) key.

Why?

* Business keys change, often slowly, over time and we need to remember old and new business key values for the same business object.
* Using a surrogate key also allows us to keep track of different non-key attribute values for the same product production key with several surrogate keys, each for the different package sizes.
* Surrogate keys are often simpler and shorter
* Surrogate keys can be of the same length and format for all keys