CONCEPT OF DATABASE EXAM 2

A data dictionary is sometimes described as ____________.

"the database designer's database"

If one shop manager can only be in charge of a single shop, what kind of relationship do the entities shop manager and shop have?

1:1

In a school, every class should have one class teacher. A teacher can be a class teacher of only one class. What relationship exists between TEACHER and CLASS?

1:1

The _____ relationship should be rare in any relational database design.

1:1

In a university, the data of students such as student id, name, and address are recorded in the STUDENT table and the majors, and the data about them, is stored in the table MAJORS. Suppose a student is allowed to take only one major. What kind of relationship exists between students and majors?

1:M

The database of a theater has a list of plays in the PLAY table and a customer's list in the CUSTOMER table. The relationship, CUSTOMER attended a PLAY, is M:N. Which of the following relations with the BRIDGE entity should be used instead?

1:M CUSTOMER, BRIDGE and 1:M PLAY, BRIDGE

A car dealership has a table with cars on sale (uniquely identified by BIN number) and a table with its sales representatives. What kind is the relationship: "representative sold car"?

1:N

Consider the tables: How many rows, and columns does table1 table2 have?

6 rows, 5 columns

When adding new tables and deciding names for the attributes, which of the following should be avoided to lessen confusion?

Homonyms

A table of products with a foreign key of vend_code references to the table vendor. Which of the following could be an unintended effect of deleting an entry in the vendor table?

It damages the referential integrity.

The ____ allows using independent tables linked by common attributes.

JOIN

A video game store has a table with its users. What type of data type is best for the attribute of being an adult?

Logical

In a university, the data of students such as student id, name, and address are recorded in the STUDENT table and the courses are stored in a COURSE table by the course code, number of credits, and professor teaching. Usually, a student takes up to 5 courses per cycle, and each course has a minimum of 2 students. What kind of relationship exists between the courses and students?

M:N

An unnormalized data structure includes entities that can have relations with many instances of another entity type, for instance, employees that can be assigned to multiple projects. How does this affect adding, updating, and deleting data?

Multiple rows need to be updated.

Which of the following is a reason to use indices in a database?

Retrieve data ordered by a specific attribute.

When using a foreign key the same value shows up in several rows. Why is this not considered data redundancy?

The elimination of the foreign key would result in losing the information of the relation.

A null is created when you press the Enter key or the Tab key to move to the next entry without making a prior entry of any kind.

True

All relational tables satisfy 1NF requirements.

True

If the attribute (B) is functionally dependent on a composite key (A) but not on any subset of that composite key, the attribute (B) is fully functionally dependent on (A).

True

In a table of clients of a store, there is a field to store how much a client has spent. Which of the following is the best description of the attribute domain of how much the client has spent?

[0, ∞)

unique

a single data value

Consider the following table: Which of the following pair of attributes could be a primary key for the table?

attribute 1, attribute 2

Consider the following table Which of the following pairs of attributes could have a functional dependence?

attribute 2, attribute 4

Consider the following table Which of the following could be a primary key?

attribute 3

An index key can have multiple _____ (a composite index).

attributes

A _____ contains all of the attribute names and characteristics for each table in the system.

data dictionary

In a(n) _____ diagram, the arrows above the attributes indicate all desirable dependencies.

dependency

Which of the following options is more adequate for a primary key in a table of employees?

employee social security number

According to the data-modeling checklist, _____ should be nouns that are familiar to business, should be short and meaningful, and should document abbreviations, synonyms, and aliases for each entity.

entity names

Which of the following traits should you balance with design integrity?

flexibility

To eliminate repeating groups and convert a table into the first normal form (1NF), what can you change in a table to convert multivalued attributes into single-valued attributes? _____.

focus

The proper use of _____ keys is crucial to controlling data redundancy.

foreign

In a database context, the word _____ indicates the use of the same attribute name to label different attributes.

homonym

A(n) ______ table is the implementation of a composite entity used to implement an M:N relationship.

linking

Relations are a ________ construct, thus it's easier for users to think in terms of tables.

mathematical

Which unnormalized attribute in your data structure could make your data management more difficult?

