- \n
- \n
<\/a>Overview of different types of keys used in E-R diagrams.<\/li>\n
- How to establish a primary key from a set of alternatives.<\/li>\n
- Composite, superkey, candidate, primary and alternate keys explained.<\/li>\n
- Other keys you may come across include foreign and partial keys.<\/li>\n<\/ul>\n
If you\u2019re unfamiliar with entities and attributes, you may want to read Intro to the E-R Diagram<\/a> first.<\/p>\n
The ER diagram is a way to model a database in an organized and efficient way.\u00a0<\/span>A key<\/strong> is a way to categorize attributes in an E-R diagram.\u00a0When 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.<\/p>\n
Composite Keys<\/h2>\n
In general, keys can be single-attribute (unary) or multi-attribute (n-ary).\u00a0A composite key<\/em> 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].\u00a0<\/p>\n
Superkey, Candidate, Primary and Alternate Key<\/h2>\n
\u00a0<\/strong>A superkey<\/strong> 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:<\/p>\n
<\/a><\/p>\n
In this example, {License #}<\/em> 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<\/em> superkeys as these could refer to more than one person [2].<\/p>\n
A candidate key is<\/strong> a minimal super key that uniquely identifies an entity. Minimal superkeys have no unnecessary attributes; In other words, superkeys that don\u2019t have subsets that are also superkeys. \u00a0For example, {State,License#} or {VIN} in the above set are possible choices for candidate keys. \u00a0<\/p>\n
One you have identified all the candidate keys, choose a primary key<\/strong>. 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]:<\/p>\n
- \n
- A non-null value for each instance of the entity.<\/li>\n
- A unique value for each instance of an entity.<\/li>\n
- A non-changing value for the life of each entity instance.<\/li>\n<\/ol>\n
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<\/strong> is any candidate key not chosen as the primary key. For this example, {State,License#} is an alternate key. A partial key<\/strong> 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.<\/span>\u00a0<\/p>\n
<\/a><\/p>\n
When the word \u201ckey\u201d 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.\u00a0<\/p>\n
<\/a><\/p>\n
\u00a0<\/em><\/p>\n
Dashed underlines indicate partial keys.\u00a0<\/p>\n
<\/a><\/p>\n<\/p>\n
Other Keys You May Come Across<\/h2>\n
Foreign keys<\/strong> 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].<\/p>\n
A secondary key<\/strong> 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].<\/p>\n
References<\/h2>\n
[1] Entity-Relationship modeling<\/a><\/p>\n
[2] Relational Model and the Entity-Relationship (ER) Model<\/a><\/p>\n
[3] Primary and Foreign Keys<\/a><\/p>\n
[4] Entity-Relationship Diagram Symbols and Notation<\/a><\/p>\n
[5] The Entity-Relationship Model<\/a><\/p>\n
<\/a><\/p>\n
[6] Database Design ER<\/a><\/p>\n<\/div>\n
Go to Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"