Case Analysis 15-15: BSN Bicycles I (Creating a Database from Scratch with Microsoft Access)

**Background:**
Bill Barnes and Tom Freeman opened their BSN bicycle shop in 2010. Apart from Jake, a friend who occasionally helps out, Bill and Tom are the only employees. The shop is located in a small commercial space that was once a restaurant. The former kitchen now stores spare parts and provides space for bicycle repairs, while the former dining area serves as the retail sales area. The "corporate office" consists of just a desk and a file cabinet in the back corner of the retail area.

Bill and Tom are more friends and bicycling enthusiasts than businessmen. They've invested their life savings into the shop and are eager for it to succeed. In the first year of operations, they worked hard to transform the space into its current condition, which includes an old-timey sign above the door with their name "BSN Bicycles."

Marketing efforts have been minimal, limited to chatting with friends, distributing flyers at bicycle races, and placing a few ads on the Internet. Similarly, accounting tasks have been neglected due to time constraints. However, the owners recognize two key issues: (1) some customers prefer to buy on credit, and (2) all suppliers expect timely payments.

Currently, BSN's "customer credit system" consists of a box of 3″ × 5″ cards, with customer information on the front and invoice details on the back. The "supplier accounts system" is similar, using green cards for vendors and gray cards for customers.

**Jake's Proposal:**
Jake, a student at the local university, is taking an AIS course that covers Microsoft Access. He suggests converting the shop's current "accounting systems" to a DBMS. He believes BSN needs a customer table, a vendor (supplier) table, and an inventory table, with possibly more tables required.

**Requirements:**

1. Identify the resources, events, and agents for BSN’s accounting systems. Draw one or more E-R diagrams that illustrate the relationships between these items.

2. Identify the tables necessary to create a working database for the company’s receivables, payables, and inventory.

3. Using Access or another DBMS required by your instructor, create at least three records for each of the tables identified in part 2. Hints:
- Use the information on the front of the 3″ × 5″ card in Figure 15-19 for the customer record structure.
- The data fields for the Vendors table should include the vendor ID, vendor name and address information, phone number, fax number, and contact person.
- The data fields for the Inventory table should include item number, item description, units (e.g., dozen, each, etc.), unit cost, unit retail sales price, and quantity on hand.

4. Create relationships for your various tables.

5. Document your work by printing the relationships window.

---

Case Analysis 15-16: BSN Bicycles II (Creating Queries in Access)

**Background:**
Business has been growing at BSN Bicycles, and the store owners have been using their Access database to store customer information. They are now considering how best to attract more customers. One idea is to analyze the locations of their current customers. The owners also want a complete list of their credit customers.

**Requirements:**

1. If not already done, create a database for BSN and the customer table described in Case 15-17. Create at least 10 customer records, including one with your name. Several customers should live in Texas (TX) and Massachusetts (MA), and several should have the zip code "12345." Customers in TX and MA, and those with zip code 12345, do not have to overlap.

2. If not already done, create several invoices for your customers.

3. Create a query that selects all customers living in TX or MA.

4. Create a query that selects all customers living in zip code 12345.

5. Create a query that selects all customers living in TX who also have zip code 12345.

6. Create a query that selects all credit customers. (Hint: Use the word "Yes" for the criteria in this query.)