Lecture Slides on Databases - Introduction to Software Engineering | CSCI 0320, Study notes of Software Engineering

Download Lecture Slides on Databases - Introduction to Software Engineering | CSCI 0320 and more Study notes Software Engineering in PDF only on Docsity! CSCI0320 Introduction to Software Engineering Lecture 11 Databases July 13, 2009 Lecture 11 Page 1 Page 2 Motivation • There is a lot of data available – People want to use it • Freedb: database of all CDs published – 4G of data (one big text file) – Semi-parsable text records • Provide disk title, artist, genre • Provide track title, artist, offsets • Provide additional information • Suppose you wanted to use this data – How would you find your favorite CD? – How would you determine how many CDs for a given song? July 13, 2009 Lecture 11 Page 5 Network Databases • Hierarchies are limited – Instead of trees, use graphs • Entities & Relationships – Entities represent data basics – Relationships show how basics are related • Query by traversal July 13, 2009 Lecture 11 Page 6 Need for More • These were limiting – Require anticipating user’s queries • Since query implies navigation – Want an organization • That is independent of the queries • That can handle any query efficiently – At least potentially • Result was the relational database model – Data organized into tables – Relationships between tables • Defined dynamically as part of query July 13, 2009 Lecture 10 Page 7 Relations or Tables • Basic unit for data organization – Table has rows and columns – Columns are typed FIELDS – Rows are TUPLES representing one data element July 13, 2009 Lecture 11 Page 10 CD Database ID Title ArtistID Length Genre Year July 13, 2009 Lecture 11 Disk ID Name DiskId ArtistId Length Number Offset Track ID Name Artist Page 11 CD Database July 13, 2009 Lecture 11 DiskID TrackID Data Extended Word Type ID Words Type: T => Title, A => Artist, N=>Track name, D => Disk Data, I => Track data Page 12 Why This Format • Why have artist as a separate relation? • Why have ID field in Disk, Track? • Why have Words as a separate relation? • Why have length in disk relation? • Why have length & number in track? July 13, 2009 Lecture 11 Page 15 Tables As Results • The result of a query – Set of matching tuples – But this is a table • Result of a query is a table – Can be used in another query – Can be treated as an original table July 13, 2009 Lecture 11 Page 16 Indices • To achieve efficiency, use indices – Fast access to tuples in a relation • Based on a key – Can be single field – Can be multiple fields • Key does not have to be unique • How would you implement an index? – Data structure on disk – Balanced tree with blocked records – Hash table • Why not index everything? July 13, 2009 Lecture 11 Page 17 Indices for CD Database • Disk: ID, ArtistID • Track: ID, ArtistID, DiskID, Name • Extended: DiskID, TrackID • Artist: ID, Name • Words: Word July 13, 2009 Lecture 11 Page 20 SQL Select Basics • SELECT – Defines the resultant relation – List of fields and where they come from – <expression> – <expression> as <name> – * for everything – COUNT(*) and other group expressions July 13, 2009 Lecture 11 Page 21 WHERE Clause • Sequence of relationals – Separated by AND and OR – X.field = ‘value’ – X.field = Y.value • Can also included nested SELECT – X.field IN ( SELECT …) • Can also set operations on tables July 13, 2009 Lecture 11 Page 22 Examples • Find all CDs with ‘alive’ in the title – Simple form • SELECT D.title FROM Disk D • WHERE D.title LIKE ‘%alive%’ – Using the Word table • SELECT D.title • FROM Disk D, Words W • WHERE W.word = ‘alive’ AND – W.id = D.id AND W.type = ‘T’ • psql –h bridget cdquery July 13, 2009 Lecture 11 Page 25 Java and SQL • Using psql is okay (actually painful) – How to use it from a program – Could run psql as a command • Database runs as a separate process – Typically on a separate machine – WHY? • Handle multiple users simultaneously • Protect and isolate from user code – Communicate with the database • Using sockets • Message protocol July 13, 2009 Lecture 11 Page 26 Message Protocol • Want to work at a higher level – Send a query, read the results • Might not want all the results at once – Don’t want to deal with messages – But you need standard messages • This has been done – For multiple languages (same protocol) – For multiple database systems – JDBC for Java – ODBC for C++ – PHP uses it with built-in data structures July 13, 2009 Lecture 11 Page 27 Using JDBC • First need to load the library – Each database system is different • Why? – Class.forName(“postgresql.Driver”) • Then you need to connect to the database – java.sql.Connection • Represents the connection • A program can have multiple connections • But typically will only use one – DriverManager.getConnection(nm,user,pw) • nm = “jdbc:postgresql://bridget/cdquery • Catch SQLException – sql_conn.close() to disconnect July 13, 2009 Lecture 11 Page 30 Handling Query Results • java.sql.ResultSet – Represents the result of a query • Acts as an iterator over tuples in the result • Lets you access fields of the current tuple – Iterating • next(): move to next (first) tuple • first(), last(), previous(), absolute(int) – Accessing fields • By index (1..n) or by name • Specified by type (automatic conversion) • getString(int), getString(“field”) • getInt, getDouble, getBytes, getTime, getDate • getTimestamp – Close the result set using close() July 13, 2009 Lecture 11 Page 31 XML Databases • Suppose you wanted to query data – Where the data is in XML format • What do queries look like? – Data relationships – Hierarchy relationships • XPath expresses hierarchy relationships – Can specify arbitrary paths • With wildcards • XQuery provides query language – Combines SQL like access to data – With XPath descriptions of paths July 13, 2009 Lecture 11 Page 32 XML Implementation • How would you implement this? – Internal representation of XML – Indices where appropriate – What if the XML document is huge? • Move everything to a relational database – Database relations expression hierarchy – Map XPath to SQL expression – Add that to normal expression July 13, 2009 Lecture 11