CIS 523 Spring 2000

3/8/00 Midterm Exam 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.

Sample University Database contents are provided for reference.

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

Multiple Choice

(2 points each)

1. Which of the following is true of hierarchical database organization?

A). is inefficient even for answering queries that match the hierarchy.

B) sometimes requires forcing non-hierarchical data into a hierarchy.

C). All data can easily and naturally fit into a hierarchical format.

D) All of the above.

E) None of the above.

2. Which of the following is true of relational database theory:

A) There can be values that are in a domain for an attribute that do not currently appear in the data for the relation.

B) In formal relational database theory, tuples are ordered, since linked lists are ordered.

C) All of the above.

D) None of the above.

3. Which of the following is true of the levels of the general database architecture?

A) The "external level" shows the way data is viewed by individual users.

B) The "external level" shows a logical view of the whole database

C) All of the above.

D) None of the above.

True/False - If false, explain why.

(3 points each)

4. The entity integrity rule requires that at least one attribute that is part of the primary key must have an actual value (not null or empty (or default)) for all tuples in a relation.

5. There is no need for the relationships between entities to be represented in a database, only the entities need to be represented.

6. If the effect of a referential integrity violation on a delete is RESTRICT, using our sample university database, the deletion of a section with students enrolled will be rejected and stopped.

7. How violations of the referential integrity rule should be handled depends on the specific data and business situations involved; there is no response which is always the correct response in all situations.

8. In entity-relationship modeling, in turning an entity-relationship diagram into a database design, each many to many relationship becomes a table.

9. In the relational algebra, TIMES or cartesian product produces a result with all attributes that are in either relation and all possible combinations of the tuples.

10. Proper normalization does not lose any data from the database.

11. Relational algebra set difference is commutative. I.e. Relation A – Relation B produces the same result as Relation B – Relation A.

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

(3 points each)

12. A(n) _________ is a field whose value uniquely identifies a record. No two records can have the same value for this field.

13. A database manager deals with data that is considered ________, that is, it is going to be around for a while.

14. Due to the theoretical background behind relational databases, records are also called ________.

15. An attribute’s value for a particular tuple may be unknown, or sometimes the attribute is not applicable for a particular tuple. In these cases, the attribute can have _______ as its value in the database for that tuple.

16. In order to be ________, two relations must have the same number of attributes, with the same attribute names, and same domains. This is necessary for several Relational Algebra operations.

17. In the relational algebra, ________ produces a result relation from one relation, containing only the tuples that meet some condition.

18. A(n) ________ exists if there are functional dependencies such that PK ® X, X ® Y, and PK ® Y. That latter attribute is functionally dependent on the primary key indirectly through other functional dependencies.

Short Answer

(points as shown)

(4 points)

19. If we are an auto dealership and we are keeping track of the salespeople and the specific cars that they sell (NOT the models), what is the cardinality of the relationship between salesperson and car? Explain!

(5 points)

20. How can a designer know if an entity’s participation in a relationship is total or not?

(5 points)

21. If a database schema is as follows:

Politician#, Last First Position State Party District Salary NetWorth

With Primary Key: Politician #

And Functional Dependency (besides those based on primary key): Position ® Salary

What Update Anomaly(s) could there be?

(5 points)

22. Given the below data, what can you say regarding the possibility of the following functional dependency existing?

Contribution --> Invitation

Name Party Contribution Amount Invitation

------ ----- ------------ ------- ----------

Smith Dem Y $ 1000 Y

Tanaka Dem Y $100000 Y

Harris Dem N $ 0 N

Bush Rep N $ 0 N

Problems

(points as shown)

(10 points)

23. Looking at the following E/R diagram, specify what this tells you about LOAN in the modeled world – don’t stop at reporting the technical terms!

(20 points)

24. BusinessTrips R Us has the following information in a file. First, what normal form (if any) is this data in? Then, reorganize the data so that it is in 3NF and show a few records in each resulting table. The functional dependencies are provided in case intuition does not provide sufficient guidance.

ID Name Dept StartYear TripID FromCity ToCity DepartureDate ReturnDate Account Item# Amount

11 Jones 12H 1985 1234 Boston Chicago 12/30/98 01/23/99 XA111 1 375

11 Jones 12H 1985 1234 Boston Chicago 12/30/98 01/23/99 XA111 2 67

11 Jones 12H 1985 1234 Boston Chicago 12/30/98 01/23/99 XA111 3 15

11 Jones 12H 1985 1234 Boston Chicago 12/30/98 01/23/99 XA111 4 67

11 Jones 12H 1985 2156 Boston New York 05/30/99 06/03/99 XA111 1 179

11 Jones 12H 1985 2156 Boston New York 05/30/99 06/03/99 XA111 2 99

11 Jones 12H 1985 2156 Boston New York 05/30/99 06/03/99 XA111 3 23

11 Jones 12H 1985 2156 Boston New York 05/30/99 06/03/99 XA111 4 99

11 Jones 12H 1985 2156 Boston New York 05/30/99 06/03/99 XA111 5 34

11 Jones 12H 1985 2156 Boston New York 05/30/99 06/03/99 XA111 6 99

11 Jones 12H 1985 3125 Chicago Houston 11/23/99 12/03/99 YB222 1 279

11 Jones 12H 1985 3125 Chicago Houston 11/23/99 12/03/99 YB222 2 279

11 Jones 12H 1985 3125 Chicago Houston 11/23/99 12/03/99 YB222 3 279

11 Jones 12H 1985 3125 Chicago Houston 11/23/99 12/03/99 YB222 4 279

11 Jones 12H 1985 3125 Chicago Houston 11/23/99 12/03/99 YB222 5 279

13 Smith 13A 1998 3021 Boston Houston 11/19/99 11/23/99 XA111 1 378

13 Smith 13A 1998 3021 Boston Houston 11/19/99 11/23/99 XA111 2 68

13 Smith 13A 1998 3021 Boston Houston 11/19/99 11/23/99 XA111 3 32

13 Smith 13A 1998 3021 Boston Houston 11/19/99 11/23/99 XA111 4 68

13 Smith 13A 1998 3021 Boston Houston 11/19/99 11/23/99 XA111 5 19

13 Smith 13A 1998 3141 Boston New York 12/10/99 12/13/99 ZC333 1 169

13 Smith 13A 1998 3141 Boston New York 12/10/99 12/13/99 ZC333 2 75

13 Smith 13A 1998 3141 Boston New York 12/10/99 12/13/99 ZC333 3 124

13 Smith 13A 1998 3141 Boston New York 12/10/99 12/13/99 ZC333 4 67

13 Smith 13A 1998 3141 Boston New York 12/10/99 12/13/99 ZC333 5 124

13 Smith 13A 1998 3141 Boston New York 12/10/99 12/13/99 ZC333 6 56

14 Wilson 12H 1991 3354 Boston Baltimore 12/17/99 12/19/99 WD444 1 199

14 Wilson 12H 1991 3354 Boston Baltimore 12/17/99 12/19/99 WD444 2 79

14 Wilson 12H 1991 3354 Boston Baltimore 12/17/99 12/19/99 WD444 3 37

ID -> Name

ID -> Dept

ID -> StartYear

TripID -> ID

TripID -> DepartureDate

TripID -> ReturnDate

TripID -> FromCity

TripID -> ToCity

TripID -> Account

TripID,Account,Item# -> Amount

PK: TripID, Account, Item#