The code you've presented satisfies the requirements.
If we think about querying the tables starting from each "side", we can see this. (Assume the supplied parameters are suitably valid.)
Return all instructors in a given department:
SELECT i.*
FROM department d
INNER JOIN instructors i ON i.dept_id = d.dept_id
WHERE d.dept_id = @departmentId;
We specify a value for the primary key of department, which restricts rows from that table to just 1 row. When we join to instructors, we need to find all instructors with that dept_id, which could be all of them, none of them, or somewhere in between. For one department, we can find many (or, more accurately, zero-to-many) instructors. (Note: yes, the query can be simplified; I'm just trying to demonstrate a point.)
Return all departments for a given instructor:
SELECT d.*
FROM instructors i
INNER JOIN department d ON d.dept_id = i.dept_id
WHERE i.inst_id = @instructorId;
Again, because we specify a unique value for the primary key, this restricts the rows returned from instructors to just 1 row. In that single row, there is exactly 1 dept_id. Because dept_id is also unique in department, when joining to that table, we can only get back a single department. For one instructor, we will find exactly one department.
If you follow this process for all the relationships you create, it's easy to figure out if the requirements/design specs are met or not. You'll start to see these more easily (without writing queries) once you get more experience under your belt.
Best Answer
I don't have any insider knowledge of how Google Docs is implemented. However, if you look at things like Windows file permissions, this type of thing usually works on the these principles:
Inheritance - To grant access to many objects with the same scope, grant the access at the highest level (e.g. folder, not file) and all children of the granted node get the same permission as the parent.
Group and Individual Privilege - Rights can be granted to groups and to individuals. Individuals can belong to multiple groups and you might be in more than one that has access. Alternatively, access can also be granted to an individual.
Explicit Grant - Assume access is denied unless there is an explicit grant in place. The explicit grant can be to the individual or to one or more groups that the individual is a part of. The grant can be to the specific resource or the resource can inherit the grant from an ancestor. However, no access is allowed unless there is a qualifying grant somehow.
By applying these three principles you can manage the amount of data that must be maintained to control access to resources.
EDIT:
See my answer to a similar question over on Stack Overflow. The basic idea is to map resources and principals into hierarchies or networks.
The resources (documents) will probably fit neatly in an un-level hierarchy of directories and documents. How you model the actor (user/group) depends on your business rules. Generally groups are one level deep, but users can be in multiple groups. This means you will probably want a sub-typing approach to principals such that they can be users or groups. You will have an intersection table (m:n) between principals to indicate membership.
To make the traversal of the resource hierarchy more efficient, you might consider one of the common relational hierarchy tricks. My usual favourite is "visitation numbers". See also my answer to this question for more about working with visitation numbers.
Consider the following (mostly logical) ERD:
Using this type of model, the query to find out if a user has access to a given document is a matter of joining
MembershiptoResourcethroughGrantwhere the matched left and right are greater than or equal to the left and right of the resource in question. This gives you the answer based on group membership. To get direct individual grants you would do the same thing but leave outMembershipand just use the ID fromIndividualto filter theGrants.