Enterprise Integration & Modeling: Metadatabase Research Home
 home || MDB Research | Virtual Lab | Case Tool | Downloads | Publications | Researchers | Links

Studio II.4 MusicWeb: A Customer Ordering System

Author: David Levermore (leverd@alum.rpi.edu)

The Scenario

MusicWeb is a student-run company at Rensselaer Polytechnic Institute*** that offers custom-made music CD's for sale over the Internet. The company is recognized as a leading producer of musical talent in all genres', and to date is highly sought after by young and emerging musical talent. The company's core business is an e-commerce exchange that aggregates music from, primarily, new musicians and allows customers to create custom made music CD's from various artists.

MusicWeb obtains its database of songs from independent artists that communicate with MusicWeb via the Internet. An artist desiring to enter into an agreement with MusicWeb, responds to a Request for Proposal (RFP) that exists on MusicWeb's corporate website. The RFP includes an extensible markup language (XML) template that all artists must use in communicating song information to MusicWeb. The XML template facilitates the transfer of all song details, that is, song data, links to song file(s) and artist information via the Internet to MusicWeb. The artist and MusicWeb share in the proceeds obtain from the sale of songs on the MusicWeb e-commerce exchange.

MusicWeb receives it raw materials (CD cases, labels) from a number of suppliers that communicate with MusicWeb via the Internet. A supplier desirous of entering into an agreement with MusicWeb, responds to a Request for Proposal (RFP) that exists on MusicWeb's corporate website. The RFP includes an XML template that all suppliers must use in communicating product information to MusicWeb. MusicWeb commits the product information from a "partner" supplier to its inventory using a proprietary XML processing system.

A customer visiting the MusicWeb service has the ability to browse the expansive MusicWeb database of songs, and can customize any number of music CD's for purchase within a single visit. The customer can also save these "play-lists" for future visits. Each song is priced individually and a CD can hold at most twelve (12) songs.

When an order is placed, the play-lists are saved to the order file and sent to our production facility. MusicWeb has an in-house CD production facility that produces the music CD's and packages them for delivery, which is handled by any package carrier such as UPS, FedEx or USPS.

This basic model is expandable to including other visions. For instance, the company could practice e-business on its own operational processes and connect them to the systems of external constituencies such as music portals and recording companies in an extended enterprise manner. It could even transform itself into an Exchange to develop the peer-to-peer market complementing the traditional value chain. These additional visions would be good candidates for future investigation and exploration.

Aside: We make extensive use of built-in PHP commands in this studio, and use the online PHP manual extensively. If at any point you wish to get the definition of a function used in any of the scripts that are discussed later, you can always perform a search using the following:

http://www.php.net/function_name

For example, to perform a search on odbc_result we would do the following:

http://www.php.net/odbc_result

Note on the Technology: We recommend that you download phpdev5 (a program that supports PHP 4), Apache 2, and MySQL 4 (see Studio II.1). On the PHP.ini file, the register_globals should be changed to "ON", and in the MS Access database, the 'ParentCategory' of the 'CategoryFamily' table should be changed to 'ALL' for each of the category names.

Introduction

The following tutorial illustrates the development of an e-commerce application to satisfy the requirements as specified in the overview above. The application builds upon the material presented in studios II.2 and II.3, and to some extent studio II.1 although more involved PHP scripts are used. It is recommended that, if not done so already, the previous three tutorials be completed.

The design and development of all software applications demands the analysis of the underlying application architecture and its requirements (see studio III), however we forgo this analysis here for the simple reason that this tutorial provides an overview of the development an e-commerce application. While we will discuss the various components this application, we will only implement a few for demonstration purposes. Disclaimer: The application designed herein should not be used in a production environment without the complete understanding of its security and performance requirements.

Figure 1 - Entity Relationship Model of MusicWeb Application

Figure 1 - Entity Relationship Model of MusicWeb Application

