Database Management – Week 3

Question 1

Which of the following SQL statements deletes an object?

• ALTER
• Selected: DROPThis answer is correct.
• REMOVE
• DEL

Correct! In the SQL language, the CREATE statement is used to add a new object; the ALTER statement is an instruction to change an existing object; and the DROP statement is used to delete an object.

1/1

Question 2

What permission must be granted for a user to run procedures within a database?

• Selected: executeThis answer is correct.
• list
• read
• server-level

Correct! Granting the EXECUTE permission to a user on a database allows the user to execute any procedure within that database.

1/1

Question 3

Which attribute includes binary and numeric as categories?

• column
• row
• Selected: data typeThis answer is correct.
• record labels

Correct! A data type is a predefined form of data that specifies the data values that a logical database object can store. A data type attribute must be defined for each column and variable in a database. The main data type categories are: character and unicode string types, binary types, numeric types, and date types.

1/1

Question 4

Which of the following is true about naming objects?

• The database management system provides strict rules.
• Tables should always be named with a character prefix.
• Selected: Camel case does not use special characters to link words.This answer is correct.
• A column name of Last Name is preferable to LastName.       Â

Correct! Camel case is a naming convention that concatenates (links together) words using uppercase for the first character of each word. Examples of this style are: FirstName, LastName, and AdventureWorks2012. Camel case is easier to read than words linked together using underscores or other characters, and it should form the basic syntax for naming objects.

1/1

Question 5

What should you always do before creating a new database?

• Selected: Back up the master system database.This answer is correct.
• Delete all existing temporary databases.
• Install a fresh instance of SQL Server 2012.
• Stop and restart the SQL Server service.

Correct! Before creating a new database, you should always make a full backup of the master system database, which stores the configuration settings.

1/1

Question 6

Which of the following is true about the autogrowth setting?

• By default, it doubles the size of the log file when it becomes full.
• You should keep this value as small as possible.
• The data file is affected by it, but log files are not.
• Selected: You should configure it so that file growth occurs infrequently.This answer is correct.

Correct! The autogrowth setting specifies how much the database will increase the size of the physical files once the space that was initially allocated to them becomes full; this setting can be stated in absolute or relative terms. The defaults are 1 MB for the data file and 10 percent for the log file. Due to the performance impact of increasing the size of the files, you should make these settings sufficiently large to make file growth a very infrequent event.

1/1

Question 7

Which of the following is true about the size of data and log files?

• You should not change the default sizes.
• You should not set the size to be larger than the data requires.
• Selected: Increasing the size in an existing database will degrade performance.This answer is correct.
• Best practice is to change the maxsize setting from its default value.Â

Correct! The initial size setting defines the initial size of the data and log files; the default sizes are 4 MB and 1 MB, respectively. Increasing the file size after a database has been created consumes significant resources and will lead to degraded performance.

1/1

Question 8

Which of the following is true about log files?

• Selected: They aid in database rollback.This answer is correct.
• There can be only one log file per database.
• Log files are optional for each database.
• They should be stored on the same physical disk as data files.

Correct! The rollback capability relies on writing the transaction to the log file prior to committing the transaction to the data file. The log of database transactions is also necessary when recovering a database from a backup, which is done by replaying the transaction activity from the log to ensure no loss of data.

1/1

Question 9

What should you define if you want to grant several users the same permissions to database objects?

• schema
• Selected: roleThis answer is correct.
• user
• access list

Correct! A role groups together permissions on different database objects.

1/1

Question 10

Which of the following is a logical container that groups together collections of objects within a database and allows them to be managed as a group?

• filegroup
• transaction
• data file
• Selected: schemaThis answer is correct.

Correct! A schema is a logical container that groups together collections of objects within a database and allows them to be managed as a group.

 

1/1

Question 1

Which of the following may require the database engine to perform a full scan on every record of the database?

• invalid constraints
• Selected: no index is definedThis answer is correct.
• too many triggers
• no secondary key     Â

Correct! If no index is defined, a query will require the database engine to undertake a full table scan on every record within the table in order to find matches.

1/1

Question 2

Which term describes the default index of a table, which is used to uniquely identify a record?

• record Index
• tertiary key
• primary Index
• Selected: primary keyThis answer is correct.

Correct! Each table should have a default index, which is typically based on a primary key—the column or combination of columns that uniquely identifies each record.

1/1

Question 3

Which of the following is a file extension used in the physical structure of a SQL database?

• Selected: .ldfThis answer is correct.
• .txt
• .mdb
• .dbf

