UNIT2-Reference ppt..pptx which weil; be leading tpo the undrsdtanding od thr LSTMS

UNIT2-Reference ppt..pptx which weil; be leading tpo the undrsdtanding od thr LSTMS

1. Introduction to DBMS (The Relational Model)

2. 2 The Relational Model Developed by Codd (IBM) in 1970 Considered ingenious but impractical in 1970 Conceptually simple Computers lacked power to implement the relational model Today, microcomputers can run sophisticated relational database software

3. Relational model basics Data is viewed as existing in two dimensional tables known as relations A relation (table) consists of unique attributes (columns) and tuples (rows) Tuples are unique Sometimes the value to be inserted into a particular cell may be unknown, or it may have no value. This is represented by a null Null is not the same as zero, blank or an empty string Relational Database: Any database whose logical organization is based on relational data model. RDBMS: A DBMS that manages the relational database.

4. Relational data model

5. EF CODD 12 RULES Every database has tables, and constraints cannot be referred to as a rational database system. And if any database has only relational data model, it cannot be a Relational Database System (RDBMS) . So, some rules define a database to be the correct RDBMS. These rules were developed by Dr. Edgar F. Codd (E.F. Codd ) in 1985 , who has vast research knowledge on the Relational Model of database Systems. Codd presents his 13 rules for a database to test the concept of DBMS against his relational model, and if a database follows the rule, it is called a true relational database (RDBMS) . These 13 rules are popular in RDBMS, known as Codd's 12 rules .

6. EF CODD 12 RULES Rule 0: The Foundation Rule The database must be in relational form. So that the system can handle the database through its relational capabilities. Rule 1: Information Rule A database contains various information, and this information must be stored in each cell of a table in the form of rows and columns. Rule 2: Guaranteed Access Rule Every single or precise data (atomic value) may be accessed logically from a relational database using the combination of primary key value, table name, and column name. Rule 3: Systematic Treatment of Null Values This rule defines the systematic treatment of Null values in database records. The null value has various meanings in the database, like missing the data, no value in a cell, inappropriate information, unknown data and the primary key should not be null. Rule 4: Active/Dynamic Online Catalog based on the relational model It represents the entire logical structure of the descriptive database that must be stored online and is known as a database dictionary. It authorizes users to access the database and implement a similar query language to access the database.

7. EF CODD 12 RULES Rule 5: Comprehensive Data SubLanguage Rule The relational database supports various languages, and if we want to access the database, the language must be the explicit, linear or well-defined syntax, character strings and supports the comprehensive: data definition, view definition, data manipulation, integrity constraints, and limit transaction management operations. If the database allows access to the data without any language, it is considered a violation of the database. Rule 6: View Updating Rule All views table can be theoretically updated and must be practically updated by the database systems. Rule 7: Relational Level Operation (High-Level Insert, Update and delete) Rule A database system should follow high-level relational operations such as insert, update, and delete in each level or a single row. It also supports union, intersection and minus operation in the database system. Rule 8: Physical Data Independence Rule All stored data in a database or an application must be physically independent to access the database. Each data should not depend on other data or an application. If data is updated or the physical structure of the database is changed, it will not show any effect on external applications that are accessing the data from the database.

8. EF CODD 12 RULES Rule 9: Logical Data Independence Rule It is similar to physical data independence. It means, if any changes occurred to the logical level (table structures), it should not affect the user's view (application). For example, suppose a table either split into two tables, or two table joins to create a single table, these changes should not be impacted on the user view application. Rule 10: Integrity Independence Rule A database must maintain integrity independence when inserting data into table's cells using the SQL query language. All entered values should not be changed or rely on any external factor or application to maintain integrity. It is also helpful in making the database-independent for each front-end application. Rule 11: Distribution Independence Rule The distribution independence rule represents a database that must work properly, even if it is stored in different locations and used by different end-users. Suppose a user accesses the database through an application; in that case, they should not be aware that another user uses particular data, and the data they always get is only located on one site. The end users can access the database, and these access data should be independent for every user to perform the SQL queries. Rule 12: Non Subversion Rule The non-submersion rule defines RDBMS as a SQL language to store and manipulate the data in the database. If a system has a low-level or separate language other than SQL to access the database system, it should not subvert or bypass integrity to transform data.

9. Schemas, Instance and Database State 10 October 2020 9 Database Schema: The description of a database. Includes descriptions of the database structure, data types, and the constraints on the database. Schema Diagram: An illustrative display of (most aspects of) a database schema Database State: The actual data stored in a database at a particular moment in time . This includes the collection of all the data in the database. Also called database instance (or occurrence or snapshot). □ The term instance is also applied to individual database components, e.g. record instance, table instance, entity instance

10. Schemas, Instance and Database State USN Name Sem Dep Dep HOD □ Schema Diagram Student Department Database State at time “X” 10 October 2020 10 Database State at time “Y” Database Instance Database Instance

11. Key Primary key is a candidate key that is chosen by the database designer to identify entities with in an entity set. Primary key is the minimal super keys. In the ER diagram primary key is represented by underlining the primary key attribute. Ideally a primary key is composed of only a single attribute. But it is possible to have a primary key composed of more than one attribute. 10 October 2020 11

12. No two rows can have the same primary key value. Every row must have a primary key value. The primary key field cannot be null. Value in a primary key column can never be modified or updated, if any foreign key refers to that primary key. 10 October 2020 12

13. 10 October 2020 13

14. 10 October 2020 14

15. 10 October 2020 15

16. PK Vs FK PK FK Primary key uniquely Foreign key is a field in identify a record in the the table that is primary table. key in another table. Primary Key can't accept null values. We can have only one Primary key in a table. No duplicate values Foreign key can accept multiple null value We can have more than one foreign key in a table. Duplicate values allowed

17. SQL is used to make a request to retrieve data from a Database. The DBMS processes the SQL request, retrieves the requested data from the Database, and returns it. This process of requesting data from a Database and receiving back the results is called a Database Query and hence the name Structured Query Language . S Q L

18. SQL SQL is a language that all commercial RDBMS implementations understand. SQL is a non-procedural language We would be discussing SQL with respect to oracle syntax

19. Structured Query Language (SQL)

20. Statements DDL (Data Definition Language) Create Alter Drop Truncate DML (Data Manipulation Language) Insert Update Delete Select DCL (Data Control Language) Grant revoke TCL (Transaction Control Language) Commit Rollback Savepoint

21. What is SQL ? SQL stands for Structured Query Language SQL lets you access and manipulate USN Name 1BM14CS001 Arjun 1BM14CS002 Balaji Select USN, Name from student_info ; student D a t aba s e student_info SQL query databases Application Program Input 10 October 2020 21 Output 1BM14CS001 Arjun 1BM14CS002 Balaji Database Management System (DBMS)

22. R D B M S Row or Tuple or R e c o r d Database table Name: student_info U SN Name 1BM14CS001 Arjun 1BM14CS002 Balaji RDBMS stands for Relational Database Management System . RDBMS is the basis for SQL, and for all modern database systems such as Oracle, MySQL, MS SQL Server, IBM DB2, and Microsoft Access. The data in RDBMS is stored in database objects called tables . A table is a collection of related data entries and it consists of columns and rows . Column or Attribute T a b l e or Relation 10 October 2020 22

