New User Special Price Expires in

Let's log you in.

Sign in with Facebook


Don't have a StudySoup account? Create one here!


Create a StudySoup account

Be part of our community, it's free to join!

Sign up with Facebook


Create your account
By creating an account you agree to StudySoup's terms and conditions and privacy policy

Already have a StudySoup account? Login here

Database Des&Implmnt

by: Roman McCullough

Database Des&Implmnt CMPSCI 645

Roman McCullough
GPA 3.57

Yanlei Diao

Almost Ready


These notes were just uploaded, and will be ready to view shortly.

Purchase these notes here, or revisit this page.

Either way, we'll remind you when they're ready :)

Preview These Notes for FREE

Get a free preview of these Notes, just enter your email below.

Unlock Preview
Unlock Preview

Preview these materials now for free

Why put in your email? Get access to more of this material and other relevant free materials for your school

View Preview

About this Document

Yanlei Diao
Class Notes
25 ?




Popular in Course

Popular in ComputerScienence

This 32 page Class Notes was uploaded by Roman McCullough on Friday October 30, 2015. The Class Notes belongs to CMPSCI 645 at University of Massachusetts taught by Yanlei Diao in Fall. Since its upload, it has received 16 views. For similar materials see /class/232249/cmpsci-645-university-of-massachusetts in ComputerScienence at University of Massachusetts.


Reviews for Database Des&Implmnt


Report this Material


What is Karma?


Karma is the currency of StudySoup.

You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!

