chap 9

code

A _____ is a set of letters or numbers that represents a data item. Codes can be used to simplify output, input, and data formats

query language

A _______ _________ allows a user to specify a task without specifying how the task will be accomplished.

Read it!!!!!!

A combination key also can be called a composite key , a concatenated key , or a multivalued key .

3nf

A popular rule of thumb is that a design is in 3NF if every nonkey field depends on the key, the whole key, and nothing but the key. As you will see, a 3NF design avoids redundancy and data integrity problems that still can exist in 2NF designs.

2NF

A table design is in second normal form(____ ) if it is in 1NF and if all fields that are not part of the primary key are functionally dependent on the entire primary key. If any field in a 1NF table depends on only one of the fields in a combination primary key, then the table is not in 2NF.

key fields

During the systems design phase, you use _______ ______ to organize, access, and maintain data structures. The four types of keys are primary keys, candidate keys, foreign keys, and secondary keys.

database management system DBMS

In a __________ _________ __________ (_____) , all the tables are connected by common fields. A typical common field might be a Customer Number, which could be used to locate information about that customer in other tables. A common field that connects two tables is said to link, join, or relate the tables.

ODBC, open database connectivity

______ ______ _________ ____________, open database connectivity , is an industry-standard protocol that makes it possible for software from different vendors to interact and exchange data.

Audit log files,audit fields

_______ ______ ________ , which record details of all accesses and changes to the file or database, can be used to recover changes made since the last backup. You also can include ______ ______ , which are special fields within data records to provide additional control or security information. Typical audit fields include the date the record was created or modified, the name of the user who performed the action, and the number of times the record has been accessed.

Block sequence codes

________ _______ _________ use blocks of numbers for different classifications. College course numbers usually are assigned using a block sequence code. 100-level courses, such as Chemistry 110 and Mathematics 125, are freshman-level courses, whereas course numbers in the 200s indicate sophomore-level courses. Within a particular block, the sequence of numbers can have some additional meaning, such as when English 151 is the prerequisite for English 152.

intranet,extranet

2.An ________ is a private, company-owned network to provide Web-based access to internal users. An ________ is an extension of a company intranet that allows access by external users, such as customers and suppliers.

physical record,block,buffer

3.Physical storage involves a _______ _______ , or _______ , which is the smallest data unit that can be handled by the operating system. The system reads or writes one physical record at a time. When the system reads a physical record, it loads the data from storage into a ______, which is a segment of computer memory.

many-to-many relationship

A , abbreviated M:N , exists when one instance of the first entity can relate to many instances of the second entity, and one instance of the second entity can relate to many instances of the first entity. The relationship between STUDENT and CLASS, for example, is many-to-many—one student can take many classes, and one class can have many students enrolled. Figure 9-17 shows several M:N entity-relationships. One of the connecting lines is labeled with the letter M, and the letter N labels the other connection.

primary key

A _______ _______ is a field or combination of fields that uniquely and minimally identifies a particular member of an entity. For example, in a customer table the customer number is a unique primary key because no two customers can have the same customer number. That key also is minimal because it contains no information beyond what is needed to identify the customer

data warehouse

A _______ _________ allows users to specify certain dimensions , or characteristics. By selecting values for each characteristic, a user can obtain multidimensional information from the stored data. For example, in a typical company, most data is generated by transaction-based systems, such as order processing systems, inventory systems, and payroll systems. If a user wants to identify the customer on sales order 34071, he or she can retrieve the data easily from the order processing system by entering an order number.

logical record

A _______ _________ is a set of field values that describes a single person, place, thing, or event. For example, a logical customer record contains specific field values for a single customer, including the customer number, name, address, telephone number, credit limit, and so on. Application programs see a logical record as a group of related fields, regardless of how or where the data is stored physically.

data structure

A _______ __________ is a framework for organizing, storing, and managing data. Data structures consist of files or tables that interact in various ways

file-oriented system

A _______-_______ _______ , sometimes called a file processing system, stores data in one or more separate files.

foreign key

A ________ _______ is a field in one table that must match a primary key value in another table in order to establish the relationship between the two tables.

secondary key

A ________ _______ is a field or combination of fields that can be used to access or retrieve records. Secondary key values are not unique. For example, if you need to access records for only those customers in a specific ZIP code, you would use the ZIP code field as a secondary key. Secondary keys also can be used to sort or display records in a certain order. For example, you could use the GPA field in a STUDENT file to display records for all students in grade point order.

