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