3130703 DBMS GTU Study Material Notes Units-3 PDF

Preview

Summary

In this documents, you will get an easy explanation to solve Database Management Systems problems with examples. The content of the notes is very easy to understand and really helps to increase your Database Management Systems proficiency. All the chapters are filtered in a good manner....

Description

3 – Relational Query Language Explain keys. Super key • •

A super key is a set of one or more attributes (columns) that allow us to identify each tuple (records) uniquely in a relation (table). For example, the enrollment_no, roll_no, semester with department_name of a student is sufficient to distinguish one student tuple from another. So {enrollment_no} and {roll_no, semester, department_name} both are super key.

Candidate key • •

•

Candidate key is a super key for which no proper subset is a super key. For example, combination of roll_no, semester and department_name is sufficient to distinguish one student tuple from another. But either roll_no or semester or department_name alone or combination of any two columns is not sufficient to distinguish one student tuple from another. So {roll_no, semester, department_name} is candidate key. Every candidate key is super key but every super key may not candidate key.

Primary key •

A Primary key is a candidate key that is chosen by database designer to identify tuples uniquely in a relation.

Alternate key •

An Alternate key is a candidate key that is not chosen by database designer to identify tuples uniquely in a relation.

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.

What is relational algebra? Explain relational algebraic operation. • • •

Relational algebra is a language for expressing relational database queries. Relation algebra is a procedural query language. Relational algebraic operations are as follows:

Selection:•

• • •

Operation: Selects tuples from a relation that satisfy a given condition. It is used to select particular tuples from a relation. It selects particular tuples but all attribute from a relation. Symbol: σ (Sigma) Notation: σ(condition) Operators: The following operators can be used in a condition. =, !=, , =, Λ(AND), ∨(OR)

| 3130703 – Database Management System (DBMS)

1

3 – Relational Query Language •

Consider following table Student Rno Name 101 Ramesh 108 Mahesh 109 Amit 125 Chetan 138 Mukesh 128 Reeta 133 Anita

•

Dept CE EC CE CI ME EC CE

CPI 8 6 7 8 7 6 9

Example: Find out all the students of CE department.

σDept=“CE” (Student) •

Output: The above query returns all tuples which contain CE as department name. Output of above query is as follows Student Rno Name 101 Ramesh 109 Amit 133 Anita

Dept CE CE CE

CPI 8 7 9

Projection:• • • •

Operation: Selects specified attributes of a relation. It selects particular attributes but all unique tuples from a relation. Symbol: ∏ (Pi) Notation: ∏ (attribute set) Consider following table Student Rno Name 101 Ramesh 108 Mahesh 109 Amit 125 Chetan 138 Mukesh 128 Reeta 133 Anita

•

Dept CE EC CE CI ME EC CE

CPI 8 6 7 8 7 6 9

Example: List out all students with their roll no, name and department name. ∏Rno, Name, Dept (Student) | 3130703 – Database Management System (DBMS)

2

3 – Relational Query Language •

Output: The above query returns all tuples with three attributes roll no, name and department name. Output of above query is as follows Student Rno 101 109 125 138 133

• •

Name Ramesh Amit Chetan Mukesh Anita

Dept CE CE CI ME CE

Example: List out students of CE department with their roll no, name and department. ∏Rno, Name, Dept (σDept=“CE” (Student)) Output: The above query returns tuples which contain CE as department with three attributes roll no, name and department name. Output of above query is as follows Student Rno Name Dept 101 Ramesh CE 109 Amit CE 133 Anita CE

Division:• • • • • • •

Operation: The division is a binary relation that is written as R1 ÷ R2. Condition to perform operation: Attributes of R2 is proper subset of attributes of R1. The output of the division operator will have attributes = All attributes of R1 – All attributes of R2 The output of the division operator will have tuples = Tuples in R1, which are associated with the all tuples of R2 Symbol: ÷ Notation: R1 ÷ R2 Consider following table Project Work Task Student Task Database1 Shah Database1 Database2 Shah Database2 Shah Compiler1 Vyas Database1 Vyas Compiler1 Patel Database1 Patel Database2 | 3130703 – Database Management System (DBMS)

