INTRODUCTION

Factory located in Crozet, Virginia.
Assembles Premium -Quality Bikes.
Known for its quality and craftsmanship of their products.
Aldo turned the company to his son, Frank Celli.
Currently the company is maintaining paper based systems and due to the significant increase in sales of mountain bikes the company wants to replace the paper based systems with database application.
Frank wants to upgrade to an on-line bike shop up to date with latest technology.

CURRENT

OPERATIONS

The below services/systems have to be replaced by the database applications:

Paper based system maintained the models and parts of each bikes, has led to several problems.
Each assembly part or BOM is being maintained in a spreadsheet, there by , keeping track of parts substitute and sub assembly difficult.
All the inventory operation management has been supported by paper based system which has resulted in inventory problems.

Currently a spreadsheet is used to keep a track of model number and names of the product line as well as their introduction and discontinuation dates.
Any customization request received by the customer, could not be accommodated easily, making it harder for the engineer to make changes to the models
The inventory management in the current operation doesn’t maintain a track of all the bikes, parts, substitute parts, supplier information, quantity etc thereby leading to mismanagement

PROCESS FLOW CHART

BUSINESS PROCESS

MODEL NOTATION

DESCRIPTIVE ENTITY RELATIONAL MODEL

ENTITY RELATIONSHIP DIAGRAM

Customer + Order + Employee + Membership Relationship.
Customers can place an order for complete bikes and parts.
Each order has a unique order number and all the customer details are recorded.
Based on the membership plan which the customer has, the payment is made accordingly.
All the payment details are recorded
Customers can renew a membership or can purchase a different one.
Employee keep records of every order placed by customer.

Customer-Membership Relationship

ENTITY RELATIONSHIP DIAGRAM

The customer can place an order for either bike or a part.
In a product line there are different bikes (Racers, Classics etc.)with many models(Kilimanjaro , Dolomiti etc.) and sizes(SM , MD , XL ,XXL etc.).

Bike-Bike Model-Order Relationship

BILL OF MATERIALS - PART

Maintain a bill of materials (BOM) or recipe that describes the composition of that part.
The system should record which parts are used in the subassembly, in which quantity, when the assembly was first introduced, and when it was discontinued.
Substitution of each parts if exists.
Repository of Assembly and sub assemble.
Parts and supplier have Many to Many Relationship.

ENTITY RELATIONSHIP DIAGRAM

Transaction –Check In/Out of the Parts based on the Order.
Indicates the order number in case the request is based on a bike order.
The assembly of a bike may involve several checkout transactions.

Transaction - Part Relationship

INDEXING

Created index on part_Supplier Columns : (part_id , supplier_id).
To find out what all parts were supplied ,which supplier.
Created Index on "order“ Column (membership_id)
Above indexs will help in sorting out the membership_id in the ORDER table and will help to find the customer_orders based on the membership_id in the order table.
Created Index on Membership ,Column : (customer_id)
Above Index will sort all the Customer_id based on the Membership_id’s

SAMPLE QUERIES

SELECT BikeModel.product_line, BikeModel.model,

BikeMSales.sales
FROM BikeModel
   LEFT JOIN (
       SELECT BikeModel.bikemodel_id, count(*) as sales
       FROM BikeModel
           INNER JOIN Bike
           ON BikeModel.bikemodel_id = Bike.bikemodel_id
       GROUP BY BikeModel.bikemodel_id
   ) as BikeMSales
   ON BikeModel.bikemodel_id = BikeMSales.bikemodel_id

List of BikeModels and total sales for each

SAMPLE QUERIES

SELECT Supp.company_name, P.name,

P_Supp.manufacturer_no, P_Supp.price
FROM Supplier as Supp
INNER JOIN Part_Supplier as P_Supp
ON Supp.supplier_id = P_Supp.supplier_id

   INNER JOIN Part as P
   ON P_Supp.part_id = P.part_id
WHERE   Supp.active = 1 AND
P.discontinued IS NULL
ORDER BY Supp.supplier_id

List of parts sold by suppliers that are not

discontinued and the supplier is active

GAMIFICATION

Keep track of miles driven by the customer and daily congratulate for there achievements and when they reach a particular milestone. They are awarded with some goodies(to encourage them to use more and more of Cellibini Mountain Bikes)

THANK YOU

CELLIBINI

INTRODUCTION

CURRENT

OPERATIONS