1. ##a. Assume#R1#and#R2#are#union1compatible.##Minimum#is#max(N1,N2)#=#N2,#maximum#is#N1+N2.#b. Assume#R1#and#R2#are#union1compatible.##Minimum#is#0,#maximum#is#min(N1,#N2)#=#N1.#c. Assume#R1#and#R2#are#union1compatible.##Minimum#is#0,#maximum#is#N1.#d. No#assumptions#needed.##Minimum#and#maximum#are#the#same,##N1*N2.#e. Assume#there’s#a#field#called#“a”.##Minimum#is#0,#maximum#is#N1.#f. Assume#there’s#a#field#called#“a”.##Minimum#is#1,#maximum#is#N1.#g. Assume#R1’s#fields#are#a#superset#of#R2’s.##Minimum#is#0,#maximum#is#N1.##2. These#are#not#necessarily#the#most#efficient#solutions!##And#because#of#the#nature#of#the#relational#algebra,#other#solutions#may#work#as#well.#a. πname(πeid(employee)#⋈#works#⋈#πcid(σname=”First#Bank#Corporation”(company)))#b. πeid(employee#⋈#works#⋈#σcity=”Wilmington”#∨#city=”Philadelphia”(company))#c. πeid(σcity=”Philadelphia”(employee)#⋈#works#⋈#σcity=”Wilmington”(company))#d. πcid(company#⋈#works#⋈#σcity=”Wilmington”#∨#city=”Philadelphia”(employee))#e. Set#division#can#solve#this:##πcid(πcid,employee.city(company#⋈#works#⋈#employee)/πcity(employee))#f. πcid(πcid,employee.city(company#⋈#works#⋈#employee)/πcity(σage>50(employee)))#g. We#don’t#actually#need#to#do#anything#special:#πmanager_id,managee_id(manages)#h. There#are#several#different#ways#to#do#this.##Here’s#one#with#a#rename#of#a#cross1product:###ρ(workPairs(1#→#eid1,#2#→#cid1,#3#→#salary1,#4#→#eid2,#5#→#cid2,#6#→#salary2),#works#×#works)#πeid1,eid2(σcid1=cid2#∧#eid1#≠#eid2#∧#salary1#>#salary2(workPairs))##3. In#words:#a. The#names#of#companies#employing#people#that#live#in#Wilmington#and#make#more#than#$100,000/year.#b. The#names#of#companies#employing#people#that#live#in#Wilmington#and#make#either#more#than#$100,000/year#or#less#than#$10,000/year.#c. The#names#and#salaries#of#employees#with#managers#that#live#in#Wilmington.##(Note:##there#should#have#been#a#join#to#Works#to#recover#salary.##An#acceptable#answer#is#also#“The#names#of#employees#with#managers#that#live#in#Wilmington”.)#d. Average#salaries#grouped#by#city.#e.
View Full Document
Unlocking...