The University of Texas
CS 327e Fall 2003 Test
1 Solution
NAME:
__________________________________ EID: __________________
Date: 2003-10-10 Duration: 60 mins Total: 21 Qs (100 pts).
For the multiple-choice
questions 1 through 20, Use a pencil to circle the correct and most appropriate
answer. Each multiple-choice question carries 4 points.
NOTE:
The correct answers are marked in bold.
1) A table, that does not have a composite primary key i.e.
its PK is made up of 1 column, and is in 1NF, is
|
a. |
Not in 2NF. |
|
b. |
Also in 2NF. |
|
c. |
Can never hope to be in 3NF. |
|
d. |
Is made up of a composite of wood and metal. |
2) A recursive relationship is often found in a ternary
relationship.
|
a. |
True. |
|
b. |
False. |
3) In a generalization hierarchy, the relationship between
supertype and subtype is 1:1.
|
a. |
True. |
|
b. |
False. |
4) Consider 3 tables in a database: T1, T2, T3.
T1 is in 2NF, T3 is in 1NF, and T2 is in 3NF. Hence, the entire
database is said to be in
|
a. |
1NF. |
|
b. |
2NF. |
|
c. |
3NF. |
|
d. |
4NF. |
|
e. |
BCNF. |
5)
Problems associated with keeping lists for tracking things include
|
a. |
They are difficult to change without inconsistencies. |
|
b. |
Deletions from lists may have unintended consequences. |
|
c. |
Partial data is difficult to record. |
|
d. |
All of the above. |
|
e. |
None of the above. |
6)
Foreign keys are used to represent relationships.
|
a. |
True. |
|
b. |
False. |
7)
One conceptual schema will normally have many different external schemas based
on it.
|
a. |
True. |
|
b. |
False. |
8)
Non-identifying relationships in IDEF1X are the same as 1:1 and 1:N IS-A
relationships in extended E-R model.
|
a. |
True. |
|
b. |
False. |
9)
Identifying connection relationships in IDEF1X are the same as ID-dependent
relationships in the extended E-R model.
|
a. |
True. |
|
b. |
False. |
10)
Domains
|
a. |
Are named set of values that an attribute can have. |
|
b. |
Reduce ambiguity between attributes having the same name that
may or may not refer to the same thing. |
|
c. |
Reduce ambiguity between attributes having the same values, but
may or may not be the same. |
|
d. |
All of the above. |
|
e. |
None of the above. |
11)
There is no such thing as a one-way relationship; relationships always exist in
both directions.
|
a. |
True. |
|
b. |
False. |
12)
If one attribute functionally determines the other, but not the reverse, the
attributes have a many-to-many relationship.
|
a. |
True. |
|
b. |
False. |
13)
A required parent can be specified by making a foreign key value NOT NULL.
|
a. |
True. |
|
b. |
False. |
14)
For identifying connection relationships, the child must have a parent, so the
foreign key value is required.
|
a. |
True. |
|
b. |
False. |
15)
When transforming supertype / subtype entities into a relational database
design, the key of the supertype table is placed into the subtype table
typically as the key.
|
a. |
True. |
|
b. |
False. |
16) When transforming an
Extended E-R model into a relational database design, recursive relationships
are treated fundamentally the same as other HAS-A relationships.
|
a. |
True. |
|
b. |
False. |
17)
The ideal primary key is
|
A. |
Short |
|
B. |
Numeric |
|
C. |
Seldom changing |
|
D. |
A and B |
|
E. |
A, B, and C |
18)
Which of the following is not true about surrogate keys?
|
A. |
They
are identifiers that are supplied by the system, not the users. |
|
B. |
They
have no meaning to the users. |
|
C. |
They
tend to slow database performance. |
|
D. |
A and B |
|
E. |
A, B, and C |
19) Which of the following is
not true about representing subtypes in a relational database design?
|
A. |
One
table is created for the supertype and one for each subtype. |
|
B. |
All of the attributes of
the supertype are added to the subtype relations. |
|
C. |
The
key of the supertype is typically made the key of the subtypes. |
|
D. |
A 1:1 relationship is
represented between the supertype and each subtype. |
|
E. |
An instance of the
supertype may be related to one instance each of several subtypes. |
20) When transforming an
ID-dependent E-R data model relationship into a relational database design, the
referential integrity constraints should allow
|
A. |
Parent
updates to cascade. |
|
B. |
Child updates to cascade |
|
C. |
Child
deletes to cascade. |
|
D. |
A and B. |
|
E. |
A, B and C. |
21) Consider the following table in 1NF:
R (A,
B, C, D, E, F)
With
the following functional dependencies:
FD1) A, C à
B, D, E, F
FD2) A à
B
FD3) C à
D
FD4) E à
F
a)
(10 points) Decompose this table into table(s) that is (are) in 2NF. Clearly
show the resultant table(s) along with their respective keys and FDs.
For 2NF, remove partial dependencies.
R1 (A, B) FD2) A à
B
R2 (C, D) FD3)
C à
D
R3 (A, C, E, F) FD4) E à F FD1.1) A, C à
E, F
b)
(10 points) Decompose the above 2NF tables into table(s) that is (are) in 3NF.
Clearly show the resultant table(s) along with their respective keys and FDs.
For 3NF, remove transitive dependencies.
R1 (A, B) FD2) A à
B
R2 (C, D) FD3)
C à
D
R3 (A, C, E) FD1.2) A, C à
E
R4 (E, F) FD4)
E à
F