Mastering Database Concepts - SchoolNotes



Teacher Note: Microsoft Access 2003 and 2007 were used to create the databases in this objective. All graphics in the guide are based on these versions. Make adjustments to notes and directions as necessary in accordance with the software version used in your classroom.

Database Uses in Business

I. Database Use and Purpose – A database is a tool used to organize, store, retrieve, and communicate groups of related information.

A. Examples of databases and database uses in business and society:

1. phone book

2. online library card catalog

3. payroll data including employee names, social security numbers, pay rates, exemptions, etc.

4. eBay and other online businesses use databases to maintain inventory and keep track of customers, products, vendors, pay pal accounts

B. Tables are the building blocks of all databases. They are used to group and organize the information within a database.

1. A table is an arrangement of columns and rows. It is the fundamental building block of all databases.

2. An entry is a single piece of data in a database table.

3. A field is a grouping or category of similar information contained in a table; a unique identifier for a category of information

4. A record is a complete description of all of the fields related to one item in a table.

II. Data Types, Field Properties, Primary Keys, and Printing

A. Before a database can be built, the user must first define its purpose and determine how the data will be organized into fields.

1. Fields should be formatted in accordance with the data they contain so that the database can be searched, used in calculations, and sorted as needed.

2. A field name should be short and descriptive

3. A data type defines the value of a cell and a field property defines the format of the data within the cell

B. Data types: used to control how data in a particular field will be searched, sorted, or used in calculations. The default format is text.

1. Text – used for data that will be sorted in alphabetical order or listed randomly, but will not be used in mathematical calculations.

2. Memo – a data type used for lengthy entries that allows combinations of text and numbers

3. Number – used for data that may be calculated, sorted, or filtered.

4. Date/time – data type used to arrange and sort data chronologically, often used with the input mask field property

5. Currency – data type used to format numbers as decimal values with a dollar sign. The default format for currency is two decimal places.

6. Auto Number – used to automatically assign a number to each record and to assist in preventing duplication of data.

7. Yes/No – a data type in toggle format that allows a user to select a yes or no value in a database cell. The default setting is No.

8. Lookup Wizard – a data type used to define preset values for database entries. This feature increases productivity and accuracy by reducing the amount of data entry required.

C. Field properties – used to control how data is entered in a field

1. Field size – used to control the size of the field, measured in characters

2. Format – used to control the layout of the field, such as one or two decimal places

3. Input Mask – a template-like field property that regulates how data is entered in a cell. For example, if the phone number input mask is used, the user will be prompted to input data in a specific format, such as (919) 555-5555.

4. Caption – a field property used for column naming that allows a more user-friendly and properly formatted field name than the field name stored in the database design

D. Editing and Printing – Procedures to add and delete records and fields, edit field names, rearrange fields in a database and print.

III. Database Management – The effectiveness of a database can be measured by the user’s ability to retrieve useful information.

• An effective database is one that contains timely information and thus requires continuous maintenance.

o Consider the telephone directory and the amount of information that must be gathered, edited, and processed to provide customers with accurate information.

• Many tools are available to assist in the retrieval, processing, and manipulation of database information.

A. Filters allow for the retrieval of information that meets specific criteria.

1. Filter by Selection – filters for information related to an active cell

2. Filter/Advanced Filter Sort – used to define specific search criteria

B. Sorts allow for information to be arranged in a specific order such as alphabetical, chronological, ascending, or descending.

C. Database Relationship – a link between two or more tables in a database

• The relationship is developed when common fields in the tables are linked, such as the Customer ID field in a Customer Address table and the Customer ID field in a Purchases table.

• The relationship enhances the power of the database by allowing retrieval of data from multiple tables and eliminating the need for data duplication.

1. Types of Relationships

a. One-to-Many is a type of relationship that occurs when a record in one table is linked to more than one record in another table.

b. Many-to-Many relationships are formed by connecting two or more tables that have a one-to-many relationship. The connection is made by a Junction Table.

2. Relationship Components

a. Primary Key – used to format each record in a database as a unique entity. Primary keys allow database tables to communicate.

i. May be assigned to an existing field that contains information unique to each record.

ii. May be assigned via an AutoNumber field that automatically assigns a unique number to each record.

iii. The linked fields must be of the same data type and size

b. Foreign key – when tables are linked by the primary key, the related field in the second table is known as the foreign key

c. Junction table – a table used to join primary key fields from multiple tables

d. Join line – a graphical representation of the link between two or more tables

e. Referential integrity protects related data that is stored in multiple tables.

Referential integrity would prevent a customer in a customers table from being deleted if the customer’s ID also appears in the order table

IV. Raw Data Import - A useful and practical method for obtaining information from other sources and creating database tables. Eliminates the need for repetitive data entry and assists in the protection of the data’s integrity.

A. Must be formatted as text

B. Must be separated at field and record breaks with a separator, such as a comma (delimitated)

| |Instructional Activities |Relevancy |Resources |

| |View and discuss the Database Fundamentals PowerPoint as students take |Helps students UNDERSTAND (B2) |Database Fundamentals |

| |notes using the Database Fundamentals graphic organizer |the purpose and uses of databases |PowerPoint |

| |The graphic organizer asks students to associate company or business |used in business |Database Fundamentals graphic|

| |names with the database examples (Slide 3). Possible examples include |database concepts and components and |organizer |

| |BellSouth, SIMS, iTunes, FBI, Wikipedia, e-Bay, Yost & Little Realty, |how tables are organized | |

| |Blue Cross Blue Shield, and Lowes Foods inventory | | |

| |Relate to previous knowledge by allowing students to brainstorm and | | |

| |suggest additional examples of databases | | |

| |Before students can complete the last page of the graphic organizer, | | |

| |facilitate discussion on Slides 8-11 about the components of a database | | |

| |table | | |

| |Slide 13 instructs students to complete the last page of the graphic | | |

| |organizer. When students have completed the page, review answers with | | |

| |the class. | | |

|Continued on next page |

| |Lead a class discussion about how information is organized in the online |Helps students UNDERSTAND (B2): |Guided Practice: Search an |

| |travel database at |how data is organized in a database |Online Database activity and |

| |Ask students how the information is grouped and assist them in reaching |characteristics of fields, records, |key |

| |the conclusion that the flight time, airline, and departure city are all |and tables | |

| |examples of field names in the site’s database | | |

| |Distribute Guided Practice: Search an Online Database to students | | |

| |Demonstrate the search procedures for Flights 1 and 2 as students follow | | |

| |along at their computers | | |

| |Instruct students to complete the searches for Flights 3-5 independently | | |

| | | | |

| |Monitor/provide assistance as needed | | |

| |Once the students have completed all five searches, lead a class | | |

| |discussion using the questions at the end of the activity | | |

| |Review the descriptions of field, record, entry, and database as related | | |

| |to the data used in the search | | |

| |View and discuss the Data Types and Field Properties PowerPoint as |Helps students UNDERSTAND (B2) the |Data Types and Field |

| |students take notes using the Data Types and Field Properties graphic |defining features of how data is |Properties PowerPoint |

| |organizer |organized, formatted, and categorized|Data Types and Field |

| |Discuss the difference and interdependence of data types and field |in a database |Properties graphic organizer |

| |properties | | |

| |Provide examples of the uses of data types and field properties as each | | |

| |item is presented in the presentation | | |

| |Distribute Review of Database Tables to students and instruct them to |Helps students UNDERSTAND (B2) |Review of Database Tables |

| |complete the activity independently using notes |conceptual knowledge of database |activity and key |

| |Monitor and provide assistance as needed |tables | |

| |Upon completion of the activity, review the answers as a class | | |

| |Distribute Guided Practice: Create a Database: Computers to students |Helps students UNDERSTAND (B2) |Guided Practice: Create a |

| |Demonstrate how to plan the structure of a database, enter data, add |procedures to plan the structure, |Database: Computers activity |

| |captions, and insert and delete records as students take notes and follow|enter data, add captions, and add and|and key |