23. SQL commands fro Data Definition Command Description create to create new table or database alter for alteration truncate delete data from table drop to drop a table rename to rename a table 10 October 2020 23

24. SQL: create command 10 October 2020 24 create is a DDL (Data Definition) command used to create a table or a database. Creating a Database Syntax: create database database-name ; Example: create database student ;

25. SQL: create command create is a DDL (Data Definition) command used to create a table or a database. Creating a Database Syntax: create database database-name ; Example: create database student ; Creating a Table Syntax: create table table-name ( column-name1 datatype1, column-name2 datatype2 ); Example: create table student_info ( USN char(10), Name char(30) ); student is database name or schema name student_info is the table name Here student_info will be created under the database student student_info 10 October 2020 25 USN Name student Da t a b as e

26. SQL: create command 10 October 2020 26 create table student_info ( USN char(10), Name char(30); );

27. Specifying Constraints in SQL 10 October 2020 27 Specifying Attributes constraints and Attribute values Specifying Key and Referential Integrity constraints Specifying constraints on Tuples using CHECK

28. Specifying Attributes constraints and Attribute values 10 October 2020 28 create table student_info ( USN char(10), Name char(30) NOT NULL , DepName char(3) NOT NULL DEFAULT ‘CSE’, Marks int NOT NULL CHECK (Marks > 0 AND Marks < 101) ); Constraints on Attributes NOT NULL DEF A ULT CHECK

29. Specifying Key and Referential Integrity constraints 10 October 2020 29 PRIMARY KEY Constraint create table student_info ( USN char(10), Name char(30) NOT NULL , DepName char(3) NOT NULL DEFAULT ‘CSE’, Marks int NOT NULL CHECK (Marks > AND Marks < 101) PRIMARY KEY (USN) );

30. Specifying Key and Referential Integrity constraints 10 October 2020 30 PRIMARY KEY Constraint Question: What is the difference between following two create commands ? create table student_info ( USN char(10), Name char(30) NOT NULL , DepName char(3) NOT NULL DEFAULT ‘CSE’, Marks int NOT NULL CHECK (Marks > AND Marks < 101) PRIMARY KEY (USN) ); create table student_info ( USN char(10), Name char(30) NOT NULL , DepName char(3) NOT NULL DEFAULT ‘CSE’, Marks int NOT NULL CHECK (Marks > 0 AND Marks < 101) CONSTRAINT usn_pk PRIMARY KEY (USN) );

31. Specifying Key and Referential Integrity constraints H a s Student 1 D e pa r t m e nt M D - I D □ Referential Integrity constraint or Foreign Key Constraint USN S - N am e D-Name create table Department ( 10 October 2020 31 D_ID int, D_Name char(3), PRIMARY KEY (D_ID) ); create table Student ( USN char(10), S_Name char(20), D_ID int, PRIMARY KEY (USN), FOREIGN KEY (D_ID) REFERENCES Department(D_ID) );

32. Specifying Key and Referential Integrity constraints D_ID D-Name 10 CSE 20 ISE □ Referential Integrity constraint or Foreign Key Constraint Student Table Department Table USN S-Name D_ID 1BM14CS001 Akash 10 1BM14CS002 Bharath 10 1BM14CS003 Ragu 10 1BM14CS004 Mohan 20 1BM14CS005 Nikil 20 Question Assume, D_ID value of CSE department in Department table has been updated to 50 . Then how the values of D_ID column in Student table should be affected Assume, Department table has been deleted. Then how the values of D_ID column in Student table should be affected 10 October 2020 32

33. Specifying Key and Referential Integrity constraints 10 October 2020 33 □ Referential Integrity constraint or Foreign Key Constraint create table Department ( D_ID int, D_Name int(3), PRIMARY KEY (D_ID) ); create table Student ( USN char(10), S_Name char(20), D_ID int, PRIMARY KEY (USN), FOREIGN KEY (D_ID) REFERENCES Department(D_ID) ON DELETE CASCADE );

34. Specifying Key and Referential Integrity constraints 10 October 2020 34 □ Referential Integrity constraint or Foreign Key Constraint create table Department ( D_ID int, D_Name int(3), PRIMARY KEY (D_ID) ); create table Student ( USN char(10), S_Name char(20), D_ID int, PRIMARY KEY (USN), FOREIGN KEY (D_ID) REFERENCES Department(D_ID) ON DELETE SET NULL );

35. Specifying Key and Referential Integrity constraints D_ID D-Name 10 CSE 20 ISE □ Referential Integrity constraint or Foreign Key Constraint Student Table Department Table USN S-Name D_ID 1BM14CS001 Akash 10 1BM14CS002 Bharath 10 1BM14CS003 Ragu 10 1BM14CS004 Mohan 20 1BM14CS005 Nikil 20 Question 1. When the following SQL Command is executed, what will be the status of Student table contents? Update Department set D_ID=50 where D_ID=10; 10 October 2020 35

36. Specifying Key and Referential Integrity constraints D_ID D-Name 10 CSE 20 ISE □ Referential Integrity constraint or Foreign Key Constraint Student Table Department Table USN S-Name D_ID 1BM14CS001 Akash 10 1BM14CS002 Bharath 10 1BM14CS003 Ragu 10 1BM14CS004 Mohan 20 1BM14CS005 Nikil 20 Question 1. When the following SQL Command is executed, what will be the status of Student table contents? Delete from Department where D_ID=10; 10 October 2020 36

37. ON DELETE CASCADE ON DELETE SET NULL ON DELETE SET DEFAULT ON UPDATE CASCADE ON UPDATE SET NULL ON UPDATE SET DEFAULT 10 October 2020 37 Specifying Key and Referential Integrity constraints

38. Schema change statements in SQL 10 October 2020 38 Drop command can be used to drop tables or constraints. The DROP TABLE Statement General Syntax: DROP TABLE table_name The DROP DATABASE Statement General Syntax: DROP DATABASE database_name The TRUNCATE TABLE Statement What if we only want to delete the data inside the table, and not the table itself ? Then, use the TRUNCATE TABLE statement: General Syntax: TRUNCATE TABLE table_name

39. Schema change statements in SQL □ Drop command D_ID D e p _ n a me 10 CSE 20 ISE department Table student Table USN Name D_ID 1BM14CS001 Avinash 10 1BM14CS002 Balaji 10 1BM14CS003 Ram 10 1BM14CS004 Mohan 20 1BM14CS005 Nikil 20 Assume the above two tables were created using following create commands create table department (D_ID integer, Dep_name varchar(3),primary key (D_ID)); create table student (USN varchar(10),Name varchar(20),D_ID integer, primary key(usn),foreign key (D_ID) references department(D_ID)); Note: Assume after above two table creation, values shown above are inserted into tables 10 October 2020 39

40. Schema change statements in SQL □ Drop command D_ID D e p _ n a me 10 CSE 20 ISE department Table student Table USN Name D_ID 1BM14CS001 Avinash 10 1BM14CS002 Balaji 10 1BM14CS003 Ram 10 1BM14CS004 Mohan 20 1BM14CS005 Nikil 20 Question 1. When the following SQL Command is executed, what will be the output insert into student values (‘1BM14CS006’,’Patel’,50); 10 October 2020 40

