Homework is due at class-time on Wednesday (9/22). Submit either a hardcopy or email it to me.

We finished talking about the basics of the relational database model (beginning of chapter 2, chapter 4 (Integrity Rules), chapter 5 (functional dependencies and keys).

For the following homework you will need Microsoft Access, you can find it in the labs, or get a free license and software at https://my.cdm.depaul.edu/resources/msdnaa.asp. Microsoft offers introductory tutorials on Access 2007.

1. [Henry Books, database schema, 15pt] Draw a diagram with the relational schemas and foreign keys for the Henry Books database (Author, Wrote, Book, Publisher, Branch, Inventory).

Your diagram should be similar to the diagrams we saw in class; for another example, see the  company miniworld (2nd page has the diagram; it's for the company database which we will see next week). In particular:

• list all attributes of each relation
• underline the primary key of each relation (every relation must have a primary key)
• draw an arrow from each foreign key to the primary key it refers to (there should be 5 foreign keys in your diagram)

2.  [Database Schema, 15pt] You are designing a small customer database. We'll simplify a bit and say you for every customer you want only their first name, last name, address and any credit card (store number, type, expiration date) they care to use.

1. Come up with relational schemas that allow you to store this information. Make sure you can store an unlimited number of credit cards for each customer. Your relational schema needs to include the table designs, primary keys should be underlined and foreign keys should have arrows pointing at the primary keys they reference.
2. Give an example of what a table implementing your relational schema could look like. (One or two records are fine. Never use real data.)

3. [Integrity Constraints, 15pt] For each of the following types of constrains, give an example of what type of action would violate it. Choose your example for the Henry Books database.

1. Primary Key Constraint
2. Domain Constraint
3. Entity Integrity Constraint
4. Referential Integrity Constraint
5. An (imaginary) functional dependency Price -> Paperback in the Book table.

Hint: Example: the functional dependency Publisher Name → City would be violated by inserting records (PGNY, Penguin, New York) and (PGLN, Penguin, London) into the Publisher table.

Marcus Schaefer
Last updated: September 15th, 2010.