Bài giảng Enterprise information systems - Chapter 7: Information Retrieval from Relational Databases

Chapter 7Information Retrieval from Relational DatabasesChapter Learning ObjectivesIdentify and explain the purpose of the three primary relational algebra operatorsIdentify and explain the primary components of a Structured Query Language (SQL) statementIdentify the relational algebra operations achieved by a given SQL statementCreate a SQL statement to retrieve requested information from a relational databaseExamine a SQL statement and the tables to which it will be applied and identify the query resultFind errors in a SQL statementCreate a Microsoft Query-by-Example (QBE) to retrieve information from relational tablesExamine a Microsoft Access QBE query and the tables to which it applies and identify the query resultFind errors in a Microsoft Access QBE query2Examples of Needs for Multiple Views of One Data SetCash-basis versus Accrual AccountingWeighted Average versus FIFO or LIFODouble-Declining Balance Depreciation versus Straight LineForeign Currency TranslationHow do we get these multiple views??????? 3Answer: Query the Data SetWhat is Querying?It is asking questions about the data in the database and manipulating or combining the data in different waysWe can isolate certain rows in tables, we can isolate certain columns in tables, we can join tables together, we can create calculations based on various data items, etc.4Querying/Information RetrievalSeveral ingredients are necessary for effective queryingA database that is well-designedIf tables are not fully relational or incompletely specified, or if conceptual model has not been correctly converted into relational form, querying will be difficult or impossibleA query developer who understands the table structures and the nature of the data in the tablesA query developer who understands the desired query outputA query developer who has good logic and reasoning skillsA query developer who knows the querying language used to retrieve information from the enterprise database5Three Query LanguagesRelational AlgebraThree main operators: Select, Project, JoinProvides 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.6Relational AlgebraSelectincludes only certain rows from a database table in its “answer”. Project includes only certain columns from a database table in its “answer”Joincombines two or more database tables on the basis of one or more common attributes 7Example Tables (Incomplete Enterprise Database)from Dunn & McCarthy (2004) working paper8Relational Algebra SELECT Find the cash receipts from Customer #2 (keeping all the details of those cash receipts)Select Cash Receipt Where Customer Number = C-2 Giving Answer9Relational Algebra PROJECTFind the customer number, name, and salesperson number for all customersProject Customer Over (Customer#, Name, SP#) Giving Answer10Join TypesInner joinincludes only the records from both tables that have the exact same values in the fields that are joinedI.e., Outer joinincludes all records from one table, and matches those records from the other table for which values in the joined fields are equalI.e., Left Outer JoinRight Outer Join11Relational Algebra Inner JoinFind all details of all customers and all available details of each customer’s salespersonJoin Customer, Salesperson Where Customer.SP# = [Salesperson.Employee Number] Giving Answer12Relational Algebra Left Outer JoinFind all details of all sales and the cash receipt number and amount applied of any cash receipts related to those salesLeft Outer Join Sale, [Sale - CashRecDuality] Where [Sale.Sale#] = [Sale - CashRecDuality.Sale#] Giving Answer13SQL (Structured Query Language)Each query statement follows the same structure:SELECT attribute name(s)FROM table name(s)WHERE criteria is met;14SQL Statements and Relational AlgebraSQL’s SELECT component isolates columnsi.e., relational algebra’s projectSQL’s FROM component is used for identifying the table(s) involvedif >1 table, helps accomplish relational algebra’s join (together with WHERE component that specifies equal fields)SQL’s WHERE component isolates rowsi.e., relational algebra’s selectalso helps accomplish relational algebra’s joinmay be left blank for single-table queries that retrieve all rows15 Find the cash receipts from Customer #2 (keeping all the details of those cash receipts)SQL and Relational Algebra SELECTSelect *From [Cash Receipt]Where [Customer Number] = C-2;(note: the brackets are needed because of spaces in the table and field names; also note * is a wild card indicating all columns should be included)16SQL and Relational Algebra PROJECTSelect Customer#, Name, SP# From Customer;Find the customer number, name, and salesperson number for all customers17SQL and Relational Algebra Inner JoinSelect *From Customer, SalespersonWhere Customer.SP# = [Salesperson.Employee Number];Find all details of all customers and all available details of each customer’s salesperson18SQL and Relational Algebra Outer JoinFind all details of all sales and the cash receipt number and amount applied of any cash receipts related to those salesSelect *From Sale LeftJoin [Sale-CashRecDuality]Where [Sale.Sale#]=[Sale-CashRecDuality.Sale#];19Mathematical Comparison OperatorsSQL Queries may include mathematical comparison operators such as = equal to greater than>= greater than or equal to not equal to (or != in some software)Mathematical comparison operators are typically included in the WHERE clause of the SQL statement, and may be used on all types of fieldsFor date fields, dates that are earlier in time are “less than” dates that are later in time.For text fields, A =50000;21SQL Mathematical Comparison Operators on Character Attributes Select Sale#, AmountFrom SaleWhere SalesRep# E-10;22Queries with Logical OperatorsQueries may include logical operators AND, OR, and NOTAND accomplishes a set intersection – answer includes all instances that meet BOTH conditionsOR accomplishes a set union – answer includes all instances that meet one condition and all instances that meet the other conditionNOT identifies instances that do not meet one or more conditions 23Queries with Special OperatorsBETWEEN is used to define the range limits.The end points of the range are includedSelect Sale#, Amount, DateFrom SaleWhere Date BETWEEN 7/1 and 7/31;24Queries with Special OperatorsIS NULL is used to retrieve attributes for which the value is null.Select *From CashWhere Balance IS NULL;25Queries with Special OperatorsEXISTS is used to retrieve attributes for which the value is not null.Select *From CashWhere Balance EXISTS;26Aggregation Functions in QueriesAn aggregation function summarizes the data values within a field (column)COUNT summarizes the number of rows that contain a given value in the fieldAVERAGE computes the arithmetic mean value of all rows included in the answerSUM computes the arithmetic sum of all rows included in the answerMIN identifies the minimum (lowest) attribute value for the fieldMAX identifies the maximum (greatest) attribute value for the field27Queries with Horizontal Calculations“Horizontal” calculations mathematically combine values from different fields for each rowHorizontal calculations should NOT be included in the same query as an aggregation functionOne query may perform a horizontal calculation and another query that builds on the first query may perform the aggregation function, or vice versaThe “correct” order for the queries depends on the goal28Relational Algebra SELECT in QBE Cash Receipts from Customer C-229Relational Algebra “Select” QBE Example: Cash Receipts from Customer C-230Relational Algebra SELECT in QBE Cash Receipts from Customer C-231Relational Algebra SELECT in QBE Cash Receipts from Customer C-2Enter =“C-2” as Criteria in the Customer Number field32ResultRelational Algebra SELECT in QBE Cash Receipts from Customer C-233Relational Algebra PROJECT in QBECustomer#, name, salesperson#34Relational Algebra PROJECT in QBECustomer#, name, salesperson#35Relational Algebra PROJECT in QBECustomer#, name, salesperson#36Relational Algebra PROJECT in QBECustomer#, name, salesperson#Result37Relational Algebra Inner Join in QBE: All details of customers and their salespeople38Relational Algebra Inner Join in QBE: All details of customers and their salespeople39Relational Algebra Inner Join in QBE: All details of customers and their salespeople40Relational Algebra Inner Join in QBE: All details of customers and their salespeopleResult41Relational Algebra Outer Join in QBE Details of all sales, related cash receipts42Double-click on the join lineRelational Algebra Outer Join in QBE Details of all sales, related cash receipts43Click OKClick on appropriate join typeRelational Algebra Outer Join in QBE Details of all sales, related cash receipts44Notice change in join lineRelational Algebra Outer Join in QBE Details of all sales, related cash receipts45Relational Algebra Outer Join in QBE Details of all sales, related cash receipts46Relational Algebra Outer Join in QBE Details of all sales, related cash receiptsResult47QBE with Mathematical Comparison OperatorCash Account# and Balances >=$50,00048QBE with Mathematical Comparison OperatorCash Account# and Balances >=$50,00049QBE with Mathematical Comparison OperatorCash Account# and Balances >=$50,000Result50QBE with Mathematical Comparison on Character Attribute: Sales Not made by E-1051QBE with Mathematical Comparison on Character Attribute: Sales Not made by E-1052QBE with Mathematical Comparison on Character Attribute: Sales Not made by E-10Result53AND operator in QBE: Sales made before July 31 by Sales Rep E-1054AND operator in QBE: Sales made before July 31 by Sales Rep E-1055Enter selection criteria on same line to accomplish logical “AND”AND operator in QBE: Sales made before July 31 by Sales Rep E-1056AND operator in QBE: Sales made before July 31 by Sales Rep E-10Result57OR operator in QBE: Sales made before July 31 OR by Sales Rep E-1058OR operator in QBE: Sales made before July 31 OR by Sales Rep E-1059Enter selection criteria on separate lines to accomplish logical “OR”OR operator in QBE: Sales made before July 31 OR by Sales Rep E-1060OR operator in QBE: Sales made before July 31 OR by Sales Rep E-10Result61Aggregation SUM and Special Operator BETWEEN in QBE: Total Sales between July 15 and July 3162Bring only the fields you need into the query gridAggregation SUM and Special Operator BETWEEN in QBE: Total Sales between July 15 and July 3163Enter Criteria with BETWEEN operatorClick on summation symbol to add “Total” line to query grid (used for aggregations)Aggregation SUM and Special Operator BETWEEN in QBE: Total Sales between July 15 and July 3164Total line defaults to “Group By” for each field; Change the Amount field to “Sum” and change the Date field to “Where”Aggregation SUM and Special Operator BETWEEN in QBE: Total Sales between July 15 and July 3165Aggregation SUM and Special Operator BETWEEN in QBE: Total Sales between July 15 and July 3166Aggregation SUM and Special Operator BETWEEN in QBE: Total Sales between July 15 and July 31Result67Horizontal Calculation in QBE: Inventory-Sale Line Item Extension68Save query so that fields will be available to the Expression Builder; then click on the magic wand to start the Expression BuilderHorizontal Calculation in QBE: Inventory-Sale Line Item Extension69Horizontal Calculation in QBE: Inventory-Sale Line Item Extension70Horizontal Calculation in QBE: Inventory-Sale Line Item Extension71Horizontal Calculation in QBE: Inventory-Sale Line Item Extension72Horizontal Calculation in QBE: Inventory-Sale Line Item Extension73Horizontal Calculation in QBE: Inventory-Sale Line Item ExtensionResult74Querying SummaryQuerying provides the power of the relational database modelOnce you unlock the mystery of query construction, you can tap into the wealth of information that is at your fingertips in a well-designed relational databaseQuerying requires organized thinking and logicYou must 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 sequenceSome people find it helpful to organize their thinking by considering what relational algebra operators are needed even though the relational algebra language is rarely usedManually calculating the query result using a representative data sample is also very helpful for identifying query errorsRemember to separate horizontal calculations from vertical aggregationsComprehensive testing of queries is crucial before releasing queries for use by general users75Chapter 7End of Chapter