Free 98-364 Sample Questions — Database Fundamentals

You have developed a stored procedure named usp_GetEmp that accepts an employee number as a parameter and retrieves the details about the employee from the CurrentEmp table of a database named Employees. You have tested it, and it works exactly as you expected. Later, another employee tries to use the stored procedure and receives the following error: The SELECT permission was denied on the object 'CurrentEmp', database 'Employees.schema 'dbo' What should you do to resolve the problem?

• A. Modify usp_GetEmp to include the With Execute As Owner clause
• B. Modify usp_GetEmp to include the With Execute As Caller clause
• C. Grant the employee the SELECT permission on the CurrentEmp table
• D. Grant the employee the SELECT permission on the Employees database

Which of the following approaches begins with the identification of only a few important concepts and, based on these, the design proceeds, spreading outward ` ̃radially'?

• A. Inside-out approach
• B. Bottom-up approach
• C. Mixed approach
• D. Top-down approach

What is the relationship between the foreign key and primary key? Each correct answer represents a complete solution. (Choose all that apply.)

• A. foreign key constraint works in conjunction with a primary key constraint to enforce referential integrity among related entities
• B. foreign key ties attribute(s) of an entity to the primary key of another entity, for the purpose of creating a dependency
• C. There is no relationship between a primary key and a foreign key
• D. foreign key and a primary key create a link between two entities

You assign User1 a set of permissions that include the WITH GRANT OPTION. The WITH GRANT OPTION enables User1 to:

• A. request a log of permission use
• B. delegate permissions to other users
• C. create new database users
• D. view other users' permissions

Your class project requires that you help a charity to create a website that registers volunteers. The website must store the following data about the volunteers: ✑ Given name ✑ Surname ✑ Telephone number ✑ Email address You need to recommend a correct way to store the data. What do you recommend?

• A. Create a table that contains columns that are named given name, surname, phone number, and email
• B. Create a table that contains rows that are named given name, surname, phone number, and email
• C. Create a view that contains columns that are named given name surname, phone number, and email
• D. Create a view that contains rows that are named given name surname, phone number, and email

Which of the following is the process of extracting trails on a regular basis so that they can be transferred to a designated security system where the database administrators do not have access?

• A. Native auditing
• B. Abstraction
• C. Database security
• D. Encryption

Which of the following are the main approaches in the database design? Each correct answer represents a complete solution. (Choose three.)

• A. Top-down approach
• B. Bottom-up approach
• C. Middle approach
• D. Inside-out approach

You need to retrieve data from two related database tables based on a column that exists in both tables. Which command should you use in a query?

• A. INTERSECT
• B. UNION
• C. JOIN
• D. TRUNCATE

Which of the following terms is described in statement below? It is a collection of conceptual tools for describing data, data relationship, data semantics, and consistency constraints.

• A. Data mode
• B. Generic Data Model
• C. Relational model
• D. Entity-Relationship Model

You are the database administrator of a MySQL database server. Your network contains a combination of new and old (pre-4.1) MySQL clients. You are configuring a new server for testing purposes. You want to allow connections only by the clients that have new-format passwords. Which of the following steps will you take to accomplish the task?

• A. Start the server with the --old-password option
• B. Start the server with the --secure-auth option
• C. Start the server with the --enable-named-pipe option
• D. Start the server with the --shared-memory option

Which of the following are the steps of database planning life cycle? Each correct answer represents a part of the solution. (Choose three.)

• A. Application Design
• B. Data conversion and loading
• C. Conceptual database design
• D. Operational Maintenance

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.)

• A. 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
• B. 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
• C. 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
• D. 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

JDBC API 2.0 defines five levels of transaction isolation for database concurrency control. Which of the following is the lowest level of transaction isolation?

• A. TRANSACTION_SERIALIZABLE
• B. TRANSACTION_NONE
• C. TRANSACTION_READ_COMMITTED
• D. TRANSACTION_READ_UNCOMMITTED
• E. TRANSACTION_REPEATABLE_READ

You have two tables named Salesperson and Sales. You need to ensure that each record in the Sales table has a valid associated salesperson record in the Salesperson table. Which database object should you add to the Sales table?

• A. Nonclustered index
• B. Primary key
• C. Foreign key
• D. Clustered index

One reason to create a stored procedure is to:

• A. Improve performance
• B. Minimize storage space
• C. Bypass case sensitivity requirements
• D. Give the user control of the query logic