Web programming Units WEB1P and WEB2P

Databases and SQL

What is a database?

A database is a structured collection of records held in a computer system. The name for the description of the structure of a database is a schema. The most common form of database is a relational database. Although underpinned by a mathematical model, a relational database basically consists of a set of tables, each consisting of rows and columns. Each column in a table may be of a different type. Each row (or tuple) is a set of data and usually corresponds to a single record contained within the database. Some columns contain data that stores relationships between tables. The column or columns whose values are intended to match up with rows of another table are called foreign keys. If a set of columns can be used to uniquely identify a row in a table, those columns are called the primary key.

• Wikipedia on databases

SQL

SQL (structured query language) is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. It includes commands for extracting data from a database (SELECT), modifying records in the database (INSERT, UPDATE, DELETE), as well as manipulating the structure of the database (CREATE, ALTER, DROP). SQL is supported by virtually all relational database management systems (DBMS) including industry leaders such as Oracle, Microsoft SQL Server, IBM's DB2 and Sybase, plus open source products such as MySQL, PostgreSQL and Apache Derby. Unfortunately, each implements a slightly different version of SQL, with each supplier often providing additional functions above and beyond the SQL core.

• Wikipedia on SQL
• W3Schools tutorial on SQL

Key concepts

Concepts about databases that you need to be familiar with include:

• tables
• keys, primary keys, foreign keys
• data types
• relations
• SQL (SELECT, INSERT, UPDATE, DELETE)
• transactions

Specific DBMSs

• MySQL (reference manual)
• Java DB (Derby)