Database Systems FINAL

Data is the result of interpreting facts to reveal meaning. (T or F)

False, Data is the result of processing RAW DATA to facts to reveal their meaning

Unstructured data is data that exists in the format in which it was collected (raw state). While Structured data is the result of formatting unstructured data to enable storage, use, and generation of information. (T or F)

True.

Correct. Unstructured data is in its raw state while structured data is formatted to enable storage and generation of meaningful information.

Data redundancy exists when data is duplicated and stored unnecessarily at different places.

True:

Data redundancy exists when data is duplicated and stored unnecessarily at different places.

One of the advantages of a database system over previous data management approaches is reduced costs. (T or F)

FALSE:

Correct. Database systems require sophisticated hardware and software and highly skilled personnel. This increases costs.

The term database system refers to an organization of components that define and regulate the collection, storage, management, and use of data within a database environment.

True:

Correct. Database system refers to an organization of components that define and regulate the collection, storage, management, and use of data within a database environment.

How would you define data that is verifiable?

a. The data is stored in different places within the database.

b. The data cannot be changed or manipulated.

c. The data always yields consistent results.

d. The data is obtained from trusted sources.

C

Correct. Data is said to be verifiable if the data always yields consistent results

One of the advantages of database systems is that ___

a. Database systems maximizes data inconsistency

b. Database systems decreases end user productivity

c. Database systems enable the data in the database to be shared among multiple applications or users.

d. Database systems do not integrate many different users' data into a single all-encompassing data repository.

C.

Correct. One of the advantages of database systems is that Database systems enable the data in the database to be shared among multiple applications or users.

A good Database Management System (DBMS) will perform well even with a badly designed database. (T or F)

False:

Correct. A well designed database is essential for a DBMS to perform well.

Thorough database planning and design is important to ensure that data is properly used to give the organization the most benefit. (T or F)

True:

Correct. Thorough database planning and design is important for a DBMS to perform well and to give the organization the most benefit.

Which of the answers below is one of the challenges of managing a database system?

a. Infrequent Upgrade

b. Management Simplicity

c. Reduced costs

d Vendor Dependence

d. Vendor dependende:

Vendor dependence is one of the challenges of managing a database system.

Within the database environment, a data model represents data structures and their attributes, relations, constraints, and transformations with the purpose of supporting a specific problem domain. Define "problem domain" in the context of data models.

a. a blueprint containing all necessary instructions for building a database that will meet all end-user requirements

b. a clearly defined area within a real-world environment that will be systematically addressed

c. a relatively simple and usually graphical representation of complex real-world objects or events

d. the process of creating a specific data model for a particular set of real-world data to be addressed

b. a clearly defined area within a real-world environment that will be systematically addressed

Correct. A problem domain is a real-world set of circumstances, objects or events that serve as data points and represent the boundaries and scope of the problem(s) to be addressed.

Business rules use simple language to establish definitions of entities, attributes, relationships, and constraints. For instance, a business rule establishing that entities can have multiple attributes, and that each available attribute may be associated with only one entity, might be stated as "An employee may be assigned multiple devices" and "Each device may be assigned to only one employee". Classify the relationship between EMPLOYEES and DEVICES in this example using shorthand notation.

A. 1:1

B. ..

C. M:N

D. 1:M

D. 1:M

Correct. 1:M signifies a one-to-many relationship, as described in the example. This relationship can also be described according to database convention as EMPLOYEE is assigned DEVICES.

Business rules that are properly written will define entities, attributes, relationships, and _____.

A. Fields

B. Objects

C. Constraints

D. Classes

C. Constraints

Correct. Constraints are intentional restrictions to the data, and allow us to ensure the data is accurate and reliable. Specifically, constraints enforce rules that were defined at the time of model creation as the data is manipulated.

In the context of relational databases, a column in a relational table is associated with the _____(s) of a real-world person, place, thing, or event.

A. Entity

B. Atrributes

C. Relationships

D. Constraints

B. Attributes

Correct. Attributes are the characteristics of an entity, usually represented as a value. In the context of relational databases, the columns in a relational table correspond to attributes of an entity.

A(n) _____ is bidirectional.

a. Attribute

