CSC 240                                                                Spring 2007

02/23/07                                                                               Midterm Exam #1                                               Test Form A

Name:

Instructions:

    Answer all questions in the answer sheet provided. On Multiple Choice, choose the ONE BEST ANSWER.

    Remember to put the letter of your test form on the top of your answer sheet.

    Hand in Test, Answer Sheet, and Help Sheets, all with your name.

Multiple Choice

(4 points each)

1. How many different normal forms are there?

A) one

B) two

C) three

D) None of the above.

2. Which of the following is true about hierarchical databases?

A) have trouble representing one-to-many relationships

B) have trouble representing many-to-many relationships

C) provide ad hoc query capabilities

D) all of the above

E) none of the above

3. Which of the following is true about many to many relationships?

A) rarely occur in real life

B) should result in an extra table in the database to represent the relationship

C) a foreign key should be placed in BOTH sides of the relationship

D) all of the above

E) none of the above

4. Which of the following is an example of a business rule?

A) if a foreign key is not null, then it must reference an existing primary key in another table

B) a car must have at least one person registered as the owner

C) no attribute that is part of the primary key may have a null value in any record in the database

D) all of the above

E) none of the above

True/False 

(3 points each)

5. In a relational database, the natural links between entities are represented by including both entities in the same table.  

6. Entity-Relationship modeling is a waste of time since a DB design cannot be confirmed correct until after all normalization has been done.

7. One problem with traditional file systems is that to answer unexpected user questions, specialized program code (typically in a 3^rd Generation Language) had to be written.

8. Business Rules are rules that are established by the DB designer and given to the rest of the organization to ensure that data does not get inconsistent.

9. Since violation of 2NF involves partial dependencies, if a table primary key is a single attribute (not a composite), then if the table is in 1NF it must automatically be in 2NF.

10. A database design in first normal form causes several of the same problems as a non-database file system.

Completion (fill in the blank) (mostly key terms (not all of which are a single word))

(4 points each)

11. A(n) ________ is an attribute whose value uniquely identifies a record. No two records can have the same value for this attribute.

12. ________ is data about data. It is used to make a DBMS more flexible than regular file systems.

13. A problem with pre-database approaches, ________ means that programs and data are tightly coupled, so that changes in data causes changes in programs, and vice-versa.

14. ________ attributes are attributes for which a record may have multiple values at once. When these exist, they present an extra challenge in designing for a relational database.

15. A(N) ________ functional dependency is one in which an attribute is functionally dependent on part of the primary key. The whole primary key is not needed in order to identify the dependent value.  A table including this type of dependency may be in 1NF, but it is not in 2NF.

Short Answer

(points as shown)

                (7 points)

16. Suppose that you are modeling for SEPTA, and that SEPTA wants to keep track of BUS DRIVERS and all of the ROUTES that the bus drivers can be assigned to (they know the route etc).  What do you think the connectivity between BUS DRIVER and ROUTE is? Briefly explain why.  

(5 points)

17. In keeping track of books in a library, we have identified COPY (a single copy of a book) as being in a Many to One relationship to BOOK. In designing tables, where should the foreign key linking the two tables be put? Explain.

(6 points)

18. 18. Suppose you have been assigned to a project, and must develop an E/R diagram for the project. Suppose that you have identified entity (types) of FIASCOL and MEDIUS. If you do not know the appropriate connectivity for the relationship (let’s call the relationship IS ABOVE – a FIASCOL IS ABOVE a MEDIUS, a MEDIUS is BELOW a FIASCOL) between these, what questions would you ask in order to determine the connectivity?

(9 points)

19. Suppose that we have created a database for Philadelphia Park Race Track and there is a table for HORSE and for OWNER, with a foreign key OwnerID from HORSE to OWNER. Assume the data is currently correct. Briefly explain changes to the DB that could cause a referential integrity error to occur (at least 3 kinds of changes).

(6 points)

20. Our analysts believe that there is a functional dependency  Party ® Invitation

Given the data (known to be correct) collected by our clients, shown below, what can you say about the following regarding the possibility of a functional dependency existing?

Name	Party	Contribution	Amount	Invitation
Smith	Dem	Y	$1000	Y
Tanaka	Dem	Y	$100,000	Y
Harris	Dem	N	$0	N
Bush	Rep	N	$0	N

Problems

(points as shown)

(13 points)

21. Looking at the following E/R diagram, specify what this tells you about HOUSE in the modeled world – don’t stop at reporting the technical terms! – go on to their meaning in the world – as if you were reviewing the diagram with an end-user (a “lay” person). Center your discussion on HOUSE and the things it is related to!