41. Schema change statements in SQL □ Drop command D_ID D e p _ n a me 10 CSE 20 ISE department Table student Table USN Name D e p_ num 1BM14CS001 Avinash 10 1BM14CS002 Balaji 10 1BM14CS003 Ram 10 1BM14CS004 Mohan 20 1BM14CS005 Nikil 20 Question 1. When the following SQL Command is executed, what will be the output insert into student values (‘1BM14CS006’,’Patel’,50); Cannot add a row: a foreign key constraint fails 10 October 2020 41

42. Schema change statements in SQL □ Drop command D_ID D e p _ n a me 10 CSE 20 ISE department Table student Table USN Name D e p_ num 1BM14CS001 Avinash 10 1BM14CS002 Balaji 10 1BM14CS003 Ram 10 1BM14CS004 Mohan 20 1BM14CS005 Nikil 20 Question 1. When the following SQL Command is executed, what will be the output drop table department; 10 October 2020 42

43. Schema change statements in SQL □ Drop command D_ID D e p _ n a me 10 CSE 20 ISE department Table student Table USN Name D e p_ num 1BM14CS001 Avinash 10 1BM14CS002 Balaji 10 1BM14CS003 Ram 10 1BM14CS004 Mohan 20 1BM14CS005 Nikil 20 Question 1. When the following SQL Command is executed, what will be the output drop table department; Department Table will not be deleted because of foreign key constraint 10 October 2020 43

44. Schema change statements in SQL □ Drop command D_ID D e p _ n a me 10 CSE 20 ISE department Table student Table USN Name D_ID 1BM14CS001 Avinash 10 1BM14CS002 Balaji 10 1BM14CS003 Ram 10 1BM14CS004 Mohan 20 1BM14CS005 Nikil 20 Question 1. When the following SQL Command is executed, what will be the output drop table student; 10 October 2020 44

45. Schema change statements in SQL □ Drop command D_ID D e p _ n a me 10 CSE 20 ISE department Table student Table USN Name D e p_ num 1BM14CS001 Avinash 10 1BM14CS002 Balaji 10 1BM14CS003 Ram 10 1BM14CS004 Mohan 20 1BM14CS005 Nikil 20 Question 1. When the following SQL Command is executed, what will be the output drop table student; student Table will be deleted 10 October 2020 45

46. Schema change statements in SQL □ Drop command D_ID D e p _ n a me 10 CSE 20 ISE department Table student Table USN Name D e p_ Num 1BM14CS001 Avinash 10 1BM14CS002 Balaji 10 1BM14CS003 Ram 10 1BM14CS004 Mohan 20 1BM14CS005 Nikil 20 Question 1. When the following SQL Command is executed, what will be the output drop table department cascade constraints; 10 October 2020 46

47. Schema change statements in SQL □ Drop command D_ID D e p _ n a me 10 CSE 20 ISE department Table student Table USN Name D e p_ Num 1BM14CS001 Avinash 10 1BM14CS002 Balaji 10 1BM14CS003 Ram 10 1BM14CS004 Mohan 20 1BM14CS005 Nikil 20 Question 1. Whether the following two SQL commands will be executed successfully drop table department cascade constraints; insert into student values (‘1BM14CS006’,’Patel’,50); Into the student table new row will be inserted with dep_num 50 10 October 2020 47

48. Schema change statements in SQL 10 October 2020 48 □ Drop Command General syntax: drop table table_name [drop_behavior] There are two drop behavior options Cascade : All constraints that references the table are dropped automatically along with the table itself. Restrict : Table is dropped only it is not referenced in any constraints.

49. SQL - DROP TABLE DROP TABLE Deletes table structure Cannot be recovered Use with caution DROP TABLE UnqTable;

50. SQL - ALTER TABLE □ Add/Drop Column Syntax: ALTER TABLE tablename (ADD/MODIFY/DROP column_name) ALTER TABLE Customer_Details ADD Contact_Phone Char(10); ALTER TABLE Customer_Details MODIFY Contact_Phone Char(12); ALTER TABLE Customer_Details DROP (Contact_Phone);

51. SQL - ALTER TABLE □ Add/Drop Primary key ALTER TABLE Customer_Details ADD CONSTRAINT Pkey1 PRIMARY KEY (Account_No); ALTER TABLE Customer_Details ADD CONSTRAINT Pkey2 PRIMARY KEY (Account_No, Cust_ID); ALTER TABLE Customer_Details DROP PRIMARY KEY; Or ALTER TABLE Customer_Details DROP CONSTRAINT Pkey1;

52. Truncate Table □ Deleting All Rows of a table TRUNCATE TABLE Customer_Details

53. Basic queries in SQL 10 October 2020 53 To retrieve data from database table, basic SQL statement is SELECT Syntax: select column_name_list from table_name where condition;

54. Select statement 10 October 2020 54 □ Where clause conditions = != < > <= >=

55. SQL select statement D_ID D-Name 10 CSE 20 ISE department Table student Table usn name d e p_ num marks 1BM14CS001 Avinash 10 100 1BM14CS002 Balaji 10 80 1BM14CS003 Chandan 10 45 1BM14CS004 Dinesh 10 60 1BM14IS005 Avinash 20 90 1. List out the USN’s of the students who belong to department number 10? 10 October 2020 55

56. SQL select statement D_ID D-Name 10 CSE 20 ISE department Table student Table usn name d e p_ num marks 1BM14CS001 Avinash 10 100 1BM14CS002 Balaji 10 80 1BM14CS003 Chandan 10 45 1BM14CS004 Dinesh 10 60 1BM14IS005 Avinash 20 90 1. List out the USN’s of the students who belong to department number 10? Select USN from Student where dp_num=10; 10 October 2020 56

57. SQL select statement d_id d e p _n a me 10 CSE 20 ISE department Table student Table usn name d e p_ num marks 1BM14CS001 Avinash 10 100 1BM14CS002 Balaji 10 80 1BM14CS003 Chandan 10 45 1BM14CS004 Dinesh 10 60 1BM14IS005 Avinash 20 90 Question 1. List out the USN’s of the students who belong to CSE department ? 10 October 2020 57

58. SQL select statement d_id d e p _n a me 10 CSE 20 ISE department Table student Table usn name d e p_ num marks 1BM14CS001 Avinash 10 100 1BM14CS002 Balaji 10 80 1BM14CS003 Chandan 10 45 1BM14CS004 Dinesh 10 60 1BM14IS005 Avinash 20 90 Question 1. List out the USN’s of the students who belong to CSE department ? 10 October 2020 58

59. SQL select statement: Aliasing d_id d e p _n a me 10 CSE 20 ISE department Table student Table usn name d e p_ num marks 1BM14CS001 Avinash 10 100 1BM14CS002 Balaji 10 80 1BM14CS003 Chandan 10 45 1BM14CS004 Dinesh 10 60 1BM14IS005 Avinash 20 90 10 October 2020 59

