microsoft office 2003virgil.azwestern.edu/~cvb/cis120/book notes/np11_im_chapter11… · web...

New Perspectives on Computer Concepts (11th Edition) Instructor’s Manual 1 of 16

Computer ConceptsChapter Eleven: Databases

A Guide to this Instructor’s Manual:We have designed this Instructor’s Manual to supplement and enhance your teaching experience through classroom activities and a cohesive chapter summary. This document is organized chronologically, using the same heading in blue that you see in the textbook. Under each heading you will find (in order): Lecture Notes that summarize the section, Figures and Boxes found in the section (if any), Teacher Tips, Classroom Activities, and Lab Activities. Pay special attention to teaching tips, and activities geared towards quizzing your students, enhancing their critical thinking skills, and encouraging experimentation within the software. In addition to this Instructor’s Manual, our Instructor’s Resources CD also contains PowerPoint Presentations, Test Banks, and other supplements to aid in your teaching experience.

For your students: Our latest online feature, CourseCasts, is a library of weekly podcasts designed to keep your students up to date with the latest in technology news. Direct your students to http://coursecasts.course.com, where they can download the most recent CourseCast onto their mp3 player. Ken Baldauf, host of CourseCasts, is a faculty member of the Florida State University Computer Science Department where he is responsible for teaching technology classes to thousands of FSU students each year. Ken is an expert in the latest technology and sorts through and aggregates the most pertinent news and information for CourseCasts so your students can spend their time enjoying technology, rather than trying to figure it out. Open or close your lecture with a discussion based on the latest CourseCast.

Table of Contents Chapter Objectives 2Section A: File and Database Concepts 2Section B: Data Management Tools 4Section C: Database Design 6Section D: SQL 10Section E: Database Security 12Glossary of Key Terms 15

http://coursecasts.course.com/

New Perspectives on Computer Concepts (11th Edition) Instructor’s Manual 2 of 16

Chapter Objectives Students will have mastered the material in Chapter Eleven when they can:

Define basic database terminology, such as fields, records, record types, and cardinality

Describe six database models and their applications

Explain the capabilities of various data management tools, such as commercial applications, word processing software, spreadsheet software, file management software, and database management software

Describe various ways to provide access to databases over the Web

Explain how to design an effective relational database

List the principles for creating effective report templates

Describe how to add records, delete records, search for information, update fields, and simultaneously access data from multiple tables using SQL queries

Explain how database vulnerabilities affect individuals and organizations

Describe the legal, regulatory, and procedureal methods that pertain to database security

List the steps you can take when working with databases to protect your privacy and identity

SECTION A: FILE AND DATABASE CONCEPTS (610)

Database Basics (610) LECTURE NOTES

Explain that a database is a collection of information, typically stored as a computer file.

Identify databases that are not computer files, such as card files, address books, and rolodexes.

Introduce Vintage Music Shop, a fictitious music store on the Web. It is an example that continues throughout each section.

Explain that data mining is the process of analyzing information in databases to discover previously unknown information, including relationships and patterns.

Introduce OLAP, which is covered on page 613.

TEACHER TIPBased on the description of Vintage Music Shop on page 610, work as a class to list the kinds of data Vintage Music Shop maintains.

FIGURES Figure 11-1, Figure 11-2, Figure 11-3, Figure 11-4

CLASSROOM ACTIVITIES1. Class Discussion: Ask students to brainstorm applications for databases? Do

students have a need for a database for home use? What types of organizations use databases?

2. Quick Quiz: A(n) _________ is a collection of information. (Answer: database.) True/False: The data in a typical database is stored in no particular order.

(Answer: True.) Which of the following refers to the process of analyzing existing information in

databases to discover previously unknown, and potentially useful information, including relationships and patterns?a. queryingb. data miningc. analysisd. All of the above(Answer: B.)

Database Models (614) LECTURE NOTES

Explain that a structured file uses a uniform format to store data for each item in the file.

Explain that a field contains the smallest unit of meaningful information and that each field has a unique field name.

Mention to students the pros and cons of limiting fields (such as a LastName field) and of allowing unlimited field lengths.

