1 / 37

Preparing to Automate Data Management

Chapter 1. Preparing to Automate Data Management. “You can use all the quantitative data you can get, but you still have to distrust it and use your own intelligence and judgment.” — Alvin Toffler. Discovering and Evaluating Sources of Existing Data.

yan
Download Presentation

Preparing to Automate Data Management

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. Chapter 1 Preparing to Automate Data Management “You can use all the quantitative data you can get, but you stillhave to distrust it and use your own intelligence and judgment.”— Alvin Toffler

  2. Discovering and Evaluating Sources of Existing Data • Identify information that organization needs to manage and organize • Might begin to see patterns that indicate how to organize data • Database management system (DBMS) • Includes: • Oracle • ColdFusion • Microsoft Access • MySQL Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  3. Assimilating the Available Information and Planning the Database • First step in database design • Determine best way to organize data into logical groups of fields • Field • Single characteristic of entity • Also called column • Record • Values in each field in table • Also called row Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  4. Assimilating the Available Information and Planning the Database (continued) • Table • Collection of fields that describe one entity • Also called entity or relation • Database • Collection of one or more tables • Relational database • Contains related tables through fields that contain identical data Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  5. Evaluating Field Values and Assigning Appropriate Data Types • Data type • Determines how to store data in field • DBMSs use different names for some data types • How do you determine which data type to assign each field? • Depends on what function you want to derive from data • Each data type has different properties Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  6. Common Data Types and Their Descriptions Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  7. The Text and Memo Data Types • Text data type • Letters and numbers • Not used in calculations or formulas • Stores maximum of 255 characters • Default for all fields created in access database • Memo data type • Store long passages of text • Displays only 64,000 characters Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  8. The Number Data Type • Stores both positive and negative numbers • Contains up to 15 digits • Use for values used in calculations Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  9. The Currency Data Type • Includes two decimal places and displays values with dollar sign • Use for monetary values Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  10. The Date/Time Data Type • Display values in format mm/dd/yyyy • Can also include time in different formats • Used in calculations if necessary Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  11. The AutoNumber Data Type • Unique to Access • Number automatically generated by access • Produces unique values for each record • Useful to distinguish two records that share identical information • Produces values of up to nine digits Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  12. The Yes/No Data Type • Assigned to fields requiring • Yes/no • True/false • On/off • Takes up one character of storage space • Make data entry easy • Check box Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  13. The Lookup Wizard Data Type • Creates fields to look up data in • Another table • Or list of values created for field • Makes data entry easy • Ensures that valid data entered into field Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  14. Selecting the Correct Data Type • Helps store correct data in correct format while using least amount of space • Eases data entry and interactivity with data • Choosing certain data types results in user-friendly interactive features • Drop-down menus • Check boxes • Hyperlinks • Correctly manipulate data Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  15. Assigning the Correct Field Size for Text Fields • Important to consider field size when assigning data types • Minimize space reserved for each record by assigning smallest data type that will store data • Be conservative when assigning field sizes • But not too conservative Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  16. Assigning the Correct Field Size for Number Fields Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  17. Dividing the Existing and Missing Data into Tables • Tables • Single most important component of database • Most databases contain • Multiple tables • Hundreds or even thousands of records • Primary key • One field that creates unique value in each record • Used to identify each record in table • May be a combination of fields Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  18. Leszynski/Reddick Naming Conventions for Database Objects Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  19. Understanding Relational Database Objects • Users can view data in tables by: • Opening table • Creating other objects • Four main objects in database • Tables • Queries • Forms • Reports Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  20. Tables • Data in relational database stored in one or more tables • View data in table • Open it and scroll through records • Most of the time, three other main database objects used to display data normally Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  21. Queries • Query • Question asked about data stored in database • Query results • Look similar to table • Fields displayed in columns • Records displayed in rows Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  22. Forms • Used to view add delete, update and print records in database • Based on table or query • Interface more attractive than table datasheet • Customize form’s appearance with instructions and command buttons • Switchboard • Form displayed when database opened • Provides controlled method for users to open objects in database Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  23. Form Based on a Table Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  24. Reports • Formatted presentation of data from table or query • Created as printout or to be viewed on screen • Data displayed by report usually based on query • Dynamic • Reflect latest data from object • Cannot be used to modify data Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  25. Accounts Receivable Report Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  26. Understanding Relational Database Concepts • Flat file database • Simple database • Contains single table of information • Relational database • Contains multiple tables to store related information • Common field • Field that appears in two or more tables and contains identical data to relate tables • Primary key in first table • Foreign key in second table Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  27. Creating Table Relationships • Take advantage of interrelated objects • Goal in good database design • Create separate tables for each entity • Ensure each table has primary key • Use common field to relate tables • Relate two (or more) tables • Query them as though they are one big table • Join • Specifies relationship between tables and properties of relationship Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  28. One-to-Many Relationships • Abbreviated as 1:M • One record in first table matches zero one or many records in related table • Primary table • One side • Related table • Many side Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  29. One-to-Many Relationship Between Customers and Prescriptions Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  30. One-to-One Relationships • Abbreviated as 1:1 • Exists when each record in one table matches exactly one record in related table Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  31. One-to-One Relationship Between Physical and Billing Addresses Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  32. Many-to-Many Relationships • Abbreviated as M:N • Each record in first table matches many records in second table • Each record in second table matches many records in first table • Junction table Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  33. Many-to-Many Relationship Between Employees and Classes Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  34. Understanding Referential Integrity • Null value • Field does not contain any value • Entity integrity • Guarantee that there are no duplicate records in table • Each record unique • No primary key field contains null values • Referential integrity • If foreign key in one table matches primary key in second table • Values in foreign key must match values in primary key Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  35. Understanding Referential Integrity (continued) • When database does not enforce referential integrity • Problems occur that lead to inaccurate and inconsistent data • Orphaned • No longer match between primary key in primary table and foreign keys in related table Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  36. Referential Integrity Errors Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

  37. Overriding Referential Integrity • Might want to override referential integrity • Intentionally change primary key • Delete parent record • Cascade updates • Change primary key value so that DBMS automatically updates appropriate foreign key values in related table • Cascade deletes Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach

More Related