2013

Motor bike store

Heera Prasad

2

3

1. Background

D.K. motors is a privately owned organization which is a dealership showroom for motor bikes of Honda. It is associated with other shops in the same district. D.K. motors have three departments Sales, Service, Administrative section where Motor bike parts are stored and maintained. The shop is located in the heart of the town. Most of the customers are from the nearby area and they are familiar with this shop through advertisement through posters, internet advertisement and via referral from other customers. The potential customer come to the shop and make a discussion with the sales person to check with the requirement they are looking into. They usually have a list of features and limited knowledge about the models of the bike. Mostly they work with single sales person until the deal is completed on a commission basis. All the data related to the shop was maintained with the help of a software and some of them was a paper work. The opinions of the customer forced the organization to develop new database that can provide more information more efficiently than the previous system. To simplify the data associated with the shop and to retrieve the enormous amount of data associated with the organization the new database was an immediate requirement.

2. Purpose

The purpose behind the design of database is to simplify the work associated with the sales and service of a bike and also to retrieve the data quickly. The previous data entry was a paper work and through a software which is identified to have flaws in Customer details, Parts description and Employee details and time consuming data management was another issue. The new system has each Bike has a bike Id which is associated with the Customer id of the customer table and sales Id of the Sales table. Sales of the bikes are in traditional ways. A salesperson can help a customer to select the bike according to the requirements of the customer. The bike is associated with a service id of service table through a job card table that has a job card id. The service table is again related to the mechanic table that has a primary key mechanic id. The service table is associated to the parts table which has a parts id. The service is done with the help of mechanic and the job card specifies the received date for servicing and the fulfillment date. As the required relationships are made this data model enables the user to access data quickly.

3. Business Goals

To track the maintenance. To complete the job easily according to the fulfillment date. To easily identify the parts ordered and the amount of parts needed to be stocked in the shop and the number of bikes sold out. To track the details about all the customer that is customer who bought bike and customers who have given bike for services. To track the details about the inventory. To save the time since there are no paper work involved. Customer satisfaction is a major goal through efficient data management.

4

4. System Overview

The administrative department has a networked computer in the office with access to the distribution center’s file server. The database would be stored and ran from the server so that the data will be backed up automatically.

There needs to be a login screen for 7 authorized users. The product must have a user-friendly interface with simple and straightforward menu options. The users must have a basic knowledge of computers to use this database. The 3 users always manages the data while other users has in charge of monitoring the service section with the administration.

5. User Requirements

Users of this system will consist of the employees that will perform the scheduled maintenance and their immediate supervisor. The requirements for these users are outlined below.

Login/Authorization – A login screen will allow only authorized users with employee id /sales id to schedule and make changes to maintenance history to the Bikes, Customers, Parts, Service, Employee details.

The service details and the mechanic assigned to any particular service must be specified in the field.

The Salesperson associated with the sold bike must be specified.

The parts used for the servicing should be specified.

The mechanic has no direct relationship with the customer (given bike for service).

The sales person deals with the customer to whom bike is sold.

Most of the sales person also works with administrative section.

The system must show the location of the parts used for servicing in the rack and also about the number of parts stocked inside the shop while searching with the parts id.

The employee details (work done by them, salary details) should be retrieved from the employee id.

5

6. Data Requirements and Table relationships

The data previously maintained by the organization both paper work and computerized data will need to be stored and maintained effectively by the new database. The required data (included in the data dictionary) will include the details of the bike ( Bike Id, bike registration number, bike model, bike color, bike year ), Customer, Servicing details of the bike, Parts purchased by the customer, Sales details, Employee details (Mechanic and Sales person), Job card details. All the details regarding the business subjects are defined in the data dictionary. The database used in the design is SQL Database. The entity relationship is as follows:

SALESPERSON TO SALES – 1:M SALES TO BIKE –M:M SALES TO CUSTOMER – M:1 BIKE TO CUSTOMER –M:1 BIKE TO JOBCARD – 1:M CUSTOMER TO JOBCARD -1:M JOBCARD TO SERVICE -1:M SERVICE TO PARTS –M:M MECHANIC TO SERVICE -1:M

6

7. Data Dictionary

Table Name

Column Name

Column Type

Column Size

Column Status

Comments Attribute Definition

BIKE Bike_Id Number 15 Not null(PK)

This field is used to store registration number for

bikeBikeReg_No Variable