60. SQL select statement: Distinct Student Table usn name d e p_ num marks 1BM14CS001 Avinash 10 100 1BM14CS002 Balaji 10 80 1BM14CS003 Chandan 10 45 1BM14CS004 Dinesh 10 60 1BM14IS001 Avinash 20 90 Avinash Balaji C h a n d a n Dinesh Avinash 10 October 2020 60

61. SQL select statement: Distinct Student Table usn name d e p_ num marks 1BM14CS001 Avinash 10 100 1BM14CS002 Balaji 10 80 1BM14CS003 Chandan 10 45 1BM14CS004 Dinesh 10 60 1BM14IS001 Avinash 20 90 Avinash Balaji C h a n d a n Dinesh 10 October 2020 61

62. SQL select statement: Order By Student Table usn name d e p_ num marks 1BM14CS001 Avinash 10 100 1BM14CS002 Balaji 10 80 1BM14CS003 Chandan 10 45 1BM14CS004 Dinesh 10 60 1BM14IS001 Avinash 20 90 10 October 2020 62

63. SQL select statement: Order By Student Table usn name d e p_ num marks 1BM14CS001 Avinash 10 100 1BM14CS002 Balaji 10 80 1BM14CS003 Chandan 10 45 1BM14CS004 Dinesh 10 60 1BM14IS001 Avinash 20 90 10 October 2020 63

64. SQL select statement: Order By 10 October 2020 64 Student Table usn name d e p_ num marks 1BM14CS001 Avinash 10 100 1BM14CS002 Balaji 10 80 1BM14CS003 Chandan 10 45 1BM14CS004 Dinesh 10 60 1BM14IS001 Avinash 20 90 Question List usn, name of the students who belong to department number 10 ordered by ascending order of their marks ?

65. SQL select statement: Order By Student Table usn name d e p_ num marks 1BM14CS001 Avinash 10 100 1BM14CS002 Balaji 10 80 1BM14CS003 Chandan 10 45 1BM14CS004 Dinesh 10 60 1BM14IS001 Avinash 20 90 Question List usn, name of the students who belong to department number 10 ordered by ascending order of their marks ? 10 October 2020 65

66. w.r.t SELECT statement SQL operators: LIKE, IN, BETWEEN 10 October 2020 62

67. The SQL SELECT Statement The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set. □ Note: SQL is not case sensitive . SELECT is the same as select. 10 October 2020 67

68. w.r.t to SELECT SQL statement 10 October 2020 68 1. SQL Alias: We can give a table or a column another name by using an alias. SQL Alias Syntax for Tables SELECT column_name(s) FROM table_name AS alias_name; SQL Alias Syntax for Columns SELECT column_name AS alias_name FROM table_name; 2. The DISTINCT keyword can be used to return only distinct (different) values. □ SQL SELECT DISTINCT Syntax SELECT DISTINCT column_name(s) FROM table_name

69. w.r.t to SELECT sql statement 3. The ORDER BY keyword is used to sort the result-set by a specified column. □ SQL ORDER BY Syntax SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC | DESC; 4. The WHERE clause is used to filter records. The WHERE clause is used to extract only those records that fulfill a specified criterion. SQL WHERE Syntax SELECT column_name(s) FROM table_name WHERE column_name operator value With the WHERE clause, the following operators can be used: 10 October 2020 69

70. Substring Pattern Matching: SQL LIKE Operator 10 October 2020 70 The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. SQL LIKE Syntax SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern

71. SQL LIKE Operator The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. SQL LIKE Syntax SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern Example: Write SQL statement to list the names starting with letter “A” from following student table. 10 October 2020 71

72. SQL LIKE Operator Write SQL statement to list the names starting with letter “A” from the following student table . 10 October 2020 72

73. SQL LIKE Operator Write SQL statement to list the names starting with letter “A” from the following student table . Note: Wildcard character % , A substitute for zero or more characters 10 October 2020 73

74. SQL LIKE Operator Question Write SQL statement to list name of the students whose names end with letter ‘ h ’? 10 October 2020 74 Note: Wildcard character % , A substitute for zero or more characters

75. SQL LIKE Operator Question Write SQL statement to list name of the students whose names end with letter ‘ h ’? 10 October 2020 75

76. SQL LIKE Operator Question Write SQL statement to list name of the students whose names are having the substring ‘ in ’? 10 October 2020 76 Note: Wildcard character % , A substitute for zero or more characters

77. SQL LIKE Operator Question Write SQL statement to list name of the students whose names are having the substring ‘ in ’? 10 October 2020 77

78. SQL LIKE Operator Question Write SQL statement to list name of the students whose names start with letter ‘A’ or ‘B’ 10 October 2020 78

79. SQL LIKE Operator Question Write SQL statement to list name of the students whose names start with letter ‘A’ or ‘B’ 10 October 2020 79

80. SQL LIKE Operator Question Write SQL statement to list name of the students whose names start with letter ‘A’ or ‘D’ but end with letter ‘h’ 10 October 2020 80

81. SQL LIKE Operator Question Write SQL statement to list name of the students whose names start with letter ‘A’ or ‘D’ but end with letter ‘h’ 10 October 2020 81

82. SQL LIKE Operator Question Write SQL statement to list name of the students whose third letter in the name is ‘ a ’ . 10 October 2020 82

83. SQL LIKE Operator Question Write SQL statement to list name of the students whose third letter in the name is ‘ a ’ . Note: An underscore (_) in the pattern matches exactly one character A percent sign (%) in the pattern can match zero or more characters underscore (_) and percent sign (%) are referred as wildcard characters 10 October 2020 83

84. SQL - INSERT INTO Syntax: INSERT INTO tablename (Columnlist) VALUES ( value list ) □ Single-row insert with values for all Columns INSERT INTO Customer_Details VALUES (106, 'Costner', 'A.', 'Kevin', 3350, 'Savings', 'Indus Bank', '[email protected]') Inserting one row, few columns at a time INSERT INTO Customer_Details ( C u st _ I D , C u st _ La st _ N a m e , C u st _M i d _ N a m e , C u st _ F i r st _ N a m e , A cc o un t _ N o , Account_Type, Bank_Branch) VALUES (107, 'Robert', 'B.', 'Dan', 3351, 'Savings', 'Indus Bank')

85. SQL - INSERT INTO □ Inserting NULL Value into a Column INSERT INTO Customer_Details (Cust_ID, Cust_Last_Name, Cust_Mid_Name, Cust_First_Name, Account_No, Account_Type, Bank_Branch) VALUES (108, 'Robert', 'B.', 'Dan', 3352, 'Savings', 'Indus Bank') Or INSERT INTO Customer_Details (Cust_ID, Cust_Last_Name, Cust_Mid_Name, Cust_First_Name, Account_No, Account_Type, Bank_Branch, Cust_Email ) VALUES (108, 'Robert', 'B.', 'Dan', 3352, 'Savings', 'Indus Bank‘, NULL )

86. SQL - DELETE FROM □ With or without WHERE clause Syntax: DELETE FROM tablename WHERE condition Deleting All Rows DELETE FROM Customer_Details Deleting Specific Rows DELETE FROM Customer_Details WHERE Cust_ID = 102;

87. Truncate DELETE TRUNCATE Data can be recovered Data cannot be recovered. DML statement DDL statement DELETE does not do so TRUNCATE releases the memory occupied by the records of the table

88. Updating Particular rows UPDATE Customer_Fixed_Deposit SET Rate_of_Interest_in_Percent = 7.3 WHERE Amount_in_Dollars > 3000; SQL - UPDATE Syntax: UPDATE tablename SET column_name = value [ WHERE condition ] Updating All Rows UPDATE Customer_Fixed_Deposit SET Rate_of_Interest_in_Percent = NULL;

89. SQL - UPDATE □ Updating Multiple Columns UPDATE Customer_Fixed_Deposit SET Cust_Email = ‘ [email protected] ’ , Rate_of_Interest_in_Percent = 7.3 WHERE Cust_ID = 104

90. Retrieving All columns from a table To select set of column names, SELECT column1, column2,… FROM TableName Example SELECT * FROM Customer_Details; Or SELECT Cust_ID, Cust_Last_Name, Cust_Mid_Name, Cust_First_Name, Account_No, Account_Type, Bank_Branch, Cust_Email FROM Customer_Details;

91. Retrieving Few Columns SELECT Cust_ID, Account_No FROM Customer_Details; Implementing Customized Columns Names SELECT Account_No AS “Customer Account No.”, Total_Available_Balance_in_Dollars AS “Total Balance” FROM Customer_Transaction;

92. SQL - ALL, DISTINCT Get all Customers Name: SELECT ALL Cust_Last_Name FROM Customer_Details; Or SELECT Cust_Last_Name FROM Customer_Details; Get all distinct Customer Name SELECT DISTINCT Cust_Last_Name FROM Customer_Details;

93. SELECT COL1,COL2,......... FROM TABLE NAME WHERE < SEARCH CONDITION> Retrieving a subset of rows □ For retrieval of rows based on some condition, the syntax is

94. SELECT Account_No, Total_Available_Balance_in_Dollars FROM Customer_Transaction WHERE Total_Available_Balance_in_Dollars > 10000.00; Relational operators = , < , > , <= , >= , != or < > □ List all customers with an account balance > $10000 Relational operators SELECT Cust_ID, Account_No FROM Customer_Details WHERE Cust_First_Name = ‘Graham’; List the Cust_ID, Account_No of ‘Graham’

95. S E L E C T A cc o u n t _ N o FROM Customer_Transaction WHERE Total_Available_Balance_in_Dollars >= 10000.00; □ List all Account_No where Total_Available_Balance_in_Dollars is atleast $10000.00 Relational operators

96. List all Account_Nos with balance in the range $10000.00 to $20000.00. S E L E C T A cc o u n t _ N o FROM Customer_Transaction WHERE Total_Available_Balance_in_Dollars >= 10000.00 AND Total_Available_Balance_in_Dollars <= 20000.00; Or S E L E C T A cc o u n t _ N o FROM Customer_Transaction WHERE Total_Available_Balance_in_Dollars BETWEEN 10000.00 AND 20000.00; Retrieval using BETWEEN

97. List all customers who have account in Capital Bank or Indus Bank . SELECT Cust_ID FROM Customer_Details WHERE Bank_Branch = ‘Capital Bank’ OR Bank_Branch = ‘Indus Bank’; Or SELECT Cust_ID FROM Customer_Details WHERE Bank_Branch IN (‘Capital Bank’, ‘Indus Bank’); Retrieval using IN

98. create table test1 ( roll number, name char(20), age number); insert into test1 values( 400,'ccbb', 40); select *from test1; create table prasad AS select * from test1where age>20; select *from prasad; 10 October 2020 98

99. Views in SQL 10 October 2020 99 A view is a kind of “ Virtual Table ” A view is customized representation of data from one or more tables . The tables that the view is referencing are know as base tables . A view is considered as a stored query or a virtual table. Why we need Views ? Views, which are kind of virtual tables, allow users to do the following: Structure data in a way that users or classes of users find natural or intuitive. Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more. Summarize data from various tables which can be used to generate reports.

100. Creating Views 10 October 2020 100 Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables, or another view. To create a view, a user must have the appropriate system privilege according to the specific implementation. The basic CREATE VIEW syntax is as follows: CREATE VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];

101. Creating Views □ Example faculty(F_ID, F_name, Dnum, Email_ID, Salary) department(Dname,Dnumber) dep_info(Dept_name, Num_of_Emps, Total_Salary) V I R T U A L Table 10 October 2020 101

102. Creating Views □ Example faculty(F_ID, F_name, Dnum, Email_ID, Salary) department(Dname,Dnumber) dep_info(Dept_name, Num_of_Emps, Total_Salary) V I R T U A L Table 10 October 2020 102

103. Updating View faculty(F_ID, F_name, Dnum, Email_ID, Salary) cs_faculty_view (F_ID, F_name, Dnum, Email_ID) V I R T U A L Table 10 October 2020 103

104. Updating View faculty(F_ID, F_name, Dnum, Email_ID, Salary) CREATE VIEW cs_faculty_view (F_ID, F_name, Dnum, Email_ID) AS Select * From faculty Where Dnum=10; cs_faculty_view (F_ID, F_name, Dnum, Email_ID) V I R T U A L Table 10 October 2020 104

105. Updating View Update cs_faculty_view Set Email_ID=‘ [email protected] ’ Where Emp_name=‘Balaji’; faculty(F_ID, F_name, Dnum, Email_ID, Salary) CREATE VIEW cs_faculty_view (F_ID, F_name, Dnum, Email_ID) AS Select * From faculty Where Dnum=10; cs_faculty_view (F_ID, F_name, Dnum, Email_ID) V I R T U A L Table 10 October 2020 105

106. Updating a View 10 October 2020 106 A view can be updated under certain conditions: The SELECT clause may not contain the keyword DISTINCT. The SELECT clause may not contain aggregate functions. The SELECT clause may not contain set functions. The SELECT clause may not contain set operators. The SELECT clause may not contain an ORDER BY clause. The FROM clause may not contain multiple tables. The WHERE clause may not contain subqueries. The query may not contain GROUP BY or HAVING. Calculated columns may not be updated. All NOT NULL columns from the base table must be included in the view in order for the INSERT query to function. So if a view satisfies all the above-mentioned rules then you can update a view. Note: Similarly Rows of data can be inserted into a view. Rows of data can be deleted from a view.

107. Dropping Views: 10 October 2020 107 Syntax DROP VIEW view_name; Example: drop view cs_faculty_view;

