The Relational Data Model and Relational Database Constraints
Multiple-Choice Questions:
1) The relational model represents the database as a collection of ______.
a) files b) relations c) applications d) systems
2) In formal relational model terminology, a row is called ______.
a) tuple b) attribute c) relation d) domain
3) In formal relational model terminology, a column is called ______.
a) tuple b) attribute c) relation d) domain
4) The data type of values that appear in each column is represented by _____ of possible values.
a) range b) product c) domain d) function
5) A domain is a set of ______ values.
a) multiple b) complex c) small d) atomic
6) The degree of relation is the number of ______ of its relation schema.
a) attributes b) tuples c) data types d) relationships
7) A domain should be given _______.
a) name b) data type c) format d) all of these
8) A relation is defined as a _____ of tuples.
a) function b) set c) tree d) graph
9) The null value of an attribute indicates ______ value.
a) zero b) unknown c) infinite d) error
10) The relational model is sometimes called as ______ relational model.
a) round b) complex c) tabular d) flat
11) The relation schema can be interpreted as a type of _______.
a) statement b) assertion c) truth table d) definition
12) Each tuple in a relation can be interpreted as a _____ or a type of assertion.
a) virtual b) value c) fact d) domain
13) _______ constraint can be expressed in schema of relational model by using DDL.
a) schema-based b) inherent model-based c) application-based d) system-based
14) ________ constraint specifies that no two distinct tuples in any state of relational schema can
have same values for superkeys.
a) entity-integrity b) domain c) referential-integrity d) key
15) _______ constraint specifies that within each tuple, the value of each attribute must be atomic
value from some domain.
a) entity-integrity b) domain c) referential-integrity d) key
16) _______ key is used to identify tuples in a relation.
a) secondary b) primary c) main d) number
17) When there is more than one key in a relation, then each such key is called ______.
a) primary b) useful c) multiple d) candidate
18) _______ constraint states that no primary key value can be null.
a) key b) domain c) referential-integrity d) entity-integrity
19) _______ constraint states that a tuple in one relation that refers to another relation must refer to
an existing tuple in that relation.
a) key b) domain c) referential-integrity d) entity-integrity
20) _______ constraint is used to maintain consistency among tuples in two relations.
a) key b) domain c) referential-integrity d) entity-integrity
TRUE or FALSE:
1. All values in a column are of same data type. (T/F)
2. Each row in a table represents a collection of different data values. (T/F)
3. In formal relational model terminology, a table is called a relation. (T/F)
4. A domain is a set of composite values. (T/F)
5. A domain need not be given a format. (T/F)
6. It is possible for several attributes to have same domain. (T/F)
7. Tuples in a relation must have a particular order. (T/F)
8. Ordering of values in a tuple is important. (T/F)
9. Composite or multivalued attributes are allowed in relational model. (T/F)
10. A relation schema can have more than one key. (T/F)
11. A key can have redundant attributes. (T/F)
12. A superkey can have redundant attributes. (T/F)
13. The underlined attribute in ER diagram represents a primary key. (T/F)
14. It is better to have a primary key that has as many attributes as possible. (T/F)
15. Key and entity-integrity constraint are specified on more than one relation. (T/F)
16. The referential-integrity constraint is specified between two relations. (T/F)
17. A foreign key can have a null value. (T/F)
18. A foreign key can refer to its own relation. (T/F)
19. Insert operation cannot violate domain constraint. (T/F)
20. Delete operation can violate all constraints. (T/F)
LIBRARY DATABASE
Suppose each of the following update operations is applied directly to the LIBRARY database state.
Discuss all integrity constraints violated by each operation, if any.
1. Insert <‘114455’, ‘Discrete Mathematics’, ‘Pearson’> into BOOK.
2. Insert <‘225566’, ‘Morris’> into BOOK_AUTHORS
3. Insert <‘552211’, ‘222222’, ‘234567’, ‘3/4/2006’, ‘3/5/2006’> into BOOK_LOANS.
4. Insert <‘222345’, ‘Ahmed’, ‘1124, Street 5, Riyadh’, ‘222 5432’> into BORROWER.
5. Insert <‘774433’, ‘444444’, 12> into BOOK_COPIES.
6. Insert <‘555555’, ‘Central’, ‘555 Central Point’> into LIBRARY_BRANCH.
7. Delete the BOOK tuple with BookID = ‘881166’.
8. Delete the BORROWER tuple with CardNumber = ‘234567’.
9. Delete the LIBRARY_BRANCH tuple with Branch ID = ‘222222’.
10. Delete the BOOK_LOANS tuple with Book ID = ‘331199’.
11. Delete the BORROWER tuple with CardNumber = ‘334567’.
12. Modify the Book ID of BOOK_COPIES tuple with Book ID = ‘223344’ to ‘886655’.
13. Modify the Branch ID of BOOK_LOANS tuple with Book ID = ‘225577’ to ‘111222’.
14. Modify the Card Number of BOOK_LOANS tuple with Book ID = ‘773355’ to ‘111223’.
15. Modify the Number of Copies of BOOK_COPIES tuple with Book ID = ‘114455’ to 20.
16. Modify the Phone Number of Borrower of BORROWER tuple with Card Number =
‘223456’ to ‘345 7890’.
BOOK
Suppose each of the following update operations is applied directly to the COMPANY database
state. Discuss all integrity constraints violated by each operation, if any.
1. Insert <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1952-06-21’, ‘2365 Newcastle Rd, Bellaire,
TX’, M, 58000, ‘888665555’, 1> into EMPLOYEE.
2. Insert <‘Product A’, 4, ‘Bellaire’, 2> into PROJECT.
3. Insert <‘Production’, 4, ‘943775543’, ‘1998-02-01’> into DEPARTMENT.
4. Insert <‘677678989’, null, ’40.0’> into WORKS_ON.
5. Insert <‘453453453’, ‘John’, M, ‘1970-02-03’, ‘SPOUSE’> into DEPENDENT.
6. Delete the WORKS_ON tuples with ESSN = ‘333445555’.
7. Delete the EMPLOYEE tuple with SSN = ‘9887654321’.
8. Delete the PROJECT tuple with PNAME = ‘ProductX’.
9. Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with
DNUMBER = 5 to ‘123456789’ and ‘1999-10-01’, respectively.
10. Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN = ‘999887777’ to
‘94377543’.
11. Modify the HOURS attribute of the WORKS_ON tuple with ESSN = ‘999887777’ and
PNO = 10 to ‘5.0’.
