Transcript BS1904wA

Computer Applications for Business (10)

Last week: Using Databases
» Queries combining multiple tables
» Mail Merge – expanding documents with query results

This week:
»
»
»
»
»
Wrap-up of Database work
Practical – Seminar confirmation letters
Mail-Merge with Spreadsheet data
An aside on presentation graphics
Completing practicals
BS1904 Week 10
1
Lessons learnt from your peers


Where external data exists, create table by importing it
Get table design right before doing anything else
» When you create a query, it can imply a relationship
» Once a field is in a relationship, it can’t be changed
So you may be stuck with 255 characters for a number

Avoid spaces in field names
» Mostly it works, BUT you can’t use them in an Expression

Next create relationships
» Referential integrity will protect you from erroneous data

Finally create the query
» Secure in the knowledge you’ve done everything to avoid
processing garbage
BS1904 Week 10
2
Relational Database Terms


Red names are the formal ones, Blue are what we’ll use
The whole thing is a Relation or Table
53730
28719
Tuple
Row 53550
79632
51883
36453
Jones Bill W 1
Blanagan J E 1
Lake Mary
0
Rubble Barney 1
Smith Tina
0
Thomas John 1
Primary Key (unique)
BS1904 Week 10
03
05
07
11
03
08
100355
101039
090952
011152
091150
110961
044
172
044
090
044
044
73
43
02
11
73
02
20000
18000
11000
50000
21000
12000
Domain
Column/Field
3
Redundancy in Databases

One of the goals of a database is to reduce redundancy
» If you store a piece of information in two places,
– it wastes space
– and creates the risk that the copies will get out of step

Most business records do involve redundancy:
Emp#
120
122
222
310
355

Name
Jones
Marx
Able
Enson
Spoto
Salary Project
20000 x
17500 y
21000 y
30000 z
29000 x
Completion
061125
070119
070119
060922
061125
Need to get rid of this by going to Third Normal Form
BS1904 Week 10
4
Reducing Redundancy

One approach is to look for functional dependency
between fields:
» Emp# and Name
» Project and Completion date

Can then split these between separate tables
» As we did with Delegates and Seminars
Employees
Emp#
Name
Salary
Project#
BS1904 Week 10
M
1 Project
Project#
Completion
Project Name
5
Using the Database

We often want a view of chunks of the original large
table, complete with redundancy. But…
» Usually only selected rows
» and often only a selection of columns

So we only need to ask the DBMS to reconstruct a small
part of the conceptual table
» Still saves space
» Guarantees integrity of data (did that frustrate you last week?)

With Access, we used Queries to do this work
» SQL is the underlying language for selection/sorting
» You can inspect the SQL generated by Access by using
the View menu
BS1904 Week 10
6
Extracting Access Data

Access is a cheap but powerful database tool
» Lets you do most of the things expensive relational
database packages can do
» Has a standard interface (ODBC) to communicate with
other programs
» If you need to upgrade to (say) Oracle or SQL Server,
ODBC helps with the migration

Microsoft has improved Report generator in Access
» Do the exercises in Week 8 to practise Reports
» If you want to generate certain fixed multi-page reports,
Mail Merge may be a feasible alternative
BS1904 Week 10
7
Handling Customer Orders

As we saw, most businesses need tables for:
»
»
»
»

Customer records (name, address, contact, customer-ref)
Orders (customer-ref, order-ref, date)
Order items (order-ref, product-ref, quantity)
Products (product ref, description, price)
Another example might be to confirm orders by letter
»
»
»
»
Each letter must be correctly addressed
Must list all items included in the order
Information is scattered amongst the tables
Generate report from order items and orders to show
all orders placed today – reporting beats mail-merge here
BS1904 Week 10
8
Mail-Merge from Spreadsheet

All you really need for Mail Merge is tabular data
»
»
»
»

Can come from a Database Table
Or an “on the fly” table like an Access Query
A spreadsheet
Even data from another Word document
(easy with tables, very hard otherwise)
An example is sending out exam results
» There is a document and suitable data source in:
http://www2.winchester.ac.uk/bm/courses/bs1904/
» We are interested in rows 3 to 23, columns A to M –
name that area first (e.g. call it results)
» Use the mail-merge wizard to perform the merge
BS1904 Week 10
9
Aside on Presentation Graphics



Single Hons will cover PowerPoint with Mike Davies
Many tools are common to all Office Applications
Example: the drawing tools
» Generate vector graphics in your files (fairly economical)
» Standard “autoshapes” for arrows, flowcharts
» Also text boxes and “callouts”
This is a callout
 Some hints
» You can change a text box into any autoshape
» But it’s hard to add text to most other drawing objects
» Use “No Fill” to avoid obscuring objects behind shape
(filling with white looks similar, but obscures them)
» Don’t rely on fill to hide things, it fails on some printers!
BS1904 Week 10
10
Optional Exercises
These are less important than completing
the ones you have been set, which
resemble the tasks of the exam
We’ll do practice exams next week
BS1904 Week 10
11
Optional
Mail-Merge from Word Document

The same exercise can be done purely within Word
» Make sure your data is in a table
(can handle non-table data, but it’s hard to get right)

Create a Data Source document
» An easy way is to Copy the data from your Excel sheet
» When you paste into Word, it will create a table

Now create a Master document to use the data
» Probably best to start from the example built before
(saving under a new name)
» Go through the Mail Merge routine as usual
BS1904 Week 10
12
Optional
Excel Database Practical

This work all uses the Data pull-down menu
» Instructions in Practice.doc page 18
(see Learning Network or module web-site)

To create and manipulate a list of books
»
»
»
»
»
Open an Excel worksheet and enter the field names
Type given list of books under the field names
Sort the records on different fields
Filter the records by various criteria
Use a pivot table to display and summarize the data
BS1904 Week 10
13