Tire Service:Home

From Openitware
Jump to: navigation, search

History

  • Last term: Spring 2011
  • Previous term: None

Contents

Project Description

This TireSerivce project is a collaboration of 4 UNHM students aimed to simplify business. Currently the business utilizes a customer relation system that tracks sales, service records, and tire life for their respective customers. The system is a hand written paper filed system that consists of only previous invoices. The combined efforts of the project team will take the labor intensive process to a whole new level and integrate it into a database, making their business processing efficient and creating an effective business tool. The team will use expert knowledge of database design, data modeling and an intimate understanding of the Structured Query Language of MySQL to develop an interactive tool that the business can use on a daily basis.

Purpose

Create a database to manage records of tire sales for the purpose of maintaining customer warranty service plans for the life of the tire(s).

A tire service business. One of the project's developers is the "proxy client".

Abstract

Motivation

To improve the system of finding invoices from customers. Invoices are needed to verify if a customer is still under warranty for their service.

Objectives

This project will allow tire installers to quickly look up previous invoices to verify what warranty services can be performed. This system will cut down on both the time and error margin involved in using a paper filing system to find invoices.

Methods

In order to accomplish the mission of the project a database was created based off a paper invoice. This database keeps all the information about the individual invoice as well as the customer and their vehicle(s). Keeping all of this information in one spot will allow for invoices to be searched for and obtained based on the member, the vehicle, or the invoice number.

Results

What has the project accomplished?

The project has created a place for the information from a formerly paper only invoice to be entered where it can be searched and manipulated much easier than before.

Conclusion

The tire service shop should have an easier time knowing what warranty services each customer customer deserves. The ability to quickly search for invoices using a computer versus searching for a paper invoice will greatly increase productivity.

Activities

Team Members

Roles and Responsibilities

Note from Mihaela. The project wiki is missing the following: project abstract with its components; database analysis: conceptual model diagram with a brief description and definitions of the conceptual model entities; parent-of and child-of dependency diagrams and their brief descriptions. I'm assigning these missing elements as follows: Joey writes the abstract. Sean writes the database analysis and the missing diagrams with their descriptions. Everybody writes SQL queries. This means that everybody will assume the SQL Programmer role while finishing P2. Write your queries in a .sql file whose name is your last name. Include the file in the sql folder of your local copy of the repository. Add the file to version control (Add command in TortoiseSVN), and commit the file (do not forget the log message!).

Joey
  • Primary roles: SQL programmer, system analyst
  • Secondary roles: Team leader, wiki editor
  • Responsibilities: SQL script to create the database tables and constraints, project description, SQL queries, preparation of team meetings agendas and minutes.
Ryan
  • Primary roles: DB designer, SQL programmer
  • Secondary roles: Wiki editor
  • Responsibilities: Relational model diagram and entity/attribute definitions, SQL queries; project wiki organization and editing.
Sean
  • Primary role: System analyst, DB designer, SQL programmer
  • Secondary roles:
  • Responsibilities:
Michael
  • Primary roles: System analyst, DB designer, SQL programmer, SQL code reviewer
  • Secondary roles: Proxy client (developer who stand in for the real client - with sufficient knowledge of the application domain to answer clarification questions from developers).
  • Responsibilities: Requirements specification and user scenarios, normalization in the database design, SQL code review.

Team Meetings

April 14/16, 2011

Agenda

  • Clarify Individual Assignments
  • Reinforce Communications

Minutes

In Class, 11:35am -- 12:04pm Discussed:

  • Individual Responsibilities
  • Sharing/Reviewing each others work
  • Contact Information Exchange
  • Scheduled a Skype meeting for Sat, 5:30pm -- 7pm
  • Decided somehow that Joey is the team leader :)

Minutes

Skype meeting, 04-16-11 --5:35pm to 6:12pm

We talked about our changes on the wiki site, the diagram and made a “live” change to it (removed a FK that was not needed). We discussed the agenda for our next class, Ryans’ changes to the wiki site, and Sean mentioned he will be working the child to parent tree diagram (order of deletion tree?), and we all agreed to the agenda for our next class meeting.

April 22, 2011

Agenda

To discuss the following:

  • Needed changes or additions to the diagram
  • Parent, Child diagrams
  • Overlooked information for wiki site
  • Tread chart and whether attributes are needed for the record
  • Warranty attribute (active, void-no tread, not covered?)
  • Necessary queries for end users (standard/scripts/reports)
  • Normalization progress
  • Requirements (brainstorm with Michael)
  • Web-page or Interface for presentation (if time table allows)
  • Develop new time table with responsibilities listed

Minutes

April 28, 2011

