Question

Mike has created a column called money to employee salaries. You have chosen INT as the data type for the column because SMALLINT has a max value of 32767, is small for salary data. Mike add a CHECK constraint to the column to limit it to positive values below 1million as nobody should have money higher that that. SO what is the domain of the column?

-2147483 to 2147483647

0 to 999999

-1000000 to 1000000

-32768 to 32767

Answer 1

Let’s understand domain and CHECK constraint briefly before finding the correct answer:

1. Domain: A domain of an attribute in database is the range of valid values that it can have in the database.

Example: A smallint in the database has domain -32,768 to 32,767 i.e. it can store any value within the range.

2. CHECK constraint: A CHECK constraint in database is used to manipulate the Domain of an attribute by putting explicit domain range usingbelow syntax:

Create table employee(employee_id int, salary int, CHECK (salary<1000000)

The above query will set a new domain to the salary attribute that it can have value less than 100000.

CONSIDERING GIVEN QUESTION:

Correct option: As per given instruction, Mike has added below check:

Salary to be more than 0 (positive)

Salary to be less than 1M i.e. 999999

Thus the given column salary has domain:

0 to 999999

Considering other options to find why these are incorrect:

1. -2147483 to 2147483647: This is incorrect range for any data type in database

2. -1000000 to 1000000: This would have been applied if Mike provided a check on 1M, without a negative value check as well as operator <= and >=

3. -32768 to 32767: This is default range of smallint, but Mike has chosen INT.