ISC329 Database Applications

First Name:______Last Name:______ID:

Midterm

Part I (40%, each question is worth 2 points)

Check the letter of the choice that best completes the statement or answers the question.

  1. A relational database data is organized as (check only one)
  1. elements.
  2. a collection of records.
  3. tables.
  4. a collection of common fields.
  1. A field, or a combination of fields, that has a unique value is a (check only one)
  1. secondary key.
  2. foreign key.
  3. primary key.
  4. alternate key.
  1. A foreign key must (check only one)
  1. be defined in all tables in the database.
  2. be numeric.
  3. match the field value of a primary key in a related table.
  4. be unique.
  1. DBMS performs several important functions that guarantee the integrity and consistency of the data in the database. Which of the following is NOT one of those functions? (check only one)
  1. Storage management.
  2. Data reports.
  3. Security management.
  4. Directory management.
  1. The hierarchical database model depicts a set of ______relationship (check only one)
  1. many to one.
  2. one to many.
  3. one to one.
  4. many to many.
  1. The entity integrity rule requires that (check only one)
  1. all entries are unique.
  2. foreign key values do reference primary key values.
  3. a part of the key may be null.
  4. duplicate object values are allowed.

  1. The term first normal form (1NF) describes the tabular format in which (check only one)
  1. all the key attributes are defined.
  2. there are no repeating groups in the table. Row/column intersection can contain one and only one value, not a set of values.
  3. all attributes are dependent on the primary key.
  4. all of the above.
  1. The referential integrity rule requires that (check only one):
  1. it makes it possible for an attribute to have a corresponding value.
  2. every null foreign key value must reference an existing primary key value.
  3. every non-null foreign key value must reference an existing primary key value.
  4. it makes it possible to delete a row in one table whose primary key does not have a matching foreign key value in another table.
  1. Each table must have (check only one):
  1. A primary key.
  2. A secondary key.
  3. A foreign key.
  4. A logical key.
  1. A Relational operator that yields all possible pairs of rows from two tables is known as a (check only one)
  1. union
  2. product
  3. join
  4. project
  1. An attribute (or combination of attributes) used strictly for data retrieval purposes is called a (check only one)
  1. superkey
  2. candidate key
  3. secondary key
  4. primary key
  1. A Relational operator that allows for the combination of information from two or more tables is known as a (check only one)
  1. union.
  2. joign.
  3. difference.
  4. product.
  1. Cardinality expresses ______number of entity occurrences associated with one occurrences of the related entity (check only one)
  1. an undetermined
  2. the specific.
  3. a programmed.
  4. a pre-determined.
  1. In a conceptual model, the DBMS only needs a(n) ______for the relationship (check only one)
  1. name
  2. table
  3. object
  4. all of the above
  1. The external model is (check only one)
  1. DBMS independent and hardware independent.
  2. DBMS independent and hardware dependent.
  3. DBMS dependent and hardware independent.
  4. DBMS dependent and hardware dependent.
  1. A table is in the Fourth Normal Form (4NF) if (check only one)
  1. all attributes must be dependent on the primary key, and must be independent on each other.
  2. no row can contain two or more multivalued facts about an entity.
  3. all attributes must be dependent on the primary key, and must be dependent on each other.
  4. A and B.
  5. B and C.
  1. A table that is in 2NF and contains no transitive dependencies is said to be in (check only one)
  1. 1NF.
  2. 2NF.
  3. 3NF.
  4. 4NF.
  1. The coding, testing, and debugging are part of the (check only one):
  1. planning phase.
  2. analysis phase.
  3. implementation phase.
  4. design phase.
  1. A design must match relevant user requirements, which are based on the proposed level of information-generating efficiency. A general system requirement could be that (check only one)
  1. the system does not necessarily need to be easy to use.
  2. the system must be fully integrated.
  3. users do not need to access the system correctly.
  4. the system provides security measures.

  1. The term first normal form (1NF) describes the tabular format in which (check only one)
  1. all the key attributes are defined
  2. there are no repeating groups in the table. Row/column intersection can contain one and only one value, not a set of values
  3. all attributes are dependent on the primary key
  4. all of the above

Part II (60%, each question is worth 20 points)

  1. For the following table

Table name: EMPLOYEEDatabase name: CH2_QUESTIONS

propose:

  • A primary key
  • A candidate key (different from the primary key)
  • A foreign key (probable)
  • A super key (different from the primary key)
  • A secondary key
  1. Draw an ERD with Crow’s foot model notation that satisfies the following business rules (note composite entities, weak entities, strong and weak relationships, and cardinalities).

Note: entities to represent are written in capital letters.

An INVOICE is written by a SALESREP. Each sales representative can write many invoices, but each invoice is written by a single sales representative.

The INVOICE is written for a single CUSTOMER. However, each customer can have many invoices.

An INVOICE may include many detail lines (LINE) which describe the PRODUCTs bought by the customer.

An invoice LINE references one PRODUCT, and a PRODUCT may be referenced in one or more invoice LINEs.


  1. Given the following dependency diagram:

Propose a set of relations that is in 3NF, such as Relation1(C1, C2, C3) …

1