| |along at their computers |delete records |(5.01/Dbase Activities/ |

| |Review each step in the Steps for Creating a Database (Slide 12 of the | |Computers |

| |Data Types and Field Properties PowerPoint) while leading the class | |Data Types and Field |

| |through the activity | |Properties PowerPoint |

| |Monitor/provide assistance as needed | | |

| |Distribute Independent Practice: Create a Database: Scavenger Hunt to |Helps students UNDERSTAND (B2) how to|Independent Practice: Create|

| |students |plan the structure of a database and |a Database: Scavenger Hunt |

| |Review the instructions for the activity with the class and determine the|field properties through independent |activity and key |

| |protocol for saving the database and the table |practice |(5.01/Dbase Activities/ |

| |Discuss the characteristics of appropriate field names: descriptive, | |Scavenger |

| |concise, unique | | |

| |Instruct students how they should gather and record the survey | | |

| |information | | |

| |One option is to use a word document to record the data | | |

| |Instruct students as to whether they are to survey the entire class or a | | |

| |specific number of students, or perhaps the survey should be gender | | |

| |specific | | |

| |Allow a sufficient amount of time to collect the data before instructing | | |

| |students to return to their computers for data entry | | |

| |Monitor/provide assistance as needed | | |

| |Upon completion of the activity, view samples of student work using a | | |

| |digital projector and discuss the similarities and differences of their | | |

| |choices for field names and properties | | |

| |Ask students how Yes/No and Lookup formats could be used in the database | | |

| |Distribute the Guided Practice: Database Management: Anti-Virus |Helps students UNDERSTAND (B2) |Guided Practice: Database |

| |activity sheet and the Anti-Virus_Student database to students |procedures to: |Management: Anti-Virus |

| |electronically. |Edit data types and field properties |activity and key |

| |Demonstrate the activity while students take notes and follow along at |Enter data |(5.01 Dbase Activities/ |

| |their computers |Use AutoNumber |Anti-virus |

| |Discuss the effect of deleting the entire field. Can the deleted action |Add captions |(5.01 Dbase Activities/ |

| |be undone? |Add a field |Anti-Virus_Student |

| |Discuss how to print and allow students to print as well OR require them |Delete a field | |

| |to take notes and print the next activity |Print a table | |

| |Distribute the Independent Practice: Create a Database: Survey activity|Helps students UNDERSTAND (B2) |Independent Practice: Create|

| |and the Survey_Student database electronically to students |procedures to: |a Database: Survey activity |

| |Provide directions for saving the database and the table in student |Assign data types and field |and key |

| |directories |properties |(5.01 Dbase Activities/ |

| |Instruct students to complete the activity independently |Enter data |Survey |

| |Instruct students to print a copy of the database |Add a field |(5.01 Dbase Activities/ |

| |This activity requires students to add a field (Marital Status) to the |Delete a field |Survey_ Student |

| |database and later delete it. They are instructed in the activity to |Print a table | |

| |print the database with the added field and then ask the teacher for | | |

| |instructions about which field to delete. Make sure all students have | | |

| |completed the activity before instructing them to delete the Marital | | |

| |Status field | | |

| |Review the procedures for adding a field and determining appropriate data| | |

| |formats | | |

|Continued on next page |

| |

| |

| |Distribute the Guided Practice: Create a Database: MusicFun handout and|Helps students UNDERSTAND (B2) |Guided Practice: Create a |

| |the MusicFun_Student database to students electronically. |procedures to: |Database: MusicFun activity |

| |Instruct students on how to save the database to their student |Add fields |and key |

| |directories |Rearrange fields |(5.01 Dbase Activities/ |

| |Demonstrate the activity as students take notes and follow along at their|Add entries |MusicFun |

| |computers |Format data types and properties |(5.01 Dbase Activities/ |

| |Facilitate discussion with examples of when and why these formats and | |MusicFun_ Student |

| |edits are performed in business | | |

| |Ask the following questions: | | |

| |Why might a field be added to a company database? | | |

| |Why might fields be rearranged? | | |

| |What types of entries will be added? | | |

| |Why is the date with input mask used? What’s the benefit of using it? | | |

| |Why is AutoNumber used? | | |

| |Why is a Lookup value used? | | |

| |Why is a Yes/No field used? | | |

| |Distribute the Independent Practice: Create a Database: B&B activity to |Helps students UNDERSTAND (B2) how to|Independent Practice: Create|

| |students along with the electronic database B&B_Student. |manage a database by editing fields, |a Database: B&B activity and|

| |Instruct students how to save the database to their student directory |data, data types and field properties|key |

| |Instruct students to complete the activity independently. | |(5.01 Dbase Activities/B&B |

| |Monitor/provide assistance as needed | |(5.01 Dbase |

| |Ask if they know anyone who manages a database and discuss the job | |Activities/B&B_Student |

| |responsibilities, high school and community college courses available, | | |

| |salary, and etc. | | |

| |Distribute Database Review to students and instruct them to complete the |Assesses how well students UNDERSTAND|Database Review student |

| |activity independently either using their notes or as a quiz grade to |(B2) database concepts |activity and key |

| |determine if more review is needed. | | |

| |Discuss with students the need for output options when using a database |Helps students UNDERSTAND (B2) the |Determining Sort Order |

| |Sometimes, specific data is requested that only includes certain fields |difference between ascending and |activity and key |

| |Information is often requested in a particular order, such as flights |descending order and why data is | |

| |arranged from cheapest to most expensive or latest departure times to |sorted | |

| |earliest departure times | | |

| |Use examples to discuss the difference between ascending and descending | | |

| |Distribute Determining Sort Order to students and instruct them to work | | |

| |in pairs to complete the activity | | |

| |Upon completion of the activity, review answers with the class | | |

| |Discuss with students how operators are used in a database to define the |Helps students UNDERSTAND (B2) |Understanding Operators |

| |organization and retrieval of data |database operators through examples |activity and key |

| |Use Expedia as an example and discuss how operators are used to define |of how they are used to organize data| |

| |dates, such as not less than 02/02/09 and not greater than 02/07/09 | | |

| |An example of the equal operator may specify that airline equals Delta | | |

| |Distribute UNDERSTAND Operators to students and instruct them to complete| | |

| |the activity in pairs | | |

| |In Part II, use the unpacked content for reference as students record | | |

| |notes about the key terms | | |

| |Demonstrate and provide examples of the key terms | | |

| |Review the activity with the class upon completion | | |

| |Distribute Guided Practice: Filtering and Sorting a Database to students|Helps students UNDERSTAND (B2) filter|(5.01/Dbase Activities/ |

| | |and sort operations and operators |Survey (5.01/Dbase |

| |Using a digital projector, demonstrate the filter and sort actions |through guided practice |Activities/ Computers |

| |required to complete each task. Describe the procedures for each method | |Guided Practice: Filtering |

| |as students take notes. Allow students to follow along at their | |and Sorting a Database |

| |computers to complete the activity. | |activity and key |

| |Discuss why filter and sort operations are performed on a database. What| | |

| |are the benefits? | | |

| |Show the results of each filter or sort to the class to confirm that | | |

| |their results are similar. If their results are different, determine why| | |

| |through class discussion and problem solving | | |

| |Distribute Independent Practice: Filtering and Sorting a Database to |Helps students UNDERSTAND (B2) filter|(5.01/Dbase Activities/B&B |

| |students |and sort operations and operators |Independent Practice: |

| |Instruct students that they will need to retrieve the B&B database to |through independent practice |Filtering and Sorting a |

| |complete the activity | |Database activity and key |

| |Instruct them to record their results in table format in a word | | |

| |processing document, including a short narrative in the document that | | |

| |describes how they retrieved the information for each search | | |

| |Monitor/provide assistance as needed | | |

| |Using a digital projector view the Database Relationships PowerPoint and |Helps students UNDERSTAND (B2) the |Database Relationships |

| |discuss with students the How, What, and Why of a database relationship. |defining features and characteristics|PowerPoint |

| |Students should take notes using the Relationships graphic organizer |of a database relationship |Relationships graphic |

| |Use examples from , TicketMaster, or other popular database | |organizer |

| |sites that are familiar to students to differentiate possible database | | |

| |relationships used by each site | | |

| |

|Continued on next page |

| |Distribute the Guided Practice: One-to-Many Relationships activity to |Helps students UNDERSTAND (B2) |Guided Practice: One-to-Many |

| |students along with an electronic copy of the PetRegistry database |one-to-many relationships through |Relationships activity and |

| |Demonstrate the activity using a digital projector and allow students to |examples and guided practice |key |

| |take notes and follow along at their computers | |( 5.01/ Dbase Activities/ |

| |Point out the primary key fields of each table and discuss the primary | |PetRegistry |

| |and foreign keys (PetOwners table – primary key is OwnerID; Pet Roster | | |

| |table – foreign key is OwnerID). | | |

| |Demonstrate setting up a one-to-many relationship between the two tables | | |

| |and allow students to follow along at their computers and record notes | | |

| |Demonstrate how to delete and re-establish the relationship | | |

| |Demonstrate how to view, expand, and collapse the relationships in the | | |

| |PetOwners table | | |

| |Discuss student answers to #8. | | |

| |Distribute the A Little More Guidance activity to students and the States|Helps students UNDERSTAND (B2) how |A Little More Guidance |

| |electronically. |information is shared within database|activity |

| |Instruct students to create a new table in the database and save it as |tables |( 5.01/ Dbase Activities/ |

| |Just States | |States |

| |In design view of the Just States table, demonstrate how to look up the | | |

| |state names and abbreviations from the State table as students follow | | |

| |along at their computers. | | |

| |For independent practice, require students to create another table named | | |

| |State Population. Require students to add a new field named States and | | |

| |use the method of their choice to retrieve the state names from the State| | |

| |table. | | |

| |Facilitate and provide assistance as needed. | | |

| |Distribute Independent Practice: One-to-Many Relationships to students |Helps students UNDERSTAND (B2) |Independent Practice: |

| |and instruct them to complete the activity independently as you monitor |one-to-many relationships through |One-to-Many Relationships |

| |and provide assistance as needed |independent practice |activity and key |

| |Optional: Require students to use print screen or specialized software | |( 5.01/Dbase Activities/ |

| |to capture the Authors table with all relationships expanded | |Publishers |

| |Distribute the Guided Practice: Many-to-Many Relationships activity and |Helps students UNDERSTAND (B2) |Guided Practice: |

| |the Beach Rentals_Student database to students. |many-to-many relationships through |Many-to-Many Relationships |

| |Instruct students how to save the database to their student directory |examples and guided practice |activity and key |

| |Demonstrate the activity and allow students to follow along at their | |( 5.01/Dbase Activities/ |

| |computers and take notes | |Beach Rentals_Student |

| |As a class, discuss and answer the questions at the end of the activity | |( 5.01/Dbase Activities/ |

| | | |Beach Rentals |

| |Distribute the Independent Practice: Many-to-Many Relationships activity|Helps students UNDERSTAND (B2) the |Independent Practice: |

| |and the Flights_Student database to students |purpose and uses of many-to-many |Many-to-Many Relationships |

| |Instruct students to complete the activity independently |relationships |( 5.01/Dbase Activities/ |

| |Monitor and provide assistance as needed | |Flights_Student |

| |Students may refer to their notes from the previous lesson | |( 5.01/Dbase Activities/ |

| | | |Flights |

| |Distribute Guided Practice: Importing Data to students |Helps students UNDERSTAND (B2) |Guided Practice: Importing |

| |Introduce the activity and explain that data used in database tables can |through example and guided practice |Data activity |

| |be imported from other sources. Discuss common sources used for import |how data is imported into a database |(5.01 Dbase Activities/Raw |

| |and the benefits of importing data |and the required properties of the |Data for FBLA |

| |Using a digital projector, demonstrate the activity and allow time for |data | |

| |students to take notes and follow along at their computers | | |

| |Restate the procedures followed to complete the import | | |

| |Distribute Independent Practice: Importing Data to students |Helps students UNDERSTAND (B2) |Independent Practice: |

| |Go over the directions and points to remember |through independent practice how data|Importing Data activity and |

| |Instruct students what filenames to use when saving the word processing |is imported into a database, the |key |

| |document and database |required properties of the data, and |( 5.01/Dbase Activities/ Raw |

| |Instruct students to complete the activity independently including the |the procedures |Data for University |

| |written response question at the end, which asks students to develop a | | |

| |procedure for importing data | | |

| |Monitor/provide assistance as needed. | | |

| |Upon completion of the activity, ask for student volunteers to share the | | |

| |procedures they developed with the class | | |

| |Distribute Database Concept Review to students and instruct them to |Reviews how students UNDERSTANDINGOF |Database Concept Review |

| |complete the activity independently as a review of the concepts learned |(B2) database concepts learned in |activity and key |

| |in this objective |this objective | |

| |Students are directed to work with a partner in part of the activity. | | |

| |Review the activity directions with the class before they begin work. | | |

| |This concludes the instruction for Objective 5.01. Objective 5.02 will build on the students’ knowledge of tables by incorporating the use |

| |of database queries, forms, and reports. |

•

Table of Contents

Database Fundamentals 366

Guided Practice: Search an Online Database 369

Guided Practice: Search an Online Database Key 371

Data Types and Field Properties 373

Review of Database Tables 376

Review of Database Tables Key 378

Guided Practice: Create a Database: Computers 380

Guided Practice: Create a Database: Computers Key 383

Independent Practice: Create a Database: Scavenger Hunt 385

Independent Practice: Create a Database: Scavenger Hunt Key 386

Guided Practice: Database Management: Anti-Virus 387

Guided Practice: Database Management: Anti-virus Key 389

Independent Practice: Create a Database: Survey 390

Independent Practice: Create a Database: Survey Key 391

Guided Practice: Create a Database: MusicFun 392

Independent Practice: Create a Database: B&B 396

Independent Practice: Create a Database: B&B Key 399

Independent Practice: Create a Database: B&B Key 400

Database Review 401

Database Review Key 402

Determining Sort Order 403

Determining Sort Order Key 405

Understanding Operators 407

Understanding Operators Key 409

Guided Practice: Filtering and Sorting a Database 411

Guided Practice: Filtering and Sorting a Database Key 413

Independent Practice: Filtering and Sorting a Database 416

Independent Practice: Filtering and Sorting a Database Key 418

Types of Relationships 421

Guided Practice: One-to-Many Relationships 423

Guided Practice: One-to-Many Relationships Key 425

A Little More Guidance 428

Independent Practice: One-to-Many Relationships 429

Independent Practice: One-to-Many Relationships Key 431

Guided Practice: Many-to-Many Relationships 432

Guided Practice: Many-to-Many Relationships Key 435

Independent Practice: Many-to-Many Relationships 436

Independent Practice: Many-to-Many Relationships Key 438

Guided Practice: Importing Data 439

Independent Practice: Importing Data 440

Independent Practice: Importing Data Key 441

Database Concept Review 442

Database Concept Review Key 444

Database Fundamentals

DIRECTIONS: RECORD NOTES WHILE VIEWING THE DATABASE FUNDAMENTALS POWER POINT

1. What are the field names?

2. What is the name of the table?

3. How many records are there?

4. How many entries are there?

5. Describe the contents of one record.

6. Describe the contents of one field.

Guided Practice: Search an Online Database

DIRECTIONS: YOU WILL SEARCH AN ONLINE DATABASE OF AIRLINES. GO TO WWW. AND SEARCH FOR 5 ROUND-TRIP FLIGHTS TO 5 DIFFERENT DESTINATIONS. ANSWER THE QUESTIONS AT THE END OF THE ACTIVITY WHEN YOU HAVE COMPLETED THE SEARCH.

1. Flight One

|Flight # | | |Airline | |

|Departure City | | |Arrival City | |

|Departure Time | | |Arrival Time | |

|Length of Flight | | |Date Leaving | |

|# of Stopovers | | |Cost of Trip | |

2. Flight Two

|Flight # | | |Airline | |

|Departure City | | |Arrival City | |

|Departure Time | | |Arrival Time | |

|Length of Flight | | |Date Leaving | |

|# of Stopovers | | |Cost of Trip | |

3. Flight Three

|Flight # | | |Airline | |

|Departure City | | |Arrival City | |

|Departure Time | | |Arrival Time | |

|Length of Flight | | |Date Leaving | |

|# of Stopovers | | |Cost of Trip | |

4. Flight Four

|Flight # | | |Airline | |

|Departure City | | |Arrival City | |

|Departure Time | | |Arrival Time | |

|Length of Flight | | |Date Leaving | |

|# of Stopovers | | |Cost of Trip | |

5. Flight Five

|Flight # | | |Airline | |

|Departure City | | |Arrival City | |

|Departure Time | | |Arrival Time | |

|Length of Flight | | |Date Leaving | |

|# of Stopovers | | |Cost of Trip | |

Questions:

1. What are the field names?

2. What is the name of the database?

3. How many entries were recorded in your search?

4. How many records were searched?

Guided Practice: Search an Online Database Key

DIRECTIONS: YOU WILL SEARCH AN ONLINE DATABASE OF AIRLINES. GO TO WWW. AND SEARCH FOR 5 ROUND-TRIP FLIGHTS TO 5 DIFFERENT DESTINATIONS. ANSWER THE QUESTIONS AT THE END OF THE ACTIVITY WHEN YOU HAVE COMPLETED YOUR SEARCH.

Note: These answers will vary with the date and destination selections. If this site is not available, select a similar site or create a database which contains relevant data from which the students can gather the information.

1. Flight One

|Flight # |919/749 | |Airline |Delta |

|Departure City |Raleigh | |Arrival City |Las Vegas |

|Departure Time |6:00 a.m. | |Arrival Time |9:50 a.m. |

|Length of Flight |6 hr 60 m | |Date Leaving |January 1, 2008 |

|# of Stopovers |1 | |Cost of Trip |$305 |

2. Flight Two

|Flight # |4472/1226 | |Airline |US Airways |

|Departure City |Asheville | |Arrival City |Nassau, Bahamas |

|Departure Time |9:45 a.m. | |Arrival Time |1:54 p.m. |

|Length of Flight |4 hr 9 m | |Date Leaving |November 1, 2007 |

|# of Stopovers |1 | |Cost of Trip |$587 |

3. Flight Three

|Flight # |1122/4250 | |Airline |US Airways |

|Departure City |New Bern | |Arrival City |Miami, FL |

|Departure Time |5:35 a.m. | |Arrival Time |9:37 a.m. |

|Length of Flight |4 hr 2 m | |Date Leaving |December 3, 2007 |

|# of Stopovers |1 | |Cost of Trip |$521 |

4. Flight Four

|Flight # |1701/2075 | |Airline |Northwest |

|Departure City |Charlotte | |Arrival City |Missoula, MT |

|Departure Time |5:47 p.m. | |Arrival Time |11:49 p.m. |

|Length of Flight |8 hr 2 m | |Date Leaving |October 1, 2007 |

|# of Stopovers |1 | |Cost of Trip |$461 |

5. Flight Five

|Flight # |2420/1495 | |Airline |Continental |

|Departure City |Greensboro | |Arrival City |Los Angeles |

|Departure Time |6:35 a.m. | |Arrival Time |10:52 a.m. |

|Length of Flight |7 hr 17 m | |Date Leaving |September 12, 2007 |

|# of Stopovers |1 | |Cost of Trip |$331 |

Questions:

1. What are the field names?

• Flight #, Airline, Departure City, Arrival City, Departure Time, Arrival Time, Length of Flight, Date Leaving, # of Stopovers, and Cost of Trip

2. What is the name of the database?

• Expedia

3. How many entries were recorded in your search?

• 50

4. How many records can be produced as a result of your search?

• 5

Data Types and Field Properties

DIRECTIONS: RECORD NOTES AND ANSWER THE QUESTIONS BELOW WHILE VIEWING THE DATA TYPES AND FIELD PROPERTIES POWER POINT.

1. What is the function of a data type?

2. What is the function of a field property?

3. Why are data types important?

4. Critical thinking: If text is selected for the data type of a field and the field contains the birthdates of 50 employees, what will happen when the data is sorted?

5. What data types are available? Use the help feature of your software to briefly describe the purpose of each data type listed below. Hint: Select the first data type and press the F1 key

|Data Type |Description |

|Text | |

|Memo | |

|Number | |

|Date/time | |

|Currency | |

|AutoNumber | |

|Yes/No | |

|Lookup Wizard | |

6. When should you change from the default data type?

7. Why are field properties important?

8. What field properties are available? Click on each field property listed below and view the descriptions as they appear in the right-hand pane. Describe each.

|Field Property |Description |

|Field size | |

|Format | |

|Input mask | |

|Caption | |

9. When should you set a field property?

10. What are the steps for creating a database?

1)

a.

b.

c.

2)

3)

4)

a.

b.

5)

Review of Database Tables

Part I Directions: Match the letters above with their definition below.

1. Field ______

2. Record ______

3. Entry ______

4.

Part II Directions: Answer the questions below using the above diagram.

5. Number of entries _________

1. Number of records _________

2. Number of fields _________

Part III Directions: Identify the correct data type for each item using the list below

|Text |Number |Date/Time |Yes/No |

|Currency |Memo |Lookup Wizard | |

1. 123-45-6789 (social security number) __________________

2. John __________________

3. $257.98 __________________

4. 12587 (employee ID number) __________________

5. Paid/Not Paid __________________

6. This item needs to be reordered. It

is one of our best selling items. __________________

7. Series of choices (low, medium, high) __________________

8. December 15, 2006 __________________

9. (555) 555-5555 (phone number) __________________

10. Pets Allowed/Pets Not Allowed __________________

11. Age of students __________________

12. Cost of trip __________________

13. Date of Purchase __________________

Part IV Directions: Place the parts of a database in order from the smallest unit to the largest unit. (1 being the smallest, 5 being the largest)

|Field |Character |Entry |Record |Database |

1.

2.

3.

4.

5.

Part V: Match the definition to the correct term.

| |Field |Is also a true/false field. Will contain a checkbox for yes no. Default is No. |

| |Entry |Monetary values |

| |Record |Creates fields that automatically enter a unique number when a record is added |

| |Text |Includes words and/or numbers not used in calculations |

| |Number | This format is applied to a field that will be sorted chronologically |

| |Input Mask | Data and formulas keyed in a field |

| |Yes/No |An efficient means of storing and retrieving data and printing reports from the stored |

| | |data |

| |Currency |One item of information in a record. Represented by a column |

| |Lookup Field |A group of related fields of information |

| |Memo |A format often applied to dates and times |

| |Database |Like text, but max of 64,000 characters |

| |AutoNumber |Allows you to create a finite set of choices for that field’s entry values |

| | | |

| | | |

Review of Database Tables Key

Part I Directions: Match the letters above with their definition below.

3.

1. Field __B____

2. Record __A____

3. Entry __C____

Part II Directions: Answer the questions below using the above diagram.

4. Number of entries ____20___

4. Number of records ____4___

5. Number of fields ____5___

Part III Directions: Identify the correct data type for each item using the list below:

|Text |Number |Date/Time |Yes/No |

|Currency |Memo |Lookup Wizard | |

1. 123-45-6789 Text

2. John Text

3. $257.98 Currency

4. 12587 Text

5. Paid/Not Paid Yes/No

6. This item needs to be reordered. It is one of our best selling items. Memo

7. Series of choices (low, medium, high) Lookup Wizard

8. December 15, 2006 Date/Time

9. (555) 555-5555 Text

10. Pets Allowed/Pets Not Allowed Yes/No

11. Age of students Number

12. Cost of trip Currency

13. Date of Purchase Date/Time

Part IV Directions: Place the parts of a database in order from the smallest unit to the largest unit. (1 being the smallest, 5 being the largest)

|Field |Character |Entry |Record |Database |

1. Character

2. Entry

3. Field

4. Record

5. Database

Part V: Match the definition to the correct term.

|H |Field |Is also a true/false field. Will contain a checkbox for yes no. Default is No. |

|F |Entry |Monetary values |

|I |Record |Creates fields that automatically enter a unique number when a record is added |

|D |Text |Includes words and/or numbers not used in calculations |

|E |Number |This format is applied to a field that will be sorted chronologically |

|J |Input Mask |Data and formulas keyed in a field |

|A |Yes/No |An efficient means of storing and retrieving data and printing reports from the |

| | |stored data |

|B |Currency |One item of information in a record. Represented by a column |

|L |Lookup Field |A group of related fields of information |

|K |Memo |A format often applied to dates and times |

|G |Database |Like text, but max of 64,000 characters |

|C |AutoNumber |Allows you to create a finite set of choices for that field’s entry values |

| | | |

Guided Practice: Create a Database: Computers

DIRECTIONS: IN THIS ACTIVITY, YOU WILL FOLLOW ALONG WITH YOUR TEACHER TO CREATE A DATABASE AND COMPLETE THE FOLLOWING TASKS:

• Plan the structure

• Enter data

• Add records

• Delete records

1. Plan the structure

a. How will the data be grouped? You will be creating a computer inventory database. For each item, you have the name of the manufacturer, the serial number of the equipment, the room location, and the ID number of the student to which each piece of equipment is assigned.

b. What will the field names be? Record the field names in the first row of each column below:

| | | | |

|Compaq |12345 |214 |5 |

|Dell |54321 |110 |6 |

|Dell |98765 |310 |9 |

|Packard Bell |45678 |214 |3 |

|Compaq |34567 |110 |4 |

|Compaq |95425 |111 |8 |

|Gateway |55557 |109 |8 |

|Dell |90864 |222 |12 |

|Dell |123674 |214 |15 |

|Gateway |87093 |119 |27 |

|Hewlett Packard |83765 |111 |22 |

c. What will you name the database?

2. Open and save a new database

Notes:

3. Create a table

Notes:

4. Enter the field names

a. Set data types. Unless the data will be used in a sort, search, or calculation, use the default settings. Think about how a user might search the database for information. Will the information be sorted by room number? Will the information be searched by student number?

Notes:

b. Set field properties if needed. Do you need to limit the size of any of the fields? Do any of the fields need to be represented as a decimal?

▪ Use the caption field property to assign user-friendly names to each field

Notes:

5. Enter the data using the table on the previous page.

Edit the Database Table:

1. Edit the database to add the following records:

|Hewlett Packard |88334 |109 |25 |

|Compaq |22990 |211 |26 |

|Dell |34419 |110 |27 |

|Gateway |22334455 |214 |28 |

2. Edit the database to delete the following 2 records:

• Compaq with Serial Number 34567

• Dell with Serial Number 123674

Notes:

Guided Practice: Create a Database: Computers Key

|FIELD NAME |FIELD PROPERTY (FIELD SIZE) |FIELD PROPERTY | |

| | |(Caption) | |

|Manufacturer |20 |Same | |

|SeNo |9 |Serial Number | |

|RNo |4 |Room Number | |

|StNo |4 |Student Number | |

|Manufacturer |Serial Number |Room Number |Student Number |

|Compaq |12345 |214 |5 |

|Dell |54321 |110 |6 |

|Dell |98765 |310 |9 |

|Packard Bell |45678 |214 |3 |

|Compaq |34567 |110 |4 |

|Compaq |95425 |111 |8 |

|Gateway |55557 |109 |8 |

|Dell |90864 |222 |12 |

|Dell |123674 |214 |15 |

|Gateway |87093 |119 |27 |

|Hewlett Packard |83765 |111 |22 |

1. Edit the database by adding the following records:

|Manufacturer |Serial Number |Room Number |Student Number |

|Hewlett Packard |88334 |109 |25 |

|Compaq |22990 |211 |26 |

|Dell |34419 |110 |27 |

|Gateway |22334455 |214 |28 |

2. Edit the database by deleting the following 2 records:

Compaq with Serial Number 34567

Dell with Serial Number 123674

Independent Practice: Create a Database: Scavenger Hunt

DIRECTIONS: IN THIS ACTIVITY, YOU WILL WORK INDEPENDENTLY TO CREATE A DATABASE BASED ON INFORMATION YOU GATHER FROM YOUR CLASSMATES.

• You will need to determine field names and captions, data types, and field properties by reading the questions below.

• Once you have determined the fields, you will survey your classmates as assigned by your teacher.

• You will then create a database table to record your results.

Survey Questions:

1. What is your first and last name?

2. Do you have blue eyes?

3. What is your favorite sport?

4. Do you have your driver’s license?

5. Are you a Senior, Junior, Sophomore or Freshman?

6. What is your favorite music group or band?

7. What is your favorite food?

8. What is your favorite color?

9. Are you left-handed or right-handed?

10. How old are you?

Independent Practice: Create a Database: Scavenger Hunt Key

Student answers will vary

Guided Practice: Database Management: Anti-Virus

DIRECTIONS: IN THIS ACTIVITY, YOU WILL FOLLOW ALONG WITH YOUR TEACHER TO COMPLETE THE FOLLOWING TASKS AS YOU CREATE A DATABASE OF ANTI-VIRUS SOFTWARE:

|Determine data types and field properties |Add captions |

|Enter data |Add a field |

|Use AutoNumber |Delete a field |

| |Print a table |

•

1. Open the Anti-Virus_Student database that has been sent to you electronically by your teacher.

2. Save the database in your directory according to teacher directions

3. Open the Anti-Virus_Student database table

4. Add fields 1 and 3-6 (below) and set the data types and field properties. NA means to accept the default settings.

|Field |Field Name |Data Type |Field Properties |

| | | |Format |Field Size |Decimal Places |Caption |

| |Title |Memo |NA |NA |NA |Software Title |

| |Rating |Number |NA |Decimal |2 |NA |

| |Compatibility |Text |NA |NA |NA |NA |

| |Size |Text |NA |NA |NA |NA |

5. Enter the rest of the table data below:

|No |Popular Anti-virus Software |Price |Rating |

| |Titles | | |

|No |Number |AutoNumber |NA |

|Age |Same |Number |Long Integer |

|Income |Same |Currency |14 |

|Gender |Same |Text |2 |

|NoChdrn |Number of Children |Number |Long Integer |

|Status |Marital Status |Text |NA |

Note: Students were instructed to delete the Marital Status field

Guided Practice: Create a Database: MusicFun

DIRECTIONS: IN THIS ACTIVITY, YOU WILL FOLLOW ALONG WITH YOUR TEACHER TO CREATE A DATABASE AND COMPLETE THE FOLLOWING TASKS:

•

• Add fields

• Rearrange fields

• Add entries

• Apply field formats:

• Date with Input Mask

• AutoNumber (review)

• Lookup

• Yes/No

1. Open the MusicFun_Student database that has been sent to you electronically by your teacher.

2. Follow teacher instructions for saving the database in your student directory.

3. Add a field named MusicianID in front of the CD/Album Title field and set the data type to AutoNumber

4. Add another field named Genre after MusicianID (and in front of CD/Album Title)

a. Set the data type to Lookup and follow teacher directions to enter the lookup values

b. Key the following values for the lookup column:

i. Pop

ii. Classical

iii. Country

iv. R & B

v. Reggae

vi. Rock

Notes:

5. Add another field named Release after the Artist field

a. Set the data type to Date/Time

b. Set the caption to Release Date

c. Set the field property for Input Mask and short date format

Notes:

6. Add another field after Release

a. Name the field Under 25

b. Set the data type to Yes/No

c. Set the caption to Under 25?

d. Note that the Format field property automatically selects Yes/No

Notes:

7. Before entering the data, notice how a drop down menu appears in the Genre column. How does this benefit efficiency and accuracy?

8. Notice that the Under 25? column contains boxes. The default value is No. To change the value to yes, click or depress the space bar.

9. Enter the remaining data below. When keying the date, enter a zero before any single digit numbers, such as 04/03/2008.

|MusicianID |Genre |

| |Create a table |

| |Enter the data |

| |Enter the field names |

| |Open and save a new database |

| |Plan the structure of the database (sketch on paper) |

| |Set the data types |

| |Set the field properties |

Part II Directions: Select the appropriate data type or field property for the data listed below:

| | |Data | | |Data Type/Field Property |

| | |Children? | |B |Input Mask |

| | |List of ice cream flavors | |C |Lookup |

| | |List of pizza toppings | |D |Number |

| | |Married? | |E |Text |

| | |Net pay | |F |Yes/No |

| | |Price | | | |

| | |Street address | | | |

| | |Telephone number | | | |

| | |Zip code | | | |

Database Review Key

PART I DIRECTIONS: ARRANGE THE STEPS IN CREATING A DATABASE IN THEIR PROPER ORDER BY PLACING A NUMBER BY EACH ITEM.

|Step |Procedure |

|3 |Create a table |

|7 |Enter the data |

|4 |Enter the field names |

|2 |Open and save a new database |

|1 |Plan the structure of the database (sketch on paper) |

|5 |Set the data types |

|6 |Set the field properties |

Part II Directions: Select the appropriate data type or field property for the data listed below:

| | |Data | | |Data Type/Field Property |

|F | |Children? | |B |Input Mask |

|C | |List of ice cream flavors | |C |Lookup |

|C | |List of pizza toppings | |D |Number |

|F | |Married? | |E |Text |

|A | |Net pay | |F |Yes/No |

|A | |Price | | | |

|E | |Street address | | | |

|B | |Telephone number | | | |

|D | |Zip code | | | |

Determining Sort Order

DIRECTIONS FOR PART I: PLACE AN A IN THE ORDER COLUMN FOR EACH SCENARIO THAT IS AN EXAMPLE OF ASCENDING ORDER OR A D IF THE SCENARIO IS OF DESCENDING ORDER

|Order |Scenario |

| |The class should line up from the shortest student to the tallest for the vault contest. |

| |The library books were shelved in alphabetical order by author’s first name. |

| |The most expensive items were listed first. |

| |The family reunion photograph shows the babies in the front row and the great-grandparents in the last row. |

| |The cities with the most snow days were listed first. |

| |The first grade teacher’s mailboxes were assigned before the fifth grade teacher’s. |

| |Bus 13 left the parking lot first, Bus 37 was second and Bus 73 left last. |

| |The oldest student was allowed to go first in the lunch line, while the youngest was last to get his tray. |

| |The mountains were listed on the board. The ones with the highest elevation were listed first while the ones with the lowest |

| |elevation were listed last. |

| |The cities with the least snow days were listed first. |

Directions for Part II: Study each list below and determine the sort order. Write A for ascending and D for descending.

|Order |Scenario |Order |Scenario |

| | a). a full tank of gas | | a). a can of coke |

| |b). ¼ tank of gas | |b). a 16 oz. bottle of coke |

| |c). running on empty | |c). a 2 liter bottle of coke |

| | a). a penny | | a). Seniors |

| |b). a nickel | |b). Juniors |

| |c). a dime | |c). Freshmen |

| | a). 1923 | | a). 04/15/2006 |

| |b). 1945 | |b). 07/04/2006 |

| |c). 1990 | |c). 09/25/2006 |

