8350 Q 010913 DIS

CSCI 8350
Classroom exercises - DBMS Review
DISCUSSION in boldface

1. Consider relations r(R), s(S), t(T) and u(U) as shown below.

r(R) s(S) t(T) u(U)
A
B

B
C
D

B
A

B
3
4
5
7 a b c a

a b e
7
3
8
5
4
6

a e c d 5
8
7
11

a c e f For each of the following expressions, determine it is valid (i.e., it is a correct way to write a query) or not.
If yes, indicate the result (i.e., resulting schema and rows). If not, explain why.
(a) B (C=9(s))
Valid, with empty result (should still show schema):
B

(b) (r  t)  B (s) r  t is OK, but the intermediate result has two columns (with attributes A and B in the schema), which is not union compatible with B (s) (which has only one attribute B in the schema). So the entire expression is invalid.
(c) Natural join of r and u.
Actually, you were asked to evaluate the expression r |x| s. The two relations (r and s) have join attribute B. The joined result is:
A
B
3
4
7
a b a

2. For the transaction database shown in a slide, how can we “flatten” it to make it a relational table (i.e., in 1NF)? Show the resulting RDB table and identify its primary key.
.

Result (“flattened” table in 1NF, with primary key):

TID
Item
10
A
10
B
10
D
20
A
20
C
20
D
30
A
30
D
30
E
40
B
40
E
40
F
50
B
50
C
50
D
50
E
50
F
3. For the company DB (schemas of tables are shown below), write RA query and SQL query for each of the following. (State any reasonable assumptions only if necessary.)

(1) Find the name of the city for the company which has employees with salary higher than 300,000.
This is a very simple query. Both RA and SQL require two tables: works and company.
RA:
city ( salary > 300000) (works |x| company)
[Note: |x| denotes natural join.]

SQL:
There are several ways of doing this; for example,
Select city
From works, company
Where works.company_name = company.company_name and salary > 300000;