B.Tech Database Management Systems CS-502 Unit 2

Relational Data Models

• Domain: A named set of atomic values, indivisible from the database's perspective.
  • Examples include:
    • SSN: 9-digit number
    • Name: String starting with an uppercase letter
    • GPA: Real number between 0.0 and 4.0
    • Sex: {female, male}
    • Dept_Code: {CMPS, MATH, ENGL, etc.}
• Attribute: Role name associated with a value from a specific domain; denoted as Dom(A).
• Tuple: A mapping of attributes to values, representing an entity or relationship (e.g., {Name → "Keerthy", Sex → Male, IQ → 786}).
• Relation: A set of tuples with the same attributes, commonly referred to as a table.
• Relational Schema: Structure description of a relation (e.g., R(A1, A2, ..., An)), indicating attributes and their domains.
• Relational Database: Collection of relations that align with their specified schemas.

Characteristics of Relations

• Ordering of Tuples: Relations are sets, so tuples have no inherent order; referencing specific tuples (e.g., 5th tuple) is meaningless.
• Intension vs. Extension:
  • Intension: Permanent schema of a relation that does not change over time.
  • Extension: Set of tuples present in a relation at any moment; varies over time.

Integrity Constraints

• Naming Structure: Includes the name of the relation and its attributes.
• Set of Integrity Constraints: Includes various rules like:
  • Entity Integrity Rule: Ensures primary keys are unique and not null.
  • Referential Integrity Rule: Maintains consistency between related tables.
  • Domain constraints for data value limitations.

Relational Query Languages

• Relational Query Languages: Utilize relational algebra to execute user requests.
  • Procedural Languages: Specify how to obtain results through a sequence of operations (e.g., retrieve student marks and compute CGPA).
  • Non-Procedural Languages: Define what data is needed without specifying how to fetch it (e.g., SQL).

SQL Language Types

• Types of SQL Statements:
  • DML (Data Manipulation Language): Operations for manipulating data.
  • DDL (Data Definition Language): Statements for defining and modifying database structure.
  • DCL (Data Control Language): Permissions and security.
  • TCL (Transaction Control Language): Manage transactions in databases.

Query Operations

• Combining Selection and Projection: Often combined to filter and display specific data from one relation efficiently.
  • Example for selection of U.S. Supreme Court justices born in Arizona before 1935 using a selection (σ) and projection (π) operation.
• Union Operation: Merges tuples from two relations with the same schema; noted as R3 = R1 ∪ R2.

Joins

• Equi-Join vs. Natural Join: Equi-joins match values from different relations based on a specified attribute, while natural joins do so automatically on common attributes.

Summary of Relational Algebra Operations

• SELECT (σ): Retrieves tuples that meet a specific condition.
• PROJECT (π): Retrieves specified attributes, eliminating duplicates.
• UNION (∪): Combines tuples from two relations, ensuring union compatibility.

Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC)

• TRC: Based on retrieving entire tuples that match given conditions.
• DRC: Focuses on selecting specific attributes based on conditions, expressed as {< a1, a2, ..., an > | P(a1, a2, ..., an)}.

Variables in Relational Calculus

• Bound Variables: Maintain meaning even if replaced (e.g., conditions using ‘For All’).
• Free Variables: Change meaning if replaced (e.g., different tuple variable results in a different query).

Well-Formed Formulas (WFF)

• Closed WFF: All variables are bound.
• Open WFF: Contains at least one free variable, affecting the query's outcome.

These notes provide foundational understanding essential for mastering Database Management Systems concepts in the context of relational data models.

Explore the fundamentals of Relational Data Models in Database Management Systems with this quiz for B.Tech students. Focus on domain definitions and examples of atomic values. Test your knowledge on this critical topic covered in the 5th semester.