Technology / Sql Database Implementation

Sql Database Implementation

This essay Sql Database Implementation is available for you on Essays24.com! Search Term Papers, College Essay Examples and Free Essays on Essays24.com - full papers database.

Autor:  anton  19 October 2010
Tags:  Database,  Implementation
Words: 3907   |   Pages: 16
Views: 285

Kudler Fine Foods

Introduction

Kudler Fine Foods is a specialty grocery store that provides high quality foods to its customers. The Kudler mission is:

To provide our customers the finest in selected foodstuffs, wines, and related needs in an unparalleled consumer environment.

Our selections, coupled with our experienced, helpful and knowledgeable staff, merge to offer each customer a delightful and pleasing shopping outing.

We will provide this service because we ‘shop the world’ for our products; purchase only the finest of products are highly selective in acquiring our team members; and will go to extensive lengths to assure that Kudler’s is the purveyor of choice for customers aspiring to purchase the finest epicurean delights. (University of Phoenix: Virtual Organizations, Kudler Fine Foods, 2005).

Kudler Fine Foods has embarked on a new sales and marketing designed to decrease costs and increase revenues for the fiscal year 2006.

Membership Rewards

During 2006, Kudler plans to increase customer loyalty by offering new customer focused programs such as: frequent shopper/rewards programs and cooking class socials. Kudler’s wants to provide better information management; this new initiative involves tracking purchase behavior at the individual level and providing high value incentives through a partnership with a loyalty points program. The demographic information gained by tracking purchase behavior will help improve customer satisfaction and planning for future growth of the company. Kudler would like to focus on internal processes to increase behind the scene efficiency and cost cutting initiatives. Kudler’s purchasing department has developed a supplier relations program. This inventory program will reduce costs or ordering foods and minimize the amount of food to be stored, while maintaining a zero stock out environment. Using the existing technical infrastructure, Kudler will modify its existing database to help accomplish its goals for upcoming year. SQL generated reports will help to determine if the business achieves its cost reduction and revenue goals.

Currently, Kudler provides a monthly newsletter providing shoppers with current specials and store information. Kudler new marketing strategy involves the creation of a membership rewards program. The membership program will reward customers who frequently purchase store products, attend special store functions, sign up friends (new shoppers), and purchase special bonus store items. Registration for the rewards program involves filling out a membership information form. Store employees will enter the information gathered from the membership form into the database. A membership card or telephone number at the point of sale (POS) terminal will identify members during checkout or at any customer service center. It has been determined that price is not the primary differentiating factor for Kudler consumers; in fact, these shoppers are focused on quality and finding specialized items. Therefore, Kudler will not offer discounts on store items like lower end markets. For customers that purchase frequently, Kudler has collaborated with several other companies to create a loyalty points program. Customers can then redeem these points for high-end gift items.

The Kudler loyalty points and redeemable gift program breakdown is as followed:

Frequent Shopper Program – Earning Points

100 Loyalty points 1 Redeemable coupon

10 Loyalty points For purchases totaling $100

5 Loyalty points For every special event attended, i.e. cooking social

5 Loyalty points For every new friend signed up

1 Loyalty points Each special bonus item purchased

Gift Items – Spending Points

Ashford

10% off

Brooks Brothers

15% off

eBags

25% off

Apple

Up to 17% off & free shipping orders over $50

Dell Computers

12% off

Philips

Up to 40% off

FTD

20% off

Equifax

33% off

AT&T 17% off monthly bill and more

Sprint PCS

25% off monthly bill

Busch Gardens

Up to 30% off

Independence Air

10% off

Wine.com

15% off

The existing Kudler database will require changes in order to accommodate the new marketing strategy. Details on the necessary changes appear later in this paper. The new membership rewards program will also assist with the collection of demographic information, which is also important for the success of the marketing strategy. The estimated revenue growth generated by the membership program and special event program is 12.25% for the 2006 fiscal year. At the end of 2006, comparing the current year to the previous year will validate this estimate.

Valued Customer Membership

Kudler’s new strategy for increasing profitability will include introducing new programs aimed at increasing revenues by adding to its customer base and increasing customer loyalty. Since the store’s founding in 1998, Kathy Kudler’s vision of a one-stop gourmet grocery store has been the main reason for its success. In keeping with its current image as a “boutique grocery,” Kudler’s will introduce expanded services to attract new up-scale customers who do not find it necessary to shop by price. Direct mail advertising will target hi-income zip codes to attract new customers who have not yet become aware of the Kudler name and mission.

