Assignment #3 – Access
Due Date: April 6, 2024 @ 11:59PM
ASSIGNMENT OBJECTIVES
The objective of this assignment is to get you familiarized with the basics of designing a database in a simple RDBMS application such as MS Access. As an introductory assignment, each requirement is elaborated through a series of guided steps. In addition to advancing your knowledge of relational databases, a complementary objective is to foster your analytical thinking and logical reasoning skills.
SUBMISSION INSTRUCTIONS
1. This assignment is due by 11:59 PM on Saturday, April 6, 2024.
2. This assignment is to be completed individually and must adhere to the University’s policy on Academic Integrity. 1
3. The assignment submission must be:
• One (1) electronic .ZIP file 2 3
,
• Contain only files pertinent to this assignment, and
• Have files that follow the naming and file format conventions specified below.
• Make sure to download and open your submission from Brightspace after you have submitted to ensure the files are as expected. Any files that cannot be opened (e.g. the file extension was removed or altered) will receive a grade of zero.
4. The following files and file naming are required (including underscores):
• ADM1370_W24_A03_StudentID.zip (contains your submission files)
• ADM1370_W24_A03_StudentID_Academic (PDF or MS Word file)
• ADM1370_W24_A03_StudentID_Database (Access file)
• ADM1370_W24_A03_StudentID_Report (both PDF and MS Word files)
5. Include your signed Declaration of Academic Integrity (see next page).
6. Failure to properly name your files, submit in the correct file formats, or omitting your Academic Integrity statement will result in deductions, up to and including receiving a mark of zero on the assignment.
1 https://www.uottawa.ca/current-students/academic-integrity
How to create ZIP files:
2 MacOS: https://support.apple.com/en-ca/guide/mac-help/mchlp2528/mac
3 Windows: https://support.microsoft.com/en-us/windows/zip-and-unzip-files-f6dde0a7-0fec-8294-e1d3-703ed85e7ebcADM1370 – Applications of Information Technology for Business
STATEMENT OF ACADEMIC INTEGRITY
Individual Assignment Checklist & Disclosure
Read the disclosure below following the completion of your assignment. Once you have verified these points, hand in this signed disclosure with your assignment.
1. I acknowledge to have read and understood my responsibility for maintaining academic integrity, as defined by the University of Ottawa’s policies and regulations. Furthermore, I understand that any violation of academic integrity may result in strict disciplinary action as outlined in the regulations.
2. If applicable, I have referenced and/or footnoted all ideas, words, or other intellectual property from other sources used in completing this assignment.
3. A proper bibliography is included, which includes acknowledgement of all sources used to complete this assignment.
4. This is the first time that I have submitted this assignment or essay (either partially or entirely) for academic evaluation.
5. I have not utilized unauthorized assistance or aids including but not limited to outsourcing assignment solutions, and unethical use of online services such as artificial intelligence tools and course-sharing websites.
ASSIGNMENT SCENARIO
Icarus Flights (IF) is attempting to modernize their information systems to better manage their operations. As part of their modernization project, they have tasked you with developing a prototype database application that they will then provide to developers and/or suppliers when obtaining an enterprise-grade system.
IF maintains a fleet of different aircraft that fly passengers across its regional routes. Each aircraft has various seat configurations, referred to as seat maps, that include different types and numbers of seats; for example, larger jet airplanes will have Premium Class, Business Class, and Economy Class seats while turboprop airplanes normally only have Economy Class seats.
When a passenger wishes to travel with the airline, they will book a seat on a particular flight and will be issued a ticket. The ticket will state which flight it is for, which seat on the airplane they will sit in, and how much they paid for the ticket. As the airline prioritizes passenger experience, they avoid double-booking seats on a flight, ensuring only one passenger is ever assigned to a particular seat for a flight.
When being given requirements for the prototype, a conceptual model was provided, along with the required tables and fields on the following pages.
To prepare the database for IF, you will need to perform the following actions:
create & populate tables,
link tables together through lookups and relationships,
create queries to display results,
create forms for data entry,
create reports for tables and queries,
create a navigation menu for the database.
Each of these are described throughout the rest of the document.ADM1370 – Applications of Information Technology for Business
Conceptual Model
Database Table Definitions
AIRPLANE
Field Name
Type
Length / Size
Index
Comments
plane_Tail
Short Text
6
Yes (No Duplicates) Primary Key
plane_Model
Short Text
30
plane_Manufacturer Short Text
30
SEAT
Field Name
Type
Length / Size
Index
Comments
seat_ID
AutoNumber Long Integer Yes (No Duplicates) Primary Key
seat_No
Short Text
45
Seats are identified by column and
row; for example, first row seats are
A1, then B1, then C1.
seat_Type
Short Text
10
Lookup Value List with the following
values: Premium, Business, Economy
seat_Price
Number
Formatted as Currency
plane_Tail
Short Text
Foreign Key (Lookup-based)
FLIGHT
Field Name
Type
Length / Size
Index
Comments
flight_No
AutoNumber Long Integer Yes (No Duplicates) Primary Key
flight_Depature
Date/Time
flight_Arrival
Date/Time
flight_Origin
Short Text
3
flight_Destination
Short Text
3ADM1370 – Applications of Information Technology for Business
Assignment #3 – Access
Due Date: April 6, 2024 @ 11:59PM
© 2024 David R. Hanssen - All rights reserved. No part of this document may be reproduced, stored in retrieval systems, or
transmitted in any form or by any means, electronic, mechanical, photocopying, recording or otherwise without prior written
permission from the instructors.
Page 7 of 20
V1.4
PASSENGER
Field Name
Type
Length / Size
Index
Comments
pax_ID
AutoNumber Long Integer Yes (No Duplicates) Primary Key
pax_Last_Name
Short Text
45
pax_First_Name
Short Text
45
pax_Full_Name
Calculated
Use Expression Builder to concatenate
the First and Last name with a space in
between them.
pax_Tier
Short Text
45
Lookup Value List with the following
values: Silver, Gold, Platinum
pax_Email
Short Text
80
pax_Phone
Short Text
15
Use the default Phone Number input
mask.
TICKET
Field Name
Type
Length / Size
Index
Comments
ticket_No
AutoNumber Long Integer Yes (No Duplicates) Primary Key
ticket_Price
Number
Double
Format as Currency
seat_ID
Number
Long Integer
Foreign Key (Lookup-based)
pax_ID
Number
Long Integer
Foreign Key (Lookup-based)
flight_No
Number
Long Integer
Foreign Key (Lookup-based)
PART 1 - CREATING TABLES
Start Microsoft Access and create a Blank Database.
Step 1.1 - Create the AIRPLANE Table
1. Create a new table using the Design View (Create => Table Design) command.
2. In the Field Name column, enter all the attributes as shown in the AIRPLANE table definition. Make sure the Data Type pertaining to each field is of the correct type.
3. Use the “Description (Optional)” column in the Design View to describe the type of information that goes in each of those fields (e.g. for the plane_Model field, state that the field captures the plane’s manufacturer’s model number)
4. Make sure that the plane_Tail field is designated as the primary key for this table. To set a field as your primary key, you can right click the field name and select Primary Key. Indicate this is the primary key field in the field description.
5. Save this table by clicking on the save icon on the top-left corner. You will be prompted for a name. Name the table “AIRPLANE”.
Now that you have at least one database object created, you save your database file using the notation specified under SUBMISSION INSTRUCTIONS. Make sure to save your database file regularly so that you do not lose any work. Whenever you are finished working on your file, make sure that it has been saved and stored somewhere that you can open or download from at a later time.
Step 1.2 - Create the SEAT, PASSENGER, and FLIGHT Tables
1. For the SEAT, PASSENGER, and FLIGHT tables, repeat the steps from Step 1.1, referring to the appropriate table definitions and assigning the correct primary keys.
Step 1.3 - Populate AIRPLANE, SEAT, PASSENGER, and FLIGHT Tables
1. Populate the AIRPLANE, SEAT, PASSENGER, and FLIGHT tables before proceeding to the next step. Sample data has been provided in a separate spreadsheet published on Brightspace for the AIRPLANE, SEAT, and
FLIGHT tables. For the PASSENGER table, create 10 passenger records with made-up information for testing, with one of them having your name and university email. Note that you will need to populate
airplanes before you can populate seats.
Step 1.4 - Create the TICKET Table
1. You will now need to create the TICKET table by repeating the prior steps while referring to the TICKET table definition and the following instructions. Name your table as TICKET when saving.
2. Make sure the ticket_No field is set to the AutoNumber and designate this field as the primary key.
3. The seat_ID, pax_ID, and flight_No fields in this table require special attention as these will be the foreign keys used to form relationships between the tables. You will need to use the Lookup Wizard to configure these foreign key fields as outlined below:
3.1. In the Design View of the TICKET table, create a field named seat_ID. For the Data Type, select Lookup Wizard from the drop-down options.
3.2. In the Lookup Wizard popup, select “I want the lookup field to get the values from another table or query” and click Next.
3.3. Choose the SEAT table from the list of tables and click Next.
3.4. Select the seat_ID, seat_No, seat_Type, and plane_Tail fields as your Selected Fields and click Next.
3.5. Uncheck the Hide Key Column checkbox and resize the lookup columns so the field headers are visible. Click Next.
3.6. Select seat_ID as the field value for storage in the TICKET table and click Next.
3.7. Confirm the name of the new field is “seat_ID” and click Finish.
Following the prior sequence of steps should produce a new field in the TICKET table such that every time a new record is being added, a drop-down menu appears with information being fetched from the SEAT table. Once a seat on a plane is selected, its seat_ID will appear as the field value.
3.8. Repeat the steps above to create the pax_ID and flight_No fields in the TICKET table. These fields should be based on lookups from the TICKETS table. For pax_ID: display pax_ID, pax_Full_Name, and pax_Tier in the dropdown but only store pax_ID. For flight_No: display all fields in the FLIGHT table but only store flight_No.
4. After creating all your fields, you can change the order in which the fields appear in the table. You can do so by opening the table in Design View and dragging the fields up or down. Re-arrange the table fields according to the order they appear in the TICKET table definition.
By using the Lookup Wizard in the TICKET table, we have already started to create relationships among the PASSENGER, FLIGHT, and SEAT tables. Next, we will verify and refine these relationships.
Step 1.5 - Populate the TICKET Table
1. Add 10 different different to your newly created TICKET table, making up the dates. Create multiple tickets where you are the passenger.
PART 2: DEFINING TABLE RELATIONSHIPS
The next step is to connect the tables:
1. Open the Relationships view (Database Tools => Relationships)
2. If you followed the steps in the previous part, the tables should have lines connecting them to represent their relationships. Rearrange the boxes so that the diagram resembles the conceptual model provided earlier in this document. If not all relationships were formed when creating the tables, use the “Edit
Relationships command under the “Relationships Design” tab to create them.
3. Notice that the relationship lines between the tables do not indicate the cardinality (e.g. one-to-many, many-to-one, etc.) of the relationships yet. To fix this, in the “Edit Relationships” dialog box, check the box next to “Enforce Referential Integrity”.
4. Save the relationships by clicking on the save icon in the toolbar.
PART 3: FORMULATING QUERIES
Step 3.1 – Platinum Status Query
Create a query that can be used to list all passengers with platinum status and their information.
1. Create a new query using the Design View (Create => Query Design).
2. Select the PASSENGER table, then select all fields within the table except for First Name and Last Name.
3. Under the pax_Tier field, specify the criteria to only show records where the field is equal to “Platinum”
4. Save this query as “1_Platinum_Query”
5. Run the query to ensure data is being correctly pulled and presented. If no data appears, review the previous steps, and ensure you have at least one passenger with platinum status.
Step 3.2 – Booked Seats Query
Create a query that can be used to search for booked seats on a specific flight.
1. Create a new query using the Design View (Create => Query Design).
2. Select the FLIGHT, TICKET, and SEAT tables, then select the following fields:
• FLIGHT.flight_NO
• FLIGHT.flight_Depature
• FLIGHT.flight_Arrival
• FLIGHT.flight_Origin
• FLIGHT.flight_Destination
• Seat.seat_No
• Seat.seat_Type
3. Under the flight_No field, specify a parameter based criteria with the statement “Enter Flight #”
4. Save this query as “2_Booked_Seats”
5. Run the query and provide a flight number for one of the flights in the FLIGHT table. Ensure that data appears and that the correct fields are visible.
Step 3.3 – Flight Statistics Query
Create a query that lists all passenger bookings by plane model and seat type, represented by the number of tickets booked and average cost per ticket.
1. Create a new query using the Design View (Create Query Design) and select all three tables.
2. The query should project the following the fields:
• AIRPLANE.plane_Model
• SEAT.seat_Type
• TICKET.ticket_NO
• TICKET.ticket_Price
3. Enable the “Totals” function under the “Query Design” tab to allow for calculign the number of tickets booked and the average cost per ticket. This will reveal a new “Total:” row in the query designer. Select the correct transformation for each column, and apply an alias to show the result as “Tickets Booked” and “Average Cost of Ticket”. 4
4. Save this query as “3_Flight_Statistics”
5. Run the query to ensure data is being correctly pulled and presented. If no data appears, review the previous steps, and ensure you have at least one ticket in the database.
4 An alias in this context means to show the user a different name the original table or field name when they run the query. To apply an alias in the Design View: (1) simply type the name you want to show, then put a colon (:) right after, followed by the name of the field.
For example - Field: Customer Name: custName would show “Customer Name” instead of “custName”.
PART 4: APPLICATION INTERFACE
To work with the database, a user interface needs to be created to give users the ability to view and edit data.
The following interface components are required:
1. a Passenger form
2. a Scheduled Flight report
3. an Airplane Flight Statistics report
4. a Navigation Menu form that contains the above form(s) and report(s)
Step 4.1 - Creating the Passenger Form
1. Select the Form Wizard under the Create tab in the ribbon.
2. Select the PASSENGER table and select all fields.
3. Choose the Columnar view.
4. Name the form object as “1_Passenger_Form”
5. To improve the aesthetics of your form, switch to Design View: (1) adjust the positioning of the form labels and fields, (2) change the fonts, and (3) add descriptive labels to different parts of the form:
5.1. Change the title of the form in the header to “Passenger Form”.
5.2. Insert an image of your choosing in the Detail section of the form.
You are encouraged to further customize the form, as long as the form is legible and usable. See below for an example of a what the form could look like:
Step 4.2 - Creating the Staff Directory Report
1. Use the Reports Wizard command under the Create tab in the ribbon.
2. Select the FLIGHT table, and add the flight_No, flight_departure, flight_arrival, flight_origin, and flight_destination fields to the selected fields box.
3. There is no need for any groupings, skip the next screen.
4. Choose a Tabular layout.
5. Save the report as “1_Flights_Report”.
6. To improve the appearance of your report, use the Design view to: (1) reposition and resize the headings and columns, (2) change fonts, and (3) use more descriptive column headings rather than the table field names. At a minimum, you should format your report to include (1) a descriptive title, (2) an image in the header and (3) descriptive column headings.
You are encouraged to further customize the report, as long as the report is legible and usable. See below for an example of a what the report could look like:
Step 4.3 – Create Flight Statistics Report
Create a report that lists all flight statistics, based on your query defined in Part 3, that is sorted by airplane model.
1. Use the Reports Wizard command under the Create tab in the ribbon.
2. Select the 3_Flight_Statistics query from Part 3.
3. There is no need for any groupings, skip the next screen.
4. Select the tabular layout.
5. Save the report as “2_Flight_Statistics”.
6. You are encouraged to customize the report, as long as the report is legible.
Step 4.4 - Creating a Navigation Form
To enhance the usability of your database, you will now create a main menu that allows the user to click on buttons and launch certain components of the database.
1. You will do this by creating another form called “0_Navigation” using the Navigation command under the Create tab.
• You can use either the Horizontal or Vertical tabs layout and drag and drop the appropriate database objects into the navigation form. You are welcome to create as many navigation menu options as you like, but at a minimum, your navigation menu should have the following components:
2. Create a tab labeled “Passenger Form” that launches the Passenger form you created in Step 4.1.
3. A tab labeled “Scheduled Flights” that launches the Scheduled Flights report you created in Step 4.2.
4. A tab labeled “Airplane” that launches the Airplane Flight Statistics report you created in Step 4.3.
Note: You can drag database objects from the left navigation pane onto the “[Add New]” placeholder, then rename the items by double-clicking on them.
7. To improve the aesthetics of your navigation form, switch to Design view:
7.1. At a minimum, you should format the navigation form to include a descriptive title and, in the footer, include a text label that includes your name and student number.
7.2. Also include a button labeled “Exit Database.” The button should allow a user to quit the MS Access application. In the Design View of the Navigation form, you can use the button control in the Form
Design Tools => Controls group. In the Command Button Wizard, select the “Application” category and the “Quit Application” action.
8. Lastly, configure the database to open this form by default when the user opens the database. To do this, you can change the settings under File => Options => Current Database => Application Options and selecting “0_Navigation” from the Display Form drop-down menu.
PART 5: DATABASE ENHANCEMENTS
While the database you have prepared is sufficient as a prototype, there are many improvements that could be made. You are to propose enhancements by adding additional queries and forms to your version of the database. You are to justify their inclusion by writing a report that describes each addition and how it could be used by IF to manage the airline.
Tasks:
1. Create a Microsoft Word document and name it “ADM1370_W24_A03_StudentID_Report”, replacing StudentID with your Student Number. Enter your name and StudentID in the header. 5
2. Within your database, add the following enhancements and name them with the prefix “5_”:
2.1. Create an additional query in the database that uses the Totals feature (Query Design => Show/Hide), while using a combination of fields from at least 2 tables in this database. Provide a short description of the purpose and benefit of this query in your report.
2.2. Can you think of any additional queries that will be useful for the IF? Implement at least ONE additional new query in the database and provide a short description of the purpose and benefit of the query in your report.
2.3. Create a hierarchical form (main form/sub form) that uses information from the PASSENGER table in the main form and fields of your choice from the TICKET table. Describe the purpose of this form in your report.
2.4. Can you think of another hierarchical form (main form/sub form) that would be useful in this database? Implement at least ONE additional new hierarchical form in the database and provide a short description of the purpose of the form in your word document.
3. Within a two-page limit, report on the four enhancements you implemented above. Your report mustadhere to the following formatting requirements:
3.1. Normal margins (1” or 2.54 cm)
3.2. Letter size (8.5” by 11” or 21.59 by 27.94 cm)
3.3. Font: Calibri in size 11
3.4. Single line-spacing with one blank line between paragraphs
3.5. Images can be included but must fit within the two-page limit (no appendices)
5 https://www.wikihow.com/Add-a-Header-in-Microsoft-Word
6 Records in the subform should relate to the record selected in the main form. If records in the subform do not change when selecting a new record in the main form: (1) navigate to the Design View, (2) click on the subform, (3) under the “Data” tab on the right, click on “Link Master Fields”, (4) select the appropriate primary/foreign key field for both the Master and Child fields.
3.6. Enter your Full Name, Student ID, and Date in the Header.
Once your database is finished, your object pane should resemble the following but will be unique to you based
on your enhancements proposed in Part 5:
REVISIONS
Date
Comments
March 23, 2024
• Removed references to unused tables.
March 26, 2024
• Updated TICKET table definition (Price Field)
• Updated reference in Step 4.3 to query in Step 3.3