Computer Applications for Business (7)

1. Computer Applications for Business (7) • Last Time • Review of Module • Wrapping up Business Graphics and Excel charts • The module assignment • Practical work – Currency conversion spreadsheet • This week: Databases part 1 • An aside on Zip files • Two ways to approach databases: • From the requirement to hold and access information • To map the information requirements of the business to computer • We’ll cover both, but focus on using a personal database • Practical use of Microsoft Access XP • Assignment Workshop

2. 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 (there’s also an HLOOKUP) • Protection of cells and sheets • How to indicate protection visually

3. Compressing Files • How many bytes do you need for a page of text? • 50 lines of 70 characters is 3,500 • But a lot of them are (repeated) spaces • e is much more common than q or z • We could encode repeated characters, • use fewer bits for e • and more for q and z • Compressing this way can save half the space • Valuable when making back-ups • Or when files must pass over a network • Algorithms based on Huffman and Lempel-Ziv Compaction (or Welch’s later enhancement) Morse code does this

4. Packaging Files • It’s useful to group files into a package • Ensures that interrelated files stay together, • saving effort in attaching to e-mails for transmission, • or downloading from a web-site • Most common package is “Zip” file • File-type is .zip • Combines packaging with compression • XP lets you look at zip file in Explorer, looking like Folder • Easy to extract by dragging files into the required folder • Be careful: • you can look at an “inner” file without saving it, but make sure you don’t use them in the Zip folder

5. Zip File Practical • Go to BS1904 area on http://www2.winchester.ac.uk/bm/ • Download sample.zip and save it in “My Documents” • Remember to right-click and Save Target As • What happens if you left-click instead? • Find the file you downloaded in Windows Explorer • How big is it? • Open this zip file to look inside the package • How many files are there in it? • Extract the files into a folder of My Documents • How much space do they take up now? How do you create one? Find an easy way to get this?

6. Databases

7. Storing and Finding Data • Many of the things we do with computers have a manual equivalent • Paper documents are put into folders • Folders put into hanging files in cabinets • Works well when there’s a single way to classify subject, for example, filing letters by customer/supplier • Computer analogy is files in folders on disks (hierarchy) • For looking up information with an obvious key (such as name), we might use a card-index • What if there are different ways to refer to the files? • Could look up letters by date, addressee, project… • With paper, this can require you to file duplicate copies

8. Avoiding Redundant Data • Rather than keep duplicate copies sorted differently, we could: • Sort the collection appropriately before each lookup • As with the Paramount card filing system • Or the Hollerith cards used in business from 1890 to 1960s • build multiple indexes to a single collection • For example, file each letter by date and sequence • Then build indexes for addressee, project, sender… • Only drawback is that human now has to find item in index, then follow the link to the required document • Both these approaches are easier with computers • Sorting can be fast, and by any defined field • Following an index can be automatic, without user effort

9. Terminology • Record: Basic unit of collected information • For example, an address-card, or a recipe • Field: Component of a record • Usually a component that is present in most records • For example: Name, Address, Postcode, phone number • Key: Field used to identify a record • For example, the name field on an address record • Index: List of keys with pointers to their records • Similar to the index of a book • Can have multiple indexes to same collection of data • For example, by name, or by Post-code

10. Databases • Until 1970s, each application would have its own data structures • Structure was optimized for the particular application • Made it very difficult to integrate applications • Databases introduced make the structure general • Based on properties of the data to be held • Rather than by the program that is to use it • CIMA definition • “A database is a file of data structured in such a way that it may serve a number of applications without its structure being dictated by any one of those applications, the concept being that programs are written round the database rather than files being structured to meet the needs of particular programs.”

11. Business Models • With the idea of a general database came the desire to use it to model business information • So you could review business processes, and capture the data needed for each one • A sale is to a customer, and • involves exchange of product for money • It ties in with billing and stockholding processes • To achieve this, the business needed to agree on fundamental elements of data • Normally communicated through a “data dictionary” • Dictionary gives properties of data, referenced by a name • Application programs look up structure in data dictionary

12. Flat File database • Examples are Works databases (Microsoft and Claris) • Logically consists of a single table in one DOS file: • one record per row • one column per field • Usually offers • sorting by any column (where this has any meaning) • “form view” to help you input data and look at detail • “list view” to summarize or print multiple records • Could do this for a list of CDs (work, performer…) • May also offer: • Filters, e.g. to select CDs with Madonna as soloist • Report-writing tools Very like an Excel sheet

13. Record 1 Record 2 Record 3 Name Address Date of birth field field field Form and Table Views (From Jane Knight, Personal Computing for Business, p.100) Name Address Date of birth 1 2 3

14. Database Design • Identify the data to be held in each record • Determine how to split the record into fields • Do we want each line of address to be a separate field? • If so, may have to reserve fields that will usually be blank • For each field, we need to define: • A name (descriptive but short and allowed by software) • The type of data it will contain, for example: • numbers or currency (to be usable in calculations) • dates or times • general text (any printable characters) • binary data (such as scanned pictures, sounds…) • Counters managed by the database itself • Validation rules where appropriate (no prices in the date)

15. Microsoft Access Practical • Chapter 8 of Knight Computing for Business covers setting up a database • Set up the GARAGES table, thinking about the design • For example, do you need some kind of unique key? • Get the data from Excel sheet on the web-site, then fix up the field definitions to match the instructions • Note that Access provides some extra facilities: • You can provide drop-down lists for enumerated values • Easy definition of validation rules • Carry on with exercises up to page 203 (copy attached) • Save your database for future modification

16. Limits of Flat files If you store your data in an ordinary table, as in Excel: • Every record scanned in every sort operation • Multiple scans for complex sorting such as Date-acquired within Make within Garage • Updates can involve making second copy of entire database • Gets very slow when database size grows • Hard to relate different databases, for example: • Have database of purchases by customer • Customers are also on Address database • How do we see what’s been selling in PO postcode area? More on this next week 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

17. Assignment Workshop Key points: • Work out the Business Case for yourself • Get a cashflow 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 do this it will cost X and pay for itself in Y months, yielding an extra profit of Z over six years” • Very efficient way to get marks (esp. if backed with graph)! • Body of report • Where you back up with facts and logic the assertions you made in the Executive Summary

18. Using Turnitin • You should have received an e-mail from • To sign on, use your University e-mail address and the temporary password supplied, for example: • F.j.nerk@winchester.ac.uk • Wot8Funn1pw • The system will make you change your password to one you can remember, and let you update your details • You are now set up to submit your report when it’s finished • Turnitin will eventually generate a “Similarity report” highlighting any content that matches stuff in its database • It’s not a perfect process, so don’t expect 0% similarity