Lesson Blueprint - Access 2003 Expert Chapter 5, Pages ...



Lesson BlueprintSeries: BenchmarkApplication: Access 2010 Level 2Chapter: 2 – Building Relationships and Lookup FieldsThis chapter is taught in two sessionsPage number(s) in book: 35–46First lesson in Week 9 (Class 25)Learning Objectives:Design and edit relationships between tables including one-to-many, one-to-one, and many-to-many relationshipsDefine a table with a multiple-field primary keyEnd-of-Chapter ActivitiesSee BM-Access2010-L2-Session26 for end-of-chapter activity information for Access Level 2, Chapter 2.Estimated Times (Note: Times will vary depending on student ability and typing speed.)Lecture/Demonstration: 25–30 minutes - flexibleChapter Projects: 1–1? hours (homework time needed will vary depending on length of lecture/demonstration)Concepts Check: 20 minutesSkills Check Assessments: 45 minutesVisual Benchmark: 25 minutes Case Study: 45 minutes–1 hourStudent file(s) needed for chapter in order of occurrence: Projects:AL2-C2-RSRCompServ.accdbAssessments:AL2-C2-VantageVideos.accdbVisual Benchmark:AL2-C2-PawsParadise.accdbCase Study:AL2-C2-HillsdaleRealty.accdbEquipment Check: Following your established protocol, ensure that the students have copied the Access2010L2C2 folder.PowerPoint Slides Available: See the file named BM-Access2010-L2-C2-Presentation.pptx, Slides 1–19.SNAP Tutorials Available:2.1Creating a One-to-Many Relationship2.2Creating a Second One-to-Many Relationship2.3Editing Relationship Options2.4Establishing a Many-to-Many Relationship2.5Defining a Multiple-Field Primary KeyLecture/Demonstration Notes:This lesson explores relationships in more depth than Level 1. Students create all three types of relationships: one-to-many, many-to-many, and one-to-one.The model answer for Project 1 can be found on page 36.LESSON LEAD-IN DISCUSSIONSpend a few moments reviewing the concepts from the last lesson and ask for questions from the assigned work. Following are review questions:What does the format code > mean?What does the format code #[blue] mean?When would you use the Attachment field in a table?Relationships were introduced in Level 1, Chapter 2. Remind students that in Chapter 2 of Level 1, they learned how to create a one-to-many relationship and turn on relationship options. Ask students what is meant by the terms primary key and foreign key. Have students turn to pages 38 and 39 and review Figures 2.1 and 2.2. Write on the board the three types of relationships that can be created in Access: one-to-many, many-to-many, and one-to-one. If you have access to a computer connected to a video display projector, demonstrate the following tasks. If time is constrained due to previous discussion, focus the demonstration on the tasks preceded by an asterisk:*Have on your storage medium the student data file AL2-C2-RSRCompServ.accdb.*Open Microsoft Access and open AL2-C2-RSRCompServ.accdb. Enable the content.Open each table and review the fields.*Open the Relationships window and review creating a one-to-many relationship.Add the Customers and WorkOrders tables to the window.Resize and move the field list boxes as shown below.Drag CustID the Customers field list box to CustID in the WorkOrders list box. Reinforce that you drag from the primary table to the linking field in the related table.With the Edit Relationships dialog box open, point out that Customers appears in the Table/Query list box and WorkOrders appears in the Related Table/Query list box and that the type of relationship at the bottom of the dialog box is One-To-Many. Turn on all three relationship options and click Create. If necessary, reiterate the importance of referential integrity.Save the relationships.*Demonstrate creating a one-to-one relationship as follows:Click the Show Table button and then add the ServiceContracts table to the window.Resize and move the field list boxes as shown below.Point out that primary key fields are displayed with a key in each table.Next, point out that CustID is the primary field in both the ServiceContracts and Customers tables.Drag CustID from the ServiceContracts field list box to CustID in the Customers field list box.Click Create. (Do not turn on any relationship options.)Explain that since the field used to join the tables is the primary key in both tables, Access knows the relationship type is one-to-one.Save the relationships.Add the Technicians table and create the relationship shown below with the TechID field.You realize you forgot to enforce referential integrity and add the two cascade options. To edit the relationship, double-click the black join line between the tables. Make the necessary changes. The correct relationships are shown below.*Demonstrate creating a many-to-many relationship as follows:Point out that the infinity symbol at the right side of the join line between Customers and WorkOrders and also at the left side of the join line between WorkOrders and Technicians indicates the “many” side of each of the relationships; therefore, Customers and Technicians are joined by many-to-many.In other words explain that customer may have “many” work orders and technicians may work on “many” orders. Save the relationships.*Display a relationships report (click the Relationship Report button in the Tools group).*Change the orientation to landscape.Discuss that printing a hard copy of the defined relationships is a good idea to keep for reference.*Close the relationships report. Click Yes to save the report and accept the default name.*Close the Relationships window.Point out that the relationships report is added to the Navigation bar as an unrelated option.Ask the students how many of them have more than one computer at home or at work. Explain that each computer can have a different profile for each user name. In order to create a unique identifier for each record, three fields will have to be used to create the primary key.*Add a new table called Profile as shown below.*Currently CustID is set as the primary key. Select the CustID field and hold down the Shift key and select Username (use Ctrl for selecting nonadjacent fields). Click on the Primary Key button. Point out that keys now exist beside each of the three fields. Save the table.*Close AL2-C2-RSRCompServ.accdb. (Note: This file will be used in the next lesson.)WRAP-UP CLASS DISCUSSION TOPICS – depending on time available, consider the following topic: Remind students that good practice in database design involves creating smaller tables organized into logical units with relationships between the tables rather than large tables with many fields grouped together. Smaller tables are faster and easier to work within. By relating tables, you can work within queries to pull data from multiple tables into one datasheet. The goals in database design are to avoid data redundancy and retain data integrity.Extra Tips for Students:Repeat as often as necessary—drag the common field from the primary table to the related table!In order to enforce referential integrity, the primary key and foreign key fields must be the same data type. If you receive an error message, open both tables and review the data type.Possible Work for Advanced Students:Have students come up with another example of a many-to-many relationship. Describe the two tables and then decide what fields would be needed in the junction table.Individual Notes:(Instructor can record own notes here.) ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download