Fast Customer Service!!. Seller Inventory PSN Items related to Database Processing: Fundamentals, Design, and Implementatio Kroenke, David M. Database Processing: Fundamentals, Design, and Implementation 14th Edition Prentice-Hall Adult Education. ISBN ISBN Publisher: Pearson , This specific ISBN edition is currently not available. View all copies of this ISBN edition:. Synopsis About this title For undergraduate database management courses. Get Readers Straight to the Point of Database Processing Database Processing: Fundamentals, Design, and Implementation reflects a new teaching and professional workplace environment and method that gets readers straight to the point with its thorough and modern presentation of database processing fundamentals.

The Fourteenth Edition has been thoroughly updated to reflect the latest software. About the Author: David M. Kroenke David M. He began as a computer programmer for the U. That simulation served a key role for strategic weapons studies during a year period of the Cold War. From to , Kroenke taught in the College of Business at Colorado State University. In , he published the first edition of Database Processing , a significant and successful textbook that, more than 30 years later, you now are reading in its 14th edition. In , he left Colorado State and joined Boeing Computer Services, where he managed the team that designed database management components of the IPAD project.

After that, he joined with Steve Mitchell to form Mitchell Publishing and worked as an editor and author, developing texts, videos, and other educational products and seminars. Mitchell Publishing was acquired by Random House in During those years, he also worked as an independent consultant, primarily as a database disaster repairman helping companies recover from failed database projects. In , Kroenke was one of the founding directors of the Microrim Corporation. From to , he served as the Vice President of Product Marketing and Development and managed the team that created and marketed the DBMS product R:base as well as other related products. For the next five years, Kroenke worked independently while he developed a new data modeling language called the semantic object model. He was awarded three software patents on this technology. Since , Kroenke has continued consulting and writing. His current interests concern the practical applications of data mining techniques on large organizational databases.

An avid sailor, he wrote Know Your Boat: The Guide to Everything That Makes Your Boat Work, which was published by McGraw-Hill in Kroenke has consulted with numerous organizations during his career. In , he worked for Fred Brooks, consulting with IBM on a project that became the DBMS product DB2. In , he consulted for the Microsoft Corporation on a project that became Microsoft Access. In the s, he worked with Computer Sciences Corporation and with General Research Corporation for the development of technology and products that were used to model all of the U. Additionally, he has consulted for Boeing Computer Services, the U. Air Force Academy, Logicon Corporation, and other smaller organizations.

He also has taught part time in the Software Engineering program at Seattle University. From to , he served as the Hanson Professor of Management Science at the University of Washington. Most recently, he taught at the University of Washington from to During his career, he has been a frequent speaker at conferences and seminars for computer educators. Name the sum TotalItemsOnHand and display the results in descending order of TotalItemsOnHand. For Microsoft SQL Server, Oracle Database, and MySQL: SELECT FROM GROUP BY ORDER BY. WarehouseID, SUM QuantityOnHand AS TotalItemsOnHand INVENTORY WarehouseID TotalItemsOnHand DESC;. For Microsoft Access: Unfortunately, Microsoft Access cannot process the ORDER BY clause because it contains an aliased computed result.

To correct this, we use an SQL statement with the un-aliased computation: SELECT FROM GROUP BY ORDER BY. WarehouseID, SUM QuantityOnHand AS TotalItemsOnHand INVENTORY WarehouseID SUM QuantityOnHand DESC;. Omit all SKU items that have 3 or more items on hand from the sum, and name the sum TotalItemsOnHandLT3 and display the results in descending order of TotalItemsOnHandLT3. For Microsoft SQL Server, Oracle Database, and MySQL: SELECT FROM WHERE GROUP BY ORDER BY. To correct this, we use an SQL statement with the un-aliased computation:. Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand grouped by WarehouseID. Omit all SKU items that have 3 or more items on hand from the sum, and name the sum TotalItemsOnHandLT3. Show Warehouse ID only for warehouses having fewer than 2 SKUs in their TotalItemsOnHandLT3. Display the results in descending order of TotalItemsOnHandLT3.

