2. 计算机
  3. You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. In this database, the P

You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. In this database, the P

admin2009-05-19  46
问题 You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. In this database, the Parts table has a primary key that is used to identify each part stored in the company’s warehouse. Each part has a unique UPC code that your company’s accounting department uses to identify it. The database is configured as shown in the exhibit.


You want to maintain the referential integrity between the Parts table and the OrderDetails table. You want to minimize the amount of physical I/O that is used within the database.

Which two Transact-SQL statements should you execute? (Each correct answer represents part of the solution. Choose two.)

A. CREATE UNIQUE INDEX IX_UPC On Parts(UPC)

B. CREATE UNIQUE INDEX IX_UPC On OrderDetails(UPC)

C. CREATE TRIGGER UPCRI On OrderDetails
   FOR INSERT, UPDATE As
   If Not Exists (Select UPC From Parts
   Where Parts.UPC = inserted.UPC) BEGIN
   ROLLBACK TRAN
   END

D. CREATE TRIGGER UPCRI On Parts
   FOR INSERT, UPDATE As
   If Not Exists (Select UPC From Parts
   Where OrderDetails.UPC = inserted.UPC) BEGIN
   ROLLBACK TRAN
   END

E. ALTER TABLE dbo.OrderDetails ADD CONSTRAINT
   FK_OrderDetails_Parts FOREIGN KEY(UPC)
   REFERENCES dbo.Parts(UPC)

F. ALTER TABLE dbo.Parts ADD CONSTRAINT
   FK_Parts_OrderDetails FOREIGN KEY (UPC)
   REFERENCES dbo.Parts(UPC)
选项
答案A,E
解析 Explanation:
A: The unique index on the UPC column must be created in the Parts table since every part has an unique UPC code.

E: One Part row can be contained in many OrderDetails, but each OrderDetail row only corresponds to one specific row in the Part table. There is a one-to-many relation between the Part table and the OrderDetails table. Therefore the foreign key constraint must be defined on the OrderDetails table referencing the UPC column of the Parts table.

Incorrect Answers:
B: Each part has a unique UPC code, but each part can be included in many rows in the OrderDetails table since many different orders can contain the same part. We cannot use a unique index on the UPC column in the OrderDetails table.

C: Referential integrity is best implemented with foreign key constraints not by triggers.

D: Referential integrity is best implemented with foreign key constraints not by triggers.

F: The foreign key constraint must be from the OrderDetails table referencing the UPC column in the Parts table.
If we have a foreign key constraint from the Parts table referencing the UPC column in the OrderDetails table we would have that each Part only can be part of one OrderDetails, but that one OrderDetails row could correspond to several rows. But it should be the opposite.
转载请注明原文地址:https://kaotiyun.com/show/srhZ777K
本试题收录于： 微软70228题库微软认证分类
0

微软70228

微软认证

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