Overview/Questions How is data stored within a computer? What is - PDF document

CS108 Lecture 20: Introduction to Databases and SQL Aaron Stevens 16 March 2009 1 Overview/Questions – How is data stored within a computer? – What is a database? – How does a database fit into an application? – What are the operations we can do with data stored in a database? 2 1

Storing Data: Memory Memory is used to store programs and other data that are currently in use. – LOAD, STOR operations Advantage of memory: short access times – Read/write times in nanoseconds (10 -9 sec) Disadvantages of memory: – relatively expensive ($/byte) – “volatile” 3 Storing Data: Secondary Storage Secondary storage is used to store data for later use (examples: disks, CD, DVD). – Data is written from memory to disk. – When needed, data is read back into memory. 4 2

Secondary Storage Advantages of secondary storage: – relatively inexpensive ($/byte) – not “volatile” Disadvantage of secondary storage: long access times – Read times in milliseconds (10 -3 sec). – in 10 ms, a modern CPU can perform millions of operations! Thus, it's important to minimize the number of times that the disk is accessed. 5 Databases A database is a collection of data stored in a way to enable quick access. A database can be organized into related sub- collections called tables. – Example: a stock investment database:  A stocks table has records of various stocks and their related information.  A trades table has records of trades -- investment transactions. Each table (sub-collection) is organized by records, and each record contains fields. 6 3

Databases Each table (sub-collection) is organized by records, and each record contains fields. Example: Note that all records within a table have the same set of fields. 7 Database Management Systems A database is a collection of data (not software). A database management system (DBMS) is the software which manages a database. Functions of a DBMS: – Efficient storage – Providing a logical view of data (tables, records) – Query processing – Transaction management 8 4

Database Management Systems Efficiency - Indexing enables locating a record based on some unique attribute, called a key. – Example: looking up stocks by their symbol . Logical representation - storage by record. – Example: update the record for symbol=‘BUD’ . 9 Query Processing A query language is used to access and modify the data. SQL (Structured Query Language) is the standard for relational databases. Many different database vendors support SQL: – Oracle, Sybase, IBM DB2, MS SQL Server, MS Access – MySQL, SQLite (free/open-source) 10 5

Transaction Integrity A transaction is an atomic sequence of operations. – Example: booking a flight  Select flight, reserve seat, make payment DBMS guarantees transaction integrity: completion or failure. 11 Database Applications End users rarely interact with a database directly. A database-enabled application allows the users to interact with the database without needing to know the query language. 12 6

Structured Query Language You’ll need to know some SQL to write database-enabled applications. – independent language -- syntax and semantics. SQL is comprised of 2 sub-languages: – Data Definition Language (DDL): CREATE TABLE, DROP TABLE, ALTER TABLE – Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE We’ll focus on the DML for interacting with records. 13 Example: Investment Database Let’s consider a database with 2 tables: Notice that these tables share some data: – symbol is a primary key in stocks , and a foreign key in trades ; this enables cross-table relationships. 14 7

The SELECT Query General form: SELECT <field1>, <fields2>, … FROM <table1>, <table2>, … [WHERE <field>=<value> … ] The minimal SELECT query requires only a list of fields (or * for all) and a single table. Example: SELECT * FROM stocks 15 The SELECT Query (continued) The SELECT query can specify conditions using the WHERE clause, which creates a more refined result set (e.g. only matching records are returned). Example: SELECT symbol, name, price FROM stocks WHERE symbol="BUD" 16 8

SQL Comparison Operators SQL WHERE clauses can support all of the usual comparison operations. Here are the SQL comparison operators. 17 The SELECT Query (continued) Multiple WHERE criteria can be joined together using the logical operators AND , OR and NOT . Example: SELECT FROM stocks WHERE (earnings>0) AND (dividends/price > 0.04) 18 9

The SELECT Query (continued) Virtual fields can be created by SELECT argument, using arithmetic and some built-in aggregate functions. Example: SELECT *, dividends/price as "yield" FROM stocks WHERE (earnings>0) AND (yield<0.20) 19 The SELECT Query (continued) WHERE criteria can use wildcard comparisons as well, using the LIKE clause for near-matches. Using the LIKE clause for near-matches: SELECT * FROM stocks WHERE (symbol LIKE "B%") 20 10

The INSERT Query General form: INSERT INTO <table> VALUES (<val1>, <val2>,<val3> … ) The INSERT query will insert a record into the table. It requires a list values – one value for each field in the record. Example: INSERT INTO stocks VALUES ('MSFT', 'Microsoft Corporation', 21.96, 1.76, 0.44) 21 The UPDATE Query General form: UPDATE <table> SET <field1>=<value1>, <field2>=<value2> [WHERE <field>=<value>] Example: UPDATE stocks SET dividends=1.28 WHERE symbol= ' MO ’ Always use a WHERE clause in an UPDATE! 22 11

The DELETE Query General form: DELETE FROM <table> [WHERE <field>=<value>] Example: DELETE FROM stocks WHERE symbol= ’MSFT’ The DELETE query is extremely dangerous. Always verify your criteria before deleting! Always use a WHERE clause in a DELETE! 23 Take-Away Points – Databases persistence versus volatility. – DBMS: efficiency, logical view, query language, and transaction integrity. – Structured Query Language  SELECT, UPDATE, INSERT, DELETE  Constraints with WHERE clause 24 12

Next Time: The Python DB API Python defines a standard API (objects and methods) for interaction with databases. – No standard implementation of this interface. – 3 rd party developers write their own libraries which conforms to the standard. We will be using the sqlite3 database, which is free and is as part of the Python distribution. – Nothing extra for you to install! 25 Using Python with MS Access Should you want to use Python with Microsoft Access, you will need to download and install 2 components: 1- install Mark Hammond's pywin32-210 for python 2.5 http://starship.python.net/crew/mhammond/win32/Downloads.html 2- download adodbapi.zip from http://adodbapi.sourceforge.net/ Unzip adodbapi.zip into C:\Python25\Lib\site-packages\ This is only available for Windows! 26 13

Using sqliteClient Program I wrote a client a PythonCard GUI application which is a client program to interact with a sqlite3 database. Instructions for installing PythonCard are here: http://www.cs.bu.edu/courses/cs108/slides/CS108.PythonCard.Install.pdf You may use this to experiment with SQL statements. The program can be downloaded from: http://www.cs.bu.edu/courses/cs108/util/sqliteClient.zip 27 Student To Dos – Readings:  SQL Tutorial (today) http://www.firstsql.com/tutor.htm  Python DBAPI and sqlite3 (Wednesday) http://docs.python.org/library/sqlite3.html – HW08 due Wednesday 3/18 – Quiz 4 will be on Friday 3/20  User defined classes  Lists, dictionaries 28 14