Computer Applications for Business (7)

1. Computer Applications for Business (7) • Last Time • Review of Module • Business Graphics and Excel charts • The module assignment • Practical work – Currency conversion spreadsheet • This week: Databases part 1 • Two ways to approach databases: • From the requirement to hold and access information • As a way to map the information requirements of the business on to a 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 Compaction”

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://www.wkac.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 • 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 up 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 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 • See attached example from my CD list • May also offer: • Filters, e.g. to select CDs with Emma Kirkby as soloist • Report-writing tools Not unlike 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 12 of Knight Personal Computing for Business covers setting up a database • Her example is with QBE (Query by example) • KAC has Microsoft Access, so we’ll work with that • This means some terminology will be different • Set up the GARAGES table, thinking about the design • For example, do you need some kind of unique key? • See document on the web-site to save typing • 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 215 (copy attached) • Save your database for future modification

16. Limits of Flat files • Every record scanned in every sort operation • Multiple scans for complex sorting such as Work within category within Composer • Updates can involve 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? 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 a profit of Z over two years” • Very efficient way to get marks! • Body of report • Where you back up with facts and logic the assertions you made in the Executive Summary