multivalued

If a table is in fourth normal form, it is in third normal form and has no _____.

multivalued dependencies

Which type of dependency illustrates how one key determines multiple values of two other attributes and those attributes are independent of each other?

multivalued dependency

If a table in 2NF exhibits a transitive dependency, what does the primary key rely on to functionally determine nonprime attributes?

other non-prime attributes

Consider the following table: What is the result of πprice (table)?

price 19 8 22

Because a partial dependency can exist only when a table's primary key is composed of several attributes, a table whose _____ key consists of only a single attribute is automatically in 2NF once it is in 1NF.

primary

The one-to-many (1:M) relationship is easily implemented in the relational model by putting the _________of the "1" side in the table of the "many" side as a ________.

primary key, foreign key

Consider a database with the following tables: What kind of integrity is violated in the previous database?

referential integrity

From the table's user, the table contains _____________.

related entities

Relational algebra defines the theoretical way of manipulating table contents using _______..

relational operators

In a student's table of a large university the name attribute could be used as a _______.

secondary key

A(n) _____ is an orderly arrangement used to logically access rows in a table.

A(n) _____ is an orderly arrangement used to logically access rows in a table.

By using the relational data model the designer focuses primarily on the physical storage details.

By using the relational data model the designer focuses primarily on the physical storage details.

A car wash business has a list of all its clients in a table where each client is uniquely identified by a CLIENT_ID attribute; CLIENT_ID is a primary key. Another table with all the services provided contains a SERVICE_NUM, SERVICE_TYPE, DATE, PRICE, CLIENT_ID. Which of the previous attributes in the table of services should be a foreign key?

CLIENT_ID

The ____________operator subtracts one table from the other.

DIFFERENCE

The _________operator uses one single-column table (e.g., column "a") and one two-column table (e.g., columns "a" and "b")..

DIVIDE

Of the following normal forms, _____ is mostly of theoretical interest.

DKNF

What name has the set of permissible values of a column or attribute?

Domain

A primary key can't be null, but it can contain null attributes.

False

A table is a three dimensional structure composed of depth, width, and height.

False

Character data can contain any character or symbol intended for mathematical manipulation.

False

Normalization is a process that is used for changing attributes to entities.

False

Only a single attribute, not multiple attributes, can define functional dependence.

False

The idea of determination is unique to the database environment.

False

The order of the rows and columns is important to the DBMS.

False

The row's range of permissible values is known as its domain.

False

Proper data _____ design requires carefully defined and controlled data redundancies to function properly.

storage

Character data, also known as ___ data, can contain any character or symbol not intended for mathematical manipulation.

string

The _____ is actually a system-created database whose tables store the user/designer-created database characteristics and contents.

system catalog

Which type of demoralized table holds report data when creating a tabular report in which the columns represent data that are stored in the table as rows?

temporary

According to a standard data-modeling checklist, the non-key entities in an entity-relationship (ER) data model must be fully dependent on _____.

the primary key

Normalization works through a series of stages called normal forms. For most purposes in business database design, _____ stages are as high as you need to go in the normalization process.

three

On a table of students the attributes of student code, name, surname, gpa, birth date, and phone number. What data type is more appropriate for the gpa attribute?

umeric

A(n) _____ index is an index in which the index key can have only one pointer value (row) associated with it.

unique

When you define a table's primary key, the DBMS automatically creates a(n) _____ index on the primary key column(s) you declared.

unique

Unnormalized tables yield no simple strategies for creating virtual tables known as _____.

views

The _____ relationship is the "relational model ideal."

.1:M

Which of the following structures is used in a complex, multilevel, multisource data environment?

2NF

Consider the tables:

3

When using a data-modeling checklist to design a new database structure based on the business requirements of the end users, what is the minimum preferred normal form all entities should be in?

3NF

Some very specialized applications may require normalization beyond the _____.

4NF

Which of the following tables contains no rows with two or more multivalued facts about an entity?

4NF

Explain the Boyce-Codd normal form (BCNF). How is it related to other normal forms? Your Answer: Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF. A table is in BCNF if every determinant is a candidate key. It eliminates anomalies that can still exist in 3NF.

