---
title: Database Systems Lab 5
---
<h1 style='border: none'><center>Database Systems Lab 5</center></h1>
<h1 style='border: none'><center>Intermediate SQL</center></h1>
<h5><center>The Islamic University of Gaza<br>Engineering Faculty<br>Department of Computer Engineering</center></h5>
<h6>Authors: Usama R. Al Zayan & Rasha E. Kahil<span style="float:right">2023/03/17</span></h6>

---

## Expecteed outcomes
* To be introduced to Database Authorization commands and concepts.
* To learn how to define, alter, and use Views and Materialized Views in PostgreSQL.
* To practice working with dates in SQL.
* To learn how to import CSV file into a PostgreSQL table.

## Lab 5: Time and Plan

| Tasks | Timing |
| -------- | -------- |
| Quiz 5 | 10 min |
| Task 1 | 10 min |
| Task 2 | 40 min |
| Task 3 | 15 min |
| Task 4 | 20 min |

## Lab Tasks

### Task 1 (5 marks)

1. Create a new role, and tell us: Is the default for a role inherit or noinherit?
Use the following query to check it.

    Select  *  from pg_roles;

2. How many concurrent connections to PostgreSQL can a user make by default?
Is this command valid?

    alter user test with connection limit 2;

### Task 2 (15 marks)

3.
a. Create a new database called `realdb`.
b. Create a new schema in `realdb` and name it `auth`.
c. Create the users [`Zidane`, `Ronaldo`, `Messi`, `Kaka`].
d. Create the roles [`coach`, `player`].
e. Grant the permissions necessary to be able to create a table to the role `coach`.
f. Grant `coach` to `Zidane`.
g. Using other DBeaver window; log in as `Zidane`, also perform actions h, and i as `Zidane`.
h. Create a table named `real_players`.
i. Grant the permissions necessary to view and update the table to the role `player`, with grant option.
j. Grant `player` role to `Ronaldo` and `Kaka`.
k. Using two other DBeaver windows; log in as `Messi` and as `Ronaldo`.
l. Try accessing the table from both connections.
m. Let `Ronaldo` insert some data and view it from `Zidane`’s connection.
n. Unfortunately, `Zidane` have decided to leave. Drop his user.
Note: Revoke the role coach form him and reassign real_players table to coach.
o. Try accessing the table `real_players` from `Ronaldo`’s connection. Why is this the case?

Ronaldo did not leave the club. We want him to be able to access the table. What shall we change in the previous routine so that Ronaldo can still access the table even after Zidane leaves?

### Task 3 (10 marks)

Use the previously created `all_sections` view to create another view named `all_student_sections` displaying all sections taken by all students. Use your new view to answer the following queries
1. Find all student taking two different sections in a semester in the same time slot.
2. Find students who have attended the maximum number of sections in a single semester in our university
3. Use explain analyze on both of your quires and record the results.

### Task 4 (10 marks)

We wish to materialize our “all_student_sections” view.
1. Create a new materialized view and name it `mat_all_student_sections`.
2. Analyze both queries of the previous lab work and compare the results. Can you explain it?

###### tags: `Database Systems` `IUG` `Computer Engineering`

<center>End Of Lab 5</center>