Database systems in 21 minutes database a structured collection of data provides an abstract view of data separated from how it s stored in a file system analogous to how file systems abstract from physical devices uniform access to information provides centralized control can guarantee important properties consistency security integrity can reduce redundancy Relational Database Management Systems e g MySQL Postgres SQLite Oracle Sybase DB2 a database is a collection of tables each table has a fixed number of columns each column is an attribute common to all rows and a variable number of rows each row is a record that contains data isbn 1234 4321 2468 2467 2466 1357 1111 title MySQL TPOP Ruby Java Javascript Networks Practical Ethics author DuBois K P Flanagan Flanagan Flanagan Peterson Singer price 49 95 24 95 79 99 89 99 99 99 105 00 25 00 4320 C Prog Lang K R 40 00 Relational model simplest database has one table holding all data e g Excel spreadsheet relational model data in separate tables related by common attributes e g custid in custs matches custid in sales schema content and structure of the tables books isbn title author price custs custid name adr sales isbn custid date price qty stock isbn count extract desired info by queries query processing figures out what info comes from what tables extracts it efficiently Sample database books 1234 4321 2468 2467 custs 11 22 33 44 sales 4321 2467 2467 4321 stock 1234 4321 2468 2467 isbn title author price MySQL TPOP Ruby Java DuBois K P Flanagan Flanagan 49 95 24 95 79 99 89 99 custid name adr Brian Bob Bill Bob Princeton Princeton Redmond Palo Alto isbn custid date price qty 11 22 11 33 2012 02 28 2012 01 01 2012 02 05 2012 02 05 isbn count 100 20 5 0 45 00 60 00 57 00 45 00 1 10 3 1 Database system organization browser HTTP network connection HTML DB client SQL query response table DB server ACID the central properties of a database system Atomicity all or nothing all steps of a transaction are completed no partially completed transactions Consistency each transaction maintains consistency of whole database Isolation effects of a transaction not visible to other transactions until committed Durability changes are permanent survive system failure consistency guaranteed Retrieving data from a single table SQL Structured Query Language is the standard language for expressing queries all major database systems support it general format select column names from tables where condition select from books select name adr from custs select title price from books where price 50 select from books where author Flanagan select author title from books where author like F select author title from books order by author select author count from books group by author select author count as n from books group by author order by n desc result is a table Multiple tables and joins if desired info comes from multiple tables this implies a join operator to relate data in different tables in effect join makes a big table for later selection select title count from books stock where books isbn stock isbn select from books sales where books isbn sales isbn and books author like F select custs name books title from books custs sales where custs id sales custid and sales isbn books isbn select price count as count from books where author like F group by author order by count desc MySQL open source relational database system www mysql com LAMP Linux Apache MySQL P Perl Python PHP command line interface connect to server using command interface mysql h publicdb u bwk p type commands read responses show databases use bwk show tables select now version user source cmdfile these commands are specific to MySQL Creating and loading a table create table create table books isbn varchar 15 primary key title varchar 35 author varchar 20 price decimal 10 2 load table from file tab separated text load data local infile books into table books fields terminated by t ignore 1 lines fields have to be left justified terminated clause must be single character not whitespace multiple blanks are NOT treated as single separator can also insert one record at a time insert into books values 2464 AWK Flanagan 89 99 Item types INT of several sizes FLOAT DOUBLE DECIMAL CHAR VARCHAR BLOB binary large object of several sizes TEXT of several sizes ENUM e g M F SET DATE TIME Other statements generic SQL ought to be the same for all db systems though they are not always insert into sales values 1234 44 2008 03 06 27 95 update books set price 99 99 where author Flanagan delete from books where author Singer MySQL specific other db s have analogous but different statements use bwk show tables describe books drop tables if exists books custs SQLite an alternative www sqlite org small fast simple embeddable no configuration no server single cross platform database file most suitable for embedded devices cellphones web sites with modest traffic rapid processing 100K hits day 10 msec transaction times ad hoc file system or format replacement internal or temporary databases probably not right for large scale client server high volume web sites gigabyte databases high concurrency SQLite is not designed to replace Oracle It is designed to replace fopen Program interfaces to MySQL original and basic interface is in C about 50 functions other interfaces build on this most efficient access though query complexity is where the time goes significant complexity in managing storage for query results API s exist for most other languages Perl Python PHP Ruby C Java can use MySQL from Excel etc with ODBC module basic structure for all API s is db handle connect to database repeat stmt handle prepare an SQL statement execute stmt handle fetch result until tired disconnect db handle Python version import sys fileinput def main db mysql connect host publicdb cs princeton edu user bwk db bwk passwd xx print Enter query q sys stdin readline while q db query q res db store result r res fetch row while len r 0 print r r res fetch row print Enter query q sys stdin readline main CGI version import cgi mysql try form cgi FieldStorage db mysql connect host publicdb cs princeton edu user bwk db bwk passwd form passwd value q form sql value TODO fix injection attack db query q res db store result r res fetch row while len r 0 print r r res fetch row except print oh dear HTML cgi script form METHOD POST enctype multipart form data ACTION http www cs princeton edu bwk temp cgi input type password name passwd text size