Overview of different types of keys used in E-R diagrams.- How to establish a primary key from a set of alternatives.
- Composite, superkey, candidate, primary and alternate keys explained.
- Other keys you may come across include foreign and partial keys.
Overview of different types of keys used in E-R diagrams.If youre unfamiliar with entities and attributes, you may want to read Intro to the E-R Diagram first.
The ER diagram is a way to model a database in an organized and efficient way. A key is a way to categorize attributes in an E-R diagram. When you first start making E-R diagrams, the number of different choices for keys can be overwhelming. However, the goal of the E-R diagram is to create a simplified “bird’s eye” view of your data. A judicious choice of keys helps to achieve that goal. Although there are many different types of keys to choose from in a set of data, relatively few will actually make it to your finished diagram.
Composite Keys
In general, keys can be single-attribute (unary) or multi-attribute (n-ary). A composite key requires more than one attribute. If a key is composite, like {state,driver license#}, a composite attribute can be made from the separate parts. In the early stages of database design, like E-R diagram creation, entity attributes are often composite or multi-valued. However, these may create problems down the road and need to be handled in specific ways when you translate into an actual database [1].
Superkey, Candidate, Primary and Alternate Key
A superkey of an entity set is an attribute, or set of attributes, with values that uniquely identify each entity in the set. For example, a DMV database might contain the following information:
In this example, {License #} is a superkey as it is a unique identifier. {Make,Model,Owner,State,License#,VIN#} and {State,License#,VIN#} are also superkeys. On the other hand, {Owner} or {Make,Model,Owner} are not superkeys as these could refer to more than one person [2].
A candidate key is a minimal super key that uniquely identifies an entity. Minimal superkeys have no unnecessary attributes; In other words, superkeys that dont have subsets that are also superkeys. For example, {State,License#} or {VIN} in the above set are possible choices for candidate keys.
One you have identified all the candidate keys, choose a primary key. Each strong entity in an E-R diagram has a primary key. You may have several candidate keys to choose from. In general, choose a simple key over a composite one. In addition, make sure that the primary key has the following properties [3]:
- A non-null value for each instance of the entity.
- A unique value for each instance of an entity.
- A non-changing value for the life of each entity instance.
In this example, the best choice to identify a particular car is {VIN}, as it would never change for the lifetime of the vehicle. The first digit of a driver license number will change when a name change occurs, so this does meet the requirements of property 3 above. In addition, {VIN} is the logical choice because it is directly associated with the car. If an ownership change would occur, the VIN would stay the same. An alternate key is any candidate key not chosen as the primary key. For this example, {State,License#} is an alternate key. A partial key identifies a weak entity. Weak entities–those that rely on other entities–do not have primary keys [4]. Instead, they have a partial key–one or more attributes that uniquely identify it via an owner entity.
When the word key is used in an E-R diagram, it usually refers to the primary key for an entity [5]. Show the primary key by underlining the attribute.
Dashed underlines indicate partial keys.
Other Keys You May Come Across
Foreign keys are not used in E-R models, but they are used in relational databases to indicate an attribute that is the primary key of another table. Foreign keys are used to establish a relationship when both tables have the same attribute [5].
A secondary key is used strictly for retrieval purposes and accessing records [5]. These keys do not have to be unique and are typically not included in an E-R diagram. The term secondary key is also occasionally used as a synonym for alternate key [6].
References
[1] Entity-Relationship modeling
[2] Relational Model and the Entity-Relationship (ER) Model
[4] Entity-Relationship Diagram Symbols and Notation
