Relational Algebra Concepts Quiz

Lecture 3: Additional, Extended Relational Algebra Operators and Modification Operations

• This lecture covers additional, extended relational algebra operators and modification operations in database systems.
• The outline includes additional operators, extended operators, and modification operations.
• Fundamental operators include select, project, union, set difference, Cartesian product, and rename.
• Additional operators include set intersection, natural join, division, and assignment.
• Extended operators include Generalized Project, Aggregate Functions, and Outer Joins.
• Modification operations include deletion, insertion, and updating of tuples.

Additional Operations

• Goal: Simplify common queries.
• Set intersection: Finds tuples present in both relations. It is based on set theory's intersection operation.
• Natural join: Combines relations based on common attributes. More generally via theta join and equi-join.
• Division: Finds values in a relation that relate to all values in another relation.
• Assignment: Allows assigning results of operations to variables.

Set-Intersection Operation

• Notation: r ∩ s
• Assumes relations r and s have the same arity (number of attributes) and compatible attribute domains.
• Finds tuples that are in both relations.
• rs = { t | t ∈ r and t ∈ s }

Natural-Join Operation

• Notation: r ⋈ s
• Combines two relations based on common attributes.
• Finds tuples with the same values in common attributes.

Natural-Join Operation: Example 2

• Relations r, s: Includes tables with attributes A, B, C, D etc for relations r and s.
• r ⋈ s: Includes a table with the combined attributes and tuples meeting the join conditions.

Natural-Join Operation: Example 3

• Find the name of all customers who have an account with the bank, along with his/her account number and the balance of the account.
• Includes tables ‘depositor’ and ‘account’.

How to Generate Result of ⋈?

• Tuples from relations r and s with matching common attributes are included in the result.

Natural-Join Operation: Example 4

• Find the names of all branches with customers who have an account in the bank and who live in the city of Harrison.
• Includes ‘customer’, ‘account’, and ‘depositor’ relations.

Natural-Join Operation: Example 5

• Find all customers who have both a loan and an account at the bank.
• Uses a natural join to combine 'depositor' and 'borrower' relations.

Natural-Join Operation: Example 6

• Find all account numbers managed by any of branches in the city of Horseneck.
• Combines relations 'branch' and 'account'.

Theta Join Operator

• Theta Join (Condition Join): A more generalized join operation defining a condition based on propositional calculus.
• The condition involves attributes or constants using comparison operators (=, ≠, >, <, ≤, ≥).

Theta Join Operator: Example 1

• Find the name of all customers who have an account with the bank, along with his/her account number and the balance of the account.
• Includes 'account' and 'depositor' relations.
• Combines those based on account number.

Theta Join Operator: Example 2

• Find the customer names having loans and the loan amounts if the value of loan is more than 1000.
• Combines customer and loan relations based on matching loan numbers. Data selection is also applied to find loans over 1000.

Division Operation

• Notation: r ÷ s
• Purpose: Used for queries including "for all" clauses.

Division Operation - Example 1

• Relations r, s: Includes relation tables containing attributes A and B for r and B alone for s.
• Find values of attribute A which relate to all values in attribute B in relation s.

Division Operation - Example 2

• Relations r, s: Includes relation tables containing attributes A, B, C and D,E.
• Find values of attributes A, B, C that relate to all values in D,E of relation s.

Division Operation - Example 3

• Find the names of customers who have an account at all the branches located in the city of Brooklyn.
• Includes relational tables for ‘branch’, ‘account’, and ‘depositor’.

Assignment Operation

• Purpose: Used to write complex queries as sequential programs consisting of assignments.
• Assignments are always done to temporary relation variables.

Insertion Operations

• Insertion involves adding tuples into a database relation.
• Tuples added must meet relation criteria. (same arity, same data types).
• New data can be inserted via relational algebra expressions.

Insertion Example -1

• Insert info regarding Smith having $1200 in account A-973 at Perryridge branch, given Smith is an existing customer.
• Includes relations ‘account’ and ‘depositor’. (e.g., the same customer can be in the accounts table and the depositor table ).

Insertion Example - 2

• Insert information to specify that Smith has $1200 in account A-973 at the Perryridge branch. (Assuming Smith exists).
• Includes relations ‘account’ table and ‘depositor’ table.

