What type of relationship occurs between two different instances of an entity?

Entity Transformation

If there is a one-to-many relationship between two entities, add the key of the entity on the “one” side (the parent) into the child table as a foreign key. If there is a one-to-one relationship between one entity and another entity, add the key of one of the entities into the table for the other entity, thus changing it to a foreign key. The addition of a foreign key due to a one-to-one relationship can be made in either direction. One strategy is to maintain the most natural parent–child relationship by putting the parent key into the child table. Another strategy is based on efficiency: Add the foreign key to the table with fewer rows.

Every entity in a generalization hierarchy is transformed into a table. Each of these tables contains the key of the supertype entity; in reality, the subtype primary keys are foreign keys as well. The supertype table also contains nonkey values that are common to all the relevant entities; the other tables contain nonkey values specific to each subtype entity.

SQL constructs for these transformations may include constraints for not null, unique, and foreign key. A primary key must be specified for each table, either explicitly from among the keys in the ER diagram or by taking the composite of all attributes as the default key. Note that the primary key designation implies that the attribute is not null and unique. It is important to note, however, that not all DBMSs follow the ANSI standard in this regard—it may be possible in some systems to create a primary key that can be null. We recommend that you specify “not null” explicitly for all key attributes.

One-to-Many Relationships

The most common type of relationship is a one-to-many relationship. (In fact, relational databases are constructed from the rare one-to-one relationship and numerous one-to-many relationships.) For example, Antique Opticals 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.

The example about which you read earlier, concerning Antique Opticals and the distributor from which the company 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.)

9.4 An Example of Fixed Hierarchies—Stock Classification

Sometimes a number of entity types are linked by one-to-many relationship types showing a hierarchy of detail. However, this construct can cause considerable difficulty to the business because it allows only one hierarchy to be represented. This does not reflect the real world, and as a result, it can cause unnecessary and inappropriate restrictions on the business. In fact, it is the combination of entity-type partitioning and restrictive cardinalities.

Consequences

Imposing restrictions through the data structure means

Arbitrary or inappropriate restrictions are placed on the data that can be held.

Fudge or false data may be introduced to overcome the restrictions in the data structure. This may have to be programmed around.

Data may be replicated to overcome the restrictions in the data structure. The different versions must be reconciled.

The entity type will only work within the context defined. A change in business rules may require a change in the database structure.

The resultant system is harder to share.

Failing to correctly recognize entity types means

The same data structures may be replicated.

The same functionality may be replicated.

An example is given in Figure 9-8.

Each stock_item must belong to one and only one stock_item_group, which must belong to one and only one stock_item_group_type. An example of the kind of restriction this can place is that you might have Naphtha as a stock_item, and you might have Feedstock, Intermediate, and Finished Product as stock_item_groups. This model restricts you to allowing Naphtha to be only classed as one of those, even though it can be all three.

The first step is to check that the entity types are of the same type. By comparing each of the entity types, you can see that all the entity types are classes of stock or material, so the entity type class is a supertype to them all, as shown in Figure 9-9.

The next thing is to check that all the relationship types are the same. In our case, the three relationship types indicated in Figure 9-9 are of the same type, indicating that an instance of the entity type at the one end is a generalization of an instance of the entity type at the many end. On the other hand, the relationship type between stock_item and sales_product is indicating that one is sold as the other, which is a different type of relationship type. This being the case, we can simplify the model and add flexibility at the same time by introducing a supertype to stock_item, stock_item_group, and stock_item_group_type of say, stock_class, and move the one-to-many relationship type to the supertype. This gives the situation in Figure 9-10.

This removes the restriction on what levels you have, but it allows you to classify any stock class as being one of its three subtypes. However, we still have to consider the cardinality of the relationship types. The cardinality of the relationship type between sales_product and stock_item was dealt with in the previous chapter and should be many-to-many. From the example used in the first paragraph of this example it is also clear that the relationship type on stock_class should also be a many-to-many relationship type. This leads to the situation in Figure 9-11.

Now we have a general pattern that is widely applicable. If we wish, we can still put back our restrictive relationships for the special case of stock_item, stock_item_group, and stock_item_group_type, but now if we do, we have a flexible model that will still support the business when these restrictions do not apply.

Translating an ER Diagram into Relations

