Chapter 4: Organizing and Manipulating the Data in Databases

2. Chapter 4:Organizing and Manipulating the Data in Databases • Introduction • Creating Database Tables in Microsoft Access • Entering Data In Database Tables • Extracting Data From Databases: Data Manipulation Languages • Recent Database Advances and Data Warehouses

3. Creating DatabaseTables in Microsoft Access • Database Management Systems • An Introduction to Microsoft Access • Creating Database Tables • Creating Relationships

4. Database Management Systems (DBMS) • Overview • Not a database • Separate software system • Functions • Enables users to utilize database information more efficiently • Examples • Access, SQL Server, mySQL, Oracle, DB2

5. Introduction to Microsoft Access • A popular relational DBMS • Used by many businesses and individuals • Used for small database applications

6. Microsoft Access – Opening Screen

7. Creating Database Tables – Defining Record Format • Field Name • Names assigned to the data fields • Data Type • Specified for each data field • Identifies how to store the data – field properties • Description • Optional field • Defines record structures

8. Creating Database Tables – Opening Screen

9. Creating Database Tables – Record Format

10. Creating Relationships • Purpose • Link tables together • Enable users to create multi-table reports • Steps in Creating Relationships • Select tables • Link the tables

11. Creating Database Relationships – Linking Tables

12. Creating Relationships – Multitable Relationships

13. Creating Records • Utilize datasheet view to input data

14. Ensuring Valid and Accurate Data Entry • Data Definition Language (DDL) • Enables users to define record structure • Define individual fields of each record

15. Tools for Data Validation • Proper Data Types for Fields • Input Masks • Limit data to specific formats • Default Values • Data fields of new records

16. Tools for Data Validation • Drop-Down Lists • Validation Rules • Create rules than limit range of values that may be entered • Referential Integrity • Deleting of information disallowed when it would disrupt references

17. Drop-Down List Example

18. Validation Rule Example

19. Creating Referential Integrity

20. Study Break #1 • All of the following are examples of DBMSs except: • Access • Oracle • DB2 • SQL

21. Study Break #1 - Answer • All of the following are examples of DBMSs except: • Access • Oracle • DB2 • SQL

22. Study Break #2 • An example of a validation rule is: • An input value must be an integer • An input value must also have a default value • An input value must be between 0 and 40 • You cannot delete parent records that have child records associated with them

23. Study Break #2 - Answer • An example of a validation rule is: • An input value must be an integer • An input value must also have a default value • An input value must be between 0 and 40 • You cannot delete parent records that have child records associated with them

24. Tips for CreatingDatabase Tables and Records • Design first • Create tables and records last • Name tables systematically • Use conventional tbl prefixes • Use mnemonic names for data fields • Assign correct data types to data fields

25. Tips for CreatingDatabase Tables and Records • Data fields that link tables must be the same data type • Limit the size of text data fields to reasonable lengths • Use input masks

26. Extracting Data From Databases • Schema • All information in a database • All relationships of the tables • Map of entire database • Subschema • Subset of the schema

27. Creating Select Queries • Queries • Create customized subschemas • Dynaset • Dynamic subset of a database • Created by queries • Data Manipulation Language (DML)

28. Query Example

29. Creating Select Queries • One-Table Select Queries • Creates a dynaset • Based on: • Criteria determining which records to include • Criteria determining which fields to include from those records • Single or Multiple Criteria

30. Select Query Example

31. Multi-Table Select Query Example

32. Multi-Table Select Query Example

33. Creating Action Queries • Delete queries • Append queries • Sum a column • Update queries • Make-table queries

34. Query Wizard Queries • Simple query Wizard • Crosstab queries • Find-Duplicates queries • Find-unmatched queries

35. Query Wizard Screen

36. Guidelines for Creating Queries • Spell accurately and be case sensitive • Specify AND and OR operations correctly • Tables must be joined properly • Name queries systematically • Choose data fields selectively

37. Extracting Data From Databases • Structured Query Language (SQL) • Example of SQL Instructions

38. Extracting Data From Databases • Online Analytical Processing (OLAP) • Complex, multidimensional data analysis • Pivot tables • Data Mining • Utilize a set of data analysis and statistical tools • Identify relationships, patterns, or trends

39. Cloud Computing • Form of Internet-based Computing • Software provided through the Internet • Processing occurs on a Web of computers • Expands IT capabilities • Database-As-A-Service (DAAS) • Outsourcing of databases • Backup Services

40. Data Warehouses • Pools of data from separate applications • Characteristics • Free of errors • Defined uniformly • Span longer timeframe than transaction systems • Optimized data relationships