digital video lessoncurriculum.naf.org/packaged/assets/downloads/technolo… · web viewlesson 9....

AOIT Database Design

Lesson 9Creating a Relational Database

In this lesson, students create a relational database for their culminating project using Microsoft Access. They learn the best practices for naming conventions for both tables and fields, plus the importance of observing naming conventions in a database. As students create relationships between their entities, they learn the concepts behind the different types of data integrity, including entity integrity, referential integrity, check constraints, and validation rules. Students also learn how to import data into a database from other Access tables, from Microsoft Excel, or from text files. During the lesson, students apply all of what they learn to their culminating project database.

Advance Preparation You will need to get sample data from all culminating project clients so that students can import a

sample of data into their database. (They import the data in Class Period 9.) Ideally, the sample should include about 50 items and be delivered in some electronic format, preferably either an Excel file or a text file. The lesson explains how to import data in these formats. If the data from your client is in another format, you will need to determine the best way for students to import it into their databases.

The instructions and tutorials for using Microsoft Access are for Access 2013. If your students are using Access 2007 or 2010, they may encounter some slight differences in navigation.

This lesson is expected to take 10 class periods.

Lesson FrameworkLearning ObjectivesEach student will:

Identify the structural elements of a relational database table Define best practices for entity and attribute naming conventions Create a relational database using Microsoft Access Demonstrate the ability to add data to tables Demonstrate the ability to verify relationships between keys Describe what data integrity refers to and list some constraints Demonstrate the ability to establish referential integrity rules for a database

Copyright © 2009–2014 NAF. All rights reserved.

AOIT Database DesignLesson 9 Creating a Relational Database

Demonstrate the ability to establish field-level and row-level rules for a database, including optional attributes

Academic StandardsThe relevant Common Core State Standards are too extensive to list here but are an important basis for this lesson. For details, please refer to the separate document “Correlations to the Common Core Standards” (available in the Course Planning Tools section of the course materials).

Use product or service design processes and guidelines to produce a quality information technology (IT) product or service (Common Career Technical Core 2012, IT 2)

Demonstrate the use of cross-functional teams in achieving IT project goals (Common Career Technical Core 2012, IT 3)

Design, create and maintain a database (Common Career Technical Core 2012, IT-PRG 10) Be able to modify worksheet data, structure and formatting (IC3 GS4 2012, Key Applications) Be able to sort data, manipulate data using formulas and functions, and create simple charts (IC3

GS4 2012, Key Applications) Plan and manage activities to develop a solution or complete a project (ISTE NETS for Students

2007, 4b) Transfer current knowledge to learning of new technologies (ISTE NETS for Students 2007, 6d)

AssessmentAssessment Product Means of Assessment

Relational database package, including list of naming conventions, relational diagram printout, and relational database file

Rubric: Relational Database (Teacher Resource 9.1)

Prerequisites Working knowledge of using primary keys as unique identifiers for an entity Working knowledge of foreign keys and relationships Basic understanding of how to use Access to create tables Each culminating project group needs an accurate entity-relationship model

Instructional Materials Teacher Resources

Teacher Resource 9.1, Rubric: Relational Database Teacher Resource 9.2, Guide: Setting Up Tables in Access Teacher Resource 9.3, Sample Access, Excel, and Text Files: DolphinDemo.accdb,

DolphinTables.accdb, Categories.xls, and Suppliers.txt (separate ZIP file) Teacher Resource 9.4, Presentation and Notes: Data Integrity (includes separate PowerPoint file)

Copyright © 2009–2014 NAF. All rights reserved. 2


Teacher Resource 9.5, Answer Key: Validation Rules Worksheet Teacher Resource 9.6, Key Vocabulary: Creating a Relational Database Teacher Resource 9.7, Bibliography: Creating a Relational Database

Student Resources Student Resource 9.1, Scenario: Naming Conventions Student Resource 9.2, Checklist: Relational Database Student Resource 9.3, Reference: Naming Conventions Student Resource 9.4, Note Taking: Data Integrity Student Resource 9.5, Reading: Data Integrity Student Resource 9.6, Defining Format: Data Integrity Student Resource 9.7, Tutorial: Using the Relationship Tool to Specify Foreign Keys in Access Student Resource 9.8, Procedural Steps: Setting Up Keys and Relationships in Access Student Resource 9.9, Worksheet: Validation Rules Student Resource 9.10, Reference: Syntax for Common Validation Rules Student Resource 9.11, Culminating Project Work: Setting Field Properties and Enforcing

