Page 110 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 6: Relational Language and Database Design

7. An entity set attributes that does not have a primary key within them, is termed as a Notes
............................. set.
8. ..........................determine whether two subclasses are allowed to contain the same entity.

9. ............................ determine whether the entities in the subclasses collectively include all
entities in the superclass.
10. .............................. allows us to indicate that a relationship set participates in another
relationship sets.

6.15 Review Questions

1. Amazon.com has decided to reorganize its database. Information about users, books and
sales are stored. Amazon stores as much information as possible on user activity, in order
to analyze and improve its site. Below are few requirements:
(a) A user has a unique id, name, password and a single email address. Amazon contacts
users periodically by email, so it is important to know whether a user’s email has
been bouncing back messages and whether the user is willing to be spammed.
(b) The last date of a user’s visit is stored, so that Amazon can display to the user a list
of items that are new to the site since his last visit.

(c) Books have an ISDN number, title, author’s name, publisher’s name and cost.
(d) For each sale, Amazon stores the date of sale, the items bought, the customer (that
has to be a user), his/her address (street, number, city, state, country, zipcode),
telephone number, and credit-card number.
(e) Users who have bought at least one book, can place comments about every book
(although it is not a book that he has bought at Amazon), by giving a rate to the
book from 1 to 10.
(f) Amazon stores, for each comment, the content of the comment and the percentage of
users who were helped by this comment.
(g) A book can be on a ‘wish-list’ of a user. This is a book that the user would like to buy
at Amazon. Books from a wish-list can be bought for the user by himself, or by a
friend. The friend has also to be a user of Amazon. Amazon wants to keep track of
whether books on a wish-list were bought, and by whom they were bought.

Draw an entity relationship diagram to model the information described above. Remember
to put constraints, key attributes, etc. If you use the ISA relationship, state any covering
and overlap constraints that hold. Make any necessary and logical assumptions. State any
such assumptions clearly.

2. A Bank wants to computerize all of its transactions. It offers the following account types:
Savings Bank (SB), Recurring Deposit (RD), Fixed Deposit (FD)
The Bank also wishes to keep track of loans given to the customers. Identify the entities
and its attributes with all possible relationships. Write the ER diagram and state clearly
the assumptions that you make. The following assumptions may be considered:
(a) A customer can have only one type of account.

(b) Joint accounts are not allowed. Loans can be taken only when the customer has at
least one of the account types.

LOVELY PROFESSIONAL UNIVERSITY 103