WORKBOOK ACTIVITY
Table of Contents TOPICS LAB LAB 1: LAB LAB &: LAB LAB %: LAB LAB *: LAB LAB -:
PAGES
ACTI ACTIVI VITY TY Fundaentals of !atabase "ana#eent S$ste ACTI ACTIVI VITY TY Relat'onal !atabases
% 11
ACTI ACTIVI VITY TY Relat'onal Al#eb(a
1)
ACTI ACTIVI VITY TY Ent't$ Relat'ons+', "odel
&-
ACTI ACTIVI VITY TY St(u.tu(ed /ue($ Lan#ua#e 0S/L
%&
DFC 2033 DATABASE SYSTEM
LAB 2:
ACTIVITY !atabase T(ansa.t'on "ana#eent
-*
LAB ACTIVITY %: Relat'onal Al#eb(a !u(at'on: & 3ou(s
Lea(n'n# Out.oes This activity encompasses activities 3A, 3B and 3C At the end of this activity, you should be able to: 1. Identify the fundamental operators to retrieve information. a. Restrict (elect! b. "ro#ect c. $oin (outer, inner! d. Cross "roduct 2. %efine the traditional set of operators. a. &nion b. Intersection c. %ifference 3. &se the operators to 'rite the epression.
T3EORY
The basic set of operations for the relational model These operations enable a user to specify basic retrieval re)uests. The result of retrieval is a ne' relation, 'hich may have been formed from one or more relations. The al*ebra operations thus produce ne' relations, 'hich can be further manipulated usin* operations of the same al*ebra.
F45!A"E5TAL OPERATORS There are + fundamental operators to retrieve information from relational database. Restrict ( elect ! σ
( R )
"ro#ect Л (R)
Page & of 6
DFC 2033 DATABASE SYSTEM
$oin Inner $oin (Л (R))
uter $oin -eft uter $oin (Л (R))
(Л (R))
Ri*ht uter $oin (Л (R))
(Л (R))
(Л (R))
Cross "roduct (Л (R)) X (Л (R))
TRA!ITIO5AL OPERATORS There are 3 traditional set operator: &nion (Л (R))
Intersection (Л (R))
(Л (R))
(Л (R))
%ifference (Л (R)) – (Л (R))
ACTIVITY The follo'in* activities (Activity 3A, Activity 3B and Activity 3C! are based on the tables belo': STAFF1 staffNo /01 555
staffName amat Ali Ana6
osition 2lectric en*ineer 4eb developer "ro*rammer
STAFF! staffNo 555 /01 +++
staffName Ana6 Ali amat A6ie
osition "ro*rammer 4eb developer 2lectric en*ineer 4eb developer Page % of 6
DFC 2033 DATABASE SYSTEM
573
8a6lim
Technical support
R"#$%T &ro'ectNo 5 7 3
&ro'ectName %A%% "ro*rammin* 9ultimedia
%ost 5 0
%ST"$R custNo C5 C5 C55 C; C75
custName Adam co Te*as Tulin dn Bhd 4an*sa 9e'an*i dn Bhd 4a'asan dn Bhd 8alim on dn Bhd
&ro'ectNo 5 7 7 3
staffNo 555 573 573 111 /01
A.t'7't$ %A Activity utcome: a. Identify the fundamental operators to retrieve information. i. Restrict (Select) ii. ro!ect iii. "oin (#uter$ Inner) iv. Cross roduct Based on *iven table, *enerate the output base on operation belo'.
Restrict (Select) Result5
σ osition * +,eb de-elo&er. ( STAFF! )
Result5 staffNo
staffName
osition
Ali
4eb developer
+++
A6ie
4eb developer
ro'ect Result7 Result7 custN"
Л custNo/ custName/ staffNo (%ST"$R)
custName
staffNo
Page * of 6
DFC 2033 DATABASE SYSTEM
C5
Adams Co
555
C5
Te*as Tulin dn Bhd
573
C55
4an*sa 9e'an*i dn Bhd
573
C;
4a'asan dn Bhd
111
C75
8alim on dn Bhd
/01
#"0N A. I<<2R $I< Result3
(Л staffNo/ staffName/ osition (STAFF!)) (Л custNo/ custName/ ro'No/ staffNo (%ST"$R))
Result3 staffNo
staffName
osition
cusNo
cusName
ro'No
55
Ana6
"ro*rammer
C5
Adams Co
5
B. &T2R $I< Result+
(Л staffNo/ staffName/ osition (STAFF!)) Page - of 6
DFC 2033 DATABASE SYSTEM
(Л custNo/ custName/ ro'No/ staffNo (%ST"$R)) Result+
%artesian roduct Result
TA==5 > "R$2CT
Result
Page 2 of 6
DFC 2033 DATABASE SYSTEM
A.t'7't$ %B Activity utcome: a. Define the traditional set of operators. i. %nion ii. Intersection iii. Difference Based on *iven table, *enerate the output base on operation belo'.
N0"N TA==5
TA==7
0NT$RS$%T0"N TA==5
TA==7
Page 8 of 6
DFC 2033 DATABASE SYSTEM
0FF$R$N%$ %ifference TA==5 ? TA==7
%ifference TA==7 ? TA==5
A.t'7't$ %C Activity utcome: a. %se the operators to &rite the e'pression.
Based on *iven table, *enerate the epression usin* relational al*ebra on situation belo'.
1. elect from TA==7 'here position is 4eb %eveloper.
2. elect staff name and pro#ect number for staff 573 in C&T92R@s relation. Page ) of 6
DFC 2033 DATABASE SYSTEM
3. elect same staff name from TA==5 and TA==7.
4. elect staff name from TA=5 'hich is not in TA==7
5. elect C&T92R outer #oin 'ith TA==5
Page 6 of 6