| | a). David | | a). Rock |

| |b). Christine | |b). Pop |

| |c). Becky | |c). Classical |

| | a). 150 | |a). Abbington Green |

| |b). 110 | |b). Dry Ridge Inn |

| |c). 90 | |c). Secret Garden |

Determining Sort Order Key

DIRECTIONS FOR PART I: LABEL EACH SITUATION OR “SCENARIO” AS EITHER ASCENDING OR DESCENDING IN THE SPACES PROVIDED.

|A |The class should line up from the shortest student to the tallest for the vault contest. |

|A |The library books were shelved in alphabetical order by author’s first name. |

|D |The most expensive items were listed first. |

|A |The family reunion photograph shows the babies in the front row and the great-grandparents in the last row. |

|D |The cities with the most snow days were listed first. |

|D |The fifth grade teacher’s mailboxes were assigned before the first grades. |

|A |Bus 13 left the parking lot first, bus 37 was second and bus 73 left last. |

|D |The oldest student was allowed to go first in the lunch line, while the youngest was last to get his tray. |

|D |The mountains were listed on the board. The ones with the highest elevation were listed first while the ones with the lowest |

| |elevation were listed last. |

|A |The planets with the fewest moons were listed first. |

Directions for Part II: Study each list below and determine the sort order. Write A for Ascending and D for Descending

