CODEWITHSUNDEEP
databasedatabase-designconstraintsentity-relationshiperd
arnobpl
arnobpl

Reputation: 1226

Zero_or_One vs One_or_only_One entity relation just for initially allowable Null field

I have confusion about Zero_or_One vs One_and_only_One entity relation in step 4 of the following scenario. The scenario is:

  1. There are two entities: School and Teacher.

  2. Without cardinality defined, ERD is:

    ______________________
| SCHOOL             |                ____________________
----------------------                | TEACHER          |
| ID [PK]            |                --------------------
| Headmaster_ID [FK] |----------------| ID [PK]          |
----------------------                --------------------

  3. A teacher can be a headmaster of zero or one school but not more than one school. So ERD becomes:

    ______________________
| SCHOOL             |                ____________________
----------------------                | TEACHER          |
| ID [PK]            |                --------------------
| Headmaster_ID [FK] | 0|-------------| ID [PK]          |
----------------------                --------------------

  4. A school normally has exactly one headmaster. But if Headmaster_ID is restricted to be Not_Null, then the headmaster (also a teacher) for a new school must be inserted into Teacher table before the new school is inserted into School table. I do not want this restriction in the database. So which one is correct for the given scenario?

    ______________________
| SCHOOL             |                ____________________
----------------------                | TEACHER          |
| ID [PK]            |                --------------------
| Headmaster_ID [FK] | 0|----------|0 | ID [PK]          |
----------------------                --------------------

    VS

    ______________________
| SCHOOL             |                ____________________
----------------------                | TEACHER          |
| ID [PK]            |                --------------------
| Headmaster_ID [FK] | 0|----------|| | ID [PK]          |
----------------------                --------------------

So my question: is it OK to use Zero_or_One rather than One_and_only_One just because a field can be initially Null for a short time? Or something else should be done in this case? I want a better explanation for the issue in generic sense.

Upvotes: 2

Views: 1853

Answers (1)

philipxy
philipxy

Reputation: 15158

TL;DR

  • What matters about the "normal" presence of a headmaster vs "short time" without is whether it happens in the application domain of schools, teachers and headmasters vs only in your particular implementation. The "normal" and "short" are irrelevant. An ER diagram records facts true of every situation/state that can can ever arise for associations/tables in the application/database.

  • Neither of your proposed solutions are (quite) appropriate whether or not there is always a headmaster in the application domain.

  • If you want no headmaster only until a school ever has one then you must use DBMS-specific security measures and/or non-declarative code to get what assurance you can.

The basic diagram

Your step 2 is unjustifiably presuming a FK. Here is the basic situation:

______________________
| SCHOOL             |                      ____________________
----------------------                      | TEACHER          |
| ID [PK]            |Headmastered_by       --------------------
|                    | -------------------- | ID [PK]          |
----------------------         Headmaster_of--------------------

Now that you have decided what entities are participating in a relationship and picked names per order of entities you can clarify just what relationship you mean. Then you can observe its cardinality (per entity & ordering). Then you can decide how to represent it.

If a school always has a headmaster

(Reading from a box along a line to a cardinality and another box:)
A school is Headmastered_by 1 teacher. (Every school participates.)
A teacher is Headmaster_of one 0 or 1 school. 

______________________
| SCHOOL             |                      ____________________
----------------------                      | TEACHER          |
| ID [PK]            |Headmastered_by       --------------------
|                    | |0----------------|| | ID [PK]          |
----------------------         Headmaster_of--------------------

Headmastered_by is not 0-or-many to 1 so it's not represented by a SCHOOL NOT NULL (relational) FK to TEACHER. It's not 0-or-many to 0-or-1 so it's not represented by a SCHOOL NULLable (SQL) FK to TEACHER. Similarly there is no FK in the other direction. However you could represent Headmastered_by by a SCHOOL NOT NULL FK to TEACHER that is also UNIQUE NOT NULL (a candidate/alternate key). Then schools and headmasters are 1:1 and some teachers may not be headmasters (not referenced).

