1 / 47

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

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). General Overview - rel. model. Formal query languages rel algebra and calculi

feryal
Download Presentation

Temple University – CIS Dept. CIS331– Principles of Database 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. 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

More Related