Current plans are to invite customers to become members of Kudler’s Valued Customer program. The appeal of this membership is unique in the grocery industry, in that it offers social contact and interaction between the stores and the customer base. Rather than offering special pricing for its members, Kudler will invite its member customers to exclusive after-hours parties and events in the stores.

Local and national social program organization will support some of the events now in the planning stage. Guest speakers will include a number of activists, including Kathy Kudler herself. Other events in plan are wine and cheese tasting, book signing by authors – especially authors of cookbooks, cookware demonstrations, and cooking lessons by world-famous chefs. Food and drinks will always be served, making each event an opportunity for social networking and meeting new potential friends. Featured guests such as Mario Batali, Rachael Ray, Bobby Flay, and possibly Emeril Lagasse will generate high interest. Kudler has already planned 18 Specialty Cooking Classes within the first 3 months of 2006.

Though the newsletter will have general announcements of these events, marketing is planning a direct mail program; sending current members invitations to upcoming events. Admission to the events will require presentation of a membership card or an invitation. Members can use the invitations received in the mail to invite friends whom they believe are compatible with the Kudler concept. As further incentive for members to attend, and to build the membership rolls, members who bring invited guests will be given chances to win door prizes. Word of mouth advertising is one of the best methods of adding new customers and making new friends.

The impact of the Valued Customer program and Special Events on the Decision Support System is not hard to imagine. Additional fields in the database tables tracking customer information are needed to contain information about: attendance at events, success of the program in attracting new customers and members, new data points for the analysis of Kudler’s demographics, etc. Details on the necessary changes appear later in this paper.

Demographic Studies

Kudler’s goal is to develop a plan that will enable management to make well-informed decisions regarding the growth of the company. As with all good plans Kudler Foods will require specific, accurate, and up-to-date data. The data must be readily available and should not place any additional burden on day-to-day operations. The data will be utilized in the decision making process for possible future growth of the company.

It is desirable, from a management stand point, to not to create a entirely new process for the gathering of data, needed for the decision making process. Kudler management would prefer to leverage the power and information contained of their current database system. Management also realizes that some modification to the database will need to take place before input and eventual harvesting of needed data can occur.

Specifically, Kudler management wishes to extract the zip code information from every transaction that is processed at all three of their current store locations. To prevent having to perform the data extraction from each of the stores databases separately. Data extraction will take place from the La Jolla store database after synchronization with the Del Mar and Encinitas store databases. Database synchronization will occur on a daily basis at a specific time; this will be an automated process, which occurs after normal business hours. A database ‘view’ will provide the harvested data. The view titled “Transactions_By_Store” will execute a SQL query designed to extract specific data from the La Jolla store database. The data represented in the view will have be ordered by store location, the zip code data will be counted, and then all of the data will be grouped into transactions by zip code. The SQL query will use two of the database tables, Orders and Stores. Here is a sample of the SQL query used in the “Transaction_By_Store”:

Example SQL View Query

CREATE VIEW Transactions_By_Store AS

SELECT Store_Name as Store,

Zip_Code as 'Zip Code',

COUNT(Zip_Code)as 'Transactions'

FROM Orders

INNER JOIN Store ON Orders.Store_Code = Store.Store_Code

GROUP BY Zip_Code

Sample Database View

Store Zip Code Transactions

1 LaJolla 85706 13

2 LaJolla 85707 3

3 LaJolla 85708 11

4 LaJolla 85712 30

5 LaJolla 85720 1

6 LaJolla 85721 3

7 DelMar 85746 1

8 DelMar 85747 9

9 DelMar 85748 20

Kudler already has plans for the zip code data. Specifically, they will use the data to plot the geographic distribution, by zip code, of all purchases for each store. A ‘demographic ring’ will provide a visual representation of the collected data. Each store will have a map of their surrounding area. In the center of each of these maps will be the location of the specific store, La Jolla, Del Mar, and Encinitas. Radiating out from the store location will be a series rings. Each ring represents a pre-determined distance from the Kudler Foods store location. There will be four rings for each store. The rings will represent the distance of one, three, five, and ten or more miles from the stores’ location.

Kudler Foods management will use these maps to plot out where shoppers are traveling from to shop at a particular store location. Management will also be able to use this demographic information to help determine where their advertising dollars are most effective. It is the desire of Kudler Foods management to use this demographic information in the planning of future store locations. Management will review the demographic rings on a reoccurring basis. As an example, a certain zip code area, in the ten or more miles demographic ring, shows a high rate of patronage for a specific store. During the business decision process, insights into the geographic distribution of the customer base can be valuable when considering a new store location. Additionally, analysis of the impact of opening a new store on the existing stores' customer base would greatly influence the final decision.

