Note: This question is part of a series of questions that use the same or similar answer choices. An
answer choice may be correct for more than one question in the series. Each question is independent
of the other questions in this series. Information and details provided in a question apply only to that
question.
You have a database that is denormalized. Users make frequent changes to data in a primary table.
You need to ensure that users cannot change the tables directly, and that changes made to the primary table
also update any related tables.
What should you implement?
A.
the COALESCE function
B.
a view
C.
a table-valued function
D.
the TRY_PARSE function
E.
a stored procedure
F.
the ISNULL function
G.
a scalar function
H.
the TRY_CONVERT function
Explanation:
Using an Indexed View would allow you to keep your base data in properly normalized tables and maintain
data-integrity while giving you the denormalized “view” of that data.
http://stackoverflow.com/questions/4789091/updating-redundant-denormalized-data-automaticallyin-sql-server
B is correct
5
8
agree
2
5
I Don’t Agree, I’ll Defend E), Stored procedure
MY INTERPRETATION OF THIS QUESTION
1)
“You have a database that is denormalized”
repeated information over the database columns
2)
“You need to ensure that users cannot change the tables directly”
this can be done with permissions. give users permissions to views or stored procedures but forbids access to tables.
2)
“changes made to the primary table also update any related tables. What should you implement?”
this is if you change column1 in primary table all the related tables will need to be updated, whether they have a reference or not.
if you implement a stored procedure you can update the primary table, and update the related tables whether the columns are PK, FK or not, because you can create custom logic.
With views this is not true. If you have columns that are not Referenced (FK) in the related tables, you can’t update cascading. so it will not work.
the stored procedure is the only solution that can alter both referenced and unreferenced columns that might be spread throughout the database
Heres and Example:
/*RESET OBJECTS*/
DROP TABLE IF EXISTS A
DROP TABLE IF EXISTS B
DROP TABLE IF EXISTS C
DROP VIEW IF EXISTS TEST_PRIMARY_TBL_UPT
/*PRIMARY TABLE*/
CREATE TABLE A
(
A_ID int PRIMARY KEY,
A_NAME Varchar(20)
)
INSERT INTO A (A_ID, A_NAME) VALUES (1, ‘A’), (2, ‘AA’), (3, ‘AAA’)
/*SECONDARY TABLE 1*/
CREATE TABLE B(
B_ID int identity(10,10),
B_NAME Varchar(20),
A_ID int FOREIGN KEY REFERENCES A(A_ID)
ON UPDATE CASCADE,
A_NAME Varchar(20)
)
INSERT INTO B (B_NAME, A_ID, A_NAME) VALUES (‘B’, 1, ‘A’), (‘BB’, 3, ‘AAA’), (‘BBB’, 2, ‘AA’)
/*SECONDARY TABLE 2*/
CREATE TABLE C
(
C_ID int identity(10,10),
C_NAME Varchar(20),
A_ID int FOREIGN KEY REFERENCES A(A_ID)
ON UPDATE CASCADE,
A_NAME Varchar(20)
)
INSERT INTO C (C_NAME, A_ID, A_NAME) VALUES (‘C’, 1, ‘A’), (‘CC’, 3, ‘AAA’), (‘CCC’, 2, ‘AA’)
/***************** VIEW TEST *****************/
CREATE OR ALTER VIEW TEST_PRIMARY_TBL_UPT
AS
SELECT A.A_ID, A.A_NAME,
B.B_NAME, B.B_ID, B.A_ID as B_A_ID, B.A_NAME as B_A_NAME,
C.C_NAME, C.C_ID, C.A_ID as C_A_ID , C.A_NAME as C_A_NAME
FROM A
INNER JOIN B ON A.A_ID = B.A_ID
AND A.A_NAME = B.A_NAME
INNER JOIN C ON A.A_ID = C.A_ID
AND A.A_NAME = C.A_NAME;
/*CHEACK RESULT SET*/
SELECT * FROM TEST_PRIMARY_TBL_UPT
/*UPDATE ATTRIBUTE OF PRIMARY TABLE WITH PK AND FK (Cascades because of cascade option)*/
UPDATE TEST_PRIMARY_TBL_UPT
SET A_ID = 10
WHERE A_ID = 1
/*UPDATE ATRIBUTE OF PRIMARY TABLE WITHOUT PK OR FK (Doesn’t Cascade)*/
UPDATE TEST_PRIMARY_TBL_UPT
SET A_NAME = ‘Alberto’
WHERE A_NAME = ‘A’
/***************** STORED PROCEDURE TEST *****************/
CREATE OR ALTER PROC TEST_PRIMARY_TBL_UPT_SP
(
@NEW_A_NAME varchar(20),
@OLD_A_NAME varchar(20),
@NEW_A_ID int,
@OLD_A_ID int
)
AS
BEGIN
SELECT @OLD_A_NAME OLD_A_NAME,
@NEW_A_NAME NEW_A_NAME ,
@OLD_A_ID OLD_A_ID,
@NEW_A_ID NEW_A_ID
–UPDATE NON PK COLUMN
UPDATE B
SET A_NAME = @NEW_A_NAME
WHERE A_ID = @OLD_A_ID
UPDATE C
SET A_NAME = @NEW_A_NAME
WHERE A_ID = @OLD_A_ID
UPDATE A
SET A_NAME = @NEW_A_NAME
WHERE A_ID = @OLD_A_ID
–UPDATE PRIMARY TABLE PK AND CASCADE
UPDATE A
SET A_ID = @NEW_A_ID
WHERE A_ID = @OLD_A_ID
END
/*TEST SP (It Cascades even for the non referenced columns because the custom logic was written)*/
EXEC TEST_PRIMARY_TBL_UPT_SP
@NEW_A_ID = 1,
@NEW_A_NAME = ‘ANDROID’,
@OLD_A_ID = 1,
@OLD_A_NAME = ‘A’
/*CHEACK RESULT SET*/
SELECT * FROM TEST_PRIMARY_TBL_UPT
15
1
“With views this is not true. If you have columns that are not Referenced (FK) in the related tables, you can’t update cascading. so it will not work.”
CORRECTION
With views this is not true. If you update columns that are not Referenced (FK) in the related tables, it will not cascade . so it will leave that data inconsistent throughout the tables.
0
0
i agree, the right answer it’s D
requirement:You need to ensure that users cannot change the tables directly, and that changes made to the primary table also update any related tables.
–> If you want users to be able to use views to update data, base the view on a single table, or use a stored procedure to perform the update.
–IMPORTANT!! You cannot delete a row if the view references more than one base table. You can only update columns that belong to a single base table.
–>IMPORTANT! You cannot insert a row if the view references more than one base table.
so we need a SP
3
0
Sorry i meant answer is E: stored procedure
3
0
D
When modifying data through a view (that is, using INSERT or UPDATE statements) certain limitations exist depending upon the type of view. Views that access multiple tables can only modify one of the tables in the view. Views that use functions, specify DISTINCT, or utilize the GROUP BY clause may not be updated. Additionally, inserting data is prohibited for the following types of views:
3
2
B
1
2
teoricamente es una vista
As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
Fuente:https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-2017
faltaria un ejemplo practico para corroborar la veracidad
0
1
the correct answer is B.
adjunto fundamento segun maestros MICROSOFT Y REDGATE
USE AdventureWorks2012
GO
—————————————————-
–Returns employee name and current and previous departments.
———————————————————-
CREATE VIEW [HumanResources].[vEmployeeDepartmentHistory]
AS
SELECT
e.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,s.[Name] AS [Shift]
,d.[Name] AS [Department]
,d.[GroupName]
,edh.[StartDate]
,edh.[EndDate]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh
ON e.[BusinessEntityID] = edh.[BusinessEntityID]
INNER JOIN [HumanResources].[Department] d
ON edh.[DepartmentID] = d.[DepartmentID]
INNER JOIN [HumanResources].[Shift] s
ON s.[ShiftID] = edh.[ShiftID];
G0
———————————————
–FUENTE:https://www.red-gate.com/products/sql-development/sql-doc//browse-sql-doc/TestServer/User_databases/AdventureWorks2012/Views/vEmployeeDepartmentHistory.html
——————————————————————————-
–Modify Data Through a View
————————————
USE AdventureWorks2012
GO
UPDATE HumanResources.vEmployeeDepartmentHistory
SET StartDate=’2003-02-15′ , EndDate=getdate()
WHERE FIRSTNAME=’KEN’ AND LASTNAME =’Myer’
—————————-
FUENTE:https://docs.microsoft.com/en-us/sql/relational-databases/views/modify-data-through-a-view?view=sql-server-2017
—————————————–
0
3