Business Rules Student Resource 9.12, Tutorial: Importing Data Using the Import Wizard

Equipment and Supplies LCD projector connected to a computer Software to be used for drawing entity-relationship models, for all student computers Microsoft Access for all students Printer available for student use Blackboard, whiteboard, or flip chart Sticky notes

Lesson StepsStep Min. Activity

CLASS PERIOD 1

1 15 Scenario: Naming Conventions



Step Min. Activity

This activity helps students understand why they need to use strict naming conventions for their entities and attributes.

Explain that in this lesson, students will be setting up their culminating project database in Access and that working on the actual database requires a lot of precision. There is no room for error: even small errors will cause the database to not work correctly and will be a source of frustration for the client. Explain that the first step in creating the database will be to establish the naming conventions they will adhere to.

Refer students to Student Resource 9.1, Scenario: Naming Conventions. Look at the list of naming conventions together, and ask students to suggest reasons why naming conventions are important. Make sure that they note the following:

Naming conventions help promote consistency. Naming conventions prevent the creation of duplicate fields for the same attribute. Using standard naming conventions promotes better understanding of the data

stored in the database.

Instruct students to work in pairs to complete the tables on the resource. When they have completed their work, go over the answers as a class and answer any questions students might have.

Tell students they will have a chance to set up their own naming conventions shortly.

2 10 Culminating Project Work: Elements of an Effective Relational DatabaseThis activity gives students an overview of the steps they will be going through in this lesson to create their culminating project relational database, and it provides them with tools to track their progress.

Distribute the rubric in Teacher Resource 9.1, Rubric: Relational Database. Explain to students that this rubric will be used to assess all the work they do during this lesson to create their culminating project relational database. Tell them that you will be reviewing each line of the rubric with them before they start working on that particular aspect of the database. Ask them to point out what makes this rubric different from the other assessment criteria they have seen in this class, and answer any questions they might have. Tell students to keep this rubric in their notebook so that they can refer to it whenever they are doing project work.

Next, refer students to Student Resource 9.2, Checklist: Relational Database. Explain that this checklist is designed to help them track their work throughout the lesson. When they complete an item, they should check it off. If they have questions or concerns about an item, they should note them so that they can find an answer themselves, get help from their peers if necessary, inform their group of an item of concern, or get help from the teacher when they have exhausted other possibilities. Answer any questions, and tell students they will learn more about the line items as they work through the project. Instruct students to keep this resource in their notebook so that they can use it throughout the project.

Tell students that tracking their work during a project and making sure that all elements are completed on time and with high quality is a professional skill that will help them succeed in the work world.



Step Min. Activity

3 25 Culminating Project Work: Establishing Naming ConventionsIn this activity, students define naming conventions for the entities and attributes of their culminating project database.

Refer students to Student Resource 9.3, Reference: Naming Conventions, and tell them they should adhere to the rules and guidelines in this reference when they establish the naming conventions for their culminating project database. Explain that these are the guidelines most often used by database professionals, and it is good practice to make their databases adhere to these professional naming convention guidelines. Discuss the difference between rules and guidelines, and tell them to use the guidelines as a basis for establishing rules for their database. For the database project, they need to have strict rules (not just guidelines) that everyone in the group adheres to.

Instruct students to read through the reference in their group, and then to create a list of naming conventions for their database on a separate sheet of paper. This page should go in their culminating project portfolio.

Before students begin work, go over the line in the rubric (Teacher Resource 9.1, Rubric: Relational Database) that relates to naming conventions, and answer any questions. Circulate as students are working, and answer any questions they have.

Ask students to submit their naming conventions for your review before they proceed with their culminating project database. (You will need to give students your feedback on the naming conventions by next class period.) If they have not finished, they should finish the conventions for homework.

Tell students that professional database developers consider effective naming conventions an extremely important aspect of database development. All of the subsequent work hinges on the coherency of the naming conventions.

CLASS PERIOD 2