Character15 Not null A unique

field which store

Registration number of bike when purchased

BikeModel Variable Character

30 Field used to define which

model the bike belongs

toBikeColour Variable

Character30 Field

displays the color of the

bikeBikeYear YEAR 4 Not null Displays the

year in which bike was

manufactured

Table Name

Column Name

Column Type

Column Size

Column Status

Comments Attribute Definition

PARTS Parts_Id Number 15 Not null(PK)

Field used to store Parts

numberParts_nm Variable

Character50 Not null Store the

name of the parts

Parts_Price Number 10 Not null Store the Price of the parts

Parts_Rack Variable Character

20 Not null Stores the location of the

parts in the shop

Table name

Column Name

Column Type

Column Size

Column status

Comments Attribute definition

SERVICE Service_ID (PK)

Number 15 Not Null Unique key

which stores

service idMechanic_ID Number 15 Not

null(Fk1)Foreign

key which stores

mechanic id

Service_Hrs Varchar 10 Not null Format hh.mm-hh.mm

Field which stores time

taken to service the bike

Jobcard_ID Number 15 Not null(Fk2)

Foreign field

which stores the jobcard id

Parts_ID Number 15 Not null(Fk3)

Foreign field

which stores the

part id

7

Table name Column name Column type

Column size

Column status

Comments Attribute definition

MECHANIC Mechanic_ID Number 15 Not null Unique field which stores the id of the

employee Mechanic

Mechanic_Lname Variable character

50 Not null Field which

stores the last name

of the mechanic

Mechanic_Fname Variable character

50 Not null Field which

stores the first name

of the mechanic

Table Name Column Name Column Type

Column Size

Column Status

Comments

Attribute Definition

CUSTOMER

Customer_Id Number 15 Not null(PK)

This field store the

Customer_Id for the bike purchased

CustLast_nm Variable character

50 Not null Field stores Customer

Name.CustFrst_nm Variable

Character50 Stores

Customer contact Number

Cust_no Variable Character

20 Stores Customer number

Cust_cntct Variable Character

20 Stores Customer contact Number

Cust_Street Variable Character

50 Stores a part of addres of the customer

Cust_City Variable Character

20 Stores The name of City

in which customer

livesCustomer_state Variable

Character20 Stores the

State in which

customer lives

Customer_Zipcode Variable Character

20 Stores the zipcode of

the customerBike_Id (FK1) Number 15 Not

null(FK1)

Stores bike number

associated with each

customer as it is a

foreign key

8

8. Reporting Requirements

Users should be able to print reports on:

User should be able to print reports related to Customer billing, selling of a bike.

The admin should be able to locate the parts on the parts rack and familiar to identify the parts associated with a bike using parts number.

User should be able to print bill related to servicing and parts used for servicing.

9. Project Schedule and Costs

TABLE NAME

COLUMN NAME

COLUMN TYPE

COLUMN SIZE

COLUMN STATUS

COMMENTS ATTRIBUTE DEFINITION

JOBCARD JobCard_ID Number 15 NOT NULL (PK)

Unique field which has job card id

Bike_ID Number 15 Not Null A foreign field which stores Bike id

Cust_ID Number 15 Not Null A foreign field which stores customer id

ServiceDate_rec Variable character

20 Not NULL Format mm/dd/yyyy

Stores date the bike was received for service

ServFul_Date Variable character

30 Not Null Format mm/dd/yyyy

Stores the date in which the bike should be returned after service

Complaints Variable Character

200 Field stores the compliants for the bike

9

DK Motors has established business relation with COMPUTEK SOLUTIONS to develop the database system. The estimated cost to develop and test the database and maintain is $2500.

Project Dates Project Tasks Project Costs02/21/2013 Data gathering done by COMPUTEK SOLUTIONS according to the

requirement given by the user and the data model is checked for consistency.$500

02/25/2013 – 05/26/2013 Create database from data model. Use test data to verify results as required by D K Motors.

$1000

05/29/2013 – 07/10/2013 Office employees will enter old data from paper records once front end application is tested.

$300

07/15/2013 – 08/04/2013 Additional testing is done and all aspects of the database functionality using old data already entered by office employees.

$700

09/10/2013 – ongoing Database system goes live for all future scheduling $0PROJECT TOTAL = $2500

10

10. Sample SQL Statements

CREATE TABLE statements for all tables:

