Relational Model Concepts

The relational model represents the database as a collection of relations, where each relation resembles a table of values or a flat file of records.

It is called a flat file because each record has a simple, linear (flat) structure.

When a relation is viewed as a table:

• Each row represents a collection of related data values.

• A row typically corresponds to a real-world entity or relationship.

Components of the Relational Model

• A row is called a tuple. It holds a unique data set.
  (Cardinality: Total number of rows in a relation.)

• A column header is called an attribute. It defines the properties or characteristics of the data.
  (Degree: Total number of columns in a relation.)

• A domain defines the set of valid values and the data type allowed for an attribute.

• A relation refers to the entire table structure made up of tuples and attributes.

---

Domain

A domain (D) is a set of atomic values.

• Atomic means each value in the domain is indivisible from the perspective of the relational model.

• Each domain has a specified data type or format (e.g., integer, string, date).

---

Properties of a Valid Relation

1. Each relation (table) has a unique name.

2. Attribute values are atomic (indivisible).

3. Attribute (column) names are unique within a relation.

4. No duplicate tuples (rows) are allowed.

5. The order of rows and columns is not significant.

6. Integrity constraints must be followed to maintain data consistency.

---

Relation Schema

A relation schema (R) is denoted as:

R(A1, A2, A3, ..., An)

Where:

• R is the name of the relation.

• A1, A2, ..., An are the attributes of the relation.

• Each attribute Ai is associated with a domain D, denoted as dom(Ai), which defines the set of valid values it can hold.

• The degree or arity of a relation is the number of attributes n in the schema.

---

Relation Instance

A relation instance (r) of the schema R(A1, A2, A3, ..., An) is denoted as:

r(R) = {t1, t2, t3, ..., tm}

Where:

• Each t is an n-tuple, an ordered list of n values:
  t = ⟨v1, v2, v3, ..., vn⟩

• Each value vi (1 ≤ i ≤ n) is either:

  • From the domain dom(Ai), or

  • A special NULL value, representing unknown or inapplicable data.

---

Characteristics of Relations

1. Ordering of Tuples

   • Tuples (rows) in a relation are not ordered.

   • The order of tuples does not affect the relation.

2. Ordering of Values within a Tuple

   • Although the tuples themselves are unordered, the position of each value in a tuple matters, as it corresponds to a specific attribute.

   • This ordering defines the structure of the data in the tuple.

3. Values and NULLs in Tuples

   • Each value in a tuple must be:

     • Atomic: Indivisible within the relational model.

     • From the attribute's domain or NULL.

   • Composite and multivalued attributes are not allowed in the basic relational model.

   • NULL is a special marker used to indicate: Unknown values, Inapplicable values.

   • NULLs do not imply zero or empty string—they are distinct.

---

Relational Model Notation

The following notation is commonly used:

1. A relation schema R of degree n is denoted by:
   R(A1, A2, A3, ..., An)

2. Uppercase letters Q, R, S denote relation names.

3. Lowercase letters q, r, s denote relation states (instances of relations).

4. Letters t, u, v denote tuples.

5. The name of a relation schema (e.g., STUDENT) also refers to the current set of tuples (state) in that relation.

6. An attribute A belonging to a relation R can be qualified using dot notation: R.A. For example, STUDENT.Name or STUDENT.Age. This is because the same name may be used for two attributes in different relations. However, all attribute names in a particular relation must be distinct.

7. An n-tuple t in a relation instance r(R) is denoted as:
   t = ⟨v1, v2, ..., vn⟩, where each vi corresponds to the value of attribute Ai.

8. Tuples can also be denoted using named attributes: Example:
   t[Name] = ⟨'Barbara Benson'⟩
t[Ssn, Gpa, Age] = ⟨'533-69-1238', 3.25, 19⟩

Relational Keys

Keys are used to uniquely identify records in a relation and enforce relationships between tables.

1. Super Key (SK)

   • Any combination of one or more attributes that can uniquely identify a record (tuple) in a relation.

2. Candidate Key (CK)

   • A minimal super key—no unnecessary attributes.

   • Must be unique and contain no null values.

3. Primary Key (PK)

   • One of the candidate keys selected to uniquely identify records in the table.

   • Chosen for simplicity (typically the one with the least number of attributes).

   • Cannot contain null values.

4. Alternate Key (AK)

   • Candidate keys not chosen as the primary key.

   • Still unique and non-null.

5. Foreign Key (FK)

   • An attribute (or set of attributes) in one table that refers to the primary key in another table.

   • Enables cross-table referencing.

   • Example: cust_id in the Orders table referencing id in the Customer table.

6. Composite Key

   • A primary key that consists of two or more attributes.

7. Compound Key

   • A composite key that also includes two foreign keys, used to link two different tables together.

8. Surrogate Key

   • A system-generated or artificial key (e.g., auto-incremented integer) used as the primary key.

   • Has no business meaning and is often used for simplicity and performance.