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.
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.
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.
1. 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.
2. 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)
3. 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.
4. 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.
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.
5. Make sure the ticket_No field is set to the AutoNumber and designate this field as the primary
key.
6. 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:
6.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.
6.2. In the Lookup Wizard popup, select “I want the lookup field to get the values from another
table or query” and click Next.
6.3. Choose the SEAT table from the list of tables and click Next.
6.4. Select the seat_ID, seat_No, seat_Type, and plane_Tail fields as your Selected Fields and
click Next.
6.5. Uncheck the Hide Key Column checkbox and resize the lookup columns so the field headers
are visible. Click Next.
6.6. Select seat_ID as the field value for storage in the TICKET table and click Next.
6.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.
6.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.
7. 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
2. 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.
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:
1. Use the Reports Wizard command under the Create tab in the ribbon.
8. Select the FLIGHT table, and add the flight_No, flight_departure, flight_arrival, flight_origin,
and flight_destination fields to the selected fields box.
9. There is no need for any groupings, skip the next screen.
10. Choose a Tabular layout.
11. Save the report as “1_Flights_Report”.
12. 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:
1.
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.
13. To improve the aesthetics of your navigation form, switch to Design view:
13.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.
13.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.
14. 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.
14.1. 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.
14.2. 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. 6
14.3. 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.
15. Within a two-page limit, report on the four enhancements you implemented above. Your report
must adhere to the following formatting requirements:
15.1. Normal margins (1” or 2.54 cm)
15.2. Letter size (8.5” by 11” or 21.59 by 27.94 cm)
15.3. Font: Calibri in size 11
15.4. Single line-spacing with one blank line between paragraphs
15.5. Images can be included but must fit within the two-page limit (no appendices)
15.6. Enter your Full Name, Student ID, and Date in the Header.
