Database Concepts, 6/E Solutions manual and test bank Auer & Kroenke
Database Concepts
Sixth Edition
David M. Kroenke • David J. Auer
Instructor’s Manual
Prepared by David J. Auer
CHAPTER TWO
THE RELATIONAL MODEL
Instructor’s Manual to accompany:
Database Concepts (Sixth Edition)
David M. Kroenke and David J. Auer
© 2013, 2011, 2010, 2008 Pearson Education, Inc. Publishing as Prentice Hall
u CHAPTER OBJECTIVES
· Learn the conceptual foundation of the relational model
· Understand how relations differ from nonrelational tables
· Learn basic relational terminology
· Learn the meaning and importance of keys, foreign keys, and related terminology
· Understand how foreign keys represent relationships
· Learn the purpose and use of surrogate keys
· Learn the meaning of functional dependencies
· Learn to apply a process for normalizing relations
u CHAPTER ERRATA
· There are no known errors at this time. Any errors that are discovered in the future will be reported and corrected in the Online DBC e06 Errata document, which will be available at http://www.pearsonhighered.com/kroenke.
u THE ACCESS WORKBENCH
· Solutions to the Access Workbench exercises may be found in Solutions to all Sections: The Access Workbench, which is a separate document within the Instructor’s Manual.
u TEACHING SUGGESTIONS
· The Art Course database discussed in Chapter 1 is a good database to use for an in-class demo of the concepts in this chapter. The DBMS screenshots in Chapter 2 use that database as the example database. For example, see Figure 2-7, 2-8 and 2-9. See the list, data and database files supplied, and use the following:
· MS Access:
· “Art Course List” in DBC-e05-Lists-And-Data.xlsx
· DBC-e06-Art-Course-Database-CH01.accdb
· SQL Server 2012 Express Edition:
· DBC-e06-MSSQL-Art-Course-Database-Create-Tables.sql
· DBC-e06-MSSQL-Art-Course-Database-Insert-Data.sql
· NOTE: Create a database diagram for the database
· Oracle Database 11g Release 2:
· DBC-e06-ODB-Art-Course-Database-Create-Tables.sql
· DBC-e06-ODB-Art-Course-Database-Insert-Data.sql
· DBC-e06-ODB-Art-Course-Database-SQL-Queries-CH01.sql
· MySQL 5.5:
· DBC-e06-MySQL-Art-Course-Database-Create-Tables.sql
· DBC-e06-MySQL-Art-Course-Database-Insert-Data.sql
· The goal of this chapter is to present an overview of the major elements of the relational model. This includes the definition of a relation, important terminology, the use of surrogate keys, and basic design principles.
· Students often misconstrue the statement that only a single element is allowed in a cell to mean that the cells must be fixed in length. One can have a variable length memo in a cell but that is considered, semantically, to be one thing. By the way, there are a number of reasons for this restriction. Perhaps the easiest to explain is that SQL has no means for addressing sub-elements in a cell.
· When students execute SQL SELECTs, they may generate relations with duplicate rows. Such results do not fit the definition of relations, but they are considered relations nonetheless. This is a good example of “theory versus practice”.
· You may want to emphasize that foreign keys and the primary key that they reference need not have the same name. They must, however, have the same underlying set of values (domain). This means that the values not just look the same; it means that the values mean the same thing. A foreign key of CatName and a foreign key of ValentineNickName might look the same, but they do not mean the same thing. Using ValentineNickName as a foreign key to Name in the relation CAT would result in some weird results.
· Referential integrity constraints are important. You might ask the students to think of an example when a foreign key does not have a referential integrity constraint (answer: whenever a parent row is optional, say, STUDENTs need not have an ADVISER).
· We favor the use of surrogate keys. Unless there is a natural, numeric ID (like PartNumber), we almost always add a surrogate key to our database designs. Sometimes a surrogate key will be added even if there is a natural, numeric ID for consistency. Surrogate keys can cause problems (primarily patching up foreign keys) if the database imports data from other databases that either do not employ a surrogate key or use a different one. In some cases, institutions have developed policies for ensuring that surrogate keys are unique globally. It’s probably best for the students to get into the habit of using them and consider not using them as an exception. Professional opinions vary on this, however.
· If you’re using Oracle Database, then you’ll need to teach the use of sequences to implement surrogate keys. Sequences are an awkward solution to this problem, however, and may be why surrogate keys are less used in the Oracle-world. Maybe there will be a better solution to them from Oracle in the future.
· The discussion of functional dependencies is critical—maybe the most important in the book. If students can understand that all tables do is record “data points” of functional dependencies, then normalization will be easier and seem more natural.
· In physics, because there are formulae like F = ma, we need not store tables and tables of data recording data points for force, mass, and acceleration. The formula suffices for all data points. However, there is no formula for computing how much a customer of, say, American Airlines, owes for his or her ticket from New York to Houston. If we could say the cost of an airline ticket was $.05 per mile, then we could compute the cost of a ticket, and tables of airline flight prices would be unnecessary. But, we cannot; it all depends on … So, we store the data points for functional dependencies in tables.
· This chapter presents the design principle that every determinant should be a candidate key. This is, of course, the definition of Boyce-Codd Normal Form. This leaves out 4NF, 5NF, and domain/key normal form. At this level, we do not think those omissions are critical. See the normalization discussion in Chapter 5 for more on this topic.
· If we use domain/key normal form as the ultimate, then, insofar as functional dependencies are concerned, the domain/key definition that “every constraint is a logical consequence of domains and keys,” comes down to Boyce-Codd Normal Form. Therefore, we proceed on good theoretical ground with the discussion as presented in this chapter.
· Students should understand three ambiguities in a null value. This understanding will help them comprehend the issues addressed by INNER and OUTER joins in the next chapter.
· Exercises 2.40 and 2.41 deal with multivalued dependencies and fourth normal form (4NF). These are instructive as they show students how to deal with situations where the value of one column in a table is associated with several values of another attribute in (at least initially) the same table. This is an important concept, and after BCNF it is the next important concept students need to understand about normalization.
·
u ANSWERS TO REVIEW QUESTIONS
2.1 Why is the relational model important?
It is the single most important standard in database processing and is used for the design and implementation of almost every commercial database worldwide.
2.2 Define the term entity and give an example of an entity (other than the one from this chapter).
Entity is the formal name for a “thing” that is being tracked in a database, and is defined as something of importance to the user that needs to be represented in the database.
Example: TEXTBOOK
2.3 List the characteristics a table must have to be considered a relation.
· Rows contain data about an entity.
· Columns contain data about attributes of the entity
· Cells of the table hold a single value.
· All entries in a column are of the same kind.
· Each column has a unique name.
· The order of the columns is unimportant.
· The order of the rows is unimportant.
2.4 Give an example of a relation (other than one from this chapter).
Example: TEXTBOOK (ISBN, Title, Publisher, Copyright)
2.5 Give an example of a table that is not a relation (other than one from this chapter).
Example: TEXTBOOK (ISBN, Title, Publisher, Copyright, Authors)
A table is not a relation when there are multiple author names in the Authors column.
2.6 Under what circumstances can an attribute of a relation be of variable length?
It can be of a variable length, if that attribute is considered to be a single thing like a memo or other variable length data item.
2.7 Explain the use of the terms file, record, and field.
These terms are synonyms for table, row, and column. These terms, however, generally refer to pre-relational bases.
2.8 Explain the use of the terms relation, tuple, and attribute.
These terms are synonyms for table, row, and column. These terms, however, are the ones used in relational database theory.
2.9 Under what circumstances can a relation have duplicate rows?
When manipulating a relation with a DBMS we may end up with duplicate rows. Although in theory we should eliminate the duplicates, in practice this is often not done.
2.10 Define the term unique key and give an example.
A unique key is a column whose values identify one and only one row.
Example: TEXTBOOK (ISBN, Title, Publisher, Copyright)
where ISBN is a unique identifier.
2.11 Define the term nonunique key and give an example.
A nonunique key not only identifies a row, but it potentially identifies more than one row.
EXAMPLE: TEXTBOOK (ISBN, Title, Publisher, Copyright)
Publisher is a nonunique identifier.
2.12 Give an example of a relation with a unique composite key.
EXAMPLE: APARTMENT (BuildingNumber, ApartmentNumber, NumberOfBedrooms, Rent)
where (BuildingNumber, ApartmentNumber) is a unique composite key.
2.13 Explain the difference between a primary key and a candidate key.
Both are unique identifiers. One is chosen to be the identifier for the relation and for foreign keys based on the relation. The other could be chosen as well, but since it is not, it is called a candidate.
2.14 Describe four uses of a primary key.
A primary key can be used
· to identify a row.
· to represent the row in foreign keys.
· to organize storage for the relation.
· as a basis for indexes and other structures to facilitate searching in storage.
2.15 What is a surrogate key, and under what circumstances would you use one?
A surrogate key is a unique, numeric identifier that is appended to a relation to serve as the primary key.
2.16 How do surrogate keys obtain their values?
They are supplied automatically by the DBMS.
2.17 Why are the values of surrogate keys normally hidden from users on forms, queries, and reports?
Surrogate keys are normally hidden because they usually have no meaning to the users.
2.18 Explain the term foreign key and give an example.
A foreign key creates the relationship between the tables; its key value corresponds to a primary key in a relation other than the one where the key is a primary key.
EXAMPLE: TEXTBOOK (ISBN, Title, Publisher, Copyright)
PUBLISHER (PublisherName, Street, City, State, Zip)
Publisher in TEXTBOOK is a foreign key that references PublisherName in PUBLISHER.
2.19 Explain how primary keys and foreign keys are denoted in this book.
Primary keys are underlined and foreign keys are in italics.
2.20 Define the term referential integrity constraint and give an example of one.
Referential integrity constraint is a rule specifying that every value of a foreign key matches a value of the primary key.
Example: Publisher in TEXTBOOK must exist in PublisherName in PUBLISHER.
2.21 Explain three possible interpretations of a null value.
Three possible interpretations are:
· Value not appropriate
· Value known to be blank
· Value appropriate and unknown
2.22 Give an example of a null value (other than one from this chapter), and explain each of the three possible interpretations for that value.
An example of null value would be: Null value for the attribute DeceasedDate in the table SUBSCRIBER.
· The subscriber may be a corporation and a value is inappropriate.
· The subscriber may be alive, and the value is known to be blank.
· The subscriber may be dead, but the date of death is unknown, and the value is appropriate, but not none.
2.23 Define the terms functional dependency and determinant, using an example not from this book.
A functional dependency is a logical relationship in which the value of one item in the relationship can be determined by knowing the value of the other item.
EXAMPLE: ISBN à Title
This means that if the ISBN (of a textbook) is known, then we will also know (can determine) the title. The item on the left—the one whose value is known—is called the determinant.
2.24 In the following equation, name the functional dependency and identify the determinant(s):
Area = Length ´ Width
The functional dependency is:
(Length, Width) à Area
(Length, Width) is the determinant.
Note this is different than saying “Length and Width are the determinants”.
2.25 Explain the meaning of the following expression:
A à (B, C)
Given this expression, tell if it is also true that:
A à B
and
A à C
The functional dependency:
A à (B, C)
means that a value of A determines the value of both B and C.
Yes, it is true that
A à B and A à C
2.26 Explain the meaning of the following expression:
(D, E) à F
Given this expression, tell if it is also true that:
D à F
and
E à F
The functional dependency:
(D, E) à F
means that values of the pair (D, E) determine the value of F.
No, it is not true that
D à F and E à F
2.27 Explain the differences in your answers to questions 2.25 and 2.26.
A à (B, C) is just shorthand for A à B and A à C
However, (D, E) à F means that the composite, as a whole, identifies F.
For example:
EmployeeNumber à (FirstName, LastName)
This means that
EmployeeNumber à FirstName
and that
EmployeeNumber à LastName.
But:
(FirstName, LastName) à HireDate
does not mean that FirstName à HireDate (There could be lots of employees named “Bob”.)
2.28 Define the term primary key in terms of functional dependencies.
A primary key is one or more attributes that functionally determines all of the other attributes.
2.29 If you assume that a relation has no duplicate data, how do you know there is always at least one primary key?
Because the collection of all the attributes in the relation can identify a unique row.
2.30 How does your answer to question 2.29 change if you allow a relation to have duplicate data?
It doesn’t work—such tables do not have a primary key.
2.31 In your own words, describe the nature and purpose of the normalization process.
The purpose of the normalization process is to prevent update problems in the tables (relations) in the database. The nature of the normalization process is that we break up relations as necessary to ensure that every determinant is a candidate key.
2.32 Examine the data in the Veterinary Office List—Version One in Figure 1-30 (see page 52), and state assumptions about functional dependencies in that table. What is the danger of making such conclusions on the basis of sample data?
PetName à (PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)
OwnerEmail à (OwnerLastName, OwnerFirstName, OwnerPhone)
OwnerPhone à (OwnerLastName, OwnerFirstName, OwnerEmail)
The danger is that there may be possibilities not apparent from sample data. For example, two owners might have pets with the same name.
2.33 Using the assumptions you stated in your answer to question 2.32, what are the determinants of this relation? What attribute(s) can be the primary key of this relation?
Attributes that can be the primary key are called candidate keys.
Determinants: PetName, OwnerEmail, OwnerPhone
Candidate keys: PetName
2.34 Describe a modification problem that occurs when changing data in the relation in question 2.32 and a second modification problem that occurs when deleting data in this relation.
Changes to owner data may need to be made in several rows.
Deleting data for the last pet of an owner deletes owner data as well.
2.35 Examine the data in the Veterinary Office List—Version Two in Figure 1-31 (see page 52), and state assumptions about functional dependencies in that table.
PetName à (PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)
OwnerEmail à (OwnerLastName, OwnerFirstName, OwnerPhone)
OwnerPhone à (OwnerLastName, OwnerFirstName, OwnerEmail)
(PetName, Date) à (Service, Charge)
The last functional dependency assumes a pet is seen at most on one day and that there is no standard charge for a service.
2.36 Using the assumptions you stated in your answer to question 2.35, what are the determinants of this relation? What attribute(s) can be the primary key of this relation?
Determinants: PetName, OwnerEmail, OwnerPhone, (PetName, Date)
Candidate keys: (PetName, Date)
2.37 Explain a modification problem that occurs when changing data in the relation in question 2.35 and a second modification problem that occurs when deleting data in this relation.
Same as 2.34:
Changes to owner data may need to be made in several rows.
Deleting data for the last pet of an owner deletes owner data as well.
u ANSWERS TO EXERCISES
2.38 Apply the normalization process to the Veterinary Office List—Version One relation shown in Figure 1-30 (see page 52) to develop a set of normalized relations. Show the results of each of the steps in the normalization process.
STEP ONE:
PET-AND-OWNER (PetName, PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)
Functional Dependencies:
PetName à (PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)
OwnerEmail à (OwnerLastName, OwnerFirstName, OwnerPhone)
OwnerPhone à (OwnerLastName, OwnerFirstName, OwnerEmail)
PET-AND-OWNER Candidate Keys: PetName
Is every determinant a candidate key?
NO—OwnerEmail and OwnerPhone are NOT candidate keys.
STEP TWO:
Break into two relations: OWNER and PET
OWNER (OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)
PET (PetName, PetType, PetBreed, PetDOB, {Foreign Key})
FOR OWNER:
Functional Dependencies:
OwnerEmail à (OwnerLastName, OwnerFirstName, OwnerPhone)
OwnerPhone à (OwnerLastName, OwnerFirstName, OwnerEmail)
OWNER Candidate Keys: OwnerPhone, OwnerEmail
Is every determinant a candidate key?
YES—OwnerEmail and OwnerPhone are candidate keys—Normalization complete!
We can choose either candidate key as primary key.
(A) IF WE USE OwnerPhone as primary key, THEN:
OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail)
PET (PetName, PetType, PetBreed, PetDOB, OwnerPhone)
Functional Dependencies:
PetName à (PetType, PetBreed, PetDOB, OwnerPhone)
PET Candidate Keys: PetName
Is every determinant a candidate key?
YES—PetName is a candidate key—Normalization complete!
FINAL NORMALIZED REALTIONS:
OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail)
PET (PetName, PetType, PetBreed, PetDOB, OwnerPhone)
(B) IF WE USE OwnerEmail as primary key, THEN:
OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail)
PET (PetName, PetType, PetBreed, PetDOB, OwnerEmail)
Functional Dependencies:
PetName à (PetType, PetBreed, PetDOB, OwnerEmail)
PET Candidate Keys: PetName
Is every determinant a candidate key?
YES—PetName is a candidate key—Normalization complete!
FINAL NORMALIZED REALTIONS:
OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail)
PET (PetName, PetType, PetBreed, PetDOB, OwnerEmail)
2.39 Apply the normalization process to the Veterinary Office List—Version Two relation shown in Figure 1-31 (see page 52) to develop a set of normalized relations. Show the results of each of the steps in the normalization process.
STEP ONE:
PET-AND-OWNER (PetName, PetType, PetBreed, PetDOB, OwnerLastName,
OwnerFirstName, OwnerPhone, OwnerEmail, Service, Date, Charge)
Functional Dependencies:
PetName à (PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)
OwnerEmail à (OwnerLastName, OwnerFirstName, OwnerPhone)
OwnerPhone à (OwnerLastName, OwnerFirstName, OwnerEmail)
(PetName, Date) à (Service, Charge)
The last functional dependency assumes a pet is seen at most on one day and that there is no standard charge for a service.
PET-AND-OWNER Candidate Keys: (PetName, Date)
Is every determinant a candidate key?
NO—PetName, OwnerEmail and OwnerPhone are NOT candidate keys.
STEP TWO:
Break into two relations: OWNER and PET-SERVICE
OWNER (OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)
PET-SERVICE (PetName, PetType, PetBreed, PetDOB, {Foreign Key}, Service, Date, Charge)
FOR OWNER:
Functional Dependencies:
OwnerEmail à (OwnerLastName, OwnerFirstName, OwnerPhone)
OwnerPhone à (OwnerLastName, OwnerFirstName, OwnerEmail)
OWNER Candidate Keys: OwnerPhone, OwnerEmail
Is every determinant a candidate key?
YES—OwnerEmail and OwnerPhone are candidate keys—Normalization complete!
We can choose either candidate key as primary key. We will use OwnerPhone.
If a student chooses OwnerEmail, the steps will be similar as shown in Exercise 2.37.
IF WE USE OwnerPhone as primary key, THEN:
OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail)
PET-SERVICE (PetName, PetType, PetBreed, PetDOB, OwnerPhone, Service, Date, Charge)
FOR PET-SERVICE:
Functional Dependencies:
PetName à (PetType, PetBreed, PetDOB, OwnerPhone)
(PetName, Date) à (Service, Charge)
The last functional dependency assumes a pet is seen at most on one day and that there is no standard charge for a service.
PET-AND-SERVICE Candidate Keys: (PetName, Date)
Is every determinant a candidate key?
NO—PetName is NOT a candidate key.
STEP THREE:
Break PET-SERVICE into two relations: PET and SERVICE
OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail)
PET (PetName, PetType, PetBreed, PetDOB, OwnerPhone)
SERVICE (PetName, Date, Service, Charge)
PET Functional Dependencies:
PetName à (PetType, PetBreed, PetDOB, OwnerPhone)
PET Candidate Keys: PetName
Is every determinant a candidate key?
YES—PetName is a candidate key—Normalization complete!
SERVICE Functional Dependencies:
(PetName, Date) à (Service, Charge)
The functional dependency assumes a pet is seen at most on one day and that there is no standard charge for a service.
SERVICE Candidate Keys: (PetName, Date)
Database Concepts, 6e (Kroenke/Auer)
Chapter 2 The Relational Model
1) While the relational model for databases appears to hold much promise, few commercial databases have implemented it.
Answer: FALSE
Diff: 1 Page Ref: 59
2) Every table is a relation, but not every relation is a table.
Answer: FALSE
Diff: 3 Page Ref: 62
3) Every relation is a table, but not every table is a relation.
Answer: TRUE
Diff: 2 Page Ref: 62
4) Every cell in a relation can hold only a single value.
Answer: TRUE
Diff: 1 Page Ref: 60
5) In the relational model, each row of a table contains data that represents an attribute of the entity.
Answer: FALSE
Diff: 2 Page Ref: 60
6) A key must be unique.
Answer: FALSE
Diff: 1 Page Ref: 60
7) To be considered a composite key, a key must contain at least two attributes.
Answer: TRUE
Diff: 1 Page Ref: 63
8) Candidate keys may or may not be unique.
Answer: FALSE
Diff: 2 Page Ref: 65
9) The primary key is used both to identify unique rows in a relation and to represent rows in relationships.
Answer: TRUE
Diff: 2 Page Ref: 64
10) To represent a relationship in the relational model, the primary key of one relation is placed into a second relation.
Answer: TRUE
Diff: 1 Page Ref: 67
11) When used to represent a relationship, the primary key must have the same name as the corresponding foreign key.
Answer: FALSE
Diff: 3 Page Ref: 68
12) Surrogate key values have no meaning to the users.
Answer: TRUE
Diff: 1 Page Ref: 67
13) Since surrogate keys are used to uniquely identify rows, their values are normally displayed prominently on all forms and reports for the users to see.
Answer: FALSE
Diff: 2 Page Ref: 67
14) The use of surrogate keys usually complicates application programming since most DBMS products require the application program to generate surrogate key values.
Answer: FALSE
Diff: 2 Page Ref: 67
15) Null values can cause problems because they are ambiguous.
Answer: TRUE
Diff: 1 Page Ref: 71
16) If the condition exists such that knowing the value of attribute X determines the value attribute Y, then attribute Y is functionally dependent on attribute X.
Answer: TRUE
Diff: 1 Page Ref: 72-73
17) Given the functional dependency for the attributes of ENTITY1, X → (A, B, C), X is a candidate key for the relation ENTITY1 (A, B, C, X).
Answer: TRUE
Diff: 3 Page Ref: 77
18) Normalization is the process of removing all functional dependencies from a relation.
Answer: FALSE
Diff: 2 Page Ref: 76
19) To create a well-formed relation through normalization, every determinant must be a candidate key.
Answer: TRUE
Diff: 1 Page Ref: 76
20) Any table that meets the definition of a relation is said to be in second normal form.
Answer: FALSE
Diff: 2 Page Ref: 75
21) The first step of the normalization process is to identify all the candidate keys of a relation.
Answer: TRUE
Diff: 1 Page Ref: 75
22) In the normalization process, it is not necessary to identify all the functional dependencies in a relation.
Answer: FALSE
Diff: 1 Page Ref: 75
23) In the normalization process, it is necessary to identify all the determinants in a relation.
Answer: TRUE
Diff: 2 Page Ref: 75
24) In the normalization process, if you find a candidate key that is not a primary key, then you have determined that the relation needs to be broken into two or more other relations.
Answer: FALSE
Diff: 2 Page Ref: 75
25) In the normalization process, if you find that every determinant in a relation is a candidate key, then you have determined that the relation is well formed.
Answer: TRUE
Diff: 2 Page Ref: 75
26) Since Microsoft Access is a personal database, it is not subject to the modification problems that occur in other relational databases.
Answer: FALSE
Diff: 2 Page Ref: 82-83
27) In Microsoft Access, relationships between tables are created in the Relationships window.
Answer: TRUE
Diff: 1 Page Ref: 92
28) In Microsoft Access, foreign keys are designated by using the Foreign Key button in the toolbar.
Answer: FALSE
Diff: 2 Page Ref: 92
29) In Microsoft Access, a relationship is created by dragging a foreign key column and dropping it on top of the corresponding primary key.
Answer: FALSE
Diff: 2 Page Ref: 92
30) Microsoft Access forms can only contain data from one table.
Answer: FALSE
Diff: 1 Page Ref: 93
31) Which of the following is not true about a relation?
A) A relation is a two-dimensional table.
B) The cells of a relation must hold a single value.
C) A relation may have duplicate column names.
D) A relation may not have duplicate rows.
E) The order of the rows of a relation is insignificant.
Answer: C
Diff: 1 Page Ref: 60
32) Which of the following is true about a relation?
A) The order of the columns in a relation must go from largest to smallest.
B) All entries in any column must be of the same kind.
C) A relation may have duplicate column names.
D) A relation may have duplicate rows.
E) A relation may have multiple names.
Answer: B
Diff: 2 Page Ref: 60
33) Which of the following terms is synonymous with "tuple"?
A) Attribute
B) Table
C) Field
D) Row
E) Relation
Answer: D
Diff: 1 Page Ref: 62
34) Which of the following terms is synonymous with "relation"?
A) Attribute
B) Table
C) Record
D) Row
E) Tuple
Answer: B
Diff: 1 Page Ref: 62
35) Which of the following is true about a key?
A) It may be unique.
B) It may be non-unique.
C) In may identify more than one row.
D) Both A and B
E) All of the above
Answer: B
Diff: 1 Page Ref: 63
36) A key that contains more than one attribute is called a(n):
A) composite key.
B) complex key.
C) multi-key.
D) n-key.
E) candidate key.
Answer: A
Diff: 1 Page Ref: 63
37) A primary key is:
A) required to be unique.
B) used to represent rows in relationships.
C) a candidate key.
D) used to identify unique rows.
E) All of the above
Answer: E
Diff: 2 Page Ref: 63
38) A candidate key is:
A) required to be unique.
B) used to represent rows in relationships.
C) a candidate to be the primary key.
D) Both A and B
E) Both A and C
Answer: E
Diff: 3 Page Ref: 63
39) When the primary key of one relation is placed into a second relation, it is called a:
A) field key.
B) referential integrity.
C) foreign key.
D) candidate key.
E) relocated key.
Answer: C
Diff: 1 Page Ref: 68
40) STUDENT (SID, StudentName, Major, AdvisorID)
ADVISOR (AdvisorID, AdvisorName, Office, Phone)
Given the relations above such that each student is assigned to one advisor, which of the following is true?
A) SID is both a primary key and a foreign key.
B) AdvisorName is a determinant.
C) AdvisorID is a foreign key.
D) Phone is a candidate key.
E) Major is a candidate key.
Answer: C
Diff: 3 Page Ref: 63-71
41) A rule that requires that the values in a foreign key must have a matching value in the primary key to which the foreign key corresponds is called:
A) normalization.
B) a referential integrity constraint.
C) a key matching constraint.
D) a functional dependency.
E) synchronization.
Answer: B
Diff: 3 Page Ref: 68
42) A surrogate key may be appropriate under which of the following circumstances?
A) The available candidate keys would produce a lot of data duplication when representing relationships.
B) The primary key is numeric.
C) The available candidate keys would be prone to typographical errors.
D) The available candidate keys have little meaning to the users.
E) Both A and C
Answer: E
Diff: 2 Page Ref: 66-67
43) Which of the following is not true of surrogate keys?
A) They are meaningful to the users.
B) They are numeric.
C) They are usually generated by the DBMS.
D) They are unique.
E) They are usually hidden on forms and reports.
Answer: A
Diff: 2 Page Ref: 67-68
44) In SQL Server, the starting value of a surrogate key is called the:
A) Identity.
B) Identity Increment.
C) Identity Start.
D) Identity Seed.
E) Identity Property.
Answer: D
Diff: 3 Page Ref: 67
45) Which of the following is not true about null values?
A) A null value can mean that the value is unknown.
B) A null value is ambiguous.
C) A null value can mean that the value is known to be blank.
D) A null value can mean that no value for the field is appropriate.
E) Null values cannot be avoided.
Answer: E
Diff: 2 Page Ref: 71-72
46) MedicineCode → (MedicineName, ShelfLife, Manufacturer, Dosage)
Given the above functional dependency, which of the following statement is not known to be true?
A) MedicineCode is a determinant.
B) MedicineName is a determinant.
C) Manufacturer is functionally dependent on MedicineCode.
D) ShelfLife is functionally dependent on MedicineCode.
E) MedicineCode is a candidate key of the relation MEDICINE (MedicineName, ShelfLife, Manufacturer, Dosage, MedicineCode).
Answer: B
Diff: 2 Page Ref: 72-75
47) Which of the following functional dependency diagrams accurately represents the following situation:
• A campus has many buildings.
• Each building has a unique name.
• Each building has many rooms.
• All rooms in any given building are numbered sequentially starting at "101."
• Each room has a certain capacity, although many rooms in the same building or different buildings may have the same capacity.
• Each room is assigned to a single department.
• A department may have many rooms in one or more buildings, each with the same or different capacities.
A) BuildingName → (RoomNumber, Capacity, Department)
B) RoomNumber → (BuildingName, Department, Capacity)
C) (Department, Capacity) → (BuildingName, RoomNumber)
D) (BuildingName, Capacity) → (Department, RoomNumber)
E) (BuildingName, RoomNumber) → (Capacity, Department)
Answer: E
Diff: 3 Page Ref: 72-75
48) One important relational design principle is that:
A) every determinant must be a candidate key.
B) every candidate key must not be a determinant.
C) every primary key must be a surrogate key.
D) every determinant must be functionally dependent on the primary key.
E) every primary key must be functionally dependent on every determinant.
Answer: A
Diff: 2 Page Ref: 75
49) During the normalization process, the remedy for a relation that is not well formed is to:
A) create a surrogate key.
B) create a functional dependency.
C) break it into two or more relations that are well formed.
D) combine it with another relation that is well formed.
E) convert it into a list.
Answer: C
Diff: 1 Page Ref: 75
50) A table that meets the requirements of a relation is said to be in which normal form?
A) Relational normal form (RNF)
B) First normal form
C) Second normal form
D) Boyce-Codd normal form
E) Domain/key normal form
Answer: B
Diff: 1 Page Ref: 75
51) The first step of the normalization process is to:
A) identify all the candidate keys of a relation.
B) identify all the foreign keys of a relation.
C) identify all the functional dependencies of a relation.
D) identify all the determinants of a relation.
E) split the relation into two or more new relations.
Answer: A
Diff: 1 Page Ref: 75
52) In the normalization process, it is not necessary to:
A) identify all the candidate keys of a relation.
B) identify all the foreign keys of a relation.
C) identify all the functional dependencies of a relation.
D) identify all the determinants of a relation.
E) determine if every determinant is a candidate key.
Answer: B
Diff: 2 Page Ref: 75
53) In the normalization process, if you find a candidate key that is not a primary key then you should:
A) place the columns of the functional dependency in a new relation.
B) make the determinant of the functional dependency the primary key of the new relation.
C) leave a copy of the determinant as a foreign key in the original relation.
D) All of the above
E) None of the above
Answer: E
Diff: 3 Page Ref: 75
54) In the normalization process, if you find a candidate key that is not a determinant then you should:
A) place the columns of the functional dependency in a new relation.
B) make the determinant of the functional dependency the primary key of the new relation.
C) leave a copy of the determinant as a foreign key in the original relation.
D) All of the above
E) None of the above
Answer: D
Diff: 2 Page Ref: 75
55) In the normalization process, if you find that every determinant in a relation is a candidate key then you have determined that:
A) the relation is well formed.
B) the relation needs to be broken into two or more new relations.
C) surrogate keys in the relation may not be correctly linked to other relations.
D) the relation needs to have foreign keys added in order to be correctly linked to other relations.
E) referential integrity constraints concerning the relation need to be established.
Answer: A
Diff: 2 Page Ref: 75
56) Although Microsoft Access is a personal database, it is still subject to the following modification problem(s):
A) problems adding data.
B) problems changing data.
C) problems deleting data.
D) All of the above
E) None of the above
Answer: D
Diff: 1 Page Ref: 82-83
57) In Microsoft Access, relationships between tables are created:
A) by the Relationships button on the Create command tab.
B) by the Relationships button on the Home command tab.
C) in the Relationships window.
D) in the Table window of the table containing the primary key.
E) in the Table window of the table containing the foreign key.
Answer: C
Diff: 1 Page Ref: 92
58) In Microsoft Access, a relationship between two tables is created:
A) by entering the name of the foreign key in the appropriate table in Design View.
B) by entering the name of the primary key in the appropriate table in Design View.
C) by dragging the primary key column of one table onto the foreign key column of the other table in the Relationships window.
D) by dragging the foreign key column of one table onto the primary key column of the other table in the Relationships window.
E) by dragging the primary key column of one table onto the primary key column of the other table in the Relationships window.
Answer: C
Diff: 2 Page Ref: 92
59) In Microsoft Access, referential integrity constraints are created:
A) by setting a property value on the primary key in the table which contains it.
B) by setting a property value on the foreign key in the table which contains it.
C) by setting a property value on the primary key in the Relationships window.
D) by setting a property value on the foreign key in the Relationships window.
E) by checking the Enforce Referential Integrity check box in the Edit Relationships dialog box.
Answer: E
Diff: 2 Page Ref: 92
No comments:
Post a Comment