BCIS 4610 Interim report

1. BCIS 4610 Interim report By : Team Logical Partition LLC. Hugh Hunton, Cody Morris, James Konderla, Mark Wallace BCIS 4610 Project - Interim Report

2. Appendix 21 19 Executive Summary Discussion of the Current Situation Goals of The Project Project Scope Approach to Collecting User Requirements Functional Description of the New System Data Requirements Non-Functional Requirements Definition of Work for Phase 2 Phase 2 Timeline 22 4 5 6 7 9 10 16 18 Interim Report Content BCIS 4610 Project - Interim Report

3. Overview BCIS 4610 Project - Proposal

4. Executive Summary • Aidan Gray Home Inc. is a furniture manufacturer and distributer which targets the European themed furniture market. It’s business model has achieved immense success by combining a physical sales presence with a strong e-commerce division. Their most recent success was the opening of the first Aidan Gray Home Inc. retail store, Gray Living. • While Aidan Gray has achieved considerable growth since its founding, there are certain aspects of its business model that limit its potential for growth and efficiency. One such challenge is the fact that Aidan Gray has three separate databases, each containing vital information, that must be individually updated when changes need to be made. This creates serious inefficiency, slowing down Aidan Gray’s ability to operate profitably. In light of these challenges, Logical Partition has identified a solution: an application which will interface will all three databases. • Logical Partition will develop this application using a prototype focused approach, allowing its employees to quickly and effectively develop a seamless data-entry experience. The application will access the three databases via a user friendly application interface, allowing the user to make updates to all three database at once. By implementing this system, Aidan Gray will be able to reduce their data entry efforts by up to 66%. These savings will go directly to Aidan Gray’s bottom line, as they will be able to reduce their payroll costs and divert employee effort. While this issue could be approached in a different way, the proposed application will have the greatest impact for the capital invested by the client. As a whole, this application will be immensely beneficial for Aidan Gray Home Inc. • Key Deliverables and Benefits • System Requirement Analysis • Transparent Prototyping Process • User friendly interface • Successful database editing • Drastically increased efficiency • Reduction in data entry costs Executive Summary BCIS 4610 Project - Interim Report

5. Client description • Aidan Gray Home Inc. is a high end furniture manufacturer and distributer, • based out of Carrollton, Texas. It’s CEO, Randall Weeks, contributes a unique vision that has • contributed to Aidan Gray Home’s high level of success. They are a market leader in the • design and production of antique European themed furniture and home décor, allowing them • to capitalize on the high demand for European furniture in niche markets. They employ a full • time staff of ten at their local office, and are represented by hundreds of sales reps • throughout the country. In addition to their physical presence, Aidan Gray has established a • successful e-commerce division, aimed at increasing revenue by capturing additional sales via • the internet. As a whole, Aidan Gray’s business model is built on wholesale distribution, • leveraging physical sales reps and online sales. In 2011, the company also opened Gray Living, • its first retail store in McKinney, Texas. • Description of the as-is (old) system • Aiden Gray’s current system was built in a segmented fashion, and successfully, albeit inefficiently, resolves their needs. The system revolves around three separate functionalities that are accessed individually. One functionality is a database used solely for internal use, storing everything from product information, to freight rates, and shipping data. In addition to this database, a separate database exists to store and generate website product information. Finally, a mobile application exists which allows representatives to view pertinent information from a web browser. While this system is successful in achieving desired results, it does so in an inefficient matter. This is primarily due to the ways in which each portion of the system in accessed Discussion of the Current situation • Realistically, maintaining an accurate product catalog across three different systems, which do not communicate with each other, is quite difficult. This inefficiency results directly in increased costs due to employee work efforts. Keeping everything up to date would require hiring a full-time employee dedicated entirely to data entry. Manually entering information into three separate systems also increases the likelihood of typographical errors • which can be very costly to the company. Overall, the as-is system is successful, but creates drastic inefficiencies, increased costs, and potential typographical errors BCIS 4610 Project - Interim Report

6. The goal of the project • Logical Partition has identified the inadequacy in Aidan Gray's existing business process. The team's goal is to address this issue by reengineering existing business processes to eliminate redundancies and increase efficiency. • Rather than attempt to deploy another large costly information system, Logical Partition has decided to design a middle-ware utility. The goal of this utility is to eliminate the requirement of updating three databases. If the team is successful in this goal, Aidan Gray will only need to enter product updates into the single middle-ware utility. • Benefits for the client/other beneficiaries • Figure 1: Goals of the project • Estimations suggest that the implementation of this application will create a 66% reduction in time required to update all three databases. The benefits to the business owners will be a significant increase in productivity. End-users will have more time to focus on other duties, creating greater satisfaction and efficiency. Customers, clients, and inventory workers will enjoy greater accuracy in product information due to the elimination of potential data entry errors. Additionally, the cost and time required to design and implement this application is only 10% of the development costs of alternative solutions. All users will enjoy this solution much sooner than the alternatives, and at a fraction of the cost. There is also no increase to maintenance or hardware requirements, which means efficiency is increased while cost of operation remains the same. Goals of the project BCIS 4610 Project - Interim Report

