Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 5 discussion

Actual exam question from Microsoft's 70-762

Question #: 5
Topic #: 1

HOTSPOT -
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have a database named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.

The customer table includes a column that stores the data for the last order that the customer placed.
You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized.
You need to modify the database design to meet the following requirements:
✑ Rows in the Orders table must always have a valid value for the ProductID column.
✑ Rows in the Products table must not be deleted if they arepart of any rows in the Orders table.
✑ All rows in both tables must be unique.
In the table below, identify the constraint that must be configured for each table.
NOTE: Make only one selection in each column.
Hot Area:

Show Suggested Answer

Suggested Answer:

A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Here the foreign key constraint is put on the ProductID in the Orders, and points to the
ProductID of the Products table.
With a check constraint on the ProductID we can ensure that the Products table contains only unique rows.
References:
http://www.w3schools.com/sql/sql_foreignkey.asp

by moehijawe at Feb. 16, 2020, 11:45 a.m.

Comments

Submit Cancel

moehijawe

Highly Voted 5 years, 4 months ago

requirement for product table: "Rows in the Products table must not be deleted if they arepart of any rows in the Orders table" => check constraint is not a correct answer, one of answers should be a primary key on product ID!

upvoted 18 times

NickMane

5 years, 4 months ago

I agree

upvoted 3 times

Nickname17

5 years, 1 month ago

The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row. https://www.w3schools.com/sql/sql_check.asp

upvoted 2 times

...

Andy7622

Most Recent 4 years, 5 months ago

looks like the options aren't correct

upvoted 1 times

...

eggzamtaker

4 years, 5 months ago

The given answer is unsatisfying, but it's the closest to being "right". Ideally, there should be a PK on ProductID instead of a Check Constraint.

upvoted 1 times

kyliek7

4 years, 5 months ago

but constraint is actually PK as well ;) anyway, correct

upvoted 1 times

kyliek7

4 years, 5 months ago

Example: SQL PRIMARY KEY Constraint The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields). CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT PK_Person PRIMARY KEY (ID,LastName) );

upvoted 1 times

...

geekeek1

4 years, 6 months ago

so what is the correct answer for the product table??

upvoted 2 times

...

TheDUdeu

4 years, 7 months ago

The answer is right Rows in the Orders table must always have a valid value for the ProductID column. ✑ Rows in the Products table must not be deleted if they are part of any rows in the Orders table This is satisfied by the foreign key on the productid in the orders table. ✑ All rows in both tables must be unique. This is satisfied by using a unique check constraint on productid

upvoted 3 times

...

AmandaW

4 years, 8 months ago

still don't know which is the correct answer

upvoted 1 times

...

Kepty

4 years, 8 months ago

The answers are right... The second one - check constraint - you can use a user-defined function in check constraint that can check whether there are any lines in Order Table

upvoted 3 times

...

TatyanaY

4 years, 8 months ago

"Rows in the Products table must not be deleted if they are part of any rows in the Orders table" is guaranteed by a foreign key in Orders table. However, to establish the foright key the ProductId must be unique, that is, have a unique constraint or be a primary key. The "check constraint" does not guarantee the uniqueness (or it's rather difficult to implement), nor could meet the forigh key requirements. So, the 2nd column does not have the right answer to chose from.

upvoted 1 times

MarcusJB

4 years, 7 months ago

Maybe it is "Foreign key contraint on ProductID" for both columns, because this ensures all requirements. Although you don't have to implement the foreign key in the Products table.

upvoted 1 times

...

Oooo

4 years, 8 months ago

For products table the answer is primary key constraint on ProductID , which is not in the options but is the correct answer. Refer to https://www.examtopics.com/exams/microsoft/70-762/view/39/ Qno-156

upvoted 1 times

...

Mosufe

4 years, 10 months ago

I think that it's right, because the * on the Orders table indicates that the column already is a primary key. So the Orders already are unique.... If isnt this, dont have enough options

upvoted 2 times

...

gmu

4 years, 10 months ago

I think "Rows in the Orders table must always have a valid value for the ProductID column" is related to check constraint because is only related to the column.

upvoted 1 times

...

stm22

4 years, 11 months ago

there aren't enough choices to meet all reqs order_id might already be unique if the * means pk so all you can do is: for key on prod id in order table check contraint for unique on product_id in prod table

upvoted 1 times

...