Department of Accounting and Business Analytics

BTM 211

Management Information Systems

DB 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

Brian Edge is looking to implement a database for JTI so he can manage data more effectively across his company. The database will be used not only by him but also by employees across the company at various different levels.

Using the data model provided, create a database for JTI in SQLite.

Requirements:

Part A (15 marks):

Create the SQLite database tables.

Build an SQLite database of TABLES that match the official “Joshua_Tree_Instruments_Data_Model.pdf” provided in the Assignments folder on eClass.

IMPORTANT: Do NOT use your personal data model that you built in the Data Model Assignment

1. Create each table with the correct name. You should create nine (9) tables.

2. Create all attributes for each table with the correct name and SQLite data types.

3. Implement the primary keys (or composite keys)

4. Implement each relationship for every foreign key.

Part B (15 marks):

Import data into the SQLite database.

Using the techniques you learned in the lab videos, import the provided sample data (JoshuaTreeInstruments_Data_2024_Fall.xlsx) into each table.

1. Prepare a CSV (Comma Delimited) file for each table you intend to import into your SQLite database. Name each CSV file the name of the table. You should have nine (9) CSV files in total.

Note: You will need to reformat and “clean up” the sample data spreadsheet by cutting and re-pasting some of the columns from each table into its own CSV file.

2. Using SQLite Studios "Tools - Import" function, populate your database tables with the appropriate CSV files. 

Hint: First import the “Parent” tables that act as “1” tables in 1-M relationships – then import the “Child” tables. In general, work from the outside-in on the data model.

Part C (20 marks):

Insert new data rows into tables.

Using the techniques you learned in the Labs, insert new data rows into tables

1. Create two (2) new Customers in the Customer table. Use the following information for the new customers.

2. Create two (2) new rental agreements in the RentalAgreement table for each new customer created above, as well as the corresponding RentalItem(s). Use the following information for the new rental agreements and rental items:

New Rental for Customer 1:

New Rental for Customer 2:

Part D (50 marks):

Query the data in the SQLite database using Select.

Using the techniques you learned in the Labs, write a SELECT QUERY to answer the following questions based on the data in your SQLite database.

IMPORTANT: You MUST include your “working” SQL statements in this document. The code should not include any line numbers. We will copy these SQL code and test run with your database. Failure to submit working SQL will result in deductions.

Query 1 (5 marks):

Brian wants to pull a list of every rental id from his database. Write a query to display the following information (from left to right):

● RentalID

● StoreID

Important:. Do not include the line numbers when you copy and paste in the yellow box.

Copy and paste your working SQL statement in the box below:

Query 2 (5 marks):

Brian wants to see every row and column from the customer table. Write a query to display the following information (from left to right):

● CustomerId

● RepEmployeeID

● CreditCardNumber

● LastName

● GivenNames

● EmailAddress

● Address1

● Address2

● Municipality

● ProvinceState

● Country

● PostalCode

● PhoneNumber

Important:. Do not include the line numbers in the yellow box.