The Relational Data Model: Relations, Tuples, Domains, and Keys

What is the Relational Data Model? (In Simple Words)

Introduced by Dr. E.F. Codd of IBM in 1970, the Relational Data Model represents data in the form of two-dimensional tables called Relations.

It is the theoretical foundation behind almost every modern commercial database system in existence today, including MySQL, Oracle, PostgreSQL, Microsoft SQL Server, and SQLite.

1. Fundamental Terminology of the Relational Model

In academic computer science (DAVV examinations), standard database concepts have specific formal mathematical names:

Detailed Definitions:

1. Relation (Table): A collection of rows and columns containing data about a specific entity (e.g., STUDENT relation).
2. Tuple (Row / Record): A single horizontal row in a relation representing one specific real-world object (e.g., records for student “Rahul”).
3. Attribute (Column / Field): A named vertical column representing a specific property of the relation (e.g., Roll_No, Student_Name, Marks).
4. Domain: The pool of legal, atomic values from which an attribute draws its values (e.g., the domain of Age is positive integers between 16 and 100).
5. Degree of a Relation: The total number of attributes (columns) in a table.
   (e.g., If STUDENT has 5 columns: Roll_No, Name, Email, Phone, City, its Degree = 5).
6. Cardinality of a Relation: The total number of tuples (rows) currently stored in the table.
   (e.g., If the table has 60 student records, its Cardinality = 60).

2. Null Values in Relational Databases

What does NULL mean in a database table?

• NULL is NOT zero (0).
• NULL is NOT an empty text string (“”) or blank space.
• NULL represents MISSING, UNKNOWN, or NOT APPLICABLE data.

Real-World Scenarios of NULL:

1. Unknown Value: A new patient is admitted to a hospital unconscious; their Blood_Group is currently NULL until a blood test is conducted.
2. Not Applicable Value: A student lives in a hostel; their Home_Landline_Number attribute is NULL because they do not have a landline phone.

3. All Types of Keys in DBMS

A Key is an attribute or a set of attributes used to uniquely identify any row (tuple) in a relation and establish links between different tables.

A. Super Key (SK)

• Any set of attributes that uniquely identifies a row in a relation.
• A super key may contain extra, redundant attributes.
• Example in STUDENT(Roll_No, Email, Phone, Name, City):
  • {Roll_No} is a super key.
  • {Roll_No, Name} is also a super key (even though Name is redundant!).
  • {Roll_No, Email, City} is also a super key.

B. Candidate Key (CK)

• A minimal Super Key with no unnecessary or redundant attributes.
• If you remove any attribute from a Candidate Key, it loses its uniqueness property.
• Every candidate key is a super key, but not every super key is a candidate key.
• Example: In STUDENT, {Roll_No}, {Email}, and {Phone} are all valid Candidate Keys!

C. Primary Key (PK)

• The single candidate key selected by the database designer to uniquely identify tuples in the table.
• Strict Rules for Primary Key:
  1. It must be 100% Unique for every row.
  2. It can NEVER be NULL (Entity Integrity Constraint).
  3. Its values should ideally remain immutable (rarely changed).
• Example: The college selects Roll_No as the Primary Key.

D. Alternate Key (Secondary Key)

• Candidate keys that were NOT chosen as the primary key.
• Formula: Alternate Keys = Candidate Keys - Primary Key
• Example: Since Roll_No was selected as the Primary Key, {Email} and {Phone} serve as Alternate Keys.

E. Composite Key

• A primary key composed of two or more attributes combined together when no single column can guarantee uniqueness.
• Example in STUDENT_ATTENDANCE:
  • Neither Roll_No alone (attends every day) nor Date alone (many students attend on the same day) is unique.
  • The combination {Roll_No, Date} forms a Composite Primary Key!

F. Foreign Key (Referential Key)

• An attribute in a child table whose values are derived from the Primary Key of a parent table.
• It creates a formal relationship between two tables.
• A foreign key CAN accept NULL values (unless declared NOT NULL), and it can contain duplicate values.
• Example: In STUDENT(Roll_No, Name, Dept_ID), Dept_ID is a Foreign Key referencing Dept_ID in DEPARTMENT(Dept_ID, Dept_Name).

💡 DAVV Exam Preparation Tips

5-Mark Question: “Differentiate between Super Key, Candidate Key, and Primary Key with a clear example.”

• Define Super Key (uniqueness with redundant columns).
• Define Candidate Key (minimal super key).
• Define Primary Key (chosen candidate key; cannot be NULL).
• Provide a small 4-column student table showing the key relationships.

10-Mark Question: “Explain the relational data model. Define the terms: Relation, Tuple, Attribute, Domain, Degree, and Cardinality. Explain all types of database keys with examples.”

• Provide the formal vs commercial terms table.
• Give clear definitions and numerical degree/cardinality calculations.
• Systematically detail all 6 key types (Super, Candidate, Primary, Alternate, Composite, Foreign Key) with real-world examples.