Relational Query Language in DBMS

     

    Relational Query Languages are specialized languages used to interact with and manipulate relational databases. They enable users to query the database for specific information. These languages can be categorized into Procedural and Non-Procedural languages, each serving different purposes and offering different levels of abstraction.

     

    Types of Relational Query Languages

    1. Procedural Query Language
    2. Non-Procedural Query Language

    1. Procedural Query Language

     

    In a Procedural Query Language, the user specifies both what data is required and how to obtain it. This type of language requires users to write a sequence of operations to retrieve the desired result. The emphasis is on the procedure or steps needed to achieve the output.

     
    Example: Relational Algebra

     

    Relational Algebra is a foundational Procedural Query Language for relational databases. It consists of a set of operations that take one or two relations as input and produce a new relation as output. Some common relational algebra operations include:

    1. Select (σ):

      • Definition: Filters rows based on a given predicate.
      • Notation: σ_condition(R)
      • Example: σ_age > 25(STUDENT) retrieves all students older than 25.

    2. Projection (π):

      • Definition: Selects specific columns from a relation.
      • Notation: π_column1, column2,…(R)
      • Example: π_name, phone_no(STUDENT) retrieves only the names and phone numbers of students.

    3. Set Difference (-):

      • Definition: Returns the difference between two relations.
      • Notation: R – S
      • Example: STUDENT – ENROLLED_STUDENT retrieves students who are not enrolled in any course.

    4. Cartesian Product (×):

      • Definition: Combines each tuple of one relation with every tuple of another relation.
      • Notation: R × S
      • Example: STUDENT × COURSE produces a relation combining every student with every course.

    5. Union (∪):Definition: Combines tuples from two relations, eliminating duplicates.

      • Notation: R ∪ S
      • Example: STUDENT ∪ GRADUATED_STUDENT retrieves all students who are either current or graduated.

     

    2. Non-Procedural Query Language

     

    Non-Procedural Query Languages allow users to specify what data they want without specifying how to retrieve it. Users describe the desired result set, and the DBMS determines the best way to get the data.

    Example: Relational Calculus

    Relational Calculus is a Non-Procedural Query Language that uses mathematical predicates to describe queries. It comes in two forms:

    1. Tuple Relational Calculus (TRC):

      • Definition: Specifies the desired tuples based on a condition.
      • Notation: { T | Condition(T) }
      • Example: { T | T ∈ STUDENT ∧ T.age > 25 } retrieves tuples of students older than 25.

    2. Domain Relational Calculus (DRC):

      • Definition: Specifies the desired data based on domain variables and conditions.
      • Notation: {<d1, d2, …> | Condition(d1, d2, …)}
      • Example: {<name, phone_no> | ∃s (s ∈ STUDENT ∧ s.name = name ∧ s.phone_no = phone_no ∧ s.age > 25)} retrieves names and phone numbers of students older than 25.

     

    SQL (Structured Query Language)

     

    SQL is the most widely used Relational Query Language and incorporates elements of both Procedural and Non-Procedural languages, though it is primarily considered Non-Procedural.

     

    SQL Query Examples

    1. Select Query:

      • SQL: SELECT name, phone_no FROM STUDENT WHERE age > 25;
      • Retrieves names and phone numbers of students older than 25.

    2. Join Query:

      • SQL: SELECT STUDENT.name, COURSE.course_name FROM STUDENT JOIN ENROLLMENT ON STUDENT.student_id = ENROLLMENT.student_id JOIN COURSE ON ENROLLMENT.course_id = COURSE.course_id;
      • Retrieves the names of students and the courses they are enrolled in.

     

    Summary

    • Procedural Query Languages (e.g., Relational Algebra) require users to specify the exact steps to obtain the result.
    • Non-Procedural Query Languages (e.g., Relational Calculus, SQL) allow users to specify what data they want without detailing how to get it.
    • Relational Algebra includes operations like Select, Projection, Set Difference, Cartesian Product, and Union.
    • Relational Calculus includes Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC).

    These relational query languages provide a powerful way to interact with and manage data in a relational database, each suited to different levels of user expertise and application requirements.