The
CS327e spring
2005 Test 1 Solution
NAME:
__________________________________ EID: __________________
Date: 2005-03-04 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.
SOLUTIONS ARE MARKED IN BOLD.
1) In
a relation where the only key is a single-attribute key, that relation will
always be in 2nd NF.
|
a. |
True. |
|
b. |
False. |
2) A recursive relationship is often found in a ternary
relationship.
|
a. |
True. |
|
b. |
False. |
3) In a relation having 1 PK
(primary key) and 1 AK (alternate key), the relationship between the PK and AK
is
|
a. |
1:1 |
|
b. |
Either 1:M or M:1 |
|
c. |
M:N |
|
d. |
unfriendly |
4) Consider 3 tables in a database: T1, T2, and T3. T1 is in 1NF,
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 does not à 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 parent is always on the
many 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 child is always on the
one side of the "one-to-many" relationship |
|
d. |
None of the above |
7) In many-to-many
relationships in a relational database design .
|
a. |
the PK of the child is
placed as a foreign key into the parent |
|
b. |
the AK of the parent is
placed as a foreign key into the child |
|
c. |
the PKs of both tables are placed as a composite PK in a third
table |
|
d. |
the parent and child lose
their key, and they need to report to the proper authorities for a
replacement |
8) In going from Logical to Physical Model, Entities,
Attributes, and Relationships transform respectively into
|
a. |
Entities, Columns, Primary
Keys |
|
b. |
Tables, Columns, Alternate
keys |
|
c. |
Tables, Attributes, Foreign
Keys |
|
d. |
Tables, Columns, Foreign Keys |
|
e. |
None of the above |
9) A conceptual schema can be represented by many
internal schemas, depending on the product and technique to be used.
|
a. |
True |
|
b. |
False |
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. |
a non-identifying
connection relationship |
|
b. |
an identifying connection relationship |
|
c. |
a non-specific relationship |
|
d. |
an IS-A relationship |
|
e. |
SUCH-A-TERRIBLE
relationship |
For questions 11) through 15), Consider the figure 5.10 above:
11) If an EMPLOYEE.HireDate is updated from a NULL value to a NON-NULL value, ASSIGNMENT.StartDate gets automatically updated due to the “U:C” on EMPLOYEE side of the relationship.
|
a. |
True. |
|
b. |
False. |
12) For a new row to be inserted into the ASSIGNMENT table, ASSIGNMENT.ProjectCode value in this new row must be a value that exists in PROJECT.ProjectCode.
|
a. |
True. |
|
b. |
False. |
13) A new row can be inserted into the PROJECT table without the project having any assignments or employees assigned to do assignments for this project.
|
a. |
True. |
|
b. |
False. |
14) For a given assignment, the DBMS will always ensure the referential integrity constraint: the start date is always less than or equal to its end date.
|
a. |
True. |
|
b. |
False. |
15) It is possible for this company to have projects that don’t have any assignments, and employees that are not working on any assignments.
|
a. |
True. |
|
b. |
False. |
For questions 16) through 20), Consider the figure 5.22b above:
16) The STUDENT table has ___ primary key(s).
|
a. |
0 |
|
b. |
1 |
|
c. |
2 |
|
d. |
3 |
|
e. |
4 |
17) The column APPOINTMENT.DepartmentName can accept NON-NULL duplicate values.
|
a. |
True. |
|
b. |
False. |
18) The relationship “Chaired_by__Chairs” is
|
a. |
1:1 |
|
b. |
1:M |
|
c. |
M:N |
|
d. |
Made of wood. |
19) Based on the relationship between COLLEGE and DEPARTMENT, you would want to set the property of UNIQUE on DEPARTMENT.CollegeName.
|
a. |
True. |
|
b. |
False. |
20) Based on the relationship between STUDENT and DEPARTMENT, you would want to set the property of NULL on STUDENT.DepartmentNameForMajor.
|
a. |
True. |
|
b. |
False. |
21) Consider the following relation in 1NF:
R (A,
B, C, D, E, F, G)
With
the following functional dependencies (FDs):
FD1) A, C, E à
B, D, F, G
FD2) A à
D
FD3) C à
B
FD4) G à
F
Primary Key (PK) = (A, C, E)
a)
(10 points) Decompose this relation into relation(s) that is (are) in 2NF.
Clearly show the resultant relation(s) along with their respective PK and FDs.
In R, FD2 and FD3 violate 2nd
NF, since they are partial dependencies. So taking them out into separate relations,
we get
1) R1 (A, D)
R1FD1: A à D
2) R2 (C, B)
R2FD1: C à B
3) R3 (A, C, E,
F, G)
R3FD1: A, C, E à F, G
R3FD2: G à F
b)
(10 points) Decompose the above 2NF relation(s) into relation(s) that is (are)
in 3NF. Clearly show the resultant relation(s) along with their respective PK
and FDs.
In the above 3 relations,
R3FD2 violates 3rd NF since it is a transitive dependency. Hence, to
make R3 into 3rd NF (R1 and R2 are already in 3rd NF
since they don’t have any transitive dependencies), we get
1) R1 (A, D)
R1FD1: A à D
2) R2 (C, B)
R2FD1: C à B
3) R3 (A, C, E,
G)
R3FD1: A, C, E à G
4) R4 (G, F)
R3FD2: G à F