Computer Applications for Business 8

1
Computer Applications for Business (8)

• Last week (except for EM)
• Zip file practical
• Databases part 1 Two ways to approach
  databases
• From requirement to hold and access information
• To map the businesss info requirements on to a
  computer
• Microsoft Access Practical single table with
  queries
• This week Databases part 2
• The Relational Model
• Practical using Microsoft Access as a relational
  database
• Completing Currency Converter (production
  spreadsheet)
• Final Assignment workshop (due this Wednesday)

2
Week 7 Garages Practical

• Used a single table
• One record per vehicle
• Fields chosen to contain key characteristics of
  car
• Field properties ensure they can hold the data
• Built, then modified table contents and layout
• Should have imported External Data from
  spreadsheet
• Exercise 1(d) involved adding to the table
  design
• How many created queries to select and sort
  contents?
• One criterion is easy to define
• unless you need to match part of a field
• Multiple criteria focus the selection further
• We can go on to produce Reports and Forms

3
Concluding Single Table Practical

• Exercise 2 Creating Queries
• Best done with Query Wizardprompts you to
  choose source and which fields to include
• Modify the design to apply selection criteria
• Selecting what you put on the Criteria line
• One criterion is easy to define
• unless you need a wildcard 2(d) and 2(g)
• Multiple criteria focus the selection further
  Exercise 3 on p.204
• (a) selects when either criterion is satisfied on
  one field OR
• (b) selects when criteria are satisfied in both
  fields AND
• Exercise 4 producing a calculated field

4
Advantages of Database Managers

• Where you have only one table, it could be stored
  in a flat file using an application like Excel
• However, performance can be a problem
• Every record scanned in every sort operation
• Multiple scans for complex sorting such as
  Date-acquired within Make within Garage
• Gets very slow when database size grows
• Updates can involve making second copy of entire
  database do you have room on the disk?
• Database managers (DBMS)
• build indexes rather than sorting the records
  themselves
• save data when it changes, leave most of DB
  unchanged

5
Limits of Flat file Databases

• With a single table, we may need to duplicate
  data
• Proper DBMS can relate different tables of data,
  e.g.
• Have database of purchases by customer
• Also have Customers on Address database
• How do we see whats been selling in PO postcode
  area?

PRODUCT CUSTOMER ADDRESS Zummats Aardvark
Co Banbury OX18 2AC Grommets Bloggs
Ltd Fareham PO15 1JB Zummats Bloggs
Ltd Fareham PO15 1JB Wickets P Vole
Inn Heckmondwyke HX13 3PV
PRODUCT CUSTOMER Grommets Bloggs Ltd Wickets P
Vole Inn Zummats Aardvark Co Zummats Bloggs Ltd
CUSTOMER ADDRESS Aardvark Co Banbury OX18
2AC Bloggs Ltd Fareham PO15 1JB P Vole
Inn Heckmondwyke HX13 3PV
6
Relational Databases

• As with simple database, data elements held in
  tables
• Also known as relations because
• each data element relates the row (record, tuple)
  its in,
• with the column (field, domain) that describes
  its category
• Database usually contains several tables, for
  example
• Purchase table saying what each customer has
  bought
• Customer address table
• Database Management System lets you manipulate
  tables
• Selecting records
• Using one table to look up items in another
• Well do this in Knights Conference exercise
  (p.220 ff)

7
Relational Database Notes

• In a relational database management system, the
  table-based structure of data is maintained, and
  the efficiency benefits of indexing are exploited
  to the full. Typically, many tables are stored
  within a single database, and a powerful query
  language, SQL, is provided.
• Access is by selecting a row (or set of rows),
  and then using named fields, so programs are not
  sensitive to addition of new fields.
• There is the concept of a View of a table, which
  is a named table consisting of a subset of the
  columns. This supports powerful Security
  facilities. For example, most users could have a
  view of the payroll table excluding salary and
  assessment grades, with only privileged users
  having access to the full table.
• To return to the question of which products sell
  in Hampshire, we can Join the sales and customer
  tables, combining records that share a common
  customer name. Where we have two sales records
  with the same customer, the address value will be
  duplicated into the new table. Thus if we SELECT
  on the address field of the joined table, wed be
  left with a table of two rows, for the products
  bought by our only Portsmouth area customer. We
  could even work on a view of the table that
  excluded the customer name for example we might
  choose to do this to avoid having too many people
  knowing the identity of customers who are buying
  goods that are in short supply this list could
  be very valuable to a competitor who has plenty
  of such goods.

