Question

You are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project.

The schema for the database used in this question is as follows: (note that primary keys are shown underlined, foreign keys in bold).

SUPPLIER (SNo, SupplierName, City)

PART (PNo, PartName, Weight)

PROJECT (JobNo, JobName, Country)

SUPPLY (SNo, PNo, JobNo, Quantity)

Provide relational algebra (NOT SQL) queries to find the following information. Each question is worth 2 marks.

NOTE:

• You can use the symbols s, P, etc or the words ‘PROJECT’, ‘RESTRICT’ etc as you prefer.
• You do not need to try to make efficient queries – just correct ones.
• Where you use a join, always show the join condition.

1. List the name and city of all suppliers.
2. List all details of Projects being run in Australia.
3. List the Project name and Part name of any Parts where more than 50 of the part has been supplied to a particular project
4. List the names of Suppliers who have supplied the Part with the name “Valve Stem 04”.
5. List the names of Projects that have had Parts supplied by Zloof Co# or a Supplier located in Perth.
6. List the names of all Projects and the Parts supplied to them, if any.
7. List the name of any Supplier who supplied Parts to the Project called “Project 01” and to the Project called “Project 02”.
8. List the name of any Supplier who supplied Parts to the Project called “Project 01” but not to the Project called “Project 02”.
9. List the name of any Supplier who supplied Parts to the Project called “Project 01” or to the Project called “Project 02”.
10. List the names of any Supplier who has supplied Parts for all Projects run in Australia.

NO SQL, Please write nicely so that i can see! Thank you!

Answer 1

1.

_{SupplierName, City} ( SUPPLIER)

2.

( _{Country = 'Austrailia'} (PROJECT) )

3.

JobName, PartName ( _PROJECT.PNo G _{Count(partNo) > 50} ( PART _{PNo = PNo} SUPPLY _{JobNo = JobNo} PROJECT) )

4.

_SupplierName ( _{ParName = 'Valve Stem 04'} ( PART _{PNo = PNo} SUPPLY _{SNo = SNo} SUPPLIER) )

Do ask if any doubt.