DMDB 4

Relational algebra has Bag semantics

• True

• False

Answer explanation

Relational algebra has set semantics, while SQL has bag semantics. However, standard set operations like UNION follow set semantics in SQL.

Assume |app| = 10. Then SELECT * FROM app, app as a has ... tuples

• 10

• 100

• 20

• 1000

• Cannot be said unless the schema is known

Answer explanation

SELECT * FROM a,b in SQL behaves like the cross product in RA. Thus, you can just multiply the size of both tables (here it's just one of size 10. Thus we have 100 tuples).

Which of the following constraints are set for PRIMARY KEYs in SQL?

• UNIQUE

• NOT NULL

• SERIAL

• FOREIGN KEY

• NONE

To load the file /tmp/users.csv into the users table, you need to execute...

• COPY users FROM '/tmp/users.csv' USING FORMAT csv;

• INSERT users FROM '/tmp/users.csv' USING FORMAT csv;

• COPY users FROM '/tmp/users.csv' WITH FORMAT csv;

• LOAD users FROM '/tmp/users.csv' WITH FORMAT csv;

Answer explanation

Which are reasons that creating views are beneficial?

• Privacy

• Usability

• Performance

• Cost

How many tuples does the following query return:

SELECT * FROM app WHERE bytes_received < ALL (SELECT bytes_sent FROM app)?

• 0

• 2

• 7

• 10

Answer explanation

We're interested in tuples, which have less bytes_received then all bytes_sent from the table. The smallest bytes_sent is 29. Thus, only the tuples with bytes_received = 10 are selected. NULL is excluded (since NULL < 29 is unknown).

How many tuples does the following query return (empty entries are NULL) :

SELECT * FROM app WHERE bytes_received < ALL (SELECT bytes_received FROM app)

• 0

• 2

• 7

• 10

Answer explanation

First of all, we have a strict inequality --> nothing is smaller than the minimum in the first place. Only NULLs might matter however NULL < 10 is unknown which means that we also don't select the NULLs giving 0 tuples. Alternatively: You see that there are 3 groups: NULL, 30 and 10 - 10 is the smallest value. None of those groups has bytes_received < 10. Especially NULL < 10 is unknown.

R1 UNION ALL R2 can be written as R1 ∪ R2 in RA

• True

• False

Answer explanation

UNION is set semantics, but UNION ALL is bag semantics. Since RA has set semantics, but we enforce bag semantics with UNION ALL, they may not give the same result, meaning they aren't equivalent.

R1 - R2 is equivalent to ... in SQL

• R1 UNION R2

• R1 NOT R2

• R1 EXCEPT R2

• R1 - R2

• R2 EXCEPT R1

SELECT A as a1, B as a2 FROM R; is equivalent to