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

▶
carnegie mellon univ dept of computer science 15 415
SMART_READER_LITE
LIVE PREVIEW

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

Sep 27, 2023 12 likes •272 views

Faloutsos CMU SCS 15-415 CMU SCS Carnegie Mellon Univ. Dept. of Computer Science 15-415 - Database Applications C. Faloutsos Lecture#1: Introduction CMU SCS Outline Introduction to DBMSs The Entity Relationship model The

slide-1
SLIDE 1

Faloutsos CMU SCS 15-415 1

CMU SCS

Carnegie Mellon Univ.

  • Dept. of Computer Science

15-415 - Database Applications

  • C. Faloutsos

Lecture#1: Introduction

CMU SCS

Faloutsos CMU SCS 15-415 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)

CMU SCS

Faloutsos CMU SCS 15-415 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

slide-2
SLIDE 2

Faloutsos CMU SCS 15-415 2

CMU SCS

Faloutsos CMU SCS 15-415 4

We’ll learn (cnt’d)

  • Advanced topics

– multimedia indexing (how to find similar, eg., images) – data mining (how to find patterns in data)

CMU SCS

Administrivia

  • Weights: as announced

Faloutsos CMU SCS 15-415 5

Course grade MT ASGN Final exam 30% 30% 40% ASGN1 ASGN8 … 5% 5% Sum= 100%

CMU SCS

Administrivia - II

  • FYI: ASGN3 and ASGN7 are heavy
  • Late policy: 4 ‘slip days’
  • Exams: no aids allowed, except

– 1 page with your notes (both sides) for MT – 2 such pages for Final

Faloutsos CMU SCS 15-415 6

slide-3
SLIDE 3

Faloutsos CMU SCS 15-415 3

CMU SCS

Faloutsos CMU SCS 15-415 7

Detailed outline

  • Introduction

– Motivating example – How do DBMSs work? DDL, DML, views. – Fundamental concepts – DBMS users – Overall system architecture – Conclusions

CMU SCS

Faloutsos CMU SCS 15-415 8

What is the goal of rel. DBMSs

CMU SCS

Faloutsos CMU SCS 15-415 9

What is the goal of rel. DBMSs

Electronic record-keeping: Fast and convenient access to information.

slide-4
SLIDE 4

Faloutsos CMU SCS 15-415 4

CMU SCS

Faloutsos CMU SCS 15-415 10

Definitions

  • ‘DBMS’ = ‘Data Base Management

System’:

the (commercial) system, like: DB2, Oracle, MS SQL-server, ...

  • ‘Database system’: DBMS + data +

application programs

CMU SCS

Faloutsos CMU SCS 15-415 11

Motivating example

Eg.: students, taking classes, obtaining grades;

  • find my gpa
  • <and other ad-hoc queries>

CMU SCS

Faloutsos CMU SCS 15-415 12

Obvious solution: paper-based

  • advantages?
  • disadvantages?

eg., student folders, alpha sorted

slide-5
SLIDE 5

Faloutsos CMU SCS 15-415 5

CMU SCS

Faloutsos CMU SCS 15-415 13

Obvious solution: paper-based

  • advantages?

– cheap; easy to use

  • disadvantages?

eg., student folders, alpha sorted

CMU SCS

Faloutsos CMU SCS 15-415 14

Obvious solution: paper-based

  • advantages?

– cheap; easy to use

  • disadvantages?

– no ‘ad hoc’ queries – no sharing – large physical foot-print

CMU SCS

Faloutsos CMU SCS 15-415 15

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

slide-6
SLIDE 6

Faloutsos CMU SCS 15-415 6

CMU SCS

Faloutsos CMU SCS 15-415 16

Next obvious solution

your layout for the student records?

CMU SCS

Faloutsos CMU SCS 15-415 17

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

CMU SCS

Faloutsos CMU SCS 15-415 18

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

slide-7
SLIDE 7

Faloutsos CMU SCS 15-415 7

CMU SCS

Faloutsos CMU SCS 15-415 19

Problems?

CMU SCS

Faloutsos CMU SCS 15-415 20

Problems?

  • inconvenient access to data (need ‘C++’

expertize, plus knowledge of file-layout)

– data isolation

  • data redundancy (and inconcistencies)
  • integrity problems
  • atomicity problems

CMU SCS

Faloutsos CMU SCS 15-415 21

Problems? (cont’d)

  • ...
  • concurrent-access anomalies
  • security problems
slide-8
SLIDE 8

Faloutsos CMU SCS 15-415 8

CMU SCS

Faloutsos CMU SCS 15-415 22

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

CMU SCS

