Creating a Database
0
Table of Contents Company Overview 1 Mission statement and Goals 2 Mission Statement: 2 Goals:………………………………………………………………………………...2 Critical Success Factor…………………………………………………………………….3 Major Concerns of the Company………………………………………………………….4 Quantative and Qualititative Variable of Success Measurement………………………….5 Business Rule…………………………………………………………………………......,6 Entity Relationship Diagram………………………………………………………………7 Relationship and Cardinality………………………………………………………………8 Relationship Description…………………………………………………………………..9 Normalization Model…………………………………………………………………10-11 Refrences…………………………………………………………………………………12
Company Overview
Leila Auto Sales provides reliable and cost-efficient vehicles for the local community of Denver, Colorado. It’s a family run business with 12 years of top-notch customer service which specializes in the used car. According to Leila Auto Sales they are committed with satisfying their customer with great car deals and service, “The staff here at Leila Auto Sales has been happy serving the Aurora, CO area and would love to help you with your next vehicle purchase. Every car we sell comes with a free comprehensive CARFAX vehicle history report. We will do our best so that you leave with a smile on your face and are satisfied with your purchase.”
Leila Auto Sales is a family run business whose owner is Mr. Moe. He along with his wife and two other employees has been able to provide the customer with the reliable used vehicles within their budget. This retailer deals with vehicles like cars, pickups, vans, and SUVs from brands like Toyota, Nissan, Chevrolet, Ford, Hyundai, Jeep, Honda, and many more. The typical price range for the vehicles starts from as low as $2,999 to as high as $135,00. According to the owner, Moe, the targeted customer for his products are mostly low income and average income people who are seeking the best quality product at an affordable price. One of the distinctive parts about this retailer is that their vehicles are usually well below the bluebook value and they also have provision to look for the different types of vehicles as per the demand and request of the customer.
Mission statement and Goals
Mission Statement:
Leila Auto Sales' mission is to provide the customer with the best purchase and ownership experience. Customer satisfaction is their top priority and they want to make sure their customer walk away rethinking the automotive buying experience.
Goals:
Leila Auto Sales' goal is to create a loyal customer base and expand its business horizon.
Some of the major goals are:
· Increase sales by 12%
· Expand the business operation
· Proper management of the online website
· Adaptation of a better Inventory management system.
Critical Success Factors:
To ensure the mission and goals of Leila Auto Sales is achieved in timely fashion, the following critical factors need to be considered.
|
Objective (Goals) |
Critical Success Factors |
|
Increase sales by 12% |
Attract new customers |
|
|
|
|
Expand the business operation
Proper Management of the online website
Adaptation of better inventory management system |
Establishment of new locations, advertisement, and add other brands of vehicles
Update the online inventory regularly, Seek professional help for website design and management
Use LIFO or FIFO inventory management using excel |
The above-mentioned goals could be achieved if only Leila Auto Sales pay proper attention to the given critical success factors. To expand their business operation and increase the sales by 12%, Leila Auto Sales need to attract new customers either through offering better discounts or after-sale services or advertisement, adding other brands of cars in their inventory or changing the business hours from 1 pm to 5 pm to 8 am to 5 pm. Likewise establishing new locations focusing on customer service and better financing options for the car could also go a long way in expanding their business.
Continuous updates of online inventory and seeking professional help to manage their online website could also help Leila Auto Sales to make their online website more appealing to customers. They could use HTTP to make their website look more authentic and also focus on marketing via social media like a Facebook market place.
Major Concerns of the Company
According to Moe, his business has not been able to keep up with the growing demand from customers and has been using the traditional method of record-keeping (writing in the paper). He explained he has no idea about the database system and gives very little attention to the online website.
So far Leila Auto Sales has been doing pretty good business but not been able to meet their targeted increase in sales by 12%. Moe also explained his inventory control is also messed up as he just maintains a record of the number of vehicles within his premises. While being asked how many numbers of vehicles he sells every year, he gave a rough estimation of around 250.
Leila Auto Sales has a fairly nice online website for a small family-owned business. However, certain things need to be considered regarding the online website like a continuous update of inventory, their opening hours, and website design. Some of the vehicles listed on the online websites have already been sold but according to the website, it is still for sale. Likewise, their online website looks not that appealing and authentic which could give their potential customer a negative idea about their business.
Customer service of Leila Auto sales is excellent which can also be observed from their online review of 4.7. However, the customer seems to be confused regarding who is the employee in their business due to lack of dress code for employees.
Quantitative and Qualitative Variables for success measurement
The following quantitative and qualitative variables need to be used for the measurement for the success or the achievement of goals and objectives set by Leila Auto Sales.
|
Objective (Goals) |
Major Indicators (Qualitative / Quantitative) |
Source |
|
Increase sales by 12%
Expand business operation
Adaptation of better inventory management system
Proper Management of online websites |
Baseline count of automotive sold / Comparison of historical income and expenditures account
Baseline count of product offered / Baseline count of store location/ Customer Satisfaction
Use of LIFI and FIFO method
Baseline count of online website visit by customer/ Increase in online inquiry/ Customer reviews |
Order (Database)
Product (Database) Location (Database) Online review and customer satisfaction surveys
Inventory (Database) Product (Database)
Online customer (Database) |
|
|
|
|
|
|
|
|
Business Rules
Some of the business rules of Leila Auto sales regarding its business operations are:
· An individual is treated as customer if s/he is related with the purchase of one or more vehicles.
· Every invoice must contain the customer record including name, phone and email address.
· Customer must provide business with their physical address if they purchase vehicles through retailers or if they wish to get a quote.
· Car for sale must specify the vehicles details like asking price, model, current mileage, and pictures of the car if it is for online.
· Vehicles that are sold must contain date of sale, agreed price, monthly payment and monthly payment date.
· All the purchase and sale of vehicles need to be conducted via. bank or any financing institution. Cash or check payment won’t be accepted for the sale of vehicle.
· If customer wish to make payment of vehicle in full while purchasing the vehicle, staff need to record the payment amount and other customer details.
· If customer wish to finance for the vehicle, finance company name, repayment start and end date must be recorded.
· Retailer / finance company must provide customer with payment detail 5 business days prior the actual payment date of vehicle. Each payment detail must consist of customer payment due date, customer payment made date, actual payment amount, and remaining balance.
Entity Relationship Diagram
Relationship and Cardinality
|
Customers |
|
Customer ID (PK) |
|
Last Name |
|
First Name |
|
Email Address |
|
Phone Number |
|
Cars Sold |
|
Car Sold ID (PK) |
|
Car for Sale (FK) |
|
Customer ID (FK) |
|
Date Sold |
|
Agreed Price |
1 to M
|
Car Financing |
|
Finance ID (PK) |
|
Car Sold ID (FK) |
|
Finance Company Name |
|
Repayment Start Date |
|
Repayment End Date |
Is a part 1 to M
|
Customer Payment |
|
Customer Payment ID (PK) |
|
Customer ID (FK) |
|
Car Sold ID (FK) |
|
Customer Payment Date Due |
Of M to 1
Customer
|
Addresses |
|
Address ID (PK) |
|
Customer ID (FK) |
|
Address Line |
|
City / Town |
1 to M
|
Cars for Sale |
|
Car for Sale ID (PK) |
|
Car Manufacturer |
|
Vehicle Category |
|
Car Model |
Relationship Descriptions
A customer can be sold 1 to many cars.
A retailer can keep 1 and only customer information.
A car sold is 1 of many cars for sale.
A car for sale has 1 and only 1 car sold.
A car sold can have 1 and only financing institution.
A financing institution can deal with 1 to many cars sold.
A customer can make 1 to many payments.
Payments can have 1 and only customer record.
An address is a part of customer.
A customer can have 1 and only address.
Leila Auto Sales Normalization Model
|
Table Name
|
1NF (1st Normal Form) |
2NF (2nd Normal Form) |
3NF (3rd Normal Form) |
|
Customers · Customer ID (PK) · Address ID (FK) · Phone Number · Email Address |
Already in 1NF – no repeating groups |
Created Addresses Entity to eliminate duplicate data with full address detail |
Already in 3NF –data appropriately dependent upon primary key |
|
Addresses
· Address ID (PK) · Customer ID (FK) · Address Line · City / Town · State / County · Zip Code
|
Already in 1NF – no repeating groups |
Already in 2NF – no duplicate data |
Already in 3NF – appropriately dependent upon primary key |
|
Cars Sold
· Car Sold ID (PK) · Car for Sale ID (FK) · Customer ID (FK) · Date Sold · Agreed Price · Monthly Payment Amount · Monthly Payment Date
|
Already in 1NF – no repeating groups |
Already in 2NF – no duplicate data |
Already in 3NF – appropriately dependent upon primary key |
|
Car Financing
· Finance ID (PK) · Car Sold ID (FK) · Finance Company Name · Repayment Start Date · Repayment End Date
|
Already in 1NF – no repeating groups |
f |
Already in 3NF – appropriately dependent upon primary key |
|
Customer Payment
· Customer Payment ID (PK) · Customer ID (FK) · Car Sold ID (FK) · Customer Payment Date Due · Customer Payment Date Made · Actual Payment Amount · Remaining Balance
|
Already in 1NF – no repeating groups |
Already in 2NF – no duplicate data |
Already in 3NF – appropriately dependent upon primary key |
|
Car For Sale
· Car for Sale ID (PK) Vehicle Category · Car Model · Asking Price · Current Mileage · Other Car Details |
Already in 1NF – no repeating groups |
Already in 2NF – no duplicate data |
Already in 3NF – appropriately dependent upon primary key |
References Duncan, W. J. (2004). Critical success factors. In M. J. Stahl (Ed.), Encyclopedia of health care management, sage. Sage Publications. Credo Reference: https://search-credoreference-com.proxy.cecybrary.com/content/entry/sageeohcm/critical_success_factors/0?institutionId=556 Montemayor, H. M. V., & Pirvulescu, R. (2015). FDI success factors: Evidence from a European manufacturer in the Chinese automobile industry. Journal of Management Policy and Practice, 16(2), 61-70
Customers Addressess Cars_Sold Cars_for_Sale Customer_Payments Car_Financing customer_ID int FK PK phone-number int FK PK email_address int FK PK address_ID int FK PK customer_ID int FK PK address_Line int FK PK car_Sold_ID int FK PK car_for_Sae_ID int FK PK customer_ID int FK PK car_for_Sale_ID int FK PK car_manufacturer int FK PK car_model int FK PK vehcile_category int FK PK other_Car_Features int FK PK current_Mileage int FK PK asking_Price int FK PK city_Town int FK PK zip_code int FK PK state_County int FK PK date_Sold int FK PK agreed_Price int FK PK monthly_Payment_Date int FK PK monthly_Payment_Amount int FK PK customer_Payment_ID int FK PK customer_ID int FK PK car_Sold_ID int FK PK customer_Payment_Date_Due int FK PK remaining_Balance int FK PK actual_Payment_Amount int FK PK customer_Payment_Date_Made int FK PK finance_ID int FK PK car_Sold_ID int FK PK finance_Company_Name int FK PK repayment_Start_Date int FK PK repayment_End_Date int FK PK monthly_Payments int FK PK M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4
CustomersAddressessCars_SoldCars_for_SaleCustomer_PaymentsCar_Financingcustomer_IDPKphone-numberemail_addressaddress_IDPKcustomer_IDFKaddress_Linecar_Sold_IDPKcar_for_Sae_IDFKcustomer_IDFKcar_for_Sale_IDPKcar_manufacturercar_modelvehcile_categoryother_Car_Featurescurrent_Mileageasking_Pricecity_Townzip_codestate_Countydate_Soldagreed_Pricemonthly_Payment_Datemonthly_Payment_Amountcustomer_Payment_IDPKcustomer_IDFKcar_Sold_IDFKcustomer_Payment_Date_Dueremaining_Balanceactual_Payment_Amountcustomer_Payment_Date_Madefinance_IDPKcar_Sold_IDfinance_Company_Namerepayment_Start_Daterepayment_End_Datemonthly_Payments
0
Da
tab
ase System Development
and Implementation Plan for
“Leila Auto Sales”
[Document subtitle]
0
Database System Development
and Implementation Plan for
“Leila Auto Sales”
[Document subtitle]