The University of Texas

CS 327e spring 2004 Test 1 Solution

NAME: __________________________________ EID: __________________

Date: 2004-02-27 Duration: 60 minutes. 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.

1) The relational model was first proposed in 1970 by E. F. Codd at IBM.

a.	True.
b.	False.

2) A recursive relationship is often found in a binary relationship.

a.	True.
b.	False.

3) Which of the following is a problem of lists that is solved by using a database?

a.	Data inconsistencies.
b.	Problems adding data.
c.	Problems deleting data.
d.	Missing data.
e.	All of the above.

4) Consider 3 tables in a database: T1, T2, and T3. T1 is in 3NF, 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) A properly constructed database can be described as a list that has been broken up into a group of pieces, each of which has a single "theme" or purpose. Breaking the list into these pieces is done using a process called .

a.	Database construction.
b.	Table making.
c.	Componentization.
d.	Normalization.
e.	None of the above.

6) You are given an E-R diagram with two entities, ORDER and CUSTOMER, as shown above, and are asked to draw the relationship between them. If a given customer can place a maximum of many orders and a given order can be placed by exactly one customer, which of the following should be indicated in the relationship symbol?

a.	0:1
b.	1:1
c.	1:N
d.	N:1
e.	M:N

7) Where the extended E-R model uses the term “N:M relationship,” the IDEF1X model uses the term ______________.

a.	relationship
b.	non-identifying connection relationship
c.	identifying connection relationship
d.	non-specific relationship
e.	domain

8) Identifying relationships in IDEF1X are the same as IS-A relationships in extended E-R model.

a.	True.
b.	False.

9) By default, referential integrity constraints ___________ adding a new row to a child table, when the foreign key does not match a primary key values in the parent table.

a.	allow
b.	disallow
c.	compel
d.	Is neutral about
e.	Must be implemented as triggers before

10) When transforming an ID-dependent E-R data model relationship into a relational database design, and the parent entity has a surrogate primary key and the child entity has a data identifier, then the primary key of the child table should be

a.	the parent's surrogate key + the child's data key
b.	the parent's surrogate key + a surrogate key in the child
c.	either a or b will work, and neither way is preferable
d.	either a or b will work, and method a is preferable
e.	either a or b will work, and method b is preferable

11) In representing a recursive relationship in a relational database design

a.	the parent rows and child rows are in different relations
b.	the child rows and the parent rows are in the same table
c.	the child rows are eliminated when they have the same key as the parent rows
d.	the relationship must be reduced to a 1:1 relationship
e.	the relationship must be converted into an IS-A relationship

12) If A à B and B à A, then A and B have a many-to-many attribute relationship.

a.	True.
b.	False.

13) Which of the following is not true about normalization?

a.	produces relations with a single theme
b.	may create referential integrity constraints
c.	reduces the number of functional dependencies in the schema
d.	reduces anomalies
e.	splits a poorly structured relation into 2 or more well-structured relations

14) Which of the following is not a requirement for 1NF?

a.	cells must contain single values
b.	all entries in a column must be of the same kind
c.	no two rows may be identical
d.	rows must be ordered by the value of the primary key
e.	the order of the columns is insignificant

15) If two attributes A and B have a one-to-one attribute relationship in the relation R1 (A, B, C),

then the primary key is .

a.	A
b.	B
c.	A or B
d.	A and B
e.	C

16) Including domain information in a data model makes it possible to .

a.	simultaneously modify the properties of all attributes based on the domain
b.	identify different-named attributes based on the same domain
c.	identify same-named attributes that are actually different
d.	b and c
e.	a, b, and c

17) In a 1:M relationship between 2 entities, which one of the following is true?

a.	The “1” is on the child entity
b.	The foreign key is on the “1” entity
c.	Only the child can have a composite key
d.	The primary key is on the “M” entity
e.	The foreign key is on the “M” entity

18) In IDEF1X modeling, the default non-identifying connection relationship is a 1:1 relationship.

a.	True.
b.	False.

19) There is no term in the extended E-R model for the IDEF1X model term ______________.

a.	relationship
b.	non-identifying connection relationship
c.	identifying connection relationship
d.	non-specific relationship
e.	domain

20) "Non-identifying connection relationships" correspond to "1:1 relationships" and "1:N relationships" in the extended E-R model.

a.	True.
b.	False.

21) Consider the following table in 1NF:

R (A, B, C, D, E, F, G)

With the following functional dependencies:

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

FD2) A à D

FD3) D à E

FD4) B, C à F

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.

R1 (A, D) FD2R1: A à D

R2 (B, C, F) FD4R2: B, C à F

R3 (A, B, C, D, E, G) FD1R3: A, B, C à D, E, G

FD3R3: D à E

Thus, eliminating all partial dependencies.

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 keys and FDs.

R1 (A, D) FD2R1: A à D

R2 (B, C, F) FD4R2: B, C à F

R3 (A, B, C, D, G) FD1R3: A, B, C à D, G

R4 (D, E) FD3R4: D à E

Thus, eliminating all transitive dependencies.