Transcript Intro to Databases - University of Washington

The Information School of the University of Washington
Database Intro
INFO/CSE 100, Fall 2006
Fluency in Information Technology
http://courses.washington.edu/info100/
Nov 27
fit100-23-databases © 2006 University of Washington
1
The Information School of the University of Washington
Readings and References
• Reading
» Fluency with Information Technology
• Chapter 14, Introduction to Database Concepts
• References
» Access Database: Design and Programming
• by Steve Roman, published by O'Reilly
Nov 27
fit100-23-databases © 2006 University of Washington
2
The Information School of the University of Washington
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
Nov 27
fit100-23-databases © 2006 University of Washington
3
The Information School of the University of Washington
Relational Databases
• Information is stored in tables
» Tables store information about entities
» Entities have characteristics called attributes
» Each row in a table represents a single entity
• Each row is a set of attribute values
• Every row must be unique, identified by a key
» Relationships -- associations among the data
values are stored
Table structure = schema
Table contents = instance
Nov 27
fit100-23-databases © 2006 University of Washington
4
The Information School of the University of Washington
A Table in a Database
Tables have names, attributes {fields}, entities {rows}
instance
Schema for Example table:
ID
number unique number(Key)
Last
text
person’s last name
First
text
person’s first name
JobCode number current position
Hire
date
first day on job
...
schema
Nov 27
fit100-23-databases © 2006 University of Washington
5
The Information School of the University of Washington
Two tables in a database
Nov 27
fit100-23-databases © 2006 University of Washington
6
The Information School of the University of Washington
Redundancy in a database is Very Bad
• Not every assembly of tables is a good database
• Repeating data is a bad idea
» Replicated data can differ in its different locations, e.g.
multiple addresses can differ
•
•
Inconsistent data is worse than no data
Cut down on the typos and mis-keyed entries
» Keep a single copy of any data
•
•
Reduces memory and data processing costs
if it is needed in multiple places, associate it with a key and
store key rather than the data
» Effort to update is high
Nov 27
fit100-23-databases © 2006 University of Washington
7
The Information School of the University of Washington
Relationships between tables
Nov 27
fit100-23-databases © 2006 University of Washington
8
The Information School of the University of Washington
“You can look it up”
• When looking for information, a single
item might be the answer, but a table is
more likely
» Which employees live in Kirkland?
• Table of employees
» Who is taking INFO/CSE 100?
• Table of students
» Whose mile run time  4:00?
• Table of runners
Query to a database (set of tables) produces a new table
Nov 27
fit100-23-databases © 2006 University of Washington
9
The Information School of the University of Washington
Select Operation
• Select creates a table from the rows of another
table meeting a criterion
Select * from Perms Where Hire < 1993
Nov 27
fit100-23-databases © 2006 University of Washington
10
The Information School of the University of Washington
More Select Operation
• Select creates a table from the columns of
another table
Select Last, First From Perms
This is a select from 9 dimensions to 2 dimensions
Nov 27
fit100-23-databases © 2006 University of Washington
11
The Information School of the University of Washington
Union
• Union combines two tables with same attributes
Select * From Perms Union Select * From Temps
Nov 27
fit100-23-databases © 2006 University of Washington
12
Join
• Combines rows if common field matches
Select * From Perms inner join JobCodes on
Perms.JobID = JobCodes.JobID
The Information School of the University of Washington
DB Operations
• These various DB Operations can create any
table from a given set of tables
• All modern database systems are built on these
relational operations
• The operations are not usually used directly, but are
used indirectly from other languages
• Structured Query Language (SQL) is the
language that we talk to the database in
Nov 27
fit100-23-databases © 2006 University of Washington
14
The Information School of the University of Washington
Database Structure
• A database contains one or more tables
» Tables include entities with attributes
» There are relationships defined between the entities in
the various tables
» Retrieve information from the tables using queries
» Create GUI front ends (forms and reports) for users
• First, design the database or create the schema
» What are the entities?
» What are the attributes of each entity? Are they
atomic?
» What are the relationships between tables?
Nov 27
fit100-23-databases © 2006 University of Washington
15
The Information School of the University of Washington
Build a Library Database Schema
• What are the entities?
• What are the attributes of each entity? Are they
atomic?
• What are the relationships between tables?
Nov 27
fit100-23-databases © 2006 University of Washington
16
Name
Price
ISBN
ID
Title
Books
∞
WrittenBy
Phone
∞
Authors
∞
PublisherOf
1
Publishers
ID
entity-relationship diagram for Library database
Phone
Name
The Information School of the University of Washington
Create a new database
Nov 27
fit100-23-databases © 2006 University of Washington
18
The Information School of the University of Washington
Create a new table in the database
Nov 27
fit100-23-databases © 2006 University of Washington
19
The Information School of the University of Washington
Creating a table in Design view
Nov 27
fit100-23-databases © 2006 University of Washington
20
The Information School of the University of Washington
Entering Table Data
Nov 27
fit100-23-databases © 2006 University of Washington
21
The Information School of the University of Washington
Build another table
Nov 27
fit100-23-databases © 2006 University of Washington
22
The Information School of the University of Washington
Add publisher ID to books
Nov 27
fit100-23-databases © 2006 University of Washington
23
The Information School of the University of Washington
Create the link between the tables
Nov 27
fit100-23-databases © 2006 University of Washington
24
Name
Price
ISBN
ID
Title
Books
∞
WrittenBy
Phone
∞
Authors
∞
PublisherOf
1
Publishers
Hey presto, we have a database!
ID
Phone
Name
The Information School of the University of Washington
Two tables with a relationship
Nov 27
fit100-23-databases © 2006 University of Washington
26
The Information School of the University of Washington
Create a query
Nov 27
fit100-23-databases © 2006 University of Washington
27
The Information School of the University of Washington
The query produces a new (virtual) table
Nov 27
fit100-23-databases © 2006 University of Washington
28
The Information School of the University of Washington
Select particular columns
Nov 27
fit100-23-databases © 2006 University of Washington
29
The Information School of the University of Washington
Select particular rows
Nov 27
fit100-23-databases © 2006 University of Washington
30
The Information School of the University of Washington
SQL behind the scenes
Nov 27
fit100-23-databases © 2006 University of Washington
31
The Information School of the University of Washington
Summary
• Databases : built on tables
» Tables represent entities
» Tables/Entities have attributes (fields)
» Tables have a primary key (unique to that table)
• Related tables are “linked” using primary keys
• Structured Query Language (SQL) used to ask
questions of database
» SQL typically “called” from other programming
languages
» Can limit rows, columns, and join tables
Nov 27
fit100-23-databases © 2006 University of Washington
32
The Information School of the University of Washington
Questions
• A(n) ______ is a unique identifier for any
row in a database table.
• The ______ operation takes rows from a
table to create a new table.
Nov 27
fit100-23-databases © 2006 University of Washington
33
The Information School of the University of Washington
Questions
• For the following, either indicate that the field is
atomic or divide the field to make the result
atomic.
Field
StudentNo
Phone
Name
Class
City
DOB
Nov 27
Contents
47385633
(206) 555-1212
Maria Murray
INFO100 : AB
Seattle, WA 98115
September 26, 1983
fit100-23-databases © 2006 University of Washington
34
The Information School of the University of Washington
Questions
• Suppose I wanted a database to run a wine review
web site.
» What would some entities be?
» What would some attributes be of those entities?
Nov 27
fit100-23-databases © 2006 University of Washington
35