b. entity

c. relationship

d. constraint

c. Relationship:

Correct. A relationship between entities flows in both directions.

Which of the following is true of business rules?

a. They allow the designer to set company policies with regard to data.

b. They allow the designer to develop business processes.

c. They can serve as a communication tool between the users and designers.

d. They provide a framework for the company's self-actualization.

C. They can serve as a communication tool between the users and designers.

Correct! They can serve as a communication tool between the users and designers.

A noun in a business rule translates to a(n) _____ in the data model.

a. entity

b. attribute

c. relationship

d. constraint

A. entity

Correct. An entity is a basic building block of a data model that represents a real-world person, place, or thing (noun) about which data will be collected and stored in the database.

A verb associating two nouns in a business rule translates to a(n) _____ in the data model.

a. entity

b. attribute

c. relationship

d. constraint

c. relationship

Correct. A verb associating two nouns in a business rule translates to a relationship in the data model because the verb between the two nouns defines the relationship between them.

Business rules must describe the main and distinguishing features of the data as viewed by _____.

a. the application programmer

b. the end users

c. the designer

d. the company

d. the company

Correct. Business rules must describe the main and distinguishing features of the data as viewed by the company. Business rules must be simple to grasp and broadly distributed in order for everyone in the company to have the same understanding of the rules.

Which of the following is true of NoSQL databases?

a. They do not support distributed database architectures.

b. They are not based on the relational model.

c. They are geared toward transaction consistency rather than performance.

d. They do not support very large amounts of sparse data.

b. They are not based on the relational model.

Correct. NoSQL databases are not based on the relational model, using a schema-less key-value model instead.

A database language enables the user to perform complex queries designed to transform the raw data into useful information. (T or F)

True.

correct using database languages a user can extract the precise information required for a given task

All SQL commands must be issued on a single line. (T or F)

False

SQL commands can use several lines, to write queries that are easier to understand using several lines is the best practice.

Comparison operators cannot be used to place restrictions on character-based attributes. (T or F)

False

Correct. Comparison operators can be placed on character-based attributes; string base comparison is made from left to right.

What command should be used to save permanently all changes made to a database.

a. SAVE

b. SAVE ALL

c. COMMIT

d. WRITE ALL

C. COMMIT

Correct. After executing the COMMIT command all the changes are permanently saved. There are no commands such as SAVE or WRITE ALL.

Which comparison operator indicates a value is not equal?

a. <

b. <=

c. >=

d. <>

d. <>

Correct. The comparison operator for not being equal means that it is greater or lesser than <>.

The COUNT function is designed to tally the number of non-null "values" of an attribute and is often used in conjunction with the DISTINCT clause. (T or F)

True

Correct. The count function is often used with the DISTINCT clause to count the number of different entities not just the total number of rows.

Rows can be grouped into smaller collections quickly and easily using the COUNT clause within the SELECT statement. (T or F)

False.

The rows are grouped by using the GROUP BY clause.

In practice which of the following is more common to find?

a. Well designed databases.

b. Best practice application.

c. Cryptic, confusing and nonstandardized attribute names.

d. Patients' names and ids

c. Cryptic, confusing and nonstandardized attribute names.

Correct. Real-world databases can be messy, the attribute names of the tables might not follow the best practices and as a result be cryptic, and confusing.

When using a cross join between a table with 8 rows and another with 5 rows how many rows are in the result?

a. 10

b. 13

c. 20

d. 40

d. 40

Correct. . The cross join is equivalent to the product of the two relations thus the result has 8 x 5 = 40 rows.

The special operator used to check whether a subquery returns any rows is _____.

A. BETWEEN

B. EXISTS

C. LIKE

D. IN

B. EXISTS

Correct. The operator EXISTS returns True when the subquery is not empty.

The practical significance of taking the logical view of a database is that it serves as a reminder of the simple file concept of data storage. (T or F)

True

You can think of a table as a persistent representation of a logical relation. (T or F)

True

The order of the rows and columns is important to the DBMS. (T or F)

False

Character data can contain any character or symbol intended for mathematical manipulation. (T or F)

False

Each table in a relational database must have a primary key. (T or F)

