REATIONAL MODEL.pdf

Database Management Systems (DBMS) GTU # 3130703 Unit-2 Relational Model  Syllabus Looping Structure of Relational Databases Relational Algebra Fundamental Operators and Syntax ▪ Selection ▪ Projection ▪...

Database Management Systems (DBMS) GTU # 3130703 Unit-2 Relational Model  Syllabus Looping Structure of Relational Databases Relational Algebra Fundamental Operators and Syntax ▪ Selection ▪ Projection ▪ Cross Product OR Cartesian Product ▪ Joins ▪ Set Operators ▪ Division ▪ Rename ▪ Aggregate Functions Open Source and Commercial DBMS Section - 1 What is Relational Model?  Relational Model was proposed by E.F. Codd to model data in the form of relations or tables.  This model is based on mathematical concepts of relation.  The relational model uses a collection of tables to represent both data and the relationships among those data.  Each table has multiple columns, and each column has a unique name. Tables are also known as relations. The relational model is an example of a record-based model. Structure of Relational Databases Columns (5) Table (Relation): A database object that holds a collection of data for a specific topic. Student Table consist of rows and columns. Attributes: RollNo Name Branch Semester SPI Title of column Column (Attribute): The vertical component of a 101 Raju CE 3 8 table. A column has a name and a particular data Cardinality = No of tuples (7) 102 Mitesh CI 3 7 type; e.g. varchar, decimal, integer, datetime etc. Rows or 103 Mayur CE 3 6 Tuples or Record (Tuple): The horizontal component of a 104 Nilesh EE 3 9 Records (7) table, consisting of a sequence of values, one for 105 Hitesh CI 3 7 each column of the table. It is also known as row. 106 Tarun ME 3 8 107 Suresh CE 3 9 A database consists of a collection of tables (relations), each having a unique name. Degree = No of columns (5) Domain is a set of all possible unique values for a specific column. Domain of Branch attribute is (CE, CI, ME, EE) What is Algebra?  Mathematical system consisting of:  Operands --- variables or values from which new values can be constructed. Operators --- symbols denoting procedures that construct new values from given values. What is Relational Algebra?  An algebra whose operands are relations or variables that represent relations.  Operators are designed to do the most common things that we need to do with relations in a database.  The result is an algebra that can be used as a query language for relations. The Relational Algebra  A procedural query language  Comprised of relational algebra operations  Relational operations:  Take one or two relations as input  Produce a relation as output  Relational operations can be composed together  Each operation produces a relation A query is simply a relational algebra expression  Six “fundamental” relational operations  Other useful operations can be composed from these fundamental operations “Why is this useful?”  Many relational databases use relational algebra operations for representing execution plans Simple, clean, effective abstraction for representing how results will be generated  Relatively easy to manipulate for query optimization Relational Algebra Operations Operator Description Selection Display particular rows/records/tuples from a relation Projection Display particular columns from a relation Cross Product Multiply each tuples of both relations Combine data or records from two or more tables 1. Natural Join / Inner Join Joins 2. Outer Join 1. Left Outer Join 2. Right Outer Join 3. Full Outer Join Combine the results of two queries into a single result. Set Operators 1. Union 2. Intersection 3. Minus / Set-difference Division Divides one relation by another Rename Rename a column or a table Selection Operator Section - 2.1 Selection Operator  Symbol: σ (Sigma)  Notation: σ condition (Relation)  Operation: Selects tuples from a relation that satisfy a given condition.  Operators: =, , , =, Λ (AND), V (OR) Example Display the detail of students belongs to “CE” Branch. Answer σBranch=‘CE’ (Student) Student Output RollNo Name Branch SPI RollNo Name Branch SPI 101 Raju CE 8 101 Raju CE 8 102 Mitesh ME 9 104 Meet CE 9 103 Nilesh CI 9 104 Meet CE 9 Selection Operator [σ condition (Relation)] Example Display the detail of students belongs to “CE” Branch and having SPI more than 8. Student RollNo Name Branch SPI 101 Raju CE 8 102 Mitesh ME 9 103 Nilesh CI 9 104 Meet CE 9 Answer σBranch=‘CE’ Λ SPI>8 (Student) Output RollNo Name Branch SPI 104 Meet CE 9 Selection Operator [σ condition (Relation)] Example Display the detail of students belongs to either “CI” or “ME” Branch. Student RollNo Name Branch SPI 101 Raju CE 8 102 Mitesh ME 9 103 Nilesh CI 9 104 Meet CE 9 Answer σBranch=‘CI’ V Branch=‘ME’ (Student) Output RollNo Name Branch SPI 102 Mitesh ME 9 103 Nilesh CI 9 Selection Operator [σ condition (Relation)] Example Display the detail of students whose SPI between 7 and 9. Student RollNo Name Branch SPI 101 Raju CE 8 102 Mitesh ME 9 103 Nilesh CI 9 104 Meet CE 9 Answer σSPI>7 Λ SPI8 (Student) Answer ∏ Name, Branch, SPI (σSPI>8 (Student)) Output-1 Output-2 RollNo Name Branch SPI Name Branch SPI 102 Mitesh ME 9 Mitesh ME 9 103 Nilesh CI 9 Nilesh CI 9 Combined Projection & Selection Operation Example Display Name, Branch and SPI of students who belongs to “CE” Branch and SPI is more than 7. Student RollNo Name Branch SPI 101 Raju CE 8 102 Mitesh ME 9 103 Nilesh CI 9 104 Meet CE 7 Step-1 σBranch=‘CE’ Λ SPI>7 (Student) Answer ∏ Name, Branch, SPI (σBranch=‘CE’ Λ SPI>7 (Student)) Output-1 Output-2 RollNo Name Branch SPI Name Branch SPI 101 Raju CE 8 Raju CE 8 Combined Projection & Selection Operation Example Display Name of students along with their Branch who belong to either “ME” Branch or “CI” Branch. Student RollNo Name Branch SPI 101 Raju CE 8 102 Mitesh ME 9 103 Nilesh CI 9 104 Meet CE 7 Step-1 σBranch=‘ME’ V Branch=‘CI’ (Student) Answer ∏ Name, Branch (σBranch=‘ME’ V Branch=‘CI’ (Student)) Output-1 Output-2 RollNo Name Branch SPI Name Branch 102 Mitesh ME 9 Mitesh ME 103 Nilesh CI 9 Nilesh CI Exercise  Write down the relational algebra for the student table. Student  Display Rollno, Name and SPI of all students belongs to “CE” Branch. RollNo Name Branch SPI  List the Name of students with their Branch whose SPI is more than 8 101 Raj CE 6 and belongs to “CE” Branch. 102 Meet ME 8  List the Name of students along with their Branch and SPI who belongs to either “CE” or “ME” Branch and having SPI more than 8. 103 Harsh EE 7  Display the Name of students with their Branch name whose SPI 104 Punit CE 9 between 7 and 9.  Write down the relational algebra for the employee table. Employee  Display the Name of employee belong to “HR” Dept and having salary EmpID Name Dept Salary more than 20000. 101 Nilesh Sales 10000  Display the Name of all “Admin” and “HR” Dept’s employee. 102 Mayur HR 25000  List the Name of employee with their Salary who belongs to “HR” or “Admin” Dept having salary more than 15000. 103 Hardik HR 15000  Display the Name of employee along with their Dept name whose 104 Ajay Admin 20000 salary between 15000 and 30000. Cartesian Product / Cross Product Section - 2.3 Cartesian Product / Cross Product  Symbol: X (Cross)  Notation: Relation-1 (R1) X Relation-2 (R2) OR Algebra-1 X Algebra-2  Operation: It will multiply each tuples of Relation-1 to each tuples of Relation-2.  Attributes of Resultant Relation = Attributes of R1 + Attributes of R2  Tuples of Resultant Relation = Tuples of R1 * Tuples of R2 Example Perform Cross Product between Student and Result. Answer (Student) X (Result) Student Result Output RNo Name Branch RNo SPI Student.RNo Name Branch Result.RNo SPI 101 Raju CE 101 8 101 Raju CE 101 8 102 Mitesh ME 102 9 101 Raju CE 102 9 102 Mitesh ME 101 8 If both relations have some attribute with the same name, it can be 102 Mitesh ME 102 9 distinguished by combing relation-name.attribute-name. Cartesian Product / Cross Product Example Example Perform Cross Product between Student and Result. Consider only selected attributes Student – RNo, Name and Branch Student Result Result – RNo, SPI and BL RNo Name Branch Sem RNo SPI BL Rank 101 Raju CE 3 101 8 1 2 102 Mitesh ME 5 103 9 0 1 Answer ∏ RNo, Name, Branch (Student) X ∏ RNo, SPI, BL (Result) Output Student.RNo Name Branch Result.RNo SPI BL 101 Raju CE 101 8 1 101 Raju CE 103 9 0 102 Mitesh ME 101 8 1 102 Mitesh ME 103 9 0 Cartesian Product / Cross Product Example Example Perform Cross Product between Student and Result. Consider only selected tuples Student – Branch=‘CE’ and Sem=3 Student Result Result – SPI>7 and BL7 Λ BL