CET_TARDE - MTA Database - DIAGNOSTIC1

Question 1 of 12

1

John works as a Database Administrator for Bluewell Inc. The company has a SQL Server
database. A table in the database has a candidate key and an attribute that is not a constituent of
the candidate key. The non-key attribute depends upon the whole of the candidate key rather
than just a part of it. Which of the following normal forms is represented in the scenario?

Select one of the following:

4 NF
2 NF
1 NF
3 NF

Explanation

Question 2 of 12

1

You work as a Database Designer for DataOneWorld Inc. The company has a SQL Server database. You are assigned the task of creating a data model of an enterprise based on a specific data model. The model to be created should be independent of a particular DBMS. Which of the following database designs will help you accomplish the task?

Select one of the following:

Logical database design
Conceptual database design
Physical database design
Application design

Explanation

Question 3 of 12

1

Which of the following steps in database planning helps to determine the requirements of the database through interviewing?

Select one of the following:

Gathering information
Identifying the relationship between objects
Identify the objects
Identifying the type of information for each objects
Modeling the object

Explanation

Question 4 of 12

1

Consider the case of a fruit juice company. The company manufactures fruit juices and supplies them to wholesalers. The Database Designer creates a table named Production. The code is given below:
1. CREATE Table Production
2. (Fruit_type VarChar,
3. Fruit_name Char(20),
4. Quantity Int(3))
Which of the above-mentioned lines has an error?

Select one of the following:

Line 3
Line 2
Line 1
Line 4

Explanation

Question 5 of 12

1

John works as a Database Administrator for DataOneWorld Inc. The company has a SQL Server database. John wants to insert records in a table where the database is structured in a fixed format. Which of the following data models will he use to accomplish the task?

Select one of the following:

Object relational data model
Entity-Relationship Model
Network data model
Relational model

Explanation

Question 6 of 12

1

You manage a database named Customers, which includes a table named Orders. The Orders table is frequently queried, but only orders with a sales total of more than $1000.00 are required in the query. You want to create an index to speed up these types of queries at the same time, ensuring the index is as small as possible. What type of index should you use?

Select one of the following:

Non-clustered
Filtered
Clustered
XML

Explanation

Question 7 of 12

1

Which of the following database terms is described in the statement below? "It prevents the current database operation from reading or writing a data item while the data item is being accessed by another operation."

Select one of the following:

Lock
Encryption
Constraint
Deadlock

Explanation

Question 8 of 12

1

Your Company is designing and developing a number of databases for a stock exchange. One of the databases will contain highly sensitive data for which high level of security will be required. Although high processing speed is one of the prime requirements of the customer, for this database, security of the data will take priority over the processing speed. It needs to be ensured that even if unauthorized access to the database is obtained, the rogue user is unable to read the data. Which of the following protection techniques will you suggest for this database?

Select one of the following:

Authentication
Encryption
Native auditing
Integrity controls

Explanation

Question 9 of 12

1

You work as a Database Administrator for DataOneWorld Inc. Management instructs you to remove an object from the relational database management system. Which of the following statements will you use to accomplish the task?

Select one of the following:

DROP
SELECT
CREATE
ALTER

Explanation

Question 10 of 12

1

Which of the following is a building working model of a database system?

Select one of the following:

Prototyping
Bottom-up approach
Conceptual database design
Top-down approach

Explanation

Question 11 of 12

1

Remo works as a Database Designer for Tech Inc. He wants to create a table named Product. He issues the following query to create the Product table:

CREATE Product (
ProductID Char (10) NOT NULL,
OrderID Char (10) NULL
ProductName Varchar NOT NULL,
Primary key (OrderID, ProductID))

What are the errors in the above query?
Each correct answer represents a complete solution. (Choose two.)

Select one or more of the following:

An attribute declared as a primary key cannot contain NULL values.
Each attribute should be defined as a primary key separately.
A table cannot have two primary keys.
ProductName is declared as Varchar without specifying the width of the column.

Explanation

Question 12 of 12

1

You are a database developer for a database named Customers hosted on a SQL Server 2008 server. Recently, several customers were deleted from the Customers database. To ensure this is not repeated in future, you have decided to create a DML trigger to prevent it. What code will create the trigger to meet your goals? Each correct answer represents a complete solution. (Choose all that apply.)

Select one or more of the following:

CREATE TRIGGER trgDeleteCustomer ON dbo.Customers
BEFORE DELETE AS
RAISERROR ('Customers cannot be deleted. An error has been logged', 16, 10) WITH LOG ROLLBACK TRANSACTION
CREATE TRIGGER trgDeleteCustomer ON dbo.Customers
AFTER DELETE AS
RAISERROR ('Customers cannot be deleted. An error has been logged', 16, 10) WITH LOG ROLLBACK TRANSACTION
CREATE TRIGGER trgDeleteCustomer ON dbo.Customers
AFTER DELETE
AS
IF (SELECT COUNT(*) FROM DELETED) > 1
BEGIN
RAISERROR ('Customers cannot be deleted. An error has been logged', 16, 10) WITH LOG ROLLBACK TRANSACTION
END
) CREATE TRIGGER trgDeleteCustomer ON dbo.Customers
AFTER DELETE AS
IF (SELECT COUNT(*) FROM DELETED) > 0
BEGIN
RAISERROR ('Customers cannot be deleted. An error has been logged', 16, 10) WITH LOG ROLLBACK TRANSACTION
END

	Created by Hawerth Castro almost 7 years ago

Database Quizzes preparing certified

CET_TARDE - MTA Database - DIAGNOSTIC1

Question 1 of 12

Select one of the following:

Explanation

Question 2 of 12

Select one of the following:

Explanation

Question 3 of 12

Which of the following steps in database planning helps to determine the requirements of the database through interviewing?

Select one of the following:

Explanation

Question 4 of 12

Select one of the following:

Explanation

Question 5 of 12

John works as a Database Administrator for DataOneWorld Inc. The company has a SQL Server database. John wants to insert records in a table where the database is structured in a fixed format. Which of the following data models will he use to accomplish the task?

Select one of the following:

Explanation

Question 6 of 12

Select one of the following:

Explanation

Question 7 of 12

Which of the following database terms is described in the statement below? "It prevents the current database operation from reading or writing a data item while the data item is being accessed by another operation."

Select one of the following:

Explanation

Question 8 of 12

Select one of the following:

Explanation

Question 9 of 12

You work as a Database Administrator for DataOneWorld Inc. Management instructs you to remove an object from the relational database management system. Which of the following statements will you use to accomplish the task?

Select one of the following:

Explanation

Question 10 of 12

Which of the following is a building working model of a database system?

Select one of the following:

Explanation

Question 11 of 12

Select one or more of the following:

Explanation

Question 12 of 12

Select one or more of the following:

Explanation