Customer Database Management Systemin Retail BusinessSunah Kim Zhe Zhao Tae Woo KimDec. 6, 2008AbstractAmong many computing knowledge we learned in class, we found databasedevelopment especially useful for managing data in our daily life, small business,and even for research. Particularlly, we are interested in managing customer in-formation in retail business, how the data help understand customer better, andhow a company gets benefit from it. Thus, our team decided to build a databasesystem for a imaginary retailer, Bow-Wow, using Open Office Database Develop-ment. We focused on efficiently structuring relationship between tables based onthird normal form, making user friendly forms for data coding, generating queriesand reports for building up marketing strategy.1 Introduction<Bow-Wow>is a company that sells items we need to raise a dog. They are specialized indog product such as dog food, dog accessories, and apparels for dogs. They started asa small business and have displayed a rapid growth based on their strength in customerdatabase management. Now, they are trying to focus more on customer relationshipmanagement to be a more friendly company for dog lovers. To be specific, We havebasic data about customers and we would like to take advantage of this to make ourbusiness a profitable one. In other words, we not only want to maintain customer databut also want to derive meaningful information for marketing our shop and our product.We prepared tables, queries, forms, and reports for this purpose. What follows next isthe explanations for these four.12 Tables and Relationship2.1 TablesBased on third normal form, we made six different talbes - Customer, Dog, Species,Sales, Sales:Item(link table), Item - as follows. Customer Table• Customer TableNames of customers are randomly made. Some of their names are taken afterfamous statisticians. Their addresses are centered at Iowa city since we assumeour shop Bow-Wow is located at downtown area. For the street names and otheraddress information, our team referred apartment rental website, bakersapart-ment.com.• Dog TableThis table includes owner ID, name, age, and species of dogs. Species are writ-ten in abbreviated terms. Since one customer could own more than one dogs,customer and dog table has one to many relationship.• Species TableFor the sake of convenience, we use abbreviation of species names. Speciestable also shows how big the species is. The size of species is necessary formany reasons. We need to know the ratio large, middle, and small sized dogs tomake an order for the dog apparels.• Item TableTo figure out what kind of goods we are selling, this item table is what oneneeds to look at. This table shows the name, price, manufacturer(brand), and thecategory of the items. For the category, we have apparel, foods, toys, and sportsgoods. All the information in this table is useful since it helps us categorizecustomers into several groups which in turn, allows us differentiated marketingstrategy for different groups. We will cover issues on marketing in detail whenin query section.• Sales TableSales table shows the date when each sale has occurred and the customer id,the id of customer who made this purchase. The primary key of this table isreceipt number which is generated every time a customer receive a receipt. Thecustomer id is a foreign key. The relationship between customer and this table isone to many.• Sales:Item TableThis is a linking table. In this table, we combined receipt number with item IDand item quantity. If we link this table with Sales:Item table, we can figure outwho has purchased which item, when, and how much. This table actually has allthe information about each purchased based on its relationship with other tables.22.2 Relationship between tablesBasically, we used one-to-many and many-to-many relationship in our database. Morespecifically, Most of they are one-to-many relationship except for one, which is Salestable and Item table. To define a relationship between Sales and Items which definitelyis many-to-many relationship we used link table named Sales:Items. Overall relation-ship between tables are as follows :Figure 1: Relationship of tables of Bow-Wow33 formsForms are mainly used in the shop, at the front desk. Using form, which has a clearerview of the records to type in data, will save a lot of effort and avoid the mistakes thatmay occur when typing new records directly into tables. Generally, after setting all thetables and their relationship, forms are what we mainly use to maintain data.For our database, we generate three forms. The one showed in the picture belowis the form named custom which is used to maintain all the data about customers andtheir dogs. On the upper part, it shows the information about customers such as theirnames and addresses, while the lower part displays dogs’ information correspondingto this customer. If one customer has more than one dog, then the lower part willhave multiple records. Similar idea is applied to other two forms which are used totype in information about sales. Using the first form, ’customer’, we can easily updatecustomer information and add new customers. If we type the customer ID in the smallblank that is at the bottom, left of the customer form, it bring us to that customer’sbasic information and we can easily change the content here without having to open uptables every time. In case a customer comes into the shop for the refund of an item, wecan open up the customer-Sales form and check the receipt number and purchase data.What is even better with the forms is, we figured out how to type in new recordwith drag box. For example, for the information of the speciesID at customer form,instead of manually typing in the Species ID each time, we could select correspondingID name from the drag box which saves a lot of work.Figure 2: Example Form for coding customer and dog data44 QueriesSince out tables satisfy third normal form, we need to make queries to see the datain a customized way. Not only that but also we can combine many tables and quiriestogether and abstract useful data that we want to see using built in function such assort, condition, function. In our database, we have created more than 10 queries andthey could be categorized into three steps - simple queries which used combining only,basic calculation queries which used some fuctions and advanced queries which usedcondition, sort, functions.4.1 Simple QueriesSimple
View Full Document
Unlocking...