Studio II.2 presented the database requirements of the application that is repeated here only for convenience. The entity relationship diagram is presented in Figure 1 and the database table layout views are presented in Figure 2. The exercises required of studio II.2 indicated that the database be populated with data. If this was not done, obtain the populated database here.

MusicWeb - The E-commerce Application

Because of the complexity of the website we first present the completed product and discuss the individual components used to construct the website. Only two significant components of the website have been constructed, the remaining components (identified later) are left as an exercise for the reader at the end of the tutorial. The two components of the website that are addressed are:

  1. Product Catalog, which displays the songs categorized by genre
  2. Session Management, identify and track customers as they move through the MusicWeb application
Figure 2 - Conceptual View of MusicWeb Database
Figure 2 - Conceptual View of MusicWeb Database

The start page as shown in Figure 3 displays current news, and alerts (in our case this is not dynamic, but static) and offers links to the different categories of music available from the website. The categories shown on the left in Figure 3 are dynamically generated and pulled from the MS Access database (See Technical Note Below). Additionally, the numbers of items in the shopping cart are shown in the upper right hand corner below the date (which by the way is also dynamically generated). When an item is added to the cart, the number is incremented. The PHP code responsible for these functions can be found in the files func.php, and counter.php which have been commented to make them easy to understand.

Figure 3 - Start Page for MusicWeb
Figure 3 - Start Page for MusicWeb

The entire website is built upon a single HTML template with embedded PHP code. At the top of each page you will find two lines of PHP code, session_start(), include("database_odbc.php") and include("func.php"). Session_start() is explained later, while include() allows us to import external files and use them in the current page. In this case the PHP scripts in database_odbc.php and func.php are available for use in our index.php start page and all pages existing in the website.

Product Catalog

The product catalog showcases the artists and their songs along with the cost of these songs. The catalog is viewable by two methods, category and artist. Figure 4 describes the process by category. By clicking on the classical link available on any page we obtain the list of songs that belong to this category, in this case only two songs. By clicking on a highlighted Artist link, we can obtain the listings unique to that artist. The files prodlist.php and by_artist.php contain the PHP code that enables us to manipulate this dynamic content.

Figure 4 - Product Catalog by Category
Figure 4 - Product Catalog by Category

We can obtain more information on the song by selecting the highlighted song title link available on the prodlist.php page or by_artist.php webpage. The song description page as shown in Figure 5 gives us complete details of the song and also allows us to add this item to our shopping cart. The only detail that was not included in our MS Access database was a description or further details on the song, which is why there is nothing to qualify Song Description in Figure 5 below. The code responsible for this page can be found in song_description.php.

Aside: It is important to note that we can always tell what category a song title belongs to, simply by looking at the bold entry in the categories menu on the left. In this case we are still in the Classical category. We have propagated this information in a rather crude manner - it is hard-coded in each URL that is used in the website. Imagine if we had thousands of dynamically created pages, this would not be a good design! We leave the investigation of other options for propagating the categories across different pages up to the reader.

Figure 5 - Song Description on MusicWeb
Figure 5 - Song Description on MusicWeb

Shopping Cart

The shopping cart provides a summary of the items selected by the customer as shown in Figure 6. This code can be found in viewcart.php and is also available by selecting VIEW in the upper right-hand corner of the webpage. To create this dynamic page, we first access the information stored in out session (see Session Management) which is the product ID and use this information to query our database on the specific product. We are then able to access the Song Title and Cost. The quantity here is simply used as a sanity check for the customer to display clearly how the numbers are generated in the total.

The customer is also able to remove an item from the cart by selecting the "X" which actually removes the product item from the session discussed previously.

Once the customer is satisfied with the selected items, the Checkout >> button can be selected, where customer information including personal details, shipping and billing information can be obtained. This feature has not been implemented and is left as an exercise for the reader at the end of the tutorial.