______________________
| SCHOOL             |                      ____________________
----------------------                      | TEACHER          |
| ID [PK]            |Headmastered_by       --------------------
| TEACHER_ID NOT NULL| |0----------------|| | ID [PK]          |
|  [UNIQUE] [FK]     |         Headmaster_of--------------------
----------------------

...but with "no restriction" on "insert into Teacher"

If you want a school to have exactly one headmaster in the application domain then it's a little odd that you "do not want this restriction in the database" that "headmaster (also a teacher) for a new school must be inserted into Teacher table before the new school is inserted into School." Then you either hope for users to follow a protocol or you use DBMS security measures and/or non-procedural code to force both changes in the same transaction or at least to make the user go out of their way not to do so.

If a school can sometimes have no headmaster

A school is Headmastered_by 0 or 1 teacher.
A teacher is Headmaster_of one 0 or 1 school. 

______________________
| SCHOOL             |                      ____________________
----------------------                      | TEACHER          |
| ID [PK]            |Headmastered_by       --------------------
|                    | |0----------------0| | ID [PK]          |
----------------------         Headmaster_of--------------------

You can represent this in two mirror-image ways using a NULLable FK in one table that is UNIQUE NULLable. (If your DBMS supports the SQL standard's multiple NULLs in a UNIQUE NULLable column.)

______________________
| ONE                |                      ____________________
----------------------                      | OTHER            |
| ID [PK]            |Headmaster_one        --------------------
| OTHER_ID NULLable  | |0----------------0| | ID [PK]          |
|  [UNIQUE][FK]      |      Headmaster_other--------------------
----------------------

You could represent it by adding a NULLable FK to SCHOOL that is also UNIQUE NULLable. Then schools and headmasters are 1:1 while some schools may have no headmaster (NULL) and some teachers may not be headmasters (not referenced).

You could represent it by adding a NULLable FK to TEACHER that is also UNIQUE NULLable. Then headmasters and schools are 1:1 while some teachers may not be headmasters (NULL) and some schools may not have headmasters (not referenced).

But you can represent this symmetrical situation symmetrically by adding a Headmaster table with UNIQUE NOT NULL CKs (SCHOOL_ID) and (TEACHER_ID) plus NOT NULL FKs to respective tables. The two CK constraints enforce 1:1 for headmastered schools & headmasters. But there can be headmasterless schools (not referenced) and non-headmaster teachers (not referenced).

____________________________________________
| SCHOOL             | TEACHER             | Headmaster
--------------------------------------------
| SCHOOL_ID NOT NULL | TEACHER_ID NOT NULL |
|  [UNIQUE] [FK]     |  [UNIQUE] [FK]      |
--------------------------------------------

The obvious and natural initial representation for an application relationship is such an explicit table. If you start with this then you can rearrange to asymmetrical schemas with FKs etc when there are appropriate special cases. Note that in asymmetrical versions a Headmaster_is/Headmaster_of relationship instance must be extracted from what is ostensibly/supposedly/called an entity table but is de facto a relationship table. (And the FK gets inappropriately called a "relationship".)

(The IDEF1X method records the use of NULLable vs NOT NULL FKs in its ER diagrams, rather than allowing a method to be chosen by implementers. This is a good idea, because the NULLability of an attribute is visible to users and affects the meaning of relationships/tables.) (Of course, that is also a good argument to not to use ER formally and to specify directly by relational schemas.)

...but only initially

As to enforcing (in the application domain) that a school can have no headmaster only until it has had one: In a typical SQL DBMS you can't constrain this declaratively. You can write triggers and stored procedures restricting UPDATE when IS NOT NULL or UPDATing/DELETing rows, but these are bypassable by users. You can add a column showing the last value, or whether it was NULL, or a table for that, to restrict user updates to your table, but you can't restrict user access to that attribute/table. So it comes down to the security measures that your DBMS offers.

Upvotes: 1

Related Questions