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

1

1

Aaron Stevens

16 March 2009

CS108 Lecture 20: Introduction to Databases and SQL

2

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

3

Storing Data: Memory

Memory is used to store programs and

• ther 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”

4

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.

3

5

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.

6

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.

4

7

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.

8

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

5

9

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’.

10

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)

6

11

Transaction Integrity

A transaction is an atomic sequence of

• perations.

– Example: booking a flight

• Select flight, reserve seat, make payment

DBMS guarantees transaction integrity: completion or failure.

12

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.

7

13

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.

14

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.

8

15

The SELECT Query

General form:

SELECT <field1>, <fields2>, … FROM <table1>, <table2>, … [WHERE <field>=<value> … ]

The minimal SELECT query requires only a list

• f fields (or * for all) and a single table.

Example:

SELECT * FROM stocks

16

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"

9

17

SQL Comparison Operators

SQL WHERE clauses can support all of the usual comparison operations. Here are the SQL comparison operators.

18

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)

10

19

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)

20

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%")

11

21

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)

22

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!

12

23

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!

24

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

13

25

Next Time: The Python DB API

Python defines a standard API (objects and methods) for interaction with databases.

– No standard implementation of this interface. – 3rd 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!

26

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!

14

27

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

28

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