| Project 2d must be time stamped Friday (2:00AM) for Friday turn-in | |||
| Please keep up with the reading | |||
| 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 | |||
| Tables have names, attributes, tuples |
| 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 | |||