Using Relational Algebra, I need help with the database management question belo

Question

Using Relational Algebra, I need help with the database management question below:

Please explain each step because I am still trying to really get the hang of Relational Algebra.

Find the furniture that use no nuts/bolts. The answer will have the FID and the Model#.

Here are the tables:

Furniture (FID, Manufacturer Name, Model#, Model Name, Description, I#)

Parts (Part#)

Wood (Part#, Wood Piece Length, Wood Piece Width, Wood Piece Height, Wood Piece Type [Of Wood])

Nuts and Bolts (Part#, Length, Diameter, Thread Count)

Instructions (I#)

Instruction Step (I#, Step#, Text)

Warehouse Location (Aisle, Shelf, Bin#, Part#)

Used In (I#, Step#, Part#¸Number Used)

Package (FID, Part#, Number Used)

Thank you!

Explanation / Answer

To solve the problem.. start by identifying, what all tables you need....

For finding the furniture FID and Model#, you need Furniture table.. To search in this table, you need to figure out FID(Primary Key).

So a furniture contains many parts.. the relation between parts and furniture can be derieved from Package table.
Now we know which furniture has which parts.. There can be many rows(many parts) in the table corrsponding to a furniture.

Now we need to know, if the part contained any Nuts and bolts..
For this, relation between a part and nuts-bolts can be determined using table Nuts and Bolts. Nuts-Bolts table contains partId for each record.

So the query will be similar to below..

Print all furnitures whose furniture Id is not in below :
       Find all furniture Ids for which some part atleast contains nuts-bolts
      

In programming terms:      

// select only those furniture which are negation of the internal query      
select FID, Model# from Furniture where FID not in(

       // find furnitures with nuts-bolts
       select distinct FID from Package where Part# is in(
              
               // all the parts from nuts-and-bolts table contain nuts-bolts
               select distinct Part# from Nuts-and-Bolts;      
       )
)

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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