Deck 6: Relational Database Design

Question 1

The REA conceptual model can be implemented only with relational database software.

Answer 1

False

Question 2

A conceptual database model is independent of any particular database software package.

Answer 2

True

Question 3

A physical database model is independent of any particular database software.

Answer 3

False

Question 4

In the relational database model each table represents either an entity or a relationship between entities.

Question 5

The order in which columns are placed in a relational database table is crucial to maintain referential integrity.

Question 6

Entity integrity refers to a primary key that will allow null values.

Question 7

The following pet table
is a good example of a relational table.

Question 8

In the following pet table
the shots received column is a repeating group because the column contains multiple values.

Question 9

When converting a REA model into relational tables, each entity object becomes a relation.

Question 10

The rule for converting relationships for which the maximum participation cardinalities are many for both entities is to post the primary key of either entity into the table of the other entity.

Question 11

A concatenated relationship key contains fields from one relational database table.

Question 12

Efficient database design avoids null values as much as possible and uses the least amount of tables.

Question 13

Relationships may have attributes; these attributes should be attached to the relationship on an entity-relationship diagram or listed in the relationship section of an entity-relationship grammar.

Question 14

All About Pets needs to track information about quantities of inventory items sold in various sales transactions. For example, the enterprise needs to know 5 cans of puppy food, 1 leash, 2 sacks of kitty litter, and 4 cans of kitten food were sold in Sale 17. The most appropriate assignment for the quantity sold attribute is to the relationship between inventory type and sale.

Question 15

The view in which the field data types and field properties for a database table can be seen in Microsoft Access is called the datasheet view.

Question 16

Fields for which the data type is "text" may be used in calculations in queries.

Question 17

You will be able to join two fields of different data types together as long as the data values match exactly.

Question 18

Referential integrity determines whether or not null values are allowed in a field.

Question 19

To communicate to Microsoft Access the fact that a primary key of one entity has been posted as a foreign key in another table, one must simply use the same field name and data type in each of the two tables.

Question 20

A primary key in Microsoft Access can only contain one field.

Question 21

Each column of a relational table represents a (an)

A) Relation
B) Record
C) Attribute
D) Cardinality
E) Tuple

Question 22

The purpose of foreign keys is to

A) Uniquely and universally identify instances of the entity into which they are posted.
B) Link relations so the database can be used to store business data and to generate useful information.
C) Create null values in the database.
D) Determine minimum cardinalities of relationships between entities.
E) Determine maximum cardinalities of relationships between entities.

Question 23

In relational database design, the definition of a fact is:

A) The pairing of the primary key and candidate key.
B) The pairing of a candidate key data value with another attribute data value.
C) The placement of data into a cell.
D) A row in a table.
E) A foreign key.

Question 24

What potential problem exists in this table structure?

A) The "fact rule" is violated if more than one account is at the same bank.
B) The "fact rule" is violated if more than one account has the same account balance.
C) The "fact rule" is violated if more than one bank has the same address.
D) All of the above are potential problems.
E) None of the above are potential problems.

Question 25

In relational database design, a low load indicates

A) Most values of the attribute are expected to be null.
B) All values of the attribute are expected to be null.
C) All values of the attribute are expected to be non-null.
D) Most values of the attribute are expected to be non-null.
E) Nothing is known about how many values of the attribute will be null.

Question 26

When implementing the relationships of an entity-relationship diagram into relational tables, the first things you should consider are the _________ cardinalities to minimize _________.

A) minimum, redundancy
B) minimum, load
C) maximum, redundancy
D) maximum, load
E) maximum, null values

Question 27

On average, 99 out of 100 sales have related cash receipts (there is usually 1 sale out of 100 that either results in a sale return or becomes a bad debt). On average, 45 out of 100 cash receipts are related to sales (the company also generates revenue through rentals, which it keeps track of in a separate entity set; 50 out of 100 of its cash receipts result from those rentals, the other 5 out of 100 result from financing events). Considering both redundancy and load, which of the following would be the best relational table implementation solution?