For Microsoft SQL Server, Oracle Database and MySQL: SELECT FROM WHERE GROUP BY HAVING ORDER BY. To correct this, we use an SQL statement with the un-aliased computation: SELECT FROM WHERE GROUP BY HAVING ORDER BY. In your answer to Review Question 2. The WHERE clause is always applied before the HAVING clause. Otherwise there would be ambiguity in the SQL statement and the results would differ according to which clause was applied first. Do not use the IN keyword. WarehouseID, WarehouseCity, WarehouseState INVENTORY, WAREHOUSE INVENTORY. Use the IN keyword. Do not use the NOT IN keyword.

Since we want the query output for warehouses that are not Atlanta or Bangor or Chicago as a set, we must ask for warehouses that are not in the group Atlanta and Bangor and Chicago. This means we use AND in the WHERE clause — if we used OR in the WHERE clause, we would end up with ALL warehouses being in the query output. This happens because each OR eliminates only one warehouse, but that warehouse still qualifies for inclusion in the other OR statements. To demonstrate this, substitute OR for each AND in the SQL statement below. Use the NOT IN keyword. Do not be concerned with removing leading or trailing blanks. Note that the SQL syntax will vary depending upon the DBMS—see the discussion in Chapter 2. Use a subquery. Use a join, but do not use JOIN ON syntax. WarehouseID INVENTORY, WAREHOUSE INVENTORY.

Use a join using JOIN ON syntax. For Microsoft SQL Server, Oracle Database, and MySQL: SELECT FROM ON WHERE. WarehouseID INVENTORY JOIN WAREHOUSE INVENTORY. For Microsoft Access: Microsoft Access requires the SQL JOIN ON syntax INNER JOIN instead of just JOIN: SELECT FROM ON WHERE. WarehouseID INVENTORY INNER JOIN WAREHOUSE INVENTORY. SELECT FROM WHERE AND GROUP BY. WarehouseID, AVG QuantityOnHand AS AverageQuantityOnHand INVENTORY, WAREHOUSE INVENTORY. Note the use of the complete references to INVENTORY. Warehouse—the query will NOT work without them. For Microsoft SQL Server, Oracle Database, and MySQL: SELECT FROM ON WHERE GROUP BY. WarehouseID, AVG QuantityOnHand AS AverageQuantityOnHand INVENTORY JOIN WAREHOUSE INVENTORY. For Microsoft Access: Microsoft Access requires the SQL JOIN ON syntax INNER JOIN instead of just JOIN: SELECT FROM ON WHERE GROUP BY.

WarehouseID, AVG QuantityOnHand AS AverageQuantityOnHand INVENTORY INNER JOIN WAREHOUSE INVENTORY. WarehouseID and WAREHOUSE. WarehouseID—the query will NOT work without them. The above version of the query works in Access, SQL Server, Oracle Database, and MySQL. Thus the most typical, preferred solutions for each system are as follows: For Microsoft Access: SELECT. Write an SQL statement to display the WarehouseID, the sum of QuantityOnOrder and sum of QuantityOnHand, grouped by WarehouseID and QuantityOnOrder. Name the sum of QuantityOnOrder as TotalItemsOnOrder and the sum of QuantityOnHand as TotalItemsOnHand. Use only the INVENTORY table in your SQL statement. SELECT FROM GROUP BY. WarehouseID, SUM QuantityOnOrder AS TotalItemsOnOrder, SUM QuantityOnHand AS TotalItemsOnHand INVENTORY WarehouseID, QuantityOnOrder;.

Explain why you cannot use a subquery in your answer to question 2. In a query that contains a subquery, only data from fields in the table used in the top-level query can be included in the SELECT statement. If data from fields from other tables are also needed, a. join must be used. In question 2. Manager but INVENTORY would have been the table in the top-level query. Therefore, we had to use a join. Explain how subqueries and joins differ. If data from fields from other tables are also needed, a join must be used. See the answer to question 2. In Chapter 8, correlated subqueries will be discussed, and correlated subqueries do not have an equivalent join structure—you must use subqueries.