Explain that a flat file is a data file that contains only one record type. Contrast a flat file with a database. A database can contain many record types.

This allows a database to create relationships, associations between data that is stored in different record types.

Explain that in database terminology, a record refers to a collection of data fields, and each record stores data about one entity—a person, place, thing, or event.

Distinguish record occurrence and record type. A record occurrence is a particular instance of a record that contains data describing an entity. A record type is the general record format—a form that contains blanks for data, but no actual data. Figure 11-8 illustrates the difference between a record occurrence and a record type.

Explain that cardinality refers to the number of associations that two record types can have. The three different relationships are one-to-one, one-to-many, and many-to-many. Figure 11-9 shows an ER diagram where the data structure contains rectangles that represent entities (record types) and lines that represent relationships.

Use Figures 11-10 and 11-11 to illustrate hierarchical and network database models.

Use Figures 11-12 and 11-13 for a detailed walkthrough of relational databases.

TEACHER TIPAsk students to cite cases for Vintage Music Shop in which it would be wise to limit fields to a maximum length, and other cases when leaving a field length unlimited would be most appropriate. Relate the terminology of fields, records, and files to the structure of data discussed earlier in the text.

FIGURES Figure 11-5, Figure 11-6, Figure 11-7, Figure 11-8, Figure 11-9, Figure 11-10,

Figure 11-11, Figure 11-12, Figure 11-13, Figure 11-14, Figure 11-15, Figure 11-16

CLASSROOM ACTIVITIES1. Assign a Project: Have the students create lists of field names for a personal

database. Is more than one table needed to organize the fields? For example, a telephone database, music database, recipe table? Within each of these tables there could be common fields like the name of the person who gave you the CD or recipe.

2. Quick Quiz: Each field has a unique ______ name that describes its content. (Answer: field.) True/False: In database jargon, a relationship is an association between data

that is stored in different record types. (Answer: True.) Which of the following kinds of databases excels in representing items that

have slightly different attributes?a. dimensionalb. objectc. hierarchicald. relational(Answer: B.)

SECTION B: DATA MANAGEMENT TOOLS (622)

Data Management Software (622) LECTURE NOTES

Discuss personal organizers available as standalone products or as part of PDAs, and other simple applications that let users keep small numbers of records.

Explain that a flat data file using tables can be created in a word-processing program, such as Microsoft Word, or a spreadsheet program, such as Microsoft Excel.

Point out that custom software is specially written for a particular file. The software and data are dependent on each other (if you change the data or file structure you must also change the program). This approach is fairly costly compared to other approaches.

Explain that one goal for databases is that they have data independence; that is, they separate data from the programs that manipulate the data. Custom software often suffers from data dependence, which results in data that is difficult to modify.


CLASSROOM ACTIVITIES1. Assign a Project: Have the students think of an application for which they could use

a database at home. Then have them refer to the table in Figure 11-20 and determine what type of tool best fits their needs.

2. Quick Quiz: _____________ refers to data and program modules being so tightly interrelated

that they become difficult to modify. (Answer: Data dependence.) True/False: Modern database software supports data independence. (Answer:

True.)True/False: Data dependence is usually found in well written software. (Answer: False.)

Database Management Systems (625)LECTURE NOTES

Explain what database management systems are. They use generalized data management software to manage the data in one or more related files. Database management software generally supports one of the four database models, but most common today is database management software that supports the relational database model.

Use Figure 11-21 to show an example of Microsoft Access, an entry-level DBMS, or demonstrate Access in class.

Explain that whereas an entry-level DBMS is designed for personal and small business use, database server software is designed to manage billions of records and several hundred transactions per second.

TEACHER TIPAsk students to identify the types of data that are best suited for an entry-level DBMS, and those best suited for database server software.

FIGURES Figure 11-21, Figure 11-22, Figure 11-23

CLASSROOM ACTIVITIES1. Assign a Project: Have students write a brief paragraph explaining what each of the

following is: DBMS, XML DBMS, ODBMS, RDBMS.

2. Quick Quiz: A(n) _____ allows you to create, update, and administer a relational database.

