Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
Department of Accounting and Business Analytics
BTM 211
Management Information Systems
DM Assignment – Fall 2024
Case Study - Joshua Tree Instruments
Background
Joshua Tree Instruments (JTI) has been the top choice for musical instruments rentals in Alberta since 1987. What started as a hobby for Brian Edge, an avid guitar collector and rock enthusiast, slowly turned into a business. Now, Brian has expanded and carries the top brands in musical instruments.
After opening his first retail location in south Edmonton, Brain opened six more stores, another in northern Edmonton, two in Calgary, one in Grande Prairie, one in Red Deer, and his most recent expansion, a store in Lethbridge. With 90 employees spread out across his locations, JTI has been able to serve the Albertan community with pride and hopes to one day expand into other provinces on the Western coast while maintaining their reputation of carrying the biggest brands in instruments and the highest quality of rentals.
Problem
JTI serves a wide range of customers from solo artists, bands, to schools and educators. Brian explained they currently have a computer system to manage all 5000 of their customers, however it’s outdated and has been struggling to keep up with demand.
“When a customer comes into any one of our locations, an employee will ask them if they have opened an account with us. If they have, we ask them for their account number so we can look them up in our system. If they do not, we give them a paper form to fill out. On the form, we ask for their given name and last name, email address, phone number, and address (municipality, province/state, country, postal code). We also ask for a credit card number to keep on file, that way we can process any outstanding payments without requiring the customer to come in.”
Once they fill out the form, Brian mentioned that the servicing employee will manually enter the information into our system and open an account for them. The account is used to track customer rental agreements and to manage any outstanding fees. He also mentioned that if a customer is part of a band or larger group, we consolidate their accounts so they can rent under one number. We currently have 250 accounts on file.
Over the past couple of years, JTI has seen a dramatic spike in instrument rentals and although they have a computer system to manage their customer accounts, they currently have nothing digital to manage their rental agreements and instrument inventory. To combat this, Brian wants to implement a records management system.
Currently, Brian scans every rental agreement (7500) and saves it under the customer’s account number, then archives each physical agreement in a filing cabinet for five years. At the end of the five years, he shreds the agreement.
Brian has provided us with a copy of one of his rental agreements, as seen here:
Brian explained that for any items rented (15,000) that cost over 200 dollars they add a warranty of 50 dollars to the product to further protect it in the case of damages.
Each employee at JTI has an identification number, and every employee is managed by a supervisor. Under that identification number, their last name is managed, as well as their job type, salary, hire date, and commissions.
JTI is proud to carry a wide variety of instruments to please every type of customer, from pianos, guitars, drums, microphones, brass, woodwinds, and string and they keep track of the different types with a code. To accompany each code, there is also a longer description and in stock quantity. But as Brian’s inventory has grown, he has had a hard time keeping track of every instrument.
“Currently, we have 10,000 instruments documented across all of our locations and have a unique identification number for each. We offer a large variety of instruments both new and old, therefore we need to keep track of the manufacturer and the manufactured date. We also need to know the condition of the instrument. If an instrument has been previously repaired (4520), it will have a repair id associated with it that describes the repair and the cost. We also keep a record of the wholesale price of the instrument, and the store that the instrument is associated with.”
Each store is identified by its identification number, and JTI keeps track of its address (municipality, province, country, postal code) and square footage.
Requirements
Using Draw.io software and the attached “JTI_DM_Assignment_Starting_Point.drawio” file as your starting point, create an Entity Relationship Diagram (i.e., data model) of the Joshua Tree Instruments business, as shown in the case above.
Starting from the provided starting point diagram, completely and correctly specify all Entities, Relationships, and Attributes as described in the lecture and lab materials on Entity Relationship Diagramming. Include all relevant facts from the case in your model, including primary keys, volumes, data types for all fields, descriptive labels, and foreign key fields.
There is no need to create or assume any new entities or attributes other than what is required for the above case. Your final model should have exactly NINE entities.
You can use any naming convention you want as long as we can understand what you’re trying to say. (ie. first name instead of given names)
When making relationships, ensure that your lines DO NOT overlap as you will be deducted marks.
Use a white background when creating your diagram. If you use a dark background you will be deducted marks.
When describing your relationships, you can use the same root words (has, is in, contains) but the overall descriptions should be different for each relationship.
Use PascalCase for entities and camelCase for primary keys, foreign keys and attributes
NOTE: In the starting point diagram, the number of lines with ‘?????’s indicates the total number of expected attributes (including all keys). DO NOT reorganize the diagram.
All work is to be done individually. Do not copy, in whole or in part, the work of others, including paper printouts, electronic files, or computer programs. Do not use the work of others as a starting point and then modify it. All work submitted under your name must be yours and yours alone.
Marking Scheme
DATA MODEL:
● Correct entities (well named, identified, volumes)
● Correct relationships ((Connected to correct entities, cardinalities (1-M? M-1?) correct, well described, NO crossing of lines)
● Correct Attributes (Each in the correct entity, well named, correct data type, unique primary key, and foreign key fields are italicized)
● Submitted file created using draw.io, named correctly, delivered electronically through eClass, on time.