Understanding One to One Relationship Vs One to Many Relationship in DBMS Environment

Although data items are real attributes of the entities represented, they need not always be explicitly defined within the formal database structure. A relationship may be represented in the data structure using one of the three forms.

1. Explicit data items: as defined by the user and stored in each of the entity records participating in the relationship, sometimes called ‘symbolic pointers’
2. Physical pointers: chaining or an array of pointers in the many direction of the relationship, pointer in one direction to an exclusive member of the related entity class.
3. Physical contiguity: storing member records with parent records.

 

More than one form can be used simultaneously, resulting in greater redundancy and greater reliability.

 

A One to One relationship can be represented by a data item value in one entity being equal to a data item value in the other entity. There can be no relationship between two entities unless there is some common domain on which to base the relationship. The second representational form uses a physical pointer in one record to point to a related record instance. In the third representational form, one of the two entity types could be designated as parent with the other entity instance physically stored next to the parent entity instance. With physical pointers or physical contiguity, it is no longer necessary to explicitly store the data item values of the common domain.

The problem with the last two representational forms is that proper recording of the relationship is dependent upon the correctness of the physical pointer or physical congruity. If the un-stored attribute value in the dependent entity instance changes, the person updating the database must identity both the old and the new instances so that the system can appropriately update both physical pointers, or the change must be reflected in a physical move of the entity record.

Also, the pointer and physical contiguity representational forms imply a directionality and access path which must be known and adhered to on the part of the user. It is more difficult to access dependent entity instances independent of parent entity instances. It is generally more desirable to store the data item explicitly and then let the use of pointers of physical contiguity be dictated solely by the need for more rapid access or better utilization of storage. At the very least, the user should be insulated from these physical considerations. Defining the database structure should initially be concerned only with the relationship and not its representational form that is a matter for physical storage structure design.

 

A One-to-Many relationship: The relationship is represented by adding a data item to one record. It contains the identifier of the related record from the other entity class. Either the identifier of X is stored in the records of type Y or vice versa. Doing one or the other has some access efficiency implications.

A one-many relationship between X and Y can be represented by storing the identifier of X in each instance of Y, storing a set of identifiers of Y in a repeating group within each instance of X or storing the identifier for the first Y in X and having the rest of the Y’s chained together. Another representational choice is to store the Y’s physically contiguous with their related X. A variation of the third choice would be to store the Y’s of an X remote from X but physically contiguous with each other. The first representational form is the bases for the relational data model while the second and third are the basis of the network data model.

There is an implicit directionality from many-to-one in the relational data model and directionality from one-to-many in the network model. Neither one of these directions is inherent in the relationship to be defined and captured in the stored database. The particular representational form chosen depends upon the behavioral characteristics of the database, how it is used and the more frequent avenue of access. It is obviously possible to store sufficient information to permit efficient access in the either direction. With higher degree, One-to-Many relationships, the extra attributes of the relationship can be added to the repeating group of foreign identifiers in either the X or the Y record.

Alternatively, the extra attributes can be added to the special linking record. This last alternative is the only one permitted way in either the network or the relational data models. Ideally the user should not be involved with definitions at this level of representation. It should be sufficient to declare two entity classes, a relationship between them, the characteristics and criteria of the relationships, any attributes of the relationship and the behavioral characteristics of the relationship.

Tweet

 

| distributed database management | what is database management | management software database | Database File System | Database Management System - DBMS Applications | Program Communication in Database Management System - DBMS | Difference between good database design and a bad one | Good Database Desing Using Normalization | Direct Manipulation in Database Management System - DBMS | MIS and the Role of Database in an Organization | Maintaining database quality in an organization | Validation criteria in a Database Management System - DBMS model | Relational Database Management System - RDBMS | successful database design | Database Management System - DBMS Relationship | Understanding cloud computing database management system |