Transcript Forms - University of Washington

The Information School of the University of Washington
More Forms
INFO/CSE 100, Fall 2006
Fluency in Information Technology
http://courses.washington.edu/info100/
Nov 29
fit100-24-forms © 2006 University of Washington
1
The Information School of the University of Washington
Due on Friday @ Start of Class
• Homework 4
• Also on Friday - we will go over midterm 2
and project 2b
Nov 29
fit100-24-forms © 2006 University of Washington
2
The Information School of the University of Washington
Readings and References
• Reading
» Fluency with Information Technology
• Chapter 16, Case Study in Database Design
• References
» MS Access Help files
• keyword “form”
Nov 29
fit100-24-forms © 2006 University of Washington
3
Name
Price
ISBN
ID
Title
Books
∞
WrittenBy
Phone
∞
Authors
∞
PublisherOf
1
Recall the ERD (entity relationship diagram)
from last class
Publishers
ID
Phone
Name
The Information School of the University of Washington
Link one book with many authors?
• We DO want:
» to link each book to one or more authors
• We DON'T want
» to specify extra fields (author1, author2,
author3,…)
• this is wasteful and limits the max number of authors
» to specify each book entry several times, naming a
different author in each row
• this duplicates all the other information about the book
Nov 29
fit100-24-forms © 2006 University of Washington
5
The Information School of the University of Washington
Add a cross-reference table!
• Refine the design so that it includes another table
that is a book-author cross reference
» Each entity in the table is a single cross reference
•
•
Attribute: ISBN
Attribute: Author ID
» Primary key becomes both Foreign keys combined
• Now we can break the many-to-many relationship
into two 1-to-many relationships that we already
know how to implement
Nov 29
fit100-24-forms © 2006 University of Washington
6
The Information School of the University of Washington
Define new cross-reference entities
Name
Price
ISBN
ID
Title
Books
1
∞
ISBN
Nov 29
Book/Author
∞
Phone
1
Authors
AuthorID
fit100-24-forms © 2006 University of Washington
7
The Information School of the University of Washington
book-author table
Nov 29
fit100-24-forms © 2006 University of Washington
8
The Information School of the University of Washington
Define the new relationships
Nov 29
fit100-24-forms © 2006 University of Washington
9
Define a query that uses the relationship
Query By Example
actual SQL
The Information School of the University of Washington
Get the new view of the data
• Notice that this view has redundant data
» That's okay, because we are not storing it this way, just presenting
it
» The redundant items (Alex, Another Press) came from a single
entry in a table – they are guaranteed to be identical
Nov 29
fit100-24-forms © 2006 University of Washington
11
Name
Price
ISBN
ID
Title
Books
∞
WrittenBy
Phone
∞
Authors
∞
PublisherOf
1
Now we've implemented this entire schema.
Publishers
ID
Phone
Name
View: All Books from “Another Press”
The Information School of the University of Washington
View: All Books by Alex
Nov 29
fit100-24-forms © 2006 University of Washington
14
View: All info about a given ISBN
The Information School of the University of Washington
Views as Tables
• Recall that the result of a query is a table
• We have been presenting the table to the
user in simple tabular form
Nov 29
fit100-24-forms © 2006 University of Washington
16
The Information School of the University of Washington
But tables are not pretty …
Users need help
understanding what they
are looking at and what
they can do with it
Nov 29
fit100-24-forms © 2006 University of Washington
17
The Information School of the University of Washington
Front end and Back end
• Front end
» We present the data to the user with some sort
of Graphical User Interface
• Simple tabular display as we have been doing
• MS Access provides Forms and Reports for GUIs
• Web pages
• Back end
» The database stores the data in tables
» We use queries to construct new "virtual"
tables or views
Nov 29
fit100-24-forms © 2006 University of Washington
18
The Information School of the University of Washington
Forms
A form is primarily used to enter or display data in a database
The designer controls what it looks like and how it works,
so it can be tailored to specific needs
Nov 29
fit100-24-forms © 2006 University of Washington
19
The Information School of the University of Washington
A Form is just a Face for a table
• The form lets the designer arrange the data, label
it, provide some control over events, etc
» the presentation
» multiple presentations are possible depending on the
specific needs of each user
• Underlying data comes from a table or a query
» the content
» single source of data ensures consistency
Nov 29
fit100-24-forms © 2006 University of Washington
20
The Information School of the University of Washington
How does a form get built?
The Form wizard can help get you started.
Nov 29
fit100-24-forms © 2006 University of Washington
21
But you probably want to tweak it …
Design
The Information School of the University of Washington
But forms are not very compact …
Users like to have reports
densely packed with information
and logically arranged …
Nov 29
fit100-24-forms © 2006 University of Washington
23
The Information School of the University of Washington
Reports
•
A Report is another face for a table (or query)
• The report lets the designer arrange the data, label it,
provide some control over events, etc
» the presentation
» multiple presentations are possible depending on the specific
needs of each user
• Underlying data comes from a table or a query
» the content
» single source of data ensures consistency
Nov 29
fit100-24-forms © 2006 University of Washington
24
The Information School of the University of Washington
How does a report get built?
The New Report wizard can build a
complete report for you.
Nov 29
fit100-24-forms © 2006 University of Washington
25
The Information School of the University of Washington
But this wizard is kind of naïve …
Nov 29
fit100-24-forms © 2006 University of Washington
26
You might want to use the
Report Wizard instead since
it gives you more control.
Better looking report, but you still probably want to tweak it …
But you probably want to tweak it …
The Information School of the University of Washington
Explore the Design capabilities
•
•
•
•
Properties of the various controls can be set
Controls and labels can be moved around
Images and patterns can be applied
Totals, averages, subtotals etc can be
calculated
• Information can be grouped by selected
fields
• Etc, etc – there is a lot of flexibility in how
these reports get generated
Nov 29
fit100-24-forms © 2006 University of Washington
31
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 29
fit100-24-forms © 2006 University of Washington
32
The Information School of the University of Washington
Questions
• In our wine review web site, what would the
relationship be between a particular bottle of wine
and the wine maker?
»
»
»
»
Nov 29
1 to 0
1 to 1
1 to many
many to many
fit100-24-forms © 2006 University of Washington
33
The Information School of the University of Washington
Questions
• If we had two tables: WINE and WINE_MAKER
and the schema looked like this:
» WINE: wine_id, wine_name, wine_type
» WINE_MAKER: maker_id, maker_name, city, phone
Which table will take which primary key as a foreign
key to build this relationship?
Remember the relationship is:
1 WINE_MAKER to many WINE
Nov 29
fit100-24-forms © 2006 University of Washington
34
The Information School of the University of Washington
Questions
• Can I create two different forms that update the
same information? Explain why this is/is not
possible.
Nov 29
fit100-24-forms © 2006 University of Washington
35