The University of Texas

CS 327e Fall 2003 Test 1 Solution

NAME: __________________________________ EID: __________________

Date: 2003-10-16 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, G)

With the following functional dependencies:

FD1) B à A

FD2) B, D à A, F, C, E, G

FD3) D à C

FD4) F à G, 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 keys and FDs.

For 2NF, remove partial dependencies.

R1 (B, A) FD1) B à A

R2 (D, C) FD3) D à C

R3 (B, D, E, F, G) FD4) F à G, E FD2.1) B, D à F, E, G

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 (B, A) FD1) B à A

R2 (D, C) FD3) D à C

R3 (B, D, F) FD2.2) B, D à F

R4 (F, G, E) FD4) F à G, E