Test 1

The University of Texas

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