Faloutsos CMU SCS 15-415 23

DBMS solution

  • commercial/freeware DBMS &
  • application programs

CMU SCS

Faloutsos CMU SCS 15-415 24

Main vendors/products

Commercial

  • Oracle
  • IBM/DB2
  • MS SQL-server
  • Sybase
  • Informix/IBM
  • (MS Access,
  • ...)

Open source Postgres (UCB) mySQL, mSQL miniBase (Wisc) Predator (Cornell) sqlite (sqlite.org) (www.acm.org/sigmod)

slide-9
SLIDE 9

Faloutsos CMU SCS 15-415 9

CMU SCS

Faloutsos CMU SCS 15-415 25

<Demo with sqlite3>

  • Insert ‘student’ and ‘takes’ records
  • Find the ‘os’ class roster
  • Find the GPA of ‘Smith’

www.cs.cmu.edu/~christos/courses/dbms.S12/files/sqldemo.zip

CMU SCS

Faloutsos CMU SCS 15-415 26

Detailed outline

  • Introduction

– Motivating example – How do DBMSs work? DDL, DML, views. – Fundamental concepts – DBMS users – Overall system architecture – Conclusions

CMU SCS

Faloutsos CMU SCS 15-415 27

How do DBs work?

Pictorially:

DBMS data and meta-data = catalog = data dictionary

select * from student

slide-10
SLIDE 10

Faloutsos CMU SCS 15-415 10

CMU SCS

Faloutsos CMU SCS 15-415 28

How do DBs work?

% sqlite3 miniu.sql sqlite>create table student ( ssn fixed; name char(20) );

Smith,John, 123,db,A,os,B Tompson,Peter,234 Atkinson,Mary,345,os,B,graphics,A

CMU SCS

Faloutsos CMU SCS 15-415 29

How do DBs work?

% sqlite3 miniu.sql sqlite>create table student ( ssn fixed; name char(20) );

Smith, 123,db,A,os,B Tompson, 234 Atkinson, 345,os,B,graphics,A

CMU SCS

Faloutsos CMU SCS 15-415 30

How do DBs work?

sqlite>insert into student values (123, “Smith”); sqlite>select * from student;

slide-11
SLIDE 11

Faloutsos CMU SCS 15-415 11

CMU SCS

Faloutsos CMU SCS 15-415 31

create table student (ssn fixed, name char(20)); insert into student values(123, "Smith"); insert into student values(234, "Tompson"); insert into student values(345, "Atkinson");

  • - see what we have inserted

select * from student; ssn name

  • --------- ----------

123 Smith 234 Tompson 345 Atkinson

CMU SCS

Faloutsos CMU SCS 15-415 32

How do DBs work?

sqlite>create table takes ( ssn fixed, cid char(10), grade fixed));

CMU SCS

Faloutsos CMU SCS 15-415 33

  • - register students in classes and give them grades

drop table if exists takes; create table takes (ssn fixed, cid char(10), grade fixed); insert into takes values( 123, "db", 4); insert into takes values( 123, "os", 3); insert into takes values( 345, "os", 3); insert into takes values( 345, "graphics", 4);

Smith,John,123,db,A,os,B Tompson,Peter,234 Atkinson,Mary,345,os,B,graphics,A

slide-12
SLIDE 12

Faloutsos CMU SCS 15-415 12

CMU SCS

Faloutsos CMU SCS 15-415 34

  • - see what we inserted

select * from takes; ssn cid grade

  • --------- ---------- ----------

123 db 4 123 os 3 345 os 3 345 graphics 4

Smith,John,123,db,A,os,B Tompson,Peter,234 Atkinson,Mary,345,os,B,graphics,A

CMU SCS

Faloutsos CMU SCS 15-415 35

How do DBs work - cont’d

More than one tables - joins Eg., roster (names only) for ‘os’

CMU SCS

Faloutsos CMU SCS 15-415 36

How do DBs work - cont’d

sqlite> select name from student, takes where student.ssn = takes.ssn and takes.c-id = ‘os’

slide-13
SLIDE 13

Faloutsos CMU SCS 15-415 13

CMU SCS

Faloutsos CMU SCS 15-415 37

  • - find the os class roster

select name from student, takes where student.ssn = takes.ssn and cid="os"; name

  • Smith

Atkinson

Smith,John,123,db,A,os,B Tompson,Peter,234 Atkinson,Mary,345,os,B,graphics,A

CMU SCS

Faloutsos CMU SCS 15-415 38

Views - a powerful tool!

what and why?

  • suppose secy is allowed to see only ssn’s

and GPAs, but not individual grades

  • -> VIEWS!

CMU SCS

