Keys in the ER Model
A key attribute distinguishes one entity from another. Understanding super keys, candidate keys, primary keys and partial keys at design time prevents most identity problems later.
-
DBMS Fundamentals
- Data, Information and Databases
- What a DBMS Is and Why It Exists
- File System versus DBMS
- Advantages and Limitations of a DBMS
- Database Users and the Role of the DBA
- Three Level Architecture and Data Abstraction
- Logical and Physical Data Independence
- Schema, Instance and Metadata
- Database Applications and the Database System Environment
- Database Architecture
- Data Models
-
ER Model
- Entities, Entity Sets and Entity Types
- Types of Attributes in the ER Model
- Keys in the ER Model
- Relationships, Relationship Sets and Degree
- Cardinality and Participation Constraints
- Strong and Weak Entities
- Drawing and Reading ER Diagrams
- Extended ER: Generalisation, Specialisation and Aggregation
- Converting an ER Diagram into Relational Tables
- ER Design Projects
- Relational Model
- Relational Algebra
- Functional Dependencies
-
Normalisation
- Why Normalisation Exists: Anomalies and Redundancy
- First Normal Form
- Second Normal Form and Partial Dependency
- Third Normal Form and Transitive Dependency
- BCNF and BCNF Decomposition
- 4NF, 5NF, Multivalued and Join Dependencies
- Lossless Decomposition and Dependency Preservation
- Complete Worked Normalisation: Unnormalised to BCNF
- Denormalisation and When to Use It
Concept
Every entity in an entity set must be distinguishable from every other. A key is the attribute or set of attributes that makes that possible. Phase 5 treats keys formally in the relational model; this note covers what you decide while still drawing the diagram.
The key types
| Key | Definition | Example |
|---|---|---|
| Super key | Any attribute set that uniquely identifies an entity. May contain extra attributes. | {roll_no}, {roll_no, name}, {roll_no, name, dob} |
| Candidate key | A minimal super key — remove any attribute and uniqueness is lost. | {roll_no}, {email} |
| Primary key | The candidate key chosen to identify entities. Underlined in the diagram. | {roll_no} |
| Alternate key | A candidate key not chosen as primary. | {email} |
| Composite key | A key made of more than one attribute. | {roll_no, course_code} |
| Partial key | Identifies a weak entity only within its owner. Drawn with a dashed underline. | {dependant_name} within one employee |
EVERY candidate key is a super key.
NOT every super key is a candidate key.
EXACTLY ONE candidate key becomes the primary key.
The rest become alternate keys.
super keys
+--------------------------------------+
| {roll_no, name, dob} |
| {roll_no, name} +--------+ |
| {email, dob} |candidate| |
| | {roll_no}| | <- minimal
| | {email} | |
| +--------+ |
+--------------------------------------+Choosing a primary key
Judge every candidate against these five tests.
- Unique — no two entities may share the value, ever.
- Never null — every entity must have one, from the moment it exists.
- Stable — it must not change. Changing a primary key means changing every reference to it.
- Minimal — as few attributes as will do the job.
- Simple — short and easy to compare and index.
Natural and surrogate keys
| Natural key | Surrogate key | |
|---|---|---|
| What it is | An attribute with real world meaning | A value generated only to identify the row |
| Example | roll number, vehicle registration | an auto generated id |
| Advantages | Meaningful, already known to users, avoids an extra column | Never changes, never null, always short |
| Disadvantages | Real world values do change, and get reissued | Meaningless to users, needs a unique constraint on the natural key anyway |
Whichever you choose, still declare a uniqueness constraint on the natural key. A surrogate key alone will happily allow the same student to be inserted twice under two different generated ids.
Example
STUDENT ( roll_no, email, aadhaar_style_id, name, dob )
candidate keys {roll_no}, {email}, {aadhaar_style_id}
primary key {roll_no} stable, never null, short,
already used by everyone
alternate keys {email}, {aadhaar_style_id}
Why not email as primary?
students change their email address, and a primary key
must be stable. Keep it as an alternate key with a
uniqueness constraint.
Why not a national identifier?
it is sensitive personal data, it may be absent for some
students, and it should not be scattered across every
table that references a student.That last point is a genuine design and privacy rule: do not use sensitive personal identifiers as primary keys, because a primary key is copied into every table that refers to it.
Common mistakes
- Saying a candidate key is any unique attribute set. Minimality is what separates candidate from super.
- Choosing a changeable attribute. Phone numbers, names and email addresses all change.
- Assuming a composite key is bad design. For a relationship such as enrolment, a composite key is exactly right.
- Forgetting the partial key on weak entities. It is drawn with a dashed underline and it is examined.
- Using a name as a key. Names are not unique and they change.
Exam and interview questions
- Define super key, candidate key, primary key, alternate key and composite key with examples.
- Why is every candidate key a super key but not the reverse?
- State five criteria for choosing a primary key.
- Compare natural and surrogate keys and say when you would choose each.
- What is a partial key and which kind of entity has one?
Practice
- For BOOK ( isbn, title, author, accession_no, publisher ), list the super keys, candidate keys and choose a primary key with reasons.
- Explain in three sentences why a mobile number is a poor primary key.
- Give one case where a composite key is the correct choice and say why a surrogate would be worse.
Conclusion
Super keys identify, candidate keys identify minimally, and one candidate becomes the primary key. Choose it to be unique, never null, stable, minimal and simple — and never choose sensitive personal data.