Databases

Databases are collections of information; our study repeats a theme: Tell the computer the structure, and it can help you!

Why Study Databases?

Some of us want to compute, but all of us want information …

Much of the archived information is in tables

Databases enhance applications, e.g. Web

Once you know how to create databases, you can use them to personal advantage

Databases introduce interesting ideas

Stone Age Databases

Before relational databases (the kind we study) there were only “flat files”

Structural information was difficult to express

All processing of information was “special cased” -- custom programs were needed

Information repeated; difficult to combine

Changes in format of one file means all programs that ever process that file must be changed … adding ZIP codes

Relational Databases

Information is stored in tables

Tables store information about entities -- things or stuff … keep entities of one kind

Entities have characteristics called attributes

Tables are tuples (rows or records) of attributes (columns or fields )

Every row must be unique, identified by a key

Relationships -- associations among the data values are stored

A Table

Tables have names, attributes, tuples

Redundancy Is Very Bad

Not every assembly of tables is a good database -- repeating data is bad

Replicated data can differ in its different locations, e.g. multiple addresses can differ

Inconsistent data is worse than no data

Keep a single copy of any data, and if it is needed in multiple places, associate it with a key, and store key rather than the data

“You can look it up”

When looking for information, a single item might be the answer, but a table is more likely

“Who is taking FIT100”? Table of students

“Whose mile run time £ 4:00?” Runner table

“Who won 2003 Grammy for ‘Best New Artist?” A table containing only a single row

“Who is president of UW?” Empty Table

Tables From Tables

There are five fundamental operations on tables to create tables:

Select -- pick rows from a table

Project -- pick columns from a table

Union -- combine two tables w/like columns

Difference -- remove one table from another

Product -- create “all pairs” from two tables

Select Operation

Select creates a table from the rows of another table meeting a criterion

Select_from Example On Hire < 1993

Project

Project creates a table from the columns of another table

Project Last, First From Example

Union

Union (written like addition) combines two tables with same attributes

PoliticalUnits = States + Provinces

Difference

Difference (written like subtraction) removes 1 table’s rows from another

Eastern = States - WestCoast

Product

Product (written like multiplication) combines columns and pairs all rows

Colors = Blues x Reds

Join

Join (written like a bow tie) combines rows (like x) if common field matches

Homes = States Students

DB Operations

The five DB Operations can create any table from a given set of tables

All modern database systems are built on these relational operations

Join is not primitive, but can be built from 5

Join, select and project are used most often

The operations are not usually used directly, but are used indirectly from other languages


	Databases are collections of information; our study repeats a theme: Tell the computer the structure, and it can help you!


	Some of us want to compute, but all of us want information …
			Much of the archived information is in tables
			Databases enhance applications, e.g. Web
			Once you know how to create databases, you can use them to personal advantage
			Databases introduce interesting ideas


	Before relational databases (the kind we study) there were only “flat files”
			Structural information was difficult to express
			All processing of information was “special cased” -- custom programs were needed
			Information repeated; difficult to combine
			Changes in format of one file means all programs that ever process that file must be changed … adding ZIP codes


	Information is stored in tables
			Tables store information about entities -- things or stuff … keep entities of one kind
			Entities have characteristics called attributes
			Tables are tuples (rows or records) of attributes (columns or fields )
			Every row must be unique, identified by a key
			Relationships -- associations among the data values are stored


	Not every assembly of tables is a good database -- repeating data is bad
			Replicated data can differ in its different locations, e.g. multiple addresses can differ
			Inconsistent data is worse than no data
			Keep a single copy of any data, and if it is needed in multiple places, associate it with a key, and store key rather than the data


	When looking for information, a single item might be the answer, but a table is more likely
			“Who is taking FIT100”? Table of students
			“Whose mile run time £ 4:00?” Runner table
			“Who won 2003 Grammy for ‘Best New Artist?” A table containing only a single row
			“Who is president of UW?” Empty Table


	There are five fundamental operations on tables to create tables:
			Select -- pick rows from a table
			Project -- pick columns from a table
			Union -- combine two tables w/like columns
			Difference -- remove one table from another
			Product -- create “all pairs” from two tables


	Select creates a table from the rows of another table meeting a criterion
			Select_from Example On Hire < 1993


	Project creates a table from the columns of another table
			Project Last, First From Example


	Union (written like addition) combines two tables with same attributes
			PoliticalUnits = States + Provinces


	Difference (written like subtraction) removes 1 table’s rows from another
			Eastern = States - WestCoast


	Product (written like multiplication) combines columns and pairs all rows
			Colors = Blues x Reds


	Join (written like a bow tie) combines rows (like x) if common field matches
			Homes = States Students


	The five DB Operations can create any table from a given set of tables
			All modern database systems are built on these relational operations
			Join is not primitive, but can be built from 5
			Join, select and project are used most often
			The operations are not usually used directly, but are used indirectly from other languages