Transcript BS1904w9A

Business Application Packages (10)

Last Time: Databases part 2
» The Relational Model
» Practical:
– Forms and Reports
– Using Microsoft Access as a relational database

Week 9 Self Study: Mail-Merge

This week:
» More uses of Databases
» Mail Merge
» Practical – putting Word and your database together
Business Application Packages: Week 10
1
Business Value of Computing


Business is driven by people
The computer rarely creates anything new
»
»
»
»

It can allow you to re-use work already done
Help you manage and store work for reference and reuse
Present information clearly
Simplify decision-taking by providing data and calculation
Repetitive tasks can be automated
» Handling Orders
» Creating bills
» Spotting trends

And information made more timely
» e.g. Web site showing stock position from database
Business Application Packages: Week 10
2
Integrating Packages


For one-off tasks, like writing a letter, a single package
is sufficient
For decision-support, you often need to combine:
» data,
» computation, and
» display




The data may be in a spread-sheet or database
Spreadsheets are good for calculation and generating
graphics
Word-processors are good for laying out reports
Presentation graphics are better in front of an audience
Business Application Packages: Week 10
3
Mail Merge

A common process that you can automate
» For example, sending a form-letter to all your customers
» Integrates word-processing with data retrieval

Works by creating a “Master document”
» Contains all the words and layout common to all letters
» and “place holder” fields to plug in variable data

Microsoft Word will take data from various sources
» Other Word documents can be rather tricky as source
» Usual source is a table (database or spreadsheet)
» We’ll use a database to extend our experience of Access
Business Application Packages: Week 10
4
Master Document



Word provides extensive help for building this
Best if you already have the source of variable data
Use Tools/Mail Merge to select the data source
» Often this will be a Query combining multiple tables,
like the “people attending seminars” one last week
» That way you can pick fields from it
» Word validates these as you go

Write the document, using “Insert Field” where you need
to plug in external data
» It’s best to use the Word toolbar to save pulling down the
menu and typing in MergeField names
» Word XP makes it easy to preview the merged result
Business Application Packages: Week 10
5
Mail-Merge/Database Practical


Using linked tables in Knight’s example
Problem: organizing seminars for conference delegates
» Delegates’ details are held in a table
» Seminar details are held in another table
» Each delegate can attend one seminar
(but we hope each seminar will attract many delegates!)
Seminars
Seminar-ref
Name
From
To
Fee
Business Application Packages: Week 10
1
Delegates
Delegate-ref
Name
Company
Address...
M Phone #
Seminar-ref
6
Seminar Confirmation Example


Start with the conference database you created
Generate a letter to each delegate confirming the
seminar chosen
» (see BS1009w9.doc for problem description)

You will need to extend the database first
» Names will need to be restructured to avoid nasties like
“Dear Marghanita Laski”
» And create a query that contains fields from both tables,
joined by the Seminar_ref value

Then create a suitable master document
» Test it out for layout and validity,
» then merge into a file to check that all letters get created
Business Application Packages: Week 10
7
Relational Database Terms


Tuple
Row
Red names are the formal ones, Blue are what we’ll use
The whole thing is a Relation or Table
53730
28719
53550
79632
51883
36453
Jones Bill W 1
Blanagan J E 1
Lake Mary
0
Rubble Barney 1
Smith Tina
0
Thomas John 1
Prime Key
Business Application Packages: 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
8
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
20000
17500
21000
30000
29000
Project
x
y
y
z
x
Completion
021125
030119
030119
020922
021125
Need to get rid of this by going to Third Normal Form
Business Application Packages: Week 10
9
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#
Business Application Packages: Week 10
M
1 Project
Project#
Completion
Project Name
10
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 “joined” table
» Still saves space
» Guarantees integrity of data

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
Business Application Packages: Week 10
11
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
» Designed mainly for a single user – upgrade when you
need a multi-user database

The Report facility in Access has been improved in
recent versions, but you may still want to use Mail
Merge
Business Application Packages: Week 10
12
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
Make query from order items and orders to show
all orders placed today;
– each item to contain customer-ref and product description
Business Application Packages: Week 10
13
Practice Examination

Goal of exam is to measure parts of the course not
covered in the assignment – mainly
» Using Access, including building queries
» Mail-merge with Word and Access

A practice paper is on the Business web-site at
http://cmg.wkac.ac.uk/courses/bs1009/bs1009me.doc

(copy attached to the hand-out)
Starts easy, gets progressively harder
»
»
»
»
Simple modification of data in the database (be accurate)
Mail-merge from an existing table
Mail-merge from new query built on a single table
.. And from query working from two tables
Business Application Packages: Week 10
14
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, hard otherwise)
An example is sending out exam results
» There is a suitable data source in:
http://cmg.wkac.ac.uk/courses/bs1009/mailmer2.xls
» Create a Word document as basis for the letter, then open
the spreadsheet as Tools/Mail Merge data source
» We are interested in rows 3 to 23, columns A to M
Business Application Packages: Week 10
15
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
Business Application Packages: Week 10
16