Database Multiple Choice Questions on “Integrity Constraints”.
1. To include integrity constraint in an existing relation use :
a) Create table
b) Modify table
c) Alter table
d) Drop table
Answer: c
Clarification: SYNTAX – alter table table-name add constraint, where constraint can be any constraint on the relation.
2. Which of the following is not an integrity constraint?
a) Not null
b) Positive
c) Unique
d) Check ‘predicate’
Answer: b
Clarification: Positive is a value and not a constraint.
3.
CREATE TABLE Employee(Emp_id NUMERIC NOT NULL, Name VARCHAR(20) , dept_name VARCHAR(20), Salary NUMERIC UNIQUE(Emp_id,Name)); INSERT INTO Employee VALUES(1002, Ross, CSE, 10000) INSERT INTO Employee VALUES(1006,Ted,Finance, ); INSERT INTO Employee VALUES(1002,Rita,Sales,20000);
What will be the result of the query?
a) All statements executed
b) Error in create statement
c) Error in insert into Employee values(1006,Ted,Finance, );
d) Error in insert into Employee values(1008,Ross,Sales,20000);
Answer: d
Clarification: The not null specification prohibits the insertion of a null value for the attribute.
The unique specification says that no two tuples in the relation can be equal on all the listed attributes.
4.
CREATE TABLE Manager(ID NUMERIC,Name VARCHAR(20),budget NUMERIC,Details VARCHAR(30));
Inorder to ensure that the value of budget is non-negative which of the following should be used?
a) Check(budget>0)
b) Check(budget<0)
c) Alter(budget>0)
d) Alter(budget<0)
Answer: a
Clarification: A common use of the check clause is to ensure that attribute values satisfy specified conditions, in effect creating a powerful type system.
5. Foreign key is the one in which the ________ of one relation is referenced in another relation.
a) Foreign key
b) Primary key
c) References
d) Check constraint
Answer: b
Clarification: The foreign-key declaration specifies that for each course tuple, the department name specified in the tuple must exist in the department relation.
6.
CREATE TABLE course ( . . . FOREIGN KEY (dept name) REFERENCES department . . . );
Which of the following is used to delete the entries in the referenced table when the tuple is deleted in course table?
a) Delete
b) Delete cascade
c) Set null
d) All of the mentioned
Answer: b
Clarification: The delete “cascades” to the course relation, deletes the tuple that refers to the department that was deleted.
7. Domain constraints, functional dependency and referential integrity are special forms of _________
a) Foreign key
b) Primary key
c) Assertion
d) Referential constraint
Answer: c
Clarification: An assertion is a predicate expressing a condition we wish the database to always satisfy.
8. Which of the following is the right syntax for the assertion?
a) Create assertion ‘assertion-name’ check ‘predicate’;
b) Create assertion check ‘predicate’ ‘assertion-name’;
c) Create assertions ‘predicates’;
d) All of the mentioned
Answer: a
Clarification: None.
9. Data integrity constraints are used to:
a) Control who is allowed access to the data
b) Ensure that duplicate records are not entered into the table
c) Improve the quality of data entered for a specific property (i.e., table column)
d) Prevent users from changing the values stored in the table
Answer: c
Clarification: None.
10. Which of the following can be addressed by enforcing a referential integrity constraint?
a) All phone numbers must include the area code
b) Certain fields are required (such as the email address, or phone number) before the record is accepted
c) Information on the customer must be known before anything can be sold to that customer
d) When entering an order quantity, the user must input a number and not some text (i.e., 12 rather than ‘a dozen’)
Answer: c
Clarification: The information can be referred to and obtained.