DOC PREVIEW
UW CSE 444 - Lecture Notes

This preview shows page 1-2-3-27-28-29 out of 29 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 29 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Introduction to Database Systems CSE 444 Lecture 1: IntroductionThe Staff !! Instructors: !! Wolfgang Gatterbauer (CSE 438) !! Alexandra Meliou (CSE 444) !! Email: cse444-instructors@cs !! Office Hours: Wed 2pm – 3:50pm !! TAs: !! Grad: Rita Sodt (rsodt@cs) !! Office Hours: Thu 10:30am – 12pm CSE 216 !! Ugrad: Liem Dinh (liemdinh@cs) !! Office Hours: Tue 1:30pm – 3pm CSE 220 1 Same as the course number, how cool is that!Communications !! Web page: http://www.cs.washington.edu/444 !! Lectures will be available there !! The mini-projects description will be there !! Homeworks will be posted there !! Mailing list !! Announcements, group discussions !! You are already subscribed !! Message board !! Great place to ask assignment-related questions 2Textbook !! Main textbook: !! Other Texts: !! Database Management Systems, Ramakrishnan, Gehrke !! Foundations of Databases, Abiteboul, Hull, Vianu !! Fundamentals of Database Systems, Elmarsi, Navathe !! Data on the Web, Abiteboul, Buneman, Suciu 3 Database Systems: The Complete Book Hector Garcia Molina Jeffrey Ullman Jennifer WidomCourse Format !! Lectures: MWF, 12:30pm – 1:20pm !! EEB 045 !! Sections: Thu, 8:30am – 9:20am, 9:30am – 10:20am !! EEB 025 !! 4 Mini-Projects !! 3 Homework assignments !! Midterm and final exams 4Grading !! Homeworks 30% !! Mini-projects 30% !! Midterm 15% !! Final 25% 5 Homeworks 30% Mini-Projects 30% Midterm 15% Final 25%Four Mini-Projects !! SQL !! SQL in Java !! Database tuning !! Parallel processing: MapReduce 6 Check course website for due datesThree Homework Assignments !! Conceptual Design !! Transactions !! Query execution and optimization 7 Check course website for due datesExams !! Midterm: Wednesday, February 9, in class !! Final: Thursday, March 17, 8:30-10:20am, in class 8Outline of Today’s Lecture !! Overview of a DBMS !! A DBMS through an example !! Course content 9Database !! What is a database ? !! A collection of files storing related data !! Give examples of databases !! Accounts database; payroll database; UW’s students database; Amazon’s products database; airline reservation database 10Database Management System !! What is a DBMS ? !! A big C program written by someone else that allows us to manage efficiently a large database and allows it to persist over long periods of time !! Give examples of DBMSs !! DB2 (IBM), SQL Server (MS), Oracle, Sybase !! MySQL, PostgreSQL, … 11Market Shares !! From 2006 Gartner report: !! Oracle: 47% market with $7.1BN in sales !! IBM: 21% market with $3.2BN in sales !! Microsoft: 17% market with $2.6BN in sales !! From 2008 Gartner study: 12An Example !! The Internet Movie Database !! http://www.imdb.com !! Entities: Actors (1.8M), Movies (1.5M), Directors, … !! Relationships: who played where, who directed what, … 13Required Data Management Functionality !! Describe real-world entities in terms of stored data !! Create & persistently store large datasets !! Efficiently query & update !! Must handle complex questions about data !! Must handle sophisticated updates !! Performance matters !! Change structure (e.g., add attributes) !! Concurrency control: enable simultaneous updates !! Crash recovery !! Security 14DBMS Benefits !! Expensive to implement all these features inside the application !! DBMS provides these features (and more) !! DBMS simplifies application development 15Back to Example: IMDB database 16 Actor Movie id fName lName gender id name year rank Directors id fName lName Genre mid genre Movie_Directors did mid Casts pid mid roleTables 17 Actor: Cast: Movie: id fName lName gender 429073 Tom Hanks M 146871 Amy Hanks F . . . id Name year 561300 Toy Story 1995 . . . . . . . .. pid mid role 429073 561300 Woody . . .SQL 18 SELECT * FROM ActorSQL 19 SELECT count(*) FROM Actor This is an aggregate querySQL 20 SELECT * FROM Actor WHERE lName = ‘Hanks’ This is a selection querySQL 21 SELECT * FROM Actor, Casts, Movie WHERE lname='Hanks' and Actor.id = Casts.pid and Casts.mid=Movie.id and Movie.year=1995 1.8M actors, 11M casts, 1.5M movies; How long do we expect it to take? This query has selections and joinsHow Can We Evaluate the Query ? 22 Actor: Cast: Movie: id fName lName gender . . . Hanks . . . id Name year . . . 1995 . . . pid mid . . . . . . Plan 1: . . . . [ in class ] Plan 2: . . . . [ in class ]Evaluating Tom Hanks 23 !"!"Actor Cast Movie !lName=‘Hanks’ !year=1995 !"!"Actor Cast Movie !lName=‘Hanks’ !year=1995 21K 21K 61 61 178K 884 20 20What an RDBMS Does Well (1/2) !! Indexes: on Actor.lName, on Movie.year !! Multiple implementations of joins !! Query optimization !! Access path selection !! Join order !! Join implementation !! Statistics ! 24Now Let’s See Database Updates !! Transfer $100 from account #4662 to #7199: 25 X = Read(Account, #4662); X.amount = X.amount - 100; Write(Account, #4662, X); Y = Read(Account, #7199); Y.amount = Y.amount + 100; Write(Account, #7199, Y); CRASH ! What is the problem ?What a RDBMS Does Well (2/2) !! Transactions ! !! Recovery !! Concurrency control 26Client/Server Architecture !! There is a single server that stores the database (called DBMS or RDBMS): !! Usually a beefy system, e.g. IISQLSRV !! But can be your own desktop… !! … or a huge cluster running a parallel dbms !! Many clients run apps and connect to DBMS !! E.g. Microsoft’s Management Studio !! Or psql (for postgres) !! More realistically some Java or C++ program !! Clients “talk” to server using JDBC protocol 27What This Course Contains !! SQL !! Conceptual Design !! Transactions !! Database tuning and internals (very little) !! Query Optimization !! Distributed databases: a taste of MapReduce !! More stuff depending on time


View Full Document

UW CSE 444 - Lecture Notes

Documents in this Course
XML

XML

48 pages

SQL

SQL

25 pages

SQL

SQL

42 pages

Recovery

Recovery

30 pages

SQL

SQL

36 pages

Indexes

Indexes

35 pages

Security

Security

36 pages

Wrap-up

Wrap-up

6 pages

SQL

SQL

37 pages

More SQL

More SQL

48 pages

SQL

SQL

35 pages

XML

XML

46 pages

Triggers

Triggers

26 pages

Load more
Download Lecture Notes
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Lecture Notes and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Lecture Notes 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?