Physical design quiz

Select an ideal data type for the following data value and/or data range.

9/24/2020: Date 11:59pm: time 9/24/2020 11:59pm up to June 6, 2079: smalldatetime 9/24/2099 11:59pm: datetime

Which one of the following is FALSE about index?

All of the above are FALSE.

Which of the following is NOT a good candidate for denormalization?

All of the above are good candidate for denormalization.

Which of the following is NOT an objective of selecting a proper data type for table columns?

All of the above are objectives of selecting a proper data type for table columns.

Which of the following is NOT a goal of the physical database design?

Assure all tables are in 3NF.

_______ should be used to put a value of a column like the Order Date column of the Customer Order table. For example, a system can assign today's date from the system's clock without asking a user to manually type in.

Default value constraint

Which one of the following is FALSE about denormalization?

Denomalization reduces a chance of having insertion anomalies.

When you want to assure that a user must type in some value (not leaving a column blank), you should use:

Null value control (constraint).

When you want to reject a negative value from the Quantity Ordered column of the Order Line table, you should use:

Range control (also called as check constraint).

When a table stores only 300 records at most, which of the following data types should you use for the primary key field assigning sequential numbers (1, 2, 3...300)? For example, you store only 300 different records of Product Types for a nation-wide large retailer chain.

The PK field should be smallint.

If indexes speed up data retrieval, then why don't we index all fields in a transactional database?

The overhead of maintaining the indexes from inserting, deleting and updating the indexed records may outweigh the benefits derived.

Which of the following is TRUE about referential integrity rule?

To make SET TO DEFAULT referential integrity rule, the foreign key column must have the default value.

Which of the following is TRUE about referential integrity rule?

To make SET TO NULL referential integrity rule, the foreign key column must allow the NULL value.

When you want to assure that two employees will NOT have the exactly same login name, you should use:

Unique constraint.

Which one of the following is TRUE about foreign key constraint on the primary key and matching foreign key, Customer ID of the Customer table and Customer ID of Customer Order table?

When you type 4 in Customer ID of a record in Customer Order table, one of records in the Customer table must have 4 in its Customer ID column.

Which one of the following is FALSE about referential integrity rule on the primary key and matching foreign key, Customer ID of the Customer table and Customer ID of Customer Order table?

You chose the Restrict (or No action) option of the referential integrity. Now, you can delete a record from Customer Order table only after you delete the record of the customer from the Customer table who has placed that customer order.

When you create a new table in SQL Server the default choice is

a sorted table

If Customer Order table and Order Line table store records (rows) of the last 10 years, you should use

horizontal partitioning

Select an ideal data type for the following data value and/or data range.

human's last name: varchar(50) Abbreviation of states of the US: char(2) 02101 (one of zip codes in Boston): char(5) 中华人民共和国 as a country name: ncarchar(50) One Japanese letter that represents color, such as 赤: nchar(1)