Introduction to Database SystemsIntroduction to Database SystemsCSE 444Lecture 1IntroductionIntroduction1CSE 444 - Summer 2010Staff•Instructor: Hal PerkinsInstructor: Hal Perkins– CSE 548, [email protected] hours: CSE labs tba, office drop-ins and appointments elcomeappointments welcome• TA: Michael Ratanapintha–michaelratcsmichaelrat cs– Office hours: tba2CSE 444 - Summer 2010CommunicationsWeb pagehttp // cs ashington ed /444•Web page: http://www.cs.washington.edu/444– Lectures, homework, projects will be available there•Discussion list•Discussion list– See the web page–Discussions about the course databases etc Stay inDiscussions about the course, databases, etc. Stay in touch outside class• Mailing listg– Mostly announcements, intent is fairly low traffic– You are already subscribed if you are registered3CSE 444 - Summer 2010TextbookMain textbook, available at the bookstore:• Database Systems: The Complete Book, 2nded., Hector Garcia-Molina, Jeffrey Ullman,Jennifer WidomYo ill get the most o t of class if o read (skim /You will get the most out of class if you read (skim / get confused about) related sections before seeing them in lecture4CSE 444 - Summer 2010Other TextsAvailable at the Engineering Library (not on reserve – would anyone care if they were?):• Database Management Systems, Ramakrishnan• Fundamentals of Database Systems, Elmasri, Navathe• Foundations of Databases, Abiteboul, Hull, Vianu• Data on the Web, Abiteboul, Buneman, Suciu5CSE 444 - Summer 2010Course Format• Lectures MWF, 10:50-11:50 am, EE 037• Quiz sections: Th 9:40-10:40 or 10:50-11:50, EE 045EE 0454Mi ijt•4 Mini-projects• 3 homework assignments• Midterm and final6CSE 444 - Summer 2010Grading• Homeworks 30%•Mini-projects 30%Miniprojects 30%• Midterm 20%Fi l20%*•Final 20%**During summer, the final exam is the last day of class. Roughly a 2ndmidterm.7CSE 444 - Summer 2010Four Mini-Projects1. SQL (already posted)2SQL inJava2.SQL in Java3. Database tuning4Plll iMRd4.Parallel processing: MapReduceDue: Wednesdays every other week, online, 11pm8CSE 444 - Summer 2010Three Homework Assignments1. Conceptual Design2Transactions2.Transactions3. Query execution and optimizationDue: Wednesdays every other week, also 11 pm9CSE 444 - Summer 2010Late Policy• You have 4 late days to use during the quarter however you wishqy– No more than 2 on any single assignment or projectpj– Used in 24 hour chunks–No other late assignments acceptedgp(And we may specify no late days for particular assignments if needed to hand out solutions bf tth d fth t )before exams or at the end of the quarter)CSE 444 - Summer 2010 10Academic Conduct• We all learn best when we work with others, talk to colleagues, etc., and you ,g,,ydefinitely should do that, but…•Anything you submit for credit isAnything you submit for credit is expected to be your individual work (or your group’s work if the assignmentyour group s work if the assignment specifically allows for that)–Enough said?–Enough said?CSE 444 - Summer 2010 11Outline of Today’s Lecture1. Overview of a DBMS2. A DBMS through an example3. Course content12CSE 444 - Summer 2010DatabaseWhat is a database ?Give examples of databasesp13CSE 444 - Summer 2010Database Management SystemWhat is a DBMS ?Give examples of DBMSsp14CSE 444 - Summer 2010Required DataRequired Data Management Functionality1. Describe real-world entities in terms of stored data2. Create & persistently store large datasets3Efficiently query & update3.Efficiently query & update1. Must handle complex questions about data2. Must handle sophisticated updates3. Performance matters4. Change structure (e.g., add attributes)5.Concurrency control: enable simultaneous updates5.Concurrency control: enable simultaneous updates6. Crash recovery7. Security and integrityCSE 444 - Summer 2010 15DBMS Benefits• Expensive to implement all these features inside the application• DBMS provides these features (and more)• DBMS simplifies application developmentppp pHow do we decide what features should go into the DBMS?CSE 444 - Summer 2010 16Market SharesFrom 2007 Gartner report:• IBM: 21% market with $3.2BN in sales• Oracle: 47% market with $7.1BN in sales• Microsoft: 17% market with $2.6BN in sales17CSE 444 - Summer 2010An ExampleThe Internet Movie Database http://www.imdb.com• Entities: Actors(1 8M)Movies(1 5M)DirectorsActors (1.8M), Movies (1.5M), Directors, …•Relationships:Relationships:who played where, who directed what, …18CSE 444 - Summer 2010TablesActor: Cast:id fName lName gender pid mid195428 Tom Hanks M645947 Amy Hanks F195428 337166. . .Movie:. . .id Name year337166 Toy Story 1995. . .. . . . ..19CSE 444 - Summer 2010SQLSELECT *FROM Actor20CSE 444 - Summer 2010SQLSELECT (*)SELECT count(*)FROM ActorThis is an aggregate query21CSE 444 - Summer 2010SQLSELECT *FROM ActorWHERElname=‘Hanks’WHERE lname = HanksThis is a selection query22CSE 444 - Summer 2010SQLSELECT *FROM Actor, Casts,Movie,,WHERE lname='Hanks' and Actor.id = Casts.piddCid M i idd M i 1995and Casts.mid=Movie.id and Movie.year=19951 8M actors 11 4M casts 1 5M movieshow can it be so fast?This query has selections and joins231.8M actors, 11.4M casts, 1.5M movies –how can it be so fast?CSE 444 - Summer 2010How Can We Evaluate the Query ?Actor: Cast:Movie:id fName lName genderid Name yearpid mid. . . Hanks. . .. . . 1995. . .. . .. . .Plan 1: . . . . [ in class ]Plan 2: . . . . [ in class ]24CSE 444 - Summer 2010Evaluating Tom Hanks><><><><><><><σlName=‘Hanks’σyear=1995σlName=‘Hanks’σyear=1995Actor Cast Movie Actor Cast Movie25CSE 444 - Summer 2010Optimization and Query Execution• Indexes: on Actor.lName, on Movie.year• Multiple implementations of joinspp j• Query optimization (which join order? access path selection)• Statistics26CSE 444 - Summer 2010Now Let’s See Database Updates• Transfer $100 from account #4662 to #7199:X R d(A t #4662)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);27CSE 444 - Summer 2010Now Let’s See Database Updates• Transfer $100 from account #4662 to #7199:X R d(A t #4662)X = Read(Account, #4662);X.amount = X.amount - 100;Write(Account, #4662, X);CRASH !Y = Read(Account, #7199);Y.amount = Y.amount + 100;Write(Account, #7199, Y);CRASH !What is the problem ?28What is the problem ?CSE 444 - Summer
View Full Document
Unlocking...