|Order |Scenario |Order |Scenario |

|D |a). a full tank of gas |A |a). a can of coke |

| |b). ¼ tank of gas | |b). a 16 oz. bottle of coke |

| |c). running on empty | |c). a 2 liter bottle of coke |

|A |a). a penny |D |a). Seniors |

| |b). a nickel | |b). Juniors |

| |c). a dime | |c). Freshmen |

|A |a). 1923 |A |a). 04/15/2006 |

| |b). 1945 | |b). 07/04/2006 |

| |c). 1990 | |c). 09/25/2006 |

|D |a). David |D |a). Rock |

| |b). Christine | |b). Pop |

| |c). Becky | |c). Classical |

|D |a). 150 |A |a). Abbington Green |

| |b). 110 | |b). Dry Ridge Inn |

| |c). 90 | |c). Secret Garden |

Understanding Operators

UNDERSTANDING MATHEMATICAL OPERATORS IS NECESSARY WHEN CONDUCTING DATABASE SEARCHES AND SORTS AND APPLYING FILTERS AND QUERIES. WHEN INSTRUCTED TO LOCATE INFORMATION IN A DATABASE WITH PHRASES SUCH AS SHORTER THAN, TALLER THAN, OLDER THAN ETC, YOU WILL NEED TO TRANSLATE THE PHRASE INTO WORDS THAT THE DATABASE UNDERSTANDS.

