CS3/586, Introduction to Databases, Fall 2009

Homework 1 Due in class before the lecture begins Thursday Oct 8.

You may do this assignment as an individual or as part of a team of two persons.   If you work in a team, then turn in one assignment paper, with both of your names on the paper. [n] means the question is worth n%. Grammar and spelling matter. Type the text; you may draw the diagrams.

1.[25] Draw a UML ER diagram to keep track of information for an online bookstore with the following characteristics. Include cardinality constraints.

· Each book has a unique publisher, unique ISBN, year, title and price, one or more authors. Each author has a dedication (perhaps empty) in each book.

· Each author has a unique name, URL and address.

· Each publisher has a name, unique address, and phone

· Some authors have a contract with a publisher. These contracts have beginning and end dates. An author is not allowed to have a contract with more than one publisher.

2[25]. Draw a UML ER diagram to keep track of bank systems with the following characteristics. Include cardinality constraints.

· Each bank has a unique code, a name and an address

· Each branch belongs to a bank and has a branch number within that bank and an address.

· Each branch has a manager, who has a ssn and a name. A manager may manage up to 4 branches.

· Each customer has an ssn and an address.

· Ordinary loans, between a customer and a branch, have a duration and an amount.

· Subprime loans, also between a customer and a branch, also have a duration and an amount, but are under the supervision of a manger.

3. [15] Here is a legal instance of an employee relation, with the given attributes:

a. If there is an example of an attribute or set of attributes that you can deduce is not a primary or a candidate key, give one. If not, explain why.

b. If there is an example of an attribute that you can deduce is a primary or candidate key, give one. If not, explain why.

4. [10] Consider the relations Students, Faculty, etc on page 13 of our text. Describe all the foreign key constraints among these relations. Assume the normal meaning of each attribute.

5. [25] Here is an ER diagram. Translate it into a relational schema, preserving all information. In your schema, indicate primary keys and foreign keys.