(Answer: RDBMS or relational database management system.) True/False: An entry-level DBMS usually includes all the tools you need to

manipulate data in a database. (Answer: True.)What does DBMS stand for? (Answer: Database management system.)

Databases and the Web (627)LECTURE NOTES

Explain that Web-enabled database tools give users the advantages of database management combined with access to the World Wide Web. These tools are a particularly useful resource for e-commerce.

Point out that Web sites can provide access to databases through static Web publishing and dynamic Web publishing.

Use Figure 11-25 to discuss Web publishing. Explain the purpose of server-side programs and the tools used to create them.

TEACHER TIPVisit a Web site that uses forms, such as Expedia.com or Travelocity.com. Refer to page 628 and explain the process the Web site uses to display the form and send the form data to a database.


XML (630)LECTURE NOTES

Explain that XML is a markup language that allows you to incorporate field tags, data, and tables in a Web document.

Use Figure 11-28 to explain that XML helps organize data in context. Explain that an XML document can also contain structured data organized into

fields and records, replacing a traditional database.


CLASSROOM ACTIVITIES1. Assign a Project: Have students browse the Internet and find database

management software. Have them list price and system requirements, as well.2. Quick Quiz:

Database ______ software allows any remote computer or network workstation to access data in a database. (Answer: client.)

True/False: An ODBMS allows you to create, update, and administer a relational database. (Answer: False.)

The script that can get user input, run queries, and display query results is ______.a. ASPb. CGIc. PHPd. HTML(Answer: A.)

SECTION C: DATABASE DESIGN (633)

Defining Fields (633) LECTURE NOTES

Explain to students that you start defining fields by determining what data you want to collect and store. Then you organize that data into fields, breaking data when appropriate.

Use Figure 11-31 to illustrate the benefits of breaking data into fields. Make sure students understand what a primary key is. Review the data types listed on page 634.

FIGURES Figure 11-31, Figure 11-32, Figure 11-33, Figure 11-34, Figure 11-35, Figure 11-36

CLASSROOM ACTIVITIES1. Class Discussion: List the commonly used data types and have students call out

examples of each data type.2. Quick Quiz:

The term _____________ refers to the arrangement of fields, tables, and relationships in a database. (Answer: database structure.)

True/False: A data type specifies where data is stored. (Answer: False.)True/False: The hyperlink data type stores URLs use to link directly from a database to a Web page. (Answer: True.)

Normalization (637)LECTURE NOTES

Review the goals of normalization. Eliminating data redundancy as part of the normalization process will make it easier to update a file, and specifying a range check to limit the range of valid entries will make it more difficult for data entry errors to occur.

Explain that the next step in designing a database is to group the fields into tables, considering that data redundancy should be avoided. Also identify any relationships among the tables.


CLASSROOM ACTIVITIES1. Assign a Project: Have the students list the groups of fields they would use in their

hypothetical, home-use databases.

2. Quick Quiz: _____________ helps database designers create a database structure that can

save storage space and increase processing efficiency. (Answer: Normalization.)

True/False: The goal of normalization is to minimize data redundancy. (Answer: True.)

______________ is the amount of data that is repeated or duplicated in a database. (Answer: Data redundancy.)

Organizing Records (639)LECTURE NOTES

Discuss sorting. One powerful feature of databases is that they can present data in various ways depending on how their tables are sorted or indexed.

Explain that the sort order is the order in which records are stored on disk. Point out that databases work more efficiently when their data is sorted. Explain that a sort key usually determines how the records are sorted. For

example, a table can use the record number as the sort key, and then store the records in numerical order.

Explain that a database index contains a list of keys, and each key references the record that contains the rest of the fields related to that key. Use Figure 11-40 to illustrate this concept.

Point out that unlike a sort order, the index is not related to how the records are stored on disk. Tables are usually indexed by any fields that are commonly used as search fields, such as Customer Name, not an arbitrary record number.

TEACHER TIPMake sure students understand that a sort key is not the same thing as a primary key. The sort key is one or more fields used to specify where new records are inserted in a table. The primary key is a field that contains data unique to a record.

FIGURES Figure 11-40

