Implementing the physical database

These questions are based on: 70-229 – Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition Microsoft Self-Test Software Practice Test.


Objective: Implementing the physical database
SubObjective: Create and alter database objects (including constraints, indexes, stored procedures, tables, triggers, user-defined functions and views)


Item No. 70-229.2.2.11
Multiple Answer, Multiple Choice


You are the database developer for Smith Importers, a national wholesale imports company. You supply imported goods to retail imports stores across the country. You are designing a database to track customer orders.


You want to accomplish these goals:



  • Data must be consolidated weekly into an aggregate form for reporting purposes.
  • Customers must receive e-mail notification only when an entire order has been canceled.
  • Customers must not place an order that would cause them to exceed their credit limit.
  • Customers must be able to make changes to or cancel an order only before it is prepared for shipment.
  • Orders must not be processed for customers who have an outstanding account balance more than 90 days old.
  • Customers must automatically be assigned a credit limit based on qualifications when they are added to the database.

You create these objects:



  • Customer table, which contains customer information, including credit_limit and available_credit
  • Orders table, which contains order information, including order_id, order_amount, prepare_date and ship_date
  • Order_details table, which contains line items of each order
  • INSERT trigger on the Customer table to assign a credit limit when a customer is added
  • INSERT, UPDATE trigger on the Order_details table, which computes the order amount and places that amount in the order_amount column of the Orders table
  • INSERT, UPDATE trigger on the Orders table, which prevents customers from placing orders that would cause them to exceed their available credit limit
  • DELETE trigger on the Order_details table to send an e-mail notification to the customer if an order is canceled.

Which goals are met with these actions? (Choose all that apply.)



  1. Data is consolidated weekly into an aggregate form for reporting purposes.
  2. Customers receive e-mail notification only when an entire order has been canceled.
  3. Customers cannot place an order that would cause them to exceed their credit limit.
  4. Customers can make changes to or cancel an order only before it is prepared for shipment.
  5. Orders are not processed for customers who have an outstanding account balance more than 90 days old.
  6. Customers are automatically assigned a credit limit based on qualifications when they are added to the database.

Answers:



  1. Customers cannot place an order that would cause them to exceed their credit limit.


  1. Customers are automatically assigned a credit limit based on qualifications when they are added to the database.

Tutorial:
With the actions taken, two of the goals are met. Data is not consolidated weekly into an aggregate form for reporting purposes. Customers do not receive e-mail notification only when an entire order has been canceled. Customers cannot make changes to or cancel an order only before it is prepared for shipment. Nothing prevents orders from being processed for customers who have an outstanding account balance more than 90 days old.


You have not implemented any automated method to consolidate data on a weekly basis. Automating this process would best be accomplished using a stored procedure and scheduling this procedure to run on a weekly basis. This goal is not met.


Because the DELETE trigger was created on the Order_details table, the customer will receive an e-mail each time a line item is deleted from an order. The customer notification requirement was for when an order, not a single item, was deleted. Customers are not notified when orders are deleted. This would require a DELETE trigger on the Orders table. This goal is not met.


By creating the INSERT, UPDATE trigger on the Orders table, you have ensured that customers cannot place orders that would cause them to exceed their available credit limit. This goal is met.


Because the appropriate restrictions are not in place, customers can cancel orders at any time, not just before they are prepared for shipment. Simply adding a prepare date column to the table will not ensure that an order can be canceled only before it is prepared for shipment. This goal is not met.


There are no restrictions on placing orders for customers. Therefore, customers with an outstanding account balance more than 90 days old may still place orders. But you could add this logic to the existing INSERT, UPDATE trigger on the Orders table or add another INSERT, UPDATE trigger. This goal is not met.


With the INSERT trigger created on the Customer table, customers are assigned a credit limit when they are added to the table. This goal is met.


By creating the INSERT, UPDATE trigger on the Order_details table, you ensure the order_amount column of the Orders table is updated. Without this trigger, order_amount is NULL. The quantities and items are in the Order_details table, so this data must be summarized and the Order table updated with a trigger.


Reference:
1. MCSE Training Kit Microsoft SQL Server 2000 Database Design and Implementation – Implementing Data Integrity
- Lesson 2: Implementing Integrity Constraints – FOREIGN KEY Constraints


2. MCSE Training Kit Microsoft SQL Server 2000 Database Design and Implementation – Implementing Data Integrity
- Lesson 2: Implementing Integrity Constraints – CHECK Constraints


3. MCSE Training Kit Microsoft SQL Server 2000 Database Design and Implementation – Implementing Triggers
- Lesson 2: Creating and Maintaining Triggers – Creating Triggers Using Transact-SQL


4. MCSE Training Kit Microsoft SQL Server 2000 Database Design and Implementation – Implementing Triggers
- Lesson 3: Programming Triggers – Trigger Syntax, System Commands, and Functions

Like what you see? Share it.Google+LinkedInFacebookRedditTwitterEmail
cmadmin

ABOUT THE AUTHOR

Posted in Archive|

Comment: