A school having 500 students wants to identify beneficiaries of the merit-cum means scholarship, achieving mor

Exercises · Q2

Q. A school having 500 students wants to identify beneficiaries of the merit-cum means scholarship, achieving more than 75% for two consecutive years and having family income less than 5 lakh per annum. Briefly describe data processing steps to be taken by the school to prepare the list of beneficiaries.

Tamil Nadu DgeTextbookSubjective· 3mImportance★★★★★est

9% · 9/97 Questions

✓ Free question

Concept understanding — Set Operations

Relational Algebra Operations: A First Look

Think of a relational database as a collection of neat, rectangular tables. Each table has rows (records) and columns (attributes). Now, suppose you want to ask questions of this data — "Which customers live in Delhi?" or "Show me all orders placed last month." Relational algebra is the set of basic operations you use to answer such questions. It is the language of queries at the most fundamental level.

You do not need to write code or do math. You only need to understand what each operation does to a table — like a set of tools in a toolbox.

The Core Operations

There are eight classic operations. They fall into two groups: those that work on one table at a time, and those that combine two tables.

Operations on a Single Table

Select (also called Restrict) — This operation picks certain rows from a table based on a condition. For example, from a table of students, you might select only those rows where the city is "Mumbai". The result is a smaller table with the same columns but fewer rows.

Project — This operation picks certain columns from a table. For example, from a student table with columns Roll No, Name, City, and Marks, you might project only Name and City. The result is a table with fewer columns. Duplicate rows are automatically removed.

Rename — This operation simply gives a new name to the resulting table or to its columns. It is useful when you need to refer to the same table more than once in a query, or when you want clearer column headings.

Note

Select and Project are the two most frequently used operations. Select narrows down rows; Project narrows down columns. Together they let you extract exactly the slice of data you need.

Operations That Combine Two Tables

Union — This combines two tables that have the same structure (same number of columns and compatible data types). The result contains all rows that appear in either table, with duplicates removed. Think of it as "add the rows of one table to the rows of another, but keep only unique ones."

Set Difference — This gives you rows that are in the first table but not in the second. For example, "Which students are enrolled in Course A but not in Course B?"

Intersection — This gives you rows that appear in both tables. For example, "Which customers have bought both a laptop and a printer?"

Cartesian Product — This pairs every row of the first table with every row of the second table. If the first table has 10 rows and the second has 5, the result has 50 rows. This operation is rarely used alone — it is the foundation for the most powerful operation of all.

Join — This is the heart of relational algebra. A join combines rows from two tables based on a related column between them. For example, you have a Customers table and an Orders table. The join operation matches each order to the customer who placed it, using the Customer ID column that appears in both tables. The result is a single table with all the customer details alongside their orders.

Important

The Join operation is what makes relational databases relational. Without it, data in separate tables would remain isolated. Joins let you connect information across tables — customers to orders, students to courses, products to suppliers — and answer questions that span multiple pieces of data.

Why Relational Algebra Matters

You will never write relational algebra directly in a real database system. Instead, you will use a language like SQL (Structured Query Language). But relational algebra is the underlying logic that every SQL query follows. When you write SELECT ... FROM ... WHERE ..., you are really performing a sequence of Select, Project, and Join operations behind the scenes.

Understanding relational algebra gives you a clear mental model of what a query actually does. It helps you reason about whether your query is correct, whether it will return the right rows, and how to break a complex question into simple steps.

Tip

When you face a tricky query question, think in terms of relational algebra first: "Which tables do I need? Which columns? What condition connects them?" Then translate that into SQL. This approach rarely fails.

A Simple Example to Fix the Idea

Imagine two tables:

Students — columns: Roll No, Name, City

Courses — columns: Course ID, Course Name, Roll No (the student enrolled)

You want: "Names of students from Delhi who are enrolled in the course 'Database'."

Here is how relational algebra would break it down:

Select from Students where City = "Delhi" → a temporary table of Delhi students.
Select from Courses where Course Name = "Database" → a temporary table of that course.
Join these two temporary tables on Roll No → a table with Delhi students enrolled in Database.
Project only the Name column → your final answer.

Each step is simple. Together they answer a meaningful question.

Relational algebra is not about computation. It is about thinking in tables — how to slice, combine, and reshape them to get the information you need. Once you see data as tables and queries as operations on those tables, the whole subject becomes intuitive.

Shape of question: Plain theory question asking for a step-by-step data processing workflow.

The school needs to filter students who satisfy three conditions simultaneously: marks above 75% in both years, family income below ₹5 lakh, and eligibility in both datasets. The processing pipeline involves data collection, cleaning, transformation, and set operations to arrive at the final beneficiary list.

Step 1: Data Collection

Gather academic records for the past two years (Year 1 and Year 2) containing student ID, name, and percentage scored. Separately collect family income data for all 500 students with student ID and annual income.

Step 2: Data Cleaning

Remove duplicates, handle missing values (students who left mid-year or have incomplete records), and ensure consistent formatting of student IDs across all datasets so they can be matched reliably.

