Relational Model,relational calulus.pptx

Relational algebra

1. Relational Model

2. Relational Model A relational database is a collection of data items with pre-defined relationships between them. These items are organized as a set of tables with columns and rows. Tables are used to hold information about the objects to be represented in the database. Each column in a table holds a certain kind of data and a field stores the actual value of an attribute. The rows in the table represent a collection of related values of one object or entity. Each row in a table could be marked with a unique identifier called a primary key, and rows among multiple tables can be made related using foreign keys. This data can be accessed in many different ways 

3. Structure of Relational Databases  Relational database consists of a collection of  tables,  each of which is assigned a unique name. For example, consider the instructor table of Figure 2.1, which stores information about instructors. The table has four column headers:  ID, name, dept_ name,  and  salary . Each row of this table records information about an   instructor ,consisting of the instructor’s  ID, name, dept_name , and  salary .

4. Similarly, the  course table of Figure 2.2 stores information about courses, consisting of a  course_id , title,dept_name , and  credits,  for each course. Note that each instructor is identified bythe value of the column ID, while each course is identified by the value of the column  course_id .

5. Figure 2.3 shows a third table,  prereq ,which stores the prerequisite courses for each course. The table has two columns,  course_id  and  prereq_id . Each row consistsof a pair of course identifiers such that the second course is a prerequisite for the first course.

6. Database Schema A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data. A database schema defines its entities and the relationship among them. It contains a descriptive detail of the database, which can be depicted by means of schema diagrams. It’s the database designers who design the schema to help programmers understand the database and make it useful.

7. A database schema can be divided broadly into two categories − Physical Database Schema  − This schema pertains to the actual storage of data and its form of storage like files, indices, etc. It defines how the data will be stored in a secondary storage. Logical Database Schema  − This schema defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints.

8. Relational Algebra Relational algebra is a procedural query language, which takes instances of relations as input and yields instances of relations as output. It uses operators to perform queries. An operator can be either  unary  or  binary . They accept relations as their input and yield relations as their output.

9. The fundamental operations of relational algebra are as follows − Select Project Union Set different Cartesian product Rename

10. Select ( σ) Select operation is done by Selection Operator which is represented by "sigma"(σ). It is used to retrieve tuples (rows) from the table where the given condition is satisfied. It is a  unary operator  means it require only one operand. Notation : σ p(R) Where σ is used to represent SELECTION R is used to represent RELATION p is the logic formula

11. ROLL NAME AGE 1 Aman 20 2 Atul 18 3 Baljeet 19 4 Harsh 20 5 Prateek 21 6 Prateek 23 Student

12. Employee EMPLOYEE_NO NAME AGE E-1 Anant 20 E-2 Ashish 23 E-3 Baljeet 25 E-4 Harsh 20 E-5 Pranav 22

13. Let's understand this with an example: Suppose we want the row(s) from STUDENT Relation where "AGE" is 20 σ AGE=20 (STUDENT) ROLL NAME AGE 1 Aman 20 4 Harsh 20 E-5 Pranav 22

14. Project (∏) Project operation is done by Projection Operator which is represented by "pi"(∏). It is used to retrieve certain attributes(columns) from the table. It is also known as vertical partitioning as it separates the table vertically. It is also a  unary operator .  Notation : ∏ a(r) Where ∏ is used to represent PROJECTION r is used to represent RELATION a is the attribute list

15. Let's understand this with an example: Suppose we want the names of all students from STUDENT Relation. ∏ NAME(STUDENT)

16. NAME Aman Atul Baljeet Harsh Prateek

17. Union (∪) Union operation is done by Union Operator which is represented by "union"(∪). It is the same as the union operator from set theory, i.e.,  it selects all tuples from both relations but with the exception that for the union of two relations/tables both relations must have the same set of Attributes . It is a  binary operator  as it requires two operands.  Notation: R ∪ S Where R is the first relation S is the second relation

18. Let's have an example to clear the concept: Suppose we want all the names from STUDENT and EMPLOYEE relation. ∏ NAME(STUDENT) ∪ ∏ NAME(EMPLOYEE)

19. NAME Aman Anant Ashish Atul Baljeet Harsh Pranav Prateek

20. Set Difference (-) Set Difference as its name indicates is the difference of two relations (R-S). It is denoted by a "Hyphen"(-) and it returns all the tuples (rows) which are in relation R but not in relation S. It is also a  binary operator . Notation : R - S Where R is the first relation S is the second relation

21. Let's take an example where we would like to know the names of students who are in STUDENT Relation but not in EMPLOYEE Relation. ∏ NAME(STUDENT) - ∏ NAME(EMPLOYEE) NAME Aman Atul Prateek