4 25 Guided Practice: The Structure of Tables in AccessThis activity ensures that all students are clear on how to enter tables in Access correctly in Design View, how to import tables, and how to specify data types.

Using an LCD projector connected to a computer, demonstrate the following:

How to set up tables in Access and specify a data type for each attribute How to import tables into an Access databaseRefer to Teacher Resource 9.2, Guide: Setting Up Tables in Access, for notes on what to include in the demonstration. For the demonstration, use the two Access files, DolphinDemo.accdb and DolphinTables.accdb, found in Teacher Resource 9.3, Sample Access, Excel, and Text Files (separate ZIP file). All tutorials and images feature Access 2013. If you are using an earlier version of Access, there are only minor differences in appearance and navigation.

Instruct students to work in pairs and follow along on their own computer during the demo. Each pair should have access to the two sample database files on their computer.

Tell students that in the next activity, they will be creating the tables in their culminating



Step Min. Activity

project database and that what they have learned during this practice should serve to guide them.

5 25 Culminating Project Work: Tables and Naming ConventionsIn this activity, students create tables in Access for their culminating project database, adhering to the naming conventions that they have established.

Note: Before beginning this activity, you will need to give students your feedback on the naming conventions they submitted to you in Class Period 1.

Tell students that they will be creating the tables in their culminating project database during this activity. Before students begin work, review the lines in the rubric (Teacher Resource 9.1, Rubric: Relational Database) that deal with creating tables and applying naming conventions correctly.

Working in their culminating project groups, have students create all of the tables for their culminating project database in Access. Tell them that they can make use of any tables they have already created in Access, but they must ensure that all names adhere to their naming conventions, all columns are correct, and all data types are correct.

Tell students that they should each work on one or two tables from their database and then import them all into the project database. Remind students that they need to confer with each other about any names that they change, particularly for any foreign keys that appear in more than one table.

Tell students that they will have time at the beginning of the next class period to complete this exercise, but they should do as much as they can today so that they will have time to import the tables and check their work next class period.

CLASS PERIOD 3

6 25 Culminating Project Work: Tables and Naming Conventions (Continued)In this activity, students finish creating their culminating project tables in Access.

Instruct students to complete their tables in Access. Circulate while students are working, and answer any questions they may have.

When students have completed all of their tables and imported them into their project database, tell students to go through them as a group to make sure all attributes adhere to their naming conventions and all data types are specified correctly. Tell students that they need to have all of their tables accurate before they can go on to the next step of setting up keys and defining relationships between their tables.

7 25 Presentation: Data IntegrityThis activity introduces students to data integrity, primary key constraints, and referential integrity.

Before class begins, prepare notes to guide class discussion using Teacher Resource 9.4, Presentation Notes: Data Integrity.



Step Min. Activity

Refer students to Student Resource 9.4, Note Taking: Data Integrity, and instruct them to use this resource to take notes during the presentation.

Present Teacher Resource 9.4, Presentation: Data Integrity (separate PowerPoint file), to the class. Use the notes you prepared previously and pause whenever appropriate to encourage lively class discussion.

The preferred method is to use an LCD projector to show the presentation as a full-screen PowerPoint slideshow. Activate the slideshow by clicking the slideshow icon at the bottom of the window. Every time you click the mouse, the next slide will appear.

This presentation is also duplicated as Student Resource 9.5, Reading: Data Integrity. Students can read the presentation, answer the discussion questions in their notebook, and then convene as a class to discuss the answers. You can also have students use the reading as a review after you show the presentation.

After the presentation, have students compare their notes for completeness and accuracy in pairs. Then have them report out as a class, and answer any outstanding questions students may have.

Tells students they will be applying the concepts they have just learned to their culminating project database.

CLASS PERIOD 4

8 25 Defining Format: Data Integrity TermsThis activity helps students clarify the definitions of the terms they are learning about in this lesson.

Refer students to Student Resource 9.6, Defining Format: Data Integrity. Tell them to use all of their notes and readings to help them independently fill in the categories and characteristics of the terms listed.

After about 15 minutes, instruct students to leave their chart on their desk. Give each student some sticky notes, and instruct them to review at least three different charts of their peers and leave sticky notes commenting about categories or characteristics they like and anything they have questions about.

