Studio II.2 Connecting MS Access to the Web

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

Revised: September 2, 2003

This tutorial will focus on the database design of Microsoft Access within an E-commerce application that uses Apache and PHP as the Web Server and Scripting Language respectively.  Studio II.2 is an extension of Studio II.1 in which an introduction to the scripting language PHP was provided as well as a description of the installation of Apache and PHP.  As a result, this studio is not exactly standalone and the reader is encouraged to review Studio II.1 before proceeding.

What you will need to complete this tutorial:

• A Scripting/Programming Language - PHP
• Text Editor (Notepad)
• Web Server - Apache
• Microsoft Access 97, 2000, 2002
• Windows 2000, XP

If you have read Studio II.1, then you will have discovered that it is not necessary to install Apache, PHP and MySQL individually – some open source sites may have assembled them for users, already.

The purpose of the tutorial is to embark upon the development of an E-commerce application by focusing first on the Database Design.  We do provide a description of the needs of such a database, but complete details of the application will be left for studio II.4.

How To Get Records From a Database

The ability to obtain data from a database for use on the Internet was once a harrowing experience, requiring offline updates that only provided static views.  There simply was not an easy way to attach databases to the web. This has changed in recent years mainly due to evolution of Internet technologies as well the richness of database features that have been added to many scripting and programming languages.  The Open Database Connectivity (ODBC) Standard is one of these technologies that has opened up widespread use to various databases, such as Access, Oracle, SQL Server, Sybase and IBM’s DB2 to name a few.  ODBC provides an abstraction layer that hides the specific database access logic of individual databases and allows a programmer to develop programs using ODBC specific commands that understand how to connect to the database.

“Open Database Connectivity is a widely accepted application programming interface (API) for database access. It is based on the Call-Level Interface (CLI) specifications from X/Open and ISO/IEC for database APIs and uses Structured Query Language (SQL) as its database access language [4].”

PHP provides built in support for ODBC so MS Access or any other ODBC compliant database can easily be used with this scripting language.  Figure 1, provides a description of how a database is connected to the Internet.

Figure 1 - How Internet Database Access Works

• You click on a link from your web browser http://www.IwantanAinthisclass.edu/index.php.
  That sends a request to the web server for the file index.extension (where extension is .php in this instance, but could be .cfm, .jsp or .asp )
• Our web server has already been configured to know that *.php is handled by another the PHP engine, so the web server sends the file to the engine for processing.
• The PHP engine reads the page and processes all commands within the page. If it encounters a command that accesses a database then it retrieves the results from the database, if necessary, and inserts these results into the web page.
• PHP converts all commands and statements results to HTML and returns the request to Web server, which then sends requested page back to back to you.
  

MusicWeb E-commerce Application - Database Design

MusicWeb is a RPI student-run E-commerce website*** that provides two services:  The company acts as a Manager/Agent/Recording Company for Musicians seeking representation in the tremendously cutthroat Entertainment business.  The Musician interacts with the company electronically and transfers all rights and electronic files via the Companies comprehensive Internet website. The Company also offers for sales Compact Discs (CD’s) of the Artists music as well as compilations of Various Artists, all provided on the company’s Internet storefront.  Production of the CD’s, that is, arranging of music as well as production of the CD’s (including cases and labels) are all done “in-house” with the parts such as paper and CD cases being obtained by various suppliers.  Figure 2 shows the Entity- Relationship Model for the E-commerce website.

Figure 2 - Entity Relationship Model of Ecommerce Website

Three users will access the application: Customers, Suppliers and Artists.  More than one Supplier can provide more than one Part and more than one Part can belong to more than one Supplier.  The Plural Relationship (PR) identifies this relationship, ¯.  This type of relationship also applies to CD/Part, CD/SongFile, CD/Song Category, and Parts/Supplier. 

A Customer can have more than one credit card but each credit card belongs to only one customer.  This Functional Relationship (FR), ¯, indicates a many-to-one relationship.  This applies to Order/Orderline, Artist/Song, Customer/Order and Song/Song Category.

Each OrderLine comprises an Order, which is defined by a Mandatory Relationship, ³.  An order can have more than one OrderLine, but each OrderLine belongs to only one order.