In this activity, you will demonstrate your skill at determining what operator to use by matching the mathematical symbol with the words or phrases below.

Directions for Part I: Match the symbols with their meanings

| |Less Than | |

| |Greater Than |= |

| |Less Than or Equal To |= |

| |Greater Than or Equal To |< |

| |Not Equal To |> |

Directions for Part II: Record notes on each of the terms below as you work through this and the next activity.

|Term |Definition |

|Filter | |

|Filter by selection | |

|Filter/Advanced Filter Sort | |

|Sort | |

|Criteria | |

Directions for Part III: Write the correct statement that would find data that meets the conditions of each criterion.

Example: Criteria: At most 36 Answer: |

Directions for Part II: Record notes on each of the terms below as you work through this and the next activity.

|Term |Definition |

|Filter |A tool used to search for specific information within a database |

|Filter by selection |Filters for the field or entry that has been selected |

|Filter/Advanced Filter Sort |Filters according to specific criteria assigned by the user |

|Sort |Arranging information in some form of logical order; alphabetical, |

| |chronological, numerical |

|Criteria |A condition that must be met or not met in a search |

Directions for Part III: Write the correct statement that would find each of the criteria.

Example: Criteria: At most 36 Answer: =100 |

|100 or less |2000 |

|Under 32 |75 |