To conclude the activity, go through each word, and ask students to share the categories and then characteristics that they thought were particularly meaningful. Tell students to make any changes they would like to make to their own chart and then to put it in their notebook and put an entry in the table of contents. If necessary, they can complete this for homework.

Impress upon students the importance of being conversant with these terms. They need to understand these terms in order to do effective work on their client’s database.

9 25 Guided Practice: Setting Up Keys and Relationships between Tables in AccessThe purpose of this activity is to explain how to set up foreign keys and relationships in Access.



Step Min. Activity

Using an LCD projector, open the dolphin database file (DolphinDemo.accdb), into which you have imported all tables. Have students open this database on their own computers also. Refer students to Student Resource 9.7, Tutorial: Using the Relationship Tool to Specify Foreign Keys in Access. Explain to students that you will be demonstrating the steps in the tutorial as they follow along in the tutorial guide and on their computer. Tell them they will be using this guide as a resource to set up the foreign keys and relationships in their culminating project database. This practice run with the dolphin database will prepare them to work on their own.

Show students how to access the Relationships window in Access. Demonstrate the following actions, which are annotated in the tutorial:

Setting up relationships with pointers to foreign keys and referential integrity Using the symbols that Access provides to denote the types of relationships (1:N

and so on) Moving tables around the display area to make the diagram easier to view

When you have completed the demo, tell students to leave their relationship diagrams on their computers and to walk around the class and look at the diagrams of three other students, leaving sticky notes to indicate any questions, suggestions, or corrections they have for their peers. Finally, answer any questions students may have.

To gauge student understanding, you may find it useful to collect and mark this assignment for credit/no credit.

Students are now prepared to set up the relationships in their culminating project database.

CLASS PERIOD 5

10 25 Culminating Project Work: Setting Up Relationships in AccessIn this activity, students set up the keys and relationships in their culminating project database.

Tell students to sit in their culminating project groups and open their culminating project database. Explain that in this activity, they will be creating keys and setting up relationships in their database. Refer students to Student Resource 9.8, Procedural Steps: Setting Up Keys and Relationships in Access. Go over the instructions with them, and tell them to use the checklist to make sure they complete all the necessary steps. Refer students to the items in the rubric (Teacher Resource 9.1, Rubric: Relational Database) concerning keys and relationships, and make sure they understand how these items will be assessed.

Circulate and make sure students are on track. Instruct students to print out the relationships diagram and put it in their project portfolio. This will be submitted as part of their assessment package.

If necessary, have students finish this as homework, and help them figure out a solution for working on their database outside of the classroom.

Tell students they now have the basics of their database established; next, they will move on to the fine-tuning aspects that ensure the data entered in the database is



Step Min. Activity

coherent and makes sense.

11 25 Guided Practice: Using Check Constraints to Determine the Validity of DataThis activity introduces students to data integrity considerations based on check constraints.

Write the following term on the board:

Check constraints

Explain that part of data integrity is ensuring that the data entered in a database is valid, and DBMSs such as Access use check constraints to do this. In Access, the DBMS uses check constraints to check whether data meets the criteria specified in the field properties for each column. For example, the DBMS can check to make sure:

That every customer has a name That telephone numbers have the right number of digits That the quantity of items a person orders is a whole number

Ask students to think of another example of something a DBMS might check and tell it to a partner, and then have volunteers share their answers.

Tell students the idea of check constraints will become clearer when they look at the field properties of a database. Ask students to open the dolphin database (DolphinDemo.accdb). Tell them you will be looking at the following tables:

Customers Products Orders

To begin, display the Customers table in Datasheet View on an LCD projector, and tell students to follow along on their own computers, working in pairs. Ask students to enter what they predict would be the correct field properties for each field in the Customers table. Tell them to ignore the Validation and Validation Text fields for now; you will be addressing them in the next activity. Explain that there are no specific right answers for what should be specified. For example, they may want to have a very specific field length for a phone number, but for a name, they just need to decide whether they should use the maximum field length or something shorter.

After students have had about five minutes to work with their partner, instruct them to test the impact of each property they set on a field. For example, if they set a field as required or if they set a maximum length or default, they should try entering some data so that they can see what happens if they skip that field or try to enter more characters than the maximum specified. Seeing the resulting Access behavior immediately after they set up the constraints for a field should help students understand what they are doing.

