Use the Schema below for Problems 2 – 3: Doer(Did, Dname, Dstreet) Did = identif

Question

Use the Schema below for Problems 2 – 3: Doer(Did, Dname, Dstreet) Did = identifier of doer Dname = name of doer Dstreet = street address of doer Item(Iid, Iname, Ishade) Iid = identifier of item Iname = name of item Ishade = color of item Thing(tip, tpid, tcost) tid = identifier of doer tpid = identifier of item tcost = cost of item. Problem 2 (15 points): Display the identifier and name of the manufacturer who makes either blue item that cost less than $5.30 or is located at 146-53 Junk Street. Problem 3 (15 points): Write a Relational Algebra expression that would compute the Average cost of all the items in the Items table. The expression must display enough information so that the reader can check the calculation (using a calculator) just from the aggregate information displayed.

Explanation / Answer

Solution 2.

SELECT d1.Did, d1.Dname
FROM Doer d1 JOIN Item it1 JOIN Thing th1
ON d1.Did = th1.tid AND it1.Iid = th1.tpid
WHERE (it1.Ishade = 'blue' AND th1.tcost < 5.30) OR (d1.Dstreet = '146-53 Junk Street');

Solution 3

Aggregate function in relational algrebra is used as

a, b, c..G F1, F2, F3...(Expression), where a, b, c... are attributes for grouping and F1, F2, .. are aggregate functions.

Since we were asked to show enough information, we will also show total sum and total count along with average value of cost. The following is the relational algebra expression

tpidG sum(tcost), count(tpid), avg(tcost)(Thing)

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.