Write an SQL statement to join WAREHOUSE and INVENTORY and include all rows of WAREHOUSE in your answer, regardless of whether they have any INVENTORY. Run this statement. SELECT W. SKU, I. QuantityOnHand, I. QuantityOnOrder FROM WAREHOUSE AS W LEFT JOIN INVENTORY AS I ON W. QuantityOnOrder FROM WAREHOUSE W LEFT JOIN INVENTORY I ON W. SELECT FROM UNION SELECT FROM. SELECT FROM WHERE UNION SELECT FROM WHERE. Note that Oracle Database and SQL Server support INTERSECT directly. In MySQL and Access INTERSECT is not supported but can be simulated using a join. For Oracle and SQL Server: SELECT FROM INTERSECT SELECT FROM. DISTINCT CS SKU, CS For Oracle and SQL Server: SELECT FROM WHERE INTERSECT SELECT FROM WHERE. SKU WHERE CS CatalogPage IS NOT NULL AND CS CatalogPage IS NOT NULL;. Note that Oracle Database and SQL Server support set subtraction directly.

In MySQL and Access this operation is not supported but can be simulated using an outer join. SKU IS NULL;. ANSWERS TO PROJECT QUESTIONS For this set of project questions, we will extend the Microsoft Access database for the Wedgewood Pacific Corporation WPC that we created in Chapter 1. Founded in in Seattle, Washington, WPC has grown into an internationally recognized organization. The company is located in two buildings. One building houses the Administration, Accounting, Finance, and Human Resources departments, and the second houses the Production, Marketing, and Information Systems departments. The company database contains data about company employees, departments, company projects, company assets such as computer equipment, and other aspects of company operations. In the following project questions, we have already created the WPC.

Figure shows the column characteristics for the WPC PROJECT table. Using the column characteristics, create the PROJECT table in the WPC. accdb database. Solutions to Project Questions 2. In the Edit Relationship dialog box, enable enforcing of referential integrity and cascading of data updates, but do not enable cascading of data from deleted records. We will define cascading actions in Chapter 6. Figure shows the data for the WPC PROJECT table. Using the Datasheet view, enter the data shown in Figure into your PROJECT table. In the Edit Relationship dialog box, enable enforcing of referential integrity, but do not enable either cascading updates or the cascading of data from deleted records.

In the Edit Relationship dialog box, enable enforcing of referential integrity and cascading of deletes, but do not enable cascading updates. In Project Question 2. Why was the data entered after the referential integrity constraints were created instead of before the constraints were created? If data was entered into these columns before the referential integrity constraints were established, it would be possible to enter foreign key data that had no corresponding primary key data. Thus, we establish the referential integrity constraints so that the DBMS will not allow inconsistent data to be entered into the foreign key columns. Using Microsoft Access SQL, create and run queries to answer the following questions. Save each query using the query name format SQL-Query , where the sign is replaced by the letter designator of the question. For example, the first query will be saved as SQL-QueryA. What projects are in the PROJECT table?

Show all information for each project. What are the ProjectID, Name, StartDate, and EndDate values of projects in the PROJECT table? What projects in the PROJECT table started before August 1, ? Show all the information for each project. What projects in the PROJECT table have not been completed? Who are the employees assigned to each project? Show ProjectID, EmployeeNumber, LastName, FirstName, and Phone. ProjectID, E. Show ProjectID, Name, and Department. Show EmployeeNumber, LastName, FirstName, and Phone. ProjectID, Name AS ProjectName, P. Department AS ProjectDepartment, E. EmployeeNumber INNER JOIN PROJECT AS P ON A. Show ProjectID, Name, Department, and Department Phone.

