Relational algebra and calculus in Database management system

Relational algebra and calculus in DBMS

1. UNIT -2 Relational Algebra & Calculus

2. Relational Algebra Procedural query language used to query and manipulate data in a relational database It works on relations (tables) Produces new relations as output Provides a set of operations to retrieve data

3. Operation Symbol Description Example Union ∪ Combines tuples from two relations (no duplicates) A ∪ B Intersection ∩ Common tuples from both relations A ∩ B Difference − Tuples in A but not in B A − B Cartesian Product × All combinations of tuples from both relations A × B

4. Operation Symbol Description Example Selection σ Selects rows that satisfy a condition σ Age > 20 (Student) Projection π Selects specific columns π Name, Age (Student) Rename ρ Renames a relation or attributes ρ S(Student) Join ⨝ Combines related tuples from two relations Student ⨝ Course Division ÷ Finds tuples related to all tuples in another relation (used in complex queries)

5. SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2;

6. SELECT column1, column2 FROM table1 INTERSECT SELECT column1, column2 FROM table2;

7. Aggregate Functions in DBMS SELECT AGG_FUNC(column) FROM table [WHERE condition]; C ount() Sum() Avg () Min() Max()

8. SELECT COUNT(*) FROM EMPLOYEE; SELECT AVG(SALARY) FROM EMPLOYEE; SELECT MAX(SALARY) FROM EMPLOYEE WHERE DEPTID = 10; SELECT DEPTID, SUM(SALARY) FROM EMPLOYEE GROUP BY DEPTID; SELECT DEPTID, COUNT(*) FROM EMPLOYEE GROUP BY DEPTID;

9. Logical Operators in DBMS A ND OR NOT ALL ANY/SOME In Between Exists

10. SELECT * FROM EMPLOYEE WHERE SALARY > 50000 AND LOCATION = 'New York’; SELECT * FROM EMPLOYEE WHERE LOCATION = 'Chicago' OR SALARY > 65000; SELECT * FROM EMPLOYEE WHERE NOT LOCATION = 'New York’;

