Computer Applications for Business 10

1
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

2
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 cant be
  changedSo you may be stuck with 255 characters
  for a number
• Avoid spaces in field names
• Mostly it works, BUT you cant use them in an
  Expression
• Next create relationships
• Referential integrity will protect you from
  erroneous data
• Finally create the query
• Secure in the knowledge youve done everything to
  avoid processing garbage

3
Relational Database Terms

• Red names are the formal ones, Blue are what
  well use
• The whole thing is a Relation or Table
• 53730 Jones Bill W 1 03 100355 044 73 20000
• 28719 Blanagan J E 1 05 101039 172 43 18000
• 53550 Lake Mary 0 07 090952 044 02 11000
• 79632 Rubble Barney 1 11 011152 090 11 50000
• 51883 Smith Tina 0 03 091150 044 73 21000
• 36453 Thomas John 1 08 110961 044 02 12000

Tuple Row
Domain Column/Field
4
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 Name Salary Project Completion
• 120 Jones 20000 x 061125
• 122 Marx 17500 y 070119
• 222 Able 21000 y 070119
• 310 Enson 30000 z 060922
• 355 Spoto 29000 x 061125
• Need to get rid of this by going to Third Normal
  Form

5
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

6
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

7
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

8
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

9
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
  inhttp//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

10
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
• Some hints
• You can change a text box into any autoshape
• But its 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)
• Dont rely on fill to hide things, it fails on
  some printers!

This is a callout
11
Optional Exercises

• These are less important than completing the ones
  you have been set, which resemble the tasks of
  the exam
• Well do practice exams next week

12
Mail-Merge from Word Document
Optional

• The same exercise can be done purely within Word
• Make sure your data is in a table(can handle
  non-table data, but its 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

13
Excel Database Practical
Optional

• 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