Answer: See the 'Rationale' field for sample responses. Rationale: A table is in Boyce-Codd normal form (BCNF) when every determinant in the table is a candidate key. A candidate key has the same characteristics as a primary key, but for some reason, it was not chosen to be the primary key. Clearly, when a table contains only one candidate key, the 3NF and the BCNF are equivalent. In other words, BCNF can be violated only when the table contains more than one candidate key. Most designers consider the BCNF to be a special case of the 3NF. In fact, if the techniques shown in this chapter are used, most tables conform to the BCNF requirements once the 3NF is reached.

Define an index. Explain the role of indexes in a relational database. Your Answer: An index is a data structure that improves the speed of data retrieval operations on a table. It contains index keys and pointers that reference the location of rows in the table. Indexes allow the database to quickly locate specific records without scanning the entire table.

Answer: See the 'Rationale' field for sample responses. Rationale: An index is an orderly arrangement used to logically access rows in a table. From a conceptual point of view, an index is composed of an index key and a set of pointers. The index key is, in effect, the index's reference point. More formally, an index is an ordered arrangement of keys and pointers. Each key points to the location of the data identified by the key. DBMSs use indexes for many different purposes. An index can be used to retrieve data more efficiently. Indexes can also be used by a DBMS to retrieve data ordered by a specific attribute or attributes. For example, creating an index on a customer's last name will allow you to retrieve the customer data alphabetically by the customer's last name. Also, an index key can be composed of one or more attributes. Indexes play an important role in DBMSs for the implementation of primary keys. When you define a table's primary key, the DBMS automatically creates a unique index on the primary key column(s) you declared.

Describe a dependency diagram and explain its purpose. Your Answer: A dependency diagram is a visual representation that shows the functional dependencies between attributes in a table. It helps identify partial, transitive, and functional dependencies, which assists in the normalization process and improving database design.

Answer: See the 'Rationale' field for sample responses. Rationale: Dependency diagrams are very helpful in getting a bird's eye view of all the relationships among a table's attributes, and their use makes it less likely that you will overlook an important dependency. The following are features of a dependency diagram: The primary key attributes are bold, underlined, and shaded in a different color. The arrows above the attributes indicate all desirable dependencies-that is, dependencies based on the primary key. The arrows below the dependency diagram indicate less desirable dependencies. Two types of such dependencies exist: a. Partial dependencies. A dependency based on only a part of a composite primary key is a partial dependency. b. Transitive dependencies. A transitive dependency is a dependency of one nonprime attribute on another nonprime attribute. The problem with transitive dependencies is that they still yield data anomalies.

Define entity integrity. What are the two requirements to ensure entity integrity? Your Answer: Entity integrity ensures that every row (entity instance) in a table is uniquely identifiable. Two requirements: The table must have a primary key. The primary key cannot contain NULL values.

Answer: See the 'Rationale' field for sample responses. Rationale: Entity integrity is the condition in which each row (entity instance) in the table has its own unique identity. To ensure entity integrity, the primary key has two requirements: (1) all of the values in the primary key must be unique. (2) no key attribute in the primary key can contain a null.

Describe the use of the INTERSECT operator. Your Answer: The INTERSECT operator returns the rows that appear in both tables. It produces a result set containing only the tuples that exist in both relations.

Answer: See the 'Rationale' field for sample responses. Rationale: INTERSECT yields only the rows that appear in both tables. As with UNION, the tables must be union-compatible to yield valid results. For example, you cannot use INTERSECT if one of the attributes is numeric and one is character-based. For the rows to be considered the same in both tables and appear in the result of the INTERSECT, the entire rows must be exact duplicates.

What is a key and how is it important in a relational model? Your Answer: A key is an attribute or set of attributes used to uniquely identify a row in a table. Keys are important because they enforce entity integrity, prevent duplicate records, and allow relationships between tables through foreign keys.

