Attributes and Keys in ER Diagrams

Attributes are the set of features that describe an entity. For example, suppose we an entity Student. To describe its characteristics or properties we need attributes like Roll Number, First Name, Middle Name, Last Name, Date of Birth, Age, Phone No., Address etc. Attributes are basically used to differentiate one entity from another. When we chose an attribute or set of attributes to distinctly identify entities in entity sets, they are called keys. Attributes and Keys in ER Diagrams define characteristics of entities.

One important point to remember here is that for each attribute there is a set of permitted values called its Domain. For example, domain of Roll Number attribute is a set of numerical values.

Representation of Attributes and Keys in ER Diagrams

Attributes and Keys in ER Diagrams are represented by ellipse or oval along with the underlined entity names.

Attribute Representation in ER Diagram

Types of Attributes

On the Basis of Composition

1.      Simple Attributes

Attributes that have only atomic values are called simple attributes. These cannot be divided further. Student Roll Number is a simple entity.  Each student entity will have a Roll Number that may store a single numeric or alphanumeric value. Simple attributes are represented by oval with single outline in an ER diagrams.

2.      Composite Attributes

Attributes that can be further divided and represented into simple attributes are called composite attributes. For example Name attribute can be divided into First Name, Middle Name and Last Name. Address attribute is usually divided into House Number, Street, City, State and Zip Code. Composite attributes are represented by an oval connected with other attribute ovals in an ER diagram.

On the Basis of Count of Values that can be Stored

1.      Single Valued Attributes

Single valued attributes can have only one value at a given instance of time. For example Date of Birth, Roll Number, Father’s Name attributes can have single value. These are represented by single outline ovals in ER diagram.

2.      Multi Valued Attributes

Multi valued attributes can have more than one value at a given instance of time. For example a person can have multiple Phone Numbers,  E-mail  Ids, Addresses, Qualifications, Job Experiences. Multi valued attributes are represented with double outlined ovals in ER diagrams. In relational database a separate table is created for multi valued attributes.  These are not simply included as a column in an entity relation.

On the Basis of How the Attribute Value can be Stored

1.      Stored Attributes

The attributes whose values are stored in database and which does not change over a period of time are called Stored Attributes. The values in Stored Attributes can be used to derive values for other attribute. Date of Birth and Basic Pay are examples of Stored Attribute.  These are represented as ovals with single outline in ER diagram.

2.      Derived Attribute

The attributes whose values can change over time and can be derived from the stored attributes are called Derived Attributes. For example age attribute of a person changes from time to time.  So we can keep this attribute in database as derived attribute.  It can be derived from Date of Birth attribute and current date. You may have seen many times while filling an online form that on filling Date of Birth, Age is automatically calculated. Derived attribute is represented by dashed or dotted outline ovals in the ER diagram.

Keys in ER diagrams

A key is an attribute in ER diagrams whose values are distinct for each individual entity in an entity set.

For example for a student entity set Roll Number or Registration Number can be a key because these are unique for each student entity.

Types of Keys used in ER Diagrams

Primary Key

It is the attribute or set of attributes that can uniquely identify each entity in an entity set. In the previous ER diagram Roll Number is chosen as the primary key. It is represented by underlining the attribute name.

There are certain properties for a primary key to follow. These are:

  • Unique values must not be null i.e. primary key attribute must never contain null values for any entity.
  • All values of the primary key attribute must be unique.
  • When choosing a primary key from the pool of candidate key always choose a single simple key over a composite key. For example here in the above example, {Roll Number}, {Registration Number}, {DOB, Name} are possible candidate keys. So instead of taking {DOB, name} as the primary key we have chosen {Roll Number} as the primary key for Student Entity Set.

Foreign key

It is an attribute or set of attributes that must have values from Primary Key of the strong Entity on which a weak Entity depends on.  It is represented by underlining the attribute name with a dotted line.

Whenever there is some relationship between two entities there must be some common attributes between them. An attribute to become a foreign key must satisfy some conditions:

  • Values of foreign key attribute must have the same domain as defined for the primary key of the Strong Entity Set.
  • The foreign key values for any entity of an entity set are either null or must match the primary key data values of some entity of the primary key entity set.

For example Student and Course are two entities and have Course id as the common attribute. Course id in Student entity set is the foreign key and in Course entity set it acts as the primary key. Student is the Referring Entity Set and course is the Referenced Entity Set.