2. 计算机
  3. You are the administrator of a SQL Server 2000 computer. The server contains a database named MedicalRecords. The database table

You are the administrator of a SQL Server 2000 computer. The server contains a database named MedicalRecords. The database table

admin2009-05-19  22
问题 You are the administrator of a SQL Server 2000 computer. The server contains a database named MedicalRecords.
The database tables are configured as shown in the exhibit.


The existing PatientID field is an identity field. According to a new government regulation, the patient IDs must include a three-character prefix before the numeric portion of the ID. The patient’s home address defines the prefix.

You want to comply the regulation while minimizing changes to the database. Which two actions should you take? (Each correct answer presents part of the solution. Choose Two)

A. Drop the FOREIGN KEY constraints. In each table, change the data type of the PatientID field to char.

B. Add an On Update constraints to the Patients table, and link it to the PatientID field. Update the field in the Patients table.

C. In the Patients table, add a new field named StatePatientID that has a data type of char. Populate the field with the prefix and the existing PatientID.

D. In each table, update the PatientID field. Re-create the FOREIGN KEY constraints.

E. In the Patients table, update the PatientID field.

F. Create a trigger that populates the StatePatientID field when a new patient is created.
选项
答案C,F
解析 Explanation: The PatientID column currently holds an integer value. The new requirement demands that the patientID column includes the characters preceding the integer value. We want to implement this change with minimal change of the database. There are basically two approaches to this problem:

Method 1: add a new column StatePatientID:
1. Add the StatePatientID with data type char to the patients table. (C)
2. Populate the field with the three letters and the existing number from the PatientID column. (C)
3. Create a trigger that populates the StatepatientID field when a new patient is created. (F)

Method 2: change the PatientID column to char:
4. Drop foreign key constraints on the PatientID column. (A)
5. Change the data type of the PatientID column to char. (A)
6. Update the PatientID column in each table. We add the three letters to the integer value. (D)
7. Recreate the foreign key constraint. (D)

Method 1 only makes changes to one table meeting the requirement that changes to the database should be minimized. It would on the other hand introduce redundant data since the new StatePatientID column is derived from the PatientID column. It is no requirement to keep a normalized database so method 1 is the preferred solution.

Incorrect Answers:
A, D: Dropping all foreign and primary key constraints on the PatientID column, changing the column PatientID to char and then recreating all foreign and primary key constraints on the PatientID column throughout the database would not minimize changes to the database.

B: An ON UPDATE trigger on the PatientID field doesn’t make much sense. The PatientID field is an identity and wouldn’t be updated.

E: If the PatientID field is changed in the Patients table then it must be changed in all the other tables as well.
转载请注明原文地址:https://kaotiyun.com/show/iuhZ777K
本试题收录于： 微软70228题库微软认证分类
0

微软70228

微软认证

相关试题推荐
随机试题
最新回复(0)