108. Advantages and Disadvantages of views 10 October 2020 108 Advantages of views       Security: Each user can be given permission to access the database only through a small set of views that contain the specific data the user is authorized to see, thus restricting the user's access to stored data Query Simplicity: A view can draw data from several different tables and present it as a single table, turning multi-table queries into single-table queries against the view. Structural simplicity: Views can give a user a "personalized" view of the database structure, presenting the database as a set of virtual tables that make sense for that user. Consistency: A view can present a consistent, unchanged image of the structure of the database, even if the underlying source tables are split, restructured, or renamed. Data Integrity: If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints. Logical data independence: View can make the application and database tables to a certain extent independent. If there is no view, the application must be based on a table. With the view, the program can be established in view of above, to view the program with a database table to be separated. Disadvantages of views   Performance: Views create the appearance of a table, but the DBMS must still translate queries against the view into queries against the underlying source tables. If the view is defined by a complex, multi-table query then simple queries on the views may take considerable time. Update restrictions: When a user tries to update rows of a view, the DBMS must translate the request into an update on rows of the underlying base tables. This is possible for simple views, but more complex views are often restricted to read-only.

109. Converting ER diagram to Tables □ Relationship: One-to-One 1 F - I D 1 F - N a m e D - I D D-Name F-ID F-Name 1 Dr. H S Guruprasad 2 Dr. Umadevi V 3 Dr. Gowrishankar Approach 1: Foreign Key Faculty Table D- I D D-Name H e a d i n g F-ID 10 CSE 1 20 ISE 3 Department Table 10 October 2020 109

110. Converting ER diagram to Tables □ Relationship: One-to-One 1 F - I D 1 F - N a m e D - I D D-Name Approach 1: Foreign Key F-ID F-Name 1 Dr. H S Guruprasad 2 Dr. Umadevi V 3 Dr. Gowrishankar Faculty Table D- I D D-Name H e a d i n g F-ID 10 CSE 1 20 ISE 3 Department Table P r i ma r y Key F or e i gn Key P r i ma r y Key 10 October 2020 110

111. Converting ER diagram to Tables □ Relationship: One-to-One 1 F - I D 1 F - N a m e D - I D D-Name Approach 2: Merged Relation approach Merging two entity types and relationship into one single relation. This may be appropriate when both participations are total 10 October 2020 111 F- I D F-Name D-ID D-Name HOD 1 Dr. H S Guruprasad 10 CSE Yes 2 Dr. Umadevi V 10 CSE No 3 Dr. Gowrishankar 20 ISE Yes Faculty –Department Table

112. Converting ER diagram to Tables □ Relationship: One-to-One 1 1 F - I D F - N a m e D - I D D-Name Approach 3: Cross-reference or relationship relation approach Faculty Table F-ID F-Name Dr. H S Guruprasad Dr. Umadevi V Dr. Gowrishankar Department Table D - D - N ame ID 10 CSE 20 ISE HOD Table D - I D F - I D 10 1 20 3 Lookup Table 10 October 2020 112

113. Converting ER diagram to Tables □ Relationship: One-to-Many H a s Student 1 D e pa r t m e nt M D - I D D-Name U S N S - N am e 10 October 2020 113

114. Converting ER diagram to Tables □ Relationship: One-to-Many H a s Student 1 D e pa r t m e nt M D - I D D-Name U S N S - N am e D-ID D-Name 10 CSE 20 ISE Department Table USN S-Name D-ID 1BM14CS001 Akash 10 1BM14CS002 Bharath 10 1BM14CS003 Ragu 10 1BM14CS004 Mohan 20 1BM14CS005 Nikil 20 Student Table Approach 1 10 October 2020 114

115. Converting ER diagram to Tables □ Relationship: One-to-Many H a s Student 1 D e pa r t m e nt M D - I D D-Name U S N D-ID D-Name 10 CSE 20 ISE 10 October 2020 115 Department Table USN S-Name 1BM14CS001 Akash 1BM14CS002 Bharath 1BM14CS003 Ragu 1BM14CS004 Mohan 1BM14CS005 Nikil Student Table USN D-ID 1BM14CS001 10 1BM14CS002 10 1BM14CS003 10 1BM14CS004 20 1BM14CS005 20 S-Name Department Student Table Approach 2

116. Converting ER diagram to Tables □ Relationship: Many-to-Many Student En ro ll s _ f o r Course U S N S - N am e C - I D C - N a m e 10 October 2020 116

117. Converting ER diagram to Tables C-ID C-Name 10 DBMS 20 Java Course Table USN S-Name 1BM14CS001 Avinash 1BM14CS002 Balaji 1BM14CS003 Chandan Student Table USN C-ID 1BM14CS001 10 1BM14CS001 20 1BM14CS002 20 1BM14CS003 10 Student E n r o ll s _ f o r Course □ Relationship: Many-to-Many USN S - N am e C - I D C - N a m e Student Course Table 10 October 2020 117

118. Converting ER diagram to Tables C-ID C-Name 10 DBMS 20 Java Course Table USN S-Name 1BM14CS001 Avinash 1BM14CS002 Balaji 1BM14CS003 Chandan Student Table USN C-ID 1BM14CS001 10 1BM14CS001 20 1BM14CS002 20 1BM14CS003 10 Student E n r o ll s _ f o r Course □ Relationship: Many-to-Many USN S - N am e C - I D C - N a m e Student Course Table 10 October 2020 118

119. Converting ER diagram to Tables Faculty D e p e n d e n t s _ of Dependent N am e ----- P a r t i a l Key Weak E n t i t y O w n e r Entity Identifying R e l a t i o n s hi p F - I D F - N a m e 10 October 2020 119

120. Converting ER diagram to Tables □ Weak Entity Faculty D e p e n d e n t s _ of Dependent N am e ----- P a r t i a l Key Weak E n t i t y O w n e r Entity Identifying R e l a t i o n s hi p F - I D F - N a m e F - I D F-Name 1 Dr. Guruprasad 2 Dr. Gowrishankar Faculty Table F-ID D e p e n d e n t Name 10 Ram 10 Ravi 20 Ram Dependent Table 10 October 2020 120

121. Introduction to Relational Algebra 10 October 2020 1 17

122. Why you should Learn “Relational Algebra” ? 10 October 2020 122 Relational Algebra is Core of Relational Query Language, Example: SQL Provides framework for Query implementation and optimization Is a mathematical language for manipulating relations.

123. Relational Algebra based on Relational Model USN Name 1BM14CS001 Arjun 1BM14CS002 Balaji student_info Relational model due to Edgar Teds Codd , a mathematician at IBM in 1970 Won Turing award 1981 Won Turing award 1981 10 October 2020 123

124. Relational Algebra based on Relational Model Row or Tuple or R e c o r d Database table Name: student_info U SN Name 1BM14CS001 Arjun 1BM14CS002 Balaji Column or Attribute T a b l e or Relation USN Name 1BM14CS001 Arjun 1BM14CS002 Balaji student_info Relational model due to Edgar “Ted” Codd , a mathematician at IBM in 1970 Won Turing award 1981 Won Turing award 1981 10 October 2020 124

125. RDBMS Architecture Relational Database Management System (RDBMS) How does a SQL engine work ? SQL Q u e r y Relational Algebra (RA) Plan Optimized RA Plan Ex e c u t i o n 10 October 2020 125 Declarative query (from user) Translate to relational algebra expression Find logically equivalent- but more efficient - RA expression Execute each operator of the optimized plan!

126. RDBMS Architecture Relational Database Management System (RDBMS) How does a SQL engine work ? Relational Algebra allows us to translate declarative (SQL) queries into precise and optimizable expressions! 10 October 2020 126

