1. Relational data model Concepts

2. Relational data model • Represent database as collection of relations. • In relational model row is known as tuple, column header known as attribute, and table known as relation.

3. Relation Schema Account = (account-number, branch-name, balance) Degree of relation=3 <no. of attribute> Tuple t1= < A-101, Downtown, 500> t2= < A-102, Perryridge, 400> …………… ……………

4. Relational Database Student = (Roll_no, name, age, contact, address) Course = ( course_id, course_name, course_coordinator) Subject =(Subject_id, sub_name, subject_credit) University = (Student, Course, Subject, …..)

5. Relational data model

6. Relational data model • Domain : Set of all possible values • Domain of account-number : set of all account number • Domain of branch name : set of all branch name

7. Domain • Atomic domain: Elements of the domain are indivisible units. • domain=(rakesh, mahesh, raj) • Domain=(rakeshkumar, maheshverma, raj mittal)

8. Relational data model Table Relation Record Tuple

9. Null values • Customer=(cus_id, name, address, contact_no) • Sandeep does not have any telephone. null

10. Null values • Customer=(cus_id, name, address, contact_no) • Address of customer not known at time of record entry. null Null values signify value is unknown or does not exists.

11. Super Key • Super key: set of one and more attributes that, taken collectively allow uniquely identify tuple in the relation. <Roll No> <Roll No, Student_name> <Roll no, Branch> <Roll no, student_name, branch> <Roll no, Student_name, Branch, address> Student_name, address>

12. Candidate key • Candidate key: Super key whose proper subset are not super key or minimal super key. <Roll No> <Roll No, Student_name> subset <Roll no> <Student name> <Roll no, Branch> subset < Roll no> <Branch> <Roll no, student_name, branch> <Roll no> < student_name> <branch> <Roll no, Student_name, Branch, address> <Student_name, address> = <Student_name> < Address> X X X X

13. key • R=<A1,A2,A3,…> • t=<v1,v2,v3,….> • If K is primary key of relation R then for any t1,t2 • t1[K] =/= t[K]

14. Constraints • Domain constraints • Key constraints • Entity integrity constraints • Referential integrity constraints

15. Domain Constraint • Value of attribute A must be atomic value from domain. • Domain=(rakesh, mahesh, sanjay) • Domain=(rakeshverma, maheshsingh, sanjaymittal)

16. Relational database 5.5

17. Entity Integrity Constraint • No primary key value can be null. • Having null value implies, some tuples can’t be uniquely identified.

18. Foreign key • Primary key of a relation used in another relation then that key is known as foreign key. Faculty Dept

19. Referential Integrity Constraints • Domain of FK and PK must be same • Values in FK must be exist in PK or null.

20. 5.7

21. Insert operation • Insert <null, manoj, noida, 8099889809, M.tech> into faculty Faculty Entity integrity constraints

22. Insert operation • Insert <4321, manoj, noida, 8099889809, M.tech> into faculty • Insert <4321, rakesh, delhi, 999889809, M.tech> into faculty Faculty Key constraints

23. Insert operation Insert <D102, 120124, EE> into Dept Dept Referential integrity constraints

24. Delete operation delete employee with id 120122 from faculty Faculty Dept Referential integrity constraints

25. Update operation Update id of faculty with id=120122 to id=120111 Faculty Dept Referential integrity constraints

26. Relational Algebra • Basic set of relational model operation. How to represent operation of relation. Example : if data of employee Amit is required then how to write this query

27. Relational Algebra(Select) • Basic set of relational model operation. Faculty All data is required σ ( Faculty )

28. Relational Algebra(Select) • Basic set of relational model operation. Faculty Record of kapil is required σname=kapil( Faculty )

29. Relational Algebra • Basic set of relational model operation. Faculty Record of faculties are required whose qualification is M.tech σqualification=M.tech( Faculty )

30. Relational Algebra Record of faculties are required whose qualification is M.tech and who live in noida σqualification=M.tech ^ address=noida( Faculty )

31. Relational Algebra Name of all faculties required π name( Faculty )

32. Relational Algebra Name of all faculties and their address required π name, address( Faculty )

33. Relational Algebra name of faculties are required whose qualification is M.tech π name(σqualification=M.tech( Faculty ))

34. Relational Algebra(union) New faculty name of all faculties are required π name ( Faculty ) Uπ name ( New_Faculty )

35. Relational Algebra(Set difference) Rfaculy ( faculty who left ) name of all faculties are required π name ( Faculty ) -π name ( RFaculty )

36. Relational Algebra(Cross product) Dept Emp Dept X Emp σDept.dept_id=emp.dept_id(Dept X Emp)

37. Assertions • An assertion is a predicate expressing a condition that we wish the database always to satisfy. • An assertion in SQL takes the form create assertion <assertion-name> check <predicate> • When an assertion is made, the system tests it for validity, and tests it again on every update that may violate the assertion • This testing may introduce a significant amount of overhead; hence assertions should be used with great care. • Asserting for all X, P(X) is achieved in a round-about fashion using not exists X such that not P(X)

38. Assertion Example • The amount of every account never less than 1000 Create assertion balance_constraint check(amount>1000)

39. Triggers • A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database. • To design a trigger mechanism, we must: • Specify the conditions under which the trigger is to be executed. • Specify the actions to be taken when the trigger executes.

40. Trigger Example • Suppose that instead of allowing negative account balances, the bank deals with overdrafts by • setting the account balance to zero • creating a loan in the amount of the overdraft • giving this loan a loan number identical to the account number of the overdrawn account • The condition for executing the trigger is an update to the account relation that results in a negative balance value.

41. Disadvantage • . Simple validations can be done at the application level itself . • Triggers executes invisibly from client-application which connects to the database server.So it is difficult to figure out what happens at the database layer. • Triggers runs on every update made to the table therefore it adds more load to the database and cause the system to run slow.

42. SQL Basic Structure Set Operations Aggregate Functions Null Values Nested Subqueries Derived Relations Views Modification of the Database Joined Relations Data Definition Language Embedded SQL, ODBC and JDBC

43. Create table • Create table faculty ( Employee_id number primary key, Name varchar(20), Address varchar(100), Contact number(10), Qualification varchar(5) )

44. Create table • Create table dept ( Dept_id number primary key, Hod number, dept_namevarchar(20), foreign key(Hod) references faculty(Employee_id) )

45. Delete operation delete employee with id 120122 from faculty Faculty Dept

46. Create table • Create table dept ( Dept_id number primary key, Hod number, dept_namevarchar(20), foreign key(Hod) references faculty(Employee_id) on delete cascade )

47. Delete operation delete employee with id 120122 from faculty Faculty Dept

48. Insert command • Insert into faculty (Employee_id, Name, Address, Contact, Qualification) values (12012, ‘Kapil’, ‘Noida’, 8739748931,’Mtech’)

49. Delete command • Delete from faculty where Name=‘Amit’; • Delete from faculty;

50. Truncate • Used to delete all rows of table • Syntax: • Truncate table faculty;