Technical Paper Project TARGET ARCHERY SHOP TABLE OF CONTENTS I. Part 1: Planning 3-5 II. Part 2: Implementation 6-12 III. Part 3: Data Relationships 12-15 IV. Part 4: Development 16-17 V. Sources Cited 18 Part 1: PLANNING Good planning provides focus on the functionality of the application. This prevents writing pieces for the application that do cool things but later turn out to have no real purpose in the finished application. If more than one person works on the application, planning ensures that all the pieces fit together in the end. Requirements from the application The initial step in the planning phase is to identify exactly why the application is being developed. For Target Archery Shop, the main purpose will be to: o Collect names and addresses from users so that a customers list can be developed. o Deliver information about products to users, similar to a customer catalog. o Sell products online. o Provide technical support to people who have already purchased a product. After clearly identifying the general purpose of the application, a list is to be created on what is required of the application. For Target Archery, the goal is to develop a database of customer names and addresses for marketing purposes. o Provide a form for customers to fill out. o Store the customer information in a database. Products to the customers Information o Display a list of product categories. Each category is a link. o When the customer clicks a category link, the list of products in that category is displayed. Each product name is a link. o When a customer clicks a product link, the description of the product is displayed. Product Marketing o Provide well-written descriptions of the products that communicate their obviously superior qualities. o Use flattering pictures of the products. o Make color product brochures available online. o Offer quantity discounts. Online Product Purchase o Provide a button that customers can click to indicate their intention to buy the product. o Provide a form that collects necessary information about the product the customer is ordering, such as size, color, and so on. o Compute and display the total cost for all items in the order. o Compute and display the shipping costs. o Compute and display the sales tax. o Provide forms for customers to enter shipping and billing addresses. Online Purchase Payment o Provide a button that customers can click to pay with a credit card. o Display a form that collects customers’ credit card information. Payment Validation The usual method is to send the customer’s credit card information to a credit card processing service. Taking the user into consideration Identifying what is required from the Web database application is only one aspect of planning. What the users will want from it must also be considered. For Target Archery, the goal is to gather a list of customer names and addresses for marketing purposes. The application needs to fulfill a purpose for the users as well as for the shop. Otherwise, customers will just ignore it. Before users will be willing to give their names and addresses, they need to perceive that they will benefit in some way from giving this information. Following are some of the common reasons on why customers might be willing to register their names and addresses to the site: o Free Newsletter Subscription: To be perceived as valuable, the newsletter should cover an industry related to the products of Target Archery Shop. It should offer news and sports trends, particularly Archery — and not just serve as marketing material about the shop’s products and services. o Special Discounts Offer: Periodically e-mail special products and services discount opportunities to customers. o Notification about new products: Customers might be interested in being notified when a new product is available for sale and the store. o After having a list of tasks that you and the users want, have a plan for a Web application that is worth the time to develop and worth the users’ time to use. (Nielsen, Jakob) Making the site easy to use In planning what the Web application is going to do, we must also consider how the application is going to do it. Making the application easy to use is of utmost importance. If customers can’t find the shop’s products, they aren’t going to buy them. If customers can’t find the information that they need in a reasonable length of time, they will go look elsewhere. On the Web, customers can always decide to easily go elsewhere. Making the application easy to use is “usability engineering”. Web usability includes issues that must be considered before being implemented. o Navigation: What is on the site and where it is located should be immediately obvious to a user. o Graphics: Graphics make your site attractive, but too much graphic files can slow down display, sometimes frustrating impatient viewers. o Access: Design decisions must be made putting into consideration the application’s accessibility to users who have impairment.
o Browsers: Different browsers can display the same HTML (HyperText Markup Language) file differently. (Tiller, Eugene & Green, Phillip) Part 2: IMPLEMENTATION Products for Sale Target Archery Shop is a sports store catering mainly to Archery. Therefore, the shop’s catalog must provide customers with information about Archery and related sports. Selling the Archery products online is not feasible, in the sense that most customers feel there is a need to try out a product first in order for them to decide whether to buy or not. Currently, the application is simply intended as an online catalog. Customers can look through the catalog online and then come into the store to buy the product. The information about all the shop’s products is stored in a database, and customers can search the database for information on specific products and types of services. Following is the Implementation Plan for the application: Allow customers to select which product they want to get information about. Method # 1: Selection o Selecting from a list of links. Display a list of links that are product categories: When the customer clicks a category link, a list of products is displayed. Each product in the list is a link to a description of the product. o Typing in search terms. Display a search form in which customers can type words that describe the type of product they are searching: The application searches the database for matching words and displays the product information for any products that matches the search words. o Display description of a product when customer clicks the link. The description is stored in a database. (Nielsen, Jakob) Method 2: Members Only The second Web database application method is related to the preceding method. In addition to the online catalog, we need to put up a “Members Only” section in the Target Archery Web. In order to access this area, customers need to register by providing their names and addresses. In the Members Only section, customers can order Archery and other sports products at a discount, track status of products they ordered but have not arrived yet, and also gain privileged access to articles with news and information about the sports Archery. Below is the plan for this application: o Area where customers can register for the Members Only section. %uF0D8 Provide a link to the registration area. %uF0D8 Display a form in the registration area where customers can type their registration information. The form should include space for a user login name and password as well as the information that needs to be collected from them. %uF0D8 Validate the information that the user entered. Verify that the ZIP code is the correct length, the e-mail address is in the correct format. %uF0D8 Store the information in the database. o Provide a login section for customers who are already registered for the Members Only section. %uF0D8 Display a login form that asks for the customer’s user name and password. %uF0D8 Compare the user name and password that are entered with the user names and passwords in the database. If no match is found, display an error message. o Display the Members Only Web page after the customer successfully logged in. (Nielsen, Jakob). DESIGNING THE DATABASE Designing the database includes identifying the needed data and organizing it in the way required by the database software. First, identify what information belongs in the database. In the case of Target Archery, the application will include a task that collects information from the user. This will require balancing the urge to collect all the potentially useful information that you can think of against your users’ reluctance to give out personal information--as well as their avoidance of forms that are time consuming. One compromise is to ask for some optional information. Users who object to enter information can leave it blank. Another possibility is to offer an incentive. For this application, Target Archery customers search the online catalog for information on products that they might want to buy. We want customers to see information that will motivate them to buy a product. Among the priority information are: o Name of a particular product o Description of the product o Picture/s of the product o The cost/price of the product In the Members Only section, we want to store information about registered members. The information that needs to be stored in the database are: o Member name o Member address o Member phone number o Member fax number o Member e-mail address Additionally, we need to take the time to develop a comprehensive list of the information that needs to be stored in Target Archery database. Although we can change and add information to the database after it is developed, putting in all the necessary information from the beginning is much easier. ORGANIZING THE DATA MySQL is a Relational Database Management System (RDBMS), which means that the data is organized into tables.
Relationships can be established between the tables in the database. (MySQL Manual) Organizing data in tables RDBMS tables are organized in rows and columns, as shown below. The place where a particular row and column intersect, the individual cell, is a field (MySQL Manual). The focus of each table is an object that we want to store information about. Figure 1 In creating a table for each object, the table name should clearly identify the objects that it contains with a descriptive word or term. The name must be a character string with no spaces in it. The table name can contain letters, numbers, underscores, or dollar signs. It is customary to name the table in the singular. Thus, a name for a table of customers might be Customer, and a table containing customer orders might be named CustomerOrder. Upper and lowercase is significant on Linux/Unix but not on Windows. CustomerOrder and Customerorder are the same to Windows but not to Linux or Unix. (MySQL Manual). In database lingo, an object is an entity, and an entity has attributes (MySQL Manual). In the table, each row represents an entity, and the columns contain the attributes of each entity. For example, in a table of customers, each row contains information for a single customer. Some of the attributes contained in the columns might be first name, last name, phone number, age, and so on. Below are steps for organizing data into tables: 1. Database Name: Assign a name to the database for your application. For instance, a database containing information about archery products for kids can be named ArcheryChildren. 2. Objects Identification: Look at the list of information that needs to be stored in the database. Analyze the list and identify the objects. For instance, the ArcheryChildren database might need to store the following: o Name of child for whom the product is intended o Age of the child o Home or billing Address o Phone number o Favorite sports of other children in the family By carefully analyzing this list, we can see that we are storing information about two objects: the purchasing customer and the child. That is, the address and phone number are for the customer in general, but the name, age, and favorite sports are for the child. 3. Define and name a table for each object: For instance, the ArcheryChildren database needs a table called ChildInfo and a table called HouseholdInfo. 4. Identify the attributes for each object: Analyze the information list and identify the attributes that needs to be stored for each object. Break the information to be stored into its smallest reasonable pieces. (MySQL Manual). For example, when storing the name of a person in a table, break down the name into first name and last name. This enables sorting by the last name, which would be more difficult if the first and last name were stored together. We can also break down the name into first name, middle name, and last name, though not many applications need to use the middle name separately. (MySQL Manual). 5. Define and name columns for each separate attribute identified in Step 4: We then give each column a name that clearly identifies the information in that column. The column names should be one word, with no spaces. For example, columns named firstName and lastName or first_name and last_name. Some words are reserved by MySQL or SQL for its own use and can not be used as column names. The words are currently used in SQL statements or are reserved for future use. The words ADD, ALL, AND, CREATE, DROP, GROUP, ORDER, RETURN, SELECT, SET, TABLE, USE, WHERE and many more, can not be used as column names. Refer to the online manual for complete list of reserved words. (MySQL Words). 6. Identify the primary key: Each row in a table needs a unique identifier. No two rows in a table should be exactly the same. In designing a table, decide which column holds the unique identifier, called the primary key. The primary key can be more than one column combined. In many cases, the object attributes will not have a unique identifier. (MySQL Manual). For example, a customer table might not have a unique identifier because two customers can have the same name. When there is no unique identifier column, you need to add a column specifically to be the primary key. Frequently, a column with a sequence number is used for this purpose. In the table below, the primary key is the cust_id field because each customer has a unique ID number. (Figure 2) 7. Define the defaults: Define a default that MySQL will assign to a field when no data is entered into the field. A default is not required but is often useful. (MySQL Manual. If the application stores an address that includes a country, specify US as the default. If the user does not type a country, US will be entered. 8. Identify columns with required data: Specify that certain columns are not allowed to be empty (also called NULL). The column containing the primary key can not be empty.
That means that MySQL will not create the row if no value is stored in the column. The value can be a blank space or an empty string, but some value must be stored in the column. Set other columns, as well as the primary key, to be in error if they are empty. (MySQL Manual). Properly designed databases store each piece of information in just one place. Storing it in more than one place is inefficient and creates problems if information needs to be changed. If information is changed in one place but is not changed in another place, the database might have problems. If the same data is being stored in several rows, the tables must be reorganized. Part 3: DATA RELATIONSHIPS Building Relationships A number of tables in a database are related to one another. Mostly, a row in one table is related to several rows in another table. A column is needed to connect the related rows in different tables. In most cases, a column is included in one table to hold data that matches data in the primary key column of another table. (MySQL Manual). A common application that needs a database with two related tables is a customer order application. One table contains the customer information, such as name, address, phone, and so on. Each customer can have from zero to many orders. We can store the order information in the table with the customer information, but a completely new row would be created each time that the customer places an order. It would be much more efficient to store the orders in a separate table. The Order table would have a column that contains the primary key from a row in the Customer table so that the order is related to the correct row of the Customer table. The relationship is shown in the tables in Figures 2 and 3. The Customer table looks like the table below (Figure 3). Notice the unique cust_id for each customer. The related Order table is shown below (Figure 3). Take note that it has the same cust_id column that appears in the Customer table. In this way, the order information in the Order table is connected to the related customer’s name and phone number in the Customer table. (Figure 3) TYPES OF DATA MySQL stores information in different formats based on the type of information that MySQL is to expect. MySQL allows different types of data to be used in various ways. The principal types of data are character, numerical, and date/time data. (MySQL Manual). Character data is one of the most common types of data that is stored as strings of characters and can only be manipulated in strings. Most of the information that will be stored is character data, such as customer name, address, phone number, pet description, and others. Character data can be moved and printed. Two character strings can be put together, a sub-string can be selected from a longer string, and one string can be substituted for another. Character data can be stored in a fixed-length format or a variable-length format. (MySQL Manual). o Fixed-length format: MySQL reserves a fixed space for the data in this type of format. If the data is longer than the fixed length, only the characters that fit are stored, the remaining characters on the end are not stored. If the string is shorter than the fixed length, the extra spaces are left empty. (MySQL Manual). o Variable-length format: MySQL stores the string in a field that is the same length as the string in this format. One must still specify a length for the string, but if the string is shorter than the specified length, MySQL only uses the space required rather than leaving the extra space empty. If the string is longer than the space specified, the extra characters are not stored. (MySQL Manual). If a character string length varies only a little, use the fixed-length format. If the ZIP code does not include the ZIP+4 number, only five spaces are left empty. However, if character string varies more than a few characters, use a variable-length format to save space. Numerical data is another common type of data consisting of data that is stored as a number. Decimal numbers can be stored as well as integers. When data is stored as a number, it can be used in numerical operations, such as adding, subtracting, squaring, and so on. If data is not used for numerical operations, storing it as a character string is better because the programmer will be using it as a character string. Thus, no conversion is required. MySQL stores positive and negative numbers, but MySQL can be instructed to store only positive numbers. If data will not be negative, store the data as unsigned. There is no need to use a + or – sign before the number in doing this. (MySQL Manual). Date and time data: The third common type of data, where data is stored as a date can be displayed in a variety of date formats. It can also be used to determine the length of time between two dates or two times, or between a specific date or time and some arbitrary date or time. (MySQL Manual). Enumeration data: At times, data can have a limited number of values, like when the only possible values for a column is yes or no.
MySQL provides a data type called enumeration for use with this type of data. MySQL is simply instructed on what values can be stored in the column and MySQL will not store any other values in that particular column (MySQL Manual). MySQL DATA TYPE NAMES In creating a database, MySQL must be instructed on what kind of data to expect in a particular column by using the MySQL names for data types. The table below shows the MySQL data types used most often in Web database applications. MySQL Data Types Data Type Description CHAR(length) Fixed-length character string. VARCHAR(length) Variable-length character string. The longest string that can be stored is length, which must be between 1 and 255. TEXT Variable-length character string with a maximum length of 64KB of text. INT(length) Integer with a range from –2147483648 to +2147483647. The number that can be displayed is limited by length. INT(length) UNSIGNED Integer with a range from 0 to 4294967295. length is the size of the number that can be displayed. DECIMAL(length,dec) Decimal number where length is the number of characters that can be used to display the number, including decimal points, signs, and exponents, and dec is the maximum number of decimal places allowed. DATE Date value with year, month, and date. Displays value as YYYY-MM-DD TIME Time value with hour, minute, and second. Displays as HH:MM:SS. DATETIME Date and time are stored together. Displays as YYYY-MM-DD HH:MM:SS. ENUM (“val1”,”val2”...) Only values listed can be stored. Max. of 65535 values can be listed. Figure 4. (MySQL 5.0) MySQL permits other data types, but they are not frequently needed. For a description of all the available data types, see the MySQL online documentation. Part 4: DEVELOPMENT Developing the Application After having developed a plan listing the tasks that the application is going to perform, and having developed a database design, we are now ready to create the application. First, we build the database. Then, we write the PHP programs. Thus, we are now moments away from a working Web database application. Building the database Building the database requires turning the paper database design into a workable and running database. Building the database is independent of the PHP programs that the application uses to interact with the database. The database can be accessed using programming languages other than PHP, such as Perl, C, or Java. The database stands on its own to hold the data. The database should first be built before writing the PHP programs. The PHP programs are written to move data in and out of the database, so developing and testing them cannot be done until the database is available. Building the database requires communication with MySQL by using the SQL language. MySQL is instructed to create the database and to add tables to the database. MySQL is instructed how to organize the data tables and what format to use to store the data. (MySQL Manual). Writing the programs Programs carry out the tasks for the Web database application, and create the display that the user sees in the browser window. Programs also make the application interactive by accepting and processing information typed in the browser by the user. Programs store information in the database and get information out of the database. Thus, the database is useless unless data can be moved in and out of it. Generally, every task in this plan requires a program. Since this plan indicates that the application will display a form, a program that displays a form is therefore required. And since this paper also indicates that the application will store the data from a form, a program that gets the data from the form and puts it in the database is therefore a requirement. Finally, and worth noting is the PHP language, which was specifically developed to write interactive Web applications. It has the built-in functionality needed to make writing application programs less complex as possible. It has methods that were included in the language specifically to access data from forms. It has methods to put data into a MySQL database and methods to get data from a MySQL database. (MySQL Manual).