Faloutsos CMU SCS 15-415 39

Views

sqlite> create view fellowship as ( select ssn, avg(grade) from takes group by ssn);

takes ssn cid grade 123 db 4 123

  • s

3 345

  • s

3 345 graphics 4

slide-14
SLIDE 14

Faloutsos CMU SCS 15-415 14

CMU SCS

Faloutsos CMU SCS 15-415 40

Views

Views = ‘virtual tables’

CMU SCS

Faloutsos CMU SCS 15-415 41

Views

sqlite> select * from fellowship;

takes ssn cid grade 123 db 4 123

  • s

3 345

  • s

3 345 graphics 4

CMU SCS

Faloutsos CMU SCS 15-415 42

Views

sql> grant select on fellowship to secy;

(‘grant’ not supported in sqlite)

takes ssn cid grade 123 db 4 123

  • s

3 345

  • s

3 345 graphics 4

slide-15
SLIDE 15

Faloutsos CMU SCS 15-415 15

CMU SCS

Faloutsos CMU SCS 15-415 43

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

CMU SCS

Faloutsos CMU SCS 15-415 44

Disadvantages over (flat) files?

CMU SCS

Faloutsos CMU SCS 15-415 45

Disadvantages over (flat) files

  • Price
  • additional expertise (SQL/DBA)

(hence: over-kill for small, single-user data sets But: mobile phones (eg., android) use sqlite; some versions of firefox do, too)

slide-16
SLIDE 16

Faloutsos CMU SCS 15-415 16

CMU SCS

Faloutsos CMU SCS 15-415 46

Detailed outline

  • Introduction

– Motivating example – How do DBMSs work? DDL, DML, views. – Fundamental concepts – DBMS users – Overall system architecture – Conclusions

CMU SCS

Faloutsos CMU SCS 15-415 47

Fundamental concepts

  • 3-level architecture
  • logical data independence
  • physical data independence

CMU SCS

Faloutsos CMU SCS 15-415 48

3-level architecture

  • view level
  • logical level
  • physical level

v1 v2 v3

slide-17
SLIDE 17

Faloutsos CMU SCS 15-415 17

CMU SCS

Faloutsos CMU SCS 15-415 49

3-level architecture

  • view level
  • logical level: eg., tables

– STUDENT(ssn, name) – TAKES (ssn, cid, grade)

  • physical level:

– how are these tables stored, how many bytes / attribute etc

CMU SCS

Faloutsos CMU SCS 15-415 50

3-level architecture

  • view level, eg:

– v1: select ssn from student – v2: select ssn, c-id from takes

  • logical level
  • physical level

CMU SCS

Faloutsos CMU SCS 15-415 51

3-level architecture

  • -> hence, physical and logical data

independence:

  • logical D.I.:

– ???

  • physical D.I.:

– ???

slide-18
SLIDE 18

Faloutsos CMU SCS 15-415 18

CMU SCS

Faloutsos CMU SCS 15-415 52

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

CMU SCS

Faloutsos CMU SCS 15-415 53

Detailed outline

  • Introduction

– Motivating example – How do DBMSs work? DDL, DML, views. – Fundamental concepts – DBMS users – Overall system architecture – Conclusions

CMU SCS

Faloutsos CMU SCS 15-415 54

Database users

  • ‘naive’ users
  • casual users
  • application programmers
  • [ DBA (Data base administrator)]
slide-19
SLIDE 19

Faloutsos CMU SCS 15-415 19

CMU SCS

Faloutsos CMU SCS 15-415 55

Casual users

DBMS data and meta-data = catalog

select * from student

CMU SCS

Faloutsos CMU SCS 15-415 56

``Naive’’ users

Pictorially:

DBMS data and meta-data = catalog

  • app. (eg.,

report generator)

CMU SCS

Faloutsos CMU SCS 15-415 57

  • App. programmers
  • those who write the applications (like the

‘report generator’)

slide-20
SLIDE 20

Faloutsos CMU SCS 15-415 20

CMU SCS

Faloutsos CMU SCS 15-415 58

DB Administrator (DBA)

  • Duties?

CMU SCS

Faloutsos CMU SCS 15-415 59

