Question 1

The DELETE command is a command from which subset of SQL?

Accepted Answer

Data Manipulation Language (DML)

Question 2

The CREATE SCHEMA command is a command from which subset of SQL?

Accepted Answer

Data Definition Language (DDL)

Question 3

Joe creates a relation called Game_Scores. Joe grants Jeff privileges on the Game_Scores relation using the following SQL statement:

GRANT ALL PRIVILEGES
ON Game_Scores
To Jeff;

Accepted Answer

Jeff can execute all operations against the Game_Scores relation, but cannot grant privileges on the relation to other users.

Question 4

Todd is managing the Wine_List relation. What will be the result of executing the following SQL statement?

DELETE FROM Wine_List;

Accepted Answer

All the records in the Wine_List relation will be deleted.

Question 5

Which of the following commands can a DBA use to create a database?

Accepted Answer

CREATE SCHEMA

Question 6

Which of the following operations creates a relation that includes rows that are in one relation, but not in another?

Accepted Answer

DIFFERENCE

Question 7

In designing an inventory-tracking database for her company, Carol determines that the department_num attribute should contain exactly one letter and four numbers. Which of the following is the appropriate data type for the department_num attribute?

Accepted Answer

CHAR(5)

Question 8

Which of the following activities is performed as part of the logical design phase?

Accepted Answer

Resolving many-to-many relationships

Question 9

Ken is creating an enterprise data model. He has examined the names of all the entities and their primary keys and examined the names of all the relationships. He has also checked all the foreign keys and ensured that the

Accepted Answer

referential integrity constraints will not cause any conflicts. Ken then produces a final enterprise data model. What is the next logical step in the design process that Ken should take?

Question 10

Which component in the three-tier architecture presents the user interface?

Accepted Answer

The thin client In the three-tier model, the client is responsible for presenting a user interface. The client may also handle some processing logic such as user input validation.This creates what is known as a thin client.

Question 11

Why should optimistic concurrency control be used only in situations in which rollback due to conflicts are very rare?

Accepted Answer

Because the overhead for restarting transactions can be significant.

Question 12

Ken has two tables -- a Customers table and an Orders table. He needs to find out which Customers have placed orders. Which type of relational algebraic operation should he use to extract the appropriate data from his tables?

Accepted Answer

An intersection is a binary operation that creates a new relation containing all the rows that are in both the first and the second relations. The relations must be union-compatible to participate in an intersection operation.

Question 13

Consider the DDL statement shown in the exhibit:
CREATE TABLE Sales_Reps (
rep_num INTEGER NOT NULL PRIMARY KEY,
rep_name VARCHAR (35) NOT NULL,

Accepted Answer

location_num VARCHAR (6) NOT NULL CHECK
(location_num IN ('AB101', 'AB102', 'AB103')));
What type of constraint is included in the DDL statement?

Question 14

The database designer can create constraints that restrict the data that an attribute can receive. The definition of these constraints can be derived from various sources,

Accepted Answer

but typically they are derived from the business rules created for the enterprise in relation to the database. The database designer can place any number of restrictions on the data that a

Question 15

A column in a table that is the primary key of another table is called a(n):

Accepted Answer

foreign key.

Question 16

PARENT KEY

Accepted Answer

The primary key to which a foreign key maps

Question 17

Ken is the database administrator for ACME, Inc. He has granted Alice, a department manager, ALL PRIVILEGES on the Employee relation and included

Accepted Answer

the WITH GRANT OPTION clause. Alice, in turn, granted SELECT privileges on the Employees relation to her assistant, Roger, and she included the WITH GRANT OPTION clause as well. Which of the following describes Roger's current privileges?

Question 18

Which activities would you be likely to perform while in the conceptual design phase for a new database?

Accepted Answer

Identify entities and attribute domains for entities

Question 19

A transaction must change the database from one consistent state to another consistent state. This quality describes which of the ACID properties?

Accepted Answer

CONSISTENCY

Question 20

Which database protection technique prevents invalid data from being entered into the database?

Accepted Answer

Integrity controls

Question 21

Which statement accurately describes a characteristic of attributes?

Accepted Answer

Every attribute in a relation must have a defined domain.

Question 22

A database prototype is:

Accepted Answer

a working model of both the database and the application that accesses the database.

Question 23

SQL provides the AND, OR, and NOT keywords for use in Boolean expressions. When you use the OR keyword, one of two predicates must be true to satisfy the condition.

Accepted Answer

When using the AND keyword, both expressions being evaluated must be true to return a true.

Question 24

In a relational database, a relation is a(n):

Accepted Answer

table.

Question 25

Entities can best be described as:

Accepted Answer

logical categories that make up a database, as opposed to the actual data items that inhabit the entity.

Question 26

When Jim needs to access the corporate database, he sits at a terminal, which presents the user interface. The database, the transaction processing and the application programming are all resident on

Accepted Answer

one separate host computer. What type of database architecture is used at Jim's company?

Question 27