CREATE TABLE BIKE

(

BIKE_ID INT NOT NULL,

BIKEREG_NO VARCHAR (15),

BIKEMODEL VARCHAR (30),

BIKECOLOUR VARCHAR (30),

BIKEYEAR YEAR (4),

CONSTRAINT BIKE_PK_PRIMARY KEY (BIKE_ID));

CREATE TABLE SALESPERSON (

SALESPRSN_ID INT NOT NULL,

SALESPRSN_FNAME VARCHAR (15),

SALESPRSN_LNAME VARCHAR (15),

CONSTRAINT SALESPERSON_PK PRIMARY KEY (SALESPRSN_ID));

CREATE TABLE CUSTOMER

(CUSTOMER_ID INT NOT NULL,

CUSTLAST_NM VARCHAR (50),

CUSTFRST_NM VARCHAR (50),

CUST_NO VARCHAR (20),

11

CUST_CNTCT VARCHAR (20),

CUST_STREET VARCHAR (50),

CUST_CITY VARCHAR (20),

CUSTOMER_STATE VARCHAR (20),

CUST_ZIPCODE VARCHAR (20),

BIKE_ID INT (15)

CONSTRAINT SCHEDULE_PK PRIMARY KEY (SCHEDULE_ID)

CONSTRAINT CUSTOMER_FK FOREIGN KEY (BIKE_ID) REFERENCES BIKE (BIKE_ID));

ON UPDATE CASCADE ON DELETE RESTRICT

CREATE TABLE SALES

(SALES_ID INT NOT NULL,

SALES_NO VARCHAR (10),

SALES_DATE DATE,

BIKE_ID INT (15),

CUTOMER_ID INT (15),

SALESPRSN_ID INT (15),

CONSTRAINT SALES_PK PRIMARY KEY (SALES_ID)

CONSTRAINT SALES_FK FORIEGN KEY (BIKE_ID) REFERENCES BIKE (BIKE_ID)

CONSTRAINT SALES_FK FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID)

CONSTARINT SALES_FK FOREIGN KEY (SALESPRSN_ID) REFERENCES SALESPERSON (SALESPRSN_ID));

CREATE TABLE JOBCARD

12

(JOBCARD_ID INT NOT NULL ,

BIKE_ID INT NOT NULL,

CUSTOMER_ID INT NOT NULL,

SERVICEDATE_REC VARCHAR (20),

SERVFUL_DATE VARCHAR (30),

COMPLAINTS VARCHAR (200)

CONSTRAINT JOBCARD_PK PRIMARY KEY (JOBCARD_ID));

CONSTRAINT JOBCARD_FK FORIEGN KEY (BIKE_ID) REFERENCES BIKE (BIKE_ID)

CONSTRAINT JOBCARD_FK FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID));

ON UPDATE CASCADE ON DELETE RESTRICT

CREATE TABLE MECHANIC

(MECHANIC_ID INT NOT NULL,

MECHANIC_LNME VARCHAR (50),

MECHANIC_FNME VARCHAR (50),

CONSTRAINT MECHANIC _PK_ PRIMARY KEY (MECHANIC_ID));

CREATE TABLE SERVICE

(SERVICE_ID INT NOT NULL,

MECHANIC_ID INT NOT NULL,

JOBCARD_ID VARCHAR (10) NOT NULL,

PARTS_ID INT NOT NULL,

13

SERVICE_HRS VARCHAR (10),

CONSTRAINT SERVICE_PK_PRIMARY KEY (SERVICE_ID)

CONSTRAINT SERVICE_FK1_FORIEGN KEY (MECHANIC_ID) REFERENCES MECHANIC (MECHANIC_ID)

CONSTRAINT SERVICE_FK2_FORIEGN KEY (JOBCARD_ID) REFERENCES JOBCARD (JOBCARD_ID)

CONSTRAINT SERVICE_FK3_FORIEGN KEY (PARTS_ID) REFERENCES PARTS (PARTS_ID));

ON UPDATE CASCADE ON DELETE RESTRICT

CREATE TABLE PARTS

(PARTS_ID INT NOT NULL,

PARTS_NM VARCHAR (50),

PARTS_PRICEINT (10),

PARTS_RACK VARCHAR (20),

CONSTRAINT PARTS_PK PRIMARY KEY (PARTS_ID));

14

11. DATA MODEL

15

16