|Smaller than 3 | 30000, Children > 0 |

|Part II |

|1. |Criterion: Field= Serial Number, |

| |Criteria =87093 |

|2. |Filter by selection is also appropriate. |

|Part II continued |

|3. |Room: 119 |

| |Manufacturer: Gateway |

| |Serial Number: 87093 |

|4. |The sort doesn’t work properly because the database is not able to recognize the order of the text. |

|5. |95425, 55557, 83765, 88334 |

Independent Practice: Filtering and Sorting a Database

YOU WILL COMPLETE THIS ACTIVITY INDEPENDENTLY TO APPLY WHAT YOU HAVE LEARNED ABOUT SORTING AND FILTERING A DATABASE. FOR EACH ITEM, YOU MUST COPY THE RESULTS OF THE AFFECTED FIELD(S) INTO A WORD DOCUMENT. YOU SHOULD END UP WITH 9 TABLES IN ONE WORD DOCUMENT. LABEL EACH TABLE APPROPRIATELY AND PROVIDE AN EXPLANATION OF HOW THE SORT/FILTER WAS PERFORMED FOR EACH.

Note: Be careful not to click a yes/no box by accident and change its value

Part I: Open the B&B database and complete the following sort tasks in the HistoricInns table.

1. Simple sort: Sort by the B&B Name in ascending order. Copy/paste the sorted B&B field to a word processing document.

2. Simple sort: Sort by the Number of Rooms field in descending order. Copy/paste the Inn field and Rooms field to a word processing document.

3. Complex sort: Move two appropriate fields adjacent to each other to accomplish the sort. Primary field: Rooms in ascending order; Secondary Field: Inn in ascending order.

If using Access, the left-most field will be sorted first, so for this sort, you must drag the Rooms field in front of the Inn field. Copy/paste the two fields to a word document.

Part II: Using the same database, complete the following filter by selection tasks

4. Historic Inns which do not allow pets

5. Historic Inns which allow pets

6. Historic Inns which allow children under the age of 10

7. Historic Inns located in town

(continued on the next page)

Part III: Using the same database, complete the following advanced selections

8. Apply an advanced filter that selects records according to the following conditions:

• Historic Inns that have more than 4 rooms available

• Descending order

• Allow both children and pets.

Write a criterion statement using operators

9. Apply an advanced filter that selects records according to the following conditions:

• Historic Inns that have less than 6 rooms available

• Ascending order

• Located in a small town/country place

• Allows children.

Write a criterion statement using operators

10. Save and submit your work according to teacher directions.

Independent Practice: Filtering and Sorting a Database Key

| (1) SORT BY INN NAME |

|in Ascending Order |

|Inn |

|A Bed of Roses |

|Abbington Green |

|Albermarle Inn |

|Biltmore Village Inn |

|Blake House Inn |

|Carolina Bed & Breakfast |

|Cedar Crest Victorian Inn |

|Chestnut Street Inn |

|Colby House |

|Dry Ridge Inn |

|Inn on Main Street |

|Inn on Montford (1900) |

|North Lodge on Oakland |

|Owl's Nest Inn at Engadine |

|Secret Garden (1904) |

|The Hawk & Ivy |

|The Lion and The Rose |

|White Gate Inn and Cottage (1889) |

|(2) Sort by No. of Rooms |

|Inn |Rooms |

|Cedar Crest Victorian Inn |12 |