Date Created: 10/30/15
Welcome to CMPSCI 645 Database Design amp Implementation Instructor Gerome Miklau Overview of Databases Gerome Miklau CMPSCI 645 Database Design amp Implementation UMass Amherst Jan 29 2008 Some slide content courtesy of Zack Ives Ramakrishnan amp Gehrke Dan Suciu Ullman amp Widom Today Student information form Overview of databases Course topics Course requirements Databases amp DBMS s A database is a large integrated collection of data A database management system DBMS is a collection of software designed to store and manage databases allowing Define the kind of data stored Queryingupdating interface Reliable storage amp recovery of 100s of GB Control access to data from many concurrent users Can filesystems do it No Schema for files is limited No query language for data in files Files can store large amounts of data but no efficient access to items within file no recovery from failure Concurrent access not safe Evolution Early DBMS s 1960 s evolved from file systems Data with many small items amp many queries or modifications Airline reservations Banking Early DB systems Data model The data model includes basic assumptions about what s an item of data how to represent it and interpret it Treebased hierarchical data model Graphbased network data model Encouraged users to think about data the way it was stored No high level query language The Relational Model The relational data model Codd 1970 Data independence details of physical storage are hidden from users Highlevel declarative query language say what you want not how to compute it mathematical foundation A theory of normalization guides the design of relations Side note Turing Awards in Databases 1973 Bachman networked data model 1981 Codd relational model 1998 Jim Gray transaction processing DBMS Benefit 1 Generality and Declarativity The programmer or user does not need to know details like indices sort orders machine speeds disk speeds concurrent users etc Instead the programmeruser programs with a logical model in mind The DBMS makes it happen based on an understanding of relative costs of different methods Benefit 2 Efficiency and Scale Efficient storage of hundreds of GBs of data Efficient access to data Rapid processing of transactions Benefit 3 Management of Concurrency and Reliability Simultaneous transactions handled safely Recovery of system data after system failure More formally the ACID properties Atomicity all or nothing Consistency sensible state not violated Isolation separated from effects Durability once completed never lost How Does One Build a Database Start with a conceptual model Design amp implement schema Write applications using DBMS and other tools Many ways of doing this DBMS API writers library authors web server etc Common applications include PHPJSPservlet driven web sites The DBMS takes care of query optimization and execution Conceptual Design PROFESSOR STUDENT COURSE Designing a Schema Set of Relations akes COURSE 39 acid 7 cid sem STUDENT T sid name all J Jill 645 DB F05 Bo 683 Al 805 Maya 39 635 Arch F05 PROFESSOR Teaches fid name Convert to tables constraints Then need to do physical design the Saul layout on disk indices r Weems etc Diao Queries Find all courses that Mary takes SELECT Cname FROM Students S Takes T Courses C WHERE Sname Mary and Ssid Tsid and Tcid Ccid What happens behind the scene Query processor figures out how to answer tl the query efficren y Queries behind the scene Declarative SQL query gt Query execution plan TI SELECT Cname sname FROM Students S Takes T Courses C v WHERE Sname Mary and N Ssid Tsid and Tcid Ccid cidcid lgtltl sidsid O name Mary h The optimizer chooses the best execution plan for a query 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 VWWV and email some of it is stored in something resembling a DBMS Data management is expanding to tackle these problems DBMSs in the Real World A huge industry for 20 of the world s data Big mature relational databases IBM DBZ Oracle Microsoft SQL Server Adding advanced features including native XML support Middleware above these systems SAP Siebel PeopIeSoft dozens of specialpurpose apps Integration and warehousing systems BEAAquaLogic DBZ Information Integrator Current trends Web services XML everywhere Smarter selftuning systems Distributed databases columnstores Database Research One of the broadest most exciting areas in CS A microcosm of CS in general languages operating systems concurrent programming data structures algorithms theory distributed systems statistical techniques Theory and systems wellintegrated Recent Trends in Databases XML Relational databases with XML support Middleware between XML and relational databases Largescale XML message systems Main memory database systems Peer data management Stream data management Model management provenance Security and privacy Modeling uncertainty probabilistic databases What is the Field of Databases To an applied researcher SlGMODNLDBICDE Query optimization Query processing yetanotherjoin algorithm Transaction processing recovery but most stuff is already done Novel applications data mining highdimensional search To a theoretical researcher PODSlCDTLICS Focus on the query languages Query language logic complexity classes Course topics Fundamentals relational design query languages Theory expressiveness of query languages static analysis complexity Database internals storage indexing query processing query optimization transaction management XML and semistructured data models Security access control privacy Advanced topics incompleteprobabilistic DBs parallel and distributed DBs Prerequisites Official undergrad course in DB or OS Also Elementary complexity theory Grading Homework 30 Paper reviews amp participation 15 Midterm 30 Project 25 Homework 30 4 assignments throughout the course written problem sets practical experience with SQL XQuery Paper Reviews amp Participation 15 Approximately 5 classic papers will be assigned Short written reviews are due before the day of class Email to csG45reviewscsumassedu First paper review Read thru 14 of Codd s paper Due Wed Feb 5th Project 25 General theme apply database principles to a new problem Suggested topics will be discussed next Tuesday Groups of 2 preferred 3 possible Project work will include Reading some of the research literature Implementation Written report lnclass presentation Periodic consultation with the instructor Midterm Exam 30 Midterm scheduled for Apr 17th at 7pm No Final Textbook Database Management Systems Ramakrishnaquot Gehrke Database Management Systems Ramakrishnan and Gehrke Other useful resources Database systems the complete book Ullman Widom and GarciaMolina Readings in Database Systems Stonebraker and Hellerstein Foundations of Databases Abiteboul Hull Vianu Data on the Web Abiteboul Buneman Suciu Parallel and Distributed DBMS Ozsu and Valduriez Transaction Processing Gray and Reuter Data and Knowledge based Systems volumes I II Ullman Proceedings of SIGMOD VLDB PODS conferences Communication Instructor Office hours by appointment Email miklau at cs dot umass dot edu Check the course webpage often You should have been added to the mailing list Questions about the course


Buy Material

Are you sure you want to buy this material for

25 Karma

Buy Material

BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.


You're already Subscribed!

Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'

Why people love StudySoup

Bentley McCaw University of Florida

"I was shooting for a perfect 4.0 GPA this semester. Having StudySoup as a study aid was critical to helping me achieve my goal...and I nailed it!"

Amaris Trozzo George Washington University

"I made $350 in just two days after posting my first study guide."

Jim McGreen Ohio University

"Knowing I can count on the Elite Notetaker in my class allows me to focus on what the professor is saying instead of just scribbling notes the whole time and falling behind."


"Their 'Elite Notetakers' are making over $1,200/month in sales by creating high quality content that helps their classmates in a time of need."

Become an Elite Notetaker and start selling your notes online!

Refund Policy


All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email


StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here:

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.