| Diagram |  |
|---|---|
| How to read | For any A there are possibly many Bs. For any B there only one A. |
| Relevant relations | The many side of this relation can be either optional or mandatory. |
The two alternatives to look at first are to embed b_id in the A table or embed the a_id in the B table. Suppose we put the b_ids in A. By embedding the b_id in the A table, we are associating one and only one B entity with a particular A entity. This cannot be true according to the ER diagram, because for any A entity there are possibly many B entities. Since we cannot put multiple values in a column (rule #1), embedding in this direction is not a good choice.
The other alternative is to put the a_ids in the B table. By embedding the a_id in the B table, we are associating one and only one A entity with a particular B entity. By the ER diagram, for any B entity there is one and only one A entity. Thus, the embedded a_id column must have a value in it.
Given the above discussion, the table structure is as follows (see this page for how this should be interpreted):
A(__a_id__, a_name, <a_other>)
B(__b_id__, b_name, fk_b_a*, <b_other>)
Since this is the first one of these, I will interpret it for you: The A table has a primary key a_id and another field a_name. If A has any other attributes, they would be stored in this table as well (a_other). The B table has a primary key b_id and another field b_name. A third field (fk_b_a) is a foreign key that references the A table. If B has any other attributes, they would be stored in this table as well (b_other).
Given this table structure, we must only determine which foreign keys can be null and which are unique. The column fk_b_a is the only foreign key in these two tables. By looking at the ER diagram we can see that it cannot be null: for every B entity there must be one associated A entity. Since fk_b_a represents the associated A entity, it cannot be null. The value in fk_b_a cannot be unique since there are possibly many B entities associated with the same A entity; thus, the same a_id would be repeated in the fk_b_a column of many rows of B. Given this information, we have the following SQL create statements (see this page for how this should be interpreted):
create table A
(a_id type primary key,
a_name type,
<a_other>);
create table B (
b_id type primary key,
b_name type,
fk_b_a type not null,
<b_other>,
foreign key (fk_b_a) references a);
Back to ER to SQL page.
