Quiz 1

CMP 420: Database Systems
Lehman College, City University of New York
Spring 2003

Reading: Chapters 1 and sections 2.1-2.2.

Suggested Problems

• Exercise 2.1.1 and 2.1.2.
• Exercise 2.1.9.
• Exercise 2.2.1.
• Exercise 2.2.3.
• Exercises 2.2.6 and 2.2.7.
• (From Ullman's DB course, Fall 2000): In this exercise, you will model the database of an ``eTailer,'' such as Amazon. Here are the kinds of information that the eTailer wishes to maintain:
  • Their suppliers, including supplier names, addresses, and a logo to display.
  • The items made by each supplier (e.g., HP), including the type of item (e.g., printer), the model number (e.g., 693C), and the retail price. Note that different suppliers may use the same model number for different items, even if those items are of the same type and price. However, one supplier will not use the same model number for two different items.
  • Customers, including their name, address, and email.
  • Credit cards, including the company (e.g., Visa), number, and expiration date. Each customer may have several credit cards on file, but each credit card belongs to only one customer.
  • Orders. Each order has a unique ID, has a single customer who placed the order. One of the customer's credit cards was used to pay for the order, and the order must tell which. A customer can place several orders and use different credit cards for each. An order is for some set of items, and their is an associated quantity for each. There are several ways to represent orders, but try to use a design that does not allow an order to be placed for a customer who does not own the credit card used to pay for the order.
  Give an E/R design for this database. Briefly explain your reasoning, including the intuitive meaning of any relationships you use and any entity sets you use other than suppliers, items, customers, and orders.