Consider the following four database design :Create user views
Create a database requirements document
Apply normalization
Create an ER diagram
Which choice shows the correct ordering of these activities, from first to last, by assigned numbers

Accepted Answer

2,4,3,1

Question 28

In this method of concurrency control, a unique identifier is assigned to each transaction. This method of concurrency control is:

Accepted Answer

TIMESTSAMP METHOD

Question 29

Which type of join shows keeps every unmatched row in the second relation?

Accepted Answer

Right outer join

Question 30

In a data model, what does structural information describe?

Accepted Answer

A set of rules by which the database can be built
A complete data model should contain the following three distinct components: Integrity information that provides details about how the data will be kept accurate.

Question 31

What is a virtual table?

Accepted Answer

A table created as a result of a data manipulation operation and not a permanent part of the database

Question 32

Which of the following describes a commit?

Accepted Answer

A commit is the final step in a successful completion of a database transaction wherein the database attains a new consistent state.

Question 33

Which of the following characterize a flat-file database?

Accepted Answer

Separation of data, incompatibility of files and data dependence

Question 34

Which of the following is true of denormalization?

Accepted Answer

Denormalization can decrease the number of join operations required to fetch data.

Question 35

attributes in relations or joining relations together to decrease the occurrences of join operations required to fetch data. Maintaining a database can be more difficult after denormalization because of

Accepted Answer

the increased number of attributes that need to be tracked and updated simultaneously throughout the database. Query response time may improve after denormalization, but update and insertion operations may take longer.

Question 36

Which relational algebraic operations will return specific columns from one single relation?

Accepted Answer

Projection

Question 37

Which of the following statements about selecting and designing a database application interface is true?

Accepted Answer

Designing the application interface is interrelated with designing the database itself.

Question 38

What do rectangles represent in both a Chen ER model and an Information Engineering (IE) diagram?

Accepted Answer

Entities

practice B

CompTia Database Design & MySQL

Question	Answer
The DELETE command is a command from which subset of SQL?	Data Manipulation Language (DML)	DML retrieval of data, insertion of new data, modification or updating data, and deletion of data.
The CREATE SCHEMA command is a command from which subset of SQL?	Data Definition Language (DDL)
Joe creates a relation called Game_Scores. Joe grants Jeff privileges on the Game_Scores relation using the following SQL statement: GRANT ALL PRIVILEGES ON Game_Scores To Jeff;	Jeff can execute all operations against the Game_Scores relation, but cannot grant privileges on the relation to other users.	A user who creates a relation with the CREATE TABLE statement automatically owns that relation. That user has full privileges for that relation. Users other than the creator must be granted privileges to perform data operations on the relation
Todd is managing the Wine_List relation. What will be the result of executing the following SQL statement? DELETE FROM Wine_List;	All the records in the Wine_List relation will be deleted.	When the DELETE statement is used, the entire row is removed from the relation. To remove all the rows from a relation The DELETE statement uses the WHERE clause to determine the proper row to remove from the relation.
Which of the following commands can a DBA use to create a database?	CREATE SCHEMA	A DBA can use the CREATE SCHEMA command to create a database. A schema is a named group of related tables, views, and other database objects. It represents the complete structure of the database.
Which of the following operations creates a relation that includes rows that are in one relation, but not in another?	DIFFERENCE
In designing an inventory-tracking database for her company, Carol determines that the department_num attribute should contain exactly one letter and four numbers. Which of the following is the appropriate data type for the department_num attribute?	CHAR(5)
Which of the following activities is performed as part of the logical design phase?	Resolving many-to-many relationships	During the logical design phase, the conceptual data model is refined, removing or adjusting relationships that may cause data manipulation anomalies. The refinement process comprises the following activities:
	Identify one-to-one, one-to-many and many-to-many relationships; Resolve any many-to-many relationships; Identify and resolve any complex relationships;	Identify and resolve any recursive relationships; Identify any relationships that have attributes; Create entities from relationships that have attributes; Identify and resolve multivalued attributes; Identify and remove any redundant relationships.
