Great post, thanks!

In your code for 2nf on github (2nf_queries.sql), you also need to remove the unrelated columns at the end to complete the second normalisation step, ie you forgot:

ALTER TABLE therapist_directory
DROP COLUMN insurance,
DROP COLUMN new_patients;

Then your requested_query.sql should look like this:

SELECT COUNT(td.therapist_id) FROM therapist_directory td
JOIN specialities sp ON td.therapist_id = sp.therapist_id
JOIN visit_specifications vs ON td.therapist_id = vs.therapist_id
JOIN therapist_location tl ON td.therapist_id = tl.therapist_id
JOIN locations l ON tl.hospital_id = l.hospital_id
WHERE (speciality_one ~ '(Anxiety|Depression|Bipolar)'
OR speciality_two ~ '(Anxiety|Depression|Bipolar)'
OR speciality_three ~ '(Anxiety|Depression|Bipolar)')
AND new_patients = 'Yes'
AND city ~ '(San Francisco|Oakland|San Jose|Sacramento|Auburn)';