Show EmployeeNumber, LastName, FirstName, and Employee Phone. Sort by ProjectID in ascending order. Note the use of the aliases ProjectName, ProjectDepartment, DepartmentPhone and EmployeePhone. ProjectID, Name AS ProjectName, D. DepartmentName AS ProjectDepartment, D. Phone AS DepartmentPhone, E. EmployeeNumber, LastName, FirstName, E. DepartmentName P. Who are the employees assigned to projects run by the marketing department? Note the use of the aliases ProjectName, ProjectDepartment, DepartmentPhone, and EmployeePhone. How many projects are being run by the marketing department?

Be sure to assign an appropriate column name to the computed results. Note the use of the alias NumberOfMarketingProjects. What is the total MaxHours of projects being run by the marketing department? Note the use of the alias TotalMaxHoursForMarketingProjects. What is the average MaxHours of projects being run by the marketing department? Note the use of the alias AverageMaxHoursForMarketingProjects. How many projects are being run by each department? Be sure to display each DepartmentName and to assign an appropriate column name to the computed results. Note the use of the alias NumberOfDepartmentProjects. SELECT E. Using Microsoft Access QBE, create and run new queries to answer the questions in exercise 2. Save each query using the query name format QBE-Query , where the sign is replaced by the letter designator of the question. Database Processing: Fundamentals, Design, and Implementation reflects a new teaching and professional workplace environment and method that gets readers straight to the point with its thorough and modern presentation of database processing fundamentals.

The Fourteenth Edition has been thoroughly updated to reflect the latest software. Customer Reviews, including Product Star Ratings help customers to learn more about the product and decide whether it is the right product for them. To calculate the overall star rating and percentage breakdown by star, we don’t use a simple average. Instead, our system considers things like how recent a review is and if the reviewer bought the item on Amazon. It also analyzed reviews to verify trustworthiness. close ; } } this. getElementById iframeId ; iframe. max contentDiv. scrollHeight, contentDiv. offsetHeight, contentDiv. document iframe. For undergraduate database management courses.

Get Readers Straight to the Point of Database Processing Database Processing: Fundamentals, Design, and Implementation reflects a new teaching and professional workplace environment and method that gets readers straight to the point with its thorough and modern presentation of database processing fundamentals. Previous page. Publication date. December 1, Print length. See all details. Next page. Customers who viewed this item also viewed. Page 1 of 1 Start over Page 1 of 1. Database Processing: Fundamentals, Design, and Implementation. David Kroenke. Database Processing: Fundamentals, Design, and Implementation [RENTAL EDITION]. Government in America, Elections and Updates Edition 16th Edition. George C. Edwards III. Constitutional Law for a Changing America: Institutional Powers and Constraints Ninth Edition.

Thomas G Epstein. Abnormal Psychology: An Integrative Approach. David H. Earl R. Product Bundle. About the Author David M. Kroenke David M. He began as a computer programmer for the U. That simulation served a key role for strategic weapons studies during a year period of the Cold War. From to , Kroenke taught in the College of Business at Colorado State University. In , he published the first edition of Database Processing , a significant and successful textbook that, more than 30 years later, you now are reading in its 14th edition. In , he left Colorado State and joined Boeing Computer Services, where he managed the team that designed database management components of the IPAD project. After that, he joined with Steve Mitchell to form Mitchell Publishing and worked as an editor and author, developing texts, videos, and other educational products and seminars.

Mitchell Publishing was acquired by Random House in During those years, he also worked as an independent consultant, primarily as a database disaster repairman helping companies recover from failed database projects. In , Kroenke was one of the founding directors of the Microrim Corporation. From to , he served as the Vice President of Product Marketing and Development and managed the team that created and marketed the DBMS product R:base as well as other related products. For the next five years, Kroenke worked independently while he developed a new data modeling language called the semantic object model.

He was awarded three software patents on this technology. Since , Kroenke has continued consulting and writing. His current interests concern the practical applications of data mining techniques on large organizational databases. An avid sailor, he wrote Know Your Boat: The Guide to Everything That Makes Your Boat Work, which was published by McGraw-Hill in Kroenke has consulted with numerous organizations during his career. In , he worked for Fred Brooks, consulting with IBM on a project that became the DBMS product DB2.