Using the database view in the previous example, the following example will illustrate how the data might influence a decision. In the example database view, we see that zip code 85712 accounted for slightly over fifty percent of the La Jolla stores transactions during a specific period. With this information, Kudler management would have to weigh the risk of opening a new store in the 85712 area; risking a dramatic decrease of customers shopping at the La Jolla store. It is obvious that this demographic information will certainly benefit Kudler management in making sound business decision based on real data.

Inventory Control

One of the focuses of Kudler’s new sales and marketing plan for 2006 is to increase efficiency of internal processes by finding new ways to reduce costs associated with the stores’ inventory. Kudler’s goal is to reduce costs of ordering inventory and minimizing the amount of food to be stored, but maintaining a zero stock-out policy. In order to achieve its goal, Kudler must improve its information management by modifying the current database and the inventory purchasing procedures.

Presently an order received form is filled manually after each shipment is received and verified for the correct items, quantities, and undamaged conditions. The accounting department receives the form, and then pays the supplier. By changing to an electronic process, the information will update automatically and the accounting department will be able to access this information more quickly.

In order to fully benefit from the new supplier relations program that has been establish, Kudler must update its current purchasing procedure. Firstly, Kudler will convert their paper purchase order into an electronic document, which can then be stored in the database. In the new version of Kudler's database, there will be an electronic purchase order. This will allow the form to automatically integrate with the rest of the database. Details on the necessary changes appear later in this paper.

Currently, department managers are encouraged to combine orders with the other store’s managers to obtain better prices for ordering larger quantities of a particular item. This would be easier to coordinate if the information was available electronically. A department manager would be able to make queries in the database to obtain the quantities usually carried by not only their store but also the other stores. The ability to order large quantities will enable the manager to negotiate with the supplier for a better price. By storing all purchase orders electronically, the purchasing department will be able to track a lot of new information, including which supplier offers the best price, quality, and delivery options.

By making the necessary changes to the current database, the purchasing department can use the frequent purchase program transactions to obtain necessary data on customer purchase patterns to maintain the zero stock out policy. This will help Kudler make more up to date adjustments to the inventory to reflect the needs of the customers while reducing cost by reducing the amount of food stored.

Kudler conducts weekly operations review meeting to coordinate items on sale for the three stores simultaneously in order to avoid excess inventory and merchandise write-downs. These meetings require up-to-date information in order to ensure adequate supplies of merchandise will be available in all three stores; which may include shifting merchandise between stores. With the new database, this process can happen more frequently to better maintain fluctuations caused by the zero stock out policy and/or excess inventory among the three stores.

Each department of each store is responsible for maintaining high customer service levels for the items sold by carrying higher levels of inventory, but not excessive levels. The only way to accomplish this is by having accurate up-to-date information and being able to run reports or queries to recognize sale trends. Kudler’s policy is to maintain adequate levels of inventory to assure that stock-outs do not occur more than 2-3 times a year. Managing this customer service issue is part of monthly operations review meetings and adjustments to merchandise stocking levels are made if warranted. By improving the company’s information management with SQL queries, more frequent adjustments can be made; which will assure that the customer service level goal of 95% is reached.

Database Implications

Efficiencies and Improvements. The existing Kudler database has been sufficient for their needs up to this point in time. However, now that the company wishes to aggressively pursue expansion, what were minor flaws in the design, will develop into major issues if not addressed. If implemented, the suggested design changes will offer more flexibility to expand with the companies needs.

The first issue is in the Order table. Currently, the Order table has a field Sales_Person_ID that has no relationship to any other table. From a reporting perspective, it is difficult to analyze data when all that is present is ID numbers. In order for the analyst to really understand what a report is showing, it is important to reduce the number of abstract concepts present in the report. Additionally, since this is Kudler’s point-of-sale (POS) system, it is important that it be secure. Creating a User table to store the name of the employee, his or her ID number, password, and user-level will help address these issues. The following SQL statement will accomplish this:

/* Create User_Level Table

* Field Definitions:

* UserLevelID- Primary Key

* Description- Description of User

* SalesRights- 1 if user can conduct customer transactions

* ManagerRights- 1 if user can use manager override

* StockRights- 1 if user can change inventory levels

* ReportRights- 1 if user can run reports

* AdminRights- 1 if user can change system settings

* Disabled- 1 if user account is Disabled

*/

CREATE TABLE User_Level

(UserLevelID INT PRIMARY KEY,

Description VARCHAR(32) NOT NULL,

SalesRights BIT,

ManagerRights BIT,

StockRights BIT,

ReportRights BIT,

AdminRights BIT,

Disabled BIT);