data manipulation language DML

A ________ ________ _________(____) controls database operations, including storing, retrieving, updating, and deleting data. Most commercial DBMSs, such as Oracle and IBM's DB/2, use a DML

common field

A ________ ________ is an attribute that appears in more than one entity. Common fields can be used to link entities in various types of relationships.

data warehouse

A ________ ________ is an integrated collection of data that can include seemingly unrelated information, no matter where it is stored in the company. Because it can link various information systems and databases, a data warehouse provides an enterprise-wide view to support management analysis and decision making.

one-to-many relationship

A __________ __ _____ __________ , abbreviated 1:M , exists when one occurrence of the first entity can relate to many instances of the second entity, but each instance of the second entity can associate with only one instance of the first entity. For example, the relationship between DEPARTMENT and EMPLOYEE is one-to-many: One department can have many employees, but each employee works in only one department at a time. Figure 9-16 shows several 1:M relationships. The line connecting the many entity is labeled with the letter M, and the number 1 labels the other connecting line. How many is many? The first 1:M relationship shown in Figure 9-16 shows the entities INDIVIDUAL and AUTOMOBILE. One individual might own five automobiles, or one, or none. Thus, many can mean any number, including zero

repeating group

A __________ ______ is a set of one or more fields that can occur any number of times in a single record, with each occurrence having different values.

one-to-one relationship

A __________ _________ ________, abbreviated 1: 1, exists when exactly one of the second entity occurs for each instance of the first entity. Figure 9-15 shows examples of several 1:1 relationships. A number 1 is placed alongside each of the two connecting lines to indicate the 1:1 relationship.

subschema

A __________ is a view of the database used by one or more systems or users. A subschema defines only those portions of the database that a particular system or user needs or is allowed to access.

crow's foot notation

A common method of cardinality notation is called ______ ______ _________ because of the shapes, which include circles, bars, and symbols, that indicate various possibilities. A single bar indicates one, a double bar indicates one and only one, a circle indicates zero, and a crow's foot indicates many. Figure 9-19 shows various cardinality symbols, their meanings, and the UML representations of the relationships. As you learned in Chapter 4, the Unified Modeling Language (UML) is a widely used method of visualizing and documenting software systems design.

attribute

A field , also called an ________ , is a single characteristic or fact about an entity. For example, a CUSTOMER entity might include the Customer ID, First Name, Last Name, Address, City, State, Zip, and E-mail Address.

tuple

A record , also called a ______ (rhymes with couple), is a set of related fields that describes one instance, or occurrence of an entity, such as one customer, one order, or one product. A record might have one or dozens of fields, depending on what information is needed

read it!!!!

A standard process exists for converting a table from 1NF to 2NF. The objective is to break the original table into two or more new tables and reassign the fields so that each nonkey field will depend on the entire primary key in its table. To accomplish this, you follow these steps: 1. First, create and name a separate table for each field in the existing primary key. For example, in Figure 9-23 on the previous page, the ORDER table's primary key has two fields, ORDER-NUM and PRODUCT-NUM, so you must create two tables. The ellipsis (...) indicates that fields will be assigned later. The result is: ORDER (ORDER-NUM,...) PRODUCT (PRODUCT-NUM,...) 2. Next, create a new table for each possible combination of the original primary key fields. In the Figure 9-23 example, you would create and name a new table with a combination primary key of ORDER-NUM and PRODUCT-NUM. This table describes individual lines in an order, so it is named ORDER-LINE, as shown: ORDER-LINE (ORDER-NUM, PRODUCT-NUM,...) 3. Finally, study the three tables and place each field with its appropriate primary key, which is the minimal key on which it functionally depends. When you finish placing all the fields, remove any table that did not have any additional fields assigned to it. The remaining tables are the 2NF version of your original table. In the Figure 9-23 example, the three tables would be shown as: ORDER (ORDER-NUM, ORDER-DATE) PRODUCT (PRODUCT-NUM, PRODUCT-DESC) ORDER-LINE (ORDER-NUM, PRODUCT-NUM, NUM-ORDERED) Figure 9-24 shows the 2NF table designs. By following the steps, you have converted the original 1NF table into three 2NF tables. Why is it important to move from 1NF to 2NF? Four kinds of problems are found with 1NF designs that do not exist in 2NF:

entity

An _______ is a person, place, thing, or event for which data is collected and maintained. For example, an online sales system may include entities named CUSTOMER, ORDER, PRODUCT, and SUPPLIER.

entity-relationship diagram

An ________ ________ _______(ERD) is a model that shows the logical relationships and interaction among system entities. An ERD provides an overall view of the system and a blueprint for creating the physical data structures.

JDBC,Java database connectivity

Another common standard is called _______, or __________ _______ ________ . _____ enables Java applications to exchange data with any database that uses SQL statements and is JDBC-compliant.

Web-centric

Because intranets and extranets use the same protocols , or data transmission standards, as the Internet, they are called ______-_________ .

nonkey field

Because you can designate only one field as a primary key, you should select the field that contains the least amount of data and is the easiest to use. Any field that is not a primary key or a candidate key is called a _______ ______.

client/server design

Computer in a ___________ _________ that supplies data, processing, and services to client workstations. Computer in a client/server design that supplies data, processing, and services to client workstations.

Economy of scale.

Database design allows better utilization of hardware. If a company maintains an enterprise-wide database, processing is less expensive using a powerful mainframe server instead of using several smaller computers. The inherent efficiency of high-volume processing on larger computers is called ________ ___ _________

FILE,TABLE

Each _____ or _____ contains data about people, places, things, or events. For example, one file or table might contain data about customers, and other files or tables might store data about products, orders, suppliers, or employees

database administrator

Enterprise-wide application. Typically, a DBMS is managed by a person called a __________ ____________(____) , who assesses overall requirements and maintains the database for the benefit of the entire organization rather than a single department or user. Database systems can support enterprise-wide applications more effectively than file processing systems.

functionally dependent

Field A is __________ ___________ on Field B if the value of Field A depends on Field B. For example, in Figure 9-23, the ORDER-DATE value is functionally dependent on the ORDER-NUM, because for a specific order number, there can be only one date. In contrast, a product description is not dependent on the order number. For a particular order number, there might be several product descriptions—one for each item ordered.

relational database

In a DBMS, the linked tables form a unified data structure that greatly improves data quality and access. This design, also called a _________ _________ or relational model

orphan

In a relational database, referential integrity means that a foreign key value cannot be entered in one table unless it matches an existing primary key in another table. For example, referential integrity would prevent you from entering a customer order in an order table unless that customer already exists in the customer table. Without referential integrity, you might have an order called an _________ , because it had no related customer.

combination key

In the registration file, neither the student number nor the course ID is unique, so neither field can be a primary key. To identify a specific student in a specific course, the primary key must be a combination of student number and course ID. In that case, the primary key is called a __________ ________ .

SQL/Structured Query Language

Many database programs also generate ____ (______ ______ ________) , which is a language that allows client workstations to communicate with servers and mainframe computers.

associative entity

Notice that an M:N relationship is different from 1:1 or 1:M relationships because the event or transaction that links the two entities is actually a third entity, called an __________ ______ that has its own characteristics.

referential integrity

One type of validity check, called ___________ __________, is a set of rules that avoids data inconsistency and quality problems.

clicks to close

Reduce _______ ___ _______ , which means average page views to accomplish a purchase or obtain desired information

data integrity

Second, ________ __________ problems can occur if updates are not applied in every file. Changing the data in only one of the systems will cause inconsistent data and result in incorrect information in the second system.

query by example

Some query languages use natural language commands that resemble ordinary English sentences. With a _______ _____ ________(____) language

candidate key

Sometimes you have a choice of fields or field combinations to use as the primary key. Any field that could serve as a primary key is called a _______ _____ . For example, if every employee has a unique employee number, then you could use either the employee number or the Social Security number as a primary key.

clients,servers

The Internet and company intranets/extranets are forms of client/server architecture. In a client/server design, tasks are divided between _______ , which are workstations that users interact with, and ________, which are computers that supply data, processing, and services to the client workstations.

schema

The complete definition of a database, including descriptions of all fields, tables, and relationships, is called a ________

blocking factor

The physical storage location can be local, remote, or Web-based. Similarly, when the system writes a physical record, all data in the memory buffer is saved physically to a storage location. A physical record can contain more than one logical record, depending on the _______ ________

Read it

