Database Applications: Introduction and Advanced Topics

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