MISC373 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, WA99202          (509) 745-2012           $320

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

WKU Fashion             260 State St., SP, WA99204  (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 (200 points):

 

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 inputshould 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 whichcustomers (by name) in what quantities?

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

c.List product type and style, unit price, unitcost, 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 outputshould encompass custom reports required by the transaction suchas (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, andconsistency.

 

 

Note: this is simplified assignment.