Problem 3
Product ( maker, model, type)
PC (model, speed, ram, hd, price)
Laptop (model, speed, ram, hd, screen, price)
Printer (model, color, type, price)
1. What PC models have speed at least 3?
Sol: \(\pi_{model}\left(\sigma_{speed\ge3}\ PC\right)\)
2. Which manufacturers make laptops with hard disks at least 100GB?
Sol: \(\pi_{ma\ker}\left(\Pr oduct\bowtie\sigma_{hd\ge100\ }\left(Laptop\right)\right)\)
3. Find the manufacturers that make Laptops but not PCs.
Sol: \(\pi_{ma\ker}\left(\sigma_{type='laptop'}\left(product\right)-\sigma_{type='PC'}\left(product\right)\right)\)
4. Find the hard disk sizes that occur in two or more PCs.
Sol: \(\rho\left(P1,PC\right)\)
\(\rho\left(P2,PC\right)\)
\(\pi_{hd}\left(P1\bowtie_{\left(P1.model<>P2.model\ AND\ P1.hd=P2.hd\right)}P2\right)\)
5. Find the manufacturer(s) of the PC(s) with the highest available speed.
Sol: \(P1:=\pi_{model,speed}\left(PC\right)\)
\(\rho\left(P2\left(model\rightarrow model2,speed\rightarrow speed2\right),P1\right)\)
\(P3\ :=\ \pi_{model,speed}\left(P1\bowtie_{speed<speed2}\ P2\right)\)
\(P4\ :=P1-P3\)
\(P5\ :=\pi_{ma\ker}\left(product\bowtie P4\right)\)
Problem 4
1. Find the name and the SSN of everyone who works more than 100 hours on a project located in Boston.
Sol: \(P1:=\pi_{SSN}\left(\sigma_{hours>100}\left(\pi_{PNo}\left(\sigma_{location=boston}\ pro.ject\right)\bowtie hour\log\right)\right)\)
\(\pi_{name,ssn}\left(Employee\bowtie P1\right)\)
2. Find the name and SSN of everyone who works for department number 1 and also works on project number 2.
Sol: \(\pi_{name,ssn}\left(\sigma_{DNo=1}employee\right)\bowtie\pi_{ssn}\left(\sigma_{PNo=2\ }hour\log\right)\)
3. Find the name and the SSN of everyone who works on at least two projects.
Sol: \(\rho\left(SSNs,\pi_{SSN,PNo}\left(\Pr oject\right)\right)\)
\(\rho\left(SSNcombs\left(1\rightarrow SSN1,2\rightarrow PNo1,3\rightarrow SSN2,4\rightarrow PNo2\right),SSNs\times SSNs\right)\)
\(\pi_{name,SSN}\left(employee\bowtie\left(\pi_{SSN1}\left(\sigma_{\left(SSN1=SSN2\right)\wedge\left(PNo1\ne PNo2\right)}\left(SSNcombs\right)\right)\right)\right)\)
4. Find the name and the SSN of everyone who works on all projects.
Sol: \(\pi_{name,SSN}\left(\left(\pi_{SSN,PNo}\left(hour\log\right)\div\ \pi_{PNo}\left(pro.ject\right)\bowtie employee\right)\right)\)