Agenda

  • Needed changes to create tables
  • Parent-child dependency diagrams
  • Wiki site and code site
  • Queries for end-users
  • Requirements (conclusions)
  • Does not appear to be time for web site
  • Develop new time table with responsibilities listed

Minutes

No meeting took place. All class time was used to review for T2 and practice SQL programming.

Work Products

System Requirements

This phase corresponds to specifying the functional and non-functional features that the prototype system will have. The work products of this phase are a list of functional requirements (what the system will do for the user) and a list of non-functional requirements (what constraints apply to the system with regard to the system license, infrastructure, and operation). The developers responsible for these work products assumes the role of System Analyst.

The prototype system will provide the following features (or functional requirements) to the end-user:

  • Manage customer information
  • Manage customer vehicle information
  • Manage services that are done on customer vehicles

User Scenarios

Looking up original invoices

A customer will come and want a balance and rotation but doesn't have their original invoice. The only thing we need in order to give them a balance and rotation is the original invoice number of the tire that they purchased. To find this information, we would need to search by member number, and get the customer's original invoice, whether they purchased tires or not, and car make model and year and mileage.

Another situation in which we need to look up an original invoice is of a customer who wants to know when they had a balance and rotation last time. In this situation we could search by member number, then display the invoice numbers of all the customers invoices, date, only display invoice with the balance or rotation skus and make model year and mileage. If we need to do anything with a mileage warranty then we are going to need everything for the original invoice, all of the balance and rotation invoices, vehicle make and model year, mileages for all of the times the vehicle was brought in.

Database Analysis

This phase includes the conceptual modeling tasks. The work product of this phase is a description of the database conceptual model. The developers responsible for these tasks assume the role of DB Analyst

Database Design

This phase corresponds to the logical modeling of the database. The work products of this phase are: detailed description of the database entities, attributes, relationships, and constraints; diagrams for the database model and dependency graphs that show the order in which tables are created and deleted; justification of the normalization forms that apply to the database design. The developers responsible for these tasks assume the role of DB Designer.

Relational Model Diagram

The relational model of the database is represented in this entity-relationship diagram that uses the UML class diagram notation. The entities and their attributes with primary and foreign key attributes are shown in the diagram's nodes. The relationship maximum cardinalities are shown on the diagram edges.

Tireservicev4.png

*This diagram shows the entities and attributes used in the TireService database

Entity and Attribute Definitions

MemberVehicle

A MemberVehicle is an individual car or truck brought in for service. The MemberVehicle entity include information about the vehicle.

Attributes: VehicleID(PK), Make, Model, Year, Damage, Odometer, Plate, Mileage

Member

A Member is a person who who brings their vehicle in for service. The Member entity includes information about that person.

Attributes: MemberNumber(PK), MemberLastName, MemberFirstName, Street, City, State, Zip, Phone

Installer

An Installer is the employee perfoming the service. The Installer entity contains information about that employee.

Attributes: EmployeeID(PK), InvoiceNumber(FK), EmployeeLastName, EmployeeFirstName

Product

A Product is a type of service perfomed. The Product entity contains information about that service.

Attributes: SKU(PK), ProductType

Tire

Tire is a subtype of Product, Tire is one type of service that can be performed. The Tire entity contains information about the specific tires used in the service.

Attributes: SKU(PK, FK), Brand, Type, Size, BasePrice

Service

Service is a subtype of Product, Service covers the other types of work that can be perfomed be on a vehicle. The Service entity contains informtaion about te particular service being performed.

Attributes: SKU(PK,FK), Service, BasePrice

InvoiceLineItem

An InvoiceLineItem is a particular Product performed on an Invoice. It contains information about the quantity and base price.

Attributes: InvoiceLineItemID(PK), InvoiveNumber(FK), SKU(FK), Quantity, Price

Invoice

An Invoice is a listing of what service was performed on what vehicle, when that service was performed, and who performed that service. The Invoice entity contains information about the time and total price of the service as well as linking with the Member, MemberVehicle, Installer, and InvoiceLineItem tables.

Attributes: InvoiceNumber(PK), MemberNummber(FK), VehicleID(FK), Waiver, TimeIn, TimeOut, Date, TotalPrice, CurrentMileage

Normalization

Original

InvoiceNumber, MemberLastName, MemberFirstName, MemberNumber, Make, Model, Year, Mileage, TimeIN, TimeOut, TireQuantity, Brand, TireModel, Damage, Odometer, Service, EmployeeName, EmployeeLastName,

First Normal Form

  • Invoice: InvoiceNumber(PK), MemberLastName, MemberFirstName, MemberNumber, Make, Model, Year, Mileage, TimeIN, TimeOut, Damage, Odometer
  • Installer: InstallerID(PK), EmployeeLastName, EmployeeName
  • InvoiceLineItem: InvoiceLineItemID(PK), InvoiceNumber(FK), SKU,Price, TireModel ,Service, Brand, SKU, Price, Quantity

