Relational Model Concepts

The Relational Model is the theoretical foundation for most modern database systems it uses a collection of tables as relations to represent both data and the relationships among that data.

The core concepts of the Relational Model revolve around representing data in a structured, tabular format and defining rules to maintain its integrity and consistency. Focusing on the logical structure of the data, abstracting away the physical storage details.

Core Components of the Relational Model

The model is built on a few key terminologies that map directly to the structure of a table.

• Relation: A relation is the formal term for a table. It's a two-dimensional structure composed of rows (tuples) and columns (attributes) .

• Tuple: A tuple is the formal term for a row. Each tuple represents a single record or a real-world entity (e.g., a specific student, a particular product) where each value corresponds to an attribute.

  • Cardinality: The cardinality of a relation is the total number of tuples (rows) it contains.

• Attribute: An attribute is the formal term for a column header. Each attribute has a unique name representing a property or characteristic of the entity (e.g., StudentID, FirstName, Major).

  • Degree (or Arity): The degree of a relation is the total number of attributes (columns) it has.

• Domain: A domain is the set of all permissible, atomic values for an attribute. It defines the data type (e.g., integer, string) and any constraints on the values an attribute can hold (e.g., the domain for a Month attribute could be the set {Jan, Feb, ..., Dec}). An atomic value is one that is indivisible from the model's perspective.

Fundamental Characteristics of a Relation

For a table to be considered a valid relation in the formal model, it must adhere to the following properties:

1. No Duplicate Tuples: Since a relation is a set of tuples, every tuple in a relation must be unique, duplicates are not permitted. SQL implementations often allow duplicates unless uniqueness is explicitly enforced (e.g., via a key constraint or the DISTINCT keyword).

2. Tuples are Unordered: The order of rows has no significance. A relation is an unordered set, so rearranging the tuples does not change the relation.

3. Unique Attribute Names: All attribute names within a single relation must be unique.

4. Attributes are Unordered: The order of columns is also insignificant. Attributes are identified by their name, not by their position.

5. Atomic Values: Every value in a tuple at the intersection of a row and column must be atomic (indivisible). This means no multivalued attributes (e.g., a list of phone numbers in one cell) or composite attributes are allowed. Relations that follow this rule are said to be in First Normal Form (1NF).

---

Relation Schema

Is the logical design of database, blueprint or definition of a relation. It includes the name of the relation and the set of its attributes and their corresponding domains. Schema generally does not change.

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

• R is the name of the relation (e.g., STUDENT).
• A₁, A₂, ..., Aₙ are the attributes (e.g., StudentID, Name, Major).

Relation Instance

The relation instance (or relation state) is a snapshot of the actual data in the relation at a specific point in time. It is a set of tuples that conform to the relation's schema. It changes over time as data is updated.

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

• r(R) is a specific instance of the relation R.
• Each t is a tuple (row) containing values for each attribute, where each value is drawn from that attribute's domain or is NULL.

Relational Keys

In a relational database, keys are one or more attributes (columns) that serve two primary purposes:

1. Uniquely identify each record (row or tuple) in a table.
2. Establish relationships and enforce links between different tables.

Keys for Ensuring Uniqueness : This group of keys is focused on identifying unique records within a single table.

1. Super Key (SK) : A super key is any set of one or more attributes that can uniquely identify a row in a table. It's the most general type of key.

2. Candidate Key (CK) : A candidate key is a minimal super key. This means it's a super key with no redundant (unnecessary) attributes. You can't remove any attribute from a candidate key and still have it guarantee uniqueness. Must be unique and contain no null values.

3. Primary Key (PK) : The primary key is the one candidate key that is selected to be the main unique identifier for record in the table. It must be unique and cannot contain NULL values. The best primary keys are stable and simple.

4. Alternate Key (AK) : An alternate key is any candidate key that was not chosen to be the primary key. They are essentially the runners-up. Still unique and non-null.

Keys for Building Relationships : This key type is used to connect one table to another.

5. Foreign Key (FK) : Is an attribute (or set of attributes) in one table that refers to the primary key in another table, enabling cross-table referencing. FK creates a link between the two relations, enforcing referential integrity (ensuring that a value in the foreign key column corresponds to a valid value in the referenced primary key column).

Keys Defined by Structure : These terms describe the composition or origin of a key.

6. Composite Key : any key (primary, candidate, or foreign) that consists of two or more attributes working together to create a unique identifier.

7. Compound Key : is a specific type of composite key where at least one or both of its attributes is a foreign key. This is common in junction tables joining two different table, used to resolve many-to-many relationships.

8. Surrogate Key : is an artificial key with no business or real-world meaning. It's typically a system-generated, auto-incrementing integer (like EnrollmentID in the ENROLLMENTS table). Surrogate keys are widely used because they are simple, stable, and efficient for the database to manage.

---

Example: The STUDENTS Table

StudentID	NationalID	Email	FirstName	LastName
101	987-65-4321	a.smith@uni.edu	Anna	Smith
102	123-45-6789	b.jones@uni.edu	Ben	Jones
103	555-44-3333	c.taylor@uni.edu	Carol	Taylor

• Super Keys: Any set of attributes that uniquely identifies a row.

  • Examples include (StudentID), (NationalID), (Email), (StudentID, FirstName), and (NationalID, LastName).

• Candidate Keys: The minimal super keys (with no redundant attributes).

  • From the list above, the candidate keys are: (StudentID), (NationalID), and (Email).

  • The set (StudentID, FirstName) is a super key, but not a candidate key because FirstName is redundant, StudentID alone is already unique.

• Primary Key: The one candidate key chosen as the main identifier.

  • We would select (StudentID) as the primary key because it's concise and stable.

• Alternate Keys: The candidate keys that were not chosen as the primary key.

  • In this case, (NationalID) and (Email) are the alternate keys.

---

Example: The ENROLLMENTS Table

EnrollmentID	CourseCode	StudentID
5001	CS101	101
5002	MA203	101
5003	CS101	102

• Foreign Key: An attribute in one table that refers to the primary key of another.

  • The StudentID column in the ENROLLMENTS table is a foreign key that points to the primary key (StudentID) in the STUDENTS table, linking each enrollment to a specific student.

• Composite Key: A key made of two or more attributes.

  • In a table tracking daily attendance, the primary key might be (StudentID, AttendanceDate). Neither attribute is unique on its own, but their combination is.

• Compound Key: A composite key where at least one attribute is also a foreign key.

  • If the primary key for the ENROLLMENTS table was the combination (CourseCode, StudentID), this would be a compound key because StudentID is also a foreign key.