Transcript 1/9 - SEAS - University of Pennsylvania

Introduction
Susan B. Davidson
University of Pennsylvania
CIS330 – Database & Information Systems
Some slide content courtesy of Tova Milo
Welcome to CIS 330,
Database & Information Systems!
Instructor: Susan B. Davidson, [email protected]
 305 Levine Hall
 Office hours: MW 4-5 pm (or by appointment)
TAs: : Saurabh Garg, Vikrant Goel, Aaditya Shirodkar, Nan
Course web site: www.cis.upenn.edu/~cis330/
Online discussion forum: Piazza
Texts and readings:
 Ramakrishnan & Gerke, Database Systems, 3rd ed.
 For SQL: Greenspun, SQL for Nerds (online)
 Other books may be useful, such as Sunderraman’s ORACLE Programming: A
Primer and Brundage’s XQuery: The XML Query Language
Prerequisites: CIS121, CIS160 (Java, data structures, discrete math)
2
Course Format and Grading
 Roughly one major topic area per week to two
weeks
 Readings in the text




6 homework assignments (35%)
Two midterms (20% each)
Project (20%) – groups of 4
General participation, discussion, intangibles
(5%)
3
Cheating
 Cheating is a serious offense.
 Cheating includes copying on exams or written
assignments; obtaining advance copies of exams;
outsourcing homeworks or project work; and copying
material from the web and including on homeworks
without proper attribution.
 You may discuss concepts with your classmates or
anyone else, and you are encouraged to do so.
However, when it comes to writing the program (even
just the 'pseudo-code'), you must do it yourself
 Aiding someone else's cheating also constitutes
cheating.
4
Outline for Today’s Lecture
 Overview of database systems
 Recommended reading: Introduction of SQL for
Web Nerds, by Philip Greenspun
http://philip.greenspun.com/sql/
 (or you can read Chapter 1 of the textbook, but it’s
less fun)
 Course outline
 What the course is about
5
What Is a Relational Database
Management System ?
DataBase Management System = DBMS
Relational DBMS = RDBMS
 A collection of files that store the data
 A big C program written by someone else that
accesses and updates those files for you
6
Where are RDBMS used ?
 Backend for traditional “database” applications
 Backend for large Web sites
 Backend for Web services
7
Example of a Traditional Database
Application
 Suppose we are building a system to store
information about:




students
courses
professors
who takes what, who teaches what
8
Can we do it without a DBMS ?
Sure we can! Start by storing the data in files:
students.txt
courses.txt
professors.txt
Now write C or Java programs to implement
specific tasks
9
Doing it without a DBMS...
 Enroll “Mary Johnson” in “CSE444”:
Write a C program to do the following:
Read ‘students.txt’
Read ‘courses.txt’
Find&update the record “Mary Johnson”
Find&update the record “CSE444”
Write “students.txt”
Write “courses.txt”
10
Problems without an DBMS...
 System crashes:
Read ‘students.txt’
Read ‘courses.txt’
Find&update the record “Mary Johnson”
Find&update the record “CSE444”
Write “students.txt”
Write “courses.txt”
CRASH !
 What is the problem ?
 Large data sets (say 50GB)
 What is the problem ?
 Simultaneous access by many users
 Need locks: we know about them from OS, but now
data is on disk; and is it any fun to re-implement them ?
11
Enter a DMBS
“Two tier database system”
connection
(ODBC, JDBC)
Data files
Database server
(someone else’s
C program)
Applications
12
Functionality of a DBMS
The programmer sees SQL, which has two
components:
 Data Definition Language - DDL
 Data Manipulation Language - DML
 query language
Behind the scenes the DBMS has:
 Query engine
 Query optimizer
 Storage management
 Transaction Management (concurrency,
recovery)
13
Functionality of a DBMS
Two things to remember:
 Client-server architecture
 Slow, cumbersome connection
 But good for the data
 It is just someone else’s C program
 In the beginning we may be impressed by its speed
 But later we discover that it can be frustratingly slow
 We can do any particular task faster outside the
