App-I Databases in Electronic Commerce – Database Systems: Design, Implementation, and Management : Book Guide

This Document Contains Appendixes I, J, P, Q Appendix I Databases in Electronic Commerce Discussion Focus Use some major websites to illustrate the use of electronic commerce. For example, show how you would buy an airline ticket, a computer, or a book. This chapter’s problem section is an excellent source for discussions about the use of the Web to buy goods and services and to make product/services/price comparisons. www.pricewatch/com and www.priceline.com are excellent ways to illustrate how you might become a more informed shopper. Answers to Review Questions 1. What does e-commerce mean and how did it evolve? Electronic commerce (e-commerce) is the use of electronic computer-based technology to: • Bring new products, services or ideas to market. • Support and enhance business operations, including the sales of products and/or services over the web. The Internet started in the early 1960’s as a military project to ensure the survival of computer communications in case of nuclear attack. However, the Internet soon became the prime vehicle for sharing academic research, thus making higher education institutions the Internet’s primary users. (Section I.2, “The Road to Electronic Commerce,” shows the evolution toward e-commerce.) • During the early 1960’s, banks created a private telephone network to do electronic funds transfers (EFT). This service allowed two banks to exchange funds electronically in a fast, efficient, and secure manner. • During the early 1970’s, banks also created services such as the Automated Teller Machine (ATM) to provide “after-hours” services to their customers. ATMs where initially installed by only a few banks nationwide and these banks permitted only a limited number of account transactions. • During the late 1970’s and early 80’s, there was a boom in the use of Electronic Data Interchange (EDI). EDI enables two companies to exchange business documents over private phone networks. The use of EDI facilitated the coordination of business operations between business partners. • The early 1980’s and all 1990’s brought us the personal computer, which triggered the Internet’s accelerated growth. The wide acceptance and use of the Internet led to the current dominance of the World Wide Web. The web made the transfer of information among multiple organizations as simple as a click away. The web also became a fertile ground for the exploration and exploitation of new Internet-based technologies for the enhancement of business processes within and between corporations. 2. Identify and briefly explain five advantages and five disadvantages of e-commerce. A summary of advantages of e-commerce is described in section I.3.1. We can briefly enumerate benefits such as: • Comparison-shopping • Reduced costs and increased competition • Convenience for on-line shoppers • 24 x 7 x 365 Operations • Global Access • Lower barriers of entry • Increased Market (Customer) Knowledge A summary of the disadvantages is presented in Section I.3.2 and they are summarized as follows: • Hidden costs of operation • Network unreliability • Higher costs of staying on business • Lack of security • Loss of privacy • Low service levels • Legal issues (fraud, copyright problems) 3. Define and contrast B2B and B2C e-commerce styles. E-commerce styles can be classified as: • Business to Business (B2B): electronic commerce between businesses. • Business to Consumer (B2C): electronic commerce between businesses and consumers. • Intra-Business: electronic commerce activities between employers and employees. Business-to-Business (B2B) is a type of e-commerce in which a business sells products and/or services to other business. B2B refers to all types of electronic commerce transactions that take place between businesses. The seller is any company that sells a product or service, using electronic exchanges (such as over the Internet or using EDI). The buyer may be a not-for-profit company such as the Red Cross, a for-profit company such as Dell Computers, or a government organization such as a local municipality. A business to consumer (B2C) web site sells products or services directly to consumers or end-users. In B2C e-commerce, the main focus is on using the Internet -- in particular, the Web -- as a marketing, sales, and post-sales support channel. A complete discussion about e-commerce styles is presented in Section I.4. 4. Describe and give an example of each of the two principal B2B forms. B2B Integration. In this scenario, companies establish partnerships to reduce costs and time, to improve business opportunities, and to enhance competitiveness. For example, a company that manufactures computers might partner with its suppliers for hard disks, memory and other components. Such a partnership will help automate its purchasing system by integrating it with its suppliers’ ordering systems – which, in turn, will tie into their respective inventory systems. In this case, when a component in company “A” gets below the minimum, it will automatically generate an order to supplier “S”. Both systems would be integrated and would exchange business data, probably using XML. (See Section 13.8). Using the same technique, Company “A” may also integrate its distribution system with its distributors. Finally, the distributors may integrate their operations with those of their retailers, which in turn may integrate their activities with those of their customers. As a consequence of such integration, companies (sellers) learn to operate with other companies (buyers) and the integration of their operations makes it possible to achieve a level of efficiency that makes it difficult to switch to another provider. B2B Marketplace. In this scenario, the objective is to provide a way in which businesses can easily search, compare, and purchase products and services from other businesses. The web-based system will basically work as an on-line broker to service both buyers and sellers. Given such an environment, many of the activities are focused on attracting new members -- either providers or buyers. The “broker” offers sellers a way to market their products and/or services to other businesses, while buyers are attracted by the fact that they can compare products from different buyers and get access to special deals that are offered only to the members. Given this B2B Marketplace scenario, the broker obtains revenue through membership and transaction fees. An example of a B2B web market place for the automotive industry is the http://www.covisint.com website. 5. Describe e-commerce architecture: then briefly describe each one of its components. The e-commerce architecture is described in detail in Section I.5, “E-commerce Architecture.” Start with the three layers: Basic Internet Services, Business Enabling Services, and E-commerce Business Services. Figure I.6, “E-commerce Architecture,” provides a good summary. The basic building blocks are provided in Table I.2, “Internet Building Blocks and Basic Services.” The business enabling services are summarized in Table I.3, “Business-Enabling Services.” 6. What types of services are provided by the bottom layer of the e-commerce architecture? The bottom layer if the e-commerce architecture, known as the Internet Basic Services, describes the basic building blocks and services that are provided by the Internet and the World Wide Web. The Internet provides the basic services that facilitate the transmission of data and information between computers. 7. Name and explain the operation of the main building blocks of the Internet and its basic services. The main Internet services are transport services -- such as TCP/IP, Routers and other protocols --, document formatting, storage, addressing, retrieval and presentation protocols such as the World Wide Web, web servers, web browsers, HTML, URL, HTTP, File Transfer Protocol, and other services such as e-mail, news groups and discussion groups. A complete description of the main Internet services is located in Chapter 13’s Table 13.1 and in Section 13.5.1. 8. What does business-enabling do? What services layer does it provide? Give six examples of business enabling services. The Internet Basic Services (IBS) only form a foundation on which to run a basic website. However, IBS does not provide the services required for even elementary business transactions. The Business Enabling Layer provides the additional services to better support business transactions. Business transactions require accountability, reliability, authentication, trust, fidelity, and performance. These requirements are supported through hardware and software components that work together to provide the additional functionality not provided by the other layers. Table I.3 describes services that are used to enhance websites by providing their users the ability to perform searches, authenticate and secure business data, manage web site contents, and so on. The list in Table I.3 is not exhaustive -- technological advances enable new services, which in turn are used to enable additional business services. The Business Enabling Services are search services, security, site monitoring and analysis, load balancing, personalization, web development, database integration, transaction management, messaging and support for multiple devices. The services provided by this layer are built on top of the Internet Basic services to provide the additional services that are required to support business transactions. 9. What is the definition of security? Explain why security is so important for e-commerce transactions. In an e-commerce context, security refers to all the activities that are associated with the protection of the data and other components against accidental or intentional (probably illegal) use by unauthorized users. Privacy deals with the rights of individuals and organizations to determine the “who, what, when, where and how” authorization to use his/her data. Providing security is a major concern of e-commerce. Companies spend millions of dollars annually on hardware and software equipment to protect their own data (including personal customer data) and property against criminal activities. For e-commerce to be successful, it must ensure the security and privacy of all business transactions and the data associated with those transactions. 10. Give an example of an e-commerce transaction scenario. What three things should security be concerned with in this e-commerce transaction? E-commerce data must be secured from a transaction’s beginning to its conclusion. Note, for example the following transaction sequence: • A customer buying products online from home, enters order and credit card information in a merchant’s web page. • The information travels from the customer’s computer over the Internet to the merchant’s web server. • The merchant’s web server receives the order and credit card data and stores these data in a database. • The web server sends the order confirmation and shipping information back to the client. • The seller uses a third-party shipping company to deliver the products to the customer. • The seller uses a third party payment processing company to settle payment. • The shipping company delivers the product to the customer. • At the end of the month, the customer receives his/her credit card statement -- possibly electronically. • The customer pays the credit card, either by writing and mailing a check or through the use of electronic funds transfer. These transaction components are easily illustrated with the help of Figure I.8, “A Sample E-commerce Transaction.” Given the transaction scenario in Figure I.8, security (procedures and technology) deal with all activities required to: • Warrantee the identity of the transaction’s participants by ensuring that both the buyer and the seller are who they say they are. In other words, it needs to exist a secure way to properly identify transaction participants and the authenticity of the messages. • Protect the transaction data from unauthorized modifications while it travels on the Internet. Because it is not feasible to have private lines to connect every two computers, we use the Internet. Unlike private lines that directly connect the sender with the receiver, the Internet is formed by millions of interconnected networks. E-commerce data has to pass trough several different networks in order to travel from the client to the server; this increases the risks of data being stolen, modified or forged. • Protect the resources (data and computers). This includes protecting the end-user and the business data stored on the web server and databases from unauthorized access. It also includes securing the web server against attacks from hackers wanting to break into the system with intentions of modifying or stealing data or of impairing normal operations by limiting the resource availability. 11. You are hired as a resource security officer for an e-commerce company. Briefly discuss what technical issues you must address in your security plan. The security plan should include issues such as physical security of the computing environment and protection of the data in the databases. On-line transaction security must also cover issues such as authentication, the use of digital certificates to ensure the identity of the parties involved in business transactions, and the use of public-key encryption with digital signatures to guarantee that the data traveling on the Internet cannot be tampered with or read by unauthorized parties. The security plan should include issues such as resource security and transaction security. Transaction security includes encryption methods at the transport level, such as S-HTTP and SSL. Resource security deals with protecting the resources (hardware and software) that enable the conduct of e-commerce -- servers, routers, operating systems and applications – against threats posed by hackers, viruses, theft, and so on. Problem Solutions 1. Use the Internet at your university computer lab or home to research the scenarios described in Problems 1-10. Then work through the following problems: a. What web sites did you visit? b. Classify each site (B2B, B2C, and so on.) c. What information did you collect? Was the information useful? Why or why not? d. What decision(s) did you make based on the information you collected? The format is provided in the answers to problems 2-9. Naturally, the web sites shown here change periodically, so use the examples as a general guide. Also, keep in mind that there are many sites beyond the sites we have shown in the answers to problems 2-9. 2. Research – and document -- the purchase of a new car. Based on your research, explain why you plan to buy this car. A B C D Vehix.com B2C Car models, features, comparisons, ratings, evaluations, dealer prices on new and used models. Information was very useful. Made an informed decision. Found the car most affordable, with best ratings and features. Capability of comparing car models and features. CarMax.com B2C AutobyTel.com B2C 3. Research – and document -- the purchase of a new house. A B C D Century21.com C2B B2C Searched multiple homes based on my search criteria. Web sites provide information such as school systems, nearby attractions, city guides, comparable house prices, and financing options. The sites also provided ways to place a home for sale. In addition, there were tips for buyers and sellers and price comparisons for new and older homes. Mortgage Calculators were available to help determine what the buyer can qualify for. I was able to determine how much home I could afford, found a home within price range, locations and features desired. RealEstate.com B2C ColdwellBanker.com C2B B2C 4. You are in the market for a new job. Search the web for your ideal job. Document your job search and your job selection. A B C D Monster.com C2B B2C Search job openings by location, industry, and salary range. Research employers, salary comparison. Option to post resume and obtain resume advice. Resume writing service. Interview tips. Salary calculators, relocation information and services. Moving information. Was able to fine-tune a job search. Applied for jobs that matched my qualifications and experience. Was able to research companies and to compare salaries in different geographic regions. HotJobs.com C2B B2B B2C Headhunter .net C2B B2B B2C 5. You need to do your taxes. Download IRS form 1040 and look for online tax processing help, documenting your search. A B C D IRS.gov G2C Obtained information about latest tax laws, downloaded tax return forms. Learned how to file a tax return electronically. Found information about IRS red flags for auditing. Fond tax advice in many different areas, determined how much to save in multiple retirement instruments, etc. Searched for tax advisors within my area. Learned how retirement instruments can be used to save for retirement and to reduce the tax burden. Used many different tax calculators to estimate how much I will pay in taxes. Hrblock.com B2C CompleteTax.com B2C 6. Research the purchase of a 20-year level term life insurance policy and report your findings. A B C D QuoteSmith.com B2B B2C Searched for policies by state, age, smoking status, and amount. Obtained policy details, latest prices, and providers. Compared insurance company premiums and ratings companies. Could find best possible deals in no time at all. Intellequote.com B2B B2C Conseco.com B2C 7. Research – and document -- the purchase of a new computer. A B C D Dell.com B2C Searched for computers, compared prices, options, and warranty information. Could configure my computer according to my specifications. Obtained leasing and credit term information. Compare prices in new and refurbished computers. I was able to find my computers at the right price, with the right features, and with the best warranty. Pricewatch.com B2C Gateway.com B2C 8. Vacation time is almost here! Research—and document—the destination(s) and activities of next summer’s vacation. A B C D Travelocity.com B2C Found information in vacation packages with all-inclusive features, such as air fare, hotel accommodations, and guided-tour details. I was able to perform searches by destination, travel dates, tour operators, etc. I could compare prices for tours and hotels. I was also able to find special deals and offers to various destinations. I could do all the trip planning on-line and get additional information such as currency conversions, city guides, comments from past-users, weather information, etc. I was able to search for (and find) multiple tour vacation packages that fit my criteria. The information provided was also useful to completely plan the vacation and do all booking on-line. TourDeals.com B2C Expedia.com B2C 9. You have some money to invest. Research – and document -- mutual funds information for investment purposes. Report your investment decision(s) based on the research you conduct. A B C D Vanguard.com B2C Obtained information about investing in the stock market, mutual funds markets, and markets for other instruments. Search yielded comparative fund information such as returns, expense ratios, ratings, market capitalization, family type, price history, etc. Obtained list of best-rated funds according to search criteria. Was able to determine the best investment strategy to fit my risk tolerance. Obtained all critical information appropriate to my investment needs. Enabled me to manage all of my investments online. Fidelity.com B2C Morningstar.com B2C Appendix J Web Database Development with ColdFusion Discussion Focus Here is a good opportunity to take a look at the “big picture” of Internet database development. Review the main points in Chapter 14, “Database Connectivity and Web Development” and Appendix J, “Web Database Development with ColdFusion.” Specifically, focus on: • Different database connectivity technologies. • Multi-tier architecture for database development. • How Web-to-database middleware is used to integrate databases with the Internet. Rather than showing you long code listings in this manual, we guide you through the solution steps as they are found in the script files located on the Instructor’s CD. Using this technique, the student can step through the solutions and see the code at the same time. Figure J.1 shows the RobCor ColdFusion application’s main menu. Figure J.1 RobCor Teacher Menu The ColdFusion Problem Solutions section is a menu driven system that guides you through all of the solutions for this appendix. For example, if you click on the Solutions to Problems link (See Figure J.1) you will open the page shown in Figure J.2. Figure J.2 RobCor Problem Solutions Menu If you click on the View link for the first row shown in Figure J.2, you will see the code for the rc_u0.cfm script in Figure J.3. Figure J.3 RobCor View Code Sample Answers to Review Questions 1. What are scripts, and how are they created in ColdFusion? Scripts are a series of instructions interpreted and executed at run time. Scripts are used in web-database application development to instruct the application server components what actions to do, such as connect, query, and update a database from a web front-end. Scripts are, for the most part, transparent to the clients. The application developer must create scripts to access the database and to create the web pages dynamically. The application server executes the scripts and passes the results (output) to the web server in HTML format. 2. Describe the basic services provided by the ColdFusion Web application server. The ColdFusion Web Application Server provides the following services (among others): • Integrated Development Environment. • Session management with support for persistent application variables. • Security and authentication. • A computationally complete programming language (commands and functions) to represent and store business logic. • Access to other services: FTP, SMTP, IMAP, POP, etc. 3. Discuss the following assertion: The web is not capable of performing transaction management. Note the discussion in Section J.2.3, Transaction Management. The concept of database transactions is foreign to the Web. Remember that the Web’s request-reply model means that the Web client and the Web server interact by using very short messages. Those messages are limited to the request for and delivery of pages and their components. (Page components may include pictures, multimedia files, and so on.) The dilemma created by the Web’s request-reply model is that: • The Web cannot maintain an open line between the client and the database server. • The mechanics of a recovery from incomplete or corrupted database transactions require that the client must maintain an open communications line with the database server. 4. Transaction management is critical to the e-commerce environment. Given the assertion made in Item 3, how is transaction management supported? Clearly, the creation of mission-critical Web applications mandates support for database transaction management capabilities. Given the just-described dilemma, designers must ensure proper transaction management support at the database server level. Many Web-to-middleware products provide transaction management support. For example, ColdFusion provides this support through the use of its CFTRANSACTION tag. If the transaction load is very high, this function can be assigned to an independent computer. By using that approach, the Web application and database servers are free to perform other tasks and the overall transaction load is distributed among multiple processors. 5. Describe the Web page development problems related to database parent/child relationships. This condition is addressed in detail in Section J.2.4, Denormalization of Database Tables. Specifically, note the following: When the Web is used to interact with databases, the application design must take into account the fact that the Web forms cannot use the multiple data entry lines that are typical of parent-child (1:M) relationships. Yet those 1:M relationships are crucial in e-commerce. For example, think of order and order line, or invoice and invoice line. Most end users are familiar with the conventional GUI entry forms that support multi-table (parent-child) data entry through a multiple-component structure composed of a main form and a subform. Using such main-form/subform forms, the end user can enter multiple purchases associated with a single invoice. All data entry is done on a single screen. Unfortunately, the Web environment does not support this very common type of data entry screen. As illustrated in the ColdFusion script examples, the Web can easily handle single-table data entry. However, when multi-table data entries or updates are needed—such as order with order lines, invoice with invoice lines, and reservation with reservation lines—the Web falls short. Although implementing the parent/child data entry is not impossible in a Web environment, its final outcome is less than optimum, usually counterintuitive, less user-friendly, and prone to errors. To see how the Web developer might deal with the parent/child data entry, let’s briefly examine how you might deal with the ORDER and ORDER_LINE relationship used to store customer orders. Using an applications middleware server such as ColdFusion to create a Web front end to update orders, one or more of the following techniques might be used: • Design HTML frames to separate the screen into order header and detail lines. An additional frame would be used to provide status information or menu navigation. • Use recursive calls to pages to refresh and display the latest items added to an order. • Create temporary tables or server-side arrays to hold the child table data while in the data entry mode. This technique is usually based on the bottom-up approach in which the end user first selects the products to order. When the ordering sequence is completed, the order-specific data, such as customer ID, shipping information, and credit card details, are entered. Using this technique, the order detail data are stored in the temporary tables or arrays. • Use stored procedures or triggers to move the data from the temporary table or array to the master tables. Although the Web itself does not support the parent/child data entry directly, it is possible to resort to Web programming languages such as Java, JavaScript, or VBScript to create the required Web interfaces. The price of that approach is a steeper application development learning curve and a need to hone programming skills. And while that augmentation works, it also means that complete programs are stored outside the HTML code that is used in a Web site. Problem Solutions In the following exercises, you are required to create ColdFusion scripts. When you create these scripts, include one main script to show the records and the main options, for a total of five scripts for each table (show, search, add, edit, and delete). Consider and document foreign key and business rules when creating your scripts. 1. Create ColdFusion scripts to search, add, edit, and delete records for the USER table in the RobCor database. This problem’s solution is contained in the system summary below problem 5. 2. Create ColdFusion scripts to search, add, edit, and delete records for the INVTYPE table in the RobCor database. This problem’s solution is contained in the system summary below problem 5. 3. Create ColdFusion scripts to search, add, edit, and delete records for the VENDOR table in the RobCor database. This problem’s solution is contained in the system summary below problem 5. 4. Modify the insert scripts (rc-5a.cfm and rc-5b.cfm) for the DEPARTMENT table so the users who can be manager of a department are only those who belong to that department. This problem’s solution is contained in the system summary below problem 5. 5. Create an Order data-entry screen, using the ORDERS and ORDER_LINE tables in the RobCor database. To do this, you can use frames and other advanced ColdFusion tags. Consult the online manual and review the demo applications. NOTE The following pages show sample ColdFusion scripts that are required by the problem set. To avoid repetition and to save space, we have illustrated only one example of each script type (select, insert, update, and delete). Use the Instructor’s CD to access the complete list of ColdFusion scripts. To install ColdFusion and the scripts follow the instructions in the ColdFusion_Setup.doc This series of exercises requires the student to create the data manipulation scripts for the USER, INVTYPE and VENDOR tables. The logic used in these scripts is the same as the one shown in Appendix J “Web Database Development with ColdFusion”. To produce a user-friendly environment, we have created a menu to access all USER table database operations. The menu lets the user add, edit, delete, and search records in the USER table. Please refer to the solution scripts found on the Instructor’s CD. The menu script, named rc-u0.cfm produces the output shown in Figure PJ.1. Figure PJ.1 The User Management Menu The following pages list scripts that are required to add, edit, delete and search records. For a complete listing please refer to the Instructor’s CD. Inserting Records in the USER Table The rc-ua1.cfm script produces the data entry screen shown in Figure PJ.2. This data entry screen consists of an HTML form that contains several input boxes to enter the data. Figure PJ.2 The Add User Web Form When the user clicks the Add button, script rc-ua2.cfm is invoked. The cc-ua2.cfm script uses the CFINSERT tag to add a row to the database. (Check Figure PJ.3 to see the script’s effect.) Figure PJ.3 The Add User Results Updating Records in the USER Table The rc-ue1.cfm script will show all the data that match the USER_ID selected by the user. In addition, the script lets the end user modify the selected data. Figure PJ.4 shows the script’s output. Figure PJ.4 The Edit User Form Clicking on the Add button will trigger the rc-ue2.cfm script, which runs the CFUPDATE tag to update the database. The rc-ue2.cfm script produces the output shown in Figure PJ.5. Figure PJ.5 The Edit User Results Screen Deleting Records from the USER Table The rc-ud1.cfm script produces the results shown in Figure PJ.6. Figure PJ.6 The Delete User Form In this rc-ud1.cfm script, the Delete button is shown only if this user is not a manager of a department and does not have any orders in the ORDERS table. Otherwise, the script will not allow you to delete the USER record. After clicking on the Delete button, the rc-ud2.cfm script is invoked. The rc-ud2.cfm script produces the output shown in Figure PJ.7. Figure PJ.7 The Delete User Results Screen Searching for Records in the USER Table The rc-s1.cfm script produces the output shown in Figure PJ.8. Figure PJ.8 The Search User Form Figure PJ.8's very simple screen lets the end user enter a last name to conduct a search in the USER table -- or the end user may select a user id as the search key. Clicking on the Search button invokes the rc-us2.cfm script. The rc-us2.cfm script produces the output shown in Figure PJ.9. Figure PJ.9 The Search User Results Screen Note that the insert script (rc-5a.cfm and rc-5b.cfm) for the Department table only lists the users that can be manager of a department. The key to this script is in the SELECT SQL statement. (Note the condition used in the WHERE clause. This condition lists only those users who are not already managers of a department.) SELECT USR_ID, USR_LNAME, USR_FNAME, USR_MNAME FROM USER WHERE USR_ID NOT IN (SELECT USR_ID FROM DEPARTMENT WHERE USR_ID > 0) ORDER BY USR_LNAME, USR_FNAME, USR_MNAME The rc-5a.cfm script produces the output shown in Figure PJ.10. Figure PJ.10 The Department Data Entry Screen Script rc-5b.cfm script uses a CFINSERT tag to add the data to the database. The script rc-5b.cfm output is shown in Figure PJ.11. Figure PJ.11 The Department Insert Query Challenge Project Create an Order data entry screen, using the ORDERS and ORDER_LINE tables in the RobCor database. (To complete this problem successfully, you should know how to use frames and ColdFusion tags. Please consult the online ColdFusion manual and study the demo applications to learn how such components can be developed.) This challenge project requires the student to know HTML coding and the use of frames. In addition, some type of CGI programming or Java/JavaScript programming is recommended. Although our chapter 14 provides the basis for your students to develop Web to database interfaces, it does not cover all the components required to complete this problem. Therefore, you might pitch this problem at students who have some prior Web development experience. (Or perhaps you used supplemental material to examine our database design and implementation material from an applications development point of view!) Even if your students do not (yet) have the appropriate Web application development skills, they will find the following discussion interesting and useful for several reasons. First, they will have a chance to revisit some important database design and implementation issues in a Web environment. Second, they will be exposed to some of the details of Web database applications development. Finally, they may even store this problem into their minds, to be dusted off when they take Web-based classes! From a design standpoint, the developer can approach this problem from several different angles: • Create a multiple frame page that will have one frame for the ORDER header information and another for the ORDER_LINE data entry. In this case, the ORDER data will have to be entered, validated and saved first, before the ORDER_LINE frame is shown or accessed. Once the main ORDER data are saved, the second frame can be used to enter the ORDER_LINE rows. Both frames use buttons that will enable the system to accept data entry and to perform validation checks. This solution is not particularly well suited to a commercial e-commerce environment for two good reasons: 1. The end-user navigation among frames is awkward and is likely to be rejected by end users. 2. Keeping both frames synchronized is difficult and, unless the coding is particularly robust, is prone to failure. • A second way to tackle this problem is to borrow the typical “shopping cart” style used by most on-line stores. Students can go to Amazon, Sears, or to any other on-line store to step through the process of purchasing a product online. This process usually starts with the selection of all of the desired items and then progresses to the payment component. In other words, the process first collects the order line data and then, at its conclusion, collects the (invoice) payment data. Given this scenario, the browser must use temporary tables to store the data for the orders in progress. Later, such data are used to update the production database. In between, business logic is used to validate the data and to save such data in the proper format. The business logic is implemented through the use of cgi programs such as PERL, Java, etc. In addition, the business logic component usually employs stored procedures in the database environment. This solution generally preferred by on-line stores, because it is based on well-established and proven technology. (In fact, you can even buy applications that provide the entire shopping cart feature straight out of the box!) • Using Java programming code or some plug-in application like Power-Builder may also solve the problem. This approach requires that the complete application be downloaded to the client computer, to be run locally. Given this scenario, the end user develops an interface that can handle the one-to-many simultaneous data entry format. The entire application logic is then sent to and executed by the client side. The client application is connected to the back-end database through the Web. This solution is similar to those offered by high-end web-to-database middleware products such as NetObjects, IBM’s WebSphere or Oracle 9i Web database. In most cases, the application will be in the form of a Java applet that works in tandem with a server applet. Appendix P Working with MongoDB NOTE Most of this appendix, and all of the end of appendix problems, require the use of the Ch14_FACT.json file. The Appendix provides instructions on how to import this file into a MongoDB database as a collection. The documents in this file is a reduced version of the data from the Ch07_FACT database used in Chapter 7. It can be helpful to draw to students’ attention that this is a reduced data set compared to Chapter 7. The reason this is a reduced set is because it is limited to a specific intended application. Refer students back to Chapter 14, Big Data and NoSQL, to remind them that document databases like MongoDB are aggregate aware. Therefore, the data are organized into documents with a great deal of redundancy across documents, but in a manner that reduces the number of documents that need to be accessed during the processing of a transaction. Answers to Review Questions 1. What is the difference between a replacement update and an operator update in MongoDB? In MongoDB a replacement update will replace the entire document being updated. If the existing document has key:value pairs that are not included in the update command, then those pairs are lost. Only the pairs specified in the update command will exist in the replaced version of the document. With an operator update, the existing document is unchanged except for the changes specified in the update command. Pairs not included in the update command are not affected. 2. Explain what an upsert does. Upsert is a combination insert / update. If an existing document is found that matches the criteria given, then an update is performed on that document using the key:value pairs specified in the command. If an existing document is not found that matches the criteria given, then an insert is performed to create a document with the key:value pairs specified in the command. 3. Describe the difference between using $push and $addToSet in MongoDB. Both commands are used to add a value to an array. The $push command will always add the value to the array, even it results in duplicate values in the array. The $addToSet command will only add the value to the array if adding it does not result in duplicate values in the array. 4. Explain the functions used to enable pagination of results in MongoDB. Results can be provided in pages of information by using limit() and skip() functions. The limit() function specifies how many results to return. The skip() function allows the programmer to provide an offset of documents before the limit is applied. 5. Explain the difference in processing when using an explicit and and an implicit and with MongoDB. With both forms of logical and the DBMS must apply criteria to a document to determine if the document should be included in the results. An explicit and, using the $and operator, will determine that a document should not be included and stop applying criteria to that document as soon as one of the criteria evaluates to FALSE for that document. An implicit and will apply all criteria to the document before determining if the document should be included or not in the results. As a result, explicit and tends to perform better in most cases. Problem Solutions For the following set of problems, use the fact database and patron collection created in the text for use with MongoDB. 1. Create a new document in the patron collection. The document should satisfy the following requirements: First name is “Rachel” Last name is “Cunningham” Display name is “Rachel Cunningham” Patron type is student Rachel is 24 years old Rachel has never checked out a book Be certain to use the same keys as already exist in the collection. Be certain capitalization is consistent with the documents already in the collection. Do not store any keys that do not have a value (in other words, no NULLs). db.patron.insert( { fname: "Rachel", lname: "Cunningham", display: "Rachel Cunningham", type: "student", age: 24 } ); 2. Modify the document entered in the previous question with the following data. Do not replace the current document. Rachel has checked out two books on January 25, 2018. The id of the first checkout is “95000” The first book checked out was book number 5237 Book 5237 is titled “Mastering the database environment” Book 5237 was published in 2015 and is in the “database” subject The id of the second checkout is “95001” The second book checked out was book number 5240 Book 5240 is titled “iOS Programming” Book 5240 was published in 2015 and is in the “programming” subject Use the same keys as already exist within the collection. Conform to the existing documents in terms of capitalization. db.patron.update( { "_id": ObjectId("5a45c23f395ff183e78d9c17")}, { $set: {checkouts: [ { “id”: "95000", “year”: "2018", “month”: "1", “day”: "25", “book": 5237", "title": "Mastering the database environment", “pubyear”: "2015", “subject”: "database" }, { "id": "95001", "year": "2018", "month": "1", "day": "25", “book”: "5240", “title”: "iOS Programming", “pubyear”: "2015", “subject”: "Programming" } ] } } ) 3. Write a query to retrieve the _id, display name and age of students that have checked out a book in the cloud subject. db.patron.find({"checkouts.subject":"cloud"}, {display:1, age:1}) 4. Write a query to retrieve only the first name, last name, and type of faculty patrons that have checked out at least one book with the subject “programming”. db.patron.find({type: "faculty", "checkouts.subject":"programming"}, {fname:1, lname:1, type:1, _id:0}) 5. Write a query to retrieve the documents of patrons that are faculty and checked out book 5235, or that are students under the age of 30 that have checked out book 5240. Display the documents in a readable format. db.patron.find({$or: [ {type: "faculty", "checkouts.book":"5235"}, {type: "student", "checkouts.book":5240, age: {$lt:30}} ] } ).pretty() 6. Write a query to display the only the first name, last name, and age of students that are between the ages of 22 and 26. db.patron.find({ type:"student", $and: [{age: {$gte:22}}, {age: {$lte:26}} ] }, {fname:1, lname:1, age:1, _id:0} ) Appendix Q Working with Neo4j NOTE Most of this appendix, and all of the end of appendix problems, require the use of the Ch14_FCC.txt file. The Appendix provides instructions on how to import this file into a Neo4j graph. Answers to Review Questions 1. Explain the difference between using the same variable name and different variable names when matching multiple patterns in Neo4j. Within a given command, all references to a variable are treated as references to the same object (node, edge, or path). Therefore, if the same variable is used in multiple patterns in the same command, then the same node or edge will be required to match both patterns. If different variable names are used, then the node or edge does not have to be the same node or edge in both patterns. 2. What is the difference between using WHERE and embedding properties in a node when creating a pattern in Neo4j? Embedded properties are much more limited. Embedded property specifications are treated as using an equality operator and combined using a logical AND. With a WHERE clause, other operators in addition to equality can be used such as less than, greater than, substrings, etc. Also, criteria in a WHERE clause can be combined with OR connectors as well as AND. Problem Solutions For the following problems, use the Food Critics Club (FCC) graph database that was created and used earlier in the text for use with Neo4j. 1. Create a node that meets the following requirements. Use existing labels and property names as appropriate. The node will be a member, and should be labeled as such, with member id 5000. The member’s name is “Abraham Greenberg”. Abraham was born in 1978, and lives in the state of “OH”. Abraham’s email address is [email protected], and his username is agberg. Create (:Member {mid:5000, fname: "Abraham", lname: "Greenberg", birth: 1978, state: "OH", email: "[email protected]", username: "agberg" } ) 2. Create a restaurant node with restaurant id is 10000, the name “Hungry Much”, and located in Cobb Place, KY. Create (:Restaurant {rid: 10000, name: "Hungry Much", state: "KY", city: "Cobb Place"}) 3. Update the “Hungry Much” restaurant created above to add the phone number “(931) 555-8888”, and a price rating of 2. Match(r :Restaurant {name:"Hungry Much"}) Set r.phone = "(931) 555-8888", r.price = 2 4. Create a REVIEWED relationship between the member created above and the restaurant created above. The review should rate the restaurant as a 5 on taste, service, atmosphere, and value. Match (abe :Member {fname: "Abraham", lname: "Greenberg"}), (hungry :Restaurant {name: "Hungry Much"}) Create (abe) -[rev :REVIEWED {taste: 5, service: 5, atmosphere: 5, value: 5}]-> (hungry) 5. Create a REVIEWED relationship between member Frank Norwood and the restaurant created above. The review should rate the restaurant as a 4 on taste, service, and value, and rate the restaurant as a 2 on atmosphere. Match(frank :Member {fname: "Frank", lname: "Norwood"}), (hungry :Restaurant {name: "Hungry Much"}) Create(frank) -[rev :REVIEWED {taste:4, service: 4, atmosphere: 2, value: 4}]-> (hungry) 6. Write a query to display member Frank Norwood and every restaurant that he has rated as a 4 or above on value. Match (frank :Member {fname: "Frank", lname: "Norwood"}) -[rev :REVIEWED]-> (rest :Restaurant) Where rev.value >= 4 Return frank, rest 7. Write a query to display cuisine, restaurant, and owner for every “American” or “Steakhouse” cuisine restaurant. Match (c :Cuisine) <-- (rest :Restaurant) <-[:OWNS]- (o :Owner) Where c.name = "American" OR c.name = "Steakhouse" Return c, rest, o 8. Write a query to return the shortest path based only on reviews between members Abraham Greenberg and Herb Christopher. Match p = shortestPath((abe :Member {fname: "Abraham", lname: "Greenberg"}) -[:REVIEWED *]- (herb :Member {fname: "Herb", lname: "Christopher"})) Return p Solution Manual for Database Systems: Design, Implementation, and Management Carlos Coronel, Steven Morris 9781337627900, 9781305627482