Lecture 11 Revision
• Mapping Example
• Relational Algebra & SQL Examples
Dr. Osama Al-Haj Hassan
• Relational Algebra & SQL Examples
• Find candidate Keys
• BCNF Test
Reference to: Fundamentals of Database Systems , Ramez Elmasri and Shamkant B. Navathe, Fifth Edition. 1
Mapping Example
E1 1 R1 M E2 1 R2 1 E3
t1
t3 t4 t5 t6 t7
t2
tt t14 t15
E4 R3 E5 M R4 N
t8 t9 t10 t11
t13 t12
Solution
t1 t2
E1
t4 t1
E2
t5
t6 E3
t8 t9
E4
t14 t6 t15
t8 t9
t10 t11
E5
t10 t6
R4
t12
t1 t3
T3-R
t1 t8
R3
t10 t13
3
Company DB
Relational Algebra and SQL Examples
• Find ssn and name of employees with salary > 200 and they work for department number 1.
work for department number 1.
5
Solution
σ Employee
ππ
ππ(σ Employeesalary>400 and dno=1 )
Select ssn,name from employee where salary>400 and dno=1;
ππ ππ(
Ssn,name
)
Relational Algebra and SQL Examples
• Find ssn,name of employees with salary > 200 and they work for department “QA”.
for department “QA”.
7
Solution
Employee Department dno = dnumber
π π π
π(
σσ(Salary>400 and dname=‘QA’σσ )))))
Select ssn,name from employee,department where dno = dnumber and
salary>400 and dname=‘QA’;
π π π π(
Ssn,name
Salary>400 and dname=‘QA’
))))
Relational Algebra and SQL Examples
• Find ssn of employee and the number of projects he is working on and the total number of hours he is working.
working on and the total number of hours he is working.
9
Solution
essn ℱ
COUNT pno , SUM hours (works_on)
Select essn,count(*), sum(hours) from works_on group by essn;
essn ℱ
COUNT pno , SUM hours (works_on)
Relational Algebra and SQL Examples
• Find ssn and name of employee and the number of projects
• Find ssn and name of employee and the number of projects he is working on and the total number of hours he is working.
11
Solution
essn ℱ
COUNT pno , SUM hours (works_on) πemployee
ssn,name) ( )
(
Select essn,count(*), sum(hours) from works_on,employee Where ssn=essn
group by essn;
essn ℱ
COUNT pno , SUM hours (works_on)
ssn,name) ( )
( ssn=essn
Relational Algebra and SQL Examples
• Find ssn and number of projects each employee is working on in each department
in each department
13
Solution
Works_on project pno = pnumber
(
ππππ(Essn,pno,dnum )))))
Essn,dno COUNT pno
Select essn,dno,count(*) from works_on,project Where pno = pnumber
group by essn,dno;
Relational Algebra and SQL Examples
• Find ssn for employees who work on all projects
15
Solution
π π π
π works_on
essn,pno )
(pno) π π π
π project pnumber
÷ ρ(
Select essn , count(*) from works_on Group by essn
Having count(*)= (Select count(*) from project);
Relational Algebra and SQL Examples
• Find the SSN for employees who do not work on any project
17
Solution
)
ππ
ππ works_on
- ρ( essn
)
ππ
ππ employee
( ssn
Select ssn from employee where
Ssn not in (select essn from works_on);
)
(ssn)essn
- ρ(
ssn ) (
Relational Algebra and SQL Examples
• Find the ssn for employees who work in projects that belong to department 1 and they work in projects that belong to to department 1 and they work in projects that belong to department 2
19
Solution
Works_on project pnum = pnumber
π π π π(
essn
σ ) σ σ σ
dnum=1(
)))) π π π π(
Works_onpnum = pnumberprojectessn
σ ) σσ σ
dnum=2(
))))
∩
(Select ssn from works_on,project where
works_on.pno=project.pnumber and project.dnum=1) intersect
(Select ssn from works_on,project where
works_on.pno=project.pnumber and project.dnum=2);
Relational Algebra and SQL Examples
• Find SSN for employees who either work in projects that
belong to department 1 or they work in projects that belong belong to department 1 or they work in projects that belong to department 2 or both.
21
Solution
Works_on project pnum = pnumber
π π π π(
essn
σ ) σ σ σ
dnum=1(
)))) π π π π(
Works_onpnum = pnumberprojectessn
σ ) σσ σ
dnum=2(
))))
∪
(Select ssn from works_on,project where
works_on.pno=project.pnumber and project.dnum=1) union
(Select ssn from works_on,project where
works_on.pno=project.pnumber and project.dnum=2);
Find Candidate Keys of length 1 or 2 for the following Relation
R = {A, B, C, D, E}
F = { AB --> D, C --> E, E --> A, D --> B }
Since C is part of the candidate keys, you do not have to try A+,B+,D+, and E+ Since C does not appear on the right hand side of any FD, you know C is part of candidate keys
C+ = {CEA}, so C is not a candidate key CA+ = {CAE}, so CA is not a candidate key CB+ = {CBEAD}, so CB is a candidate key CD+ = {CDEAB}, so CD is a candidate key CE+ = {CEA}, so CE is not a candidate key
So, our candidate keys of length 1 are: nothing So, our candidate keys of length 2 are: CB,CD
23
BCNF Test
• Is the previous relation in BCNF?
• If not, then decompose it until each sub relation becomes in BCNF.
• R is not in BCNF (why?)
– Because the following FDs violate BCNF constraints:
• AB --> D, C --> E, E --> A, D --> B
• The left hand side of each FD is not a super key (not CB nor CD)
BCNF Test
R = A,B,C,D,E
F={AB --> D, C --> E, E --> A, D --> B}
R1 = A,B,D
F={AB --> D, D --> B}
R2 = A,B,C,E
F={C --> E, E --> A}
Keys: CB,CD
Key: AB Key: CB
Note: You always compute keys using attribute closure
F={AB --> D, D --> B} F={C --> E, E --> A}
R3 = E,A F={E --> A}
R4 = B,C,E F={C --> E}
Key: AB
Key: CB Key: E
R5 =C,E F={C --> E}
R6 = C,B
Key: CB Key: C
• This decomposition results in R1,R3,R5,R6
• It is dependency
preserving because No FD is lost during decomposition
25