Figure 3 provides a conceptual view of the database and shows the attributes that we are interested in. Customer and Credit Card data, as well as Supplier and Artist Data will be captured in individual tables.  Since the Artist provides music files to the service we will also capture the Song MetaData data as well as the Song File data and place these in separate files.  Each Product created from the Song File will belong to a specific category, which is defined in the Category and Category Family table.   Lastly, the parts provided by the suppliers are captured in the Parts Table.  The fieldnames that are underlined and in bold text in Figure 3 indicate the primary keys (PK) of the table. The (AK) appended to a fieldname indicates an alternate key.

We can now move on to the development of the actual design of the database in Microsoft Access.  If you have never worked with Microsoft Access the tutorials listed in the references will provide a very good starting point.  This tutorial assumes basic knowledge of Microsoft Access. 

Open Microsoft Access and create a Blank Access Database and give it the filename Ecom.  Make a note of the directory in which the database is being created, as we will need this information later.

In whatever way you are comfortable using Microsoft Access, begin to create the tables shown in Figure 3.  The text above the series of dashes, that is, “-----------” is the table name and those below the dashes are the attributes or field names.

Figure 4 - Database Design of MusicWeb in MS Access (View Larger Image)

Once all tables are created we then have to enforce the referential integrity of the database - This is achieved in MS Access 2000 by using the function Relationships found under the Tools Menu.  Select this function and then select all the tables shown in the “Show Table” window.

Enforce referential integrity by creating the relationships shown in Figure 3.  For example, a credit card has to belong to a customer or not exist at all.  That means, the CustomerID field in the Credit Card Table is a Foreign Key that is related to the CustomerID field in the Customers Table.  Repeat this as required for the remaining relationships. 

Figure 4 gives a complete description of the Access Database Design.  The integrity relationships are manifested in the lines drawn from one table to another table. 

User Scenarios for Database

These scenarios show some external processes or perspectives of the information required or concerned. The base tables involved in each scenario are enclosed in [BRACKETS].

Customer Ordering

The process of customers ordering CDs involve information from several base tables. For instance, a customer would select a series of songs from the information about all songs offered in order to have the company make a custom burned CD [CD]. The system, therefore, will need generic song information [SONG] and artist information [ARTIST] as well as the genre of the song [CATEGORY] and [FAMILY]. How and what data to assemble is a matter of implementation design for the application (or home page). The customers selections become the contents of CDs [CD PLAYLIST]. The customers would also furnish their personal information [CUSTOMER] including billing information [CREDIT CARD] when they build an order stage [ORDER] and [ORDERLINE].

CD Catalog

The company provides pre-made CDs for customers to choose and buy, as well. Thus, it shows a standard catalog [CATALOG] of such CD's made by the company. Periodically, the company looks at the performance of its musicians and identifies those making small sales. In an effort to improve its ROI, the company compiles [CD PLAYLIST] songs [SONG] and designates CDs [CD] based on its online, real time monitoring of the sales of both pre-made CDs and custom burned CDs. In essence, this perspective is technically similar to the previous custom CD ordering. The company is a customer of itself, except that these compilations are offered for sale on the company website and no transaction information (customer, order and so on) is involved.

Production Process

The company manufactures [PRODUCTION] CDs for the designated CDs [CD] according to the ordering information (such as the custom-designated CD title and description) [ORDERLINE]. Each physical copy of a CD, even if it is one of multiple copies of the same designated CD, is given a different serial # when it is assembled in production. The process uses a part [PART] which we define here to be the CD platter itself. The Production process centers on burning the digitized song files [SONGFILE] into the CD according to the content [CD PLAYLIST] of the designated CD. The process also invloves acquiring parts from suppliers [SUPPLIER] in batches of supply [SUPPLY].

Song Collection

The company has developed a good track record in producing good musical talents and finds no difficulty in attracting new artists. The information about its transactions/contracts with them [COLLECTION] manifests this working relationship. The information concerns the particular music pieces that artists [ARTIST] offer (sing) of songs [SONG] at possibly different times - i.e., there could be multiple versions of a song sung by the same artist.

Connecting the database to the Web Server

On Win9x, create the ODBC Data Source.  This application adds, removes, and configures Open Database Connectivity (ODBC) data sources and drivers.

