Decomposing BJ’s Wholesale club CJ Marselis

1. MIS 2502 Decomposing BJ’s Wholesale club CJ Marselis

2. Business Rules BJ’s is interested in developing a new application. The database will track product, customer, and sale information. It will also collect basic store information. The business rules are typical of most retail stores with the exception that sales are tracked to the customer since the customer is a member. The specific rules include: • A product may be supplied by multiple suppliers • A manufacture produces many products but sells to clients through the supplier • A sale to a store includes many products • A sale to a customer may include many products • The sale to the customer includes the store number to facilitate returns • A store has only one manager • An employee can have only 1 position

3. A product may be supplied by multiple suppliers • M:N relationship between Product and Suppliers

4. A manufacture produces many products but sells to clients through the supplier • Let’s consider the relationship between manufacturer and products first • While a product COULD be produced by many manufacturers, let’s assume that this is not the case

5. A manufacture sells to clients through the supplier • In this case the client is the Store • Basically, we need a sale to show the purchase of the product from the manufacturer to the supplier BUT this is out of scope of this db. Since we’re building the DB for BJs and BJs wouldn’t want to store the purchases of the products by the supplier from the manufacturer, we won’t worry about this

6. A sale to a store from a supplier • Since BJs would want to know what products it ordered for each store from the suppliers, we need to house this information in our db • Essentially, this is just a sale! • Remember all sales include the following components: • Customer • Seller • Products • General Sale information • Details about the sale

7. Sale • Remember all sales include the following components: • Seller • Customer • Products • Since the seller sells to multiple customers and a customer buys from multiple sellers, it’s a M:N • Each time a customer buys stuff from a seller, it’s a sale – That’s the bridge! • In this case: • Seller = Supplier • Customer = Store • Sale = delivery invoice

8. Delivery Invoice • The Delivery Invoice shows the M:N between Store and Supplier • Need FKs to Store and Supplier • Deliver and order date • Could add employee that placed the order at the buyer. This will be FK to employee table

9. A sale to a store includes many products • The buyer can purchase many products in the sale. • Since an invoice includes many products and a product can be sold in many invoices, it’s a M:N between an invoice and product • Need to show the FKs to the product and invoice tables

10. The sale to the customer includes the store number to facilitate returns • Sale includes: • Seller = Store • Customer = Customer • Sale = Sale

11. Let’s say we want to track which cashier rang up the sale. An employee can have only 1 position • Add cashier as FK to Employee Lookup

12. A sale to a customer may include many products • Just like the invoice detail table, need a bridge between sale and products

13. A store has only one manager

14. Put it all together…