Consider the following bill of materials problem: the database has a relation Subpart(Part, Subpart, Quantity), which tells which direct subparts are needed for each part and in what quantity. For instance, Subpart(mounting_assembly, screw, 4) means that the mounting assembly includes four screws. For simplicity, let us assume that parts that do not have subparts (the atomic parts) are

represented as having NULL as the only subpart (for instance, Subpart(screw, NULL,0)). Write a recursive query to produce a list of all parts and for each part the number of primitive subparts it has.

What will be an ideal response?

CREATE RECURSIVE VIEW PartListCount(Part, PrimitiveCount) AS

         SELECT S.Part, 1

         FROM Subpart S

         WHERE S.Subpart IS NULL

         UNION

         SELECT S.Part, SUM(S.Quantity * L.PrimitiveCount)

         FROM Subpart S, PartListCount L

         WHERE S.Subpart = L.Part

         GROUP BY S.Part

The ?rst, non-recursive subquery starts the computation by producing the list of primitive parts and assigning 1 as the number of primitive components of each such part (the check IS NULL is intended to ensure that the part is primitive). The recursive
subquery then computes the total number of primitive components for parts that are composed of several subparts.