• Go to the Start Menu
• Click Settings
• Click Control Panel
• Click ODBC Data Sources or Data Sources (ODBC) whichever is present

i.e.  ( Start -> Settings -> Control Panel -> ODBC Data Sources ) .

Once in the Data Sources click on the System DSN tab.  Figure 5 gives a description of what should appear.  Click Add ... to select the database driver you want. In this case we want to use the Microsoft Access Driver, (*.mdb).  After choosing the MS Access Driver, click Finish – you will have to select your database and provide a name for this connection at this point.

Figure 5 - Microsoft Access Driver Selection

Choose configure then type a name to represent this connection in the Data Source Name box and give it a description. Then click "Select..." to locate your database. Choose OK and you are all set to go. (See Figure 6)

Figure 6 - DSN Definition

Figure 7 - System DSN Tab

By clicking the "Advanced ..." button we are able to provide other database specific information such as passwords, permissions and so on, but we do not need that in this case.

Figure 7 gives the completed ODBC Data Source.  In this instance we have labeled our DSN “Ecom” and it is connected to our Access database located in the directory specified in Figure 3.

Creating Dynamic Web Pages with PHP

As stated in Studio II.1 and the beginning of this tutorial, PHP has native support for a number of popular commercial and open source databases.  This native support includes a rich subset of commands that can be used to support data entry and retrieval.  The following scripts shed some light on these commands, specifically built-in ODBC statements.

Figure 8 shows information entered into the Customer Table.

1. <?php
2. $result = odbc_connect('Ecom','','');
3.
4. $sql="INSERT INTO Customer
5. (CustomerName,CustomerAddress,CustomerCity,
6. CustomerState,CustomerZip,CustomerTelephone,CustomerEmail)
7. VALUES('Shirley-Ann Jackson','110 Eighth
8. St.','Troy','NY',12180,5182766,'president@rpi.edu')";
9. $exc = odbc_exec($result,$sql);
10. if($exc) print "Customer information entered into database";
11. ?>

Figure 8 - Insert Data into the Customer Table

Line 2 in Figure 8 uses the command ODBC_CONNECT to establish a connection to the ODBC database, Ecom. The command “Returns an ODBC connection id or 0 (FALSE) on error. The connection id returned by this function is needed by other ODBC functions. [8]”

Lines 4 to 8 uses a standard SQL insert statement to enter information into the database, Ecom. 

Line 9 uses the ODBC_EXEC command to process the query. The command “Returns FALSE on error. Returns an ODBC result identifier if the SQL command was executed successfully.[8]”

If $exc in line 10 is not false the script prints the indicated statement to the screen. Figure 9 indicates a successful query.

Figure 9 - Insert Data into the Customer Table

A check of the actual MS Access database will show that the information has been entered successfully.  This can be repeated for Suppliers and Artists.  Note that if this is performed for Credit Cards, the Customer must exist in the Customer Table before the Database will accept it.

Obtain the actual MS access database here, but we do recommend you try and build the tables yourself in order to gain a full understanding of what we are trying to capture in the database.

Conclusion

We have created a database to support the MusicWeb E-commerce Application. While MS Access satisfies a number of the application requirements, specifically referential integrity it does not address a number of others, such as triggers and table-locking. A number of these functions can be satisfied with a PHP script but nothing replaces the built-in capabilities of more full-featured databases.

Deliverables

Part I

1. Populate all tables in the MS Access database with at least 5 records per table, taking into account the referential integrity between the tables. Submit a hard-copy of the table layout. Hint: Create screenshots of the table layout in MS Access or use the print function in the datasheet view.

Part II (Optional)

1. Access is not designed for use as an "internet" database. Why? What database would you suggest for use in this application and why?
2. Duplicate the process above for your "suggested" database. Two popular, high quality, and free databases are MySQL and PostgreSQL.

References

1. http://www.php.net/manual/install-windows95-nt.php
2. http://www.devshed.com/Server_Side/PHP/DB_Basics/
3. http://hotwired.lycos.com/webmonkey/99/21/index2a.html?tw=programming
4. http://www.awtrey.com/support/dbeweb/
5. http://www.microsoft.com/data/odbc/default.htm
6. http://php.weblogs.com/odbc
7. http://www.phpbuilder.com/columns/moon19990716.php3
8. http://www.php.net

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.