True

The row's range of permissible values is known as its domain. (T or F)

False

Only a single attribute, not multiple attributes, can define functional dependence. (T or F)

False

RDBMSs enforce integrity rules automatically. (T or F)

True

Relational algebra defines the theoretical way of manipulating table contents using relational operators.(T or F)

True

The one-to-many (1:M) relationship is easily implemented in the relational model by putting the foreign key of the "1" side in the table of the "many" side as a primary key. (T or F)

False

The entity relationship model (ERM) is dependent on the database type. (T or F)

False

Correct. The ERM is independent of the database type.

Connectivities and cardinalities are established by concise statements known as business rules. (T or F)

True

Correct. Connectivities and cardinalities are established by concise statements known as business rules.

A database design can deviate from normal standards when the database size is less than 100 MB. (T or F)

False

Correct. The database design must conform to design standards.

When reading the database diagram with entities, management has a hard time understanding the implementation of these tables with just solid lines. Their relationships are still questionable. Using the tables ROOM and BUILDING, what is the most appropriate way a database designer can conceptualize their implementation in the diagram?

a. Insert the phrase "located in" in between

b. Insert the phrase "connected to" in between

c. Insert the phrase "many in" in between

d. Insert the phrase "one in many" in between

a. Insert the phrase "located in" in between

Correct. Implementation conceptualizes how the database is designed in comparison to how the organization or company operates. Using the phrase "located in" is the most realistic way of describing the two tables' relationship.

After looking at the database design, another designer found that the primary keys were not established with a simple underline. Knowing that single-valued attribute can almost always be labeled as primary identities, which of the following attributes would be a good candidate as a primary key for a table?

a. SS_NUMBER

b. CAR_COLOR

c. COLLEGE_DEGREE

d. CAR_PART

a. SS_NUMBER

Correct. A single-valued attribute is an attribute that can have only a single value. SS_NUMBER or social security number is a unique number that can be assigned to a STUDENT table, for example.

When a database designer determines that two entities share the same primary keys in their relationship, what can the designer infer between the two?

a. optional relationship

b. Unary relationship

c. weak relationship

d. strong relationship

d. strong relationship

Correct. Strong relationship because a strong (identifying) relationship exists when the primary key of the related entity contains a primary key component of the parent entity.

When examining an associative entity with a primary key and two foreign keys, what can a database designer review as well to better understand the overall design?

a. two attributes

b. two parent entities

c. primary key

d. foreign keys

b. two parent entities

Correct. Two parent entities because each of the two foreign keys are derived from its associated parent entity.

When re-evaluating the current ERD design, what is the best possible action a database designer can take to improve the current design in order to account for new operational processes in the company?

a. Review the connectivity lines and symbols

b. Review the designated private and foreign keys

c. Review the relationship text between entities

d. Correct! Review previous design documentation

Review previous design documentation

Correct. Should review previous design documentation because every ERD should include a detailed documentation to understand why things were done, which can also help to find ways to make changes later.

High processing speeds are often a high priority in database design when generating large numbers of transactions. (T or F)

True

Correct. High processing speed means minimal access time which is often times a high priority.

A user gets an error when entering new information into a table. The error suggests the wrong values are entered in. What can the user review about the table's attributes to ensure the right values are entered in?

a. The attribute's domain

b. The name of the entity

c. The tables primary identifier

d. The candidate key

a. The attribute's domain

Correct. A domain is the set of possible values for a given attribute.

Normalization works through a series of stages called normal forms.

True

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

False

In order to meet performance requirements, portions of the database design may need to be occasionally denormalized.

True

Denormalization produces a lower normal form.

True

The objective of normalization is to ensure that each table conforms to the concept of well-formed relations.

True

Relational models view data as part of a table or collection of tables in which all key values must be identified.

True

Repeating groups must be eliminated by ensuring that each row defines a single entity.

True

Dependency diagrams are very helpful in getting a bird's-eye view of all the relationships among a table's attributes.

True

From a structural point of view, 2NF is better than _____.

1NF

A table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key is said to be in _____.

1NF

A view is a virtual table based on a SELECT query.

True

Authentication is the process the DBMS uses to verify that only registered users access the database.