Step 3: Filter by Academic Criteria

From Year 1 records, create set $A$ = {students with percentage > 75%}.

From Year 2 records, create set $B$ = {students with percentage > 75%}.

Compute the intersection $A \cap B$ to get students who scored above 75% in both years. This ensures the "two consecutive years" requirement.

Step 4: Filter by Income Criteria

From the income dataset, create set $C$ = {students with family income < ₹5 lakh}.

Step 5: Apply Combined Criteria

Compute the intersection $(A \cap B) \cap C$ to identify students who meet both the academic and financial conditions. This is the set of eligible beneficiaries.

Step 6: Generate Final List

Extract complete details (name, class, contact information) for students in the final set and prepare the beneficiary list for scholarship disbursement. Verify the list manually for any edge cases (students who transferred in, siblings, etc.).

Tip

Using student ID as the primary key across all datasets ensures accurate matching. If the school maintains data in spreadsheets, VLOOKUP or database JOIN operations can automate the intersection logic efficiently.

✓Final answer

A \cap B

The school must collect, validate, filter, and verify student data across academic performance and family income to produce a final list of scholarship-eligible students.

Data processing is the systematic transformation of raw information into actionable results. Here the school needs to combine academic records with socio-economic data, apply multiple criteria simultaneously, and produce a verified list — a classic multi-stage filtering and validation workflow.

Data Processing Steps

1. Data Collection

Gather the required information from existing school records and external sources:

Academic records: Marks/percentage for the current year and the previous year for all 500 students (from examination database or mark sheets).
Family income data: Annual family income declarations, typically collected during admission or through a separate scholarship application form. This may require supporting documents like income certificates, salary slips, or tax returns.
Student identification: Roll number, name, class, section to uniquely identify each student.

Watch out

Income data is often self-reported and may be outdated. The collection step must specify the reference financial year (e.g., previous financial year) to ensure consistency.

2. Data Validation and Cleaning

Before applying filters, ensure data quality:

Completeness check: Identify students with missing percentage data for either year or missing income information. Flag these records for manual follow-up.
Range validation: Verify that percentages fall between 0–100 and income values are non-negative.
Format standardization: Ensure income is in a uniform unit (e.g., all in rupees per annum), and percentages are calculated consistently (some schools use CGPA, which needs conversion).
Duplicate removal: Check for duplicate entries of the same student.

3. Data Integration

Combine data from different sources into a single dataset. If academic records and income data are in separate files/tables, merge them using the student ID as the key. The integrated dataset should have columns:

Student_ID	Name	Class	Year1_Percentage	Year2_Percentage	Family_Income

4. Application of Eligibility Criteria (Filtering)

Apply the scholarship conditions sequentially or simultaneously:

Condition 1: Academic performance

Filter students where both Year1_Percentage > 75 AND Year2_Percentage > 75.

Condition 2: Economic criterion

From the filtered set, retain only those with Family_Income < 500000 (5 lakh).

This can be expressed as a logical condition:

(Year1_Percentage > 75) AND (Year2_Percentage > 75) AND (Family_Income < 500000)

If using a spreadsheet, apply filters or use formulas. If using a database:

SELECT Student_ID, Name, Class, Year1_Percentage, Year2_Percentage, Family_Income
FROM Students
WHERE Year1_Percentage > 75 
  AND Year2_Percentage > 75 
  AND Family_Income < 500000;

If using Python with pandas:

import pandas as pd

# Assume df is the integrated DataFrame
beneficiaries = df[
    (df['Year1_Percentage'] > 75) & 
    (df['Year2_Percentage'] > 75) & 
    (df['Family_Income'] < 500000)
]

5. Verification and Cross-checking

The filtered list is provisional. Verify:

Document verification: Cross-check income certificates against declared income for a sample or all shortlisted students.
Academic record audit: Confirm percentages from official mark sheets, not just internal records.
Eligibility window: Ensure "two consecutive years" refers to the intended academic years (e.g., Class 11 and Class 12, or the last two completed years).

6. Sorting and Ranking (Optional)

If the number of beneficiaries exceeds available scholarships, the school may:

Sort by average percentage (descending) or by family income (ascending) to prioritize within the eligible pool.
Apply additional tie-breaking criteria (attendance, extracurricular achievements).

7. Final List Preparation and Approval

Generate the final beneficiary list with all relevant details: Student ID, Name, Class, both years' percentages, family income.
Obtain approval from the scholarship committee or school administration.
Maintain an audit trail: document the number of students at each filtering stage (e.g., 500 total → 320 with >75% both years → 85 with income <5L).

8. Communication and Disbursement

Notify selected students and their parents.
Publish the list (with privacy considerations — perhaps only roll numbers publicly, full details privately).
Proceed with scholarship disbursement as per the scheme guidelines.

Tip

Automate steps 3–5 using a database query or a Python/Excel script to minimize manual errors and ensure reproducibility. A simple script can generate the list in seconds and provide a count at each filter stage for transparency.