An ER diagram in which all many-to-many relationships have been transformed into one-to-many relationships, through the introduction of composite entities, can be translated directly into a set of relations. To do so:

Create one table for each entity.

For each entity that is only at the “one” end of one or more relationships, and not at the “many” end of any relationship, create a single-column primary key, using an arbitrary unique identifier if no natural primary key is available.

For each entity that is at the “many” end of one or more relationships, include the primary key of each parent entity (those at the “one” end of the relationships) in the table as foreign keys.

If an entity at the “many” end of one or more relationships has a natural primary key (for example, an order number or an invoice number), use that single column as the primary key. Otherwise, concatenate the primary key of its parent with any other column or columns needed for uniqueness to form the table’s primary key.

Following these guidelines, we end up with the following tables for the Antique Opticals database:

Note: You will see these relations reworked a bit throughout the remainder of the first part of this book to help illustrate various aspects of database design. However, the preceding is the design that results from a direct translation of the ER diagram.

Translating an ER Diagram into Relations

An ER diagram in which all many-to-many relationships have been transformed into one-to-many relationships through the introduction of composite entities can be translated directly into a set of relations. To do so:

Create one table for each entity.

For each entity that is only at the “one” end of one or more relationships and not at the “many” end of any relationship, create a single-column primary key, using an arbitrary unique identifier if no natural primary key is available.

For each entity that is at the “many” end of one or more relationships, include the primary key of each parent entity (those at the “one” end of the relationships) in the table as foreign keys.

If an entity at the “many” end of one or more relationships has a natural primary key (for example, an order number or an invoice number), use that single column as the primary key. Otherwise, concatenate the primary key of its parent with any other column or columns needed for uniqueness to form the table's primary key.

Following these guidelines, we end up with the following tables for the Antique Opticals database:

Customer (customer_numb, customer_first_name, customer_last_name, customer_street, customer_city, customer_state, customer_zip, customer_phone)

Distributor (distributor_numb, distributor_name, distributor_street, distributor_city, distributor_state, distributor_zip, distributor_phone, distributor_contact_person, contact_person_ext)

Item (item_numb, item_type, title, distributor_numb, retail_price, release_date, genre, quant_in_stock)

Order (order_numb, customer_numb, order_date, credit_card_numb, credit_card_exp_date, order_complete?, pickup_or_ship?)

Order item (order_numb, item_numb, quantity, discount_percent, selling_price, line_cost, shipped?, shipping_date)

Purchase (purchase_date, customer_numb, items_received?, customer_paid?)

Purchase item (purchase_date, customer_numb, item_numb, condition, price_paid)

Actor (actor_numb, actor_name)

Performance (actor_numb, item_numb, role)

Producer (producer_name, studio)

Production (producer_name,item_numb)

Note: You will see these relations reworked a bit throughout the remainder of the first part of this book to help illustrate various aspects of database design. However, the preceding is the design that results from a direct translation of the ER diagram.

One-to-many Relationships

True one-to-one relationships are very uncommon, but database environments are full of one-to-many relationships. When a one-to-many relationship exists between two entities, one occurrence of entity A is related to zero, one, or more occurrences of entity B; each occurrence of entity B is related to at most one occurrence of entity A. If, for example, we add car owners to our car database, then there will be a one-to-many relationship between an owner and a car. At any time, a person can own zero, one, or more cars and a car belongs to zero or one owners.

In an ERD, the line between the related entities has |0 or || at one end, representing the zero, one, or more end of the relationship (or one and only one in the case of a mandatory relatioship). The end of the line at the “many” side of the relationship is marked with >0 or >|, representing zero, one, or more (or in the case of a mandatory relationship, one or more). In Figure 1-6, the owner entity is at the “one” end of the relationship and the car entity is at the “many” end.

12.5.3 Compiler Intrinsics

Compiler intrinsics are built-in functions provided by the compiler that share a one-to-one, or many-to-one, relationship with specific instructions. This allows the specific instructions to be written using high-level programming constructs and frees the developer from worrying about calling conventions, register allocation, and instruction scheduling. Another advantage to utilizing compiler intrinsics is that, unlike standalone or inline assembly, the compiler has visibility into what is occurring and perform further optimizations.

What is the relationship between two entities?

What occurs between two entities in which an instance?

What are the 3 types of relationships in a database?

What are the four 4 types of relationships in a database?