Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 183
Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 183 A PPLY YOUR K NO WLEDGE Review Questions 1. How would you design a set of tables in a circumstance where the deletion of one record should cause the deletion of records in related tables? 2. What are the storage considerations in an extremely limited budget? What basic configuration requirements would be set up to ensure optimum data recoverability in the event of data corruption? 3. How do you use SQL Server 2000 technologies to maintain data integrity? 4. If data and indexes are stored in two different filegroups, what considerations are there for performing backups? 5. For what purposes does the term schema apply? Exam Questions 1. You are working for a large international organization that supplies packaging materials for companies that require custom commercial designs. The number of products is becoming too large for the current computer system to handle and you need to provide a solution that will spread the load over the current server and a new machine coming into the system. Queries need to be performed over a wide variety of products and there is no predictable pattern to the queries. What is an appropriate technique to implement the changes? A. Configure replication using the new machine as a subscriber and the original machine as the publisher/distributor to balance the workload. B. Separate the table into two smaller tables and place one table on each server. Configure a partitioned view and appropriate constraints on each of the machines. C. Implement multi-server clustering so that each of the two servers can respond to data activities, thus achieving a balanced workload. D. Configure log shipping on both servers to have a copy of the data on each of the servers and propagate all changes to the alternate machine. 2. As a developer for a large healthcare provider, you are assigned the task of developing a process for updating a patient database. When a patient is transferred from one floor to another, an internal identifier, CurrentRoomID, which is used as the Primary Key, needs to be altered while the original key, AdmittanceRoomID, is still maintained. If a patient is moved more than once, only the original key and the current key need to be maintained. Several underlying tables have been configured for referential integrity against the patient table. These underlying tables must change in an appropriate manner to match with one or the other of the room keys in the patient table. These relationships will be altered based upon different situations in other tables. Figure 3.11 illustrates the PatientTracker table design exhibit. What method would you use to accommodate the update? A. Use the Cascade Update Related Fields option to have changes in the Primary Key automatically update the keys in all referenced tables. B. Use an indexed view to enable the user to make changes to multiple tables concurrently. FIGURE 3.11 PatientTracker table design exhibit.
For reliable and cheap web hosting services please check tomcat web hosting website.