A) Make a sale table, a cash receipt table, and a table for the relationship.
B) Make a sale table, a cash receipt table, and post the key of sale into the cash receipt table.
C) Make a sale table, a cash receipt table, and post the key of cash receipt into the sale table.
D) Both A and B are equally good solutions.
E) Both A and C are equally good solutions.

Question 28

The rule for converting conceptual model relationships with maximum participation cardinalities of "many to many" into relational database tables is

A) Create a separate table identified by the key attribute of the entity that has the most instances.
B) Create a relation that can have no attributes of its own.
C) Post the key attribute of either entity into the relation of the other entity.
D) Post the key attribute of the table with mandatory participation into the table with optional participation.
E) Create a separate table identified by a concatenated primary key comprised of the key attributes from both entity tables.

Question 29

If you have two entities that are related with a cardinality pattern of (1,1) - (1,N) how many tables must you include in a correctly designed database to represent this entity-relationship cluster?

A) one
B) two
C) three
D) four
E) five

Question 30

Which example relational tables contain data that best represents the cardinalities shown in the following conceptual model?

A) Example 1
B) Example 2
C) Example 3
D) Example 4
E) None of the examples contains data that accurately represents the cardinalities depicted in the conceptual model.

Question 31

Given the following set of relational database tables, what must have been the conceptual model's participation cardinalities between the entities Sale and Inventory?

A) (1,N) Sale -- (1,1) Inventory
B) (1,N) Sale -- (0,N) Inventory
C) (0,N) Sale -- (1,N) Inventory
D) (1,1) Sale -- (0,N) Inventory
E) (0,1) Sale -- (0,1) Inventory

Question 32

Disregarding minimum cardinalities, when converting conceptual models into relational tables

A) A "one to one" (maximum cardinalities) relationship should be implemented with a separate table.
B) All relationships, regardless of maximum cardinalities, should be implemented with the primary key of one entity posted into the other entity's table as a foreign key.
C) A "one to many" (maximum cardinalities) relationship should be implemented by posting the key of the one entity table into the many entity table.
D) All relationships, regardless of maximum cardinalities, should be implemented with separate relationship tables.
E) A "one to many" (maximum cardinalities) relationship should be implemented by posting the key of the many entity table into the one entity table.

Question 33

An auditor examined the database for Trent's Textiles Corporation. During her examination, she noticed two cash disbursements in the Cash Disbursements table had blanks (null values) in the vendor number field. Assuming no data entry error has occurred, what is the most plausible explanation for these blanks?

A) There is no plausible explanation - an error must have occurred.
B) Referential integrity was not established between Vendor and Cash Disbursement
C) The participation of Cash Disbursement in the relationship with Vendor is optional
D) The participation of Vendor in the relationship with Cash Disbursement is mandatory
E) The participation of Cash Disbursement in the relationship with Vendor is mandatory

Question 34

Imagine you are implementing the relationships of an entity-relationship diagram into relational table format. The rules for minimum cardinalities tell you to post the key of entity A into the table for entity B, and the rules for maximum cardinalities tell you to post the key of entity B into the table for entity A. What do you do?

A) Consider load, and if posting A into B would result in a low load, post A into B
B) Consider load, and if posting B into A would result in a low load, post B into A
C) Consider load, and if posting B into A would result in a high load, post B into A
D) Consider load, and if posting A into B would result in a high load, post A into B
E) Consider load, and if posting A into B would result in a medium load, post A into B

Question 35

For which of these relationships does an efficient relational implementation of relationship AB require posting a foreign key? (in other words, for which relationship(s) would you always post a foreign key instead of making a separate table)

A) JK only
B) JK and PQ only
C) PQ only
D) YZ only
E) JK, PQ, and YZ

Question 36

When creating a table in Microsoft Access, which of the following fields should ALWAYS be specified as a "Number" field (and should thus NEVER be specified as either a "Date", "Currency", or "Text" field)?

A) Quantity sold
B) Customer number
C) Social security number
D) Invoice number
E) Total sales dollar amount

Question 37

In Microsoft Access, enabling the "Cascade Update Related fields" option in the relationship layout accomplishes which of the following:

A) If the end user makes a change in the primary table it will change data in all tables.
B) If the end user makes a change in an unrelated table the primary table will be updated.
C) If the end user makes a change in the primary table the same change will be automatically be made in the related table.
D) If the end user makes a change in the related table the same change will be automatically be made in the primary table.
E) If the end user makes a change in an unrelated table, the related table will be updated.

Question 38

Which of the following pieces of data would most likely be stored (rather than derived via a query) in a relational database based on the REA model and implemented in Microsoft Access?

A) The number of different inventory items purchased from a vendor during a month.
B) An individual customer's accounts receivable balance on a specific date.
C) The quantity on hand of a particular inventory item on a specific date.
D) Actual unit selling price of an inventory item on a specific sale.
E) The balance of the company's checking account on a specific date.

Question 39

Which of the implementation choices depicted below represents the most appropriate Microsoft Access implementation of the Sale-Inventory relationship portrayed in this conceptual model?

A) Implementation Choice 1
B) Implementation Choice 2
C) Implementation Choice 3
D) Implementation Choice 4
E) Implementation Choices 1 and 2 are equally correct Access implementations of the relationship.

Question 40

Given the following relationship layout from Microsoft Access, which set of tables has data that is consistent with referential integrity?

A) Table Choice 1
B) Table Choice 2
C) Table Choice 3
D) Table Choice 4
E) None of the table set choices have data with referential integrity.

Question 41

Why are redundancies in data inefficient and problematic?

Question 42

Columns in a relational database structure are also called ______ ______, _______ or _______.

Question 43

Rows in a relational database structure are also called ______ ______, ________ or ________.

Question 44

What is the purpose of a primary key attribute in a relational database table?

Question 45

What is the correct action to take when converting a conceptual entity-relationship model into relational tables for a relationship that has a participation cardinality pattern of (1,1) - (1,1)?

Question 46

Say you have been brought in to check the work of a person who developed a database for Marty's Manufacturing. The conceptual model for Marty's Manufacturing includes the following relationship:
The relational database tables, which are implemented in Microsoft Access, include a table for Production Order and a table for Production Run. The primary key of Production Order (PO) is posted into the Production Run table as a foreign key. You are required in your inspection to ensure that each minimum and maximum cardinality in the conceptual model is appropriately reflected in the table design. What should you look for in the table design to determine whether the 1 minimum on Production Run is appropriately reflected in the database tables?

Question 47

In Microsoft Access, how should one delete a relationship in the relationship layout?

Question 48

In Microsoft Access, does enabling cascade updates cause data entry of primary key values for new records to be automatically posted to the related foreign key fields? Explain your response.

Question 49

What is the first step in converting a conceptual entity-relationship model into relational database tables?

Question 50

Salespeople for the Coverall Insurance Company meet with customers and try to sell them one or more of the company's five insurance policies. If they are successful in their sales efforts they obtain a signed insurance policy from the customer and the initial check for the first premium. These are sent to the company's headquarters for processing. The contract is kept on file, and the check is given to the cashier for processing into one of the company's cash accounts. Policy payment reminders are mailed to the customer (monthly or quarterly, depending on the terms of the policy). The customer sends subsequent policy payments to the cashier for processing.
Below is a partial REA business process level conceptual model for Coverall. Convert this conceptual model into a logical model consisting of a set of minimal relational database tables. Underline primary keys and write FK next to foreign keys.

Question 51

Explain the relational logical structure that is used in relational databases.

Question 52

Describe the two ways in which the one-fact-one-place rule could be violated and explain how to prevent or fix the violations.

Question 53

With respect to relational database design in Microsoft Access, explain the difference between enforcing referential integrity and creating a field property of required data entry. Use the following example to support your explanations.
The conceptual model from which the relational tables were derived is as follows:
The relational tables include a Cash Account table and a Bank table as follows:

Question 54

Convert the following conceptual model into a set of minimal relational database tables. Underline primary keys and write FK next to foreign keys in your tables. List any assumptions you make with regard to load for any of the tables.