True

When creating a table with a SELECT statement, the CREATE TABLE command statement represents the subquery.

False

A subquery will automatically overwrite an existing table if it has the same name.

False

You must first enter the command ALTER TABLE in order to issue a MODIFY command to change a columns datatype.

True

The RDBMS automatically creates the ____ tables in which to store metadata.

Data Dictionary

All changes in the table structure start by entering the ____ command first, followed by a keyword that produces the specific change.

ALTER TABLE

Inserted data will, in most cases, place a value that is meant for the primary key column into the ____ part of the VALUES statement.

First

Which of the following is NOT required in terms of syntax when a user wants to copy data from one table to another?

Use the * command to select all values.

An administrator can resolve the requirement for gathering multiple values from a SELECT statement to evaluate complex problems, for example, by processing those SELECT statements with ____.

cursors

IF a SQL subquery included the statement that reads SELECT P_CODE AS PART_CODE, P_DESCRIPT AS PART_DESCRIPT, P_PRICE AS PART_PRICE, VENDOR_CODE, then the new columns would have been created for specific information from a more overall table that was most likely called ____.

PRODUCT

A user can insert only required information for a new product for columns P_CODE, P_DESCRIPTION, P_PRICE by entering the SQL statement ____.

INSERT INTO PRODUCT(P_CODE, P_DESCRIPT) VALUES ('BRT-345','Titanium drill bit'),35.99;

An administrator lost data on a new table called PRODUCT after making a few modifications on each product's information. VENDOR and CUSTOMER were also scheduled for modification. A manager found that the data was lost because ____.

the WHERE command was not specified

Consider the following table:

What is the result of the query below?

SELECT * FROM TABLE1 WHERE val2 BETWEEN (100, 400) AND val3 IN (10, 20)

val 1 val2 val3

3 129 10

5 129 20

While designing a query you determine that the data needs to be preprocessed. Which of the following is most appropriate?

Use a subquery to preprocess.

Some very specialized applications may require normalization beyond the _____.

4NF

An atomic attribute _____.

cannot be further subdivided

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

According to the ERD data-modeling checklist, all entities in a relational table represent a single set of distinguishable sets of _____.

entity instances

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

views

The Systems Development Life Cycle (SDLC) traces the history of an organization's system.

False

The overall purpose of the database initial study is to create the conceptual design.

False

In most modern relational DBMSs, a new database implementation requires the creation of special storage-related constructs to house the end-user tables.

True

The designer does not need to have a thorough understanding of the company's data types, their extent, and uses to be able to develop an accurate data model.

False

As a database designer, which of the options below would you not include in the implementation phase of a database design?

Designing the database

In the detailed systems design phase, the designer __?

Completes the design of the system's processes

The Systems Development Life Cycle (SDLC) is divided into __ phases.

Five

Evaluation, maintenance, and enhancement are done during the __ phase of the Systems Development Life Cycle (SDLC).

Maintenance

Selecting database management system (DBMS) software is part of the _____ phase of the Database Life Cycle (DBLC).

database design

The first stage in the database design process is __?

Conceptual Design

A transaction is a logical unit of work that must be entirely completed or removed.

True

The DBMS guarantees that the semantic meaning of a transaction truly represents the real-world event.

False

Serializability means that data used during the execution of a transaction cannot be used by a second transaction until the first one is completed.

False

In a page-level lock, the DBMS will lock an entire diskpage.

True

A process that ensures a database is immediately updated by operations during the transaction, even before the transaction commit point is called write-through update.

False

When creating a multi-user database ANSI has defined standards that govern SQL database transactions. There are two SQL statements __________________ and ROLLBACK that are utilized in transaction support.

COMMIT

Lock granularity indicates the level of lock use which can take place at which levels:

database, table, page, row, or field (attribute)

You are creating a transaction management database and the currency control called __________ ensures that database operations do not conflict.

Optimistic Approach

Database recovery restores the database from current state to previous state which is triggered by a critical error caused by ___________ or ____________.

hardware error, software error

Critical events can render a database useless, there are three types of critical events __________, ____________, and ___________,

hardware/software failure, human-caused incidents, natural disasters