/* Create User Table

* Field Definitions:

* UserID- Primary Key

* FirstName- First Name

* LastName- Last Name

* Password- User Password

* UserLevelID- Current User Level

*/

CREATE TABLE User

(UserID VARCHAR(16) PRIMARY KEY,

FirstName VARCHAR(32) NOT NULL,

LastName VARCHAR(32) NOT NULL,

Password VARCHAR(32) NOT NULL,

UserLevelID INT FOREIGN KEY REFERENCES

User_Level(UserLevelID));

/* Alter Order Table

* Make Sales_Person_ID a VARCHAR(16).

*/

ALTER TABLE Order

ALTER COLUMN Sales_Person_ID VARCHAR(16);

/* Alter Order Table

* Make Sales_Person_ID a Foreign Key to User.

*/

ALTER TABLE Order

ADD FOREIGN KEY (Sales_Person_ID) REFERENCES User(UserID);

The next step to improving the database is to remove an unnecessary table. The data in this table would be better stored in another existing table. The table in question is: Tax_Table. At this time, Kudler is not providing online ordering of groceries. Therefore, the tax rate for a transaction is dependent

on the physical location of the store where the transaction takes place. These are the queries to make this change:

/* Alter Store Table

* Add Tax_Rate Column.

*/

ALTER TABLE Store

ADD Tax_Rate DECIMAL(2,2);

/* Update Store Table

* Fill Tax_Rate Column.

*/

UPDATE Store

SET Store.Tax_Rate = Tax_Table.Tax_Rate

FROM Store

INNER JOIN Tax_Table ON Store.Store_Code = Tax_Table.Store_Code

/* Remove Tax_Table Table

*/

DROP TABLE Tax_Table

Finally, the last issue is a missing field from the Inventory table. Currently, Kudler’s stores each manage their own inventory locally. However, there appears to be no way to relate the Inventory in the database to any particular store. In order to do this, we must add the Store_Code to the Inventory table. The following queries takes care of this:

/* Alter Inventory Table

* Add Store_Code Column.

*/

ALTER TABLE Inventory

ADD Store_Code CHAR(5);

/* Alter Inventory Table

* Make Store_Code a Foreign Key to Store.

*/

ALTER TABLE Inventory

ADD FOREIGN KEY (Store_Code) REFERENCES Store(Store_Code);

Membership Rewards. The next stage of development is to enhance the database to support the new features for the Marketing departments Membership Rewards plan. In the original system, customers who wanted to opt-in to the store’s monthly newsletter provided the information in the Customer table. Now that the company wants a Membership Rewards plan, the nature of this information has to change in structure.

For demographic purposes, we still want to get the Zip Code of every customer, whether or not they are members. Therefore, the existing Order table will need to have a Zip Code field added. Employees will manually enter the Zip_Code field for non-members or automatically when swiping the member’s card. The following SQL queries will accomplish the required changes:

/* Alter Order Table

* Add Zip_Code Column.

*/

ALTER TABLE Order

ADD Zip_Code VARCHAR(10);

/* Update Order Table

* Fill Zip_Code Column.

*/

UPDATE Order

INNER JOIN Customer ON Order.Customer_ID = Customer.Customer_ID

SET Order.Zip_Code = Customer.Zip_Code

This change will maintain the demographics for all orders, without numerous, mostly-blank, customer records. Deleting these most-blank records is now possible. Since Marketing wants to mail a membership card to those customers who have already signed up for the newsletter, it would make sense to delete any records that don’t include a first name, last name, or complete address:

/* Delete Customer Data

* Remove mostly-blank records.

*/

DELETE FROM Orders

WHERE Last_Name IS NULL OR

First_Name IS NULL OR

Address IS NULL OR

City IS NULL OR

State IS NULL OR

Zip_Code IS NULL;

In order to earn points for their Member Rewards card, customers will need to participate in certain activities. These activities could be anything from shopping and earning points for every dollar, to attending special events held at the store, to signing up friends to the Member Rewards program. Once the customer has earned enough points, they will be able to spend them to earn special discounts or gift certificates. In order to keep track of all this information, there will need to be two tables. The first table will be a list of all Actions for earning and spending points. The second table will be a transaction table that will keep track of earned and spent points. The following queries set up these two tables:

/* Create Action Table

* Field Definitions:

* Action_ID- Primary Key

* Action_Name- Name of the Action

* Description- Rules and Regulations for Action

* Points- Postive or Negative Point Value of the Action

*/

