Question 1

Which DDL command is used to create a relation?

Accepted Answer

CREATE TABLE

Question 2

Which of the following statements will create a base relation named Producers?

Accepted Answer

CREATE TABLE Producers
(p_num INTEGER NOT NULL PRIMARY KEY,
p_name VARCHAR (50));

Question 3

Which of the following is true of the physical design phase for a database?

Accepted Answer

It is largely dependent on the DBMS selected

Question 4

Consider the DDL statement shown in the exhibit.

CREATE TABLE Sales Reps
(s_num INTEGER NOT NULL PRIMARY KEY,
s_first_name VARCHAR (25) NOT NULL,
s_last_name VARCHAR (25) NOT NULL)

Accepted Answer

The statement will fail to execute properly due to syntax errors. The statement will fail to execute properly because there is a blank space in the table name and because the statement is missing a semicolon (;) at the end of the last line.

Question 5

Which of the following is true of secondary indexes?

Accepted Answer

If a particular attribute is updated often, it may not be a good candidate for a secondary index.

Question 6

Consider the DDL statement shown in the exhibit.

CREATE TABLE Sales_Reps
(s_num INTEGER NOT NULL PRIMARY KEY,
s_first_name VARCHAR (25) NOT NULL,
s_last_name VARCHAR (25) NOT NULL);

Accepted Answer

The resulting table will include three attributes.

Question 7

Consider the relation and the view shown in the exhibit. Which of the following SQL statements will correctly create the view?

Accepted Answer

CREATE VIEW emp_view
AS SELECT id, first_name, last_name, title, loc_num
FROM Employee
WHERE loc_num = '1004';

Question 8

Consider the DDL statement shown in the exhibit:

CREATE INDEX genre_index ON Items(i_genre);
What does this DDL statement accomplish?

Accepted Answer

It creates a secondary index on the i_genre attribute.

Question 9

Which activity occurs during the physical design phase of a database?

Accepted Answer

Schemas and relations are created

Question 10

What is denormalization?

Accepted Answer

Denormalization is the process of reuniting relations that were split when they underwent the normalization process.

Question 11

Consider the relation and the view shown in the exhibit. Which of the following SQL statements will correctly create the view?

Accepted Answer

CREATE VIEW emp_view
AS SELECT id, first_name, last_name, title, loc_num
FROM Employee;

Question 12

Susan is adding data to the table and tries to enter a record for a sales rep working at location_num 'AB105.' What will happen when Susan tries to insert the new record?

Accepted Answer

The insert operation will be disallowed.

Question 13

During this design phase, the cardinality of all relationships is examined, many-to-many relationships are resolved through decomposition and the resulting data model is normalized. Which design phase has been described?

Accepted Answer

The logical design phase

Question 14

Which of the following is true of denormalization?

Accepted Answer

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

Question 15

The statement will fail to execute properly because the closing parenthesis has been omitted.

Accepted Answer

The statement will fail to execute because of a syntax error.

Question 16

Joe creates a relation called Game_Scores. Joe grants Jeff privileges on the Game_Scores relation using the following SQL statement:

GRANT SELECT
ON Game_Scores
To Jeff;
Which of the following statements is true?

Accepted Answer

Joe has full privileges for the Game_Scores relation and Jeff can view the data but not update it in any way. (

Question 17

A user who creates a relation with the CREATE TABLE statement automatically owns that relation. That user has full privileges for that relation.

Accepted Answer

Users other than the creator of the relation must be granted privileges to perform data operations on the relation.

Question 18

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;
Which of the following statements is true?

Accepted Answer

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

Question 19

Why should a size argument be specified when assigning a data type of CHAR or VARCHAR?

Accepted Answer

To ensure that the field will be large enough to hold the appropriate data

Question 20

Modifications to LOCATIONS(location_num) are automatically reflected in changes to EMPLOYEES(location_num). A deletion can occur in LOCATIONS(location_num) only when there are no EMPLOYEES(location_num) records referencing the parent record.

Accepted Answer

What are the referential constraints for the relations defined here?

Question 21

Which DDL command is used to create a secondary index?

Accepted Answer

CREATE INDEX

Question 22

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 WITH GRANT OPTION;
Which of the following statements is true

Accepted Answer

Jeff can grant privileges on the Game_Scores relation to other users.

Question 23

During which phase of database design might a designer choose to denormalize relations in order to improve performance?

Accepted Answer

PHYSICAL

Question 24

Consider the relation shown in the exhibit. A database manager wants to set up a view called Mobile_Numbers that allows a user to find a student's first name, last name and cell phone number. Which SQL statement will accomplish this?

Accepted Answer

CREATE VIEW Mobile_Numbers
AS SELECT first_name, last_name, cell_phone
FROM Student;

Question 25

Consider the following exhibit of DBDL code.Sales_Reps(s_num: integer NOT NULL,s_first_name: variable length character string length 25 NOT NULL
s_last_name: variable length character string length 25 NOT NULL)
Primary Key: s_num

Accepted Answer

Which DDL statement will accurately create the table described in the DBDL code shown in the exhibit?

Question 26

Which DDL statement will accurately create the table described in the DBDL code shown in the exhibit?

Accepted Answer

CREATE TABLE Sales_Reps
(s_num INTEGER NOT NULL PRIMARY KEY,
s_first_name VARCHAR (25) NOT NULL,
s_last_name VARCHAR (25) NOT NULL);

Database lesson 7

CompTia Dababase Design & MySQL Lesson 7

"Know" box contains:
Time elapsed:
Retries: