Deck 4: The Relational Model: Advanced Topics

Question 1

Which of the following items is not identified in relational database design documentation?

A)primary key field for each entity
B)possible user views for each entity
C)proper one-to-many relationships between the entities
D)attributes needed for each normalized entity

Answer 1

B

Question 2

When the Unique property for an index is set to Yes in Access, every value in the index must be unique.

Answer 2

True

Question 3

What is the purpose of an index?

A)to show which database objects depend on each other
B)to identify primary, secondary, and alternate keys
C)to improve database performance when queries request a sort order for records
D)to store unique data for each record in the table

Answer 3

C

Question 4

A(n) _____ represents a field or combination of fields that may not be unique, but is commonly used for retrieval and thus should be indexed.

A)alternate key
B)combo key
C)secondary key
D)indexed key

Question 5

What shape does an E-R diagram use to represent entities?

A)rectangle
B)oval
C)triangle
D)diamond

Question 6

ProductType is a foreign key field in the Orders table and is constrained to a list of valid entries stored in the ProductTypes table. What problem does this design prevent?

A)creation anomalies
B)deletion anomalies
C)update anomalies
D)record locking

Question 7

When you build a relational database for an organization, what is a good starting point for identifying required user views?

A)Collect the organization's forms and reports.
B)Video each user.
C)Conduct a detailed survey.
D)Attend staff meetings.

Question 8

In the following Database Design Language (DBDL) notation, what does SK stand for? Employees (EmpID, LastName, FirstName, SSN, DeptNum)
AK SSN
SK LastName, FirstName
FK DeptNum à Departments

A)subkey
B)start key
C)SQL key
D)secondary key

Question 9

A user view of data is _____.

A)a table created by a particular user
B)a report requested by users
C)the specific data needed by a person or process for a particular task
D)a form used for data entry

Question 10

Building the entities, attributes, constraints, and relationships in a particular RDBMS is called the _____.

A)information-level design
B)conceptual design
C)entity-relationship (E-R) design
D)physical-level design

Question 11

During which process do you typically create an entity-relationship (E-R) diagram?

A)physical-level design
B)troubleshooting user views
C)information-level design
D)maintenance

Question 12

Access uses Database Design Language (DBDL) to diagram relationships in the Relationship report.

Question 13

Which fields should you index in a table?

A)every field
B)fields that are commonly used for sorting
C)fields that contain sensitive data
D)fields that are not part of a relationship

Question 14

Which field in a table does Access index by default?

A)primary key field
B)any numeric field
C)first field in the table
D)foreign key field

Question 15

A(n) _____ is a field that contains unique data for each record in the table but is not used as the primary key field for security or other reasons.

A)alternate key
B)secondary key
C)associate key
D)backup key

Question 16

An E-R diagram uses diamonds to illustrate relationships between tables.

Question 17

Information-level design is independent of any particular DBMS that the organization will ultimately use.

Question 18

Which of the following fields would you most likely index but set the index's Unique property to No?

A)EmployeeID
B)StreetAddress
C)OfficeNumber
D)LastName

Question 19

When referential integrity is enforced on a one-to-many relationship in Access, you may not delete a record in the "one" table that has related records in the "many" table.

Question 20

In Database Design Language (DBDL), which items do you underline?

A)table names
B)columns that make up the foreign key
C)data types
D)columns that make up the primary key

Question 21

The E-R model (ERM) represents entities, attributes, and _____.

A)indexes
B)views
C)properties
D)relationships

Question 22

Which of the following tools does not help you document relationships between tables?

A)Database Design Language
B)user views
C)entity-relationship diagram
D)entity-relationship model

Question 23

In an entity-relationship model (ERM) for a many-to-many relationship, what do you call the middle entity that links two other tables?

A)linking entity
B)composite entity
C)consolidated entity
D)secondary entity

Question 24

What type of relationship do two tables have when their primary keys match?

A)one-to-one
B)one-to-many
C)many-to-many
D)many-to-one

Question 25

In an entity-relationship model (ERM), what information appears in ovals?

A)entities
B)attributes
C)relationships
D)dependencies

Question 26

In an entity-relationship model (ERM), what does a diamond represent?

A)the "one" part of the relationship
B)the "many" part of the relationship
C)the relationship
D)an entity

Question 27

In a database for a health care clinic, a medical specialty is listed only once in the Specialties table, but each specialty may be linked to many doctors in the Doctors table. What type of relationship do the Specialties and Doctors tables have?

A)one to many
B)one to one
C)many to one
D)many to many

Question 28

A query includes the DeptID and DeptName fields from the Departments table and the EmployeeLast field from the Employees table. Both tables are shown below. What must you change before you can reassign the DeptID field value for a particular employee in the query results? Departments (DeptID, DeptName)
Employees (EmpID, EmployeeFirst, EmployeeLast, DeptID)

A)Set DeptID as the primary key field in the Employees table.
B)Add the DeptName field to the query.
C)Remove the EmpID field from the query.
D)Replace the DeptID field from the Departments table with the DeptID field from the Employees table.

Question 29

Which of the following statements is not true about primary key fields?

A)The primary key field must participate in a one-to-many relationship.
B)The primary key field contains unique data for each record in the table.
C)The primary key field is automatically indexed.
D)The primary key field is the linking field in the table on the "one" side of a one-to-many relationship.

Question 30

Which of the following Access features does not demonstrate the power of one-to-many relationships?

A)select queries involving related tables
B)forms with subforms
C)subdatasheets
D)indexes

Question 31

What can you create in Access to show data in a single record and all its related records?

A)one-to-one relationship
B)lookup reports
C)form and subform
D)related user views

Question 32

Which of the following is not true about one-to-many relationships?

A)The linking field in the "one" table is always the primary key field for that table.
B)A foreign key field cannot also be the primary key field for a particular table.
C)The linking field must have the same name in the primary and related tables.
D)One record in the "one" table can be linked or related to many records in the "many" table.

Question 33

Because you cannot directly create a many-to-many relationship between two tables, what must you create to link the tables together?

A)junction table
B)entity-relationship diagram
C)Cartesian join
D)secondary table

Question 34

In Access Table Datasheet View, how can you display the records from a related table?

A)Click the Lookup button on the ribbon.
B)Click the Related Records button on the ribbon.
C)Right-click a field and then click Relationships on the shortcut menu.
D)Click the expand button to expand a subdatasheet.

Question 35

A one-to-many relationship with _____ means that a record in the parent table must be established before a related record in the child table can be entered.

A)cardinality
B)access restrictions
C)referential integrity
D)alternate keys

Question 36

How does the Access Relationships window indicate the "many" field in a one-to-many relationship?

A)ring and crow's foot
B)number 1
C)filled circle
D)infinity symbol

Question 37

Which of the following is a type of E-R diagram?

A)Access Database Documenter
B)crow's foot notation
C)entity tree notation
D)Database Design Language (DBDL)

Question 38

A query includes the DeptID and DeptName fields from the Departments table and the EmployeeLast field from the Employees table. Both tables are shown below. What happens when you update the DeptName field value for an employee? Departments (DeptID, DeptName)
Employees (EmpID, EmployeeFirst, EmployeeLast, DeptID)

A)Access displays an error message and does not accept the update.
B)The DeptName field value changes for all employees in that department.
C)The DeptName field value changes for that one employee only.
D)Access changes the DeptID field value in the same record.

Question 39

Which properties do you set to display a list of possible values that are helpful when you enter or update data in a foreign key field?

A)Indexed properties
B)Lookup properties
C)Foreign Key List properties
D)Subdatasheet properties

Question 40

Which of the following Access features provides a drop-down list of values for a field?

A)subdatasheet
B)lookup field
C)subform
D)Relationship report

Question 41

How might you resolve an inappropriate Cartesian join?

A)Convert the join type to a RIGHT join.
B)Connect the two tables through a third junction table.
C)Convert the join type to a LEFT join.
D)Change the query to an update query.

Question 42

When two tables in a query share no common fields, each record in one table connects to each record in the other table, creating a(n) _____.

A)many-to-many relationship
B)one-to-one relationship
C)outer join
D)Cartesian join

Question 43

Describe the different types of keys illustrated in Database Design Language (DBDL) and how they are notated.

Question 44

A _____ serves on the "many" side of a one-to-many relationship with each of two other tables.

A)secondary key
B)junction table
C)lookup table
D)connecting table

Question 45

Define the types of cardinality in a database and explain the cardinality of primary keys and foreign keys.

Question 46

How would you set up a relationship between a table of Books and a table of Authors in a relational database?

Question 47

How could you determine the relationship between a table of Consultants and a table of Projects at a consulting firm?

Question 48

A query creates a Cartesian join between one table with 5 records and another table with 20 records. How many records are displayed in the results?

A)100
B)25
C)15
D)4

Question 49

Which of the following is a valid reason for creating a one-to-one relationship between two tables?

A)One table would otherwise not participate in a relationship.
B)The tables create a Cartesian join.
C)The tables are rarely updated.
D)Data needs to be imported from separate sources.

Question 50

Explain the difference between information-level design and physical-level design.