7. Scope of the project • Logical Partition’s objective in this project is to create a middle-ware application between the three existing databases Aidan Gray currently uses. These three databases will be referred to as “in-house”, “excel”, and “web”. Any database added to Aidan Gray’s infrastructure in addition to the three mentioned above is beyond the scope of this project. Additionally, the application will be deployed on existing hardware within Aidan Gray’s office. The application is not guaranteed to work if there is significant modification to the hardware, or the network configuration on which it operates. Reconfiguring the application is only possible through rewriting source code, which is not available to the end user. Creating a user-configuration interface is also beyond the scope of this project. • The implementation of Logical Partition’s middle-ware solution is intended to reduce redundancy in data entry. Specifically, the goal is to provide a single interface in which a user can create new entries. After the user populates the required fields, it will be pushed live across all three databases. It is important to note – the application will only allow users to create new entries, or alter entries previously created using the application. The constraints of the project do not allow for synchronization of any data located in any of the three databases prior to implementation. More specifically this means any old records will need to be edited the old way. • The application will be written to work entirely through a web browser. No new software or hardware will be required. Users who currently have access to edit the three databases will use the same login information to gain access to the application. There will be a back-end feature in which an administrator will be able to create, edit or delete user login credentials. This administrator interface will also be entirely web browser based. • Key deliverables • System Specification • Hardware & Software Specifications • Interface Design • Physical Process Model • Database & File Specification • Physical Data Model • Programs& Documentation • Migration Plan: • Support Plan • Project Proposal • Interim project Report • Final Project Report • Limitations • No Synchronization of existing data, only synchronization of new data will occur in this system. • Client will NOT be able to edit the form and/or interface themselves, further editing of the pre-designed system will be done through consulting. • No structural changes to the database will be done after the Design Phase. • No changes will be allowed to database hardware to prevent unforeseen program or database errors. • Administrative access should be limited to keep from accidental deletion or breakage in the system. User accounts and passwords will be issued as needed. • No Remote Connection to the Database will be allowed due to security reasons. • After Implementation, the prior system will be seen as a separate entity and changes to the prior system will not affect the current system. Project scope BCIS 4610 Project - Interim Report

8. System requirements BCIS 4610 Project - Interim Report

9. The overall approach you followed in collecting your user requirements • Due to the limited number of end-users, observation and interviews have proved to be the most effective method for requirements gathering. Initial observation was completed at a scheduled time where the current process of editing/modifying/adding information was being used. This observation gave analysts a better idea of the current constraints within the system, and the obstacles they presented to the user. • Observation was followed at a later date by one-on-one interviews with end-users and key stakeholders. The goal of these structured interviews was to determine the reasoning behind the current system design, what it did well and what it had to do better. During the interviews a series of open-ended, close-ended and probing questions were asked in a top-down questioning strategy. These types of questions were chosen to get both a broad idea of the current system’s limitations, as well as specific details as they pertained to different job functions. • Post interview analysis was conducted to determine the results of the interview. A summary of the analysis was sent to Aidan Gray for their approval. The purpose of the approval is simply to confirm that Logical Partition has a good understanding of Aidan Gray’s needs and that there was no misinterpretation during the post interview analysis. • Key stakeholders of the system • Although there are many customers and sales reps who will benefit from a new system, only the individuals who are currently a part of the product management process are considered key stakeholders. They have been identified as: • Business owners • The business owners have a key stake in the new system due to financing its development costs. The increase in efficiency and reduction of typographical errors are expected to justify the development expense. • Product Coordinators • Product coordinators will benefit from faster and more reliable product information updates. • Product Designers • Product designers are an integral part of the product management process. Their time invested to maintaining accurate product information will be significantly decreased. • Sources of information • Methods for user requirements gathering were modeled by the description in Dennis, Wixom & Roth’s , System Analysis & Design, Fourth Edition. The information was found in chapter 3, pages 113-130. • Sources of information within Aidan Gray: • Business owners • Product Coordinators • Product Designers • Internal documents such as, e-mails, BPMs Approach to collecting user requirements BCIS 4610 Project - Interim Report

10. Goal of the system, high level description • The purpose of the system is to streamline the data entry process by combining several DBMS interfaces into a single interface. This single interface, or form, will allow users to populate the fields with appropriate data and submit it to the three different databases (in-house, web, excel). The new system will differentiate the information, and only push the information to the appropriate destination. For example, the user enters a description, dimensions and price for a table. The description may only be sent to the web, the dimensions may go to excel, and the price may go to the in-house database. The new system will also allow users to query based on SKU (stock-keeping-unit), and have all information from the three databases consolidated into a single interface. From this interface, users will be able to edit or delete information, and send the changes back to the applicable databases. • Description of system functionality • User Login • Users will be validated against a users database. Users with access to databases prior to implementation of the new system will be able to use their current login credentials. • Product Query • Users will be able to query product from the various databases by using product SKU. Information from all three databases will be displayed in a single interface. • Edit Product • Users will be able to edit existing data and save the changes. • Resolve Discrepancies • Alerts users when there is a variance between databases on identical product characteristic. For example query for SKU 12345 returns table ‘A’ with height ‘X’ from one database, and returns table ‘A’ with height ‘Y’ from another. The alert serves as a reality check, and allows the users to select which value is the true value. • New Product • Users can create new products. • Provide Data to Appropriate Database • Discriminates the information based on its field and sends it to the appropriate database. • Relationship to other systems • The new system will work with existing hardware, software and databases. There will be no modification to any of the existing infrastructure. • The other systems that will be a part of the new system’s process are: • In-house database and its current structure • MySQL • Excel database • .xls files • Web database • MySQL • Each of the above systems will be accessed through the new system and will give the user read/write/edit privileges. Functional description of the new system BCIS 4610 Project - Interim Report

11. Functional Description of the new system BCIS 4610 Project - Interim Report

12. Functional Description of the new system BCIS 4610 Project - Interim Report

13. Functional Description of the new system BCIS 4610 Project - Interim Report

14. Functional Description of the new system BCIS 4610 Project - Interim Report

15. Functional Description of the new system BCIS 4610 Project - Interim Report

16. Brief description of the key types of data that is being created , stored and manipulated by the system • In general, our data consists of product information and Quantity-On-Hand counts. This information, in its current state, is spread across 3 databases, which we will describe below: • In-House Database: This database is local and is based in Microsoft Access 2003. This database holds all information regarding the products. This database, while Access based, is accessed through a shared folder on the network and is queried using basic SQL queries. This database is known as A on our ERD (see appendix – UnJoined ERD). • Web-Database: This database is used for the AidenGrayHome.com website. This database is stored off-site, on the web-provider’s server, and most of the data in this database is a duplication of the in-house database. While it’s data is majorly a duplication of the local database, the Web Database has no connectivity to the in-house system and must be updated manually. This database, according to the publisher, is MySQL based and is supplied to the web in HTML and Javascript format. This database is referenced as B in our ERD (see appendix – UnJoined ERD). • Excel Database: This database is used exclusively by the sales representatives and is housed in a Microsoft Excel 2003-based file that is uploaded to a third party source (www.repzio.com) and supplies data to the iPad-based sales application. This database also has no connectivity to the in-house system and, for all intents and purposes, is considered its own entity. This database, according to Repzio, is supplied to the user in a Javascript and HTML format that draws on the Excel file after it’s conversion to a MySQL database (this is outside the scope of our project but is important enough to Aiden Gray Home that it should be noted) This database is referenced as C in our ERD (see appendix – UnJoined ERD). • As part of our solution to Aiden Gray Home’s problem of database integration we have decided to focus the In-House database as the main system. As such, according to our JOINED ERD, we have decided to join the databases on their similar fields and, as part of the same display, query the different, or system-specific, fields of each database to provide the user form. Data Requirements BCIS 4610 Project - Interim Report