Ken is creating an enterprise data model. He has examined the names of all the entities and their primary keys and examined the names of all the relationships. He has also checked all the foreign keys and ensured that the	referential integrity constraints will not cause any conflicts. Ken then produces a final enterprise data model. What is the next logical step in the design process that Ken should take?	Validate the enterprise data model
Which component in the three-tier architecture presents the user interface?	The thin client In the three-tier model, the client is responsible for presenting a user interface. The client may also handle some processing logic such as user input validation.This creates what is known as a thin client.	The main business logic of the application is located on the server, creating its own layer or tier. The server is connected toclient by way of (LAN) or a (WAN). The application server is generally designed to be able to serve many clients at one time.
Why should optimistic concurrency control be used only in situations in which rollback due to conflicts are very rare?	Because the overhead for restarting transactions can be significant.
Ken has two tables -- a Customers table and an Orders table. He needs to find out which Customers have placed orders. Which type of relational algebraic operation should he use to extract the appropriate data from his tables?	An intersection is a binary operation that creates a new relation containing all the rows that are in both the first and the second relations. The relations must be union-compatible to participate in an intersection operation.
Consider the DDL statement shown in the exhibit: CREATE TABLE Sales_Reps ( rep_num INTEGER NOT NULL PRIMARY KEY, rep_name VARCHAR (35) NOT NULL,	location_num VARCHAR (6) NOT NULL CHECK (location_num IN ('AB101', 'AB102', 'AB103'))); What type of constraint is included in the DDL statement?	An enterprise constraint
The database designer can create constraints that restrict the data that an attribute can receive. The definition of these constraints can be derived from various sources,	but typically they are derived from the business rules created for the enterprise in relation to the database. The database designer can place any number of restrictions on the data that a	relation can receive. For instance, numeric values can be checked to ensure that they fall within a desired range, a value's format can be checked, or a value can be limited to a defined list and must come only from that list.
A column in a table that is the primary key of another table is called a(n):	foreign key.
PARENT KEY	The primary key to which a foreign key maps
Ken is the database administrator for ACME, Inc. He has granted Alice, a department manager, ALL PRIVILEGES on the Employee relation and included	the WITH GRANT OPTION clause. Alice, in turn, granted SELECT privileges on the Employees relation to her assistant, Roger, and she included the WITH GRANT OPTION clause as well. Which of the following describes Roger's current privileges?	Roger can select data in the Employees relation and he can pass the SELECT privilege on to other users.
Which activities would you be likely to perform while in the conceptual design phase for a new database?	Identify entities and attribute domains for entities	following activities: identifying entities, identifying attributes and attribute domains for entities, identifying relationships, identifying candidate and primary keys for entities, create an Entity-Relationship (ER) diagram.
A transaction must change the database from one consistent state to another consistent state. This quality describes which of the ACID properties?	CONSISTENCY
Which database protection technique prevents invalid data from being entered into the database?	Integrity controls	Invalid entries can lead to misleading or incorrect data.
Which statement accurately describes a characteristic of attributes?	Every attribute in a relation must have a defined domain.	In a relational database, a row in the table represents individual records whereas a column represents the attributes of each record. Rows are sometimes referred to as tuples.
A database prototype is:	a working model of both the database and the application that accesses the database.
SQL provides the AND, OR, and NOT keywords for use in Boolean expressions. When you use the OR keyword, one of two predicates must be true to satisfy the condition.	When using the AND keyword, both expressions being evaluated must be true to return a true.	The NOT keyword is used to reverse a Boolean expression's value.
In a relational database, a relation is a(n):	table.
Entities can best be described as:	logical categories that make up a database, as opposed to the actual data items that inhabit the entity.
When Jim needs to access the corporate database, he sits at a terminal, which presents the user interface. The database, the transaction processing and the application programming are all resident on	one separate host computer. What type of database architecture is used at Jim's company?	In a centralized, mainframe environment, a terminal presents the user interface and all other work is performed by the mainframe computer.
Consider the following four database design :Create user views Create a database requirements document Apply normalization Create an ER diagram Which choice shows the correct ordering of these activities, from first to last, by assigned numbers	2,4,3,1
In this method of concurrency control, a unique identifier is assigned to each transaction. This method of concurrency control is:	TIMESTSAMP METHOD
Which type of join shows keeps every unmatched row in the second relation?	Right outer join
In a data model, what does structural information describe?	A set of rules by which the database can be built A complete data model should contain the following three distinct components: Integrity information that provides details about how the data will be kept accurate.	Structural information detailing a particular set of rules by which a database can be built; Manipulative information detailing the kinds of operations that are permitted on the data; I
Complex relationships occur when more than two entities participate in a relationship. Often, complex relationships contain m:n relationships, and the relations involved should be decomposed to create intermediate entities.
What is a virtual table?	A table created as a result of a data manipulation operation and not a permanent part of the database
Which of the following describes a commit?	A commit is the final step in a successful completion of a database transaction wherein the database attains a new consistent state.
Which of the following characterize a flat-file database?	Separation of data, incompatibility of files and data dependence
Which of the following is true of denormalization?	Denormalization can decrease the number of join operations required to fetch data.	Denormalization is the process of reuniting relations that were split during the normalization process to improve performance. Generally, denormalization will involve the duplication of
attributes in relations or joining relations together to decrease the occurrences of join operations required to fetch data. Maintaining a database can be more difficult after denormalization because of	the increased number of attributes that need to be tracked and updated simultaneously throughout the database. Query response time may improve after denormalization, but update and insertion operations may take longer.
Which relational algebraic operations will return specific columns from one single relation?	Projection
Which of the following statements about selecting and designing a database application interface is true?	Designing the application interface is interrelated with designing the database itself.
What do rectangles represent in both a Chen ER model and an Information Engineering (IE) diagram?	Entities

"Know" box contains:
Time elapsed:
Retries: