1 / 68

Carnegie Mellon Univ. Dept. of Computer Science 15-415 - Database Applications

Carnegie Mellon Univ. Dept. of Computer Science 15-415 - Database Applications. C. Faloutsos Introduction. Outline. Introduction to DBMSs The Entity Relationship model The Relational Model SQL: the commercial query language DB design: FD, 3NF, BCNF indexing, q-opt

ron
Download Presentation

Carnegie Mellon Univ. Dept. of Computer Science 15-415 - Database Applications

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. Carnegie Mellon Univ.Dept. of Computer Science15-415 - Database Applications C. Faloutsos Introduction

  2. Outline • Introduction to DBMSs • The Entity Relationship model • The Relational Model • SQL: the commercial query language • DB design: FD, 3NF, BCNF • indexing, q-opt • concurrency control & recovery • advanced topics (data mining, multimedia) 15-415 - C. Faloutsos

  3. We’ll learn: • What are RDBMS • when to use them • how to model data with them • how to store and retrieve information • how to search quickly for information • Internals of an RDBMS: indexing, transactions 15-415 - C. Faloutsos

  4. We’ll learn (cnt’d) • Advanced topics • multimedia indexing (how to find similar, eg., images) • data mining (how to find patterns in data) 15-415 - C. Faloutsos

  5. Detailed outline • Introduction • Motivating example • How do DBMSs work? DDL, DML, views. • Fundamental concepts • DBMS users • Overall system architecture • Conclusions 15-415 - C. Faloutsos

  6. What is the goal of rel. DBMSs Electronic record-keeping: Fast and convenient access to information. 15-415 - C. Faloutsos

  7. Definitions • ‘DBMS’ = ‘Data Base Management System’: the (commercial) system, like: DB2, Oracle, MS SQL-server, ... • ‘Database system’: DBMS + data + application programs 15-415 - C. Faloutsos

  8. Motivating example Eg.: students, taking classes, obtaining grades; • find my gpa • <and other ad-hoc queries> 15-415 - C. Faloutsos

  9. Obvious solution: paper-based • advantages? • disadvantages? eg., student folders, alpha sorted 15-415 - C. Faloutsos

  10. Obvious solution: paper-based • advantages? • cheap; easy to use • disadvantages? eg., student folders, alpha sorted 15-415 - C. Faloutsos

  11. Obvious solution: paper-based • advantages? • cheap; easy to use • disadvantages? • no ‘ad hoc’ queries • no sharing • large physical foot-print 15-415 - C. Faloutsos

  12. Next obvious solution • computer-based (flat) files + • C (Java, ...) programs to access them e.g., one (or more) UNIX/DOS files, with student records and their courses 15-415 - C. Faloutsos

  13. Next obvious solution your layout for the student records? 15-415 - C. Faloutsos

  14. Next obvious solution your layout for the student records? (eg., comma-separated values ‘csv’ Smith,John,123,db,A,os,B Tompson,Peter,234 Atkinson,Mary,345,os,B,graphics,A 15-415 - C. Faloutsos

  15. Next obvious solution your layout for the student records? (many other layouts are fine, eg.: Smith,John,123 Tompson,Peter,234 Atkinson,Mary,345 123,db,A 123,os,B 345,os,B 345,graphics,A 15-415 - C. Faloutsos

  16. Problems? • inconvenient access to data (need ‘C++’ expertize, plus knowledge of file-layout) • data isolation • data redundancy (and inconcistencies) • integrity problems • atomicity problems 15-415 - C. Faloutsos

  17. Problems? (cont’d) • ... • concurrent-access anomalies • security problems 15-415 - C. Faloutsos

  18. Problems? (cont’d) [ why? because of two main reasons: • file-layout description is buried within the C programs and • there is no support for transactions (concurrency and recovery) ] DBMSs handle exactly these two problems 15-415 - C. Faloutsos

  19. DBMS solution • commercial/freeware DBMS & • application programs 15-415 - C. Faloutsos

  20. Main vendors/products Open source Postgres (UCB) mySQL, mSQL miniBase (Wisc) Predator (Cornell) (www.acm.org/sigmod) Commercial • Oracle • IBM/DB2 • MS SQL-server • Sybase • Informix • (MS Access, • ...) 15-415 - C. Faloutsos

  21. <Live demo with MS Access> • a simple query; ‘views’ • a join • an aggregate query demo 15-415 - C. Faloutsos

  22. Detailed outline • Introduction • Motivating example • How do DBMSs work? DDL, DML, views. • Fundamental concepts • DBMS users • Overall system architecture • Conclusions 15-415 - C. Faloutsos

  23. How do DBs work? select * from student Pictorially: DBMS and meta-data = catalog = data dictionary data 15-415 - C. Faloutsos

  24. How do DBs work? /mydb %isql mydb sql>create table student ( ssn fixed; name char(20) ); 15-415 - C. Faloutsos

  25. How do DBs work? sql>insert into student values (123, “Smith”); sql>select * from student; 15-415 - C. Faloutsos

  26. How do DBs work? sql>create table takes ( ssn fixed, c-id char(5), grade fixed)); 15-415 - C. Faloutsos

  27. How do DBs work - cont’d More than one tables - joins Eg., roster (names only) for ‘db’ 15-415 - C. Faloutsos

  28. How do DBs work - cont’d sql> select name from student, takes where student.ssn = takes.ssn and takes.c-id = ‘db’ 15-415 - C. Faloutsos

  29. Views - a powerful tool! what and why? • suppose secy is allowed to see only ssn’s and GPAs, but not individual grades • -> VIEWS! 15-415 - C. Faloutsos

  30. Views sql> create view fellowship as ( select ssn, avg(grade) from takes group by ssn); 15-415 - C. Faloutsos

  31. Views Views = ‘virtual tables’ 15-415 - C. Faloutsos

  32. Views sql> select * from fellowship; 15-415 - C. Faloutsos

  33. Views sql> grant select on fellowship to secy; 15-415 - C. Faloutsos

  34. Iterating: advantages over (flat) files • logical and physical data independence, because data layout, security etc info: stored explicitly on the disk • concurrent access and transaction support 15-415 - C. Faloutsos

  35. Disadvantages over (flat) files? 15-415 - C. Faloutsos

  36. Disadvantages over (flat) files • Price • additional expertise (SQL/DBA) (hence: over-kill for small, single-user data sets) 15-415 - C. Faloutsos

  37. Detailed outline • Introduction • Motivating example • How do DBMSs work? DDL, DML, views. • Fundamental concepts • DBMS users • Overall system architecture • Conclusions 15-415 - C. Faloutsos

  38. Fundamental concepts • 3-level architecture • logical data independence • physical data independence 15-415 - C. Faloutsos

  39. 3-level architecture v2 v1 v3 • view level • logical level • physical level 15-415 - C. Faloutsos

  40. 3-level architecture • view level • logical level: eg., tables • STUDENT(ssn, name) • TAKES (ssn, c-id, grade) • physical level: • how are these tables stored, how many bytes / attribute etc 15-415 - C. Faloutsos

  41. 3-level architecture • view level, eg: • v1: select ssn from student • v2: select ssn, c-id from takes • logical level • physical level 15-415 - C. Faloutsos

  42. 3-level architecture • -> hence, physical and logical data independence: • logical D.I.: • ??? • physical D.I.: • ??? 15-415 - C. Faloutsos

  43. 3-level architecture • -> hence, physical and logical data independence: • logical D.I.: • can add (drop) column; add/drop table • physical D.I.: • can add index; change record order 15-415 - C. Faloutsos

  44. Detailed outline • Introduction • Motivating example • How do DBMSs work? DDL, DML, views. • Fundamental concepts • DBMS users • Overall system architecture • Conclusions 15-415 - C. Faloutsos

  45. Database users • ‘naive’ users • casual users • application programmers • [ DBA (Data base administrator)] 15-415 - C. Faloutsos

  46. casual users select * from student DBMS and meta-data = catalog data 15-415 - C. Faloutsos

  47. naive users Pictorially: app. (eg., report generator) DBMS and meta-data = catalog data 15-415 - C. Faloutsos

  48. App. programmers • those who write the applications (like the ‘report generator’) 15-415 - C. Faloutsos

  49. DB Administrator (DBA) • schema definition (‘logical’ level) • physical schema (storage structure, access methods • schemas modifications • granting authorizations • integrity constraint specification 15-415 - C. Faloutsos

  50. Detailed outline • Introduction • Motivating example • How do DBMSs work? DDL, DML, views. • Fundamental concepts • DBMS users • Overall system architecture • Conclusions 15-415 - C. Faloutsos

More Related