8
Relationships Between Tables

• One to One
• One to Many
• Many to Many

9
Structured Query Language

• Relational Database Management Systems (RDBMS)
  implement SQL (usually pronounced sequel)
• Examples
• SELECT sales.PRODUCT sales.CUSTOMERFROM lttable
  database namegtWHERE sales.PRODUCTGrommetORDER
  BY sales.CUSTOMER
• JOIN tables that share a column to produce a new
  one

PRODUCT CUSTOMER ADDRESS Zummats Aardvark
Co Banbury OX18 2AC Grommets Bloggs
Ltd Fareham PO15 1JB Zummats Bloggs
Ltd Fareham PO15 1JB Wickets P Vole
Inn Heckmondwyke HX13 3PV
10
How Database Managers Help

• When you JOIN two tables, there will be
  redundancy in the table so produced
• However, if youre only interested in SELECTed
  records, the DBMS doesnt actually need to create
  the full table
• This saves space and processing time
• Access goes one better, and saves you writing SQL
• Can SELECT, ORDER and JOIN graphically, and
  using wizards
• There is a View SQL option when you need it
  provides an easy way to sort records exactly as
  you want
• Well cover this next week

11
Joining and Selecting

• When you join tables, for example with a Query,
  the result set contains the records that appear
  in BOTH tables (and only those records)
• You can also Select in Access it uses the
  Criteria line in Design View of the query
• Thus the criterion PRODUCTZummats would exclude
  the lines shown in pink from the query results

PRODUCT CUSTOMER ADDRESS Zummats Aardvark
Co Banbury OX18 2AC Grommets Bloggs
Ltd Fareham PO15 1JB Zummats Bloggs
Ltd Fareham PO15 1JB Wickets P Vole
Inn Heckmondwyke HX13 3PV
12
Design Considerations

• What level of granularity is needed for fields?
• Sometimes fairly obvious, e.g. splitting out
  Postcode
• But how much do you need to chop up a name?
• Surname title rest-of-nameBodger Mr Eric
  Owen MA JP
• Surname title given-name1 given-name2
  given-name3 rest-of-name suffix-lettersClinton
  ex-President William Jefferson III MA LlB
• Similarly with addresses
• Problems arise if you get it wrong in either
  direction
• database can be full of empty fields
• or you cant access the data by the component you
  want
• How do you make primary keys unique?
• Often leads to artifices like Customer numbers

13
More Design Considerations

• What are the appropriate tables?
• If two groups of data are in a 11 relationship,
  it doesnt much matter whether you put them in
  one or two tables
• If theres a one-to-many relationship, its
  clearly beneficial to separate them, thus
  avoiding duplication
• Where theres many-to-many relationship, you need
  to create a table that bridges between them

14
Data Validation

• We saw techniques for validating data in the
  practical
• Can reject non-numeric characters in a numeric
  field
• Use lists to help enter members of an enumerated
  set
• Or set ranges for numerical values
• Sometimes we need to validate consistency
• If car registered in 1992, itll probably have J
  or K prefix
• This is where you think about programming a macro
• Best to detect errors at data-entry stage if
  possible GIGO
• Design must define any consistency rules
• And mechanisms for enforcing them
• Dont forget overrides where appropriate
  (examples?)

15
Database Creation

• Once the database design is complete,Need to
  prime the tables with initial data
• May be able to import it from a previous
  system(as you did with the Garages table)
• Or use skilled people to type direct into tables
• If not, build forms to accept data in
  human-centred way
• Complex validation rules may require programming
• Inspect multiple fields on form and check
  consistency
• Access supports Macros to do that
• Database may also include generated fields
• Well see why later

16
Forms and Reports

• Data isnt much use while its locked into a
  database
• We need to access selected elements to get value
• Usual approach is to
• Select records according to specified
  criteria(or maybe just one criterion)
• Order them as specified
• Lay them out to be helpful to user
• Generate summaries where appropriate
• Further Practical please do in your own time
• You should already have completed Exercises 1 to
  4 in Knight (1999) Chapter 8
• Optional Report design exercises (starting on
  p.209)
• Form design (p.212) Validation rules (p.215)

17
Search Precision

• Lets assume were building a simple phone book
• Query takes form of user typing in name
• Database system returns all record containing the
  name
• Potential Problems
• Case If I type smith and database contains
  Smith how do we guarantee a match?
• Different spellings Dupre, Du Pre, Dupré
  MacPherson, Macpherson, McPherson
• CALLUP system generated a hidden, standardized
  field
