Chapter 6- Database Design: Design Method

The information-level design methodology involves representing the individual user view as a collection of tables, refining them to eliminate any problems, and then merging them into a cumulative design.

A design that supports all user views is called a constructive design

The second step in creating a user view is to represent the user view as a collection of tables.

The first step in creating a user view is to normalize the tables.

When provided with a user view or some sort of stated requirement, you must develop a collection of tables that will support it.

The primary key is a unique identifier.

The basic relationships among entities are: one-to-many, many-to-many, and one-to-one.

In a one-to-many relationship, the primary key of the "many" table becomes the foreign key of the "one" table.

Careful planning in the early stages of the normalization process will usually avoid the need to consider fourth normal form.

You create a many-to-many relationship by creating a new table whose primary key is the combination of the primary keys of the original tables.

There are two types of primary keys that you can use in your database design.

A foreign key is a column or collection of columns in one table that is required to match the value of the primary key for some row in another table, or be null.

In DBDL, you represent a table by listing all columns and then underlining the primary key.

In an entity-relationship diagram, rectangles represent foreign keys.

When you use an E-R diagram to represent a database, it visually illustrates all the information listed in the DBDL.

In an E-R diagram, a dashed line represents an identifying relationship and a solid line represents a nonidentifying relationship.

Nulls are used when a value is either unknown or inapplicable.

When you combine third normal form tables, the result will always be in third normal form.

The use of an "m" and an "n" in an E-R diagram indicates a many-to-many relationship.

A weak entity is a column or collection of columns that could have been chosen as a primary key, but was not.

A set of requirements that is necessary to support the operations of a particular database user is known as a(n) ____. a. user view b. user table c. user attribute d. user field

The properties of the entities you choose as you design the user view will become the ____ in the appropriate tables. a. rows b. columns c. data d. DBMS

If each employee works in a single department and each department has only one employee, the relationship between employees and departments is ____. a. one-to-one b. one-to-many c. many-to-one d. many-to-many

A(n) ____ is a primary key that consists of a column that uniquely identifies an entity, such as a person's Social Security number. a. surrogate key b. weak entity c. artificial key d. natural key

A column that you create for an entity to serve solely as the primary key and that is visible to users is called a(n) ____. a. synthetic key b. weak entity c. artificial key d. natural key

A(n) ____ is a system-generated primary key that is usually hidden from users. a. weak entity b. surrogate key c. natural key d. artificial key

When a DBMS creates a(n) ____ key, it is usually an automatic numbering data type, such as the Access AutoNumber data type. a. surrogate b. artificial c. natural d. logical

A natural key is also called a(n) ____ key. a. Surrogate b. Intelligent c. Secondary d. Defining

Which of the following shows sample DBDL documentation for the Employee table? a. Employee (EmployeeNum, LastName, FirstName, Street, City, State, PostalCode) b. Employee (EmployeeNum, LastName, FirstName, Street, City, State, PostalCode) c. Employee (EmployeeNum, LastName, FirstName, Street, City, State, PostalCode, (WageRate, SocSecNum, DepartmentNum) ) d. Employee (EmployeeNum, LastName, FirstName, Street, City, State, PostalCode, WageRate, SocSecNum, DepartmentNum) AK SocSecNum SK LastName FK DepartmentNum→Department

An alternative to the primary key of a table is listed with the ____ abbreviation in DBDL. a. SK b. FK c. PK d. AK

Combining the following two tables would result in ____. Employee (EmployeeNum, LastName, FirstName, WageRate, SocSecNum, DepartmentNum) Employee (EmployeeNum, LastName, FirstName, Street, City, State, PostalCode) a. Employee (EmployeeNum, LastName, FirstName, WageRate, SocSecNum, DepartmentNum, (Street, City, PostalCode) ) b. Employee (EmployeeNum, LastName, FirstName, WageRate, SocSecNum, DepartmentNum, Street, City, State, PostalCode) c. Employee (EmployeeNum, LastName, FirstName, Street, City State, PostalCode, WageRate, SocSecNum) d. Employee (EmployeeNum, LastName, FirstName, WageRate, SocSecNum, DepartmentNum, EmployeeNum, LastName, FirstName, Street, City, State, PostalCode )

Independent entities have ____ in an E-R diagram a. dashed borders b. dark backgrounds c. square corners d. rounded corners

An entity that does not require a relationship to another entity for identification is called a(n) ____. a. alternative entity b. foreign entity c. independent entity d. single entity

A relationship that is necessary for identification is called a(n) ____. a. objectifying relationship b. merging relationship c. identifying relationship d. referential relationship

Convert the following table to first normal form: Branch (BranchNum, BranchName, (BookCode, Title, OnHand) ) a. Branch (BranchNum, BranchName) Book (BookCode, Title) b. Branch (BranchNum, BranchName) c. Book (BookCode, Title) d. Branch (BranchNum, BranchName, BookCode, Title, OnHand )

When implementing the physical-level design, for secondary keys, you must ensure that it is possible to retrieve data rapidly on the basis of a value of the ____ key. a. primary b. secondary c. alternate d. foreign

After the information-level design is completed, ____ is the next step. a. The physical-level design b. The modality-level design c. The DBMS-level design d. The table-level design

When designing a database, you might find it helpful to design a(n) ____ to obtain the required information from users. a. E-R diagram b. table c. report d. survey form

The type of information collected on the survey form that describes how data is updated is ____. a. Attribute information b. Processing information c. Relationships d. Entity information

The crucial issue in making the determination between a single many-to-many-to-many relationship and two (or three) many-to-many relationships is the ____. a. independence b. data dependence c. redundancy d. cardinality

If a many-to-many-to-many relationship is created when it is not appropriate to do so, the conversion to ____ normal form will correct the problem. a. first b. second c. third d. fourth

A subtype is called a(n) ____ in IDEF1X terminology. a. dependent b. inner type c. specification d. category

In IDEF1X, the ____ is the symbol for category. a. square b. rectangle c. diamond d. circle

Complete categories are represented by ____ line(s) below the category symbol. a. one b. two c. three d. four

Inthe standard E-R diagrams, relationships are drawn as ____, with lines connecting the entities involved in relationships. a. rectangles b. diamonds c. circles d. squares

An entity that exists to implement a many-to-many relationship is called a(n) ____. a. composite entity b. complex entity c. complicated entity d. circular entity

On an E-R diagram, the number closest to the rectangle represents ____ cardinality. a. maximum b. minimum c. midvalue d. smallest

On an E-R diagram, the number closest to the relationship represents ____ cardinality. a. maximum b. minimum c. midvalue d. largest

____ means that an entity has a minimum cardinality of zero. a. The entity has a mandatory role in the relationship. b. The entity is not required in the relationship. c. The entity should not be in the relationship as an attribute. d. The entity must be listed with all zeroes in the data fields.

____ means that an entity has a minimum cardinality of one. a. The entity is required in the relationship. b. The entity is not required in the relationship. c. The entity should not be in the relationship as an attribute. d. The entity must be listed with all ones in the data fields.