Figure 6 - Cart Contents on MusicWeb
Figure 6 - Cart Contents on MusicWeb

Session Management

Two customers visit an e-commerce website we will called NotSecure Website and proceed to purchase items as shown in Figure 7; Customer A selects product C001 and customer B selects product D999. Conceptually, as shown in Table 1, each transaction is pushed to an order database where a problem is created. Which customer ordered what product? Of course we could collect the customer information at the same time the individual order is added to the cart, but this is not a good design given the obvious reason - what would happen if another item is added to the order!


Figure 7 - Customer Ordering Products from MusicWeb
Figure 7 - Customer Ordering Products from MusicWeb

Hypertext Transport Protocol (HTTP) is the communications protocol of the Internet which allows customer A and B to interact with NotSecure Website. When customer A or B requests an item from NotSecure Website a connection is open with the server, which then sends the required document to the requester and then the connection is closed. Subsequent connections are independent of previous connections and thus no state is transferred from one or the other. HTTP is considered a stateless protocol.

Product Quantity Order Date
C001 2 1/18/2002 7:11:02 PM
D999 5 1/18/2002 7:11:02 PM
Table 1 - Conceptual Order Database on NotSecure Website

State can be maintained with the use of Session Management, currently a function of programming or scripting languages such as PHP, JSP and similar applications. Session Management assigns a unique ID as shown in Table 2, to a customer of the website and maintains this association for the length of time designed in the application.

Product Quantity Order Date Session ID
C001 2 1/18/2002 7:11:02 PM c26e8178126688de
D999 5 1/18/2002 7:11:02 PM b863604bef4b0cda
Table 2 - Conceptual Order Database showing Session ID 's

With this model any number of items can be added to the order table and these will be associated with the session ID. At some other convenient time we can collect the customer information, and add it to the session.

In the case of MusicWeb, sessions are created by the built-in PHP function session_start() that we have placed at the top of each page in the website. If the session does not exist prior to a user visiting the website it will be created and associated with that specific user. Session_start() creates a unique ID and saves this to the temporary directory as defined in the PHP configuration file (see A Technical Note).

MusicWeb stores the $cart variable which is an array that contains other arrays. A variable us registered with the session by the use of the session_register() function. When a song title is added to our cart we first create an array and then assign the song ID and quantity required. We actually do not use the quantity in our application but have included it for demonstrative purposes. This array is then added to the array $cart. Table 3 provides a better description of this.

Item Details\Item # 0 1 2 ... n
Product_ID 12345 678910 234909 ... 234234
Quantity 1 1 1 ... 1
... ... ... ... ... ...
m ## ## ## ... ##
Table 3 - Description of Cart Contents

To select a specific occurrence in the cart, as we do when we want to delete an item from the cart, on the view cart page, we first have to iterate through the different item arrays, i.e. 0, 1, 2 ... n and then look at the individual item details - Product_ID through item m.

Conclusion

We have endeavored to show the process of creating an e-business application and make the assumption that this studio achieves this task. As stated before this application should not be used in a production environment without the full understanding of the security and performance issues as well as the limitations associated with the tools used to create the application - in this case, PHP, Apache and MS Access. Essentially, the development of any e-business application requires planning, time and people resources (experts in specific fields related to the application, e.g. programmers, security experts, operating systems and databases). Feel free to download this application which has been "zipped" for your convenience. Follow the instructions in "A Technical Note" to ensure that you are able to correctly set this up on your Windows PC.

Technical Notes

In some cases the php.ini file provided in the Merlin Desktop, Merlin Server as well as the individual install of Apache and PHP, is not properly configured for session management. For sessions to work properly on Microsoft Windows, we must make some changes to the php.ini file.

The php.ini file can be found in the c:\windows directory. If it is not there perform a search of your PC using find found at

Start > Programs > Find. 

Make a backup of the php.ini file (e.g. php.bak) when it is found so that we can quickly fall back on it if any problems arise. Open the php.ini file in Notepad and scroll down towards the end of the document. Look for the following text:

