DBMS Chapter 2 Introduction PDF

Tanta University Faculty of Commerce Introduction to Database Management Systems (DBMS) Prof.Dr Elsayed Sallam Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 1 Introduction to Relational Model...

Tanta University Faculty of Commerce Introduction to Database Management Systems (DBMS) Prof.Dr Elsayed Sallam Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 1 Introduction to Relational Model Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 2 Chapter 2: Introduction to Relational Model Agenda  Structure of Relational Databases  Database Schema  Keys  Schema Diagrams  Relational Query Languages  The Relational Algebra Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 3 Example of an Instructor Relation Model attributes Instructor (or columns) tuples (or rows) Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 4  Relation Schema and Instance  A1, A2, …, An are attributes.  R = (A1, A2, …, An ) is a relation schema.  Example:  Instructor = (ID, name, dept_name, salary).  A relation instance r defined over schema R is denoted by r (R).  The current values a relation are specified by a table.  An element t of relation r is called a tuple and is represented by a row in a table. Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 5  Attributes  The set of allowed values for each attribute is called the domain of the attribute.  Attribute values are (normally) required to be atomic; that is, indivisible.  The special value null is a member of every domain. Indicated that the value is “unknown”.  The null value causes complications in the definition of many operations. Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 6  Relations are Unordered  Order of tuples is irrelevant (tuples may be stored in an arbitrary order).  Example: Instructor relation with unordered tuples Instructor Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 7  Database Schema  Database schema -- is the logical structure of the database.  Database instance -- is a snapshot of the data in the database at a given instant in time.  Example: schema: Instructor (ID, name, dept_name, salary)  Instance: ID name dept_name salary 98345 Kim Elec. Eng. 80000 10101 Srinivasa Comp. Sci. 65000 n 33456 Gold Physics 87000 76543 Singh Finance 80000 Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 8  Keys  Let K  R  K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R)  Example: {ID} and {ID,name} are both superkeys of instructor.  Superkey K is a candidate key if K is minimal. Example: {ID} is a candidate key for Instructor.  One of the candidate keys is selected to be the primary key.  Which one? Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 9  Keys  Foreign key constraint: Value in one relation must appear in another Referencing relation Referenced relation Example: dept_name in Instructor is a foreign key from instructor referencing department. Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 10 Schema Diagram for University Database Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 11  Relational Query Languages  Procedural versus non-procedural, or declarative  “Pure” languages:  Relational algebra  Tuple relational calculus  Domain relational calculus  The above 3 pure languages are equivalent in computing power.  We will concentrate in this chapter on relational algebra  Not Turing-machine equivalent.  Consists of 7 basic operations. Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 12  Relational Algebra  A procedural language consisting of a set of operations that take one or two relations as input and produce a new relation as their result.  Seven basic operators select:  (sigma) project:  (Pi) union: ∪ Set Intersection ∩ set difference: – (minus) Cartesian product: x rename:  (r rho) Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 13  Relational Algebra (cont.) Relational algebra is a procedural query language. It gives a step by step process to obtain the result of the query. It uses operators to perform queries. Types of Relational operation Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 14  Definitions Informal Terms Another name Formal Terms Table Relation Column Header Field Attribute All possible Column Domain Values Row Record Tuple Table Definition DB Structure Schema of a Relation Cardinality No of rows Degree No of columns Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 15  Data Structure Field name TABLE NAME Attribute Dept_name building budget Comp.Sci. Physics 1000000 Finance Finance 7000000 Tupple, Music Finance 600000 row Physics Physics 800000 History Finance 500000 Biology Physics 90000 Elec.Eng. Physics 1100000 Domain Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 16  1. Select Operation σ The select operation selects tuples that satisfy a given predicate. It is denoted by sigma (σ). Notation: σ p(r) , Where: σ is used for selection prediction r is used for relation p is used as a propositional logic formula which may use connectors like: AND OR and NOT. These relational can use as relational operators Like =, ≠, ≥, , ≤. Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 17  1. Select Operation σ (cont.)  The select operation selects tuples that satisfy a given predicate.  Notation:  p (r)  p is called the selection predicate  Example: select those tuples of the Instructor relation where the instructor is in the “Physics” department.  Query :  dept_name=“Physics” (Instructor)  Result ID Name Dept_name salary 22222 Einstein Physics 95000 33456 Gold Physics 87000 Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 18  1. Select Operation σ (cont.)  Comparisons is allowed using = ,  , > ,  , < ,  in the selection predicate.  We can combine several predicates into a larger predicate by using the connectives:  (and),  (or),  (not)  Example: Find the instructors in Physics with a salary greater $90,000, we write:  dept_name=“Physics”  salary > 90,000 (Instructor) Result ID Name Dept_name salary 22222 Einstein Physics 95000 Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 19  1. Select Operation σ (cont.)  The select predicate may include comparisons between two attributes. Example, find all departments whose name is the same as their building name: department Dept_name building budget Comp.Sci. Physics 1000000 Finance Finance 7000000 Music Finance 600000 Physics Physics 800000 History Finance 500000 Biology Physics 90000 Elec.Eng. Physics 1100000 Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 20  1. Select Operation σ (cont.) Example, find all departments whose name is the same as their building name:  Query:  dept_name=building (department) Result Dept_name building budget Finance Finance 7000000 Physics Physics 800000 Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 21  2. Project Operation   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 result is defined as the relation of k columns obtained by erasing the columns that are not listed  Duplicate rows removed from result, since relations are sets. Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 22  2. Project Operation  (cont.)  A ,A ,A ….A (r) 1 2 3 k  A unary operation that returns its argument relation, with certain attributes left out. This operation shows the list of those attributes that we wish to appear in the result. Rest of the attributes are eliminated from the table. Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 23  2. Project Operation Example  (cont.)  Example: CUSTOMER RELATION  Query:  name, city (CUSTOMER) CUSTOMER NAME STREET CITY Result Jones Main Harrison NAME CITY Smith North Rye Jones Harrison Hays Main Harrison Smith Rye Curry North Rye Hays Harrison Johnson Alma Brooklyn Curry Rye Brooks Senator Brooklyn Johnson Brooklyn Brooks Brooklyn Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 24  2. Project Operation Example (cont.)  Example: eliminate the dept_name attribute of Instructor  Query: ID, name, salary (Instructor)  Result: Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 25  Composition of Relational Operations  The result of a relational-algebra operation is relation and therefore of relational-algebra operations can be composed together into a relational-algebra expression.  Consider the query - Find the names of all instructors in the Physics department. name( dept_name =“Physics” (Instructor))  Insteadof giving the name of a relation as the argument of the projection operation, we give an expression that evaluates to a relation. Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 26  3. Union Operation ∪ The union operation allows us to combine two relations. Suppose there are two tuples R and S. The union operation contains all the tuples that are either in R or S or both in R & S. It eliminates the duplicate tuples. It is denoted by ∪. 1. Notation: R ∪ S  A union operation must hold the following condition: R and S must have the attribute of the same number. Duplicate tuples are eliminated automatically. Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 27  3. Union Operation ∪ (cont.)  For R ∪ S to be valid. 1. R, S must have the same arity (same number of attributes) 2. The attribute domains must be compatible (example: 2nd column of r deals with the same type of values as does the 2nd column of s) Notation: R ∪ S Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 28  3. Union Operation ∪ (cont.)  Example : DEPOSITOR RELATION  BORROW RELATION CUSTOMER ACCOUNT_ CUSTOMER_ LOAN_NO _NAME NO NAME Jones L-17 Johnson A-101 Smith L-23 Smith A-121 Hayes L-15 Mayes A-321 Jackson L-14 Turner A-176 Curry L-93 Johnson A-273 Smith L-11 Jones A-472 Williams L-17 Lindsay A-284  Query: ∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR) Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 29  3. Union Operation ∪ (cont.)  Result  CUSTOMER_NAME Result Johnson Smith Hayes Query:CUSTOMER_NAME (BORROW) ∪ Turner ∏ CUSTOMER_NAME (DEPOSITOR) Jones Lindsay Jackson Curry Williams Mayes Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 30  3. Union Operation ∪ (cont.)  Example: to find all courses taught in the Fall 2017 semester, or in the Spring 2018 semester, or in both course_id ( semester=“Fall” Λ year=2017 (section)) ∪ course_id ( semester=“Spring” Λ year=2018 (section)) Result Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 31  4. Set Intersection Operation ∩ Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in both R & S. It is denoted by intersection ∩. 1. Notation: R ∩ S  Example: Using the above DEPOSITOR table and BORROW table,  Query: ∏ CUSTOMER_NAME (BORROW) ∩  ∏ CUSTOMER_NAME (DEPOSITOR) CUSTOMER_NAME Result Smith Jones Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 32  4. Set Intersection Operation ∩ (cont.)  The set-intersection operation allows us to find tuples that are in both the input relations.  Notation: r ∩ s  Assume:  r, s have the same arity  attributes of r and s are compatible  Example: Find the set of all courses taught in both the Fall 2017 and the Spring 2018 semesters. course_id ( semester=“Fall” Λ year=2017 (section)) ∩ course_id ( semester=“Spring” Λ year=2018 (section))  Result: cource_id CS-101 Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 33  5. Set Difference Operation - Suppose there are two tuples R and S. The set Difference operation contains all tuples that are in R but not in S. It is denoted by Difference minus (-). 1. Notation: R - S  Example: Using the above DEPOSITOR table and BORROW table, Query: ∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR) CUSTOMER_NAME Result: Jackson Hayes Willians Curry Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 34  5. Set Difference Operation – (cont.)  The set-difference operation allows us to find tuples that are in one relation but are not in another.  Notation r – s  Set differences must be taken between compatible relations. r and s must have the same arity  attribute domains of r and s must be compatible  Example: to find all courses taught in the Fall 2017 semester, but not in the Spring 2018 semester cource_id course_id ( semester=“Fall” Λ year=2017 (section)) − CS-347 course_id ( semester=“Spring” Λ year=2018 (section)) PHY-101 Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 35  6. Cartesian-Product Operation X  The Cartesian-product operation (denoted by X) allows us to combine information from any two relations. The Cartesian product is used to combine each row in one table with each row in the other table. It is also known as a cross product. It is denoted by X. 1. Notation: E X D Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 36  6. Cartesian-Product Operation X (cont.)  Example: the Cartesian product of the relations Instructor and teaches is written as: instructor X teaches  We construct a tuple of the result out of each possible pair of tuples: one from the Instructor relation and one from the teaches relation (see next slide)  Since the instructor ID appears in both relations we distinguish between these attribute by attaching to the attribute the name of the relation from which the attribute originally came.  instructor.ID  teaches.ID Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 37 The instructor X teaches table Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 38  6. Cartesian-Product Operation X (cont.) DEPARTMENT  Example: EMPLOYEE EMP_ID EMP_NAME EMP_DEPT DEPT_NO DEPT_NAME 1 Smith A A Marketing 2 Harry C B Sales 3 John B C Legal Query: EMPLOYEE X DEPARTMENT EMP_ID EMP_NAME EMP_DEPT DEPT_NO DEPT_NAME 1 Smith A A Marketing 1 Smith A B Sales 1 Smith A C Legal 2 Harry C A Marketing 2 Harry C B Sales 2 Harry C C Legal 3 John B A Marketing 3 John B B Sales 3 John B C Legal Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 39  7. Rename Operation   The results of relational-algebra expressions do not have a name that we can use to refer to them. The rename operation is used to rename the output relation. It is denoted by rho (ρ).  It is used to assign a new name to a relation and is denoted by ρ (rho).  Syntax: ρ newname (tablename or expression)  The rename operator,  , is provided for that purpose  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) Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 40  7. Rename Operation  (cont.)  Consider the student table given below − Regno Branch Section  The student table is renamed 1 CSE A with newstudent with the help 2 ECE B of the following command − 3 CIVIL B  ρnewstudent (student) 4 IT A Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 41  7. Rename Operation  (cont.)  The Regno, branch columns of student table are renamed to newRegno and newBranch respectively.  ρnewRegno,newBranch(∏Regno,branch( student))  Binary operations are applied newRegno newbranch on two compatible relations. 1 CSE 2 ECE 3 CIVIL 4 IT Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 42  8. Join Operation  The Cartesian-Product instructor X teaches associates every tuple of instructor with every tuple of teaches.  Most of the resulting rows have information about instructors who did NOT teach a particular course.  To get only those tuples of “instructor X teaches “ that pertain to instructors and the courses that they taught, we write:  instructor.id = teaches.id (instructor x teaches ))  We get only those tuples of “instructor X teaches” that pertain to instructors and the courses that they taught.  The result of this expression, shown in the next slide Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 43  8. Join Operation (cont.)  The table corresponding to:  instructor.id = teaches.id (instructor x teaches)) Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 44  8. Join Operation (cont.)  The join operation allows us to combine a select operation and a Cartesian-Product operation into a single operation.  Consider relations r (R) and s (S)  Let “𝛉 theta” be a predicate on attributes in the schema R “union” S. The join operation r ⋈𝜃 s is defined as follows: 𝒓 ⋈𝜽 𝒔 = 𝝈𝜽 (𝒓 × 𝒔)  Thus  instructor.id = teaches.id (instructor x teaches ))  Can equivalently be written as instructor ⋈ Instructor.id = teaches.id teaches. Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 45  The Assignment Operation  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: Find all instructor in the “Physics” and Music department. Physics   dept_name=“Physics” (instructor) Music   dept_name=“Music” (instructor) Physics  Music  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. Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 46  Equivalent Queries  There is more than one way to write a query in relational algebra.  Example: Find information about courses taught by instructors in the Physics department with salary greater than 90,000  Query 1  dept_name=“Physics”  salary > 90,000 (instructor)  Query 2  dept_name=“Physics” ( salary > 90.000 (instructor))  The two queries are not identical; they are, however, equivalent -- they give the same result on any database. Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 47  Equivalent Queries (cont.)  There is more than one way to write a query in relational algebra.  Example: Find information about courses taught by instructors in the Physics department  Query 1 dept_name=“Physics” (instructor ⋈ instructor.ID = teaches.ID teaches)  Query 2 (dept_name=“Physics” (instructor)) ⋈ instructor.ID = teaches.ID teaches  The two queries are not identical; they are, however, equivalent - - they give the same result on any database. Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 48 Prof.Dr. Elsayed. Sallam 2024-2025 Introduction to Relational Model 49