Types of Relationships in Font

Paint Code 39 in Font Types of Relationships

You can depict two or more entities in a relationship, and depending on the number of entities, you may describe the degree of relationship as binary, ternary, quaternary, etc. The most common degree of relationship in real life cases is binary, so let s examine a binary relationship in more detail.

The cardinality of a relationship indicates how many instances of one entity can be related to an instance of another entity. Just because a binary relationship reflects a relationship between two entities doesn t mean that there is always a one-to-one relationship between them cardinality in ER modeling expresses the number of occurrences of one entity in relation to another entity. Entity relationships can be one-to-one, one-to-many, many-to-many, or some other type. The most common relationships are the following (assume there are two entities, A and B): One-to-many (1:M) relationship: In this case, each instance of an entity A is related to several members of another entity, B. For example, an entity called Customer can check out many books from a library, but one and only one Customer can borrow each book at a time. Thus, the entity Customer and the entity Book have a one-to-many relationship. Of course, the relationship may not exist if you have a Customer who has not yet borrowed a Book. So the relation is actually one Customer may borrow none, one, or many Books. One-to-one (1:1) relationship: This relationship is a situation where only one instance of either entity can be related to an instance of the other entity. For example, a person could have only one legal social security number (SSN), and each SSN should refer to just one person. Many-to-many (M:M) relationship: In this situation, each instance of entity A is related to one or more instances of entity B, and an instance of entity B is related to one or more instances of entity A. As an example, let s take an entity called Movie Star and an entity called Movie. Each Movie Star can star in several Movies, and each Movie may have several Movie Stars. In real life, a many-to-many relationship is usually broken down into a simpler one-to-many relationship, which happens to be the predominant form of cardinality in the relationships among entities. Accurately determining cardinalities of relationships is the key to a well-designed relational database. Duplicated data, redundancy, and data anomalies are some of the problems that arise when you don t model relationship cardinalities correctly.

Candidate keys are those attributes that can uniquely identify a row in a table, and a table can have more than one candidate key. For example, it s fairly common for an employee table to have both a uniquely generated sequence number as well as another identifier, like an employee number (or social security number). (Of course, any whole row, itself, could serve as a candidate key, because by definition a relational model can t have any duplicate tuples. However, a whole row is rarely used as the key, since the point of a key is to easily access the row.) The primary key is the candidate key that s chosen to uniquely identify each row in a table. You should always strive to select a key based on a single attribute rather than on multiple attributes, for simplicity and efficiency. Keys are vital when you come to the point of physically building the entity-relationship models. A natural primary key is one that consists of data items or entity attributes. Almost all modern relational databases, including Oracle databases, also offer simple system numbers or sequenced numbers that are generated and maintained by the RDBMS as an alternative to a natural primary key (such as a sequence number to identify orders). Such keys are often referred to as surrogate or artificial primary keys. Whatever method you choose a natural key or a surrogate key certain rules apply: The primary key value must be unique. The primary key can t be null (blank). The primary key can t be changed (it must remain stable over the life of the entity). The primary key must be as concise as possible.