CELLIBINI
MOUNTAIN BIKES
JOSE CASTILLO
NITIN JAIN
SAI AMRUTH LINGA
VAISHNAVI PREETHI ELLURU
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
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
DBMS Project
By Jose
