You work as a Software Developer for XYZ CORP. You create a SQL server database named DATA1 that will manage the payroll system of the company. DATA1 contains two tables named EmployeeData…

Question

Options

• ADefine triggers in the EmployeeData table.
• BAdd stored procedures by using Transact-SQL queries.
• CAdd constraints to the EmployeeData table.
• DDefine indexes in the EmployeeData table.
• EDefine views in the database.

• A
  17% (6)
• B
  83% (29)

Explanation

In the given scenario, you will add constraints to the EmpID and DeptNo columns of the EmployeeData table, as you want EmpID to be unique, and the number entered in the DeptNo column to be valid. A constraint enforces the integrity of a database. It defines rules regarding the values allowed in the columns of a table. A constraint is the standard mechanism for enforcing integrity. Using constraints is preferred to using triggers, rules, and defaults. Most of the RDBMS databases support the following five types of constraints: NOT NULL constraint: It specifies that the column does not accept NULL values. CHECK constraint: It enforces domain integrity by limiting the values that can be placed in a column. UNIQUE constraint: It enforces the uniqueness of values in a set of columns. PRIMARY KEY constraint: It identifies the column or set of columns whose values uniquely identify a row in a table. FOREIGN KEY constraint: It establishes a foreign key relationship between the columns of the same table or different tables. Following are the functions of constraints: Constraints enforce rules on data in a table whenever a row is inserted, updated, or deleted from the table. Constraints prevent the deletion of a table if there are dependencies from other tables. Constraints enforce rules at the column level as well as at the table level. Defining indexes in the EmployeeData table will help you find employee information based on EmpID, very fast. An index is a pointer to a table. It speeds up the process of data retrieval from a table. It is stored separately from a table for which it was created. Indexes can be created or dropped without affecting the data in a table. The syntax for creating an index is as follows: CREATE INDEX <Index name> Indexes can also be used for implementing data integrity in a table. A unique index does not allow duplicate values to enter in a row if a particular column is indexed as a unique index. The syntax for creating a unique index is as follows: CREATE UNIQUE INDEX <Index name> You will also add a stored procedure named AddEmp by using Transact- SQL queries. AddEmp will accept data values for new employees and will subsequently add a row in the EmployeeData table. Stored procedures are precompiled SQL routines that are stored on a database server. They are a combination of multiple SQL statements that form a logical unit and perform a particular task. Stored procedures provide the capability of combining multiple SQL statements and improve speed due to precompiled routines. Most of the DBMS provide support for stored procedures. They usually differ in their syntax and capabilities from one DBMS to another. A stored procedure can take three parameters: IN, OUT, and INOUT. Note: Stored procedures are very similar to functions and procedures of common programming languages. You will also define a view named DeptEmpView that will combine data from the Department and EmployeeData tables and thus produce the required result. A view can be thought of as a virtual table. The data accessible through a view is not stored in the database as a distinct object. Views are created by defining a SELECT statement. The result set of the SELECT statement forms the virtual table. A user can use this virtual table by referencing the view name in SQL statements in the same way a table is referenced. Answer: A is incorrect. You do not need to define any triggers in the EmployeeData table, as they are not required while making the EmpID unique, or while entering valid data values in DeptNo. A trigger is a special kind of stored procedure that automatically runs when data in a specified table is updated, inserted, or deleted. Triggers can query other tables and can include complex SQL

No community discussion yet for this question.