Design And Implement Database Objects

Question 1

What is the mathematical definition of a relation?

Answer 1

Requires that you have no duplicated rows

TODO: Need better definition here

Question 2

What are the criteria for first normal form?

Answer 2

1. All Columns must be atomic - that is, each column should represent one value
2. All rows of the table must contain the same number of values - no arrays

Question 3

What is a surrogate key?

Answer 3

This is a artificial key that is normally used to provide performant uniqueness for a table row.

Question 4

What is a natural key?

Answer 4

A unique values or unique combination of values that appear in row data that uniquely define the row.

Question 5

What is Boyce-Codd Normal Form?

Answer 5

Every candidate key is identified, all attributes are fully dependent on a key, all columns must identify a fact about a key and nothing but a key.

Question 6

What is second normal form

Answer 6

All attributes must be a fact about the entire primary key and not any non-primary key attributes.

Question 7

What is third normal form?

Answer 7

All attributes must be a fact about the entire primary key and not any non-primary key attributes.

Question 8

Would the following columns fit second normal form?

SKU (Key column 1)
Location (Key column 2)
ItemDisplayName
ItemPrice
PriceDate

Answer 8

No - this is because Item Display Name describes the SKU and is not specific to where the Item is sold.

No - because prices change over time. Either the PriceDate needs to be included in the Key or the price and price date need to be split out to another table.

Question 9

TODO - Need a example question that differentiates between 2NF And 3NF

Answer 9

Need answer

Question 10

What is a schema used for?

Answer 10

This is used to group together objects for security and logical ordering

Question 11

What is Pascal-casing?

Answer 11

A naming convention what all the words are leading character capitalized and words are concatenated.

ItemPrice
PriceDate
CreateDateTime

Question 12

What is camel casing?

Answer 12

A naming convention where the first word is all lower cases and subsequent word have the leading character uppercased. Also all words are concatenated.

itemPrice
priceDate
createDateTime

Note - this is for information purposes only. Probably won’t be on the exam.

Question 13

What are the elements of a four part naming schema?

Answer 13

Server.Database.Schema.Object