CLASSROOM ACTIVITIES1. Assign a Project: Have Students write a paragraph explaining how the sort order

differs from indexing.2. Quick Quiz:

A table’s _______ is one or more fields used to specify where new records are inserted in a table. (Answer: sort key.)

True/False: The sort key and primary key are the same thing. (Answer: False.) What is the sort order? (Answer: The sort order is the order in which records are

stored on disk.)

Designing the Interface (640)LECTURE NOTES

Explain that the database interface is the window into which users will enter data. For students interested in programming, mention Visual Basic as the language used to create user interface forms.

Emphasize that it’s important to keep the user in mind. A well-designed user interface for a database is clear, intuitive, and efficient. The order of the fields should make sense for the user of the database, not the designer.

Explain that the design should also make clear which entry box corresponds to which label. The labels themselves should be meaningful and informative, even providing instructions for how to complete a field.

TEACHER TIPShow examples of database interfaces, if possible, and ask students to evaluate them. If you don’t have access to a variety of databases, use Figures 11-41 and 11-42, which show the same data entry screen designed in two ways.

FIGURES Figure 11-41, Figure 11-42

CLASSROOM ACTIVITIES1. Class Discussion: On an overhead display device, show some examples of database

user interfaces. Have students determine whether they are well or poorly designed and list the reasons why.

2. Quick Quiz: True/False: The way that database records, queries, and reports appear on the

screen depends on the user interface. (Answer: True.) True/False: Entry areas should appear in a consistent position relative to their

labels. (Answer: True.) True/False: Including on-screen instructions in a database interface is not

recommended. (Answer: False.)

Designing Report Templates (642)LECTURE NOTES

Explain that a report generator in a database helps you create the reports. Explain that like forms, reports should also be designed with the user in mind. Use the list on page 643 to identify the hallmarks of a well-designed report.


CLASSROOM ACTIVITIES1. Class Discussion: On an overhead display device, show some examples of report

templates. Have students determine whether they are well or poorly designed and list the reasons why.

2. Quick Quiz: A(n) ____________ is a software tool for specifying the content and format for a

database report. (Answer: report generator.) A(n) __________ contains the outline or general specifications for a report,

including such elements as the report title, fields to include, fields to subtotal or total, and report format specifications.. (Answer: report template.)

True/False: Data is merged into a repot template as soon as you complete the template. (Answer: False.)

Loading Data (644)LECTURE NOTES

Demonstrate how to import a flat file into an Access database. Demonstrate the import and export features of Access in class. Explain that entering data can be tedious and cumbersome. To avoid data

redundancy and errors, you can use an importing and exporting tool.

Discuss the linking relationship between programs when using the import feature.

TEACHER TIPYou could also show how to use data created in other programs, such as a Word table or an Excel spreadsheet, in an Access database.

LAB ACTIVITYThe Lab “Working with Database Software” deals with issues that relate to this section of the textbook. You might want to go through the lab during class time if you have a computer with a projection device. Or, assign this lab for students to do on their own.

CLASSROOM ACTIVITIES1. Assign a Project: Have students define a list of fields, organize the records, and

remove any redundancy. Design an input screen and enter five records.2. Quick Quiz:

Fixed length fields that hold character data, such as people’s names, should be assigned the ______ type. (Answer: text data.)

True/False: A computed field is a calculation that a DBMS performs during processing, and then temporarily stores in a memory location. (Answer: True.)

If data exists electronically in another type of database file or in flat files, it is usually possible to transfer the data using a(n) ______.a. custom-written conversion routineb. import routinec. export routined. All of the above(Answer: D.)

SECTION D: SQL (645)

SQL Basics (645) LECTURE NOTES

Explain that an SQL query is a command that retrieves certain information from a database. For example, the query SELECT * FROM Employee where Gender = 'F' would select records for all female employees.

Explain that queries allow you to lookup or search for specific data in the database.

Familiarize students with the SQL command words listed in Figure 11-46. In addition, they should be familiar with USE, FROM, and INTO.

TEACHER TIPTake time in class to dissect an SQL statement such as SELECT * FROM Employee where Gender = 'F' so students understand how that statement works to select certain records.


