Kudler Fine Foods (KFF) is a specialty food store chain in California. Smith Systems Consulting (SSC) was contacted by KFF to develop a Frequent Shopper Program (FSP). The FSP will be able to monitor client purchasing history and accumulate the history as redeemable loyalty points which clients can use for gift items and other products or services available through external partner companies. The Sales and Marketing page section of KFF’s intranet site describes the strategic purpose of the FSP. SSC will provide IT services and of course consulting. Some other things would be developmental solutions, strengthening, implementation, IT integration, analysis, and design (Apollo Group, 2004). Smith Consulting will also submit a proposal to Kudlers that will detail the development processes of the FSP project. This paper will list the project’s logical and physical models, this includes hardware, software, network, controls, database, and other development related tools. Logical and Physical Models
Logical and physical models are representations of the key elements and processes of a software development. The logical model describes the processes, especially data, in as much detail as possible, without giving regard to how the system will be physically implemented. Logical data models include entities and relationships among them and how data flows from one process or entity to another. Representations of the key elements and processes of a software development is logical and physical models. A logical model will describe the processes without giving regard as to how the system will be physically implemented in as much detail as possible. A physical model is just a modified version of the logical model. It is intended to work with a specific set of software and hardware components (Hoberman, 2009). Logical Model of the FSP In-Store Operation
The brand new FSP will add changes to existing functions within KFF operations. That means when a client initiates a transaction the cashier will ask if the client is a member of KFF FSP and/or if they would like to be a member if they are currently not. If the client does not want to be a member then the cashier will proceed with the checkout but if they do then the client will be asked to fill out the appropriate application and the information will be entered into the system. If the client is already a member then their member’s card will be scanned and then the purchase will continue. Depending on his purchase patterns and history, the FSP will issue discounts and points to the customer. The points and rewards information will be printed on the receipt and given to the customer. Figure 1 shows the low-level data flow diagram of the proposed new system, which includes the basic processes described above. The high-level data flow diagram includes additional details such as data flow to and from databases, and how each database is designed with specific tables.
Figure 1 Low-level Logical Data Flow Diagram
Figure 2 High-level Logical Data Flow Diagram
Logical Data Flow Diagram for FSP Redemption Process
KFF’s customers can redeem their points in various ways. They can login to Kudler’s members’ online portal. The customers can login using the membership ID printed on the back of their membership cards, or by the preferred username they used upon signing up. When they login, they are required to enter their user ID and password. The security system will determine if the customer input is valid or not by matching the credentials to the user’s database. From there onward, the users can retrieve their information, and also use the web portal to redeem their points. The points can be used to print coupons and exchange for items and products not only valid for Kudler stores, but also for Kudler’s partner companies. When customers use the points, the system will calculate the total remaining points and update the customer table. Figure 3 shows the logical data flow diagram of the processes detailed above, and Figure 4 shows the physical model of the entire FSP project.
Figure 3 Logical Data Flow Diagram for FSP Redemption Process
Figure 4 Physical Model of Frequent Shopper Program
The hardware requirements are crucial to the system development, consisting of physical servers and other peripherals. These components are the legs and limbs of the system. The hardware section of the system will accept input, and process the input in a way specified by the design, and returns an output (Blundell, 2008). Centralized Data Management System and Database Server Hardware – This will be the central server in which data gathered by the tracking software will be uploaded to and downloaded from. All locations will have access to this hardware to share valuable customer information. This server will be used for customer data verification as well. It will also be the central sever in which data such as rewards program, points, purchases will be stored. The local database servers will have an Intel i5 processor, 8GB of RAM, and 2TB of storage to handle all the extra load and processing, while the central database server will have 4TB of storage. Backup Servers – Each location will have a local backup storage to ensure that customer information is safe, and can be retrieved in case of connectivity issues such as broadband connection problems.
Backup servers will only need little processing power so an Intel Core 2 Duo will suffice. The backup servers will also need at least 4TB of storage space to handle data from all KFF locations, and a maximum of 4GB of RAM. Web Server – A front end website will enable customers to purchase products, see new products, obtain points and rewards information, and change their customer profile. The web site will be the online portal for the customers into the Kudler Fine Foods stores. The web server can become heavily loaded if the FSP is a success. For future-proofing, the web server will have an Intel I5 processor, 4GB of RAM, and 500GB of storage. Membership Card – Cards embedded with bar codes will be used to quickly obtain customer information at the stores. Bar codes will be scanned and automatically matched with the information gathered from the local and central databases. Bar Code Scanners – Kudler will use and update existing POS systems and bar code scanners and install them to those without. Routers, Switches, Hubs, and Hardware Firewalls – These peripherals are necessary to create a high throughput, and secure network.
Hardware must be compatible and capable to interact without conflict with upgraded equipment. The network wired with CAT6 cabling and DSL will provide adequate speed for processing data. The network should permit customer orders into the central database where inventories are adjusted and suppliers are notified if inventories drop below set quantities. Securing the network from unauthorized access is a definite requirement, but includes the capability to interact safely from the web and web store customers. Firewall and security software are the tools that make this possible. For example, Citrix could be used to access the network remotely. The software design, hardware selected, and network functionality must interact individually and together without conflict and with reliability. Network
KFF uses an Ethernet network as their network connection type. SSC will replace the WAN Ethernet by a more secure virtual private network. The point-to-point structure of a VPN will offer the FSP many benefits, especially in reducing cost. With a VPN, short dedicated connections replace long-distance leased lines to the closes point of presence (POP) of the internet service provider (Douligeris & Serpanos, 2007). The VPN will require each employee of KFF to login to the network before acquiring access. The usernames and login times and other activities can be logged for tracking and added security. The VPN setup is also highly scalable. Any new or additional network can be added easily. Furthermore, a permanent or temporary link can be provided to third party or other business partners for a secure connectivity. A firewall between the public domain (Internet) and the VPN gateway will offer additional security.
Figure 5 Kudler Fine Foods Proposed VPN Network Diagram
KFF already has a good structure for their database. The database will only require few additional tables, normalizations, and optimization to improve its efficiency. The current database does not have a specific location for the rewards, points, and other data necessary for the FSP project. Smith Systems Consulting will add three additional columns in the customer table. These three new columns will include Customer Points and Customer Rewards, and Card Serial ID under the Customer table. The Card Serial ID is a unique number printed underneath the barcode of ever member’s FSP membership card. The database will also need new events handler that will trigger whenever the customer purchases a product, or use his points as redemption.
The FSP project will use two databases: local and centralized. The POS systems will only connect to the local database for a more rapidly response. The local database will synchronize with the central database on a timed interval. By doing this, whenever connectivity issues or disasters arise between the local and the central database, KFF can still continue with their services. The database engine software is MySQL, one of the most popular open source database software available. MySQL is open source and free, which will decrease the total cost of entire FSP development. Microsoft Access will be the primary client for the MySQL database by using myODBC driver, a link between MySQL and Microsoft Access. User Interface
The user interface (UI) of the FSP program must be efficient and effective to improve productivity. After scanning the membership cards, the customer information needs to appear on the POS screen, complete with name, photo (when applicable), address, and telephone number. Other necessary information will include the expiration date of the membership card so that it is quick to see at a glance if the customer is still a member or not.
The POS GUI must be the same in all locations to promote singularity. With a unified interface, an employee can be transferred or work from one location to another without any need of additional training. The bar code scanner will improve productivity by reducing the amount of manual typing by the employee, and reduces risk of human error.
The online portal and e-commerce solution will need to be attractive to all ages. Each customer must have the ability to set their own profile information, retrieve their purchase and points history, redeem rewards, and purchase KFF products online. The online portal or web site will also have a preference page where users can change various settings to meet their preferences. This will also encourage even older customers with poor vision to try the website by changing the font size and colors. A neatly organized layout and an internal search engine will promote navigability.
Because MySQL is highly scalable and flexible, SSC will include a web interface written in PHP language for the Finance and Accounting Department to use in addition to the finance and analytics applications. This will enable a simple secure browser to make a secure connection. Detailed reports and other data can be accessed out as long as the user provides valid login credentials. Controls
Protecting the FSP from unauthorized access, use, disclosure, disruption, modification, or destruction is the primary concern of the security controls. The new FSP development will have four main objectives of security controls, which are information confidentiality, integrity, availability, and non-repudiation (Byrnes & Proctor, 2002). The following list shows the risks that the proposed security controls will help eliminate: Human Error – common human mistakes.
Damage by Employees – information deliberately disgruntled Computer Systems Misuse – employees using computer systems for personal use. Information Theft and Fraud – information use for personal purposes. Malwares – viruses, worms, trojans, and other malicious software. Hackers – individual or group trying to gain access.
Natural Disasters – natural calamities such as storms, floods, and power outages.
To ensure the security of the information, SSC will have the following security control strategies on different access levels. Administrative
A handbook consisting of Standard Operating Procedures (SOP) as well as policies and guidelines is one of the key elements in ensuring the proper usef of the new FSP system. This will also include policies regarding passwords, use of computer systems, computer security policies, and others related to information security. Logical
Computerized security system will be put in place before and after the installation of the main components of the FSP. Identification software such as card-based access to server computers and workstations helps prevent unauthorized use. Different levels of Kudler employees will have different levels of access to the system, depending on their company hierarchical status. To prevent unauthorized access and confusion in which files and information are accessible, SSC will implement a file control system. Network control measures such as authentication and Access Control Lists (ACL) will help ensure network and information security. Physical
To minimize physical access to information-sensitive computer systems and hardware, access to such components will be available to upper management only. Server rooms and IT department buildings are to remain closed whenever empty. Entry to such rooms will need a card-based authentication. Cryptography
The use of Internet as the main link between networks, there is a greater risk of data transmission interception. The use of cryptography will ensure that any intercepted data will require decryption. In any cryptology approach that uses a cipher, the objective is to have a system that the translation of encrypted text into plain text requires a key or password that even if the algorithms become at risk, deciphering will still need the proper key (Stamp, 2005). Access to e-mail communications and the Internet-based employee and customer portal via kiosks are accessible only through a Secure Sockets Layer (SSL) connection. Training to Fix Common Problems
Smith Systems Consulting, together with knowledgeable super users will train KFF employees of common information security practices. A security meeting will discuss simple information security mistakes such as weak passwords, unpatched, and outdated computer systems, and misuse of computer systems.
This paper lists many of the specifications designed by SSC for KFF’s Frequent Shopper Program. Security controls, database design, network diagrams, data flow, and development tools help improve the overall success of the project. With careful analysis, design, and testing, SSC will become ready for the FPS project implementation.
Apollo Group. (2004). Smith Systems Consulting. Retrieved February 13, 2012 from https://ecampus.phoenix.edu/secure/aapd/cist/vop/Business/Smith/SmithHome003.htm Byrnes, C., Proctor, P. (2002). Information Security Must Balance Business Objectives. Retrieved February 13, 2012 from http://www.informit.com/articles/article.aspx?p=26952. Douligeris, C., Serpanos, D. (2007). VPN Benefits. Network Security: Current Status and Future Directions. John Wiley and Sons Hoberman, S. (2009). Physical Data Model Explained. Data Modelling Made Simple: A Practical Guide for Business and IT Professionals. Technics Publications. Stamp, M. (2005). Information Security: Principles and Practice. Wiley-Interscience.