You are designing a Windows Azure SQL Database for an order fulfillment system. You
create a table named Sales.Orders with the following script.
Each order is tracked by using one of the following statuses:
• Fulfilled
• Shipped
• Ordered
• Received
You need to design the database to ensure that that you can retrieve the following
information:
• The current status of an order
• The previous status of an order.
• The date when the status changed.
The solution must minimize storage.More than one answer choice may achieve the goal.
Select the BEST answer.
A.
To the Sales.Orders table, add three columns named Status, PreviousStatus and
ChangeDate. Update rows as the order status changes.
B.
Create a new table named Sales.OrderStatus that contains three columns named
OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes.
C.
Implement change data capture on the Sales.Orders table.
D.
To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and
ReceivedDate. Update the value of each column from null to the appropriate date as the
order status changes.
I think A will minimize storage.
There is another version of this question where B is correct:
You need to design the database to ensure that you can retrieve the status of an order on a given date. The solution must ensure that new statuses can be added in the future.
Option A is wrong for the simple fact that: “All columns must depend on the key, the whole key, and nothing but the key.”
Question does not ask for 3NF- “All columns must depend on the key, the whole key, and nothing but the key.”
Option A would minimize storage and meet the requirement – current previous status (Slow Change Dimension Type3) – so A should be the correct answer.
Apparently, you have NO idea that one of the key objectives being tested on this exam, is your knowledge of Normalization!! If you do, you wouldn’t have said the above.
Anyway, good luck with your choice answer.
SQL Database solution is NOT just about OLTP which is typically highly Normalization.
For example; in contrast to OLTP, Data warehouses often use denormalized or partially denormalized schemas (such as a star schema) to optimize query performance.
The question is asking for achieving current & previous status and minimizing the storage, so option A should be best answer.
If the question is asking for “ensure that new statuses can be added in the future.”, then option B would be the best answer.
Makes Sense, Option ‘A’ can serve all three requirments with minimum harddisk storage.
I think if you add new column to the exciting table will be less in storage than creating a new table
So A is the right answer
Answer A would violate normalization forms and thus would not be BEST solution and it will actually consume more disk space when number of records increase.
If you compare these lines and lets say you have one million orders, it is obvious B is the correct answer and takes less disk space:
option A, you would add following columns:
Status (varchar(10)), previous status (varchar(10)), ChangeDate (Date)
option B, you would add following columns in new table:
OrderID (int), StatusDate (Date), Status (varchar(10))
+1
If all the order will have multiple status changes, as they will, this isn’t the optimal solution
It will be optimal, as every Status Change would have an accompanying date.
The actual status info is not saved in the Sales.Orders table, but is correlated as the primary key of the table (OrderID) would be the foreign key in the Sales.OrderStatus table.
but after order completion, there will be up to 4 rows per order. So storage required: 4 * (4+3+10) = 68 Bytes per order.
And A. otion requires only 10+10+3=23 Bytes per order.
Answer B: correct answer. The table will store just the statuses that the order had, you are also able to track the change date of all previous statuses
Answer A: you are not able to track the change date of all Order statuses just the most recent one
ADVENTURE WORKS CYCLE CASE STUDY
A. General overview
Adventure Works Cycle is an international company that sells cycling equipment and accessories.
Adventure Works cycles currently has 300 employees, and is experiencing rapid growth.
The company has a sales department, a marketing department, an accounting department, and a human resources department.
• Physical Location
The company has one main office located in Los Angeles. The office contains 100 employees. The other 200 employees, called field associates, are spread across North America and Europe.
B. Existing Environment.
• Active directory
The network contains one Active Directory forest named adventure_works.com. The forest contains a single domain. All domain controllers run Windows Server 2012 R2.
• SQL Server
Adventure Works Cycles has an existing customer relationship management (CRM) system that the company plans to replace. The company is developing a replacement application in-house. The new CRM application will be used by the field associates and by the sales management team in the main office.
The network currently contains one SQL Server 2008 server located in the main office. For the new application, the company plans to deploy additional SQL Servers for failover and redundancy and plans to use SQL Server 2014.
• Problem statements
Adventure Works cycles identifies the following issues with the current CRM system:
• Users report that a view named View1 takes a long time to run.
• Due to high network latency, users report slow database access times.
• Users do not always have current sales data and product inventory data.
• Database updates sometimes timeout. The database administrators discover the occurrence of deadlocks.
• Existing backup Solution
Currently, SQL server backups are stored on Linear Tape-open (LTO) tapes drives for long-term storage.
Adventure Works Cycle wants to eliminate completely the hardware requirements for long term backup storage.
C. Requirements
• Databases
The new CRM application will use three databases named DB1, DB2, and DB3. DB1 will be used by developers for testing purposes. DB1 will be a copy of DB2. DB2 will be the production database for the application. DB3 will be used for SQL Server Reporting Services (SSRS).
• Technical Requirements
Adventure works cycles identify the following technical requirements for the new CRM application:
• Database locking must be minimized.
• Database downtime must be minimised if a database fails.
• Users must not have direct access to the database tables.
• Multiple development teams will work on the new application. The design team must be able to modify the database tables. The development team members must not be able to modify tables, but must be able to create stored procedures and views.
• Schemas
In DB2, you create a schema named prod_schema1. You create a new database role named dev_role1. Dev_role1 must be able to retrieve data from all of the tables that belong to dev_schema1.
In DB2, you create a schema named dev_schema1. Dev¬_schemma1 contains several tables. Developers will create new tables in dev_schema1 only. Once the tables are validated, the tables will be move to prod_schema1.
• Sales.TransactionHistory
You plan to create a table named Sales.TransactionHistory. You will create an index for the table by using the following statement.
CREATE NONCLUSTERS INDEX [IX_TransHistory_PrductID] ON [Sales]. [TransactionHistory]
(
[ProductID] ASC
)
WITH (FILLFACTOR =0)
You will create a query named Query1 that uses the following statement.
SELECT [Sales].[Transaction History].prductID,
[Sales].[TransactionHistory].[Quantity]
FROM Sales.TransactionHistory
ORDERS BY Sales.TransactinHistory.Quantity ASC
During testing, you run the SQL Server profiler and you discover that Query1 uses a significant amount of disk resources while executing.
When user updates Sales.TransactionHistory, dirty reads, nonrepeatable reads, and phantom reads occur and must be prevented.
• Sales.SalesOrder
You plan to create a table named Sales.SalesOrders by using the following statement.
CREATE TABLE [Sales].[SalesOrders] (
[SalesOrderID] int IDENTITY (1, 1) NOT NULL,
[PartNum] int NOT NULL,
[DateRequested] datetime NULL,
[Qty] int NOT NULL,
[UnitAskPrice] float NULL,
[ShipDate] datetime Not NULL,
);
Go
You must ensure that when users update SalesOrders, other users are prevented from modifying the data in the table while the update is installing.
• Database Health
Adventure Works Cycles identifies the following health requirements:
• In Microsoft Azure, create and store a backup copy of DB1 once a week.
• Check the consistency between system metadata tables for DB1.
• Check the consistency of the disk space structure used by DB2
• Check the logical and physical consistency of the objects in DB3.
• User reports
Frequently, users will access a report named Report1. Report1 will be based on a view named View1. View1 will aggregate data from multiple columns. View1 will perform complex calculations.
Question 1
What should you recommend for the updates to Sales.TransactionHistory?
A. query hints
B. a SERIALIZABLE isolation level.
C. a REPEATABLE READ isolation level.
D. a READ COMMITED isolation level.
Question 2
You need to recommend a solution to back up DB1.
What should you include in the recommendation?
A. Azure Blob storage.
B. Azure Table storage.
C. Azure Queue Storage.
D. Azure Document DB.
Question 3
You need to recommend which statement should be used to update SalesOrder.
How should you recommend completing the statement? To answer, drag the appropriate elements to the correct locations. Each element may be once, more than once, or not at all.
You may need to drag the split bar between panes or scroll to view to view content.
Options to drag
EXPLICIT
ISOLATION
READ UNCOMMITED
ROLLBACK
SERIALIBLE
SNAPSHOT
TABLOCK
TRANSACTION
Answer area
SET TRANSACTION ISOLATION
LEVEL ———–
BEGIN TRANSACTION
UPDATE SalesOrder
…
COMMIT TRANSACTION
Question 4
What should you create in Azure to support the creation of the backups for DB1?
A. a storage account.
B. An Azure content delivery Network (CDN) endpoint.
C. A Service Bus namespace.
D. A cloud service.
Question 5(sent the printscrean)
On DB1, a developer creates a table names Table1.
What statement should you use after the table is validated? To answer, select appreciate options in the answer area.
Question 6
You need to assign security to dev_role1
How should you complete the code? To answer, drag the appropriate elements to the correct locations. Each element may be used once or more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Elements
AUTHORIZATION
CONTROL
dev_role1
dev_schema1
OBJECT_ID
SCHEMA
SELECT
Answer Area
——————- ON ———–::[————-] TO [————
CONTROL ON SCHEMA dev_schema1 To dev_role1
B is my answer
Did you manage to pass exam?
Option B is the correct answer. This is final.
I’m agree B is the correct answer.
May anyone who have recently passed exam 70-465 sent me an e-mail on [email protected]
Option A would minimize storage and meet the requirement – current previous status (Slow Change Dimension Type3) – so A should be the correct answer.
May anyone who have recently passed exam 70-465 sent me an e-mail on [email protected] . I have failed this exam for 3 times so now I know some question but I’m not sure about the answers.
B is right only when the question specificly mention normalization, otherwise B consumes more storage than A.
And just my thought, the correct answer is D. We can tell the below 3 requirements:
• The current status of an order
• The previous status of an order.
• The date when the status changed.
by examing the date of each status (FulfilledDate, ShippedDate, and ReceivedDate).
and 3 date data type consumes 8 x 3 = 24 bytes, — datetime consume 8 bytes
but answser A consumes Status – 10 bytes, PreviousStatus – 10 bytes and ChangeDate – 8 bytes = 28 bytes
+1 for A
It’s difficult to say which scenario (A or B) will minimize the storage.
The question does not mention how frequently an order may change its state. However we can guess that an order may chance status 4 times by average (ordered–> fulfilled –> shipped –> delivered).
In this scenario, option A is correct answer because it minimize the storage compared with solution B
Alter Table Sales.Orders
Add FullfilledDate Datetime,
ShippedDate Datetime,
ReceivedDate Datetime;
What would we really do?
– Normalize the statuses with an ID (tinyint!!)
– Choose solution A with 10 byte additional space per row (1+1+8) or 5 when using date instead of datetime.
Who can beat this?