127. What is an “Algebra” 10 October 2020 127 Mathematical system consisting of: Operands: variables or values from which new values can be constructed. O p e r a t o r s : sy m b o l s d e n o ti n g p r o c e d u r e s th a t construct new values from given values.

128. What is Relational Algebra (RA) ? A n a l g e b r a w h o s e o p e r a n ds a r e d a t a b as e t a b l e s o r 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. a q u e r y T h e r e s ul t i s a n a l g e b r a t h a t ca n b e u s e d a s language for relations. Relational Algebra Operations 10 October 2020 128

129. Keep in mind: RA operates on sets! RDBMSs use multisets , however in relational algebra formalism we will consider sets! Also: we will consider the named perspective , where every attribute must have a unique name  attribute order does not matter… 10 October 2020 129

130. Unary Relational Operations 10 October 2020 130 Select Operation (σ sigma) Project Operation (∏ phi)

131. Select Operation (σ sigma) 10 October 2020 131 It selects tuples that satisfy the given predicate from a relation. Select written as Notation σ selection condition ( R ) Where σ stands for selection predicate R stands for relation / table name Selection condition is prepositional logic formula which may use connectors like and, or, and not. These terms m a y u s e r e l a t i o n a l o p e r a t o r s li k e − =, ≠, ≥, < , >, ≤ .

132. Select Operation (σ) SELECT * FROM Student WHERE marks > 60 ; Select Operation (σ) Returns all tuples which satisfy a condition SQL: σ selection condition ( R ) S t u d e n t Equivalent Relational Algebra Expression 10 October 2020 132

133. Select Operation (σ) SELECT * FROM Student WHERE marks > 60 ; Select Operation (σ) Returns all tuples which satisfy a condition SQL: Relational Algebra Expression σ selection condition ( R ) S t u d e n t 10 October 2020 133

134. Select Operation (σ) SELECT * FROM Student WHERE name=‘Avinash’ ; Select Operation (σ) Returns all tuples which satisfy a condition SQL: σ selection condition ( R ) S t u d e n t Relational Algebra Expression 10 October 2020 134

135. Project Operation (∏ phi) 10 October 2020 135 It projects column(s) that satisfy a given predicate. Written as Notation ∏ A1,…., An ( R ) Where A 1 , A 2 , A n are attribute names of relation R . Duplicate rows are automatically eliminated , as relation is a set.

136. Project Operation (∏ phi) Eliminates columns, then removes duplicates Written as Notation ∏ A1,…., An ( R ) SELECT DISTINCT dep_num FROM Student ; S Q L : S t u d e n t d e p_ num 10 20 10 October 2020 136 Relational Algebra Expression

137. Project Operation (∏ phi) Eliminates columns, then removes duplicates Written as Notation ∏ A1,…., An ( R ) SELECT DISTINCT name, dep_num FROM Student ; S Q L : S t u d e n t Relational Algebra Expression 10 October 2020 137

138. Note that RA Operators are Compositional! SELECT DISTINCT name, marks FROM Student WHERE marks > 60 ; How do we represent this query in Relational Algebra? How do we represent this query in Relational Algebra? S t u d e n t 10 October 2020 138

139. Note that RA Operators are Compositional! SELECT DISTINCT name, marks FROM Student WHERE marks > 60 ; How do we represent this query in Relational Algebra? How do we represent this query in Relational Algebra? S t u d e n t 10 October 2020 139

140. Note that RA Operators are Compositional! SELECT DISTINCT name, marks FROM Student WHERE marks > 60 ; Are these logically equivalent? Are these logically equivalent? S t u d e n t How do we represent this query in Relational Algebra? 10 October 2020 140 How do we represent this query in Relational Algebra?

141. Note that RA Operators are Compositional! SELECT DISTINCT name, marks FROM Student WHERE marks > 60 ; Are these logically equivalent? Are these logically equivalent? S t u d e n t How do we represent this query in Relational Algebra? 10 October 2020 141 How do we represent this query in Relational Algebra?

142. Q u e s t i o n SELECT DISTINCT name, marks FROM Student WHERE marks > 60 ; S t u d e n t What is the output of following relational algebra query ? 10 October 2020 142

143. Rename Operation ( ρ rho) 10 October 2020 143 The rename operation allows us to rename the either relation name or attribute names or both. Written as Notation ρ S ( B1,…., Bn) ( R ) or ρ S ( R ) or ρ ( B1,…., Bn) ( R ) S is the new relation name and B 1 , B 2 ….B n are new attribute names.

144. Rename Operation ( ρ rho) □ The rename operation allows us to rename the either relation name or SELECT usn as Student_USN name as Student_Name dep_num as Department_Number FROM Student ; S Q L : attribute names or both. Student ρ S t ud e nt _ U S N , Student_Name, Department_Number ( Student ) S tud e n t 10 October 2020 144

145. Relational Algebra Set Operations - semantics 10 October 2020 145 Consider two relations R and S. UNION of R and S the union of two relations is a relation that includes all the tuples that are either in R or in S or in both R and S. Duplicate tuples are eliminated. INTERSECTION of R and S the intersection of R and S is a relation that includes all tuples that are both in R and S. DIFFERENCE of R and S the difference of R and S is the relation that contains all the tuples that are in R but that are not in S. For set operations to function correctly the relations R and S must be union compatible. Two relations are union compatible if they have the same number of attributes the domain of each attribute in column order is the same in both R and S.

146. Set Operation – Union result <- R U S 10 October 2020 146 r e s ul t

147. Set Operation – Intersection ∩ result <- R ∩ S r e s ul t 10 October 2020 147

148. Set Operation – Difference – r e s ul t <- R – S r e s ul t <- S – R 10 October 2020 148

149. J O I N S Cartesian Product Inner join Equi join Outer join Left-outer join Right-outer join Self join

150. Cartesian Product Or Cross Join  Returns All rows from first table, Each row from the first table is combined with all rows from the second table Example Select * from Table1,Table2;

151. 10 October 2020 147 ID M 1 a 2 b 4 c ID N 2 p 3 q 5 r table1 x table2 The CARTESIAN PRODUCT or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables. N o t a ti o n : t ab l e 1  t a b le 2 table1 table2 Relational Algebra Expression

152. Inner Joins Common type of join An inner join between two (or more) tables is the Cartesian product that satisfies the join condition in the WHERE clause

153. Equi Join 10 October 2020 149 num M 1 a 2 b 4 c t ab l e 1 ID N 2 p 3 q 5 r t ab l e 2 num M ID N 2 b 2 p Select * From table1, table 2 Where num=id; What is the equivalent relational algebra expression ?

154. Get all combinations of emp and cust information such that the emp and cust are co-located. SELECT Table1.Emp_ID, Table1.City, Table2.Cust_ID, Table2.City FROM Table1, Table2 WHERE Table1.City = Table2.City; Retrieval from Multiple tables-Equi join

155. Display the First and Last Name of Customer who have taken Loan Select a.Cust_Id,b.Cust_First_Name,b.Cust_Last_Name from Customer_loan a, customer_details b where a.cust_id = b.cust_id; R e t r i e v a l f r o m M u lt i p l e t a b l e s - Eq u i join

