1 / 40

Chapter 4: Organizing and Manipulating the Data in Databases

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.

rhiannond
Download Presentation

Chapter 4: Organizing and Manipulating the Data in Databases

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 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

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

  3. 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

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

  5. Microsoft Access – Opening Screen

  6. 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

  7. Creating Database Tables – Opening Screen

  8. Creating Database Tables – Record Format

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

  10. Creating Database Relationships – Linking Tables

  11. Creating Relationships – Multitable Relationships

  12. Creating Records • Utilize datasheet view to input data

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

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

  15. 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

  16. Drop-Down List Example

  17. Validation Rule Example

  18. Creating Referential Integrity

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

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

  21. 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

  22. 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

  23. 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

  24. 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

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

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

  27. Query Example

  28. 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

  29. Select Query Example

  30. Multi-Table Select Query Example

  31. Multi-Table Select Query Example

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

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

  34. Query Wizard Screen

  35. 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

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

  37. 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

  38. 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

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

More Related