To achieve multi-tennancy you should limit the number of relationships you have to the factor that determines the tennant.
For example, each of Course, Department and Staff have a foreign key to School. However, these child tables are also interrelated. Is it necessary for Staff to have a schoolid? You can get to the school a staff member works for by following the foreign key to Course.
Similarly, you have grand-child tables (SD and DSC) which are relating tables in curious ways. What has "months indicating duration of the course" to do with which staff member is teaching the course? Is it really necessary for DSC to relate to Staff? Also, you appear to have repeating groups defined in your SD and SDC tables. I believe you may have a normalization issue.
By over-relating tables you create cascade cycles. Consider the following ERD as an alternative:
Here there is only one potential cycle to be concerned with, instead of the several that are in your current model. Fewer potential cycles means less extra work that might need to be done with triggers instead of declarative referential constraints. This keeps your system simpler and more maintainable.
I think you must be misunderstanding CASCADE foreign keys, and/or the rules around foreign keys in general.
A foreign key can only refer to a table with a unique constraint or primary key. So in your example, you cannot create a foreign key referencing table_b if there can be multiple instances of a value in the referenced column.
When you DELETE FROM ... a table, and another table has an ON DELETE CASCADE foreign key reference to it, all referencing rows are deleted from the other table.
The column referenced cannot have multiple instances of the value, because you cannot create a foreign key to a column that is not unique.
The referring column of the table with the constraint may have multiple values that refer to the column in the referenced table. If so, when the referenced value is removed, all these are deleted, as you would expect in a CASCADE.
regress=> CREATE TABLE table_b (id integer);
CREATE TABLE
regress=> INSERT INTO table_b (id) VALUES (1), (2), (3), (1);
INSERT 0 4
Can't create the foreign key, no unique constraint or PK:
regress=> CREATE TABLE table_a (b_id integer REFERENCES table_b(id) ON DELETE CASCADE);
ERROR: there is no unique constraint matching given keys for referenced table "table_b"
Can't add a PK or unique constraint, has duplicates:
regress=> ALTER TABLE table_b ADD PRIMARY KEY (id);
ERROR: could not create unique index "table_b_pkey"
DETAIL: Key (id)=(1) is duplicated.
Best Answer
When SQL was first developed, the idea of cascading updates and deletes made sense. In practice, they are nothing but trouble. Deleting one row of one table or updating one field of one row of one table could lock up the entire database for hours because of the cascading locks. Or generate a race condition.
Plus, cascading actions are, by definition, side effects. You really, really want to minimize the amount of side effects. Nothing good comes from them.
So it is best to not allow the deletion of a row that is referred to by any foreign key(s). Always accept the default "On Cascade" option (No Action). Allow the DBMS to throw an exception and let the app respond correctly (delete/update the referencing rows then the referenced row -- after checking with the user if this is really what they want to do, of course).