Answer: See the 'Rationale' field for sample responses. Rationale: In a relational model, keys are important because they are used to ensure that each row in a table is uniquely identifiable. They are also used to establish relationships among tables and to ensure the integrity of the data. A key consists of one or more attributes that determine other attributes. For example, an invoice number identifies all of the invoice attributes, such as the invoice date and the customer name.

Describe the use of null values in a database. Your Answer: Null values represent missing, unknown, or not applicable data in a database. They indicate that a value either has not been entered yet, is unknown, or does not apply to that record.

Answer: See the 'Rationale' field for sample responses. Rationale: Null values are problematic in a relational model. A null is the absence of any data value, and it is never allowed in any part of the primary key. From a theoretical perspective, it can be argued that a table that contains a null is not properly a relational table at all. From a practical perspective, however, some nulls cannot be reasonably avoided. For example, not all students have a middle initial. As a general rule, nulls should be avoided as much as reasonably possible. In fact, an abundance of nulls is often a sign of a poor design. Also, nulls should be avoided in the database because their meaning is not always identifiable. For example, a null could represent:• An unknown attribute value.• A known, but missing, attribute value.• A "not applicable" condition.

What steps are involved in the conversion to the third normal form? Your Answer: Convert table to 1NF by removing repeating groups. Convert to 2NF by removing partial dependencies. Convert to 3NF by removing transitive dependencies.

Answer: See the 'Rationale' field for sample responses. Rationale: Step 1: Make New Tables to Eliminate Transitive Dependencies. For every transitive dependency, write a copy of its determinant as a primary key for a new table. A determinant is any attribute whose value determines other values within a row. If you have three different transitive dependencies, you will have three different determinants. As with the conversion to 2NF, it is important for the determinant to remain in the original table to serve as a foreign key. Step 2: Reassign Corresponding Dependent Attributes. Identify the attributes that are dependent on each determinant identified in Step 1. Place the dependent attributes in the new tables with their determinants and remove them from their original tables.

What characteristics do tables that conform to the concept of well-informed relations have? Your Answer: Tables that conform to well-formed relations have these characteristics: Each row represents a single entity instance Each column represents a single attribute Each cell contains atomic values Each table has a primary key No repeating groups or duplicate rows

Answer: See the 'Rationale' field for sample responses. Rationale: Tables that conform to the concept of well-informed relations have the following characteristics: Each table represents a single subject. No data item will be unnecessarily stored in more than one table. This results in tables that have lower redundancies. The reason for this requirement is to ensure that the data is updated in only one place. All non-prime attributes in a table are dependent on the primary key alone. The reason for this requirement is to ensure that the data is uniquely identifiable by a primary key value. Each table is void of insertion, update, or deletion anomalies, which ensure the integrity and consistency of the data.

The _________operator yields a vertical subset of a table.

PROJECT

_____ returns only the attributes requested, in the order in which they are requested.

PROJECT

_____ logic, used extensively in mathematics, provides a framework in which an assertion (statement of fact) can be verified as either true or false.

Predicate

A new department is created in a company. To keep track of the projects department, a new table is required. Given the size of the company there are a large number of tables and attributes. To avoid potential confusion, and to make sure to refer to the adequate foreign keys, which strategy is more adequate?

Query the data dictionary to get the necessary information.

To be considered minimally relational, the DBMS must support the key relational operators _____, PROJECT, and JOIN.

SELECT

_____, also known as RESTRICT, yields values for all rows found in a table that satisfy a given condition.

SELECT

An index is composed of an index key and pointers. What is the function of the pointers?

Shows the location of the occurrences of a particular index value.

Consider the following table used in a bookstore: Name: Books Primary key: COD Which of the following is a more sensitive selection index?

TITLE, CATEGORY

Consider the following table: What is the result of σprice = 19 (table)?

cod price time 2 19 10 9 19 8 7 19 12

M:N relationship can be changed into two 1:M relationships using a __________.

composite entity

A database stores the data of the sales using the primary key RECEIVE_NUM in the SALE table and the products in the PRODUCT table with primary key PROD_NUM. To create the relation that represents what products were sold in a SALE a M:N relationship is necessary. What should be used to represent this in a relational model?

composite entity with RECEIVE_NUM + PROD_NUM