Question 1

Convert the following table to first normal form:?Branch (BranchNum, BranchName, (BookCode, Title, OnHand) ) where BranchNum is the primary key

Accepted Answer

The table in its original form is not in first normal form because it contains a repeating group of (BookCode, Title, OnHand) within the Branch table. To convert it to first normal form, we need to remove the repeating group and create a separate table for books. However, simply creating a Book table as in option C would not be sufficient because it does not take into account the relationship between branches and books.

Option A is not a good choice because it eliminates the information on book inventory at each branch. Option B is also not ideal because it separates the book information from the branch information and requires a separate table to link them.

Option D is the best choice because it creates a separate Book table while also maintaining the relationship between branches and books through the use of a composite primary key (BranchNum, BookCode). This allows us to store information on book inventory at each branch and easily query for a specific branch or book.

Question 2

Which of the following is an example of an enforcing restriction?

Accepted Answer

Enforcing restrictions in foreign keys ensures that the values in the foreign key match those in the primary key of the related table, thus maintaining referential integrity. This is an example of an enforcing restriction. A, B, and C are examples of constraints, but not specifically enforcing restrictions.

Question 3

In DBDL documentation, what element of a table is followed by an arrow pointing to the table identified by the element?

Accepted Answer

Foreign keys are the elements of a table that are followed by an arrow pointing to the table identified by the element. This arrow pointing to the table represents the relationship between the two tables.

Question 4

An alternative to the primary key of a table is listed with which abbreviation in DBDL?

Accepted Answer

AK refers to an alternative key, which is a candidate key other than the primary key of a table. SK refers to a surrogate key, which is an artificially generated key used as a substitute for a natural primary key. FK refers to a foreign key, which is a column or combination of columns in one table that refers to the primary key of another table. PK refers to the primary key of a table. Since we are looking for an alternative key to the primary key, the best choice is D - AK.

Question 5

When implementing the physical-level design, for secondary keys, you must ensure that it is possible to retrieve data rapidly based on a value of which key?

Accepted Answer

Secondary keys are specifically designed to allow rapid data retrieval based on their values, complementing the primary key which uniquely identifies records.

Question 6

If each employee works in a single department and each department has several employees, what is the relationship between departments and employees?

Accepted Answer

This relationship is one-to-many because one department can have several employees, but one employee can only belong to one department.

Question 7

Which type of key is usually an automatic numbering data type, such as the Access AutoNumber data type?

Accepted Answer

The Access AutoNumber data type is an example of a surrogate key, which is an automatically generated unique identifier used as a primary key in a database. Surrogate keys do not have any intrinsic meaning or relationship to the data they identify, unlike natural or logical keys.

Question 8

After the information-level design is completed, what is the next step?

Accepted Answer

The next step after completing the information-level design is the physical-level design, which involves converting the logical design into actual physical components such as databases, hardware, and software.

Question 9

What is an entity that does not require a relationship to another entity for identification?

Accepted Answer

An independent entity is one that can be identified on its own without needing a relationship to another entity. Examples of independent entities include a person, a company, or a product. Alternative entities may be used interchangeably with other entity types, foreign entities are entities that are related to another through a foreign key, and single entities are not a recognized entity type in database design.

Question 10

What type of primary key is system-generated and is usually hidden from users?

Accepted Answer

A surrogate key is a system-generated primary key that is usually hidden from users. It is often used in situations where there is no suitable natural or candidate key available.

Question 11

Which of the following is used to improve performance in frequently used columns by maintaining a sort order but does not enforce constraints?

Accepted Answer

A nonunique index is used to improve performance in frequently used columns by maintaining a sort order, but it does not enforce constraints. Surrogate keys, artificial keys, and unique indexes all enforce constraints in addition to improving performance. Therefore, D is the correct answer.

Question 12

As you design the user view, which aspect of the table are the properties of the entities you choose?

Accepted Answer

The properties of the entities are best represented by the columns of the table. Each column represents a specific attribute or characteristic of the entity being represented, such as name, age, or address. The rows represent individual instances or records of the entity, with each cell representing the value of a specific attribute for that instance. Therefore, the columns are the most important aspect to consider when designing the user view of the table.

Question 13

Consider the following two tables where EmployeeNum is primary key in both tables. What is the result of combining the two tables??Employee (EmployeeNum, LastName, FirstName, WageRate, SocSecNum, DepartmentNum)?Employee (EmployeeNum, LastName, FirstName, Street, City, State, PostalCode)

Accepted Answer

The two tables can be combined into one table with all columns from both tables, resulting in a table with columns: EmployeeNum, LastName, FirstName, WageRate, SocSecNum, DepartmentNum, Street, City, State, and PostalCode. Option B is the only choice that includes all of these columns without duplication.

Question 14

What is the correct order of steps in the information-level design method??1. Normalize the tables.?2. Represent the user view as a collection of tables.?3. Merge the result into the cumulative design.?4. Represent all keys.

Accepted Answer

The correct order of steps in the information-level design method is:
1. Represent the user view as a collection of tables.
2. Normalize the tables.
3. Represent all keys.
4. Merge the result into the cumulative design. 
Therefore, the best choice is C with the correct order of steps.

Question 15

What is a column that you create for an entity to serve solely as the primary key and that is visible to users?

Accepted Answer

An artificial key, also known as a surrogate key, is a type of primary key that is added to a table specifically for the purpose of serving as the primary identifier for each record. Unlike natural keys that are derived from existing data attributes and have a logical relationship to the data, artificial keys have no business meaning and are usually created when a suitable natural key does not exist or is impractical to use. They are visible to users and are often used to ensure each record can be uniquely identified, especially in complex database structures.

Question 16

What type of key is also called a logical key?

Accepted Answer

An intelligent key, also known as a logical key, is a key that is made up of one or more columns that have some kind of inherent meaning or value. This is in contrast to a surrogate key, which is a key that has no inherent meaning or value and is used purely as a unique identifier. Secondary keys are indexes that allow for faster querying, and synthetic keys are keys that are generated or computed by the database system.

Question 17

What is a relationship that is necessary for identification?

Accepted Answer

An identifying relationship is necessary for identification because it establishes a connection where the existence of a child entity depends on the parent entity. This type of relationship is crucial for defining how entities relate to each other in a way that one entity's identity is contingent upon the other.

Question 18

Which of the following is a set of requirements that is necessary to support the operations of a particular database user?

Accepted Answer

A user view is a set of requirements that specify which data the user needs to access, and how that data should be presented. It includes details such as which tables and attributes the user needs to see, as well as any filters or sorting requirements. User tables, attributes, and fields are all components of the underlying database structure and do not specifically address the needs of a particular user.

Question 19

When a DBMS cannot enforce data and access restrictions, how should they be enforced?

Accepted Answer

Data and access restrictions can be enforced by peripheral programs written specifically to access the database. These programs can impose rules on users and limit their access to specific data based on their permissions. This approach is often used in conjunction with a DBMS to ensure that data remains secure and that only authorized users can access it.

Question 20

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 ____.

Accepted Answer

The crucial issue in making this determination is the independence between the relationships. If the relationships are independent, then it makes sense to have separate many-to-many relationships. If there is a dependency between the entities involved, then a single many-to-many-to-many relationship may be more appropriate. The primary key, redundancy, and primary index are all important considerations in database design, but they are not the most crucial issue in this specific determination.

Quiz 6: Database Design 2: Design Method