1 / 28

Information Retrieval from Relational Databases

Information Retrieval from Relational Databases. Chapter 7. Chapter Learning Objectives. Identify and explain the purpose of the three primary relational algebra operators Identify and explain the primary components of a Structured Query Language (SQL) statement

navid
Download Presentation

Information Retrieval from Relational 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. Information Retrieval from Relational Databases Chapter 7

  2. Chapter Learning Objectives • Identify and explain the purpose of the three primary relational algebra operators • Identify and explain the primary components of a Structured Query Language (SQL) statement • Identify the relational algebra operations achieved by a given SQL statement • Create a SQL statement to retrieve requested information from a relational database • Examine a SQL statement and the tables to which it will be applied and identify the query result • Find errors in a SQL statement • Create a Microsoft Query-by-Example (QBE) to retrieve information from relational tables • Examine a Microsoft Access QBE query and the tables to which it applies and identify the query result • Find errors in a Microsoft Access QBE query

  3. Examples of Needs for Multiple Views of One Data Set • Cash-basis versus Accrual Accounting • Weighted Average versus FIFO or LIFO • Double-Declining Balance Depreciation versus Straight Line • Foreign Currency Translation

  4. Querying/Information Retrieval Necessary components for effective querying • A database that is well-designed (e.g., fully relational) • A query developer who understands the table structures and the nature of the data in the tables • A query developer who understands the desired query output • A query developer who has good logic and reasoning skills • A query developer who knows the querying language used to retrieve information from the enterprise database

  5. Three Query Languages • Relational Algebra • Three main operators: Select, Project, Join • Provides the conceptual basis for SQL and QBE • Structured Query Language (SQL) • The user enters commands according to a pre-defined syntax to retrieve desired data. • Query By Example (QBE) • The user starts with a sample of the table(s) columns and marks the fields he or she wants to include in the answer. Defaults are available for summarizing and manipulating the data.

  6. Relational Algebra • Select • includes only certain rows in its “answer” • Project • includes only certain columns in its “answer” • Join • combines two or more tables on the basis of one or more common attributes

  7. Example Tables (Incomplete Enterprise Database)from Dunn & McCarthy (2004) working paper

  8. Relational Algebra SELECT Find the cash receipts from Customer #2 (keeping all the details of those cash receipts)

  9. Relational Algebra PROJECT Find the customer number, name, and salesperson number for all customers

  10. Join Types • Inner join • includes only the records from both tables that have the exact same values in the fields that are joined • I.e., • Outer join • includes all records from one table, and matches those records from the other table for which values in the joined fields are equal • I.e., Left Outer Join Right Outer Join

  11. Relational Algebra Inner Join Find all details of customers who have a salesperson and all available details of each customer’s salesperson

  12. Relational Algebra Left Outer Join Find all details of all sales and the cash receipt number and amount applied of any cash receipts related to those sales

  13. SQL (Structured Query Language) • Each query statement follows the same structure:SELECTattribute name(s)FROMtable name(s)WHERE criteria is met;

  14. SQL Statements and Relational Algebra • SELECT isolates columns • = relational algebra’s project • FROM identifies the table(s) involved • if >1 table, helps accomplish relational algebra’s join • WHERE isolates rows • = relational algebra’s select • also helps accomplish relational algebra’s join

  15. SQL and Relational Algebra SELECT Find the cash receipts from Customer #2 (keeping all the details of those cash receipts)

  16. SQL and Relational Algebra PROJECT Find the customer number, name, and employee number for all customers

  17. SQL and Relational Algebra Inner Join Find all details of customers who have a salesperson and all available details of each customer’s salesperson

  18. SQL and Relational Algebra Outer Join Find all details of all sales and the cash receipt number and amount applied of any cash receipts related to those sales

  19. Mathematical Comparison Operators • Queries may include mathematical comparison operators such as • = equal to • < less than • <= less than or equal to • > greater than • >= greater than or equal to • <> not equal to • Included in the WHERE clause • For text fields, A < B < C, etc.

  20. SQL Mathematical Comparison Operators Select Account#, BalanceFrom CashWhere Balance>=50000;

  21. SQL Mathematical Comparison Operators on Character Attributes Select Sale#, AmountFrom SaleWhere Employee# <> E-10;

  22. Queries with Logical Operators • Queries may include logical operators AND, OR, and NOT • AND accomplishes a set intersection – answer includes all instances that meet BOTH conditions • OR accomplishes a set union – answer includes all instances that meet one condition and all instances that meet the other condition • NOT identifies instances that do not meet one or more conditions

  23. Queries with Special Operators • BETWEEN is used to define the range limits. • The end points of the range are included Select Sale#, Amount, DateFrom SaleWhere Date BETWEEN 7/1 and 7/31;

  24. Queries with Special Operators • IS NULL is used to retrieve attributes for which the value is null. Select *From CashWhere Balance IS NULL;

  25. Queries with Special Operators • EXISTS is used to retrieve attributes for which the value is not null. Select *From CashWhere Balance EXISTS;

  26. Aggregation Functions in Queries • An aggregation function summarizes the data values within a field (column) • COUNT - the number of rows that contain a given value in the field • AVERAGE - the mean value of all rows included in the answer • SUM - the sum of all rows included in the answer • MIN - the minimum attribute value for the field • MAX - the maximum attribute value for the field

  27. Queries with Horizontal Calculations • “Horizontal” calculations combine values from different fields for each row • Should NOT be included in the same query as an aggregation function

  28. Querying Summary • Querying provides the power of the relational database model • Querying requires organized thinking and logic • You have to understand the structure of the database tables and the nature of the data in those tables. • You must identify which table(s) are needed for each query, and determine the appropriate manipulations that need to be made in the appropriate sequence • Remember to separate horizontal calculations from vertical aggregations • Comprehensive testing of queries is crucial before releasing queries for use by general users

More Related