Correct! Each database that is mounted on a SQL Server instance has two main physical file types: Data Files (.mdf) and Log Files (.ldf).

1/1

Question 4

Which of the following is NOT a system database on a SQL Server system?

• model
• Selected: directoryThis answer is correct.
• master
• resource

Correct! There are five system databases: master, tempdb, model, msdb, and resource.

1/1

Question 5

Which of the following is NOT a network protocol supported by SQL Server 2012?

• shared memory
• Selected: NWLinkThis answer is correct.
• named pipes
• TCP/IP

Correct! The following network protocols are supported by SQL Server 2012: shared memory, named pipes, and TCP/IP.

1/1

Question 6

What are int, date, and bit examples of in a SQL database?

• Selected: data typeThis answer is correct.
• column name
• condition
• record

Correct! The data type is a column attribute that defines which type of data it can store, such as int, date, or bit.

1/1

Question 7

Which of the following is NOT a reason for a database administrator to understand the main file types used in a SQL Server system?

• physical resources
• backup and recovery operations
• Selected: transparency to the userThis answer is correct.
• security

Correct! Although the physical file structure is generally transparent to an end user, it is important for a database administrator to understand the main file types and their implementation for a number of reasons, including: physical resources, backup and recovery operations, and security.

1/1

Question 8

Which system database stores information on the structure of other databases and their components?

• model
• msdb
• Selected: masterThis answer is correct.
• tempdb

Correct! Master stores information on the structure of other databases and their components, and also maintains configuration settings, including security.

0/1

Question 9

What can be used to satisfy the compliance aspects of business requirements by creating time stamps and audits?

• logical instance
• Selected: constraintThis answer is incorrect.
• index
• trigger

Actually, look again. A trigger is a procedural mechanism that can be configured to automatically fire an event when a row is inserted, modified, or deleted. It is often used to satisfy the compliance aspects of business requirements by cascading details of a change into a related table or auto-populating metadata within a record. Review Module 2 again, and specifically, page 76 in Chapter 3 of the textbook.

1/1

Question 10

Which data format represents data as a series of ones and zeros?

• Selected: binaryThis answer is correct.
• decimal
• octal
• hex

Correct! SQL Server encodes the data into a binary format, in which data is represented as a series of zeros and

Top of Form

Question 1

Which data format represents data as a series of ones and zeros?

• Selected: binaryThis answer is correct.
• decimal
• octal
• hex

Correct! SQL Server encodes the data into a binary format, in which data is represented as a series of zeros and ones.

1/1

Question 2

Which service enables a client to connect to the SQL Server instance over a network?

• loopback
• SQLCMD
• Selected: listenerThis answer is correct.
• shared memory

Correct! A listener is a service that enables a client to connect to the SQL Server instance over a network.

1/1

Question 3

Select the network protocol below that utilizes local procedure calls to pass instructions to the SQL Server.

• TCP/IP
• named pipes
• Selected: shared memoryThis answer is correct.
• IPX/SPX

Correct! Shared memory is a local procedure call, and it requires the client application to be running on the same host as the SQL Server instance.

1/1

Question 4

Which of the following may require the database engine to perform a full scan on every record of the database?

• invalid constraints
• Selected: no index is definedThis answer is correct.
• too many triggers
• no secondary key     Â

Correct! If no index is defined, a query will require the database engine to undertake a full table scan on every record within the table in order to find matches.

0/1

Question 5

Which system database stores information on the structure of other databases and their components?

• model
• msdb
• master
• Selected: tempdbThis answer is incorrect.

Actually, look again. Master stores information on the structure of other databases and their components, and also maintains configuration settings, including security. Review Module 2 again, and specifically, Page I and Table 1.

1/1

Question 6

What can be used to satisfy the compliance aspects of business requirements by creating time stamps and audits?

• logical instance
• constraint
• index
• Selected: triggerThis answer is correct.

Correct! A trigger is a procedural mechanism that can be configured to automatically fire an event when a row is inserted, modified, or deleted. It is often used to satisfy the compliance aspects of business requirements by cascading details of a change into a related table or auto-populating metadata within a record.

1/1

Question 7

Which of the following is NOT a reason for a database administrator to understand the main file types used in a SQL Server system?

• physical resources
• backup and recovery operations
• Selected: transparency to the userThis answer is correct.
• security

Correct! Although the physical file structure is generally transparent to an end user, it is important for a database administrator to understand the main file types and their implementation for a number of reasons, including: physical resources, backup and recovery operations, and security.

1/1

Question 8