Next, move on to the Products table, and then to the Orders table, proceeding in the same manner. As you work through the tables, be sure to cover the following:

Point out the possible formats of items such as dates, currency, numbers, and decimal places.

Ask students to come up with examples of when a default value would be useful.



Step Min. Activity

Discuss the implications of setting Required to Yes or No.

When you have gone through the three tables, tell students that they will be doing this same type of work in their culminating project database. Tell them to think about their culminating project database and ask questions that come to mind about how they should specify field properties.

CLASS PERIOD 6

12 15 Think, Pair, Share: Enforcing Business RulesThis activity prepares students to read about validation rules.

Explain that many business rules are enforced by creating limits, or constraints, on the data that can be entered in certain fields. Write these business rules on the board:

All customers must reside in the continental United States. All prices are expressed in US dollars only. All orders must be shipped within three days of when they are received. Overnight orders must be shipped in one day.

Ask students to try to figure out what fields they would limit and how they would limit them to make sure the business rule is enforced. Have students write their answers in their notebook and then compare their answers with a partner. After the pairs have had some time to work, ask for volunteers, and write some possible answers on the board.

Explain that in Access, you can establish constraints by setting validation rules to ensure that only valid data that adheres to the business rules is entered in a database.

13 35 Reading: Access Help on Validation RulesThis activity introduces students to the Help feature of Microsoft Access and provides them with an overview of how validation rules work in Access.

Tell students that they will learn how to set up validation rules by reading about them in the Access Help files. Explain to students that being able to use help files to find the information they need is a skill they will need when working with Access. It is also a useful professional skill, because it makes them more autonomous and able to work without direct supervision. Tell them that working with Access Help is known to be difficult, and mastering it is a professional skill they could mention in a job interview.

To begin, have students open the dolphin database (DolphinDemo.accdb), which they will be using as they work through Access Help. Next, help students navigate to the help file on validation rules:

In Access, press F1 to display Access Help. On the home page for Access Help, type validation rule in the search field and click

the search button. Select the first article, “Restrict Data Input by Using a Validation Rule.”

If you are using an earlier version of Access:



Step Min. Activity

In Access, press F1 to display Access Help. On the home page for Access Help, select Database Design. On the Database Design page, select “Create a validation rule to validate data in a

field.”

Explain that Access has organized the help files according to topics, and the best way to find what they are looking for is to click the topic that seems closest to what they are looking for. Warn them that this may require some trial and error until they become more familiar with the Access Help system.

Refer students to Student Resource 9.9, Worksheet: Validation Rules, and tell them to answer the questions and solve the problems on the worksheet as they go through the help files. Tell them that for the last part of the worksheet, they will need to work in the dolphin database.

Circulate as students are working to make sure everyone is on track.

When students are finished, have them share their worksheet answers with a partner to check for accuracy and completeness, and then go over the worksheet as a class. Use Teacher Resource 9.5, Answer Key: Validation Rules Worksheet, as necessary.

Ask students what they liked about working with Access Help. Do they think being able to use Access Help makes them more independent? Encourage them to try to use Access Help when they have a question.

CLASS PERIOD 7

14 20 Independent Practice: Building Expressions for Validation Rules in AccessIn this activity, students get practice using the Expression Builder in Access to create validation rules, and they learn the meanings of the various symbols used in validation rules.

Refer students to Student Resource 9.10, Reference: Syntax for Common Validation Rules. Instruct students to go through the chart with a partner and underline any expressions that are new to them or that they have questions about. Then go through the expressions as a class, and answer any questions that students have.

Next, tell students to open the dolphin database they have been using in this lesson.

Write the following business rules for the dolphin database on the board:

Customers live in the United States and Canada only. Customers can purchase as many products as they want. Ship dates must be after order dates. All merchandise prices must be $3 or more. No more than 15 units can be in stock of any one product.

Tell students to work in pairs and use the Expression Builder in Access to build validation rules that enforce these business rules.

Instruct students to also write validation text for each rule, which explains what the rule is doing. And, as a final step, tell them to test out their validation rules in the dolphin



Step Min. Activity

