Database Management Systems Lecture 17 PDF

Database Management Systems Relational Query language, Algebra & Unary Operations 1 Database Management Systems Unit 2: Relational Model and Database Design Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarsha...

Database Management Systems Relational Query language, Algebra & Unary Operations 1 Database Management Systems Unit 2: Relational Model and Database Design Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarshan, McGrawHill, 7th Edition, 2019. And Author Slides of Fundamentals of Database Systems”, Ramez Elamsri, Shamkant B Navathe, Pearson, 7th Edition, 2017. 2  Database Management Systems Unit 2 overview: 1. Relational Query language, Algebra & Unary Operations 2. Binary Operations 3. Set Operations, Equivalent Queries 4. Aggregate Functions and Grouping 5. SQL overview, Data definition, Structure of SQL queries 6. Database Modification 7. Additional Basic Operations 8. Set Operations and Null Values 9. Aggregate Functions 10. Nested Subqueries 11. Join expressions 12. Index definitions, Authorization 13. Functions, Procedures, Cursors 14. Triggers and Recursive Queries 3 Database Management Systems Relational Algebra A procedural language that consists of a set of operations, where each operation takes one or two relations as input and produces a new relation as the result. The result of an operation is a new relation, which is formed from one or more input relations. This property makes the algebra "closed" (all objects in relational algebra are relations) These relations can be further manipulated using operations of the same algebra. A sequence of relational algebra operations forms a relational algebra expression. The result of a relational algebra expression is also a relation that represents the result of a database query. 4 Database Management Systems Unit 2: Relational Algebra Operators: Unary Operators: Select: σ Project: ∏ Rename: ρ Binary Operators: Union: U Intersection :∩ Set difference: – Cartesian product: X Join: ⋈ 5 Database Management Systems Unit 2: Relational Algebra Use this table for query examples: University Database Employee Database: 6 Database Management Systems Unary Operator: Select (σ) The SELECT operation is used to choose a subset of the tuples from a relation that satisfies a selection condition. We can consider the SELECT operation to be a filter that keeps only those tuples that satisfy a qualifying condition. Notation: σ p (r) p is called the selection predicate The SELECT operation can also be visualized as a horizontal partition of the relation into two sets of tuples—those tuples that satisfy the condition and are selected, and those tuples that do not satisfy the condition and are filtered out. 7 Database Management Systems Unary Operator: Select (σ) Example 1: Select those tuples of the instructor relation where the instructor is in the “Physics” department. Query: σ(Dept_Name = “Physics”)(Instructor) Example 2: Select the tuples for all employees who either work in department 4 and make over $25,000 per year, or work in department 5 and make over $30,000. Query: σ(Dno=4 AND Salary>25000) OR (Dno=5 AND Salary>30000)(EMPLOYEE) 8 Database Management Systems Unary Operator: Select (σ) SELECT Operation Properties The SELECT operation σ ( ) produces a relation S that has the same schema (same attributes) as R SELECT is commutative: Because of commutative property, A cascade (sequence) of SELECT operations may be applied in any order: or 9 Database Management Systems Unit 2: Relational Model and Database Design We allow comparisons to be used in the selection predicate. =, ≠, >, ≥. 90,000 (instructor) The select predicate may include comparisons between two attributes. Example: Fetch all the details of the ‘Research’ department. σ dept_name=“Research” (department) NOTE: The number of tuples in the result of a SELECT is less than (or equal to) the number of tuples in the input relation R. 10 Database Management Systems Unary Operator: PROJECT(∏) PROJECT(∏) A unary operation that returns its argument relation, with certain attributes left out. Notation: ∏ A1,A2,A3 ….Ak (r) where A1, A2, …, Ak are attribute names and r is a relation name. The PROJECT operation, selects certain columns from the table and discards the other columns. Duplicate rows removed from result, since relations are sets This operation keeps certain columns (attributes) from a relation and discards other columns. The PROJECT operation can be visualized as a vertical partition of the relation The list of specified columns (attributes) is kept in each tuple. The other attributes in each tuple are discarded. 11 Database Management Systems Unary Operator: PROJECT(∏) Example 1: Eliminate the dept_name attribute of instructor Query: ∏ID, name, salary (instructor) Result: 12 Database Management Systems Unary Operator: PROJECT(∏) Example 2: Fetch the Lname, Fname, Salary information about the Employees. Query: ∏Lname, Fname, Salary (EMPLOYEE) Example 3: Fetch the Sex and Salary information about the Employees. Query: ∏Sex, Salary (EMPLOYEE) output for example 2 output for example 3 13 Database Management Systems Unary Operator: PROJECT(∏) Properties of PROJECT operator: The project operation removes any duplicate tuples. This is because the result of the project operation must be a set of tuples. Mathematical sets do not allow duplicate elements. The number of tuples in the result of projection ∏(R) is always less or equal to the number of tuples in R. If the list of attributes includes a key of R, then the number of tuples in the result of PROJECT is equal to the number of tuples in R. 14 Database Management Systems Composition of Relational Operations The result of a relational-algebra operation is a relation and therefore of relational-algebra operations can be composed together into a relational-algebra expression. Example: Find the names of all instructors in the Physics department. ∏name(σ dept_name =“Physics” (instructor) ) Instead of giving the name of a relation as the argument of the PROJECT operation, we give an expression that evaluates to a relation. We may want to apply several relational algebra operations one after the other. Either we can write the operations as a single relational algebra expression by nesting the operations, or we can apply one operation at a time and create intermediate result relations. 15 Database Management Systems Relational Algebra Expressions To retrieve the first name, last name and salary of all employees who work in the department 5, we must apply a select and a project operation. We can write a single relational algebra expression as follows: ∏Fname,Lname,Salary(σ DNo = 5(Employee)) Or we can explicitly show the sequence of operations, giving a name to each immediate relation: DEP5_EMPS ← σ DNO=5 (Employee) RESULT ← ∏ Fname,Lname,Salary (DEP5_EMPS) 16 Database Management Systems Unary Operator: RENAME (ρ) The general RENAME operation r can be expressed by any of the following forms: rS (B1, B2, …, Bn )(R) changes both: ○ the relation name to S, and ○ the column (attribute) names to B1, B1, …..Bn rS(R) changes: ○ the relation name only to S r(B1, B2, …, Bn )(R) changes: ○ the column (attribute) names only to B1, B1, …..Bn 17  Database Management Systems Unary Operator: RENAME (ρ) The results of relational-algebra expressions do not have a name that we can use to refer to them. The rename operator, ρ , is provided for that purpose. The RENAME operator is denoted by ρ(rho) The expression: ρx (E) returns the result of expression E under the name x Another form of the rename operation: ρx(A1,A2,.. An) (E) In some cases, we may want to rename the attributes of a relation or the relation's name or both. Useful when a query requires multiple operations 18 Database Management Systems Unary Operator: RENAME (ρ) This is a unary operator which changes attribute names for a relation without changing any values. Renaming removes the limitations associated with set operators. Notation: ρOldName->NewName (r) For Example, ρFather->Parent (Paternity). For the given operation, the attribute “Father” will be renamed as “Parent” for the relation Paternity. If there are two or more attributes involved in renaming operation, then ordering is more meaningful. ρBrach,Salary->Location,Pay (Employee) 19  Database Management Systems Unary Operator: RENAME (ρ) RESULT will have the same attribute names as DEP5_EMPS (same attributes as EMPLOYEE The 10 attributes of DEP5_EMPS are renamed to F, M, L, S, B, A, SX, SAL, SU, DNO, respectively Note: the symbol is an assignment operator 20  Database Management Systems The Assignment Operator (←) It is convenient at times to write a relational-algebra expression by assigning parts of it to temporary relation variables. The assignment operation is denoted by ← and works like assignment in a programming language. Example: Fetch the FirstName, LastName and Salary of all employees who work in department 5. With the assignment operation, a query can be written as a sequential program consisting of a series of assignments followed by an expression whose value is displayed as the result of the query. 21 Database Management Systems The Assignment Operator (←) Example: 22 Database Management Systems Questions Scenario: You have a table Employees with columns EmployeeID, Name, Department, Salary, and JoiningYear (eg:2016). Question: How would you create a query to find the names and departments of employees who joined before 2020, but without showing the EmployeeID and Salary columns? Scenario: A database contains a Customers table with fields CustomerID, FirstName, LastName, City, and PurchaseAmount. Question: If you need to rename the City column to Location, write the relational query for this scenario. Scenario: You are working with a table Products with columns ProductID, ProductName, Category, Price, and StockQuantity. Question: How would you select the ProductName and Price of all products that are in the Electronics category and have more than 10 items in stock? 23 Database Management Systems Questions Scenario: You have a table Employees with columns EmployeeID, Name, Department, Salary, and JoiningYear (eg:2016). Question: How would you create a query to find the names and departments of employees who joined before 2020, but without showing the EmployeeID and Salary columns? Answer: ∏Name,Department(σ JoiningYearLocation (Customers) 24 Database Management Systems Questions Scenario: You are working with a table Products with columns ProductID, ProductName, Category, Price, and StockQuantity. Question: How would you select the ProductName and Price of all products that are in the Electronics category and have more than 10 items in stock? Answer: ∏ProductName,Price(σ Category=’Electronics’∧ StockQuantity>10(Products)) 25 THANK YOU [email protected] 26