1 / 20

Spreadsheets & Databases: Practical Applications & Techniques

Learn about spreadsheet design, database usage, Excel demos, complex applications, style guidelines, relational databases, joins, key fields, and data modeling. Explore examples and problems with joins, projection, and restriction in this informative class.

lesterv
Download Presentation

Spreadsheets & Databases: Practical Applications & Techniques

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Spreadsheets andDatabases Class 8 LBSC 690 Information Technology

  2. Agenda • Questions • Spreadsheets • Database design

  3. What’s a Spreadsheet? • Large table containing numbers • May also contain labels to aid interpretation • Columns are named with LETTERS • Rows are named with NUMBERS • Cells are named like A4, C1, ... • Some cells are automatically calculated • Formula specified when spreadsheet is created • Values are recalculated continuously

  4. How Spreadsheets are Used • Record keeping (cassette tapes) • Calculation (income tax) • What-if analysis (cash flow) • Sensitivity analysis (exchange rate) • Goal seeking (retirement planning) • Uses continuous recalculation (“iteration”)

  5. How Spreadsheets are Used • Record keeping (cassette tapes) • Calculation (income tax) • What-if analysis (cash flow) • Sensitivity analysis (exchange rate) • Goal seeking (retirement planning) • Uses continuous recalculation (“iteration”)

  6. Spreadsheet Applications • Originally designed for financial records • Library applications • Budget • Collection development • Shelving capacity • Educational Applications • Grade records • Equipment inventory

  7. Excel Demo • Start Excel • Microsoft Office folder • Open N:\SHARE\CLASS\POSTCARD.XLS • File menu • Enter your 1997 (desired) income in cell B3 • Tax due is displayed in cell B4

  8. Excel Demo • Change the tax due • Place the cursor over B4 • Type “=B3*0.x” • “=” tells Excel this is a formula • “B3” refers to the number in cell B3 • The “x” in “0.x” should reflect your political views • 0.5 would take away half your money • Try different values in cell C3 • What kind of spreadsheet use is this?

  9. Excel Demo • Add itemized deductions • Highlight row 4 (click on 4) • Select “Row” in “Insert” menu twice • Label A4 as “Deduction amount” • Label A5 as “Taxable income” • Put the appropriate formula in B5 • Change the formula in B6 as needed • Note how it was copied from B4 with changes

  10. Excel Demo • Limit the deduction • Maximum of 50% of income or 10,000 • Search for help on “maximum” • Replace the formula in B5 with a more complicated one • You can use another cell to show a partial result

  11. When Style is Important • Too complex to visualize at once • Size • Relationships between formulas • Used by more than one person • Includes use in presentations and papers • Used for a long time • Essentially communicating to yourself

  12. Style Guidelines • Organization • Depict the solution approach visually • Group things where possible (e.g., parameters) • Build in cross-checks to discover input errors • Readability • Describe the computation • Meaningful labels help a lot • Minimize clutter

  13. Building Complex Applications • Computers keep track of detail well • But people don’t • Adopt meaningful abstractions • Organize a calculation the way you think • Use a structured process • Examples: waterfall and spiral models

  14. Relational Databases • Tables represent relations • Name, project • Name, email address, phone number • Relations can be “joined” • Name, project, email address, phone number • Relations can be “projected” • Name, email address • Relations can be “restricted” • Name = “Doug Oard”

  15. Why use Join? • Forces consistency • Doug Oard, project 18, oard@glue, 57590 • Doug Oard, project 22, oard@wam, 57590 • Limits the chance of error • Doug Oard, project 18, oard@glue, 57590 • Doug Oard, project 19, oard@glue, 57490 • Avoids lots of duplicated entry and updates • Can save a lot of storage space

  16. Problems with Joins • Data modeling for joins is complex • Taught in LBSC 670 • Joins are expensive to compute • Both in time and storage space • But it is joins that make databases relational • Projection and restriction also used in flat files

  17. Key Fields • Primary Key uniquely identifies line to join • May group several fields to get a unique key • Social security number • First and last name • “Foreign” key must appear in the other table • But it need not be unique there • Join makes a new table • Line specified by foreign key is tacked on

  18. Example of a Join on “Team” Team Team Name Team Project Name Project Chris A A Database Chris A Database Chris A B Web Chris A Database Camile A C Web Camile A Database Eileen B Eileen B Web Natalie C Natalie C Web David B David B Web Tonya C Tonya C Web Michelle Michelle Skip C Skip C Web

  19. Project to Keep Two Fields Team Team Name Project Name Project Chris A A Database Chris Database Chris A B Web Chris Database Camile A C Web Camile Database Eileen B Eileen Web Natalie C Natalie Web David B David Web Tonya C Tonya Web Michelle Michelle Skip C Skip Web

  20. Restrict to Web Pages Team Team Name Project Name Project Chris A A Database Eileen Web Chris A B Web Natalie Web Camile A C Web David Web Eileen B Tonya Web Natalie C Skip Web David B Tonya C Michelle Skip C

More Related