CS3402 Database Systems CS3402 Tutorial 5:
1. Answer:
(a) T1 × T2
Tutorials
P
Q
R1
A
B
R2
10
a
5
10
b
6
10
a
5
25
c
3
10
a
5
10
b
5
15
b
8
10
b
6
15
b
8
25
c
3
15
b
8
10
b
5
25
a
6
10
b
6
25
a
6
25
c
3
25
a
6
10
b
5
(b) T1 ⋈..T2
(c) T1 ⋈..T2
(d) T1 ⋈..T2
P
Q
R1
A
B
R2
10
a
5
10
b
6
10
a
5
10
b
5
25
a
6
25
c
3
P
Q
R1
A
B
R2
15
b
8
10
b
6
15
b
8
10
b
5
P
Q
R1
A
B
R2
15
b
8
10
b
6
15
b
8
25
c
3
15
b
8
10
b
5
25
a
6
25
c
3
25
a
6
10
b
5
10
a
5
25
c
3
(e) T1 ∗ T2
P
Q
R
A
B
10
a
5
10
b
25
a
6
10
b
CS3402 Database Systems Tutorials
2. Answer:
(a) Find the SSn (social security number) of all employees who are not supervisors
𝜋 (EMPLOYEE) − 𝜋 _ (EMPLOYEE)
(b) Find the SSn of all employees who either work in department 5 or directly
supervise an employee who works in department 5
𝜋 (𝜎 (EMPLOYEE)) ∪ 𝜋_ (𝜎 (EMPLOYEE))
(c) List the names and numbers of all departments locating in ‘Houston’
𝜋, (𝜎 (DEPARTMENT ∗ DEPT_LOCATIONS) )
(d) List the first names of all employees who have a dependent with the same first name as themselves
𝜋 (EMPLOYEE ⋈ 𝐀𝐍𝐃 _ DEPENDENT))
(e) Retrieve the salary of all employees in department 5 who work more than 10 hours per week on the project named ‘ProjectX’
WORK510 ← WORKS_ON ⋈ 𝐀𝐍𝐃 𝐀𝐍𝐃 PROJECT
PROJECTX510 ← 𝜎 (WORK510) 𝜋 (PROJECTX510 ⋈EMPLOYEE)