|Albermarle Inn |11 |

|Abbington Green |8 |

|Dry Ridge Inn |8 |

|Chestnut Street Inn |8 |

|Carolina Bed & Breakfast |7 |

|Owl's Nest Inn at Engadine |7 |

|Inn on Main Street |7 |

|White Gate Inn and Cottage (1889) |6 |

|Biltmore Village Inn |6 |

|North Lodge on Oakland |6 |

|Blake House Inn |6 |

|Colby House |5 |

|A Bed of Roses |5 |

|The Lion and The Rose |5 |

|Inn on Montford (1900) |5 |

|The Hawk & Ivy |4 |

|Secret Garden (1904) |3 |

| (3) Sort first by rooms and then by Inn in ascending order |

|Rooms |Inn |

|3 |Secret Garden (1904) |

|4 |The Hawk & Ivy |

|5 |A Bed of Roses |

|5 |Colby House |

|5 |Inn on Montford (1900) |

|5 |The Lion and The Rose |

|6 |Biltmore Village Inn |

|6 |Blake House Inn |

|6 |North Lodge on Oakland |

|6 |White Gate Inn and Cottage (1889) |

|7 |Carolina Bed & Breakfast |

|7 |Inn on Main Street |

|7 |Owl's Nest Inn at Engadine |

|8 |Abbington Green |

|8 |Chestnut Street Inn |

|8 |Dry Ridge Inn |

|11 |Albermarle Inn |

|12 |Cedar Crest Victorian Inn |

|4) B&Bs with NO pets |

|Pets |Name |

|No |A Bed of Roses |

|No |Albermarle Inn |

|No |Cedar Crest Victorian Inn |

|No |Chestnut Street Inn |

|No |Colby House |

|No |Dry Ridge Inn |

|No |Inn on Main Street |

|No |Inn on Montford (1900) |

|No |North Lodge on Oakland |

|No |Secret Garden (1904) |

|No |The Hawk & Ivy |

|No |The Lion and The Rose |

|No |White Gate Inn and Cottage (1889) |

|(5) B&Bs with pets |

|Pets |Name |

|Yes |Abbington Green |

|Yes |Biltmore Village Inn |

|Yes |Blake House Inn |

|Yes |Carolina Bed & Breakfast |

|Yes |Owl's Nest Inn at Engadine |

|(6) B&Bs allowing children |

|Name |Children |

|Abbington Green |Yes |

|Blake House Inn |Yes |

|Carolina Bed & Breakfast |Yes |

|Chestnut Street Inn |Yes |

|Colby House |Yes |

|Dry Ridge Inn |Yes |

|North Lodge on Oakland |Yes |

|The Hawk & Ivy |Yes |

|The Lion and The Rose |Yes |

|White Gate Inn and Cottage (1889) |Yes |

|(7) B&Bs in small town/country setting |

|Name |Setting |

|Inn on Main Street |Town |

|Owl's Nest Inn at Engadine |Town |

|Secret Garden (1904) |Town |

|The Hawk & Ivy |Town |

|(8) B&Bs >4 rooms, pets, children |

|Pets |

|Pets |

|Authors Table |

|Authors |Publishers |

|Evanovich, Janet |Penguin Putnam Inc |

|Hautman, Pete |Fulcrum Books |

|Hillerman, Tony |Random House |

|Hopkins, Ellen |Penguin Putnam Inc |

|Hosseini, Khaled |Harper Books |

|Meyer, Stepheni |Reader's Choice |

|Rowling, J K |Markee Brothers |

1. Create another table named Publications

a. Do not set a primary key

b. Use a lookup data type for the authors and key the authors’ names in the values list

c. Enter the data in the table

d. Save the table

2. Open the relationships window and establish a one-to-many relationship between the Authors and Publications tables using the primary key from the Authors table

3. Open the Authors table and expand the relationship for each author

4. Save and submit your work according to teacher directions

5. Think about how a bookstore would use the information and record your thoughts in the space below.

Independent Practice: One-to-Many Relationships Key

Guided Practice: Many-to-Many Relationships

DIRECTIONS: FOR THIS ACTIVITY, YOU WILL FOLLOW ALONG WITH YOUR TEACHER TO ESTABLISH A MANY-TO-MANY RELATIONSHIP. IN A MANY-TO-MANY RELATIONSHIP, TWO TABLES WITH A ONE-TO-MANY RELATIONSHIP ARE LINKED BY A JUNCTION TABLE.

1. Open the Beach Rentals_Student database that has been sent to you electronically by your teacher

2. In the Customers table, set the CustomerID field as the primary key field

3. In the Properties table, set the PropertyID field as the primary key field

4. Create a Junction Table:

a. Use AutoNumber for the ID field and set it as the primary key

Notes:

Note: An easy way to identify a junction table is to view the fields in each table. The table that contains all of the primary keys from the other related tables is the junction table!

b. Use a lookup data type for the PropertyName field and key the property names as the values

Notes:

c. Use an input mask, short date format for the date field

Notes:

5. Establish a one-to-many relationship between the Customers table and the Rentals table using the primary key from the Customers table

Notes:

6. Select the option to enforce referential integrity for both relationships

7. Establish a one-to-many relationship between the Properties table and the Rentals table using the primary key from the Properties table

8. Save and answer the questions on the next page.

Open the Customers table and try to delete Bob Dixon. What happens? Why?

Expand all of the relationships in the Customers table. Which renter has rented the most properties?

Expand all of the relationships in the Properties table. Which property is the most popular?

How can this type of relational database be used in business?

Why is it important to understand database relationships?

Why is it important to enforce referential integrity in this database?

Guided Practice: Many-to-Many Relationships Key

OPEN THE CUSTOMERS TABLE AND TRY TO DELETE BOB DIXON. WHAT HAPPENS? WHY?

Bob Dixon cannot be deleted because he is linked to another table.

Expand all of the relationships in the Customers table. Which renter has rented the most properties?

Cricket Chianelli

Expand all of the relationships in the Properties table. Which property is the most popular?

Sea Captain

How can this type of relational database be used in a business?

A beach rental company would use this database to keep track of customers and property owners. The beach rental company could quickly look up information sorted by property or by customer.

Why is it important to understand database relationships?

Databases are used by businesses worldwide to manage information. Database relationships increase the effectiveness and power of databases by allowing information to be organized into manageable and logical chunks of related data (tables) and by enabling communication between multiple tables.

Why is it important to enforce referential integrity in this database?

To help protect data from being deleted or corrupted

Independent Practice: Many-to-Many Relationships

DIRECTIONS: FOR THIS ACTIVITY, YOU WILL WORK INDEPENDENTLY TO CREATE A MANY-TO-MANY RELATIONSHIP.

1. Open the Flights_Student database that has been sent to you electronically.

2. Follow teacher instructions for saving the database to your student directory.

3. Create a database table named Pilots

a. Set the PilotID data type as number and make it the primary key field

b. Do not make any errors as you key the information from the table below:

|PilotID |Pilot Name |

|332341 |Motley, Dan |

|384858 |Main, Hydie |

|528966 |Long, John |

|754858 |Penny, Annie |

|838488 |Swain, Bob |

|859699 |Young, Debra |

|908948 |Lox, Bailey |

|958678 |Anderson, Lee |

4. Save and close the Pilots table.

5. Establish a one-to-many relationship between the Flights table and the Reservations table using the Flight# field in the Flights table as the link to the FlightNo field in the Reservations table

6. Establish a one-to-many relationship between the Reservations table and the Pilots table using the PilotID field in the Pilots table as the link to the Pilot field in the Reservations table

7. Enforce referential integrity in the link between the Pilots table and the Reservations table

Answer the following questions:

1. What is the function of referential integrity in this activity?

a. It prevents a pilot from being deleted from the pilots table if the pilot has been assigned to a flight in the reservations table

b. It allows a pilot to be deleted from the pilots table if the pilot has been assigned to a flight in the reservations table

2. Perform a filter for the flights listed below to answer the questions about the Flights table

a. How many pilots does Northwest Flight 99 have?

b. How many passengers does Northwest Flight 99 have?

c. How many passengers does US Airways Flight 5474 have?

3. Open the Pilots table and answer the following questions:

a. Which pilot has the most passengers?

b. Which pilot has the least?

4. How is the expanded relationship information helpful to the following people:

a. Customers

b. Pilots

c. Airline stewards

d. Reservations specialists

5. Save and submit your work according to teacher dirctions

