Introduction to Database Systems CSE 444 Lecture #1 January 6, 2003

Introduction to Database Systems CSE 444 Lecture #1 January 6, 2003 Guest Lecturer: Prof. Dan Suciu (Alon will explain when he returns) 1

Staff Instructor: Alon Halevy – Sieg, Room 310, [email protected] – Office hours: See web site TA: Luna Dong and Eric Chu – lunadong,[email protected] – Office hours: TBA (check web site) 2

Communications Web page: http://www.cs.washington.edu/444/ Mailing list: follow the directions at http://mailman.cs.washington.edu/csenet id/auth/mailman/listinfo/cse444 3

Introductory Questionnaire You answer a few questions about yourself. We will put it in a database and use it for queries later. The questionnaire will be in XML. Send by email to Eric Chu by Wednesday. See web page for details. 4

Textbook(s) Main textbook, available at the bookstore: Database Systems: The Complete Book, Hector Garcia-Molina, Jeffrey Ullman, Jennifer Widom Almost identical, and also available at the bookstore: A First Course in Database Systems, Jeff Ullman and Jennifer Widom Database Implementation, Hector Garcia-Molina, Jeff Ullman and Jennifer Widom 5

Other Texts On reserve at the Engineering Library: Database Management Systems, Ramakrishnan – very comprehensive Fundamentals of Database Systems, Elmasri, Navathe – very widely used Foundations of Databases, Abiteboul, Hull, Vianu – Mostly theory of databases Data on the Web, Abiteboul, Buneman, Suciu – XML and other new/advanced stuff 6

Other Required Readings There will be reading assignments from the Web: SQL for Web Nerds, by Philip Greenspun, http://philip.greenspun.com/sql/ Others, especially for XML For SQL, a good source of information is the MSDN library (on your Windows machine) 7

Outline for Today’s Lecture Overview of database systems – Reading assignment for next lecture (Wednesday): from SQL for Web Nerds, by Philip Greenspun, Introduction http://philip.greenspun.com/sql/ Course Outline Structure of the course (some left for Alon) 8

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 9

Where are RDBMS used ? Backend for traditional “database” applications Backend for large Websites Backend for Web services 10

Example of a Traditional Database Application Suppose we are building a system to store the information about: students courses professors who takes what, who teaches what 11

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 12

Doing it without a DBMS. Enroll “Mary Johnson” in “CSE444”: Write a C program to do the following: Read Read ‘students.txt’ ‘students.txt’ Read Read ‘courses.txt’ ‘courses.txt’ Find&update Find&update the the record record “Mary “Mary Johnson” Johnson” Find&update Find&update the the record record “CSE444” “CSE444” Write Write “students.txt” “students.txt” Write Write “courses.txt” “courses.txt” 13

Problems without an DBMS. System crashes: Read ‘students.txt’ Read ‘students.txt’ Read ‘courses.txt’ Read ‘courses.txt’ Find&update Find&updatethe therecord record“Mary “MaryJohnson” Johnson” Find&update the record “CSE444” Find&update the record “CSE444” Write Write“students.txt” “students.txt” Write “courses.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 them from OS, but now data on disk; and is there any fun to re-implement them ? 14

Enters a DMBS “Two tier database system” connection (ODBC, JDBC) Data files Database server (someone else’s C program) Applications 15

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 optimizer Query engine Storage management Transaction Management (concurrency, recovery) 16

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 17

How the Programmer Sees the DBMS Start with DDL to create tables: CREATE CREATETABLE TABLEStudents Students(( Name NameCHAR(30) CHAR(30) SSN SSNCHAR(9) CHAR(9)PRIMARY PRIMARYKEY KEYNOT NOTNULL, NULL, Category CategoryCHAR(20) CHAR(20) )) . . . . Continue with DML to populate tables: INSERT INSERTINTO INTOStudents Students VALUES(‘Charles’, VALUES(‘Charles’,‘123456789’, ‘123456789’,‘undergraduate’) ‘undergraduate’) . . . . 18

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 SSN 123-45-6789 123-45-6789 234-56-7890 CID CSE444 CSE444 CSE142 Quarter fall winter Still implemented as files, but behind the scenes can be quite complex “data independence” separate logical view from physical implementation 19

Transactions Enroll “Mary Johnson” in “CSE444”: BEGIN BEGINTRANSACTION; TRANSACTION; INSERT INSERTINTO INTOTakes Takes SELECT SELECTStudents.SSN, Students.SSN,Courses.CID Courses.CID FROM FROMStudents, Students,Courses Courses WHERE WHEREStudents.name Students.name ‘Mary ‘MaryJohnson’ Johnson’and and Courses.name Courses.name ‘CSE444’ ‘CSE444’ ----More Moreupdates updateshere. here. IF IFeverything-went-OK everything-went-OK THEN THENCOMMIT; COMMIT; ELSE ELSEROLLBACK ROLLBACK 20 If system crashes, the transaction is still either committed or aborted

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 21

Queries Find all courses that “Mary” takes SELECT SELECT C.name C.name FROM FROM Students Students S, S, Takes Takes T, T, Courses Courses CC WHERE WHERE S.name “Mary” S.name “Mary” and and S.ssn S.ssn T.ssn T.ssn and and T.cid T.cid C.cid C.cid What happens behind the scene ? – Query processor figures out how to answer the query efficiently. 22

Queries, behind the scene Declarative SQL query Imperative query execution pla sname SELECT SELECT C.name C.name FROM FROMStudents StudentsS, S,Takes TakesT, T,Courses CoursesCC WHERE WHERES.name “Mary” S.name “Mary”and and S.ssn S.ssn T.ssn T.ssnand andT.cid T.cid C.cid C.cid cid cid sid sid name “Mary” Students Takes Courses The optimizer chooses the best execution plan for a query 23

Database Systems The big commercial database vendors: – – – – Oracle IBM (with DB2) bought Informix recently Microsoft (SQL Server) Sybase Some free database systems (Unix) : – Postgres – Mysql – Predator In CSE444 we use SQL Server. You may use something else, but you are on your own. 24

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 here at UW on XML and databases Data integration Peer to peer, stream data management – still research 25

Course Outline (may vary slightly) Part I SQL (Chapter 7) The relational data model (Chapter 3) Database design (Chapters 2, 3, 7) XML, XPath, XQuery Midterm: February 12th. Part II Data storage, indexes (Chapters 11-13) Query execution and optimization (Chapter 15,16) Data integration Final: March 19th. 26

Structure Prerequisites: Data structures course (CSE-326 or equivalent). Work & Grading: – – – – – Homework 25%: 5 of them, some light programming. Project: 25% - Alon will explain on friday. Midterm: 20% Final: 25% Intangibles: 5% 27

So what is this course about, really ? SQL: – An old language, but still cute Newer, XML stuff – Unfortunately less programming here Theory ! Lots of implementation and hacking ! – And you need to learn a lot while you go 28