Database Reference
In-Depth Information
Implementing Advanced Cardinality
Now let's look at how you can implement advanced cardinality in SQL
Server. All relationships you create in SQL Server are one-to-many rela-
tionships; there is no such thing as one-to-one or many-to-many in the
physical world. We have talked about how you implement M:M relation-
ships in a physical model using two one-to-many relationships and a junc-
tion table, but how would you enforce a one-to-one, or even something
more advanced such as a one-to-two?
These situations can be tricky, but the good news is that we have al-
ready looked at the tools you will need to make this happen. Because the
Mountain View Music model doesn't have any of these requirements, let's
look at a sample data model for a university. Within a university you often
have several colleges; in this case, we have the college of business, the col-
lege of science, and the college of engineering. Each of these colleges has
one dean, and the university has decided that no one can be dean of more
than one college. This requires a one-to-one relationship between college
and dean. Figure 9.12 shows what the tables look like when they are set up
in SQL Server.
F IGURE 9.12
Tables to hold information about colleges and their deans
As you can see, there is a one-to-many relationship between these ta-
bles, but how do we enforce this relationship as one-to-one in SQL Server?
For starters, the fact that the dean_id column in tbl_college is required
means that we are enforcing the rule that there be at least one dean.
Additionally, because the dean_id column exists in tbl_college, each col-
lege can have only one dean. But what about the other half of the one-to-
one? Remember that each person can be dean of only one college. We can
enforce this using a trigger such as the one shown next.
ALTER TRIGGER trg_one_dean_per_college
ON tbl_college
 
Search WWH ::




Custom Search