The table is in 1NF because it has no repeating groups. The design also is in 2NF because the primary key is a single field. But the table still has four potential problems similar to the four 1NF problems described earlier. Changing the name of a sales rep still requires changing every record in which that sales rep name appears. Nothing about the design prohibits a particular sales rep from having different names in different records. In addition, because the sales rep name is included in the CUSTOMER table, you must create a dummy CUSTOMER record to add a new sales rep who has not yet been assigned any customers. Finally, if you delete all the records for customers of sales rep number 22, you will lose that sales rep's number and name.

clickstream storage

This type of data gathering is sometimes called _________ ________ . Armed with this information, a skillful Web designer could build a profile of typical new customers, returning customers, and customers who browse but do not buy.

3 NF

Those potential problems are caused because the design is not in 3NF. A table design is in third normal form(3NF ) if it is in 2NF and if no nonkey field is dependent on another nonkey field. Remember that a nonkey field is a field that is not a candidate key for the primary key. The CUSTOMER example in Figure 9-25 is not in 3NF because one nonkey field, SALES-REP-NAME, depends on another nonkey field, SALES-REP-NUM.

data redundancy

Three potential problems exist in a file processing environment. The first problem is __________ ___________ , which means that data common to two or more information systems is stored in several places. Data redundancy requires more storage space, and maintaining and updating data in several locations is expensive.

Middleware

To bridge the gap, it is necessary to use _________ , which is software that integrates different applications and allows them to exchange data. ________ can interpret client requests in HTML form and translate the requests into commands that the database can execute.

Read it!!!!!

To understand Web-based data design, it is helpful to review some basic Internet terms and concepts. To access information on the Internet, a person uses a Web browser , which is an application that enables the user to navigate, or browse, the Internet and display Web pages on his or her local computer. A Web page is a text document written in HTML(Hypertext Markup Language) . HTML uses formatting codes called tags , which specify how the text and visual elements will be displayed in a Web browser. Web pages are stored on a Web server , which is a computer that receives requests and makes Web pages available to users. Together, the Web server and the Web pages are referred to as a Web site

data mart

While a data warehouse typically spans the entire enterprise, many firms prefer to use a ________ ________, which is designed to serve the needs of a specific department, such as sales, marketing, or finance. Each data mart includes only the data that users in that department require to perform their jobs. There are pros and cons to both approaches, and the best solution usually depends on the specific situation.

Sequence codes

_______ _______ are numbers or letters assigned in a specific order. Sequence codes contain no additional information other than an indication of order of entry into the system. For example, a human resource system issues consecutive employee numbers to identify employees. Because the codes are assigned in the order in which employees are hired, you can use the code to see that employee number 584 was hired after employee number 433. The code, however, does not indicate the starting date of either person's employment.

Data mining

_______ ________ software looks for meaningful data patterns and relationships. For example, data mining software could help a consumer products firm identify potential customers based on their prior purchases. Information about customer behavior is valuable, but data mining also raises serious ethical and privacy issues, such as the example in the Question of Ethics feature on page 436. Figure 9-45 on the next page shows North Carolina State University's Ethics in Computing site, which offers many articles and research papers on this subject.

Alphabetic codes

_________ ______ use alphabet letters to distinguish one item from another based on a category, an abbreviation, or an easy-to-remember value, called a mnemonic code. Many classification codes fit more than one of the following definitions:

Cardinality ,cardinality notation

__________ describes the numeric relationship between two entities and shows how instances of one entity relate to instances of another entity. For example, consider the relationship between two entities: CUSTOMER and ORDER. One customer can have one order, many orders, or none, but each order must have one and only one customer. An analyst can model this interaction by adding _________ __________ , which uses special symbols to represent the relationship.

normalization

___________ is the process of creating table designs by assigning specific fields or attributes to each table in the database

. Logical storage,physical storage

____________ _______ refers to data that a user can view, understand, and access, regardless of how or where that information actually is organized or stored. In contrast, ________ _________ is strictly hardware-related, because it involves the process of reading and writing binary data to physical media such as a hard drive, CD-ROM, or network-based storage device. For example, portions of a document might be stored in different physical locations on a hard drive, but the user sees the document as a single logical entity on the computer screen.

first normal form

table is in _______ ________ ______(1NF ) if it does not contain a repeating group. To convert an unnormalized design to 1NF, you must expand the table's primary key to include the primary key of the repeating group.