Database Queries Who murdered the database with the candlestick in the conservatory?

1. INFO100 and CSE100 Fluency with Information Technology Database QueriesWho murdered the database with the candlestick in the conservatory? Katherine Deibel Katherine Deibel, Fluency in Information Technology

2. Review • We have so far discussed • What a database is • What database operations do • Today, we will discuss • Databases as tools • How queries are used Katherine Deibel, Fluency in Information Technology

3. Why use a database? • Keep records of our: • Clients • Staff • Volunteers • Keep a record of activities and interventions • Keep sales records • Develop reports • Perform research • Longitudinal tracking Katherine Deibel, Fluency in Information Technology

4. Database Terminology Katherine Deibel, Fluency in Information Technology

5. Database Terminology Katherine Deibel, Fluency in Information Technology

6. Data | Information | Knowledge • Data (according to Information Science) • Unprocessed, raw information • Information • Organized, structured data that is communicated in a coherent and meaningful manner • Knowledge • Information that has been evaluated and further organized so that it can be used purposefully • Action • Applying knowledge towards achieving goals Katherine Deibel, Fluency in Information Technology

7. From Data to Action • We collect data • Information is harvested from the data • Many companies are good at collecting data • Fewer are good at harvesting information • Knowledge is elicited from the information and put into action • Database Management Systems are tools for supporting this transformation process Data Information Knowledge Action Katherine Deibel, Fluency in Information Technology

8. Database Management Systems (DMSs) The Tools for Data to Information to Knowledge to Action Katherine Deibel, Fluency in Information Technology

9. Database Management Systems • DMSs are software data tools to: • Store (tables) • Organize (sort) • Add, modify or delete • Ask questions (queries) • Produce forms and reports • Toolbox is a good analogy Katherine Deibel, Fluency in Information Technology

10. Popular DBMs • Microsoft Access • FileMaker Pro • Lotus Notes • Structured Query Language (SQL) • Microsoft SQL Server • Oracle • MySQL Katherine Deibel, Fluency in Information Technology

11. Managing a Database • Three major distinctions • Purpose of database:Operational versus Analytical • Data representation:Flat-file versus Relational • Implementation:Desktop versus Client/Server Katherine Deibel, Fluency in Information Technology

12. Selecting a DBM • Desktop databases • Oriented toward single-user applications • Reside on standard personal computers • Client / Server databases • Contain mechanisms to ensure the reliability and consistency of data • Offers security options on [subsets of] data • Oriented toward multi-user applications Katherine Deibel, Fluency in Information Technology

13. Operational vs. Analytical Operational databases • Used to track and assist in daily “business” activities • Data typically changes frequently over time • Examples • Human resources • Mailing lists • Inventory management • Accounting systems • Point of sale systems (cash registers) Analytical databases • Tend to be more static • Historical data is analyzed for patterns or trends • Often support the strategic activities of an organization • Goals may include • Predicting the future • Summarizing historical data • Prove historical assumptions Katherine Deibel, Fluency in Information Technology

14. Flat-File vs. Relational Flat-File Database • All relevant data in a single table, or series of unrelated tables • Work best for small quantities of data • Typically a person’s first databases Relational Database • Solution to data entry redundancy problems • Tables linked together queried as if one table • Linked via common fields (columns) with exactly the same data Katherine Deibel, Fluency in Information Technology

15. Flat-File Example • Weaknesses common to flat-file systems • Duplicate information is repeated redundantly • Inconsistencies in how data is entered Katherine Deibel, Fluency in Information Technology

16. Relational Database Example Katherine Deibel, Fluency in Information Technology

17. Database Tables Katherine Deibel, Fluency in Information Technology

18. Interfacing with a Relational Database Our quarry is the query Katherine Deibel, Fluency in Information Technology

19. Accessing Data in a Database • Users rarely work with the entire database • Exception are the database managers • Instead, users interact through • Forms: read and write data • Reports: read only • All of these are based on the query Katherine Deibel, Fluency in Information Technology

20. Forms • Forms allow interaction with the database in a more scripted fashion • Data is read and maybe even edited Katherine Deibel, Fluency in Information Technology

21. Reports • Reports are summaries generated from the database • Read-only Katherine Deibel, Fluency in Information Technology

22. Queries • Generate a table from other tables in the database via sequences of operations • Select Difference • Project Product • Union Join • SQL: Structured Query Language • Standard database language Katherine Deibel, Fluency in Information Technology

23. Indirect SQL • SQL sequences are usually auto-generated • Interfaces allow easy construction of SQL • We can view the generated SQL if we want Katherine Deibel, Fluency in Information Technology

24. Query from Two Tables Katherine Deibel, Fluency in Information Technology

25. Nature of the Returned Table • Some records may be editable • If the data is linked to a primary key • Generally not true for collapsed data Katherine Deibel, Fluency in Information Technology

26. When fields are editable • Relies on primary keys and the underlying intelligence of the database • Further security settings can set edit rights • Updates can be sent out to all views • Synchronization is a big issue • Editing a linked value will chance all instances Katherine Deibel, Fluency in Information Technology

27. Global Update Example • Expanded database from Lab 10 Katherine Deibel, Fluency in Information Technology

28. Consistency Matters • A good relational database • Uses IDs to connect records across tables (i.e. relationships) • Provides specific views to meet specific users' needs • Learning these skills is beyond the scope of this course • Knowing the essential ideas is part of being fluent in databases • We will discuss some basic design on Friday Katherine Deibel, Fluency in Information Technology

29. Summary • Queries, on the high-level, are the final outcome of transforming data into action • Database Management Systems provide tools for creating and manipulating queries Katherine Deibel, Fluency in Information Technology

30. Project 3 • You will get to explore a database by playing different roles at an interstellar travel agency • Astronomical cartographer • Trip planner • Planetary information broker • End consumer Katherine Deibel, Fluency in Information Technology