session.save_path = /tmp

And rewrite it to show,

session.save_path = c:\windows\temp 

or wherever your Temp directory is located. Close notepad and then restart Apache by first stopping it and then starting it:

Start > Programs > Abriasoft > Stop Apache
Start > Programs > Abriasoft > Start Apache

Register Globals

Change register_globals in your PHP.INI file to ON. You should do your best to write your scripts so that they do not require register_globals to be on. Using form variables as globals can easily lead to possible security problems, if the code is not very well thought of. However, for the purposes of this tutorial register globals must be on.

Category Names

In func.php, function toc(), the top level category has the name "ALL" which may not be the case for an independently populated database, as suggested in Studio II.2. Take this into consideration when executing the PHP scripts.

Extracting musicweb.zip

Use Winzip or similar application to extract the contents of musicweb.zip to your web directory. For simplicity create a directory called musicweb and extract the contents of musicweb.zip to this directory.

The database_odbc.php (line 11) file references a DSN called ecom. You should already have this set up, if not review studio II.2 to see how this is done. Make sure you choose the name ecom when creating the System DSN in the Control Panel.

Open up your browser and type the URL, http://127.0.0.1/musicweb/ and you should see your locally hosted MusicWeb website similar to ours.

Limitations

It is important to note the following deficiencies in the MusicWeb E-commerce application:

  • No error checking made on data that is passed through the URL, more specifically no data checking is made on the category ID or song ID's. Anything can be inserted and the system will accept it so some data validation should take place here.
  • Sessions may not be secure. It is possible to hijack a session, that is, another user assumes the role of a user registered with a site through sessions.
  • MS Access is not an enterprise database application. It simply fails in the area of the management of concurrent user access. For an enterprise system these problems need to be addressed or a more robust database (Oracle, SQL Server, and PostgreSQL) used for this e-commerce system.

Deliverables

Part I

  1. The MusicWeb application is provided to you in the musicweb.zip file. Extract the contents of this zip file into the root directory of the Merlin Desktop directory. Make sure the System DSN for the MS Access database has the name Ecom (see "A Technical Note"). Open the MusicWeb application and read through the comments in the individual files. Provide screenshots of the application that showcases the data you input in Studio II.2.
  2. The MusicWeb application discussed above omits a number of key components that you should create. Create the PHP scripts and web pages that will fulfill one of the following:
    • The customer checkout process. Collect the customer information including credit card, shipping and billing information.
    • Web-based administration of the MS Access database.
    • Order fulfillment. Create a system that takes the order and carries it through to pickup by a package carrier.

Part II (Optional)

  1. Payment processing need not be an in-house function. These can be handled by third-party vendors such as Paypal and AuthorizeNet. Investigate Paypal and other providers you find and discuss how you would tie these into the MusicWeb application.

References

  1. PHP, http://www.php.net
  2. PHPBuilder.com, http://www.phpbuilder.com
  3. Developer Shed, http://www.devshed.com Speaking SQL
  4. Ratschiller, Tobias, and Gerken, Till. Web Application Development with PHP. Indianapolis: New Riders Publishing, 2000

Resources

Disclaimer

MusicWeb is a fictitious company and has no relationship with any enterprise or real world business that may have MusicWeb in their names. This tutorial was created to facilitate student education (at Rensselaer Polytechnic Institute) in creating dynamic database-driven websites.

 

viu.eng.rpi.edu is hosted by Professor Cheng Hsu.
Rensselaer Polytechnic Institute
Department of Industrial and Systems Engineering (formally Decision Sciences & Engineering Systems)
110 8th St., Center for Industrial Innovation, Room 5123, Troy, NY 12180-3590

Copyright © 1997-2016. MetaWorld, Nothing on this site may be commercially used without written consent.

Valid XHTML 1.0! Valid CSS!