| Lesson 6 | One-to-many relationships, part 1 |
| Objective | Define one-to-many Relationships |
One-to-many Relationships
A one-to-many (1:N) relationship exists when
To illustrate such a relationship graphically, think of entities A and B in terms of generic tables. A one-to-many relationship exists when one record in Table A can be associated with many records in Table B, and one record in Table B can be associated with one record in Table A. The following SlideShow illustrates a 1:N relationship between Tables A and B from the viewpoint first of Table A, then of Table B.
- for one instance of entity A, there exists zero, one, or many instances of entity B;
- but for one instance of entity B, there exists zero or one instance of entity A.
To illustrate such a relationship graphically, think of entities A and B in terms of generic tables. A one-to-many relationship exists when one record in Table A can be associated with many records in Table B, and one record in Table B can be associated with one record in Table A. The following SlideShow illustrates a 1:N relationship between Tables A and B from the viewpoint first of Table A, then of Table B.
- This image illustrates a 1:N relationship between Table A and B from the viewpoint of Table A, showing one record in Table A related to many records in Table B (A:B = 1:N)
- This image shows the relationship from the viewpoint of Table B. One record in Table B relates to one record in Table A (B:A = 1:1).
One-to-Many Relationships
The most common type of relationship is a one-to-many relationship. In fact, most relational databases are constructed from the rare one-to-one relationship and numerous one-to-many relationships. For example, DistributedNetworks typically orders many titles from each distributor, and a given title comes from only one distributor. By the same token, a customer places many orders, but an order comes from only one customer. If we have instances of two entities (A and B), then a one-to-many relationship exists between two instances (Ai and Bi) if Ai is related to zero, one, or more instances of entity B and Bi is related to zero or one instance of entity A.
Other one-to-many relationships include that between a daughter and her biological mother. A woman may have zero, one, or more biological daughters; a daughter can have only one biological mother. As another example, consider a computer and its CPU. A CPU may not be installed in any computer, or it may be installed in at most one computer. A computer may have no CPU, one CPU, or more than one CPU.
Our previous example of DistributedNetworks and the distributor from which it ordered only one title is actually a one-to-many relationship where the 'many' is currently 'one'. Remember that when we are specifying data relationships, we are indicating possible relationships and not necessarily requiring that all instances of all entities participate in every documented relationship. There is absolutely no requirement that a distributor be related to any merchandise item, much less one or more merchandise items. (It might not make much sense to have a distributor in the database from which the company does not order, but there is nothing to prevent data about that distributor from being stored.)
The next lesson explores one-to-many relationships further.
Database Design and Theory
Other one-to-many relationships include that between a daughter and her biological mother. A woman may have zero, one, or more biological daughters; a daughter can have only one biological mother. As another example, consider a computer and its CPU. A CPU may not be installed in any computer, or it may be installed in at most one computer. A computer may have no CPU, one CPU, or more than one CPU.
Our previous example of DistributedNetworks and the distributor from which it ordered only one title is actually a one-to-many relationship where the 'many' is currently 'one'. Remember that when we are specifying data relationships, we are indicating possible relationships and not necessarily requiring that all instances of all entities participate in every documented relationship. There is absolutely no requirement that a distributor be related to any merchandise item, much less one or more merchandise items. (It might not make much sense to have a distributor in the database from which the company does not order, but there is nothing to prevent data about that distributor from being stored.)
The next lesson explores one-to-many relationships further.
