1 / 43

CS634 Information Systems

IIT BOMBAY. CS634 Information Systems. Dr Deepak B Phatak Subrao Nilekani Chair Professor Kanwal Rekhi Building, Department of CSE IIT Bombay Session 6 SQL, DML-DDL. Session overview. Review of SQL Additional features of DML More on DDL Definition of constraints Assertions

lars
Download Presentation

CS634 Information Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IIT BOMBAY CS634 Information Systems Dr Deepak B Phatak Subrao Nilekani Chair Professor Kanwal Rekhi Building, Department of CSE IIT Bombay Session 6 SQL, DML-DDL

  2. Session overview • Review of SQL • Additional features of DML • More on DDL • Definition of constraints • Assertions • Course projects CS640-Session 6, SQL-DML-DDL

  3. Predicates • Simple comparison predicate : • <value expression> <operator> <value expression> • Where <operator> is one of the following • {=, < >, <, <=, >, >=} • example • 12 * sstipend < 120000 CS640-Session 6, SQL-DML-DDL

  4. Logical combination • Predicates evaluate to true, false or unknown • Simple predicates can be combined using ‘and’, ‘not’ and ‘or’ to produce complex predicates CS640-Session 6, SQL-DML-DDL

  5. Null values and three valued logic • Any comparison with null returns unknown • 5 < null • Or null <> null • Or null = null • Three-valued logic using the truth value unknown (apart from true, false) CS640-Session 6, SQL-DML-DDL

  6. Null values and three valued logic • Or • unknownortrue = true, • unknownorfalse = unknown • Unknown or unknown = unknown • And • True and unknown = unknown • False and unknown = false • Unknown and unknown = unknown CS640-Session 6, SQL-DML-DDL

  7. Null values and three valued logic • Not • Not unknown = unknown • “P is unknown” evaluates to true if predicate p evaluates to unknown • Result of where clause predicate is treated as false if it evaluates to unknown CS640-Session 6, SQL-DML-DDL

  8. Between • Val between value2 and value3 • Val >= value2 and val < = value3 • Select * • From student • Where sdob • between ‘1988-01-01’ and ‘1990-01-01’; • Val not between value2 and value3 • Not (val between value2 and value3) CS640-Session 6, SQL-DML-DDL

  9. Null predicate • Column name is null • Column name is not null • (a,b) is null • True only if both are null • (a,b) is not null • True only if both are not null CS640-Session 6, SQL-DML-DDL

  10. Like predicate • Get all names starting with ‘p’ • Select * from student • Where sname like ‘p%’; • % (Percent symbol) • Matches 0 or more characters • _(Underscore) • Matches exactly one character $ is used as an ‘escape’ character when needed CS640-Session 6, SQL-DML-DDL

  11. Like predicate Pattern would match but not ‘_abc%’ ‘xabc’ ‘abc’ • ‘Xabcdefg’ ‘xyabcdefg’ ‘%8$%’ ‘8%’ ‘15%’ • ‘18%’ ’80%’ • ‘rate 18%’ CS640-Session 6, SQL-DML-DDL

  12. Group by clause • Whenever we need to summarize data from a table on the basis of some groups • Find hostel-wise average cpi of students Select shostel, avg (scpi) From student Group by shostel; CS640-Session 6, SQL-DML-DDL

  13. Group by clause (continued) • SQL first selects all rows which meet the predicate • The rows are then grouped on the basis of the grouping attribute (s) • Aggregate function is calculated for rows of each group • All attributes in the select clause (other than aggregate function) must appear in the ‘group by’ clause CS640-Session 6, SQL-DML-DDL

  14. Set comparison • Find all students that have greater cpi than cpi of some student in hostel 8 select sname from studentwhere scpi > some (select scpifrom studentwhere shostel = 8); CS640-Session 6, SQL-DML-DDL

  15. 0 5 6 Definition of ‘some’ clause (5< some ) = true (read: 5 < some row in the table) 0 ) = false (5< some 5 0 ) = true (5 = some 5 0 (5 some ) = true (since 0  5) 5 (= some)  in However, ( some)  not in CS640-Session 6, SQL-DML-DDL

  16. Example of ‘all’ clause • Find all students who have cpi greater than cpi of all students in hostel 8 select sname from studentwhere scpi > all (select scpifrom studentwhere shostel = 8); CS640-Session 6, SQL-DML-DDL

  17. 0 5 6 Definition of all clause (5< all ) = false 6 ) = true (5< all 10 4 ) = false (5 = all 5 4 (5 all ) = true (since 5  4 and 5  6) 6 (all)  not in However, (= all)  in CS640-Session 6, SQL-DML-DDL

  18. In predicate • Value in (value1, value2, . . .) • Extract names of students with ‘AA’, ‘AB’, or ‘BB’ grades in cs634 • Select student.sname • From student natural join reg • Where reg.Ccode = ‘cs634’ and • reg.Grade in (‘AA’, ‘AB’, ‘BB’); CS640-Session 6, SQL-DML-DDL

  19. Sub-queries in predicates • Find names of students whose cpi is less than the average cpi of the students of hostel 8. • Select sname from student where • scpi < (select avg(scpi) from student • Where shostel =8); CS640-Session 6, SQL-DML-DDL

  20. Select sname, scpi • From student natural join reg • Where grade = ‘AA’ • And ccredit > 8 • And scpi < (select avg (scpi) • From student • Where shostel = 8) • Order by scpi desc; CS640-Session 6, SQL-DML-DDL

  21. More on DDL • Constraints, assertions • Conditions that must be satisfied at all times by the data in tables • Integrity constraints • Basic data integrity • Referential integrity CS640-Session 6, SQL-DML-DDL

  22. Examples of data integrity • Ensure that data in the attributes sh, scpi, and grade is always within the following prescribed value limits (Domains) • 1 <= sh <=13 • Scpi <= 10 • Grade in (‘AA’, ‘AB’, ‘BB’, ‘BC’ ‘CC’, ‘CD’, ‘DD’, ‘EE’, ‘FR’, ‘XX’, ‘P’, ‘NP’, ‘II’, ‘AU’) CS640-Session 6, SQL-DML-DDL

  23. Referential integrity sroll ccode ----- ----- 89005012 CS413 89005012 CS634 89007017 CS634 89007017 CS413 89004039 HS412 ---- ----- sroll sname 89005012 Sunita Sarawagi 89007017 Kesav Nori 89004039 Muthukrishnan Student Reg CS640-Session 6, SQL-DML-DDL

  24. Referential integrity • Group of columns that refer to some other table, • Called ‘foreign’ key • Refers to a primary key or to a candidate key in other table • What happens when changes occur due to insert/delete/update which affect proper referencing CS640-Session 6, SQL-DML-DDL

  25. Referential integrity(Nonexistent sroll in Reg) sroll ccode ----- ----- 89005012 CS413 89005012 CS634 89005013 HS412 89007017 CS634 89007017 CS413 89004039 HS412 ---- ----- sroll sname 89005012 Sunita Sarawagi 89007017 Kesav Nori 89004039 Muthukrishnan Student Reg ? CS640-Session 6, SQL-DML-DDL

  26. Referential integrity(A student row is deleted) sroll ccode ----- ----- 89005012 CS413 89005012 IT640 89007017 IT640 89007017 CS413 89004039 HS412 ---- ----- sroll sname 89005012 Sunita Sarawagi 89007017 Kesav Nori 89004039 Muthukrishnan Student Reg CS640-Session 6, SQL-DML-DDL

  27. Operational problems • Insertion • What if an entry in reg does not have the corresponding student in student • Deletion • What if a record is deleted in student while corresponding registration records exist in reg • Update : similar issue CS640-Session 6, SQL-DML-DDL

  28. Enforcing constraints • Enforcing constraints • Application DBMS • Programs schema definition • A programmer may forget to write necessary code in the program to check the constraint and take appropriate action CS640-Session 6, SQL-DML-DDL

  29. Column and table constraints • Not null • Create table student( • Sroll char(8), • Sname varchar (30) not null, • … • … • ); CS640-Session 6, SQL-DML-DDL

  30. Column and table constraints • Unique : to specify candidate keys • Create table student( • Sroll char(8) unique, • . . . • . . . CS640-Session 6, SQL-DML-DDL

  31. Column and table constraints • Another way of specifying • Create table student • (Sroll char(8), • : • Unique sroll • ); CS640-Session 6, SQL-DML-DDL

  32. Check constraint • Check (search condition) • Create table faculty( • . . . • fsalary numeric (9,2) • Check (fsalary < 100000), • . . . • ); CS640-Session 6, SQL-DML-DDL

  33. Constraint names • What happens if insert or update operation violates the constraint ? • SQL engine gives an error message like constraint c_156a29xx38p violated • Error message does not tell us much • Constraint name helps in debugging if constraint violations occur CS640-Session 6, SQL-DML-DDL

  34. List of values • Create table reg( • . . . • grade char(2), • . . . • Constraint grade_value_violation • Check (grade in (‘AA’, ‘AB’, ‘BB’, ‘BC’ ‘CC’, ‘CD’, ‘DD’, ‘EE’, ‘FF’, ‘XX’, ‘P’, ‘NP’, ‘II’, ‘AU’) • ); CS640-Session 6, SQL-DML-DDL

  35. Primary key • Chosen candidate key • Create table student( • sroll integer primary key, • . . . • . . . • ); CS640-Session 6, SQL-DML-DDL

  36. Primary key • Create table student( • . . . • Sname character varying (30), • Shostel integer, • Sroom integer, • . . . Constraint student_pk primary key • (shostel, sroom) • ); CS640-Session 6, SQL-DML-DDL

  37. Foreign key example • Create table dept • (Dno integer primary key, • . . . • . . . • ); CS640-Session 6, SQL-DML-DDL

  38. Foreign key example • Create table faculty • ( fno integer primary key • dno integer, • . . . • Constraint faculty_fk • Foreign key (dno) • references dept (dno) • ); Modern Information Systems(S6)CS640-Session 6, SQL-DML-DDL

  39. Referential constraint actions • On violation • Normal behaviour is an error message, but SQL can update other tables, if so prescribed • Set default: • Create table faculty ( • … • dno integer default 0 • References dept • On delete set default, • ); CS640-Session 6, SQL-DML-DDL

  40. Referential constraint actions • Set null • Same as above, say ‘on delete set null’ • Cascade • Update cascade • Delete cascade CS640-Session 6, SQL-DML-DDL

  41. Assertions • Restrictions involving multiple tables • More natural to express two table constraints separately CS640-Session 6, SQL-DML-DDL

  42. Assertions • Create assertion faculty_student_ratio • Check ( • ( select 4*count(*) from faculty) >= • (select count (*) from students) ); CS640-Session 6, SQL-DML-DDL

  43. Assertions • Table constraints • Are required to be true • If and only if • There is data in the table CS640-Session 6, SQL-DML-DDL

More Related