Databases III

A. Review Product and Natural Join Operations

Find T × U, and find T natural join U, given the tables below:

Table U
name favoriteColor
Bert green
Nell red
Ken blue
Table T
name age
Bert 5
Nell 6
Ken 7

B. SQL Functions

SQL functions include count, avg, sum, min, max (and, if you love statistics, stddev). Equivalent functions are provided in spreadsheets, but with SQL we can also combine the functions with select conditions.

Examples:

  1. What's the average weight of a poodle?
  2. What's the maximum cost of a female pet?
  3. How many critters do we have in the rodent department?

C. Database Tools

Database software may provide tools for:

  1. Data integrity (what's that?): attribute types in table definitions; relationships between tables, foreign key references.

    Data integrity tools can't prove that data are correct — that's too hard — but can sometimes prove that changes to the data would be incorrect and disallow them.

  2. Query design tools and "wizards" (QBE: Query By Example)
  3. Forms for input

    Design a form for input of pets.

  4. Reports for output.

    Design a report to show pets with their departments and managers Sorting in reports; in SQL (order by … asc, order by … desc)

D. Review: Advantages of Database Systems

Single-user database systems like MS Access and Openoffice.org Base provide:

  1. Elimination or reduction of redundancy
  2. Powerful query language
  3. Data integrity safeguards (not guarantees)

Multi-user (enterprise-scale) database systems provide even more. Numerous commercial products including Oracle, IBM DB2, MS SQL Server; also high-quality free products including PostgreSQL, MySQL. Their additional advantages include:

  1. Concurrency — support for multiple simultaneous users.
  2. Transactions — "all or nothing" series of actions.
  3. Security — fine-grained control over user access to data.

E. Database Systems and the Internet

Database systems power internet commerce. Programming languages, such as Python or Java, can be integrated with database systems: the program sends SQL statements to the database server, and processes their results, possibly writing a web page in HTML. This provides information for the millions. It also reminds us of the need for proper security in database systems, since any computer system on the internet is subject to intrusion attempts.