3

3 – Relational Query Language • •

Example: Find out all students having both tasks Database1 as well as Database2. ∏(student, Task)(Work) ÷ ∏(Task)(Project) Output: It gives name of all students whose task is both Database1 as well as Database2. Output of above query is as follows Student Shah Patel

Cartesian product:•

• • •

Operation: Combines information of two relations. It will multiply each tuples of first relation to each tuples of second relation. It is also known as Cross product operation and similar to mathematical Cartesian product operation. Symbol: X (Cross) Notation: Relation1 X Relation2 Resultant Relation : ✓ If relation1 and relation2 have n1 and n2 attributes respectively, then resultant relation will have n1 + n2 attributes from both the input relations. ✓ If both relations have some attribute having same name, it can be distinguished by combing relation-name.attribute-name. ✓ If relation1 and relation2 have n1 and n2 tuples respectively, then resultant relation will have n1*n2 tuples, combining each possible pair of tuples from both the input relations. R×S

R A B D

1 2 3

A A A B B B D D D

S

A D E •

1 2 3

1 1 1 2 2 2 3 3 3

A D E A D E A D E

1 2 3 1 2 3 1 2 3

Consider following table Emp Empid S01 S02 S03

Empname Manisha Anisha Nisha

Deptname Finance Sales Finance

Dept Deptname Finance Sales Production

Manager Arun Rohit Kishan

| 3130703 – Database Management System (DBMS)

4

3 – Relational Query Language •

Example: Emp × Dept Empid S01 S01 S01 S02 S02 S02 S03 S03 S03

Empname Manisha Manisha Manisha Anisha Anisha Anisha Nisha Nisha Nisha

Emp.Deptname Finance Finance Finance Sales Sales Sales Finance Finance Finance

Dept.Deptname Finance Sales Production Finance Sales Production Finance Sales Production

Manager Arun Rohit Kishan Arun Rohit Kishan Arun Rohit Kishan

Join:Natural Join Operation (⋈) •

Operation: Natural join will retrieve information from multiple relations. It works in three steps. 1. It performs Cartesian product 2. Then it finds consistent tuples and inconsistent tuples are deleted 3. Then it deletes duplicate attributes

•

Symbol: ⋈

• •

•

Notation: Relation1 ⋈ Relation2 Consider following table Emp Empid Empname Deptname S01 Manisha Finance S02 Anisha Sales S03 Nisha Finance

To perform a natural join there must be one common attribute (column) between two relations. Dept Deptame Manager Finance Arun Sales Rohit Production Kishan

Example: Empid S01 S02 S03

Emp ⋈ Dept Empname Deptname Manisha Finance Anisha Sales Nisha Finance

Manager Arun Rohit Arun

 Empname, Manager (Emp ⋈ Dept) Empname Manager Manisha Arun Anisha Rohit Nisha Arun

| 3130703 – Database Management System (DBMS)

5

3 – Relational Query Language The Outer Join Operation In natural join some records are missing if we want that missing records than we have to use outer join. • The outer join operation can be divided into three different forms: 1. Left outer join ( ) 2. Right outer join ( ) 3. Full outer join ( ) • Consider following tables College Hostel Name Id Department Name Hostel_name Room_no Manisha S01 Computer Anisha Kaveri hostel K01 Anisha S02 Computer Nisha Godavari hostel G07 Nisha S03 I.T. Isha Kaveri hostel K02 •

Left outer join (

)

The left outer join returns all the tuples of the left relation even through there is no matching tuple in the right relation. • For such kind of tuples having no matching, the attributes of right relation will be padded with null in resultant relation. • Example : College Hostel College Hostel Name Id Department Hostel_name Room_no Manisha S01 Computer Null Null Anisha S02 Computer Kaveri hostel K01 Nisha S03 I.T. Godavari hostel G07

•

Right outer join ( • • •

)