CLASSROOM ACTIVITIES1. Class Discussion: On an overhead display device or whiteboard, show some

example SQL queries and have students determine what actions those queries take.

2. Quick Quiz: ________ are detailed specifications for a command.. (Answer: Parameters.) What does SQL stand for? (Answer: Structured Query Language.) True/False: An SQL query typically begins with an action keyword. (Answer:

True.)

Adding Records (647)LECTURE NOTES

Explain that SQL statements using the keyword INSERT add data to a database. Refer to Figure 11-47 for an example of an INSERT statement and how it relates to

the WebMusic database. Use the SQL feature in Access to show students how to construct an INSERT

statement. Use the sample Northwind database in Access and add a record to one of its tables.


CLASSROOM ACTIVITIES1. Quick Quiz:

What command adds data to a table? (Answer: INSERT.) True/False: You cannot add records to a database. (Answer: False.)

Searching for Information (648)LECTURE NOTES

Explain that SQL statements use the keyword SELECT to search for a record or group of records.

Dissect the statement SELECT CDName, CDCover FROM CompactDisks WHERE ArtistName = ‘Natalie Merchant’ as an example of an SQL SELECT statement.

Review Boolean operators. SQL can also perform more complex searches using Boolean operators, which students might be familiar with from searching the Internet.

Point out that a key to success is structuring focused queries. Reviewing Boolean logic (and, or, not) helps students create focused queries.

TEACHER TIPConsider spending class time reviewing examples of the appropriate use of operators.


CLASSROOM ACTIVITIES1. Assign a Project: Have students write a paragraph explaining how OR and AND

differ.2. Quick Quiz:

One of the most common database operations is to query for a particular record or group of records by using the ________ command. (Answer: SELECT.)

What are three SQL search operators? (Answers: AND, OR, NOT.) True/False: SQL cannot perform complex searches. (Answer: False.)

Updating Fields (650)LECTURE NOTES

Point out that SQL statements using the UPDATE keyword change the records in a database (if you have the right to do so).

Analyze the sample UPDATE statements on page 650 to see how the first one reduces the number of albums in stock. The second UPDATE statement performs a global update.



A(n) ________ changes the data in more than one record at a time. (Answer: global update.)

What command means you want to change the data in some or all records? (Answer: UPDATE.)

True/False: You can change records in a database only if you have authorization to do so. (Answer: True.)

Joining Tables (651)LECTURE NOTES

Explain that in SQL terminology, creating a relationship between tables is called joining tables.

Use Figure 11-50 to show an example of two tables that have a relationship. Students should know how to use the SQL JOIN command to join and access the

data in more than one table. Figure 11-51 shows the JOIN command used with other SQL commands.


LAB ACTIVITY Refer students to the New Perspectives Web site for a Student Edition Lab called

“Advanced Databases.”

CLASSROOM ACTIVITIES1. Assign a Project: Have students design a query and a report using Design view.

Then students should view the SQL code and review each statement.2. Quick Quiz

The client software collects input, then converts it into a(n) _____ query, which can operate directly on the database to carry out instructions. (Answer: SQL.)

True/False: The SQL command that adds data to a table is the ADD command. (Answer: False.)

Using the following SQL code:SELECT INVName FROM InventoryDisksWHERE (ProductName = ‘Shirt’ AND ProductPrice < 100.00)The outcome will produce which of the following data :a. Shirt, 150.00b. Shirt, 100.00c. Hat, 125.00d. Shirt, 99.00(Answer: D.)

SECTION E: DATABASE SECURITY (653)

Database Vulnerabilities (653) LECTURE NOTES

Discuss the point on page 653 that the average American is in at least 50 databases.

Explain that people who are active users of online music stores and Web sites such as MySpace or Facebook are likely to be in many more databases.


CLASSROOM ACTIVITIES1. Class Discussion: Ask students if the security of their personal information has ever

been breached. For example, have their banks issued them new PINs for ATM cards as a result of a security breach? Have they received new credit cards from their credit card company for this reason?

2. Quick Quiz: True/False: Very few Americans are listed in any databases. (Answer: False.) True/False: The qualities that make a database efficient also make it