Insertion Example - 3

• Specify the addition of a $200 savings account for all loan customers in Perryridge branch; where the loan number is used as the account number.
• Includes relations ‘borrower’, ‘account’ and ‘depositor’ relations.

Insertion Example - 4

• Provide a $200 savings account for every loan customer in Perryridge branch using the loan number as account number for the new savings account.

Updating Operations

• Modifies specific values within a tuple without changing other values within that tuple.
• Achieved via generalized projections, with relevant attribute values updated.
• Attribute in the tuple can be updated.

Update Example -1

• To increase all balances by 5% as interest payments.

Update Example - 2

• Increase all balances by 5%.

Update Example - 3

• Pay all accounts with balances over $10,000 6 percent interest and pay all others 5 percent.

Update Example - 4

• Pay accounts with balance more than 10k 6 percent and rest 5 percent.

Note about Updating

• Ensure the specific tuple(s) match, otherwise results may change unintended tuples.
• Deletion could lead to removing tuples unintentionally, if the criteria doesn't match precisely.
• Insertion could add extra tuples unintentionally with overly broad criteria.

Outer Join

• Avoids losing information in joins by including tuples without matching counterparts. These are identified by null.
• Left Outer Join: Includes all tuples from Left Relation even if not matched in Right Relation
• Right Outer Join: Includes all tuples from Right Relation even if not matched in Left Relation
• Full Outer Join: Includes all tuples from both relations, including instances where not matched in the other.

Effect of Null in Arithmetic Operations and Predicate Logic

• The result any arithmetic operation involving null is null.
• The result of any comparison operation involving null is unknown.
• Three-valued logic uses unknown as a truth value.

How Do Relational Operations Deal with Null Values? -1

• If a selection returns unknown for a tuple, that tuple is not included in the result.
• This applies to selection operations, applying this filter to all the tuples concerned.

How Do Relational Operations Deal with Null Values? -2

• For union, intersection, and difference operations involving nulls are handled similarly to projection operations.
• Aggregate functions handle nulls, in relation to group attributes similarly to projection operations.
• Aggregated attributes (where aggregation applies) may delete or ignore nulls from any input operations.

Aggregate Functions and Grouping -1

• Aggregate functions (e.g., sum, min, max, avg) take a group of values and calculate a single resultant value.
• These are employed with grouping attributes to calculate the result over a group of rows having identical grouping attributes values.

Aggregate Operations Example 1

• Calculate the sum, average, minimum and maximum value for a column 'c' of relation ‘r’.
• Includes the attributes ‘a’, ‘b’, ‘c’

Aggregate Operations Example 2

• Find count of c, count of b and number of distinct values in b and a in relation ‘r’.

Aggregate Operations Example 3

• Includes calculations involving grouping attributes ‘b’, and ‘a’, as well as the aggregate function ‘sum(c)’.

Aggregate Operations Example 4

• Includes calculations involving grouping attributes ‘a’ and ‘b’, as well as aggregate functions ‘sum(c)’, ‘max(c)’, and ‘min(c)’.’

Aggregate Operations Example 5

• Calculate the total balance of accounts at each branch location.

Aggregate Operations Example 6

• Calculate the total balance of accounts at each branch location.
• Rename aggregation attribute to ‘branch_total’.

Aggregate Operations Example 7

• Find branches with maximum average account balance.

Practice 3

1.Retrieve customers' name, branch name and balance of an account whose balance is between 500 and 700 inclusive. 2. Retrieve all branch information that has assets more than the asset at the branch “Round Hill”. 3. Retrieve customers' name whose loan account in both “Round Hill” and “Redwood”. 4. Retrieve customers' name whose account either in “Downtown” or “Mianus” or both. 5. Retrieve customers' name, account number and balance of customers who have joined account. 6. Retrieve customers' name and account number who have more than one account.

Practice 3 (Cont.)

7. Retrieve highest total assets of all branches that are located in the same city. 8, Retrieve average balance of all customers who lives in "Harrison" and "Stamford".
8. Retrieve the number of customers who have more than one account.
9. Retrieve the number of accounts that have more than one account holder.

Test your understanding of relational algebra with this quiz covering operations like natural-join, set intersection, and more. You will answer questions about common relational operations and their results. Perfect for students studying database management systems.