Orderable representation of multi-part values Consider a table for construction projects.
Each project requires resources, e.g. steel, wood, copper, etc.
Suppose we want the following types of queries:


All projects requiring less than 3 units of wood.
All projects requiring only wood and steel.
All projects requiring at least four units of copper but no steel.


What schema design supports queries like this?



Here's one idea:

CREATE TABLE projects (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL,
    UNIQUE (name),
    PRIMARY KEY (id)
);
CREATE TABLE materials (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL,
    UNIQUE (name),
    PRIMARY KEY (id)
);
CREATE TABLE projects_and_materials (
    project_id INTEGER,
    material_id INTEGER,
    value INTEGER,
    FOREIGN KEY(project_id) REFERENCES projects (id),
    FOREIGN KEY(material_id) REFERENCES material (id)
);


The value column tells us how much of the material referenced by material_id is needed for the project referenced by project_id.
This schema can represent the data, but it's not clear to me that it's good for queries.
Your tables look adequate to me.  I'd just have a "project_materials" table with columns including "project_id", "material_name", and "material_quantity", but your teacher may not like it.  Also, is a "unit" a quantity, or distinct thing, e.g. copper tube, copper screws, pieces of wood in various sizes and shapes?

So anyway, for instance:

SELECT p.name FROM projects AS p
INNER JOIN project_materials AS m1 
ON ( m1.project_id = p.id AND m1.material_name = 'Copper' )
LEFT OUTER JOIN project_materials AS m2 
ON ( m2.project_id = p.id AND m2.material_name = 'Steel' ) 
WHERE ( m1.material_quantity &gt; 4 AND m2.material_quantity IS NULL )