Which of the following is NOT a system database on a SQL Server system?

• model
• Selected: directoryThis answer is correct.
• master
• resource

Correct! There are five system databases: master, tempdb, model, msdb, and resource.

1/1

Question 9

Which of the following is a way of organizing data, with defined rules for storing, manipulating, and retrieving data?

• modeling format
• physical architecture
• virtual instance
• Selected: logical structureThis answer is correct.

Correct! A logical structure is a way of organizing data, with defined rules for storing, manipulating, and retrieving the data; client applications interact with these logical objects within a database, rather than with the physical files.

1/1

Question 10

Which of the following is a basic building block of a database that is used to improve the speed and efficiency of data access?

• table
• constraint
• Selected: indexThis answer is correct.
• time stamp

Correct! A database index is a logical database structure used to improve the speed and efficiency of data access.

Bottom of Form

Back to assessments

Top of Form

Question 1

Which of the following is a logical container that groups together collections of objects within a database and allows them to be managed as a group?

• filegroup
• transaction
• data file
• Selected: schemaThis answer is correct.

Correct! A schema is a logical container that groups together collections of objects within a database and allows them to be managed as a group.

0/1

Question 2

Which of the following is true about the size of data and log files?

• Selected: You should not change the default sizes.This answer is incorrect.
• You should not set the size to be larger than the data requires.
• Increasing the size in an existing database will degrade performance.
• Best practice is to change the maxsize setting from its default value.Â

Actually, look again. The initial size setting defines the initial size of the data and log files; the default sizes are 4 MB and 1 MB, respectively. Increasing the file size after a database has been created consumes significant resources and will lead to degraded performance. Review Module 3 again, and specifically, page 120 in Chapter 4 of the textbook.

1/1

Question 3

What should you always do before creating a new database?

• Selected: Back up the master system database.This answer is correct.
• Delete all existing temporary databases.
• Install a fresh instance of SQL Server 2012.
• Stop and restart the SQL Server service.

Correct! Before creating a new database, you should always make a full backup of the master system database, which stores the configuration settings.

0/1

Question 4

Which of the following is true about the autogrowth setting?

• Selected: By default, it doubles the size of the log file when it becomes full.This answer is incorrect.
• You should keep this value as small as possible.
• The data file is affected by it, but log files are not.
• You should configure it so that file growth occurs infrequently.

Actually, look again. The autogrowth setting specifies how much the database will increase the size of the physical files once the space that was initially allocated to them becomes full; this setting can be stated in absolute or relative terms. The defaults are 1 MB for the data file and 10 percent for the log file. Due to the performance impact of increasing the size of the files, you should make these settings sufficiently large to make file growth a very infrequent event. Review Module 3 again, and specifically, page 120 in Chapter 4 of the textbook.

1/1

Question 5

What would you set to specify the number of places after the decimal point in a numeric data type?

• Selected: precisionThis answer is correct.
• scale
• length
• size

Correct! Precision refers to the number of places after the decimal point in a numeric data type, or the accuracy in the case of a date data type.

0/1

Question 6

What would you set to specify the maximum number of digits that a numeric data type can hold?

• precision
• scale
• Selected: lengthThis answer is incorrect.
• size

Actually, look again. Scale is the maximum number of digits that a numeric data type can hold, and length is the maximum number of characters that a string can store. Review Module 3 again, and specifically, page 122 in Chapter 4 of the textbook.

0/1

Question 7

Which attribute includes binary and numeric as categories?

• Selected: columnThis answer is incorrect.
• row
• data type
• record labels

Actually, look again. A data type is a predefined form of data that specifies the data values that a logical database object can store. A data type attribute must be defined for each column and variable in a database. The main data type Categories are: character and unicode string types, binary types, numeric types, and date types. Review Module 3 again, and specifically, Page I and Table 1.

1/1

Question 8

What should you define if you want to grant several users the same permissions to database objects?

• schema
• Selected: roleThis answer is correct.
• user
• access list

Correct! A role groups together permissions on different database objects.

1/1

Question 9

What permission must be granted for a user to run procedures within a database?

• Selected: executeThis answer is correct.
• list
• read
• server-level

Correct! Granting the EXECUTE permission to a user on a database allows the user to execute any procedure within that database.

1/1

Question 10

Which of the following is best described as a logical unit of work reading or writing from a database?

• I/O performance
• log file record
• Selected: database transactionThis answer is correct.
• individual filegroup

Correct! Log files record the database transaction activity. A database transaction is a logical unit of work reading or writing from a database.