Enhancing Knowledge Integration: An Information System Capstone Project

1. PROJECT SUMMARY Tiny Tots, Inc. (TTI) is a hypothetical retail marketing chain selling the latest fashions in children's clothing. To help executives evaluate and manage its 16 stores, TTI has requested your consulting team to design and build a relational database system that summarizes weekly sales for each product (style) by salesperson. Once the database has been developed and instantiated with four weeks of test data (provided by TTI accountants), management has requested your team to use the test data in addressing 15 questions (queries) concerning potential problem areas at TTI. Finally, management would like your team to generate a set of profit improvement recommendations based on your analysis of query results and "best practices". 2. PROJECT BACKGROUND AND REQUIREMENTS TTI has been experiencing rapid growth and plans to double in size and profits within the next three years. Currently TTI operates 16 stores, all of which have been in operation for eight years or more. They are located in four different regions, with each region covering one or more states; each state is in one and only one region. Each store is staffed with three salespeople and a store manager. A salesperson is typically 18-24 years old, a high school graduate, has at least three months of retail experience, and is usually paid a base salary plus a sales commission. A manager is at least 22 years old, a high school graduate, and has at least four years of retail experience. Each manager is paid a base salary ($30,000) and a sales commission of 2% of his/her store's total gross revenue. A manager's responsibilities include hiring and firing salespeople, setting and changing all (weekly) retail prices at his/her store for every product (style), reporting weekly sales to the corporate office, and ordering products from vendors to replenish store inventories. The system must be designed to satisfy several requirements as described below. (Note: the attribute formats are given in parenthesis within the text below; e.g., D4 indicates a four digit numeric field, C5 indicates a five character alphanumeric field.) 2.1 Store Information For each of its stores, TTI keeps track of the store's ID (C2), name (C15), state (C2), size in square feet (D4), region code (C2) and region name (C9), monthly rent (D5), the historical percentage of sales that are paid by credit card (D3). TTI also stores the IDs (C2), names (C20), base salaries (D5), commission percentages (D2) and titles (C3) of all the salespeople and managers working at the store. Each employee can work at only one of TTI's stores. In addition, TTI keeps track of the store type (C2) and description of the store type (C12); e.g., "mall store", "strip center store", etc. For each type of store, TTI wants to track the average store size (in square feet), and the average rent. Finally, for each region, TTI wants to track the number of stores in the region, and the total profit for the region (for the latest year, quarter and week). 2.2 Style Information For each piece of clothing that TTI sells in its stores, TTI keeps track of the style number (C6) and description (C20); the ID (C3), name (C10) and address (C15) of the vendor that supplies the style to TTI; and the per unit cost of the style which is the same for all stores. In addition, each style of clothing is assigned to a specific department; a department has a department code (C2), a department name (C12), and the number of different styles sold in the department (D2). TTI also needs each style's mark-up (D5,2), calculated by the following formula: average monthly retail price for this style at this store minus vendor's cost for this style, dividing the result by the vendor's cost. Each style is supplied by only one vendor, but a vendor may supply several different styles. Each store does not necessarily sell every style--regional differences dictate different stores sell different product mixes. …