156. Join Operations Join Inner Join Natural Join ( * ) (common attribute names and domain should exist between two tables) Outer Join Left Outer Join Right Outer Join Full Outer Join 10 October 2020 156

157. Natural Join ID M 1 a 2 b 4 c t ab l e 1 ID N 2 p 3 q 5 r t ab l e 2 ID M N 2 b p r e s u l t 10 October 2020 157

158. Natural Join ID M 1 a 2 b 4 c t ab l e 1 ID N 2 p 3 q 5 r t ab l e 2 ID M N 2 b p r e s u l t Natural join does not use any comparison operator. It does not concatenate the way a Cartesian product does. We can perform a Natural Join only if there is at least one common attribute that exists between two relations. In addition, the attributes must have the same name and domain. Natural join acts on those matching attributes where the values of attributes in both the relations are same. 10 October 2020 158

159. Outer join □ Retrieve all rows that match the WHERE clause and also those that have a NULL value in the column used for join.

160. Left Outer Join ID M 1 a 2 b 4 c t ab l e 1 ID N 2 p 3 q 5 r t ab l e 2 ID M ID N 2 b 2 P 1 a 4 c result <- table1 table2 10 October 2020 160 r e s ul t

161. Right Outer Join ID M 1 a 2 b 4 c t ab l e 1 ID N 2 p 3 q 5 r t ab l e 2 result <- table1 table2 10 October 2020 161 ID M ID N 2 b 2 P 3 q 5 r r e s ul t

162. Relational Algebra : Division Operation ÷ 10 October 2020 162 The division operator is used for queries which involve the ‘all’ qualifier such as “Which persons have a bank account at ALL the banks in the country?” “Which students are registered on ALL the courses given by So nthos?” “Which students are registered on ALL the courses that are tau ght in period 1?” Find sailors who have reserved ALL boats R ÷ S is used when we wish to express queries with “ALL”

163. Relational Algebra : Division Operation ÷ A B A ÷ B The division operator takes as input two relations, called the dividend relation ( a on scheme A ) and the divisor relation (b on scheme B ) such that all the attributes in B also appear in A and B is not empty. The output of the division operation is a relation on scheme A with all the attributes common with B . 10 October 2020 163

164. Relational Algebra : Division Operation ÷ A B A ÷ B The division operator takes as input two relations, called the dividend relation ( a on scheme A ) and the divisor relation (b on scheme B ) such that all the attributes in B also appear in A and B is not empty. The output of the division operation is a relation on scheme A with all the attributes common with B . 10 October 2020 164

165. Relational Algebra : Division Operation ÷ A B A ÷ B 10 October 2020 165

166. Relational Algebra : Division Operation ÷ 10 October 2020 166 Student Task Feroz Database1 Feroz Database2 Feroz Compiler1 Eshwar Database1 Sara Database1 Sara Database2 Eshwar Compiler1 Task Database1 Database2 Completed D B P r o j e c t Completed ÷ DBProject What is the Output of the following relational Algebra Expression

167. Relational Algebra : Division Operation ÷ 10 October 2020 167 Student Task Feroz Database1 Feroz Database2 Feroz Compiler1 Eshwar Database1 Sara Database1 Sara Database2 Eshwar Compiler1 Task Database1 Database2 Completed D B P r o j e c t Student Feroz Sara Completed ÷ DBProject

168. Relational Algebra : Division Operation ÷ Student Task F D1 F D2 F C1 E D1 E C1 Task D1 D2 Completed D B P r o j e c t Student F 10 October 2020 168 Completed ÷ DBProject T ← Completed ÷ DBProject

169. Relational Algebra : Division Operation ÷ 10 October 2020 169 The division operator is used for queries which involve the ‘all’ qualifier such as “Which persons have a bank account at ALL the banks in the country?” “Which students are registered on ALL the courses given by So nthos?” “Which students are registered on ALL the courses that are tau ght in period 1?” Find sailors who have reserved ALL boats R ÷ S is used when we wish to express queries with “ALL”

170. Relational Calculus

171. Relational Calculus Comes in two flavors: Tuple relational calculus (TRC) and Domain relational calculus (DRC) . Calculus has variables, constants, comparison ops , logical connectives, and quantifiers . TRC : Variables range over (i.e., get bound to) tuples . DRC : Variables range over domain elements (= field values). Both TRC and DRC are simple subsets of first-

172. Tuple Relational Calculus □ Query has the form: { T | p(T)} □ Answer includes all tuples T such that make the formula p(T) be true .

173. R and S---tuple variables Rel ---relation name

174. TRC Formulas Atomic formula: R  Rel, or R.a op S.b, or R.a op constant op is one of □ Formula: where p and q are formulas, or ■ ■ ■ ■ where variable X…. (there exists) where variable X ….(for allx= x < , > , = ,  ,  ,   p , p  q , p  q ,  X ( p ( X , ) )  X ( p ( X , ) )

175. 10 October 2020 171 Student Table USN S-Name D e p _ N u m 1BM14CS001 Akash 10 1BM14CS002 Bharath 10 1BM14CS003 Ragu 10 1BM14CS004 Mohan 20 1BM14CS005 Nikil 20

176. Find all sailors with a rating above 7 { S | S  S a il or s ^ S . r a t i n g > 7 } Query is evaluated on an instance of Sailors Tuple variable S is instantiated to each tuple of this instance in turn, and the condition “ S.rating > 7 ” is applied to each such tuple. Answer contains all instances of S (which are tuples of Sailors) satisfying the condition.

177. Find sailors rated > 7 who’ve reserved boat #103 {S | (S  Sailors) ^ (S.rating > 7) ^ (  R  Reserves (R.sid = S.sid ^ R.bid = 103))} Note the use of  to find a tuple in Reserves that `joins with’ the Sailors tuple under consideration. R is bound , S is not

178. 1 7 4 Domain relational calculus Queries have the form {<x 1 ,…,x n >| F(x 1 ,…,x n )} where x 1 ,…, x n are domain variables and F is a formula with free variables {x 1 ,…,x n } Answer : all tuples <v 1 ,…,v n > that make F(v 1 ,…,v n ) true

179. 1 75 Domain Relational Calculus const op X Queries have form: domain variables {<x 1 ,x 2 , …, x n >| p} predicate Predicate: boolean expression over x 1 ,x 2 , …, x n Precise operations depend on the domain and query language – may include special functions, etc. Assume the following at minimum: <x i ,x j ,…>  Rel X op Y X op const

180. 1 7 6 Example Find all sailors with a rating above 7 {<I,N,R,A> | <I,N,R,A>  Sailors  R>7} □ The condition <I,N,R,A>  Sailors ensures that the domain variables are bound to the appropriate fields of the Sailors tuple

181. S um m a r y The relational model has rigorously defined query languages that are simple and powerful. Relational algebra is more operational; useful as internal representation for query evaluation plans. Relational calculus is non-operational, and users define queries in terms of what they want, not in terms of how to compute it. ( Declarativeness .) Several ways of expressing a given query; a query optimizer should choose the most efficient version. Algebra and safe calculus have same expressive power , leading to the notion of relational completeness .