DBMS
 But the DBMS is general and convenient
14
How the Programmer Sees the
DBMS
 Start with DDL to create tables:
CREATE TABLE Students (
Name CHAR(30)
SSN CHAR(9) PRIMARY KEY NOT NULL,
Category CHAR(20)
) ...
 Continue with DML to populate tables:
INSERT INTO Students
VALUES(‘Charles’, ‘123456789’, ‘undergraduate’)
. . . .
15
How the Programmer Sees the
DBMS
 Tables:
Students:
SSN
123-45-6789
234-56-7890
Courses:
CID
CSE444
CSE541
Takes:
Name
Charles
Dan
…
Category
undergrad
grad
…
Name
Databases
Operating systems
Quarter
fall
winter
 Still implemented as files, but behind the scenes
can be quite complex
“data independence” = separate logical view
from physical
implementation
16
Transactions
 Enroll “Mary Johnson” in “CSE444”:
BEGIN TRANSACTION;
INSERT INTO Takes
SELECT Students.SSN, Courses.CID
FROM Students, Courses
WHERE Students.name = ‘Mary Johnson’ and
Courses.name = ‘Databases’
-- More updates here....
IF everything-went-OK
THEN COMMIT;
ELSE ROLLBACK
If system crashes, the transaction is still either committed or aborte
17
Transactions
 A transaction = sequence of statements that
either all succeed, or all fail
 Transactions have the ACID properties:
A = atomicity
C = consistency
I = independence
D = durability
18
Queries
 Find all courses that “Mary” takes
SELECT C.name
FROM Students S, Takes T, Courses C
WHERE S.name=“Mary” and
S.ssn = T.ssn and T.cid = C.cid
 What happens behind the scene ?
 Query processor figures out how to answer the query
efficiently.
19
Queries, behind the scene
Declarative SQL query
Imperative query execution pla
c.name
SELECT C.name
FROM Students S, Takes T, Courses C
WHERE S.name=“Mary” and
S.ssn = T.ssn and T.cid = C.cid
cid=cid
sid=sid
name=“Mary”
Students
Takes
Courses
The optimizer chooses the “best” execution plan for a query.
20
Database Systems
 The big commercial database vendors:




Oracle
IBM (with DB2)
Microsoft (SQL Server)
Sybase
 Some free database systems :
 MySQL (acquired by Oracle..)
 PostgreSQL
 We will use Oracle since the University provides
it…
21
An Issue: 80% of the World’s
Data is Not in a DB!
Examples:
 scientific data
(large images, complex programs that analyze the data)
 personal data
 WWW and email
(some of it is stored in something resembling a DBMS)
Data management is expanding to tackle these problems
 Flexibility – data management imposes many constraints to make
problems solvable
 Must deal with entities outside our control
In this course, we’ll start by focusing on databases, but
eventually look “outside the box” at the Web and at
bringing together data from many places
22
New Trends in Databases
 Object-relational databases
 Main memory database systems
 XML XML XML !




Relational databases with XML support
Middleware between XML and relational databases
Native XML database systems
Lots of research on XML and databases
 Data integration
 Peer to peer, stream data management – still
research
 BIG DATA
 * Hbase / Hive / Hadoop/ PigLatin
23
Course Outline
(may vary slightly)
Part I
 SQL (Chapter 5)
 The relational data model (Chapter 3)
 Database design (Chapters 2, 3, 19)
Part II
 Data storage, indexes (Chapters 8 - 11)
 Query execution and optimization (Chapter 12, 14,
15)
 XML, XPath, XQuery (Chapter 27)
 RDF
 Security (Chapter 21)
24
So what is this course about, really
?
Most CS courses concentrate on code – our interest is
managing and representing data – BIG DATA is BIG!
 SQL:
 An old language, but still cute
 Newer, XML stuff
 Unfortunately tools are still primitive here
 Theory !
 Implementation: hacking and thinking!
 And you need to learn a lot while you go
25
Size is relative
Are databases
Big Data?