The University of Texas
CS327e fall
2004 Test 1 Solution
NAME:
__________________________________ EID: __________________
Date: 2004-10-11 Duration: 60 minutes. Total: 21 Qs (100 pts).
For the multiple-choice
questions 1 through 20, circle the correct and most appropriate answer. Each multiple-choice
question carries 4 points.
1) The
Attribute Y is functionally dependent on attribute X if the value of attribute
X determines the value of Y.
|
a. |
True. |
|
b. |
False. |
2) A recursive relationship is often found in a unary
relationship.
|
a. |
True. |
|
b. |
False. |
3) Relations that have a
composite key and are in 1NF are automatically in 2NF.
|
a. |
True. |
|
b. |
False. |
4) Consider 3 tables in a database: T1, T2, and T3. T1 is in 2NF,
T2 is in 1NF, and T3 is in 3NF. Hence, the entire database is said to be in
|
a. |
1NF. |
|
b. |
2NF. |
|
c. |
3NF. |
|
d. |
4NF. |
|
e. |
BCNF. |
5) If
A à B and B à A, then A and B have a
many-to-many attribute relationship.
|
a. |
True. |
|
b. |
False. |
6)
When representing a one-to-many relationship in a relational database
design________
|
a. |
the child is always on the
one side of the "one-to-many" relationship |
|
b. |
either parent or child can
be on the one side of the "one-to-many" relationship, and the
choice is arbitrary |
|
c. |
the parent is always on the
one side of the "one-to-many" relationship |
|
d. |
either parent or child can
be on the one side of the "one-to-many" relationship, and special
criteria indicate which table should be on the one side |
|
e. |
None of the above |
7) In many-to-many
relationships in a relational database design .
|
a. |
the key of the child is
placed as a foreign key into the parent |
|
b. |
the key of the parent is
placed as a foreign key into the child |
|
c. |
the keys of both tables are
placed in a third table |
|
d. |
the parent and child lose
their key, and they have to make a new one from the locksmith |
8) By including domain information in a data model,
it is possible to identify different-named attributes based on the same domain.
|
a. |
True. |
|
b. |
False. |
9) Using products such as ERWin and Visio to analyze an
existing database is called:
|
a. |
creating project standards |
|
b. |
defining the project scope |
|
c. |
using domain expertise |
|
d. |
reverse engineering |
|
e. |
None of the above |
10) When constructing data
models from existing forms and reports, we should remember that if a child
entity has no meaning without the existence of its parent entity, then the
relationship between the entities is
.
|
a. |
an identifying connection
relationship |
|
b. |
a non-identifying
connection relationship |
|
c. |
a non-specific relationship |
|
d. |
an IS-A relationship |
|
e. |
a WAS-A relationship |
For questions 11) through 15), Consider the figure 5.10 above:
11) If PROJECT.Description is updated, ASSIGNMENT.TaskDescription gets automatically updated.
|
a. |
True. |
|
b. |
False. |
12) For a new row to be inserted into the ASSIGNMENT table, we must know the hire date of this assignment’s employee.
|
a. |
True. |
|
b. |
False. |
13) In order to delete an employee from the EMPLOYEE table, you must ensure that there are no assignments associated with this employee. Otherwise, this employee cannot be deleted.
|
a. |
True. |
|
b. |
False. |
14) For a given employee, the DBMS will always ensure that its assignment’s start dates are always greater than or equal to its hire date.
|
a. |
True. |
|
b. |
False. |
15) It is possible for this company to have 10 projects and 0 employees.
|
a. |
True. |
|
b. |
False. |
For questions 16) through 20), Consider the figure 5.22b above:
16) The APPOINTMENT table has ___ foreign keys.
|
a. |
1 |
|
b. |
2 |
|
c. |
3 |
|
d. |
4 |
|
e. |
5 |
17) The columns APPOINTMENT.ProfessorFirstName and APPOINTMENT.ProfessorLastName are foreign key columns referencing the DEPARTMENT table.
|
a. |
True. |
|
b. |
False. |
18) A student may or may not have an appointment with a professor, and a professor may or may not advise a student.
|
a. |
True. |
|
b. |
False. |
19) A Department can have co-chairs (i.e. 2 professors as chair persons).
|
a. |
True. |
|
b. |
False. |
20) If a student has a major, it is still possible for a professor from a different department (than the student’s major) to advise him/her.
|
a. |
True. |
|
b. |
False. |
21) Consider the following relation in 1NF:
R (A,
B, C, D, E, F, G)
With
the following functional dependencies:
FD1) A, E à
B, C, D, F, G
FD2) A à
C
FD3) D à
B
FD4) E à
G
PK = (A, E)
a)
(10 points) Decompose this table into table(s) that is (are) in 2NF. Clearly
show the resultant table(s) along with their respective primary keys and FDs.
FD2
and FD4 violate 2NF, since they are partial dependencies. Hence,
1)
R1R (A, C)
FD5FD2)
A à C
2)
R2R (E, G)
FD6FD4)
E à G
3)
R3R (A, B, D, E, F)
FD7FD1)
A, E à B, D, F
FD8FD3)
D à B
b)
(10 points) Decompose the above 2NF table(s) into table(s) that is (are) in
3NF. Clearly show the resultant table(s) along with their respective primary keys
and FDs.
R3R
has a transitive dependency FD8FD3. Hence,
1)
R1R (A, C)
FD5FD2)
A à C
2)
R2R (E, G)
FD6FD4)
E à G
3)
R4R3R (B, D)
FD9FD8FD3)
D à B
4)
R5R3R (A, D, E, F)
FD10FD7FD1)
A, E à D, F