• Squeezed out blanks, hyphens, apostrophes etc
• Converted Mc to mac
• Folded all names to upper-case
• Did same thing to the target the user typed in

18
Fuzzy Searching

• Some databases go further, and apply very strong
  transformations, for example
• Remove vowels and repeated consonants
• Patterson and Peterson both go to PTRSN
• Smith Smyth and Smithe all go to SMTH
• Can even classify consonants, e.g. S and Z become
  S (so Pieterzoon also becomes PTRSN)
• Usually search for exact match first, then near
  match
• Design choice whether to keep transformed
  versions
• Keeping them uses disk space,but lets you sort
  the table for efficient searching
• Generating them on the fly is expensive in
  processingand doesnt support efficient searching

19
Joining Tables
Students

• To join tables together, they must share a field
• In this example, it is Module
• Module is a unique key in the Courses table
• Joining Courses and Students gives a table with
  record for each intersection

Courses
Joined on Module
20
Relational Database Practical

• Knights example 7 from p.220 involves linked
  tables
• 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!)

Note ambiguity Name can mean SeminarName or
DelegateName these would be better field names
Delegates Delegate-ref Name Company Address... Pho
ne number Seminar-ref
Seminars Seminar-ref Name From To Fee
1
M
21
Relational Database Practical (2)

• Knights specification brings a problem
• How do I write to someone if the name is in one
  piece?
• OK in the window Mr H Huntington, BSc
• But we cant derive Dear Mr Huntington from
  that
• So lets chop the name into components
• Thats what I did in the table you can import

22
How to do it Exercise 7

• (a) Design the Seminars table according to
  instructions
• Be sure to specify Seminar ref as primary key
• Enter the data as given
• (b) Set up the Delegates table as specified (not
  quite)
• First download spreadsheet and import it to
  Access table as External data
• Get Access to use delegate refs as a primary key
• Fix up the format as specified
• (c) Use the Relationships tool to link
  the tables using Seminar ref
• Select enforce referential integrity (Ill
  explain later)
• (d) Finally, produce a query using fields from
  both tables

23
Access Summary

• Access is a reasonable inexpensive Relational
  DBMS
• Defines fields in a fairly intuitive way
• Lets you work graphically
• Provides most relational features
• Saves you writing SQL for yourself (but you can
  View it)
• Key terms
• Table where the data is actually kept
• Query view of table(s) manipulated according to
  rules you set joins, selects, sorts
• Less good for shared databases
• Good design environment before move to Oracle or
  DB2

24
Summary

• Data stored is used to model the business
• Tables hold the main players suppliers,
  products, customers, employees
• Applications then work on the appropriate data
  elements
• Avoid duplication by using multiple tables
• Youll learn next year how to design suitable
  structures
• At minimum, separate customer/supplier
  demographics from details of their dealings with
  you
• Link tables using a data element common to both
• This needs to be unique in one of the tables..
• ..where it acts as a key
• This is the place to filter to exclude irrelevant
  material

25
Optional Database Exercise

• These exercises are taken fromKnight, J (1999)
  Computing for Business
• Learn about Forms by doing Exercises 6(a)-6(c) on
  pp.212-4, and 7(e) on p.222
• And about Reports in Exercises on pp.209-211 and
  222
• Of these, only p.222 is included in the hand-out

26
Assignment Workshop

• Key points
• Work out the Business Case for yourself
• Get a cash-flow chart for ideal case and for
  contingency
• Then design your report
• Executive Summary
• Make the executive want to buy your proposal
• For example If you invest X it will pay for
  itself in Y months, yielding an extra profit of
  Z over six years
• Very efficient way to get marks!Especially if
  you back it up with a graph
• Body of report
• Where you back up with facts and logic the
  assertions you made in the Executive Summary

27
Business Report Structure

• Title Page
• Executive Summary Vital in this assignment
• Contents always a good idea
• Introduction
• Body of Report
• Conclusions Recommendations
• Bibliography Give credit to your sources
• (Appendix) Not needed here

28
Marking Criteria

• Exec. Summary 20
• Analysis 20
• Spreadsheet 20
• Structure integration 20and following the
  spec
• Credibility of Report 20including accuracy of
  expression

29
Currency Converter

• Example of spreadsheet used in Production
• Main learning outcomes
• MAX function
• Use of IF function to cope with empty and zero
  cells
• Need to choose solutions e.g. between MAX and
  IF
• VLOOKUP (theres also an HLOOKUP)
• Protection of cells and sheets
• How to indicate protection visually