Direction: Read the following case and prepare what are required.

________________________________________________________________________________________________________

Barely Bear Incorporated (BBB) sells swimwear and related products to specialty stores throughout the United States. it also sells its products to individuals over a company website page. Her business has grown rapidly and now BBB has a large following of customers who want to be able to mix and match to find their ideal swimsuit. Currently BBB products are carried in more than 1000 specialties swimwear boutiques and online retailers. She recognized the need for swimsuits sold separately

During an initial interview with Ms. Bearly, she outlined BBB business processes. She acknowledged that she does not know much about accounting information technology. As the business grew her accounting suffered at information systems were added piecemeal. She is looking forward to an assessment of her requirements and recommendations that would position BBB for substantial further growth.

Mss. Bearly described BBB sales to independent boutique retailers. BBB uses a group of independent sales representatives to sell its product's to boutique retailers around the country. These sales representatives are not BBB employees. They are paid commissions based on the dollar volume of sales. After working with the sales representatives, individual retailers, call BBB to place their order for the upcoming season. BBB will then ship the products when they become available. The retailers are expected to pay for shipments within 30 days. BBB offers a prompt payment discount for payment within 10 days.

After a thorough discussion with Ms. Bearly, you presented the UML Class Diagram and the table listing BBBs sales to retailers.

Table Listing

Resources

Finished Inventory [Finished Inventory Catalog # (PK), Description, Size, Type_code (FK), Color_Code (FK), Retail Price, Wholesale Price, Standard Cost]

Cash [Account # (PK), Bank, Account Description, Balance 4/1/13]

Events

Retailer Orders/Sales [Order # (PK), Order Date, Requested Date, Delivery Date, Customer # (FK), Order Employee # (FK), Shipping Employee # (FK), Shipping Cost

Retailer Cash Receipts [Cash Receipt # (PK), Receipt Date, Receipt Amount, Customer # (FK), Order# (FK), Employee # (FK), Account # (FK)]

Agents

Employees [Employee # (PK), Last Name, First Name, Address, City, Zip, Phone, Hire Date, Department, Supervisory Employee # (FK), Salary/Wage, Pay Amount]

Retailer Customers [Customer # (PK), Customer Name, Customer Address, Customer City/State/Zip, Customer Phone, Sales Rep # (FK)]

Sales Reps [Sales Rep #, Sales Rep Name, Sales Rep Email Address, Sales Rep Phone, Sales Rep Area]

Type Images

Fabric Colors [Fabric Color Code (PK), Fabric Color Name, Fabric Cost per Yard]

Inventory Types [Type Code (PK), Use, Type Description, Yards of Fabric Required]

Linking Tables:

Retailer Order/Sales Items [Order # + Catalog # (PK), Wholesale Price, Quantity Ordered]

Requirement:

1. Using the EXCEL spreadsheet with BBB data, (provided to you on separate file), create a new Access database, and import each worksheet in the spreadsheet into the database. Set the appropriate primary keys.
2. After importing all the data, create relationships among tables to implement your data model.
3. Prepare the queries.
   1. Sales details
   2. Sales
   3. Customer Receipts by June 30
   4. Cash Receipts by June 30
   5. Customer A/R by June 30
   6. Catalog Items Sales

4. Answer the following questions:

1. What was BBBs total revenue?
2. What are BBBs accounts receivable balance for each retailer customer as of the end of June?
3. Which BBB product generated the largest sales volumes (List all the products in descending orders of Sales dollars).

Fabric Colors Inventory Types Ship_Emp 1..1 1..1 1..1 0..* 0..* Ord_Emp 1..1 0..* 0..* 0..* Retailer Orders/Sales Emp Finished Inventory 0..* Supv 1..1 1..* 0..1 1..1 0..* 0..* 1..1 0.. * 0..* Cash Retailer C/R 0..* 1..1 Retailer Customer 1..1 0..* 1..1 Sales Rep Fabric Colors Inventory Types Ship_Emp 1..1 1..1 1..1 0..* 0..* Ord_Emp 1..1 0..* 0..* 0..* Retailer Orders/Sales Emp Finished Inventory 0..* Supv 1..1 1..* 0..1 1..1 0..* 0..* 1..1 0.. * 0..* Cash Retailer C/R 0..* 1..1 Retailer Customer 1..1 0..* 1..1 Sales Rep