11. SELECT * FROM EMPLOYEE WHERE LOCATION IN ('Chicago', 'San Francisco’); SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN 55000 AND 70000;

12. GROUP BY SELECT Department, COUNT(*) FROM Employees GROUP BY Department; SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 1; H AVING

13. SELECT name, age FROM Students ORDER BY age; ORDER BY SELECT name, marks FROM Students ORDER BY marks DESC; SELECT name, age, marks FROM Students ORDER BY age ASC, marks DESC;

14. SELECT TOP 5 name, marks FROM Students ORDER BY marks DESC; TOP SELECT name, marks FROM Students ORDER BY marks DESC LIMIT 5; LIMIT SELECT name, marks FROM Students ORDER BY marks DESC LIMIT 5 OFFSET 5;

15. ORDER BY SELECT Name, Age FROM Employees ORDER BY Age DESC; DELETE FROM Employees WHERE Age < 30;

16. SELECT * FROM Students WHERE Name LIKE 'A%’; SELECT * FROM Students WHERE Name LIKE '%n’; SELECT * FROM Students WHERE Name LIKE '% sh %'; LIKE

17. BETWEEN SELECT * FROM Students WHERE Marks BETWEEN 50 AND 80; SELECT  ProductName FROM  Products WHERE   ProductID =  ANY   ( SELECT   ProductID    FROM   OrderDetails    WHERE  Quantity =  10 ); A NY SELECT column _name FROM table_name WHERE expression operator ANY (subquery);

18. SELECT column_name FROM table_name WHERE expression operator ALL (subquery); A LL SELECT  ProductName FROM  Products WHERE   ProductID =  ALL   ( SELECT   ProductID    FROM   OrderDetails    WHERE  Quantity =  10 );

19. ID Name City 1 Ravi Chennai 2 Anu Mumbai 3 Kiran Chennai 4 Meena Delhi 5 Arjun Mumbai DISTINCT SELECT DISTINCT City FROM Customers; Chennai Mumbai Delhi

20. Alias SELECT Name AS StudentName FROM Students; An alias is a temporary name given to a column or a table in SQL. It is only for display (does not change the real column or table name). Makes query results easier to read.

21. LIKE → pattern match BETWEEN → range check ALL / ANY → comparison with subquery set DISTINCT → remove duplicates

22. StudentID Name CourseID 1 Kavi 101 2 Arjun 102 3 Priya 103 4 Rani 101 Join A JOIN in SQL is used to combine rows from two or more tables based on a related column between them INNER JOIN CourseID CourseName 101 Computer Sci 102 Mathematics 104 Physics SELECT table1.column1, table2.column2, ... FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column; Students Courses

23. LEFT JOIN (LEFT OUTER JOIN) SELECT Students.Name , Courses.CourseName FROM Students LEFT JOIN Courses ON Students.CourseID = Courses.CourseID ; Returns all rows from the left table (Students) + matching rows from right (Courses). If no match, NULL is shown.

24. RIGHT JOIN (RIGHT OUTER JOIN) SELECT Students.Name , Courses.CourseName FROM Students RIGHT JOIN Courses ON Students.CourseID = Courses.CourseID ; Returns all rows from the right table (Courses) + matching rows from left (Students).

25. FULL OUTER JOIN Returns all rows from both tables. If no match, NULL is placed . SELECT Students.Name , Courses.CourseName FROM Students FULL OUTER JOIN Courses ON Students.CourseID = Courses.CourseID ;

26. Name CourseID CourseName Kavi 101 Computer Sci Arjun 102 Mathematics Rani 101 Computer Sci NATURAL JOIN SELECT * FROM table1 NATURAL JOIN table2; CourseID is the common column in both tables → so NATURAL JOIN matches automatically. Priya is excluded ( CourseID = 103 not in Courses). Physics (104) excluded because no student has CourseID 104.

27. Advanced SQL

28. Analytical Queries Often called window functions or analytic functions . These queries go beyond simple GROUP BY and allow calculations across sets of rows while still keeping row-level detail.

29. What Are Analytical Queries Perform calculations across a set of rows related to the current row. Provide rankings, running totals, moving averages, percentiles, etc. Use window functions with the OVER() clause.

30. Employee_id Name Department salary 1 Alice HR 5000 2 Bob HR 6000 3 Charlie IT 7000 4 David IT 8000 5 Eva IT 9000 6 Frank Sales 5500 7 Grace Sales 6500

31. employee_id name department salary dept_total_salary 1 Alice HR 5000 11000 2 Bob HR 6000 11000 3 Charlie IT 7000 24000 4 David IT 8000 24000 5 Eva IT 9000 24000 6 Frank Sales 5500 12000 7 Grace Sales 6500 12000 Example Queries with OVER() Total salary by department SELECT employee_id , name, department, salary, SUM(salary) OVER(PARTITION BY department) AS dept_total_salary FROM employees;

32. What are Ranking Functions Ranking functions assign a rank, position, or number to rows in a result set. They require OVER() with ORDER BY (and often PARTITION BY). ROW_NUMBER() – assigns a unique sequential number to each row (no ties). RANK() – assigns rank, but leaves gaps when values are tied.

33. name department salary row_num Eva IT 9000 1 David IT 8000 2 Charlie IT 7000 3 Frank Sales 6500 4 Grace Sales 6500 5 Bob HR 6000 6 Alice HR 5000 7 SELECT name, department, salary, ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS row_num FROM employees;

34. Name department salary salary_rank Eva IT 9000 1 David IT 8000 2 Charlie IT 7000 3 Frank Sales 6500 4 Grace Sales 6500 4 Bob HR 6000 6 Alice HR 5000 7 SELECT name, department, salary, RANK() OVER(ORDER BY salary DESC) AS salary_rank FROM employees

35. What is a View A View is a virtual table based on the result of an SQL query. It does not store data physically (by default); it just stores the SQL query definition. CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

36. CREATE VIEW high_salary_employees AS SELECT name, department, salary FROM employees WHERE salary > 7000; SELECT * FROM high_salary_employees ;

37. Functional Dependencies A functional dependency (FD) describes a relationship between attributes in a relation (table). If attribute X determines attribute Y , it is written as: X→Y Example: Aadhaar Card → Person Details AadhaarNo →{ Name,DOB,Address } But the reverse is not true (two people can have the same name).

38. Trivial Functional Dependency Non-Trivial Functional Dependency Partial Dependency Full Functional Dependency Types of Functional Dependencies

39. Trivial Functional Dependency A dependency is trivial if the right-hand side (RHS) is a subset of the left-hand side (LHS). Example: {𝑅𝑜𝑙𝑙𝑁𝑜,𝑁𝑎𝑚𝑒}  →  𝑁𝑎𝑚𝑒 (because Name is already part of { RollNo , Name})

40. Non-Trivial Functional Dependency A dependency is non-trivial if RHS is not a subset of LHS Example:𝑅𝑜𝑙𝑙𝑁𝑜  →  𝑁𝑎𝑚𝑒 (Name is not part of RollNo )

41. Partial Dependency When a part of a composite key determines a non-key attribute. Example: In relation ( RollNo , CourseCode → StudentName ), ( RollNo , CourseCode ) is key. RollNo → StudentName (partial dependency, since only part of key determines a non-key). Analogy: From just RollNo , you already know StudentName , even without CourseCode .

42. Full Functional Dependency A dependency where the entire composite key is required to determine the attribute. Example: ( RollNo , CourseCode ) → Grade (both together determine grade). Analogy: You need both RollNo and CourseCode to know the grade.

43. Armstrong’s Axioms Armstrong’s axioms are a set of inference rules used to derive all possible functional dependencies (FDs) from a given set. They form the foundation for reasoning about FDs .

44. If Y ⊆ X , then 𝑋    →    𝑌 Example: { RollNo , Name} → Name (Because Name is part of the left side). Reflexivity Rule

45. Augmentation Rule If X → Y , then XZ → YZ Example: RollNo → Name By Augmentation: { RollNo , Dept} → {Name, Dept} Knowing RollNo + Dept also gives you Name + Dept .

46. Transitivity If X → Y and Y → Z , then X → Z Example 1 (Student Table): RollNo → Dept , Dept → HOD, RollNo → HOD

47. Normalization in DBMS

48. Normalization is a systematic approach to organize data within a database to reduce redundancy and eliminate undesirable characteristics such as insertion, update, and deletion anomalies. Normalization

49. 1NF (First Normal Form) in DBMS Each cell of the table contains atomic (indivisible) values. There are no repeating groups or arrays. Each record must be unique (no duplicate rows).

50. StudentID Name Subjects 1 Rahul Math, Science 2 Priya English, History 3 Arjun Physics, Chemistry StudentID Name Subject 1 Rahul Math 1 Rahul Science 2 Priya English 2 Priya History 3 Arjun Physics 3 Arjun Chemistry 1NF Normal table

51. It is already in 1NF. There is no partial dependency. 2NF removes partial dependency. This matters only when the table has a composite primary key. 2NF (Second Normal Form) in DBMS

52. StudentID CourseID StudentName CourseName 1 C1 Rahul Math 2 C2 Priya English 1 C2 Rahul English StudentID StudentName 1 Rahul 2 Priya CourseID CourseName C1 Math C2 English StudentID CourseID 1 C1 2 C2 1 C2 2 NF Normal table

53. It is already in 2NF . It has no transitive dependency . Transitive dependency means: A non-prime attribute depends on another non-prime attribute, instead of depending directly on the primary key. 3 NF

54. StudentID StudentName DepartmentID DepartmentName 1 Rahul D1 Computer Sci 2 Priya D2 English 3 Arjun D1 Computer Sci StudentID StudentName DepartmentID 1 Rahul D1 2 Priya D2 3 Arjun D1 DepartmentID DepartmentName D1 Computer Sci D2 English 3 NF

55. StudentID Course Instructor 1 DBMS Prof. A 2 DBMS Prof. A 3 Networks Prof. B Instructor Course Prof. A DBMS Prof. B Networks StudentID Instructor 1 Prof. A 2 Prof. A 3 Prof. B BCNF - Boyce-Codd Normal Form