Download
temple university cis dept cis331 principles of database systems n.
Skip this Video
Loading SlideShow in 5 Seconds..
Temple University – CIS Dept. CIS331– Principles of Database Systems PowerPoint Presentation
Download Presentation
Temple University – CIS Dept. CIS331– Principles of Database Systems

Temple University – CIS Dept. CIS331– Principles of Database Systems

108 Vues Download Presentation
Télécharger la présentation

Temple University – CIS Dept. CIS331– Principles of Database Systems

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Temple University – CIS Dept.CIS331– Principles of Database Systems V. Megalooikonomou Relational Model – SQL Part II (based on notes by Silberchatz,Korth, and Sudarshan and notes by C. Faloutsos at CMU)

  2. General Overview - rel. model • Formal query languages • rel algebra and calculi • Commercial query languages • SQL • QBE, (QUEL)

  3. Overview - detailed - SQL • DML • select, from, where, renaming, ordering, • aggregate functions, nested subqueries • insertion, deletion, update • other parts: DDL, embedded SQL, auth etc

  4. CLASS c-id c-name units cis331 d.b. 2 cis321 o.s. 2 TAKES SSN c-id grade 123 cis331 A 234 cis331 B Reminder: our Mini-U db

  5. DML - insertions etc insert into student values (“123”, “smith”, “main”) insert into student(ssn, name, address) values (“123”, “smith”, “main”)

  6. DML - insertions etc bulk insertion: how to insert, say, a table of ‘foreign-student’s, in bulk?

  7. DML - insertions etc bulk insertion: insert into student select ssn, name, address from foreign-student

  8. DML - deletion etc delete the record of ‘smith’

  9. DML - deletion etc delete the record of ‘smith’: delete from student where name=‘smith’ (careful - it deletes ALL the ‘smith’s!)

  10. DML - update etc record the grade ‘A’ for ssn=123 and course cis351 update takes set grade=“A” where ssn=“123” and c-id=“cis351” (will set to “A” ALL such records)

  11. DML - view update consider the db-takes view: create view db-takes as (select * from takes where c-id=“cis351”) view updates are tricky - typically, we can only update views that have no joins, nor aggregates even so, consider changing a c-id to cis333....

  12. DML - joins so far: ‘INNER’ joins, eg: select ssn, c-name from takes, class where takes.c-id = class.c-id

  13. CLASS c-id c-name units cis331 d.b. 2 cis321 o.s. 2 TAKES SSN c-id grade 123 cis331 A 234 cis331 B Reminder: our Mini-U db

  14. CLASS c-id c-name units cis331 d.b. 2 cis321 o.s. 2 TAKES SSN c-id grade 123 cis331 A 234 cis331 B inner join SSN c-name 123 d.b. o.s.: gone! 234 d.b.

  15. CLASS c-id c-name units cis331 d.b. 2 cis321 o.s. 2 TAKES SSN c-id grade 123 cis331 A 234 cis331 B outer join SSN c-name 123 d.b. 234 d.b. null o.s.

  16. outer join select ssn, c-name from takes outer join class on takes.c-id=class.c-id SSN c-name 123 d.b. 234 d.b. null o.s.

  17. outer join • left outer join • right outer join • full outer join • natural join

  18. Overview - detailed - SQL • DML • select, from, where, renaming, ordering, • aggregate functions, nested subqueries • insertion, deletion, update • other parts: DDL, embedded SQL, auth etc

  19. Data Definition Language createtable student (ssn char(9) not null, name char(30), address char(50), primarykey (ssn) )

  20. Data Definition Language createtable r( A1 D1, …, An Dn, integrity-constraint1, … integrity-constraint-n)

  21. Data Definition Language Domains: • char(n), varchar(n) • int, numeric(p,d), real, double precision • float, smallint • date, time

  22. Data Definition Language integrity constraints: • primary key • foreign key • check(P)

  23. Data Definition Language createtable takes (ssn char(9) not null, c-id char(5) not null, grade char(1), primarykey (ssn, c-id), check grade in (“A”, “B”, “C”, “D”, “F”))

  24. Data Definition Language delete a table: difference between droptable student deletefrom student

  25. Data Definition Language modify a table: altertable student drop address altertable student add major char(10)

  26. Overview - detailed - SQL • DML • select, from, where, renaming, ordering, • aggregate functions, nested subqueries • insertion, deletion, update • other parts: DDL, embedded SQL, auth etc

  27. Embedded SQL from within a ‘host’ language (eg., ‘C’, ‘VB’) EXEC SQL <emb. SQL stmnt> END-EXEC Q: why do we need embedded SQL??

  28. Embedded SQL SQL returns sets; host language expects a tuple - impedance mismatch! solution: ‘cursor’, i.e., a ‘pointer’ over the set of tuples example:

  29. Embedded SQL main(){ … EXEC SQL declare c cursorfor select * from student END-EXEC …

  30. Embedded SQL - ctn’d … EXEC SQL open c END-EXEC … while( !sqlerror ){ EXEC SQL fetch c into:cssn, :cname, :cad END-EXEC fprintf( … , cssn, cname, cad); }

  31. Embedded SQL - ctn’d … EXEC SQL close c END-EXEC … } /* end main() */

  32. dynamic SQL • Construct and submit SQL queries at run time • “?”: a place holder for a value provided when it is executed main(){ /* set all grades to user’s input */ … char *sqlcmd=“ update takes set grade = ?”; EXEC SQL prepare dynsql from :sqlcmd ; char inputgrade[5]=“a”; EXEC SQL execute dynsql using :inputgrade; … } /* end main() */

  33. Overview - detailed - SQL • DML • select, from, where, renaming, ordering, • aggregate functions, nested subqueries • insertion, deletion, update • other parts: DDL, embedded SQL, authorization, etc

  34. SQL - misc Later, we’ll see • authorization: grant select on student to <user-id> • transactions • other features (triggers, assertions etc)

  35. General Overview - rel. model • Formal query languages • rel algebra and calculi • Commercial query languages • SQL • QBE, (QUEL)

  36. Rel. model – Introduction to QBE • Inspired by the domain relational calculus • “P.” -> print (ie., ‘select’ of SQL) • _x, _y: domain variables (ie., attribute names) • Example: find names of students taking cis351

  37. Rel. model - QBE CLASS c-id c-name units cis331 d.b. 2 cis321 o.s. 2 TAKES SSN c-id grade 123 cis331 A 234 cis331 B

  38. Rel. model - QBE

  39. Rel. model - QBE names of students taking cis351 SSN c-id grade _x cis351

  40. Rel. model - QBE • condition box • self-joins (Tom’s grandparents) • ordering (AO., DO.) • aggregation (SUM.ALL., COUNT.UNIQUE. , …) • group-by (G.)

  41. Rel. model - QBE aggregate: avg grade overall:

  42. Rel. model - QBE aggregate: avg. grade per student:

  43. General Overview - rel. model • Formal query languages • rel algebra and calculi • Commercial query languages • SQL • QBE, (QUEL)

  44. Rel. model - QUEL Used in INGRES only - of historical interest. Eg.: find all ssn’s in mini-U: rangeof s is student; retrieve (s.ssn);

  45. Rel. model - QUEL general syntax: range of …. ist-name retrieve (attribute list) wherecondition SQL select attr. list from t-name where condition

  46. Rel. model - QUEL • very similar to SQL • also supports aggregates, ordering etc

  47. General Overview • Formal query languages • rel algebra and calculi • Commercial query languages • SQL • QBE, (QUEL) • Integrity constraints • Functional Dependencies • Normalization - ‘good’ DB design