University:
College
Course:
College Mathematics
Academic year:

2023
Views:

19

Pages:

41
Author:

Avaiya Priyank

Topics to be covered ▪ ▪ ▪ ▪ ▪ ▪ ▪ Structure of Relational Database Domain Keys of relations Relational Algebra Implementing Relational Algebra using SQL Set Operators Sub queries Definitions Arity = No. of Columns = 3 Total No. of Tuples = Cardinality of relation Row = Record = Tuple • Attribute = Column Name • 2 attribute in same relation (table) can't have same name Set of permitted values for that attribute = Domain • Predefined row/column format for storing information. • Relation = Table Keys ▪ Super Key • A super key is a set of one or more attributes whose values uniquely identifies each record within a relation. Super Key (EnrollNo) Super Key (RollNo, Branch, Semester) EnrollNo RollNo Name 6662030701 101 6662030702 102 6662030601 Branch Semester SPI BackLog Pritesh CE 3 8 0 3 7 0 101 Vaibha CE vMihir CI 3 8 0 6662030602 102 Darshan CI 3 6 1 5662030701 101 Priya CE 5 8 0 5662030702 102 5 7 1 5662030601 101 Shrey CE aNiddhi CI 5 7 1 Keys ▪ Candidate Key • A candidate is a subset of a super key. • A candidate key is the least combination of attribute that uniquely identifies each record in the table. • The least combination of fields distinguishes a candidate key from a super key. Keys ▪ Candidate Key What is the difference between super key and candidate key?? Candidate Key (RollNo, Branch, Semester) EnrollNo RollNo Name 6662030701 101 6662030702 102 6662030601 Branch Semester SPI BackLog Pritesh CE 3 8 0 3 7 0 101 Vaibha CE vMihir CI 3 8 0 6662030602 102 Darshan CI 3 6 1 5662030701 101 Priya CE 5 8 0 5662030702 102 5 7 1 5662030601 101 Shrey CE aNiddhi CI 5 7 1 Keys ▪ Super Key V/S Candidate Key EnrollNo alone works as a super key?? Answer is Yes So (EnrollNo, Branch) is super key but not candidate key. Super Key (EnrollNo, Branch) EnrollNo RollNo Name 6662030701 101 6662030702 102 6662030601 Branch Semester SPI BackLog Pritesh CE 3 8 0 3 7 0 101 Vaibha CE vMihir CI 3 8 0 6662030602 102 Darshan CI 3 6 1 5662030701 101 Priya CE 5 8 0 5662030702 102 5 7 1 5662030601 101 Shrey CE aNiddhi CI 5 7 1 Keys ▪ Super Key V/S Candidate Key RollNo and Branch alone works as a super key?? Answer is No Super Key (RollNo, Branch, Semester) EnrollNo RollNo Name 6662030701 101 6662030702 102 6662030601 Branch Semester SPI BackLog Pritesh CE 3 8 0 3 7 0 101 Vaibha CE vMihir CI 3 8 0 6662030602 102 Darshan CI 3 6 1 5662030701 101 Priya CE 5 8 0 5662030702 102 5 7 1 5662030601 101 Shrey CE aNiddhi CI 5 7 1 Keys ▪ Super Key V/S Candidate Key RollNo and Semester alone works as a super key?? Answer is No Super Key (RollNo, Branch, Semester) EnrollNo RollNo Name 6662030701 101 6662030702 102 6662030601 Branch Semester SPI BackLog Pritesh CE 3 8 0 3 7 0 101 Vaibha CE vMihir CI 3 8 0 6662030602 102 Darshan CI 3 6 1 5662030701 101 Priya CE 5 8 0 5662030702 102 5 7 1 5662030601 101 Shrey CE aNiddhi CI 5 7 1 Keys ▪ Super Key V/S Candidate Key Branch and Semester alone works as a super key?? Answer is No Super Key (RollNo, Branch, Semester) EnrollNo RollNo Name 6662030701 101 6662030702 102 6662030601 Branch Semester SPI BackLog Pritesh CE 3 8 0 3 7 0 101 Vaibha CE vMihir CI 3 8 0 6662030602 102 Darshan CI 3 6 1 5662030701 101 Priya CE 5 8 0 5662030702 102 5 7 1 5662030601 101 Shrey CE aNiddhi CI 5 7 1 Keys ▪ Super Key V/S Candidate Key (RollNo, Branch, Semester) is super key as well as candidate key. Super Key (RollNo, Branch, Semester) EnrollNo RollNo Name 6662030701 101 6662030702 102 6662030601 Branch Semester SPI BackLog Pritesh CE 3 8 0 3 7 0 101 Vaibha CE vMihir CI 3 8 0 6662030602 102 Darshan CI 3 6 1 5662030701 101 Priya CE 5 8 0 5662030702 102 5 7 1 5662030601 101 Shrey CE aNiddhi CI 5 7 1 Keys ▪ Primary key • A Primary key is chosen by database designer to identify tuples uniquely in a relation. ▪ Alternate key • If any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of that table. Keys ▪ Primary Key V/S Alternate Key Candidate Primary KeyKey EnrollNo Candidate Key Alternate Key (RollNo, Branch, Semester) EnrollNo RollNo Name 6662030701 101 6662030702 102 6662030601 Branch Semester SPI BackLog Pritesh CE 3 8 0 3 7 0 101 Vaibha CE vMihir CI 3 8 0 6662030602 102 Darshan CI 3 6 1 5662030701 101 Priya CE 5 8 0 5662030702 102 5 7 1 5662030601 101 Shrey CE aNiddhi CI 5 7 1 Keys ▪ Foreign key • A foreign key is a set of one or more attributes whose values are derived from the primary key attribute of another relation. Stu_ID RollNo Name Branch Semester SPI BackLog 1 101 Pritesh CE 3 9 0 2 102 Vaibhav CI 3 8 0 3 101 Nidhi CE 5 7 1 4 105 Shyam CI 5 8 2 Fac_ID Stu_ID Fac_SN Fac_Name Branch 1 1 501 Akash CE 2 2 502 Mohit CI 3 4 504 Nitin CE 4 1 515 Harnish CI Relational algebra operation ▪ It is a language for expressing relational database queries. ▪ It is a procedural query language. ▪ Operations are as below; 1. Selection 2. Projection 3. Division 4. Cartesian product / Cross product 5. Rename 6. Set Operations • Union • Intersection • Difference / Minus Selection operation ▪ Retrieve one or more than one row/s (tuples) that satisfies the condition/s with all columns (attribute) ▪ Symbol : σ (Sigma) ▪ Notation : σ(condition) ▪ Operators: • =,!=, <,>,<=,>=, Λ(AND), ∨(OR) ▪ Example: Student Stu_ID Stu_RollNo Name Department 1 101 Ram Computer 2 105 Param IT 3 208 Krishti Computer 4 106 Balram Electrical Selection operation example ▪ Find out the list of students from the following table who have department = “Computer” Stu_ID Stu_RollNo Name Department 1 101 Ram Computer 2 105 Param IT 3 208 Krishti Computer 4 106 Balram Electrical Name Department ▪ σDepartment=“Computer” (Student) ▪ Output: Stu_ID Stu_RollNo 1 101 Ram Computer 3 208 Krishti Computer Projection operation ▪ Retrieve one or more than one column/s (attribute) that satisfies the condition/s with all rows (tuples) ▪ Symbol : ∏ (Pi) ▪ Notation : ∏ (column name)