database by entering some additional data to see whether the rule has the desired effect. Explain that the beauty of working with database development tools like Access is the chance to get almost instant feedback on whatever you do; that’s what keeps a lot of database developers motivated when using these tools for hours and days on end.

After they have finished and tested their validation rules, have pairs share their work with another pair.

Finally, review the rules and text as a class. Ask several students to share their validation text, and write it on the board. Ask students to point out what they see in the different pieces of validation text that makes them clear and concise.

Ask students whether they think they are ready to write validation rules for their culminating project database. Discuss any questions or concerns before they move on to the culminating project work.

15 30 Culminating Project Work: Specifying Field Properties to Ensure Data IntegrityIn this activity, students review the business rules for their culminating project database and set field properties and validation rules that ensure data integrity. This activity focuses on the following career skills:

Demonstrating teamwork and collaboration Utilizing time efficiently when managing complex tasks

Refer students to Student Resource 9.11, Culminating Project Work: Setting Field Properties and Enforcing Business Rules. Tell students that in this activity, they will use all that they have learned about data integrity, check constraints, field properties, and validation rules to enforce data integrity in their culminating project database. Before students begin work, review the lines in the rubric (Teacher Resource 9.1, Rubric: Relational Database) that are related to data integrity, and answer any questions. Tell students to follow the instructions in the student resource to complete this activity. Remind them that they will be submitting this resource, along with their database, as part of the assessment package.

Circulate to make sure students are making progress. Explain that in the next class period, they will have about 25 minutes to complete their validation rules and check their work, and then they will present their database designs and explain their rules in front of the class.

CLASS PERIOD 8

16 50 Culminating Project Work: Specifying Field Properties to Ensure Data Integrity (Continued)In this activity, students complete their validation rules and then present their database designs to the class. They explain the validation rules they used and why, and then make any final changes to their database and submit it for assessment.

Give students about 25 minutes to complete and test their validation rules, and then instruct each group to take 5 minutes to put the table in step 3 of their worksheet



Step Min. Activity

(Student Resource 9.11, Culminating Project Work: Setting Field Properties and Enforcing Business Rules) on chart paper and post it on the wall. Then, ask each group to take a turn explaining to the class the validation rules they used and why, using their chart as a visual aid. Let other students ask questions after each presentation, and clear up any misunderstandings.

After all groups have presented, tell students they have a few minutes to make any corrections based on input from their peers—or, if necessary, they should finish all corrections for homework. Explain that they will be importing client data into their database in the next class period, so their database has to be complete in every respect.

CLASS PERIOD 9

17 25 Tutorial: Importing Data Using the Import WizardIn this activity, students learn how to prepare data and import it into a database.

Explain to students that there are essentially four ways to enter data into their culminating project database:

Add records directly to a table in Datasheet View Add records by importing data from another file such as an Excel workbook or

another spreadsheet application, a Word table, another Access database, or a text file

Add records by using a Microsoft Access form Add records by using a form within a web-based application

Tell students that they already have experience entering data directly into a table; in this lesson, they will learn to import data into the correct tables using the Import Wizard. In the next lesson, they will create forms and add data using those forms.

Explain that before importing data into their database, they need to prepare the data to ensure that it matches the columns of their database and to make sure it complies with all field properties and validation rules.

Refer students to Student Resource 9.12, Tutorial: Importing Data Using the Import Wizard. Also, give students access to Categories.xls, Suppliers.txt, and DolphinDemo.accdb (into which the tables from DolphinTables.accdb were imported in Class Period 2).

Ask students to work in pairs, and go through the first section of the tutorial, “Preparing Your Data for Import,” noting any bullets that apply to the data they will import from Categories.xls and Suppliers.txt. Give them about 10 minutes to look at the Categories and Suppliers tables in the database and the two files to be imported, and to analyze any problems that need to be resolved. In reality, these two tables are ready for import, so they shouldn’t see any problems, but the only way to know that is to do the analysis. Take some time to help the students handle what they find in this preparatory stage, and emphasize that they are likely to find more issues when they are importing their own client’s data in the next activity.

Next, using an LCD projector and following the instructions in the tutorial, demonstrate how to import an Excel file. After you finish the demonstration, ask students to work in



Step Min. Activity

pairs and follow the tutorial to import the Excel file into the dolphin database on their own. Circulate as they are working, and answer any questions they may have.