EmployeeLastName/FirstName were taken from the original form. InstallerID was make a primary key. TireModel, Quantity, and Service were taken from the original form and InvoiceLineItem was formed with InvoiceLineItemID as the PK. SKU and Price were added.

Second Normal Form

  • InvoiceLineItem: InvoiceLineItemID(PK), InvoiceNumber(FK), SKU (PK,FK),Price, Quantity
  • Product: SKU(PK), Service, Brand, Service, BasePrice, TireModel

SKU, Service, Brand, BasePrice,TireModel were taken from InvoiceLineItem, and SKU became the PK for Product and a FK for InvoiceLineItem.

Third Normal Form

  • Invoice: InvoiceNumber(PK), MemberNumber(PK,FK), VehicleID(PK,FK),TimeIN, TimeOut, Date, Waiver, TotalPrice
  • Member: MemberNumber(PK), MemberLastName, MemberFirstName, Street, City, State, Zip, Phone

MemberNumber/LastName/FirstName were taken from Invoice and Member was made PK for Member and a FK for Invoice

  • MemberVehicle: VehicleID(PK), Make, Model, Year, Damage, Odometer, Plate, Mileage

Make, Model, Year ,Mileage, Damage and Odometer were taken from Invoice and VehicleID was made a PK for MemberVehicle and a FK for Invoice.

Super-Type Sub-Type

  • Product: SKU(PK), ProductType
  • Tire: SKU(PK,FK), Brand, Type, Size, BasePrice
  • Service: SKU(PK,FK), Service, BasePrice

Product was broken into two subtypes, Tire and Service. SKU was made a FK for the sub-types

Parent Child Relation

TireService Parent Child.png


Implementation

This phase corresponds to the physical modeling of the database. The work products of this phase are SQL scripts that create the database, populate it with sample data, and query the database. The developers responsible for these tasks assume the role of SQL Programmer.

Code Base

SQL scripts and design diagrams are stored on the project's central repository on the Google Code site at tireservice.

Query Examples

This section illustrates some of the queries that the team has written. Do not include SQL code in here! For each example, write in English a brief description of what the query does and then show the query output (or results). You can use <pre> and </pre> tags around the results text to preserve its formatting. Here it is an example:

Find all customer invoices by MemberNumber.

+------------+---------------+----------------+--------+---------+------+----------+--------+----------------+-------------+--------+
| Date       | InvoiceNumber | CurrentMileage | Make   | Model   | Year | Odometer | Damage | MemberNumber   | ProductType | SKU    |
+------------+---------------+----------------+--------+---------+------+----------+--------+----------------+-------------+--------+
| 05-12-2010 |             1 | 93803          | Subaru | Impreza | 2005 | 85500    |        | 11-23-5687-562 | Tires       | 123456 |
+------------+---------------+----------------+--------+---------+------+----------+--------+----------------+-------------+--------+
1 row in set (0.00 sec)

Find an individual member's vehicle(s).

+----------------+------+----------+------+------------------+-----------+----------+
| Member Number  | Make | Model    | Year | Original Mileage | Damage    | Plate    |
+----------------+------+----------+------+------------------+-----------+----------+
| 11-11-1111-111 | Jeep | Wrangler | 2003 | 125500           | flat tire | 123 4567 |
+----------------+------+----------+------+------------------+-----------+----------+
1 row in set (0.00 sec)

Find invoices with a ProductType 'Tires'.

+----------------+--------------+--------+---------+------+
| Invoice Number | Product Type | Make   | Model   | Year |
+----------------+--------------+--------+---------+------+
|              3 | Tires        | BMW    | 550i    | 2010 |
|              1 | Tires        | Subaru | Impreza | 2005 |
+----------------+--------------+--------+---------+------+
2 rows in set (0.00 sec)

Find work done by a certain date.

+------------+-----------+------------+--------+---------+--------------+
| Date       | Last Name | First Name | Make   | Model   | Product Type |
+------------+-----------+------------+--------+---------+--------------+
| 05-12-2010 | Scott     | Frank      | Subaru | Impreza | Tires        |
+------------+-----------+------------+--------+---------+--------------+
1 row in set (0.00 sec)

Finds the original invoice(s) of individual customers.

+----------------+----------------+--------------+----------+------+-------+------+
| Invoice Number | MemberNumber   | Product Type | Quantity | Make | Model | Year |
+----------------+----------------+--------------+----------+------+-------+------+
|              3 | 22-22-2222-222 | Tires        |        2 | BMW  | 550i  | 2010 |
+----------------+----------------+--------------+----------+------+-------+------+
1 row in set (0.00 sec)