Unit 1: Database Concepts (RDBMS)

Chapter 2: Relational Data Model and Key Constraints

1. Core Terminology of the Relational Model

In the Relational Data Model, data is represented mathematically as relations. Understanding these standard terms is vital for CBSE Board exams:

Relation: STUDENT
+----------+------------+-------+-------+
| RollNo   | Name       | Class | Marks |  <-- 4 Columns (Degree = 4)
+----------+------------+-------+-------+
| 101      | Aaditya    | XII   | 94    |  <-- Tuple 1
| 102      | Bhavna     | XII   | 88    |  <-- Tuple 2
| 103      | Chirag     | XII   | 92    |  <-- Tuple 3
+----------+------------+-------+-------+
Cardinality = 3 (Total data rows)

2. Properties of a Relation

1. Atomic Values: Every attribute value in each cell must be atomic (indivisible). Multi-valued attributes (e.g., storing two phone numbers in one cell) are not allowed.
2. Unique Tuples: No two rows can be identical duplicates.
3. Ordering is Unimportant: The sequence of rows and columns has no bearing on the meaning of the relation.
4. Distinct Attribute Names: All columns within a table must have unique names.

3. Database Keys

Keys are attributes or sets of attributes used to identify tuples uniquely and establish relationships across tables.

1. Candidate Key

• Any attribute or minimal set of attributes capable of uniquely identifying a record in a table.
• A table can have multiple candidate keys.
• Example: In a STUDENT table, both Admission_No and Aadhar_Number can uniquely identify each student. Both are candidate keys.

2. Primary Key

• The specific candidate key chosen by the database designer as the principal unique identifier for the table.
• Rule: A Primary Key must be unique and can NEVER contain NULL values.
• There can only be one primary key per table.

3. Alternate Key

• Candidate keys that were not chosen as the Primary Key.
• $\text{Alternate Keys} = \text{All Candidate Keys} - \text{Primary Key}$.
• Example: If Admission_No is chosen as the Primary Key, then Aadhar_Number becomes an Alternate Key.

4. Composite Primary Key

• When a single attribute cannot guarantee uniqueness, two or more attributes combined together serve as the Primary Key.
• Example: In an ENROLLMENT(Student_ID, Course_ID, Year) table, (Student_ID, Course_ID) together form a Composite Primary Key.

5. Foreign Key

• A column (or set of columns) in a table whose values refer to the Primary Key of another table (or the same table).
• The table containing the foreign key is the Referencing / Child Table.
• The table containing the primary key is the Referenced / Parent Table.
• Used to maintain Referential Integrity.

Parent Table: DEPARTMENT
+----------+------------------+
| Dept_ID* | Dept_Name        |  (* Primary Key)
+----------+------------------+
| D01      | Computer Science |
| D02      | Information Tech |
+----------+------------------+
         ^
         | Referenced By
         |
Child Table: EMPLOYEE
+--------+----------+-----------+
| Emp_ID*| Emp_Name | Dept_ID#  |  (# Foreign Key)
+--------+----------+-----------+
| E101   | Rajesh   | D01       |
| E102   | Sunita   | D02       |
+--------+----------+-----------+

4. Integrity Constraints

Constraints are rules enforced on data columns to ensure accuracy, validity, and consistency:

1. Domain Integrity (NOT NULL, CHECK, DEFAULT): Ensures valid data type, allowable range, and non-empty values.
2. Entity Integrity: The Primary Key attribute must be unique and cannot be NULL.
3. Referential Integrity: A foreign key value in a child table must either match an existing primary key value in the parent table or be NULL.

5. Board Exam Focus & Calculations

Solved Board Questions

1. Q: A table 'Hospital' has 6 columns and 25 rows. What are its degree and cardinality?
2. Ans: Degree = 6, Cardinality = 25.
3. Q: Can a Foreign Key accept NULL values?
4. Ans: Yes, a foreign key can hold NULL values unless explicitly declared with NOT NULL.
5. Q: Differentiate between Candidate Key and Alternate Key.
6. Ans: All attribute combinations that can uniquely identify a row are Candidate Keys. The candidate keys that are not selected as the primary key are called Alternate Keys.