17. Entities • The main entities involved in this system, according to our ERD (see appendix – Joined ERD), are outlined below (for descriptions, see appendix – Joined ERD): • Item Info Table • ItemInfo(SKU (ABC), Item_Name (ABC), Department_ID (AB), Item_Weight (AB), Dimensions (ABC), Materials (AB), Color (AB), Category (C), Image_URL (C), Discontinued (C), Min_Order_Q (C)) • Department Table • Department(Department_ID (AB), Department_Name (AB), Department_Description) • Pricing Table • Pricing(SKU (ABC), Type (A), Retail_Cost (BC), Wholesale Cost (BC), Min_Req_Pur (BC), Cost (A)) • QOH Table • QOH(SKU (ABC), Loc_ID (AB), Re_Order_Point (AB), Num_On_Hand (AB)) • Relationships • For the Database, according to the ERD (see appendix – Joined ERD), consist of three major Relationships: • Item’s Department – This relationship is between the ItemInfo and Department tables and is used as a Foreign Key in the ItemInfo table, to show which department an Item Belongs to. • Item’s SKU – This relationship is between the Pricing and ItemInfo Tables and is used, as a Foreign Key and portion of a concatenated Primary Key in the Pricing table, to generate pricing information, such as wholesale and retail prices, for each separate Item based on the Item’s SKU. • Item’s SKU 2 – This relationship is between the ItemInfo and QOH tables and is used, as a Foreign Key and portion of a concatenated Primary Key in the QOH table, to generate Quantity On Hand information for each item Based on it’s SKU. Data Requirements (Continued) BCIS 4610 Project - Interim Report

18. Key Considerations/Architecture • While interviewing with the management of Aiden Gray Home Inc. it was determined that the best option, based on environmental factors and user needs, would be a Server-Based system. During the interview and data-gathering sessions we were also able to determine that Security, while important, is not the most pressing issue. Instead, the company must always have access to their data for continued and uninterrupted operations. Because of this we have decided that the server will be accessible locally without remote control but will also be connected to the internet to simplify updating of the web database. We also determined that a basic user interface will be sufficient when accessing data. In the following sections we will outline the key portions of the Non-Functional system requirements. • User Interface Requirements • According to our interview sessions, the User Interface for our system should concern itself with Usability rather than aesthetics. As such, we have designed a basic form layout on an HTML-based user interface to meet the basic requirements of this project. We have also decided, based on the relative ease of implementation, to base the interface off of the corporate colors of Orange, white, and Slate. • Mobile Integration, while originally an issue, is made simpler due to an integration package provided by Repzio that allows our interface to automatically validate our user requirements and update the web database, stored on Repzio servers, from the centralized user interface. Since speed and availability is an issue, we have decided to base our User Interface program on the server and to program it using basic HTML and Javascript tags without “bells and whistles”. • Performance • During our interview sessions we determined that speed is one of the most important issues in regards to the database system. The transaction volume, after reviewing sales data from the last year, seemed relatively standard so we have decided to implement a basic server-based system. We have also determined that the current database, while relatively small, must be left room to expand as Aiden Gray Home Inc. grows and, as stated in the key considerations, the system must always be available. To solve these issues we have determined that the server should be based on-site rather than remotely. • Security • According to our various interviews, Aiden Gray’s current database systems have built-in user validation with same-password validation per user across the 3 systems. As such, the login system will be simplified in our User Interface by validating across all 3 servers at once without the need to re-enter login data for each. We have also determined that, due to the necessity of uptime for the server, we will implement an Antivirus system on the system to prevent issues from outside sources. To prevent internal Security Issues, we have also determined that the Server will be stored in a locked server room. • Other Requirements • Based on the fact that Aiden Gray Home Inc. is based in Texas and deals internationally, we have decided to implement basic functionality for French and Spanish. According to the complexity of each language, though, we have decided that the interface itself will include this functionality, while the database will still be English-Based instead of being forced to translate each specific product to a different language. We have also determined that, based on the interviewers’ response of ease-of-use, that we will not allow customizability of the system or user interface after implementation. Non-functional requirements BCIS 4610 Project - Interim Report

19. Phase description • There are multiple aspects of this system which need to be completed prior to delivering a final report. The initial focus in regards to accomplishing these requirements is creating an HTML form prototype of graphical user interface. This HTML form will demonstrate the capabilities of the system using demonstrative data, and mockup outputs. Another important aspect of the HTML form will be designing it to output to the correct/specified location. While the efficacy of this form is the number one priority, it is also important to our team to make it visually pleasing. • Another aspect of the system which must be finalized before submitting a final report, is the population of the mock databases with mock information. This data will be used for demonstrative queries, allowing the demonstration to accurately depict the entire execution process. This is an extremely important aspect of the final report delivery, as it will allow a seamless demo of the entire system. • While the HTML form and demo databases are extremely important aspects of the system, the most important aspect for this phase is developing the physical process model. The completion of this model will solidify a semantically safe system. In order for the report to be a resounding success, it will be important that all these aspects are present and functioning correctly. • Key activities The key activities, as determined by our proposal, project requirements, and various interviews with Aiden Gray Home Inc. Management, include: • CodedHTML forms thatsupport appropriate data • SQL queries designedto add/modify/delete information from appropriate databases • Explanation and diagraming of data entry, sorting, storing, and modification of product data through the HTML form and into SQL format. • Development ofoutput pages • Key deliverables • Some of our main key deliverables for this phase include: • HTML forms with mock data • Forms will display functionality of each process within the system • Finalized Queries • Will demonstrate the query structure and the information it returns • Definition of fields on HTML form and which database(s) it is stored in • Examples of additional functionality • such as “Resolve Discrepancy” , how it works and how the user interacts with it • Demonstration of functionality Definition of work for phase 2 BCIS 4610 Project - Interim Report

