Question 5 of 8

Question 5 (10+10=20 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

Given. ArtObject(AOid, Title, Origin, TypeID, Cost, Acquired, Aid, CName) — each row is one art object, referencing its Type via TypeID, its creating Artist via Aid, and the Collection that currently holds it via CName; Type(TypeID, description) where description names the physical kind (painting, sculpture, statue, other); Artist(Aid, Tname, DoB, Awards, Country); Collection(Cname, CType, Contactname).

Find. (a) artist names with art objects spread across ≥2 distinct collections; (b) artist names with >3 awards who have created zero sculptures.

Approach. Basic relational algebra has no built-in "count distinct" operator, so (a) uses the standard SELF-JOIN trick: pair every ArtObject row against every other ArtObject row by the same artist and look for a pair whose collection names differ — any such pair proves that artist spans at least two collections. (b) is a classic universal/negative condition ("never created ANY sculpture"), which relational algebra expresses directly with the SET-DIFFERENCE operator: subtract the set of sculpture-creating artists from the set of high-award artists.

1. Part (a) — at least two different collections. Self-join ArtObject against a renamed copy of itself on matching Aid but DIFFERING CName, project the shared Aid, then join to Artist for the name:

   Pairs   = σAO1.Aid=AO2.Aid AND AO1.CName≠AO2.CName( ρAO1(ArtObject) × ρAO2(ArtObject) )
   MultiCollAid = πAO1.Aid(Pairs)
   Result  = πTname( MultiCollAid ⋈Aid Artist )

   The cross product AO1×AO2 pairs every art object with every other one (including itself); the selection keeps only pairs sharing an artist but disagreeing on collection, which can only happen when that artist truly has work in 2+ collections.
2. Part (b) — >3 awards, never a sculptor. Compute the sculpture-makers' Aid set, the high-award Aid set, subtract, then join back to get names:

   SculptureType   = πTypeID( σdescription='sculpture'(Type) )
   SculptureArtist = πAid( ArtObject ⋈TypeID SculptureType )
   HighAward       = πAid( σAwards>3(Artist) )
   Qualifying      = HighAward − SculptureArtist
   Result          = πTname( Qualifying ⋈Aid Artist )

   The set difference is what encodes "never" — any Aid appearing even ONCE in SculptureArtist is removed from consideration entirely, regardless of how many non-sculpture pieces that same artist also made.

an artist with 5 awards and one sculpture is correctly excluded from (b) while a 4-award non-sculptor is correctly kept).