vulnerable. (Answer: True.)

Database Security Measures (654)LECTURE NOTES

Explain that data views are useful when you want to, in essence, create a virtual table.

Point out that data views allow you to perform a join and present the resulting data as if the tables behind the view were one.

Discuss that the question that then follows is whether or not you take the next step of materializing the view, i.e., do you create a table that corresponds to the view? This is a way of improving performance because you avoid the need to perform a join each time you want this data view. If a given join is commonly needed, materializing the view will improve performance (reads of the database

are faster). The downside is that this will result in duplication of information so if you make an update, it will have to be updated in both places.

TEACHER TIPIf possible, show students what encrypted data looks like.


CLASSROOM ACTIVITIES1. Class Discussion: Ask students which of the security measures listed in this section

they believe is the most effective. Why?2. Quick Quiz:

What are the security measures that can be taken to protect a database? (Answers: Encryption, access controls, data security policies, and intrusion monitoring.)

A access ________ limits access to systems. (Answer: control.) True/False: A database administrator can limit the way individuals or groups

access data. (Answer: True.)

Database Security Regulations (656)LECTURE NOTES

Discuss the e-mail shown in Figure 11-55. Have students ever been recipients of messages like these?


CLASSROOM ACTIVITIES1. Class Discussion: Ask students if they think that industry self-regulation is enough

to protect consumers’ privacy. Why or why not? Do students think the government would do a better or worse job of it?

2. Quick Quiz: Laws dating back to the _____ cover disclosure of telephone and cable records.

(Answer: 1950s.) The Gramm-Leach-Bliley Act of 1999 requires what type of institutions to

establish security standards that protect customer data? (Answer: Financial.) True/False: The U.S. has the most stringent privacy regulations in the world.

(Answer: False.)

What Individuals Can Do (658)LECTURE NOTES

Emphasize that the key to minimizing risk is being extremely cautious about the information you divulge.

Use Figure 11-56 in your discussion of portable password managers.

TEACHER TIPSurvey students about the guidelines on pages 658 and 659. How many of these suggestions are students following already on a regular basis? Which of these guidelines would students resist?



A database _____ is a procedure that monitors and records user activity within a database. (Answer: audit.)

True/False: The data stored in a single database is often enough to provide a criminal with enough information to access your bank account or use your credit card. (Answer: True.)

A data _____ establishes which fields and records a particular user is allowed to access.a. portalb. viewc. privileged. display(Answer: B.)

Glossary of Key Terms access control (655) BLOB (binary large object) 635 cardinality (616) case sensitive database (636) computed field (635) data dependence (624) data independence (624) data mining (612) data redundancy (637) data type (634) data view (655) data warehouse (612) database (610) database audit (656) database client software (626) database index (639) database model (614) database server software (626) database structure (633) date data type (634) DBMS (database management

system) (625 dimensional database (619) distributed database (626) dynamic Web publishing (628) entity-relationship diagram (616) executive dashboard software (613) field (615) field format (636) field name (615) field validation rule (636) fixed-length field (615) flat file (614) global update (650) hierarchical database (617) hyperlink data type (635) INSERT (647) integer data type (634) JOIN (651) joining tables (651) logical data type (634) many-to-many relationship (616)

memo data type (634) network database (617) normalization (637) object database (620) object-relational database (621) ODBMS (object database

management system) (625) OLAP (online analytical processing)

(613) one-to-many relationship (616) one-to-one relationship (616) parameters (647) predictive analytics (613) primary key (634) RDBMS (relational database

management system) (625 real data type (634) record (615) record occurrence (615) record type (615) relational database (618) relationship (616) report generator (642) report template (642) SELECT (648) server-side program (628) sort key (639) sort order (639) SQL (Structured Query Language)

(645) SQL keywords (646) SQL query (645) static Web publishing (627) structured file (614) table (618) text data type (634) UPDATE (650) unstructured file (614) user privileges (655) variable-length field (615) XForms (629) SML (630) XML DBMS (625)

Top of Document

microsoft office 2003virgil.azwestern.edu/~cvb/cis120/book notes/np11_im_chapter11… · web...

Documents