ER-diagram with one-to-one cardinality ratio

ER-diagram with one-to-one cardinality ratio:

Assumption: 1 department has 1 manager.

The given ER diagram has cardinality ratio 1:1 because 1 department has 1 manager and 1 manager manages 1 department.

Entity Relationship diagram

     
      Fig: E-R diagram corresponding to customers and loans

Fig shows an example of an entity-relationship diagram. Such a diagram must have the following criteria:

·         Entities represented by rectangles (e.g. customer and loan)

·        Attributes of an entity represented by ellipses (e.g. loan_number and amount attributes for the entity loan)

·        Relationship sets represented by diamonds (e.g. borrows relationship set.)

·        Primary key attribute of an entity set underlined (e.g. primary key customer_id underlined for entity customer)

·        Links joining diamond relationship set to entities. (e.g. there are links joining the entities to the borrows relationship to complete the E-R diagram.)

·        Fig depicts a binary relationship between the two entities customer and loan. The cardinality ratio from customer to loan is one-to-many because a customer can borrow many loans from the bank but one loan from the bank can belong to one customer assuming the loan is not joint i.e, an individual loan.

 

Super key, Candidate key and Primary key

A superkey is a set of one or more attributes that taken collectively allow us to identify uniquely an entity in the entity set. For example, the customer_id attribute of the entity set customer is sufficient to distinguish one customer entity from another. Thus, customer_id is a super key. Similarly, the combination of customer_id and customer_name is a superkey for the entity set customer. Thus, a minimal super key with extraneous attributes is also a superkey. However only customer_name attribute of customer is not a superkey, because several people might have the same name.

Thus, we now know that if K is a superkey, then so is any superset of K. But we are often interested in minimal superkeys with no extraneous attributes. Such minimal superkeys are called candidate keys.

Several distinct sets of attributes could serve as a candidate key so long they identify uniquely an entity in the entity set. Consider the combination of customer_name and customer_street. This set of attributes can distinguish uniquely among members of the customer entity set. Therefore, both {customer_id} and {customer_name, customer_street} are candidate keys. But the latter is not a super key. Although the attributes customer_id and customer_name can distinguish customer entities, their combination does not form a candidate key, since the attribute customer_id alone is a candidate key. This set is, however, a super key.

We shall use the term primary key to denote a candidate key that is chosen by the database designer as the principal means of identifying entities within an entity set. Unlike super keys and candidate keys, there can always only be one way of representing a primary key for the entities of the entity set. It is noteworthy that every entity set must have a primary key to distinguish uniquely the entities within the entity set and to distinguish from other entity sets.