Database Processing: Fundamental, Design, and Implementation 14th Edition David M. Kroenke and David J. Copyright Š Pearson Education, Inc. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America. To create SQL queries that use the SQL SELECT, FROM, WHERE, ORDER BY, GROUP BY, and HAVING clauses. To create SQL queries that use the SQL comparison operators including BETWEEN, LIKE, IN, and IS NULL.

To create SQL queries that use the SQL built-in aggregate functions of SUM, COUNT, MIN, MAX, and AVG with and without the SQL GROUP BY clause. To create SQL queries that retrieve data from a single table while restricting the data based upon data in another table subquery. To create SQL queries that retrieve data from multiple tables using the SQL join and JOIN ON operations. To create SQL queries that retrieve data from multiple tables using SQL set operators UNION, INTERSECT, and EXCEPT. If you are using Microsoft SQL Server as your DBMS, you should use Online Chapter 10A — Managing Databases with Microsoft SQL Server , and cover pages 10A-1 through 10A to help your students get set up for the SQL work in Chapter 2. If you are using Oracle Database 12c or Oracle Database XE as your DBMS, you should use Online Chapter 10B — Managing Databases. with Oracle Database, and cover pages 10B-1 through 10BA to help your students get set up for the SQL work in Chapter 2.

If you are using MySQL 5. J, LocalCurrencyAmountt is misspelled: J. Show ItemID, Description, Store, and a calculated column named USCurrencyAmount that is equal to LocalCurrencyAmount multiplied by the ExchangeRate for all rows of ITEM. Sentence before Query 77, parenthesized comment should read: note that MySQL and Microsoft Access do not support this operator. Sentence before Query 78, parenthesized comment should read: note that Oracle Database calls this the SQL MINUS operator, and MySQL and Microsoft Access do not support this operation. The best way for students to understand SQL is by using it. Students can create databases in Microsoft Access with basic tables, relationships, and data from the material in the book. An Access version of WPC is also available there. The SQL processors in the various DBMSs are very fussy about character sets used for SQL statements.

They want to see plain ASCII text, not fancy fonts. There is a useful teaching technique which will allow you to demonstrate the SQL queries in the text using Microsoft SQL Server if you have it available. Open the Microsoft SQL Server Management Studio, and create a new SQL Server database named Cape-Codd. sql text file DBP-eMSSQL-Cape-Codd-CreateTables. sql text file DBP-eMSSQL-Cape-Codd-InsertData. sql text file DBP-eMSSQL-Cape-Codd-Query-Set-CH This file contains all the queries shown in the Chapter 2 text. Highlight the query you want to run and click the Execute Query button to display the results of the query.

An example of this is shown in the following screenshot. Microsoft Access does not support all SQL and newer constructs. While this chapter still considers Microsoft Access as the DBMS most likely to be used by students at this point in the course, there are some Review Questions and Project Questions that use the ORDER BY clause with aliased computed columns that will not run in Access see Review Questions 2. The correct solutions for these questions were obtained using Microsoft SQL Server The Microsoft Access results achieving the ORDER BY without using the alias are also shown, so you can assign these problems with or without the ORDER BY part of the questions. Microsoft Access does not support SQL wildcard characters see Review Questions 2. The correct solutions for these questions were obtained using Microsoft SQL Server , and solutions are shown for Access as well.

For those students who are used to procedural languages, they may have some initial difficulty with a language that does set processing like SQL. These students are accustomed to processing rows records rather than sets. It is time. well spent to make sure they understand that SQL processes tables at a time, not rows at a time. Students may have some trouble understanding the GROUP BY clause. If you can explain it in terms of traditional control break logic sort rows on a key then process the rows until the value of the key changes , they will have less trouble.

