ADM 1370 - Applications of Information Technology for Business - Winter 2022 - Assignment 3 Database Table Descriptions VOLUNTEERS Attribute Type Volunteer ID Number Volunteer_Last Name Short Text Volunteer_First_Name Short Text Length / Size Index Comments Long Integer Yes (No Duplicates) Primary Key 30 30 Use Expression Builder to concatenate the First and Last name values with a space in between them. Volunteer_Full_Name Calculated Volunteer Address Long Text Volunteer_Phone Short Text 15 Use the default Phone Number input mask. Monday Tuesday Wednesday Thursday Friday Saturday Sunday Yes/No Yes/No Yes/No Yes/No Yes/No Yes/No Yes/No RESIDENTS Attribute Type Comments Resident_ID Number Primary key Length / Size Index Yes / Unique/ No Long Integer Duplicates 30 30 Resident_Last_Name Resident First Name Short Text Short Text Resident_Full_Name Calculated Use Expression Builder to concatenate the First and Last name values with a space in between them Resident Address Long Text Resident_Phone Short Text 15 Use Default Phone Number Input Mask JOBS Attribute Type Length / Size Job_Number AutoNumber Long Integer Job Date Volunteer Resident Date/Time Number Number Long Integer Long Integer Index Format Comments Yes / Unique/ No Primary key Duplicates mm/dd/yy Foreign Key (Lookup-based) Foreign Key (Lookup-based) Medium Time Use Default Medium Time hh:mm AM/PM Input Mask Medium Time Use Default Medium Time hh:mm AM/PM Input Mask Time_Begin Date/Time Time_End Date/Time ADM 1370 - Applications of Information Technology for Business - Winter 2022 - Assignment 3 Part 1 - Creating Tables (20 points) Start Microsoft Access and create a Blank Database. At this point, a new blank table is available for you to use. Step A: Create the RESIDENTS Table (4 Points) 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 RESIDENTS table description. Make sure the Data Type pertaining to each Field Name is of the correct type. 4. Make sure that the Resident_ID field is also 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. You may want to also indicate this in the 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 "RESIDENTS". 3. Use the Description column for each field to reflect the type of information that goes in each of those fields (e.g. for "Resident_Address", you could state that this is this field captures the resident's primary address.) Now that you have one database object (your first table) you can save the database (File => Save As => Save Database As). When prompted for the filename, use "ADM1370_A03_G##_BCC.accdb". It is a good practice to keep saving your work at regular intervals. Please make sure to use the save icon (disk icon on the top left corner) after each step in the assignment. Step B: Create the VOLUNTEERS Table (4 Points) 1. Repeat the instructions from above, this time referring to the VOLUNTEERS table description. 2. Make sure you designate Volunteer_ID as the primary key and save your table as VOLUNTEERS when you are done with this step. Step C: Populate the RESIDENTS and VOLUNTEERS Tables (4 points) 1. Make up names and other pertinent 2. Populate the RESIDENTS and VOLUNTEERS information for 6 volunteers (including one tables with this sample data before proceeding group member) and 8 residents (include the to the next step. other group member). ADM 1370 - Applications of Information Technology for Business - Winter 2022 - Assignment 3 Step D: Create the JOBS Table (4 Points) 1. You will now need to create the JOBS table by repeating the prior steps while referring to the JOBS table description and the following instructions. Name your table as JOBS when prompted to save the table. 2. Make sure the Job_Number field is set to the AutoNumber Data Type and to designate this field as the primary key. 3. The Volunteer and Resident 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 under Data Type to configure these foreign key fields as outlined below: 3.1. In the Design View of the JOBS table, create a field named Resident. 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. Following the prior sequence of steps should produce a new field in the JOBS table such that every time a new record is being added, a drop- down menu appears with information being fetched from the RESIDENTS table. Once a particular Resident entry is selected, only the Resident_ID appears as the field value. 4. Repeat the steps above to create the Volunteer field in the JOBS table. This field should be based on a lookup from the VOLUNTEERS table. Volunteer_ID, Volunteer_Last_Name, and Volunteer_First_Name should appear in the drop-down menu of the field, and only the Volunteer_ID value should be stored in the field once a selection is made. 5. 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 JOBS table description. By using the Lookup Wizard in the JOBS table, we have already started to create relationships among the JOBS, RESIDENTS, and VOLUNTEER tables. Next, we will verify and refine these relationships. 3.3. Choose the RESIDENTS table from the list of tables and click Next. 3.4. Select the Resident_ID, Resident_Last_Name, and Resident_First_Name fields as your Selected Fields and click Next. 3.5. Sort the lookup values using Resident_Last_Name in Ascending order and click Next. Step E: Populate the JOBS Table (4 Points) 6. Add 10 different jobs to your newly created JOBS table, making up the dates and times. Create multiple jobs where you are the volunteer. Similarly, create multiple jobs where your group member is the resident. 3.6. Uncheck the Hide Key Column checkbox and resize the lookup columns so the field headers are visible. Click Next. 3.7. Select Resident_ID as the field value for storage in the JOBS table and click Next. 3.8. Confirm the name of the new field is "Resident" and click Finish. ADM 1370 - Applications of Information Technology for Business - Winter 2022 - Assignment 3 Database Table Descriptions VOLUNTEERS Attribute Type Volunteer ID Number Volunteer_Last Name Short Text Volunteer_First_Name Short Text Length / Size Index Comments Long Integer Yes (No Duplicates) Primary Key 30 30 Use Expression Builder to concatenate the First and Last name values with a space in between them. Volunteer_Full_Name Calculated Volunteer Address Long Text Volunteer_Phone Short Text 15 Use the default Phone Number input mask. Monday Tuesday Wednesday Thursday Friday Saturday Sunday Yes/No Yes/No Yes/No Yes/No Yes/No Yes/No Yes/No RESIDENTS Attribute Type Comments Resident_ID Number Primary key Length / Size Index Yes / Unique/ No Long Integer Duplicates 30 30 Resident_Last_Name Resident First Name Short Text Short Text Resident_Full_Name Calculated Use Expression Builder to concatenate the First and Last name values with a space in between them Resident Address Long Text Resident_Phone Short Text 15 Use Default Phone Number Input Mask JOBS Attribute Type Length / Size Job_Number AutoNumber Long Integer Job Date Volunteer Resident Date/Time Number Number Long Integer Long Integer Index Format Comments Yes / Unique/ No Primary key Duplicates mm/dd/yy Foreign Key (Lookup-based) Foreign Key (Lookup-based) Medium Time Use Default Medium Time hh:mm AM/PM Input Mask Medium Time Use Default Medium Time hh:mm AM/PM Input Mask Time_Begin Date/Time Time_End Date/Time ADM 1370 - Applications of Information Technology for Business - Winter 2022 - Assignment 3 Part 1 - Creating Tables (20 points) Start Microsoft Access and create a Blank Database. At this point, a new blank table is available for you to use. Step A: Create the RESIDENTS Table (4 Points) 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 RESIDENTS table description. Make sure the Data Type pertaining to each Field Name is of the correct type. 4. Make sure that the Resident_ID field is also 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. You may want to also indicate this in the 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 "RESIDENTS". 3. Use the Description column for each field to reflect the type of information that goes in each of those fields (e.g. for "Resident_Address", you could state that this is this field captures the resident's primary address.) Now that you have one database object (your first table) you can save the database (File => Save As => Save Database As). When prompted for the filename, use "ADM1370_A03_G##_BCC.accdb". It is a good practice to keep saving your work at regular intervals. Please make sure to use the save icon (disk icon on the top left corner) after each step in the assignment. Step B: Create the VOLUNTEERS Table (4 Points) 1. Repeat the instructions from above, this time referring to the VOLUNTEERS table description. 2. Make sure you designate Volunteer_ID as the primary key and save your table as VOLUNTEERS when you are done with this step. Step C: Populate the RESIDENTS and VOLUNTEERS Tables (4 points) 1. Make up names and other pertinent 2. Populate the RESIDENTS and VOLUNTEERS information for 6 volunteers (including one tables with this sample data before proceeding group member) and 8 residents (include the to the next step. other group member). ADM 1370 - Applications of Information Technology for Business - Winter 2022 - Assignment 3 Step D: Create the JOBS Table (4 Points) 1. You will now need to create the JOBS table by repeating the prior steps while referring to the JOBS table description and the following instructions. Name your table as JOBS when prompted to save the table. 2. Make sure the Job_Number field is set to the AutoNumber Data Type and to designate this field as the primary key. 3. The Volunteer and Resident 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 under Data Type to configure these foreign key fields as outlined below: 3.1. In the Design View of the JOBS table, create a field named Resident. 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. Following the prior sequence of steps should produce a new field in the JOBS table such that every time a new record is being added, a drop- down menu appears with information being fetched from the RESIDENTS table. Once a particular Resident entry is selected, only the Resident_ID appears as the field value. 4. Repeat the steps above to create the Volunteer field in the JOBS table. This field should be based on a lookup from the VOLUNTEERS table. Volunteer_ID, Volunteer_Last_Name, and Volunteer_First_Name should appear in the drop-down menu of the field, and only the Volunteer_ID value should be stored in the field once a selection is made. 5. 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 JOBS table description. By using the Lookup Wizard in the JOBS table, we have already started to create relationships among the JOBS, RESIDENTS, and VOLUNTEER tables. Next, we will verify and refine these relationships. 3.3. Choose the RESIDENTS table from the list of tables and click Next. 3.4. Select the Resident_ID, Resident_Last_Name, and Resident_First_Name fields as your Selected Fields and click Next. 3.5. Sort the lookup values using Resident_Last_Name in Ascending order and click Next. Step E: Populate the JOBS Table (4 Points) 6. Add 10 different jobs to your newly created JOBS table, making up the dates and times. Create multiple jobs where you are the volunteer. Similarly, create multiple jobs where your group member is the resident. 3.6. Uncheck the Hide Key Column checkbox and resize the lookup columns so the field headers are visible. Click Next. 3.7. Select Resident_ID as the field value for storage in the JOBS table and click Next. 3.8. Confirm the name of the new field is "Resident" and click Finish