General topics - Databases

Databases store data in the form of tables. Each table contains data about particular entities in the form of records (one record represents one entity), and each particular piece of information about that record is stored in a field. A table may have a primary key, which is any field for which each record must have a different value. For instance, every customer in a table must have a unique customer number. The surname field of the table can't be the primary key since two customers may have the same surname.

A Flat file database stores all information in one large table. The opposite to this is ...

A Relational Database contains several linked tables. Links between tables are called relationships and rely on tables having fields in common.

Object-oriented database - a relational database that uses advanced search techniques and connection to other software (e.g. connection to software for displaying movie or audio clips).

One-to-one relationships involve primary key fields in both tables (e.g. value X in one linked field can correspond only to value A in the other field).

One-to-many relationships allow duplicates in field data (e.g. value X in one field can correspond to values A, B or C in the other linked field). One linked field must be the primary key of the table.

Many-to-many relationships allow duplicate fields in both fields (e.g. any value X, Y, Z in one field can correspond to values A, B, C in the other linked field). Achieved by creating two one-to-one relationships "back-to-back" with a third table (a junction table).

Querying a database:
Menu query � You are offered a list of options in a menu.
Query by example � Specify the fields and data values to be found.
Query Language � Use Structured Query Language (SQL) to retrieve and manipuate records.

Database Management System (DBMS) is used to store, access and manipulate database information e.g.

• IBM (DB2)
• Computer Associates (Advantage)
• Oracle (Oracle Database 10g)
• Microsoft (SQL Server, Microsoft Access)
• Sybase (Sybase Adaptive Server Enterprise, SQL Anywhere Studio, Sybase IQ)

Examples in which an Internet database server can be used:

• Accessing company sales data remotely
• Internet banking
• Web storefronts i.e. buying and selling goods using an onscreen catalogue
• Web search engines

Open Database Connectivity (ODBC) standard created by The Open Group to allow operating systems to access databases from a variety of vendors (Microsoft, Oracle etc.) i.e. standard database interface. ODBC is often used with server-side languages. When you create an entry in ODBC, you must register the database in ODBC and specify a data source name (DSN) which is a text string that applications use to access the data source.

The Java Database Connectivity is similar but uses Java. Both use a Common Gateway Interface (CGI) to connect to the server database.

Updating a database:

• Locally - Updating a database installed on the same system.
• Over a LAN using a client program such as Access.
• Over the web through a CGI application. Includes applications such as search engines.

Connecting to a web database

The database contains data in one or more files. The SQL instructions that access this data are called Business Logic, and they pass the results to the client in an easy-to-read fashion called the presentation. This can be done with web forms etc.

Manipulating databases can be done via a 1-tier model, a 2-tier model or an N-tier (usually 3-tier) model.

Tier 1		Tier 2		Tier 3
The web browser presents a request for information to the web server. It displays any response from the web server to the user.		The web server (using HTTP) accepts requests for information from browser and casts them into SQL queries which it presents to the database server. It accepts answers from the server in the form of records and then passes them to the browser.		The database server holds the data and responds to SQL commands
The client's browser interrogates the database server directly and presents the information to the user.				The database server still holds the database.
In a 1-tier model, all three layers are combined into one. Typically, this would be found in an offline database, such as Microsoft Access.

CGI scripts that interrogate databases must have the correct permissions to do so. Online databases can be marked as read-only, or CGI scripts can have permissions to write to them as well.