Fall Semester 2002

• The exam is closed-book and closed-notes.
• You have 50 minutes to complete it.
• Write your answers below (no extra paper will be allowed).

Best of luck and do very well!

(The answer appear in blue).

Please complete these statements (or answer them) as best you can.

1. In a query, when rows are grouped one line of output is produced for each group.
   (Quiz 1, problem 18).

2. The WHERE clause will restrict the query results.
   (Quiz 2, problem 3)

3. What clause is used to combine groups of records that share a common characteristic?

   GROUP BY (Quiz 2, problem 24)

4. When rows are grouped, how many lines of output is produced for each group?

   One (Quiz 2, problem 25). This should have helped with Problem 1.

5. In order to restrict which groups will be displayed, which clause is used?

   HAVING (2/26)

6. In order to join tables, which clause are the conditions placed in?

   WHERE (2/27)

7. What is the term when two tables have the same structure, the same number of fields, and their corresponding fields have the same data type? Answer: union compatible (2/28)

8. In Access a relation is a table (3/1).

9. The attributes of an entity become the columns (3/4) in a table.

10. Rows are also called tuples (3/6).

11. In order to select data from more than one table, it will be necessary to join (3/21) the tables.

12. A query that changes data is a(n) update (3/22) query.

13. The type of algebra that manipulates a database is called relational (3/27) algebra.

14. The operation where every row in table A is combined with every row in table B is the (Cartesian) product (3/41).

15. (True or false?) A primary key must consist of a single column. False (5/11)

16. (True or false?) A simpler name for the Cartesian product is the intersection of two tables. False (5/43)

17. The term relationship can be defined as an association between entities(7/36).

18. An example of a one-to-many relationship might be one sales rep has zero or more customers while one customer has exactly precisely one sales rep period (8/7).

19. Creating the structure of a database is a process known as database design.

20. There are several disadvantages to database processing. They include:
    • larger file size
    • greater impact of failure

    (Quiz 8, problem 22 - see also textbook chapter one)

21. In a database system, it is important to control redundancy (8/33); each piece of data should occur only once in the database.

22. A(n) integrity (8/35) constraint is a rule that must be followed by data in the database.

23. What is a DBMS or database management system?

    A program or collection of programs through which users interact with a database. (8/41)

24. (True or false?) A table that contains a repeating group is in first normal form.

    False (9/16)

25. (True or false?) If the primary key of a table contains only a single column, the table is automatically in second normal form.

    True (9/18). It goes without saying that there are no repeating groups but thanks for asking, that was a good point.

26. (True or false?) A valid decomposition of tables is one in which the functional dependence has been split across two different tables.

    False (9/23)

27. (True or false?) A table is in fourth normal form if it is in third normal form and has no multivalued dependencies.

    True (9/24)

28. (True or false?) In a table with columns A, B, and C, there is a multivalued dependency of column B on column A if each value for A is associated with a specific collection of values for B, and further, this collection is independent of any values for C.

    True, 9/25)

29. (True or false?) If a table is in third normal form and has multivalued dependencies, then it is considered to be in fourth normal form.

    False (9/27)

30. A table is in third normal form if it is in second normal form and the only determinants (9/38) it contains are candidate keys.

31. Taking a table (or a collection of tables) and producing a new collection of tables that represents the same information but that is free of update anomalies is known as normalization (10/2).

32. If all columns in a table are functionally dependent on a particular attribute, then this particular attribute is also known as a candidate key (10/11).

33. What is a candidate key?

    Column or set of columns that could serve as a primary key (10/14)

34. What is an alternate key?

    A candidate key not chosen (10/15)

35. What is a foreign key?

    Column or collection of columns whose value is required

    • either to match the value of a primary key in another table or
    • be null

    (p. 374, textbook)

36. What is a nonkey column?

    Not part of the primary key (10/25)

37. A table is in second normal form if it is in first normal form and no nonkey column is dependent on only a portion of the primary key. (10/26)

38. If the primary key of a table is a single column, the table is in which highest normal form already?

    Second (10/28)

39. Define the term partial dependency.

    Dependency on only a portion of the primary key. (10/27)

40. Any column that determines another column is called a determinant (10/29).

41. Which normal form is called the Boyce-Codd Normal Form (BCNF)?

    Third (10/32)

42. In a table with columns A, B, and C, there is a multivalued dependency of B on A if each value for A is associated with a specific collection of values for B, and further, this collection is independent (11/14) of any values for C.

43. If there are two or more repeating groups in a table, you must place each repeating group in a(n) separate (11/17) table.

44. To summarize the requirements for the third normal form, the table must be in second normal form with the only determinants (11/19) being candidate keys.

45. What is the terminology for a value in column A in a table to determine a single value for column B in the same table?

    Answer: functional dependency (11/23)

46. A(n) user (12/1, 14/1) view is the set of requirements that is necessary to support the operations of a particular database user.

47. For each table, you must identify the primary key, any alternate keys, secondary keys, and foreign (14/2) keys.

48. It is through the foreign keys that you can create relationships among tables and enforce certain types of integrity (14/3) constraints in a database .

49. What type of diagram visually represents the structure of a database?

    E-R (Entity-Relationship, 14/7)

50. The concept or property of using a foreign key is called referential integrity (12/16).

51. (True or false?) In a one-to-many relationship, the primary key of the "many" table becomes the foreign key of the "one" table.

    False (12/10)

52. A(n) survey (12/37) form is a good way to obtain the required information from users.

53. The survey should contain information about functional dependencies (12/40) that may exist among the columns.

54. A set of requirements that is necessary to support the operations of a particular database user is known as a user view (12/1).

55. What is the first step needed in designing a user view?

    Represent the user view as a collection of tables (13/26)

56. When an entity exists to implement a relationship, it is called a composite entity (13/26).

57. What does it mean when an entity has a minimum cardinality of zero?

    Not required in the relationship (13/32, also page 195)

58. The design strategy in which specific user requirements are synthesized into a design is known as bottom up (13/34).

59. The design strategy in which a general database design that models the overall enterprise, and that repeatedly refines the model to achieve a design that supports all necessary applications, is known as top-down (13/35).

60. How can you force uniqueness in a table?

    a. designate foreign keys as alternate keys
    b. designate alternate keys as foreign keys
    c. designate primary keys as alternate keys
    d. designate foreign keys as primary keys

    (13/46, also page 205: designate foreign keys as alternate keys)

61. (True or false?) When combining third normal forms, the result does not have to be in third normal form.

    True (13/49, also page 213 in the textbook).