▪ Example: Student Stu_ID Stu_RollNo Name Department 1 101 Ram Computer 2 105 Param IT 3 208 Krishti Computer 4 106 Balram Electrical Projection operation example ▪ List all the student’s name and department Stu_ID Stu_RollNo Name Department 1 101 Ram Computer 2 105 Param IT 3 208 Krishti Computer 4 106 Balram Electrical ▪ ∏Name, Department (Student) ▪ Output: Name Department Ram Computer Param IT Krishti Computer Balram Electrical Combined Projection & Selection Operation ▪ Example: Display rollno, name & department of “CE” department students. RollNo Name Department SPI Student 101 Raj CE 8 102 Meet ME 9 103 Harsh EE 8 104 Punit CE 9 ∏ RollNo, Name, Department (σ Department=‘CE’ (Student) ) Output-1 Output-2 1 RollNo Name 2 Department 101 Raj CE 8 104 Punit CE 9 RollNo Name Branch 101 Raj CE 104 Punit CE SPI Combined Projection & Selection Operation ▪ Example: Display rollno and name of “CE” department students. RollNo Name Department SPI 101 Raj CE 8 Student 102 Meet ME 9 103 Harsh EE 8 104 Punit CE 9 σ Department=‘CE’ (∏ RollNo, Name(Student) ) 2 Output-1 RollNo Name 101 Raj 102 Meet 103 Harsh 104 Punit 1 Where is branch column to check condition?? So we can’t use selection left side and projection right side. Division Operator ▪ Symbol: ÷ ▪ Notation: Relation1 ÷ Relation2 ▪ Operation: Produce the tuples in Relation1, that match all tuples in Relation2. Division Operator Examples B1 B2 B3 A A/B1 A/B2 A/B3 Cartesian product / Cross product ▪ Combines information of two tables ▪ It will multiply each row(tuples) of first table(relation) to each row(tuples) of second table(relation). ▪ Symbol : X (Cross) ▪ Notation : Relation1 X Relation2 ▪ Resultant Relation: 1. Relation1 have n1 attribute and Relation2 have n2 attribute then resultant relation will have n1+n2 attribute. 2. Relation1 have n1 tuples and Relation2 have n2 tuples then resultant relation will have n1*n2 tuples. 3. If both relations have some attribute with same name, then it will be distinguished by relation1.attribute and relation2.attribute Cartesian product example Student Faculty Stu_ID Stu_RollNo Name Department Fac_ID Fac_Name Department 1 101 Ram Computer 1 Nitin Computer 2 105 Param IT 2 Chintan IT Student X Faculty Stu_ID Stu_RollNo Name Department Fac_ID Fac_Name Faculty.Deparmtnet 1 101 Ram Computer 1 Nitin Computer 1 101 Ram Computer 2 Chintan IT 2 105 Param IT 1 Nitin Computer 2 105 Param IT 2 Chintan IT Set Operators ▪ Operation: All of these operations take two input relations, which must be union - compatible: 1. Both relations have same (equal) number of columns RollNo Name Branch SPI EmpNo Name Branch 101 Raj CE 8 101 Patel CE 102 Meet CE 7 102 Shah CE 103 Neel ME 9 103 Ghosh EE RollNo Name Branch SPI EmpNo Name Branch Exp 101 Raj CE 8 101 Raj CE 8 102 Meet CE 7 102 Meet CE 1 103 Neel ME 9 103 Ghosh EE 9 Set Operators ▪ Operation: All of these operations take two input relations, which must be union - compatible: 2. Attributes domain must be compatible RollNo Name Branch SPI EmpNo Name Branch Subject 101 Raj CE 8 101 Raj CE DBMS 102 Meet CE 7 102 Meet CE DS 103 Neel ME 9 103 Ghosh EE EEM RollNo Name Branch SPI EmpNo Name Branch Exp 101 Raj CE 8 101 Raj CE 8 102 Meet CE 7 102 Meet CE 1 103 Neel ME 9 103 Ghosh EE 9 Union operator ▪ Symbol: U ▪ Notation: Relation1 U Relation2 ▪ Operation: Combine the records from two or more tables (sets) in to a single table (set), without duplicates. Student U Faculty Raj Student Faculty Suresh Raj Nitin Meet Suresh Raj Nitin Meet Akash Akash Intersect operator ▪ Symbol: ∩ (Intersection) ▪ Notation: Relation1 ∩ Relation2 ▪ Operation: Returns the records which are common from both relations. Student Faculty Student ∩ Faculty Raj Nitin Raj Suresh Raj Meet Akash Minus/Difference Operator ▪ Symbol: − ▪ Notation: Relation1 − Relation2 ▪ Operation: Returns all the records from first (left) relation that are not contained in the second relation. Student Faculty Raj Nitin Student – Faculty Faculty – Student Suresh Raj Suresh Nitin Meet Akash Meet Akash Rename Operator ▪ Symbol: ρ (Rho) ▪ Notation: ρA (X1,X2….Xn) (Relation) ▪ Operation: It is used to rename a relation or attributes. Student Rno Name CPI 101 Raj 8 102 Meet 9 103 Suresh 7 ▪ Find out maximum CPI from student table. ∏CPI (Student) — ∏A.CPI (σ A.CPI,<=,>= etc operators. ▪ Example: Find out the name of staff whose salary is maximum. select staff_name from staff where staff_salary=(select max(staff_salary) from staff); Single row sub query Multiple row sub query ▪ Returns one or more rows ▪ Can be used with IN, NOT IN, ANY, ALL etc operators. ▪ Example: Find out the name of staff who are from “Computer” department using sub query. SELECT * FROM staff WHERE dep_id IN (SELECT dep_id FROM department where dep_id='2'); Multiple row sub query Correlated sub query ▪ If a sub query references columns in the parent query. ▪ This makes it impossible to evaluate the sub query before evaluating the parent query. ▪ Example: Find out the name of staff who earn less salary then average salary. SELECT staff_name FROM staff WHERE staff_salary < (select avg(staff_salary) from staff); Correlated sub query