The right outer join returns all the tuples of the right relation even though there is no matching tuple in the left relation. For such kind of tuples having no matching, the attributes of left relation will be padded with null in resultant relation. Example : College Hostel

Name Anisha Nisha Isha

Id S02 S03 Null

College Department Computer I.T. Null

Hostel Hostel_name Kaveri hostel Godavari hostel Kaveri Hostel

Room_no K01 G07 K02

| 3130703 – Database Management System (DBMS)

6

3 – Relational Query Language Full outer join (

)

The full outer join returns all the tuples of both of the relations. It also pads null values whenever required. Example : College Hostel

• •

Name Manisha Anisha Nisha Isha

Id S01 S02 S03 Null

College Department Computer Computer I.T. Null

Hostel Hostel_name Null Kaveri hostel Godavari hostel Kaveri Hostel

Room_no Null K01 G07 K02

Set Operators • •

•

Set operators combine the results of two or more queries into a single result. Condition to perform set operation: ✓ Both relations (queries) must be union compatible : ✓ Relations R and S are union compatible, if 1. Both queries should have same (equal) number of columns, and 2. Corresponding attributes should have the same data type. Types of set operators: 1. Union 2. Intersect (Intersection) 3. Minus (Set Difference)

Union • • • •

Operation: Selects tuples those are in either or both of the relations. Symbol : U (Union) Notation : Relation1 U Relation2 Example : R S RUS A 1 A 1 A 1 B 2 C 2 B 2 D 3 D 3 C 2 F 4 E 4 D 3 E 5 F 4 E 5 E 4

| 3130703 – Database Management System (DBMS)

7

3 – Relational Query Language •

•

Consider following tables Emp Id Name 1 Manisha 2 Anisha 3 Nisha

Cst Id 1 2 4

Name Manisha Anisha Isha

Example:

∏Name (Emp) U ∏Name (Cst) Name Manisha Anisha Nisha Isha

Intersection • • • •

•

•

Operation: Selects tuples those are common in both relations. Symbol : ∩ (Intersection) Notation : Relation1 ∩ Relation2 Example R S R∩S A 1 A 1 A 1 B 2 C 2 D 3 D 3 D 3 F 4 E 4 E 5 Consider following tables Emp Id Name 1 Manisha 2 Anisha 3 Nisha

Cst Id 1 2 4

Name Manisha Anisha Isha

Example:

∏Name (Emp) ∩ ∏Name (Cst) Name Manisha Anisha

| 3130703 – Database Management System (DBMS)

8

3 – Relational Query Language Difference:• • • •

•

•

Operation: Selects tuples those are in first (left) relation but not in second (right) relation. Symbol : — (Minus) Notation : Relation1 — Relation2 Example : R S R—S A 1 A 1 B 2 B 2 C 2 F 4 D 3 D 3 E 5 F 4 E 4 E 5 Consider following tables Emp Id Name 1 Manisha 2 Anisha 3 Nisha

Cst Id 1 2 4

Name Manisha Anisha Isha

Example:

∏Name (Emp) - ∏Name (Cst) Name Nisha

∏Name (Cst) - ∏Name (Emp) Name Isha

Rename:•

Operation: It is used to rename a relation or attributes.

•

Symbol: ρ (Rho)

•

Notation: ρA(B)

Rename relation B to A.

ρA(X1,X2….Xn)(B)

Rename relation B to A and its attributes to X1, X2, …., Xn.

| 3130703 – Database Management System (DBMS)

9

3 – Relational Query Language •

Consider following table Student Rno Name 101 Ramesh 108 Mahesh 109 Amit 125 Chetan 138 Mukesh 128 Reeta 133 Anita

Dept CE EC CE CI ME EC CE

CPI 8 6 7 8 7 6 9

•

Example: Find out highest CPI from student table.

•

∏CPI (Student) — ∏A.CPI (σ A.CPI10000 (Branch ⋈ Account ⋈ Depositor))

(iii) find out list of all branch name with their maximum balance. ∏branch_name , G max (balance) (Account)

| 3130703 – Database Management System (DBMS)

11...