Database Access Assignment

Rose Woolen Goods

1. Description

Rose Woolen Goods buys and resells wool clothing and blankets to retailers. Now the company keeps track of the business information by paper. To reduce the workload and enhance the competitive in market, the owner decided to investigate the possibility of automating some of the record keeping activities of the company. The intention is to increase the efficiency of the staff, and thereby to increase the staff's time in offering a quality service to customers.

The database system considers only the revenue cycle for the company. Example transactions are provided for January 2017 to illustrate sales activities: updating inventory files, invoicing customers, and producing summary reports.

1). Customers.  Rose's existing customers and their account balances at the beginning of September are:

Customer Name      Customer Address          Telephone Number      Account Balance

Moore Clothing Co.   111 College St., SP, WA 99202         (509) 745-2012           $320

Specialty Book Store 20 Campbell Lane, SP, WA 99223    (509) 783-3843             $2800

WKU Fashion             260 State St., SP, WA 99204             (509) 745-4321             $1550

Please add your name, address, telephone number and an account balance of $2,500 to the end of the existing customer list.

2). Products. Rose's existing products and quantity-on-hands at the beginning of September are:

Type           Style       Unit Price           Unit Cost        Quantity on Hand

Blanket           Blue                $165                $112                48

Blanket           Green             $165                $112                21

Blanket           Red                  $165                $112                33

Sweater           Men's             $150                $90                  39

Sweater           Women's         $160                $95                  42

Top Coat         Men's             $240                $165                37

Top Coat         Women's         $225                $160                42

3). Sales Orders. Sales orders are entered by personnel in the sales department. Sales orders for the month of September are:

Order Date    Customer                   Product          Quantity Ordered

01/02/17          Moore Clothing Co.   Blue blanket               10

01/03/17          WKU Fashion             Women's sweater       15

Women's Top Coat     23

01/05/17          Specialty Book Store Blue blanket               14

Green blanket            8

Red blanket                 12

01/13/17          Moore Clothing Co.   Green Blanket             24

Red blanket                 20

01/15/17          WKU Fashion             Men's top coat            8

01/20/17          Specialty Book Store Women's top coat       20

01/23/17          Moore Clothing Co.   Men's sweater                         30

01/25/17          Your Name                 Men's top coat            12

01/30/17          Specialty Book Store Red blanket                 12

Women's sweater       20

4). Receiving Goods. As goods are ordered (to simplify, we assume the goods are also shipped), the quantity of goods available for sales decreases. The supply of goods must be increased periodically. In the case of Rose Woolen Goods, the increase occurs when goods are received at the warehouse from the company's manufacturing division. When goods are received, the receiving department places the goods in the appropriate location to be retrieved by shipping personnel. In addition, they update inventory records by completing the appropriate form on the computer. Goods received during January are:

Date Received            Product Received      Quantity Received

01/04/17                      Blue blanket               20

Green blanket             20

Red blanket                 30

01/10/17 Men's sweater                         15

Women's sweater       10

01/15/17 Men's top coat            20

Women's top coat       10

5). Reporting. Inventory reports which list the beginning quantity, quantity sold, ending quantity, unit cost, and valuation is prepared at the end of each month.

Other inquiries can be answered by using queries.

2. Database Application Requirement:

1) Design a database (table structures) for the application

2) Create tables and set relationships

3) Generate forms and input data;

Design input for the proposed system in good form. The input should encompass data entry forms such as (a) customer form, (b) product form, (c) sales order form, (d) receiving product form.

4) Generate queries:  

a. Which products (by type and style) were sold to which customers (by name) in what quantities?

b. Calculate sales revenue, cost of goods sold, and gross margin (profits) for each product.

c. List product type and style, unit price, unit cost, and the current quantity on hand (Note: to simplify, you don't need to update quantities when selling and receiving goods).

5) Generate reports

Design output for the proposed system in good form. The output should encompass custom reports required by the transaction such as (a) product sale detail report, (b) product sale summary report, (c) customer purchase detail report (d) Invoice by order no. (e) Invoice by customer. These reports should process characteristics like usefulness, convenience in format, ease of identification, and consistency.

Note: this is simplified assignment.