1 / 21

Spring 2010 Querying Data: Understanding Databases and Information Systems

This guide covers essential concepts in querying data through databases and information systems, focusing on practical applications such as listing data from tables, creating reports, and employing the Query Wizard. It discusses how to connect tables to generate meaningful data reports involving people and events, as well as how to design queries using SQL commands. Additionally, it highlights the role of data warehouses in managing historical data and outlines techniques for data mining, enabling users to uncover patterns and insights from large datasets.

trygg
Download Presentation

Spring 2010 Querying Data: Understanding Databases and Information Systems

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. IS 2101/07---Spring 2010 IS 2101—Spring 2010 Chapter 9 Databases and Information Systems

  2. Querying data • Listing data from a single table • People • Reports • List of buildings • List of people • Listing data in related tables • Rooms listed with building full names • Events listed with all people attending IS 2101/07---Spring 2010

  3. The Query Wizard • Choose tables • Choose columns from tables • Later we’ll put restrictions on the values chosen, but not yet IS 2101/07---Spring 2010

  4. Listing All People • Pick the People table • Select which fields to include • First, middle, last names • ID • Notice that the query is stored with the table that it references IS 2101/07---Spring 2010

  5. The Result of a Query • The result obtained from a query is actually a table. • This is a virtual table, that is, it is not stored directly but is computed as needed • Under the right conditions, you can modify data in the query table and it will be reflected back in the real tables IS 2101/07---Spring 2010

  6. Reports • Reports display data in a form easier to read than the tables produced by queries • The data from a report can be arranged visually in various ways • Data for a report can be pulled from tables and queries IS 2101/07---Spring 2010

  7. Create a Report • Create a report list all people • Create a report listing all buildings • Notice that reports are stored with the table they reference IS 2101/07---Spring 2010

  8. Queries with Multiple Tables • Make a list of events and people attending them • Connect people to events using tables • Create a basic query • Look at SQL IS 2101/07---Spring 2010

  9. Connecting People to Events • We want to get the people attending various events • Look at the relationship diagram • We can get from people to events by going through the attending table • We can get there through the organizing table but that would give people organizing events IS 2101/07---Spring 2010

  10. Create a Query • Use the query wizard again • Create a simple query • Select three tables • Attending • Events • People • Select all fields in them IS 2101/07---Spring 2010

  11. Run the Query • Run the query and examine the results • Each row gives information about a person attending an event • All the information about the event and the person are included • This data is highly redundant, but, since it is generated, we don’t have the consistency problem IS 2101/07---Spring 2010

  12. Query Design View • We can hide certain fields • We can add constraints • Select start times later than 4/14/2015 IS 2101/07---Spring 2010

  13. Create a Report • Listing attendees by event • Listing events by person IS 2101/07---Spring 2010

  14. Looking at the SQL • SQL is a formally defined language for expressing queries and other database operations • SQL is used to give commands to a database • The word ‘SELECT’ begins query command • FROM lists tables that are used to get data • JOIN indicates that tables are related by foreign key to primary key • WHERE indicates further criteria IS 2101/07---Spring 2010

  15. Data Warehouses • What is a data warehouse? • Large amounts of data • Selected from existing databases • Especially suited to high-level reports • Will usually hold data collected over time • Historical reports IS 2101/07---Spring 2010

  16. Filling Data Warehouses • Data is brought in from several sources • Data may have to be staged, that is, transformed to a common format IS 2101/07---Spring 2010

  17. Querying Data Warehouses • Since there is so much data, query engines must be more flexible • Queries will involve more summary results rather than IS 2101/07---Spring 2010

  18. Information Systems • Office Support Systems • Transaction Processing Systems • Detail • Management Information System • Summary • Exception • Decision Support Systems IS 2101/07---Spring 2010

  19. Information Systems • Model Management Systems • Knowledge Based Systems • Expert systems • Fuzzy Logic • Enterprise resource planning systems IS 2101/07---Spring 2010

  20. Data Mining • Examining large amounts of data • Not using specific queries, but looking for patterns and trends IS 2101/07---Spring 2010

  21. Data Mining Techniques • Classification • Estimation • Affinity grouping • Clustering • Visualization and description IS 2101/07---Spring 2010

More Related