This also explains why the GROUP BY clause will likely present the rows sorted even though you do not use an ORDER BY clause. At this point, students familiar with Microsoft Access will wonder why they are learning SQL. In many cases, they will not know that Microsoft Access generates SQL code when you create a query in design view. It is worth letting them know this is done and even showing them the SQL created for and underlying a Microsoft Access query. It has been our experience that a review of a Cartesian Product from an algebra class is time well spent. Show students what will happen if a WHERE statement is left off of a join. The following example will work. Assume you create four tables with five columns each and rows each. This happens because the JOIN is not qualified. If they understand Cartesian products then they will understand how to fix a JOIN where the results are much too large.

We did this because ORDER is an SQL reserved word part of ORDER BY. The topic of reserved words and delimiters is discussed in more detail in Chapters 7 and 8. However, now is a good time to introduce it to your students. Note that Microsoft Access SQL requires the INNER JOIN syntax instead of the standard SQL syntax JOIN used by Microsoft SQL Server, Oracle Database, and MySQL. See solutions to Review Question Students will frequently try to UNION OR INTERSECT tables that are not compatible have different schemas. String comparisons using LIKE and other operators may or may not be casesensitive, depending on the DBMS used and on the default settings set up by the DBA; see solutions to Case Question MDC-F for more details and suggestions. Screen shot solutions to all the queries in this chapter come from Microsoft Access. Note that some of them are from Access and some from Access the differences for the purposes of this chapter are entirely cosmetic font and other colors.

What is an online transaction processing OLTP system? What is a business intelligence BI system? What is a data warehouse? An OLTP system is typically one in which a database is used to store information about daily operational aspects of a business or other enterprise, such as sales, deposits, orders, customers, etc. A business intelligence BI system is a system used to support management decisions by producing information for assessment, analysis, planning and control. BI systems typically use data from a data warehouse, which is a database typically combining information from operational databases, other relevant internal data, and separately-purchased external data.

What is an ad-hoc query? An ad-hoc query is a query created by the user as needed, rather than a query programmed into an application. What does SQL stand for, and what is SQL? SQL stands for Structured Query Language. SQL is the universal query language for relational DBMS products. What does SKU stand for? What is an SKU? SKU stands for stock keeping unit. An SKU is a an identifier used to label and distinguish each item sold by a business. Summarize how data were altered and filtered in creating the Cape Codd data extraction. We also note that the OrderTotal column includes tax, shipping, and other charges that do not appear in the data extract.

The structure of the table is:. Thus one SKU may be associated once with each specific order number, but may also be associated with many different order numbers as long as it appears only once in each order. The two CATALOG tables are not formally related to any of the other tables. Using the Microsoft Access Relationship window, the relationships are shown in Figure and look like this:. Using an underline to show primary keys and italics to show foreign keys, the tables and their relationships are shown as:. Summarize the background of SQL. SQL was developed by IBM in the late s, and in it was endorsed as a national standard by the American National Standards Institute ANSI. That version is called SQL There is a later version called SQL3 that has some object-oriented concepts, but SQL3 has not received much commercial attention. What is SQL? How does it relate to the SQL statements in this chapter? SQL is the version of SQL endorsed as a national standard by the American National Standards Institute ANSI in It is the version of SQL supported by most commonly used relational database management systems.

The SQL statements in this chapter are based on SQL92 and the SQL standards that followed and modified it. What features have been added to SQL in versions subsequent to SQL? Versions of SQL subsequent to SQL have extended features or added new features to SQL, the most important of which, for our purposes, is support for Extensible Markup Language XML. Why is SQL described as a data sublanguage? A data sublanguage consists only of language statements for defining and processing a database.

He began as a computer programmer for the U. An example of this is shown in the following screenshot. He also has taught part time in the Software Engineering program at Seattle University. Show the ShipperName, ShipmentID, the DepartureDate of the shipment, and Value for items that were purchased in Singapore. Database Processing: Fundamental, Design, and Implementation 14th Edition David M.

Categories:

• Movies
• Games
• Soft
• Music
• Files