辅导 BTM 211 Management Information Systems DB Assignment – Fall 2025辅导 R语言

Department of Accounting and Business Analytics

BTM 211

Management Information Systems

DB Assignment – Fall 2025

Case Study: Maverick Airways

Background

Established in Calgary in 2004, Maverick Airways grew to be one of western Canada’s most reliable airlines. With a proven track record of on-time departures, exceptional customer service, and an expansive fleet of aircraft, we ensure every journey is smooth, safe, and stress-free.

We take pride in connecting Canadians from east to west and across oceans, providing everyone with options both affordable and luxurious. So, wherever your journey might take you, trust Maverick Airways to get you there. It’s what we do!.

Problem

Pete is looking to implement a database for Maverick Airways so he can manage data more effectively across the company. The database will be used not only by him but also by employees within all departments of the company.

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

Requirements:

Part A:

Create the SQLite database tables.

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

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:

Import data into the SQLite database.

Using the techniques you learned in the lab videos, import the provided sample data (MaverickAirways_Data_Fall_2025.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.

Part C:

Insert new data rows into tables.

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

1. Create two (2) new passengers in the Passenger table. Use the following information for the new passengers.

2. Create two (2) new tickets in the Ticket table and include relevant information. Use the following information for the new tickets:

New Ticket 1:

New Ticket 2: *Changes to image: ECON should be spelt ECNO to fit with data sheet*

Part D:

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

Pete wants a list containing all the rows and columns for every passenger they have on file. Write a query to display the following information (from left to right):

● PassengerId

● FirstName

● LastName

● Municipality

● ProvinceState

● Country

● PostalCode

● Email

● PhoneNumber

● PassportNumber

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

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

Query 2

This time, Pete wants a list of every passenger, but he only wants to include their first name, last name, and address. However, Pete wants the first names and last names to be in one column, and the full address to be in one column.

Write a query to display the following information (from left to right). Concatenate the passenger names and locations and rename them with the alias name provided in quotation marks:

● FirstName and LastName as “Passenger Name” (separate each name with a space)

● StreetAddress, Municipality, ProvinceState, and Country as “Passenger Location” (separate each name with a comma)

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

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

Query 3

Pete wants a list of every airport they fly to, but only if the airport is located in Canada.

Write a query to display the following information (from left to right).

● AirportCode

● AirportName

● AirportCountry

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

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

Query 4

Pete wants a list that displays the count of airplanes by manufacturers in ascending order. Write a query to display the following information (from left to right):

● Manufacturer

● COUNT(Manufacturer) AS “Count of Aircraft by Manufacturer”

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

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

Query 5

Pete wants a list of every route they service, but he only wants to see their routes with distances over 1000KM in descending order. Write a query to display the following information (from left to right): Spelling change.

● OriginAirportCode

● DestinationAirportCode

● DistanceInKm 

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

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

Query 6

Pete wants a list that displays the aircraft id, but he only wants to see Boeing 737-800 aircrafts. Write a query to display the following information (from left to right): Wording change

● AircraftId

● Manufacturer

● Model

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

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

Query 7

Pete wants a list of the ticket types, but he only wants to see typecode, full name, and description Write a query to display the following information (from left to right):

● TicketTypeCode

● FullName

● Description

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

Copy and paste your working SQL statement in the box below

Query 8

Pete wants a list of every flight that has been cancelled. Write a query to display the following information (from left to right):

● FlightInstance

● FlightId

● Status

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

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

Query 9

Pete wants to know what account belongs to which passenger, sorted from A-Z, and the tier that account is. Write a query to display the following information: (from left to right)

Tables used:

● AccountId

● PassengerId

● FirstName and LastName AS “Passenger Full Name”

● Tier

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

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

Submission Instructions

Before the submission deadline, you must submit electronically the following:

● One (1) SQLite database (.db) file

o We will not accept any other file types. If we cannot open your database on our computers, you will be penalized for having to re-submit the assignment.

● One (1) completed DB Assignment REQUIREMENTS document with your SQL queries pasted in Section D.

o We will not accept your query results. You must submit your query statements (the code you wrote). Failure to submit your code will result in a penalty for having to re-submit the assignment.

File naming convention: AssignmentCode_FirstNameLastName_LectureSection

Example: DB_LadyGaga_A02

Cheating and Plagiarism

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.  

The University of Alberta is committed to the highest standards of academic integrity and honesty. Students are expected to be familiar with these standards regarding academic honesty and to uphold the policies of the University in this respect. Students are particularly urged to familiarize themselves with the provisions of the Code of Student Behavior. (online at www.ualberta.ca/secretariat/appeals.htm) and avoid any behavior. that could potentially result in suspicions of cheating, plagiarism, misrepresentation of facts and/or participation in an offence. Academic dishonesty is a serious offence and can result in suspension or expulsion from the University.