Name:     ID: 
 
Email: 

ITSE1345 Chapter 4 Review

True/False
Indicate whether the statement is true or false.
 

 1. 
Constraints are used to ensure the accuracy and integrity of the data contained in the database.
 

 2. 
A constraint can only be created as part of the CREATE TABLE command.
 

 3. 
A primary key is usually given the abbreviation _pk in the constraint name if the name is assigned by the user.
 

 4. 
A constraint for a composite primary key must be created at the table level.
 

 5. 
A PRIMARY KEY constraint will make certain the column designated as the primary key does not contain a NULL value.
 

 6. 
A FOREIGN KEY constraint will not allow a row containing a NULL value in the foreign key column to be added to the table.
 

 7. 
A UNIQUE constraint is the same as a PRIMARY KEY constraint, except that it will accept NULL values.
 

 8. 
A CHECK constraint requires that a data value meet a certain condition before the record is added to the database table.
 

 9. 
With the exception of the NOT NULL constraint, constraints can be added to a table using the ADD clause of the ALTER TABLE command.
 

 10. 
Each column can only be included in one constraint.
 

Modified True/False
Indicate whether the statement is true or false.  If false, change the identified word or phrase to make the statement true.
 

 11. 
A Datatype is a rule used to ensure the accuracy of data stored in a database. _________________________

 

 12. 
The default name for a constraint is SYS Cn where n consists of a number that will make the name unique within the database. _________________________

 

 13. 
If a data value violates a(n) constraint, the entire row is prevented from being added to the table. _________________________

 

 14. 
A(n) UNIQUE constraint will allow NULL values to be stored in the designated column. _________________________

 

 15. 
In the USER_CONSTRAINTS view, the constraint type for a NOT NULL constraint will be listed as N. _________________________

 

Multiple Choice
Identify the choice that best completes the statement or answers the question.
 

 16. 
The NOT NULL constraint can only be created at the ____ level.
a.
table
c.
database
b.
column
d.
both a and b
 

 17. 
If a constraint applies to more than one column, the constraint must be created at the ____ level.
a.
column
c.
row
b.
table
d.
database
 

 18. 
When used with a FOREIGN KEY, the keyword REFERENCES refers to what?
a.
reference integrity
c.
referential integrity
b.
relational integrity
d.
relational constraint
 

 19. 
Where does the Oracle10g server store information about objects in the database, including information about constraints?
a.
in the data reference manual
c.
in the data view dictionary
b.
in the objects dictionary
d.
in the data dictionary
 

 20. 
Which clause will allow you to disable a constraint?
a.
MODIFY
c.
DISABLE
b.
DROP
d.
STOP
 
 
Structure of the ORDERITEMS table
nar001-1.jpg
 

 21. 
Based on the structure of the ORDERITEMS table, which of the following commands was most likely used to create the table?
a.
CREATE TABLE orderitems
(order# NUMBER (4) PRIMARY KEY,
item# NUMBER(2) PRIMARY KEY,
isbn VARCHAR2(10),
qty NUMBER(3));
b.
CREATE TABLE orderitems
(order# NUMBER (4),
item# NUMBER(2),
isbn VARCHAR2(10),
qty NUMBER(3),
NOT NULL (order#, item#));
c.
CREATE TABLE orderitems
(order# NUMBER (4),
item# NUMBER(2),
isbn VARCHAR2(10),
qty NUMBER(3),
PRIMARY KEY(order#, item#));
d.
CREATE TABLE orderitems
(order# NUMBER (4),
item# NUMBER(2),
isbn VARCHAR2(10),
qty NUMBER(3),
PRIMARY KEY(order#),
PRIMARY KEY(order#));
 

 22. 
Based on the structure of the ORDERITEMS table, which of the following commands will add a NOT NULL constraint to the ISBN column?
a.
ALTER TABLE orderitems ADD (isbn NOT NULL);
b.
ALTER TABLE orderitems ADD NOT NULL (isbn);
c.
ALTER TABLE orderitems ADD isbn NOT NULL;
d.
none of the above
 

 23. 
Based on the structure of the ORDERITEMS table, which of the following commands will make certain that the ISBN entered actually exists in the ISBN column of the BOOKS table?
a.
ALTER TABLE orderitems
ADD FOREIGN KEY isbn REFERENCES BOOKS TABLE (isbn);
b.
ALTER TABLE orderitems
MODIFY FOREIGN KEY (isbn) REFERENCES books(isbn);
c.
ALTER TABLE orderitems
CREATE FOREIGN KEY (isbn) REFERENCES books(isbn);
d.
ALTER TABLE orderitems
ADD FOREIGN KEY (isbn) REFERENCES books(isbn);
 

 24. 
If a PRIMARY KEY constraint, named ORDERITEMS_PK, for the ORDER# and ITEM# columns of the ORDERITEMS table have been disabled, which of the following commands will enable the constraint?
a.
ALTER TABLE orderitems
ENABLE PRIMARY KEY CONSTRAINT;
b.
ALTER TABLE orderitems
ENABLE CONSTRAINT orderitems_pk;
c.
ALTER TABLE orderitems
MODIFY PRIMARY KEY CONSTRAINT;
d.
ALTER TABLE orderitems
MODIFY PRIMARY KEY CONSTRAINT ENABLE;
 
 
Structure of the PROMOTION table
nar002-1.jpg
 

 25. 
Based on the structure of the PROMOTION table, which of the following commands will ensure that the value entered for the MINRETAIL column is less than the value entered for the MAXRETAIL column?
a.
ALTER TABLE promotion
MODIFY CHECK ( minretail < maxretail);
b.
ALTER TABLE promotion
ADD CHECK ( minretail < maxretail);
c.
ALTER TABLE promotion
MODIFY MINRETAIL CHECK ( < maxretail);
d.
ALTER TABLE promotion
ADD CHECK minretail < maxretail;
 



 
         Start Over