22. Cartesian product (X) Cartesian product is denoted by the "X" symbol. Let's say we have two relations R and S. Cartesian product will combine every tuple (row) from R with all the tuples from S.  Notation: R X S Where R is the first relation S is the second relation As we can see from the notation it is also a  binary operator . Let's combine the two relations STUDENT and EMPLOYEE . STUDENT X EMPLOYEE

23. ROLL NAME AGE EMPLOYEE_NO NAME AGE 1 Aman 20 E-1 Anant 20 1 Aman 20 E-2 Ashish 23 1 Aman 20 E-3 Baljeet 25 1 Aman 20 E-4 Harsh 20 1 Aman 20 E-5 Pranav 22 2 Atul 18 E-1 Anant 20 2 Atul 18 E-2 Ashish 23 2 Atul 18 E-3 Baljeet 25 2 Atul 18 E-4 Harsh 20 2 Atul 18 E-5 Pranav 22

24. Rename ( ρ) Rename operation is denoted by "Rho"(ρ). As its name suggests it is used to rename the output relation. Rename operator too is a  binary operator .  Notation: ρ(R,S)  Where R is the new relation name S is the old relation name Let's have an example to clear this Suppose we are fetching the names of students from STUDENT relation. We would like to rename this relation as STUDENT_NAME. ρ(STUDENT_NAME,∏ NAME(STUDENT))

25. Relational Calculus There is an alternate way of formulating queries known as Relational Calculus. Relational calculus is a non-procedural query language . In the non-procedural query language, the user is concerned with the details of how to obtain the end results . The relational calculus tells what to do but never explains how to do.

26. Tuple and domain are the major components of relational calculus. A result tuple is an assignment of constants to these variables that make the formula evaluate to be true . There are two types of relational calculus available in DBMS Tuple relational calculus (TRC ) Domain relational calculus (DRC)

27. Tuple Relational Calculus (TRC) Notation: A Query in the tuple relational calculus is expressed as following notation {T | P (T)}   or {T | Condition (T)}      Where T  is the resulting tuples P(T)  is the condition used to fetch T.

28. {S | Staff(S) ∧ S.salary > 10000} Example1 {t | TEACHER (t) and t.SALARY >20000 } It implies that it selects the tuples from the TEACHER in such a way that the resulting teacher tuples will have a salary higher than 20000. This is an example of selecting a range of values . Example2 {  T.name | Author(T) AND  T.article  = 'database' }     Output:  This query selects the tuples from the AUTHOR relation. It returns a tuple with 'name' from Author who has written an article on 'database'.

29. Domain Relational Calculus (DRC) he second form of relation is known as Domain relational calculus . In domain relational calculus, filtering variable uses the domain of attributes. Domain relational calculus uses the same operators as tuple calculus. It uses logical connectives ∧ (and), ∨ (or) and ┓ (not). It uses Existential (∃) and Universal Quantifiers (∀) to bind the variable. 

30. Notation: { a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}   a1, a2  are attributes P  stands for formula built by attributes For example: {< article, page, subject > |  ∈  javatpoint  ∧ subject = 'database'}   Output:  This query will yield the article, page, and subject from the relational javatpoint , where the subject is a database.

31. Extended operators Extended operators are those operators which can be derived from basic operators . There are mainly three types of extended operators in Relational Algebra: Join Intersection Divide  

32. Intersection Intersection operation It displays the common values in R1 & R2. It is denoted by ∩. Syntax ∏ regno (R1) ∩ ∏ regno (R2)

33. Example Find all the customers whose account is in the bank and have taken out a loan. The expression is as follows − ∏Name(Depositor) ∩ ∏Name(Borrower)

34. Depositor ID Name 1 A 2 B 3 C ID Name 2 B 3 A 5 D Borrower A B

35. Join Join  is a combination of a Cartesian product followed by a selection process. A Join operation pairs two tuples from different relations, if and only if a given join condition is satisfied.

36. Divide The division operator is used for queries which involve the ‘all’. R1 ÷ R2 = tuples of R1 associated with all tuples of R2.

37. Name Course System Btech Database Mtech Database Btech Algebra Btech Course Btech Mtech % Name database

38. Relational Algebra Vs Calculus Sr. No. Key Relational Algebra Relational Calculus 1 Language Type Relational Algebra is procedural query language. Relational Calculus is a non-procedural or declarative query language. 2 Objective Relational Algebra targets how to obtain the result. Relational Calculus targets what result to obtain. 3 Order Relational Algebra specifies the order in which operations are to be performed. Relational Calculus specifies no such order of executions for its operations. 4 Dependency Relational Algebra is domain independent. Relational Calculus can be domain dependent. 5 Programming Language Relational Algebra is close to programming language concepts. Relational Calculus is not related to programming language concepts.