本次数据库代写是用Access为一家自行车公司设计数据库
IE 0015
Project information
Although Rolling Thunder is not a large company, you will need some time to understand
the systems and the database. The learning and challenges are exactly what you have to
take when stepping into the industry.
Problem setting
Spring 2014, you just graduated and chose to join the Rolling Thunder Bicycle Company.
As a young IE engineer, you are determined to prove yourself and are eager to apply what
you have learned to make an impact on the company as well as to society. Here are your
chances:
Scenario 1 (Identifying root cause for Quality issues and estimate overall Cost)
Customer Service Department has received over a dozen customer complaints, regarding
the quality issues of the frame of their bicycles. The customers claimed that their bicycles’
frame could “break” under normal usage. Fortunately, no one gets hurt. VP of Quality
Control suspects that it is an internal quality issue and hope you can help identify the
possible root cause from the data perspective. VP of Product Management (sometimes
R&D) Department pointed out that this is an urgent case since it might lead to customer
injury. Therefore, the VP of Customer Service requested your help to, based on your
State
TaxRate
StateTaxRate
CityID
ZipCode
City
State
AreaCode
Population1990
Population1980
Country
Latitude
Longitude
ManufacturerID
ManufacturerName
ContactName
Phone
Address
ZipCode
CityID
BalanceDue
ManufacturerID
TransactionDate
EmployeeID
Amount
Description
Reference
City
StoreID Manufacturer
StoreName
Phone
ContacFirstName
ContactLastName
Address
Zipcode
CityID
ManufacturerTrans
RetailStore
PurchaseID
EmployeeID
ManufacturerID
TotalList
ShippingCost
Discount
OrderDate
ReceiveDate
AmountDue
PurchaseOrder
ComponentName
AssemblyOrder
Description
PurchaseID
ComponentID
PricePaid
Quantity
QuantityReceived
EmployeeID
TaxpayerID
LastName
FirstName
HomePhone
Address
ZipCode
CityID
DateHired
DateReleased
CurrentManager
SalaryGrade
Salary
Title
WorkArea
PurchaseItem ComponentName
Employee
GroupID
ComponentID
CustomerID
TransDate
EmployeeID
Amount
Description
Reference
CustomerTrans GroupCompon
ComponentID
ManufacturerID
ProductNumber
Road
Category
Length
Height
Width
Weight
Year
EndYear
Description
ListPrice
EstimatedCost
QuantityOnHand
PaintID
ColorName
ColorStyle
ColorList
DateIntroduced
DateDiscontinued
SerialNumber
ComponentID
SubstituteID
Location
Quantity
DateInstalled
EmployeeID
Component
Paint BikeParts
ModelType
MSize
TopTube
ChainStay
TotalLength
GroundClearance
HeadTubeAngle
SeatTubeAngle
ModelSize
ModelType
Description
ComponentID
TubeID
Quantity
TubeID
Material
Description
Diameter
Thickness
Roundness
Weight
Stiffness
ListPrice
Construction
ModelType SerialNumber
TubeName
TubeID
Length
SerialNumber SerialNumber
CustomerID
ModelType
PaintID
FrameSize
OrderDate
StartDate
ShipDate
ShipEmployee
FrameAssembler
Painter
Construction
WaterBottle
CustomName
LetterStyleID
StoreID
EmployeeID
TopTube
ChainStay
HeadTubeAngle
SeatTubeAngle
ListPrice
SalePrice
SalesTax
SaleState
ShipPrice
FramePrice
ComponentList
CustomerID
Phone
FirstName
LastName
Address
ZipCode
CityID
BalanceDue
CompGroup
GroupName
BikeType
Year
EndYear
Weight
BikeTubes TubeMaterial
BicycleTube Customer Bicycle Groupo
LetterStyle
Description
LetterStyle
3
findings, provide further information to help him take appropriate measurements for this
case (e.g., callback). VP of finance is very concerned about the possible cost accrued from
the above measurements and are asking you to provide her with an estimation of the overall
cost if products should be recalled and replaced. Attached below is a list of customers from
whom the CS has received complaints.
Customer ID
26160
40505
29577
40579
18043
41008
2281
40791
40686
40539
875
288
40523
29422
40796
Scenario 2 (Forecasting for capacity expansion)
A new sales VP has just got onboard, approximately the same time as you. She is famous
in your industry for being very demanding, and you soon find that she lives up to her
reputation. She called upon your help to provide her with information for setting up market
strategy and making the market forecast. She would like to have the sales trend (by sales
amount and/or by sales quantity), from 1999 to 2013. You can define what dimension (e.g.,
by bicycle model, by color, by state) you would like to use and in what granularity (e.g.,
by year, by month, by week, by date) to give her the information. You can generate multiple
tables/reports for her.
Scenario 3 (Ad hoc problem solving)
You got a phone call from the California State Police. There has been an accident on the
road involving a bicycle rider who right now is hospitalized and unconscious. The police
need to contact the bicycle rider’s family urgently, but the rider has no ID with him, which
leads the police to believe that he lives not very far away (here means in the State of
California). The bicycle, with serial number unidentifiable due to the accident, is a white
Rolling Thunder road bike. Judged from the condition, the bicycle should be purchased
between 1998 to now. What is the information you can provide to the police based on the
current information that is available to you? What is the further information that you would
4
want to get from the police to help the investigation (e.g., how to narrow down the scope)?
List according to your priority.