Scribd
Upload
17 views

Keys in Relational Database Model

Uploaded by

460Maitreyee Shendye
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
17 views

Keys in Relational Database Model

Uploaded by

460Maitreyee Shendye
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Keys in Relational Database Model

In a relational database model, keys are essential for uniquely identifying records within a table and
establishing relationships between tables. They ensure data integrity and facilitate efficient data
retrieval. Here is a detailed description of the types of keys and referential integrity:

Types of Keys

1. Superkey: A superkey is a set of one or more attributes that, taken collectively, can uniquely
identify a tuple in a relation. For example, in an instructor relation, both {ID} and {ID, name}
can serve as superkeys. Superkeys may contain extraneous attributes that are not necessary
for unique identification.

2. Candidate Key: A candidate key is a minimal superkey, meaning it has no extraneous


attributes. It is a set of attributes that can uniquely identify a tuple in a relation. For instance,
{ID} is a candidate key for the instructor relation. There can be multiple candidate keys in a
relation, but only one is chosen as the primary key.

3. Primary Key: The primary key is a candidate key selected by the database designer to
uniquely identify tuples within a relation. It must contain unique values and cannot have null
values. When designing a database, it is customary to list the primary key attributes of a
relation schema before the other attributes. Primary key attributes are underlined in
schema diagram. Primary keys ensure entity integrity by uniquely identifying each record.
Choosing a primary key requires careful consideration. The chosen attribute(s) must have a
non-null value for each instance of the entity, the value must be unique for each instance,
and the values must not change or become null during the life of each entity instance.

4. Foreign Key: A foreign key is an attribute or a set of attributes in one table that references
the primary key of another table. It is a field (or collection of fields) in one table that
uniquely identifies a row of another table. Foreign keys help maintain consistency by
ensuring that the value in the foreign key column matches a value in the referenced primary
key column. Foreign keys provide a method for maintaining integrity in the data (called
referential integrity) and for navigating between different instances of an entity. For
example, the dept_name attribute in the instructor table can be a foreign key referencing
the dept_name attribute in the department table.

5. Composite Key: A composite key is a primary key composed of two or more attributes. Each
attribute in the composite key may not be unique by itself, but when combined, they
uniquely identify a tuple. For example, in a classroom relation, the combination of building
and room_number can serve as a composite key.

Referential Integrity

Referential integrity is a crucial aspect of relational databases that ensures the consistency and
accuracy of data. It is maintained through foreign key constraints, which enforce rules about the
relationships between tables. Here are the key points:

 Referencing and Referenced Relations: In a foreign key constraint, the table containing the
foreign key is called the referencing relation, and the table containing the primary key is
called the referenced relation.

 Foreign Key Constraint: This constraint ensures that the value in the foreign key column of
the referencing table matches a value in the primary key column of the referenced table. For
example, the dept_name in the instructor table must match a dept_name in the department
table.

 Referential Integrity Constraint: This broader constraint requires that the values in specified
attributes of any tuple in the referencing relation also appear in specified attributes of at
least one tuple in the referenced relation. It ensures that relationships between tables
remain consistent.

By using these keys and maintaining referential integrity, relational databases can effectively
manage and organize data, ensuring its accuracy and consistency.

You might also like