Relational Database Model
Relational model represents data as a collection of tables. A table is also called a relation. A relation typically contains a set of rows or tuples. The data elements in each row represent certain facts that correspond to a real-world entity or relationship. Each column has a column header that gives an indication of the meaning of the data items in that column. The column headers are also called attributes.
Key Concepts in the Relational Model
1. Domain:
A domain is a set of atomic values allowed for an attribute.
Example: The domain of an attribute "Name" might be a string of characters, while the domain for "Employee_age" could be an integer between 20 and 70.
2. Relational Schema:
A relational schema is the blueprint of a relation, denoted as R(A1, A2, ..., An), where R is the relation's name, and A1, A2, ..., An are its attributes.
Example:
- CUSTOMER (Cust-id, Cust-name, Address, Phone#)
- CUSTOMER is the relation name
- Defined over the four attributes: Cust-id, Cust-name, Address, Phone#
Each attribute has a domain or a set of valid values. For example, the domain of Cust-id is 6 digit numbers.
3. Degree of a Relation:
The degree of a relation is the number of attributes in it.
Example: A relation with four attributes has a degree of four.
5. Cardinality:
Cardinality refers to the number of tuples (rows) present in a relation.
Example: If a table contains 50 rows, its cardinality is 50.
6. Relational Database Schema:
A relational database schema comprises multiple relational schemas and a set of integrity constraints. It defines the structure of the database and the rules governing the data.
7. Relation State (Instance):
The relation state, or instance, is the set of tuples in a relation at a specific point in time. It represents the current data in the database.
8. Keys in a Relation:
1. Super Key:
- A super key is a set of one or more attributes (columns) that, taken together, uniquely identify each record in a table.
- Every relation has at least one super key → set of all attributes.
- Example: SK = {RollNo}, {Email}, {RollNo, Name}, {RollNo, Age}, {RollNo, Email}, {Name, Email}, {Age, Email}, {RollNo, Name, Age, Email}.
2. Minimal Key:
- It is the smallest set of attributes required to ensure uniqueness.
- Example: In a table of Student, if {RollNo, Email} is a super key, but removing Email still ensures uniqueness, {RollNo} is the minimal key.
3. Candidate Key:
- A candidate key is a minimal super key, meaning it is a set of attributes that uniquely identify each record, and removing any attribute from the set would cause it to lose its uniqueness.
- Each table can have multiple candidate keys.
- Example: In a table of Student, both RollNo and Email could be candidate keys because they uniquely identify each student.
4. Primary Key:
- The primary key is a candidate key chosen by the database designer to uniquely identify records in a table.
- It is the key used for establishing relationships between tables and enforcing data integrity constraints.
- Primary keys must be unique and non-null for each record.
- Example: In a table of Student, the RollNo might be chosen as the primary key.
5. Composite Key:
- A composite key is a primary key composed of multiple attributes.
- It is used when a single attribute does not provide enough uniqueness.
- Example: {FirstName, LastName} in a table where no single attribute is unique on its own.
Characteristics of Relations
1. Uniqueness: No duplicate rows are allowed in a relation.
2. Order: The order of tuples does not matter, but the order of values within a tuple does.
3. Atomicity: Each attribute in a tuple holds an atomic (indivisible) value.
4. Nulls: Attributes can contain null values, representing unknown or inapplicable information.
Relational Model Constraints
1. Inherent Constraints:
Constraints that are inherent in the relational model, such as the prohibition of lists as attribute values.
2. Schema-based Constraints:
Constraints explicitly defined in the schema, such as domain constraints and key constraints.
3. Application-based Constraints:
Constraints enforced by application logic beyond the relational model’s expressive power.
Schema-based Constraints
1. Domain Constraints:
Ensure that each attribute value falls within the specified domain.
Example: An attribute Age should only accept integers between 0 and 120.
2. Key Constraints:
Ensure that no two tuples can have identical values for the key attributes.
Example: No two rows in a student table can have the same RollNo.
3. Entity Integrity Constraint:
The primary key of a relation cannot contain null values.
4. Referential Integrity Constraint:
It is between two relations. States that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation. Tuples in the referencing relation R1 have attributes FK (called foreign key attributes) that reference the primary key attributes PK of the referenced relation R2.
Example: In an Student table, DNO must match an existing DNO in the Department table.
Update Operations on Relations
1. Inserting a Tuple:
Description: Adding a new record to a relation.Possible Violations:
- Domain Constraint: Inserted attribute values must adhere to specified domains.
- Key Constraint: The value of key attributes in the new tuple must not already exist in the relation.
- Referential Integrity: Foreign key values in the new tuple must reference existing primary key values.
- Entity Integrity: Primary key value cannot be null in the new tuple.
2. Deleting a Tuple:
Description: Removing an existing record from a relation.Possible Violations:
- Referential Integrity: Deleting a tuple may violate referential integrity if its primary key is referenced from other tuples.
3. Modifying a Tuple:
Description: Updating attribute values of an existing tuple.Possible Violations:
- Domain Constraint: Modified attribute values must remain within specified domains.
- NOT NULL Constraint: Attributes being modified cannot be set to null.
- Other Constraints: Depending on the attribute being updated, key constraints or referential integrity may also be violated.
- Updating Primary Key (PK): Similar to a delete followed by an insert, necessitating similar options.
- Updating Foreign Key (FK): May violate referential integrity.
- Updating Ordinary Attribute: Can only violate domain constraints.
Handling Violations:
- Cancel the operation (NO ACTION).
- Inform the user of the violation.
- Trigger additional updates to correct the violation (CASCADE or SET NULL):
- CASCADE option: propagate the new primary key value into the foreign keys of the referencing tuples
- SET NULL option: set the foreign keys of the referencing tuples to NULL
- Execute a user-specified error-correction routine.