20. Interface Design - Forms • Below are the major forms that we will be designing for our final report, along with a brief description of each: • Inventory View– This form will allow a general printout of the non-discontinued inventory in the system in a basic table format. This is a basic, HTML-generated, webpage. • Inventory Edit– This report will allow the user to query for a product, based on SKU. This will generate a form with boxes that contain the information of the product built from all databases. From there this inventory will be updateable by changing the information in the forms and updating it. • Inventory Entry– This report is a basic form for entering new inventory. The same boxes as used for the Inventory Edit report will appear and the user will be able to populate them to program in a new inventory item, which will then be submitted across databases. • Product Information – This form will allow the user to, by using either the department or the SKU, query the database for a product or group of products. The information will then be displayed in a table-like, uneditable, format that displays all product information from the databases. • Login Form – A basic Login form where the user can enter their username and password and click Submit. The user information will be verified across databases and, if correct, will allow the user to proceed into the system. • Interface Design – Reports • We have outlined, below, the major reports that will be needed for our system. • Inventory View Report – This report will allow users to view a general, table-layout, review of each item based on it’s SKU, name, short description, and prices. This form is mostly meant for guest users or sales representatives (non-warehouse workers). • Inventory Entry Report - This report will allow users to view, edit or update new information as well as view the item’s specific information. There will also be an option to add new inventory to the system. • Product Information – This report is a department-based report that will find items from a specific department and output, in a table format, all of their information. It is a non-editable combination of the Inventory Entry and Inventory view reports. • Key queries • For our system we have identified several different types of queries that will need to be addressed. We will list them Below with a simple definition of each: • Login – When the user supplies their information, a login request is sent to each separate database (passwords and usernames are the same across databases). • Product Query – The user will need to be able to view the product information from each database, as well as run Create Read Update Delete operations. • Inventory – The user will need to be able to view an active quantity on hand, as well as inventory and inventory locations within the warehouse itself. Definition of work for phase 2 BCIS 4610 Project - Interim Report

21. Schedule update and phase 2 timeline • After reviewing our timeline we have determined that we are ahead of schedule and have decided to abide by the current timeline, as seen below. We have completed the Analysis phase and have included, in the appendix, a copy of our data models, the Process Models, and the Data-Flow Diagrams that have been developed for this project. We have also included sample SQL code for the database design, as well as a Semantic Object Model and, upon delivering this report, are ready to proceed to the design phase of the project. Below we have outlined the Deliverables for the duration of the project: • Design Phase: • March 13th – Architectural and User Interface Design Completed • March 27th – Physical Process Model Completed • April 4th – Program and Database & File System Design Completed • Implementation Phase: • April 17th – Test, Migration, and Support Plans Completed • **April 18th – Installation of Prototype • April 20th – Problem Report and Change Request Design Completed • April 23rd - In-Class Presentation • Project plan Phase 2 timeline BCIS 4610 Project - Interim Report

22. Appendix BCIS 4610 Project - Interim Report

23. A8-9 A3-7 DFD – Context Level DFD – Level 0 (User Interface Process Expanded) DFD – Level 1 Diagrams Business Process Maps Unjoined Databases Entity-Relationship Diagram Joined Databases Entity-Relationship Diagram Create-Read-Update-Delete (CRUD) Matrix Entity Relationship Report – UnJoined Database (Generated with Visible Analyst) Entity Relationship Report – Joined Database (Generated with Visible Analyst) Data Flow Report (Generated with Visible Analyst) A1 A2 A53-58 Semantic Object Model Reports – Joined Database (Generated with TableDesigner) A10 A11 A12-13 A14-22 A23-25 A26-37 Semantic Object Model – UnJoined Database (Generated with TableDesigner) A38 Semantic Object Model Reports– Joined Database (Generated with TableDesigner) A39-51 Semantic Object Model – UnJoined Database (Generated with TableDesigner) A52 Appendix - Contents BCIS 4610 Project - Interim Report

24. BCIS 4610 Project - Interim Report