760 likes | 925 Views
Chapter 7. Information Retrieval from Relational Databases. 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
E N D
Chapter 7 Information Retrieval from Relational Databases
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
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 How do we get these multiple views???????
Answer: Query the Data Set • What is Querying? • It is asking questions about the data in the database and manipulating or combining the data in different ways • We 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.
Querying/Information Retrieval Several ingredients are necessary for effective querying • A database that is well-designed • If 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 impossible • 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
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.
Relational Algebra • Select • includes only certain rows from a database table in its “answer”. • Project • includes only certain columns from a database table in its “answer” • Join • combines two or more database tables on the basis of one or more common attributes
Example Tables (Incomplete Enterprise Database)from Dunn & McCarthy (2004) working paper
Relational 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 Answer
Relational Algebra PROJECT Find the customer number, name, and salesperson number for all customers Project Customer Over (Customer#, Name, SP#) Giving Answer
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
Relational Algebra Inner Join Find all details of all customers and all available details of each customer’s salesperson Join Customer, Salesperson Where Customer.SP# = [Salesperson.Employee Number] Giving Answer
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 Left Outer Join Sale, [Sale - CashRecDuality] Where [Sale.Sale#] = [Sale - CashRecDuality.Sale#] Giving Answer
SQL (Structured Query Language) • Each query statement follows the same structure:SELECTattribute name(s)FROMtable name(s)WHERE criteria is met;
SQL Statements and Relational Algebra • SQL’s SELECT component isolates columns • i.e., relational algebra’s project • SQL’s FROM component is used for identifying the table(s) involved • if >1 table, helps accomplish relational algebra’s join (together with WHERE component that specifies equal fields) • SQL’s WHERE component isolates rows • i.e., relational algebra’s select • also helps accomplish relational algebra’s join • may be left blank for single-table queries that retrieve all rows
SQL and Relational Algebra SELECT Find the cash receipts from Customer #2 (keeping all the details of those cash receipts) Select *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)
SQL and Relational Algebra PROJECT Find the customer number, name, and salesperson number for all customers Select Customer#, Name, SP# From Customer;
SQL and Relational Algebra Inner Join Find all details of all customers and all available details of each customer’s salesperson Select *From Customer, SalespersonWhere Customer.SP# = [Salesperson.Employee Number];
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 Select *From Sale LeftJoin [Sale-CashRecDuality]Where [Sale.Sale#]=[Sale-CashRecDuality.Sale#];
Mathematical Comparison Operators • SQL 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 (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 fields • For date fields, dates that are earlier in time are “less than” dates that are later in time. • For text fields, A < B < C, etc.
SQL Mathematical Comparison Operators Select Account#, BalanceFrom CashWhere Balance>=50000;
SQL Mathematical Comparison Operators on Character Attributes Select Sale#, AmountFrom SaleWhere SalesRep# <> E-10;
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
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;
Queries with Special Operators • IS NULL is used to retrieve attributes for which the value is null. Select *From CashWhere Balance IS NULL;
Queries with Special Operators • EXISTS is used to retrieve attributes for which the value is not null. Select *From CashWhere Balance EXISTS;
Aggregation Functions in Queries • An aggregation function summarizes the data values within a field (column) • COUNT summarizes the number of rows that contain a given value in the field • AVERAGE computes the arithmetic mean value of all rows included in the answer • SUM computes the arithmetic sum of all rows included in the answer • MIN identifies the minimum (lowest) attribute value for the field • MAX identifies the maximum (greatest) attribute value for the field
Queries with Horizontal Calculations • “Horizontal” calculations mathematically combine values from different fields for each row • Horizontal calculations should NOT be included in the same query as an aggregation function • One query may perform a horizontal calculation and another query that builds on the first query may perform the aggregation function, or vice versa • The “correct” order for the queries depends on the goal
Relational Algebra SELECT in QBE Cash Receipts from Customer C-2
Relational Algebra “Select” QBE Example: Cash Receipts from Customer C-2
Relational Algebra SELECT in QBE Cash Receipts from Customer C-2
Enter =“C-2” as Criteria in the Customer Number field Relational Algebra SELECT in QBE Cash Receipts from Customer C-2
Result Relational Algebra SELECT in QBE Cash Receipts from Customer C-2
Relational Algebra PROJECT in QBECustomer#, name, salesperson#
Relational Algebra PROJECT in QBECustomer#, name, salesperson#
Relational Algebra PROJECT in QBECustomer#, name, salesperson#
Relational Algebra PROJECT in QBECustomer#, name, salesperson# Result
Relational Algebra Inner Join in QBE: All details of customers and their salespeople
Relational Algebra Inner Join in QBE: All details of customers and their salespeople
Relational Algebra Inner Join in QBE: All details of customers and their salespeople
Relational Algebra Inner Join in QBE: All details of customers and their salespeople Result
Relational Algebra Outer Join in QBE Details of all sales, related cash receipts
Double-click on the join line Relational Algebra Outer Join in QBE Details of all sales, related cash receipts
Click on appropriate join type Click OK Relational Algebra Outer Join in QBE Details of all sales, related cash receipts
Notice change in join line Relational Algebra Outer Join in QBE Details of all sales, related cash receipts
Relational Algebra Outer Join in QBE Details of all sales, related cash receipts
Relational Algebra Outer Join in QBE Details of all sales, related cash receipts Result
QBE with Mathematical Comparison OperatorCash Account# and Balances >=$50,000
QBE with Mathematical Comparison OperatorCash Account# and Balances >=$50,000
QBE with Mathematical Comparison OperatorCash Account# and Balances >=$50,000 Result