Independent Practice: Many-to-Many Relationships Key

ANSWER THE FOLLOWING QUESTIONS:

1. What is the function of referential integrity in this activity?

a. It prevents a pilot from being deleted from the pilots table if the pilot has been assigned to a flight in the reservations table

b. It allows a pilot to be deleted from the pilots table if the pilot has been assigned to a flight in the reservations table

2. Perform a filter for the flights listed below to answer the questions about the Flights table

a. How many pilots does Flight 99 have? 1

b. How many customers does Flight 99 have? 1

c. How many customers does Flight 5474 have? 11

3. Open the Pilots table and answer the following questions:

a. Which pilot has the most passengers? Dan Motley

b. Which pilot has the least? Debra Young

4. How is the expanded relationship information helpful to the following people?

a. Customers

Customers can find out flight information, such as flight number and pilot information. If concerned about safety, they may choose to check out the pilot on the Internet.

b. Pilots

Pilots can quickly see how full their flight is

c. Airline stewards

Airline stewards can check the flight roster

d. Reservations specialists

Reservations specialists can view flight data to find out how many spaces are left and answer customer inquiries about flight arrival and departure times

Guided Practice: Importing Data

DATA USED IN A DATABASE TABLE CAN BE IMPORTED FROM OTHER SOURCES. IN THIS EXERCISE, YOU WILL FOLLOW ALONG WITH YOUR TEACHER TO IMPORT A WORD PROCESSING FILE INTO A DATABASE. THE PROCEDURES FOR IMPORTING DATA REQUIRE THAT THE DATA BE ARRANGED IN ROWS AND COLUMNS. WHEN IMPORTING A DATA PROCESSING DOCUMENT, THE FILE FORMAT MUST BE SAVED AS TEXT.

Directions:

1. Using word processing software, key the information below (raw data) in the exact format with the commas, spacing, and line returns.

Raw Data:

Event No.,Event,Level,Transcript,Objective Test

1,Accounting I,HS,Y,Y

2,Accounting II,HS,Y,Y

4,Banking and Financial Systems,HS,N,Y

5,Business Calculations,HS,N,Y

6,Business Communication,HS,N,Y

10,Business Law,HS,Y,Y

11,Business Math,HS,Y,Y

14,Business Procedures,HS,Y,Y

2. Save the data in text format.

3. Open the database software and name the database FBLA unless otherwise instructed.

4. Follow procedures for importing the data into a database table.

Notes:

5. Store the data in a new table. Name the table Competitive Events unless instructed otherwise.

Independent Practice: Importing Data

DIRECTIONS: IN THIS EXERCISE, YOU WILL WORK INDEPENDENTLY TO IMPORT A WORD PROCESSING FILE INTO A DATABASE. KEY THE DATA BELOW USING WORD PROCESSING SOFTWARE AND SAVE IT IN TEXT FORMAT USING A FILE NAME ASSIGNED BY YOUR TEACHER. IMPORT THE DATA INTO A DATABASE TABLE AND SUBMIT YOUR WORK ACCORDING TO TEACHER DIRECTIONS. COMPLETE THE WRITTEN RESPONSE SECTION AT THE END OF THIS ACTIVITY.

Points to Remember:

• Remember, you must have an open database before you can import a file.

• Once the data has been imported, format the field properties so that the Enrollment field has comma separators and zero decimal places and the Tuition fields are formatted as currency.

• Name the database according to teacher directions.

• Answer the questions at the end of this activity and submit your work.

Raw Data:

Name,Ownership,Type,Enrollment,InState,OutofState

Appalachian State,Public,4 yr,14653,$4000,$13600

East Carolina,Public,4 yr,22767,$4487,$15000

Fayetteville,Public,4 yr,5441,$3860,$13596

A & T,Public,4 yr,10383,$4200,$13000

UNC Chapel Hill,Public,4 yr,26878,$5513,$19311

UNC Greensboro,Public,4 yr,15329,$49440,$16500

UNC Wilmington,Public,4 yr,11574,$4694,$14405

Winston-Salem State,Public,4 yr,4805,$3905,$12545

Written Response:

Write a step by step procedure for importing data from a word processing file into a database table.

Independent Practice: Importing Data Key

Procedure for Importing Data into a Database Table: (May vary with software)

1. Format the raw source data in columns and rows separated by spaces or commas.

2. Save the raw source data in plain text format.

3. Open and save a new database.

4. Use the menu or wizard options to import the source data.

5. Select the appropriate options regarding type of delimiters and headings.

6. Name the table.

7. Complete the import.

8. Format the data if required.

Database Concept Review

DIRECTIONS: ANSWER THE QUESTIONS BELOW.

1. In your own words provide the definition of a database.

2. List 3 examples of a database.

3. What is a database relationship?

4. Work with a partner and describe a one-to-many relationship using a database and tables you make up. Draw the tables on paper and key descriptions of each, then cut and paste the descriptions onto the paper.

5. Work with a partner and describe a many-to-many relationship using a database and tables you make up.

6. Work with a partner and draw a picture of the database, the tables, and the many-to-many relationship on the back of this sheet.

7. Give an example of how referential integrity could be used in your scenario (#6)

8. Number the following database elements in order from largest to smallest.

a. _____ Field

b. _____ Database

c. _____ Record

d. _____ Entry

9. Match the field types with the correct definition.

| |Text | |Allows you to create a finite set of choices for that field’s entry values. |

| |Number | |Is a true/false field |

| |Lookup Wizard | |Date and time entries in various formats. |

| |Date/Time | |This includes not only words, but also numbers not used in calculations. |

| |Currency | |This field may not include anything other than numbers. |

| |AutoNumber | |Like text, but max of 64,000 characters. |

| |Yes/No | |Creates fields that automatically enter a unique number when a record is added. |

| | | |Currency values expressed in various formats. |

Database Concept Review Key

DIRECTIONS: ANSWER THE QUESTIONS BELOW.

1. In your own words provide the definition of a database.

• Student answers will vary

2. List 3 examples of a database.

• Telephone book|

• Computerized Library card catalog

• Student data (NCWISE)

• iTunes

• Personal address book

• CD-Rom Encyclopedias

• Job Search

• Fingerprint database

• Customer contact list

• Houses for sale and MLS listings online

• Hospital/patient data

• Business stock inventory

• Barcode scanners keep inventory in databases

• Internet search engines database

• Encyclopedias & Dictionaries

3. A database relationship is a link between common fields of two or more tables in a database.

4. Answers will vary – invite students to share their examples with the class

5. Answers will vary

6. Answers will vary

7. Answers will vary

8. Answers will vary

9. Number the following database elements in order from largest to smallest.

1 Database

2 Record

3 Field

4 Entry

10.

11. Match the field types with the correct definition.

|D |Text | |Allows you to create a finite set of choices for that field’s entry values. |

|E |Number | |Is a true/false field |

|A |Lookup Wizard | |Date and time entries in various formats. |

|C |Date/Time | |This includes not only words, but also numbers no used in calculations. |

|H |Currency | |This field may not include anything other than numbers. |

|G |AutoNumber | |Like text, but max of 64,000 characters. |

|B |Yes/No | |Creates fields that automatically enter a unique number when a record is added. |

| | | |Currency values expressed in various formats. |

-----------------------

*

C

A

B

C

A

B

Tables: the Building Blocks of all Databases

*

What is a database relationship and what are the benefits?

*

With records deleted

With records added

Computers Database

Main Topic:

Organization of a Database

Definition:

Field – Definition and Example

Record – Definition and Example

Entry – Definition and Example

Tables

Four

Database Objects:

1.

2.

3.

4.

Business Uses of Databases

Can you associate company names with the database examples?

Telephone book: Customer data:

Student Data: Real estate listings:

Music: Hospital/patient data:

Fingerprints: Inventory:

Dictionaries: :

How to set up a relationship:

What is referential integrity?

What is a primary key and why is it used?

What is a foreign key and why is it used?

One-to-Many

Many-to-Many

Types of Relationships

Select All Button

Steps in Creating a Database

1. Plan the structure of the database (sketch on paper)

a) How will the data be grouped?

b) What will the field names be?

c) What will you name the database?

2. Open and save a new database

3. Create a table

4. Enter the field names

a) Set the data types

b) Set the field properties

5. Enter the data

What is the purpose of a Junction Table?

Just list the names of the objects here; don’t worry about definitions yet.

Table Components

Queries, Forms, and Reports will be discussed in 5.02

................
................

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

Google Online Preview   Download