CREATE TABLE Action

(Action_ID INT PRIMARY KEY,

Action_Name VARCHAR(32) NOT NULL,

Description VARCHAR(8192),

Points INT);

/* Create Member_Activity Table

* Field Definitions:

* Customer_ID- Foreign Key to Customer

* Action_ID- Foreign Key to Action

* Point_Value- Point Value added or subtracted

*/

CREATE TABLE Member_Activity

(Customer_ID VARCHAR(16) FOREIGN KEY REFERENCES

Customer(Customer_ID),

Action_ID INT FOREIGN KEY REFERENCES

Action(Action_ID),

Point_Value INT);

The last change required for Marketing is a table to track bonus points. From time to time, the managers will get together and decide on a list of products that will earn customers in the Member Rewards program bonus points if purchased. When the customer gets their points for a particular purchase, a second process will check if any of the items purchased currently have a bonus point value associated with it. The new table will be set up like this:

/* Create Bonus_Point Table

* Field Definitions:

* BonusPoint_ID- Primary Key

* Item_ID- Foreign Key to Item

* Bonus- Bonus Points

* Start_Date- Effective Date of Bonus

* End_Date- Expiration Date of Bonus

*/

CREATE TABLE Bonus_Points

(BonusPoint_ID INT PRIMARY KEY,

ItemID VARCHAR(10) FOREIGN KEY REFERENCES

Item(Item_ID),

Bonus INT,

Start_Date SMALLDATETIME NOT NULL,

End_Date SMALLDATETIME);

With a start and end date, Marketing can determine the effectiveness of particular promotions and then attempt to vary point values until they determine what sells the most product.

Inventory Control. Kudler is also interested in further enhancing their inventory control system. Currently, managers order new inventory with a paper form. Kudler would like to expand the database so that all purchase orders get stored electronically. This will allow the company to better track when it is ordering inventory, when inventory is received, and how much money is being spent with a particular supplier. The following two tables accomplish this:

/* Create Purchase_Order Table

* Field Definitions:

* PurchaseOrder_ID- Primary Key

* Store_Code- Foreign Key to Store

* UserID- Foreign Key to User

* PO_Date- Purchase Order Date

* Supplier_ID- Foreign Key to Supplier

* Shipped_Via Shipping Company

* FOB_Point FOB Point

* Terms PO Terms

* Sales_Tax Sales Tax

* Shipping_Cost Shipping Cost

* Other_Cost Other Cost

* Total_Cost Total Purchase Order Cost

*/

CREATE TABLE Purchase_Order

(PurchaseOrder_ID INT PRIMARY KEY,

Store_Code VARCHAR(5) FOREIGN KEY REFERENCES

Store(Store_Code),

UserID VARCHAR(16) FOREIGN KEY REFERENCES

User(UserID),

PO_Date SMALLDATETIME NOT NULL,

Supplier_ID VARCHAR(10) FOREIGN KEY REFERENCES

Supplier(Supplier_ID),

Shipped_Via VARCHAR(32),

FOB_Point VARCHAR(32),

Terms VARCHAR(32),

Sales_Tax MONEY,

Shipping_Cost MONEY,

Other_Cost MONEY,

Total_Cost MONEY);

/* Create PO_Lines Table

* Field Definitions:

* POLines_ID- Primary Key

* Purchase_Order_ID Foregin Key to Purchase Order

* Item_ID- Foreign Key to Item

* Quantity- Point Value added or subtracted

*/

CREATE TABLE PO_Lines

(POLines_ID INT PRIMARY KEY

PurchaseOrder_ID INT FOREIGN KEY REFERENCES

Purchase_Order(PurchaseOrder_ID),

Item_ID INT FOREIGN KEY REFERENCES

Item(Item_ID),

Quantity INT);

Conclusion

. By following these recommendations, Kudler Fine Foods will be able to meet all of their marketing objectives in 2006. Managers will be able to get better information from their streamlined database. The database will be able to track where inventory is at any moment. It will also be able to track trends in customer behavior, most importantly the behavior of the members in the reward program. The database will also streamline the process of evaluating the current inventory and ordering new inventory. Most importantly, Kudler will be able to provide better customer service to all of its existing customers as well as keep those new customers who decide to try Kudler’s stores.

References

University of Phoenix. (2005). Virtual Organizations, Kudler Fine Foods. Retrieved from

University of Phoenix ‘rEsource’, August 24, 2005.



Get Better Grades Today

Join Essays24.com and get instant access to over 60,000+ Papers and Essays

closeLogin
Please enter your username and password
Username:
Password:
Forgot your password?