Lesson Blueprint - Access 2003 Specialist Chapter 2, Pages ...



Lesson BlueprintSeries: BenchmarkApplication: Access 2010 Level 1Chapter: 2 – Creating Relationships between TablesChapter is taught in one sessionPage number(s) in book: 43–77Second lesson in Week 2 (Class 5)Learning Objectives:Define a primary key in a tableCreate a one-to-many and a one-to-one-relationshipSpecify referential integrityPrint, edit, and delete relationshipsView and edit a subdatasheetEnd-of-Chapter ActivitiesConcepts Check (Pages 69–70)Twelve short answer questionsSkills Check (Begins on page 70)Assessment 1Open and define a primary key. Create the given relationships. Save and print the relationships.Assessment 2Create a new table and relate the table to another. Save and print the relationships.Assessment 3Delete and edit records in a table using the cascade options. Print the tables.Assessment 4Display and edit records using a subdatasheet. Save and print the tables.Visual Benchmark (Pages 73–74)Create the Agents table shown in Figure 2.15. Determine the data type, field sizes, captions, and descriptions. Input the data. Save and print the table. Establish the relationships shown. Make the required changes and print the tables.Case Study (Begins on page 74)Part 1Create a new database and the required tables. Determine the data type, field sizes, captions, and descriptions. Input the data. Adjust the column widths and print each table on one page.Part 2Establish the relationships for the database created in Part 1. Print the relationship report.Part 3Make the requested changes to the tables. Save and print the tables.Estimated Times (Note: Times will vary depending on student ability and typing speed.)Lecture/Demonstration: 30 minutes - flexibleChapter Projects: 1 hour (homework time needed will vary depending on length of lecture/demonstration)Concepts Check: 35 minutesSkills Check Assessments: 30–45 minutesVisual Benchmark: 30 minutesCase Study: 1 hourStudent file(s) needed for chapter in order of occurrence: Projects:AL1-C2-PacTrek.accdbAL1-C2-Griffin.accdbAssessments:AL1-C2-CopperState.accdbEquipment Check: On page 43, students are instructed to copy the Access2010L1C2 folder to their storage medium. This routine will be repeated for each chapter. Establish the protocol you want to use for file retention for grading and/or backup purposes and inform the class of your policy. For example, you may decide that you want students to retain all files until after the unit or test is complete. Post your policy in writing on your course website or in other prominent locations so that students are clear on what to do with the data files.PowerPoint Slides Available: See the file named BM-Access2010-L1-C2-Presentation.pptx, Slides 1–34. SNAP Tutorials Available:2.1Creating a Relationship between Two Tables in a Database2.2Printing, Editing, and Deleting RelationshipsLecture/Demonstration Notes:This lesson introduces primary key and foreign key concepts and the process of establishing relationships between tables.Model answers for Projects 1 and 2 can be found on pages 44 and 45.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:Even though Access allows you to put spaces in field names, why should field names not include them?When should you add a caption?Why should you change field sizes?Access is referred to as a relational database management system, meaning it is possible to join two tables based on a common field. Tables are joined in what is called a relationship. Relationships allow you to display or otherwise extract information from multiple tables by treating the related tables as if they were one. Displaying or extracting the data can be done in a form, query, or report. Access provides for three types of relationships: one-to-many, one-to-one, and many-to-many. Draw a diagram on the board similar to the one shown below to depict how data from two tables can be “pulled” together. To accomplish this, a common field resides in each related table. Tell students the type of relationship shown below is a one-to-one relationship.Related TablesIn order to extract data from the fields EmpNo, LastName, Dental, and Pension, the tables Employees and Benefits are related using EmpNo as the common field to join them. In this example, the type of relationship created is 1:1 (one-to-one) since only one record would exist in each table for one employee.The ability to relate tables and pull information from multiple tables means data redundancy—storing the same information in more than one table—can be avoided. In the above example, the employee’s name is only required in the Employees table.A database can contain two different types of keys—a primary key and a foreign key. A primary key uniquely identifies each record. No two records in the same table can have the same primary key. We don’t want two employees to have the same EmpNo as this may cause major problems when it comes to paying the employees. Ask for examples of other types of data that could be set as a primary key. A few examples are: student numbers, social security numbers or social insurance numbers, driver’s license numbers, and passport numbers. Tell students that in this chapter they learn how to set primary keys and create one-to-one and one-to-many relationships.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 AL1-C2-PacTrek.accdb. *Open Microsoft Access, open the AL1-C2-PacTrek database, and enable the content.*Open each table and review the fields and records.*Explain these primary key concepts:One field in a table must be unique so that one record can be distinguished from another—such as an ID field (customer, student, item number).Once a primary key is defined, Access enforces a rule in which no two records can have the same data in the primary key field.Once a primary key is defined, Access does not allow the field to be left blank when a new record is added.Unless otherwise sorted, a table is automatically displayed in ascending order by the data in the primary key field.*Open the Orders table and ask students why Order# should be set as the primary key based on the primary key concepts. Set Order# as the primary key.*Open the Relationships window (Database Tools tab/Relationships button in the Relationships group).*Demonstrate creating a one-to-many relationship between the Orders table, Products table, and Courses table as follows:Add all three tables to the Relationships window and then close the Show Table dialog box.Explain that in the Orders table, Product# is considered a foreign key because it is the common field that will be used to create the relationship and its corresponding field in the Products table is the primary key.Make sure students understand which table will be the “one” side of the relationship (Products) and which table will be the “many” side of the relationship (Orders).Drag Product# from the Products table to Product# in the Orders table. Explain that you drag starting from the “one” table and release the mouse when pointing to the common field name in the “many” table.At the Edit Relationships dialog box, turn on all three relationship options: Enforce Referential Integrity, Cascade Update Related Fields, and Cascade Delete Related Records.Click Create.*Spend a few moments discussing the meaning of:Referential integrityCascade Update Related FieldsCascade Delete Related Records*Save and close the Relationships window.Open the Products table in Datasheet view. Point out the expand buttons next to each record before the first field. Mention that now that a relationship has been created between Products and Orders, clicking the expand button next to a record causes the related records to display in a subdatasheet. Click the expand button next to the record for Product #100-02.*Close the Products table.*Tell students you want to test the referential integrity option. To do so, you want to try to add a record to Orders using a Product# that does not exist in the Products table as follows:Open the Orders table.Add the information shown below to the blank record at the bottom of the table and then attempt to press Tab after OrderDate.Click OK at the error message that displays. Allow students time to read the entire message text and make sure the content of the message is understood.Close the Orders table. Access redisplays the error message. Click OK. Click Yes to close the object when prompted that the record cannot be saved.*Demonstrate the Cascade Update Related Fields option as follows:Open the Products table.Edit the Product# field as shown below.Close the Products table.Open the Orders table and point out the Product# for Order #1007 is automatically changed to correspond to the new value in the Product# field.Data in related records is automatically updatedClose the Orders table.Demonstrate the Cascade Delete Related Records option as follows:Open the Products table.Delete the record for Product# 999-zz.Close the Products table.Open the Orders table. Point out the records for the orders related to Product #999-zz have been automatically deleted.Close the Orders table.*Open the Relationships window. Explain that a relationship can be deleted or the relationship options can be edited by right-clicking the join line and selecting either Edit Relationship or Delete at the shortcut menu.*Demonstrate how to create and print a relationship by creating a relationship report. Click the Relationship Report button in the Tools group. Close the print preview.*Close the Relationships window.*Close the PacTrek database. (Note: This file will be used in the next lesson.)WRAP-UP CLASS DISCUSSION TOPICS – depending on time available, consider the following topic: Now that students have learned how to create relationships, spend a few moments discussing database design. Breaking data into logical groups of related tables is a better design methodology than having one large table with all fields lumped together. Smaller tables are easier to manage and edit. No data should be duplicated in more than one table with the exception of the common field used to create the relationship.If time permits, use the example of an equipment rental business. Ask students to suggest fields that should be included in a Customers table and fields that should be included in a Rentals table. List the fields on the board and circle the field that would be the primary key in each table. Make sure each table contains a primary key field. Ask students to identify the foreign key in the Rentals table. Next, ask students to consider the type of relationship that would exist between Customers and Rentals. Draw the relationship diagram shown below. Explain that the diagram is a standard tool used by database designers to show relationships between tables when planning and designing a new database. Note the primary (one) table is drawn at the left of a relationship diagram. The notation 1:N inside the diamond shape is the standard used to denote a one-to-many relationship.Extra Tips for Students:Reinforce that when creating a relationship you begin the operation by dragging the common field from the primary table. Students often don’t consider which table should be the primary table and just drag a common field from one to another. If you want to enforce referential integrity, the primary table becomes fundamental to the relationship.If students are still unclear about relationships, have a few extra examples of pairs of tables and ask what type of relationship should be created and which table becomes the primary table. (Note: At this level you can focus on the relationship type only.)Customer table and Invoice tableVideos table and Sales table Authors table and Books tableCustomer table and Customer Credit Limit tableMention that one-to-many relationships are the most common relationship type as indicated in the extra examples.Possible Work for Advanced Students:Now that students have learned how tables can be related, revisit the database created in the last lesson and have students define any relationships that can be created between tables.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