DB Administrator (DBA)

  • schema definition (‘logical’ level)
  • physical schema (storage structure, access

methods

  • schema modifications
  • granting authorizations
  • integrity constraint specification

CMU SCS

Faloutsos CMU SCS 15-415 60

Detailed outline

  • Introduction

– Motivating example – How do DBMSs work? DDL, DML, views. – Fundamental concepts – DBMS users – Overall system architecture – Conclusions

slide-21
SLIDE 21

Faloutsos CMU SCS 15-415 21

CMU SCS

Faloutsos CMU SCS 15-415 61

Overall system architecture

  • [Users]
  • DBMS

– query processor – storage manager

  • [Files]

CMU SCS

Faloutsos CMU SCS 15-415 62

DDL int. DML proc. query eval.

  • app. pgm(o)
  • trans. mgr
  • emb. DML
  • buff. mgr

file mgr

data meta-data

query proc. storage mgr. naive

  • app. pgmr

casual DBA

users

CMU SCS

Faloutsos CMU SCS 15-415 63

Overall system architecture

  • query processor

– DML compiler – embedded DML pre-compiler – DDL interpreter – Query evaluation engine

slide-22
SLIDE 22

Faloutsos CMU SCS 15-415 22

CMU SCS

Faloutsos CMU SCS 15-415 64

Overall system architecture (cont’d)

  • storage manager

– authorization and integrity manager – transaction manager – buffer manager – file manager

CMU SCS

Faloutsos CMU SCS 15-415 65

Overall system architecture (cont’d)

  • Files

– data files – data dictionary = catalog (= meta-data) – indices – statistical data

CMU SCS

Faloutsos CMU SCS 15-415 66

Some examples:

  • DBA doing a DDL (data definition

language) operation, eg.,

create table student ...

slide-23
SLIDE 23

Faloutsos CMU SCS 15-415 23

CMU SCS

Faloutsos CMU SCS 15-415 67

DDL int. DML proc. query eval.

  • app. pgm(o)
  • trans. mgr
  • emb. DML
  • buff. mgr

file mgr

data meta-data

query proc. storage mgr. naive

  • app. pgmr

casual DBA

users

CMU SCS

Faloutsos CMU SCS 15-415 68

Some examples:

  • casual user, asking for an update, eg.:

update student set name to ‘smith’ where ssn = ‘345’

CMU SCS

Faloutsos CMU SCS 15-415 69

DDL int. DML proc. query eval.

  • app. pgm(o)
  • trans. mgr
  • emb. DML
  • buff. mgr

file mgr

data meta-data

query proc. storage mgr. naive

  • app. pgmr

casual DBA

users

slide-24
SLIDE 24

Faloutsos CMU SCS 15-415 24

CMU SCS

Faloutsos CMU SCS 15-415 70

DDL int. DML proc. query eval.

  • app. pgm(o)
  • trans. mgr
  • emb. DML
  • buff. mgr

file mgr

data meta-data

query proc. storage mgr. naive

  • app. pgmr

casual DBA

users

CMU SCS

Faloutsos CMU SCS 15-415 71

DDL int. DML proc. query eval.

  • app. pgm(o)
  • trans. mgr
  • emb. DML
  • buff. mgr

file mgr

data meta-data

query proc. storage mgr. naive

  • app. pgmr

casual DBA

users

CMU SCS

Faloutsos CMU SCS 15-415 72

Some examples:

  • app. programmer, creating a report, eg

main(){ .... exec sql “select * from student” ... }

slide-25
SLIDE 25

Faloutsos CMU SCS 15-415 25

CMU SCS

Faloutsos CMU SCS 15-415 73

DDL int. DML proc. query eval.

  • app. pgm(o)
  • trans. mgr
  • emb. DML
  • buff. mgr

file mgr

data meta-data

query proc. storage mgr. naive

  • app. pgmr

casual DBA

users

pgm (src)

CMU SCS

Faloutsos CMU SCS 15-415 74

Some examples:

  • ‘naive’ user, running the previous app.

CMU SCS

Faloutsos CMU SCS 15-415 75

DDL int. DML proc. query eval.

  • app. pgm(o)
  • trans. mgr
  • emb. DML
  • buff. mgr

file mgr

data meta-data

query proc. storage mgr. naive

  • app. pgmr

casual DBA

users

pgm (src)

slide-26
SLIDE 26

Faloutsos CMU SCS 15-415 26

CMU SCS

Faloutsos CMU SCS 15-415 76

Detailed outline

  • Introduction

– Motivating example – How do DBMSs work? DDL, DML, views. – Fundamental concepts – DBMS users – Overall system architecture – Conclusions

CMU SCS

Faloutsos CMU SCS 15-415 77

Conclusions

  • (relational) DBMSs: electronic record

keepers

  • customize them with create table

commands

  • ask SQL queries to retrieve info

CMU SCS

Faloutsos CMU SCS 15-415 78

Conclusions cont’d

main advantages over (flat) files & scripts:

  • logical + physical data independence (ie.,

flexibility of adding new attributes, new tables and indices)

  • concurrency control and recovery