ISMG DUE IN 1 HOUR

ERDs should be drawn with entities, relationships, attributes and primary key of each entity, and cardinality of each relationship. Avoid using foreign keys as attributes of an entity if possible. Note that primary key of an entity is not always specifically given. Since each entity must have a primary key, you must give each entity a primary key even if what the PK may be is not specifically spelled out in the data requirements.

Web ERD Drawing Tools :

A good tool to use is ERDPlus.com. You can get a free account. It’s very easy to draw ERD using that tool. After you have finished drawing each problem, screen capture it and copy-paste it to a Word document. Submit one document that contains answers to all problems. (From ERDPlus.com, you can click on the little triangle under Diagram button and choose “Export Image.” Please do not submit five individual PNG files; Put all your drawings in one document).

Problem 1: Book Store

The database should record sales and returns for a bookstore. Draw an ERD that represent information requirements as stated or implied by the following descriptions:

· The following information about each book should be stored: ISBN, cover type (hard cover or paperback), current price, # of pages, publication year, category, #of copies in store. ISBNs are unique, that is, two books with the same ISBN are identical in every way (as far as the database is concerned.)

· A customer can purchase multiple books (multiple copies of the same book or different books). (Even though database needs to store quantity of each ISBN sold, we’ll leave this requirement out for this assignment.) Database should store date/time of purchase, total dollar amount, payment method, and amount paid.

· When a customer returns one or more books purchased, a record is created for each physical book returned. Date/time of the return should be recorded.

· Of course, a customer can only return a book that was previously purchased from the bookstore.

Problem 2: Hair Salon

Rocky Mountain Hair Salon provides hair services and sells hair products. Your ERD should represent information requirements as described below:

· The database records information about each customer (name, address, email, and phone). If the customer participates in the loyalty program, loyalty card number is stored. Note that two customers can have the same phone number (e.g., home phone number of people from the same family) but they can’t have the same loyalty number.

· The database needs to store a list of hair services that this salon can provide. For each service, the current price is stored. All customers receiving a service pay the current price of the service.

· A customer can receive multiple services during each session. Database needs to store what services were provided for the customer.

· A customer can also purchase hair products at the end of a session, which is also recorded in the database, together with session dollar total and payment method.

· Information about each product should be stored (a unique product number, description, type of product, and price.)

· If the customer participates in the loyalty program, a total number of points earned is calculated for each session and added to the current total number of points for this customer.

1