When students have successfully imported the Excel file, demonstrate how to import the text file, again using an LCD projector and following the instructions in the tutorial. As part of the demonstration, explain the difference between delimited and fixed-width fields. After you finish the demonstration, instruct students to continue working with their partner and to follow the tutorial to import the text file into the dolphin database on their own.

When all pairs are finished, ask them to share any part of the import process they found difficult, as well as any tips and tricks they learned along the way.

To gauge student understanding, you may want to mark their completed work for credit/no credit.

Tell students that they will apply what they have learned in this activity when they import data into their culminating project database.

18 25 Culminating Project Work: Importing Client DataIn this activity, students import client data into the tables of their culminating project database. This activity focuses on the following career skills:

Thinking critically and systemically to solve difficult problems Demonstrating initiative and resourcefulness in challenging situationsGive students access to the client’s Excel or text file that contains a sample of client data, and tell them that in this activity, they will use the skills they have acquired in the tutorial activity to import this data into their culminating project database. Review the section of the rubric (Teacher Resource 9.1, Rubric: Relational Database) that deals with importing data, and answer any questions that students have.

Note: Your approach to this activity should depend on the data that you receive from your client(s). If the data needs a lot of preparation before students can import it, you may want to go through it column by column as a class and talk about how to make it compatible with database tables. If the client’s data is quite a good match for the tables that most groups have, you may want to let the students work with it on their own after a brief discussion of how to prepare the data. At minimum, you will want to discuss the following:

What format the data is in What the columns look like in general Whether the data meets the constraints of field properties and validation rules Whether there are any show stoppers that students can predict by looking at the

dataDepending on the data you receive from your client, this activity may require an additional class period.

Remind students to start by preparing the data and then to use the Import Wizard to import it. Circulate to help students troubleshoot any problems that may arise. Explain that they will have most of the next class period to finish importing their data.



Step Min. Activity

CLASS PERIOD 10

19 40 Culminating Project Work: Importing Client Data (Continued)In this activity, students finish importing the data from their client into all of the tables of their culminating project database.

Instruct students to get right to work completing the import of their client’s data into their database. Circulate and help students resolve any problems as they work.

When students have finished importing their data, ask each group to share what they thought was the most difficult part of importing data. Discuss how working with data from a real customer is more difficult than working with predictable data, like that for the dolphin database.

Finally, ask students to prepare to submit their database and the accompanying documents for assessment. Refer students to Student Resource 9.2, Checklist: Relational Database, and tell them to work as a group to check their project package against this list to make sure they have included all of the necessary elements. Assess the databases using Teacher Resource 9.1, Rubric: Relational Database.

20 10 Reflection: Creating a Relational DatabaseIn this activity, students reflect on the work they have done in this lesson to create a relational database.

Remind students that creating a reliable relational database that will really work for their client is a very complex process. Now that they have completed the process and have a working database, it is a good time to review the steps in the process and reflect on the work they have done. Working as a class, have students call out the steps they have taken to complete their relational database. Ask one student to act as scribe and write the steps on the board or on chart paper.

To conclude the lesson, ask students to write a few sentences in their notebooks about which part of the process they found most difficult and how they overcame the difficulty. Have them share this reflection with a partner.

Tell students that creating a relational database is a professional skill that they should add to their resume and mention in job or internship interviews. Even after they have completed this course, they should save their relational database as an example of the quality of work they can produce.

ExtensionsEnrichment

Have students write some recommendations for future Database Design students on the best ways to import client data. Tell them to structure it like a “tips and tricks” document, formatted either as a Word document or as a blog on the class blog site. They can look on the Internet for models of this type of document.



Technology Integration Ask students to use Microsoft Access’s Data Collection feature, which works with Access, to

collect data from email forms and insert it directly into a database. After they finish the activity, have them write a few paragraphs about whether they think this is a useful feature and why. Ask them to compare this type of import with importing data from Excel or text files.Cross-Curricular Integration

Math: Have students create a Venn diagram to compare and contrast the use of mathematical symbols such as OR, <, <=, >, >=, =, and <> in database development with their use in another field of mathematics or programming.


digital video lessoncurriculum.naf.org/packaged/assets/downloads/technolo… · web viewlesson 9....

Documents