Module 04 - Data Modeling

Question 1

Accessing table structures

Answer 1

Question 2

Count rows

Answer 2

SELECT COUNT (*)

Question 3

Examing columns (Acessing table structures)

Answer 3

SELECT *

FROM [TABLE]

WHERE ROWNUM <=

Question 4

Checking referential integrity constraints

Answer 4

Given two tables with an assumed foreign key constraint:

SKU_DATA (SKU, SKU_Description, Department,Buyer)

INVENTORY (WAREHOUSEID, SKU, SKU_Description ,QUANTITYONHAND, QUANTITYONORDER)

Where INVENTORY.SKU must exist in SKU_DATA.SKU

}To find any foreign key values that violate the foreign key constraint:

}

SELECT SKU

FROM INVENTORY

WHERE SKU NOT IN

(SELECT SKU

FROM SKU_DATA);

Question 5

Normalization vs Denormalization

Answer 5

For updateable DB: Normalization

For read-only DB: Denormalization

Question 6

Read-only databases

Answer 6

Are nonoperational databases using data extracted from operational databes.

Used on querying, reporting, and datamining applications

Question 7

Denormalization

Answer 7

Is the joining of the data in normalized tables prior to storing the data. The data is then stored in nonnormalized data

Question 8

Common design problems

Answer 8

Question 9

Checking for NULL values

Answer 9

SELECT COUNT(*) AS QuantityNullCount

FROM ORDER_ITEM

WHERE Quantity IS NULL;

Question 10

Multivalue problem

Answer 10

The multivalue, multicolumn problem occurs when multiple values of an attribute are stored in more than one column.

}Solution = like the 4NF solution for multivalued dependencies, use a separate table to store the multiple values.

Question 11

Inconsistent values problem

Answer 11

Inconsistent values occur when different users, or different data sources, use slightly different forms of the same data value

Detection method:

SELECT SKU_Description, COUNT(*) AS NameCount

FROM SKU_DATA

GROUP BY SKU_Description;

Question 12

Missing values problem

Answer 12

A missing value or null value is a value that has never been provided.

In a database table, a null value appears in upper case letters as NULL.

Question 13

General-purpose remarks columns

Answer 13

A general-purpose remarks column is a column with a name such as:

• Remarks
• Comments
• Notes

It often contains important data stored in an inconsistent, verbal, and verbose way.

• A typical use is to store data on a customer’s interests.

Such a column may:

• Be used inconsistentlyHold multiple data items