Homework 1
CS422,
Summer 2005
Due: Thursday, July 7
Please upload your solutions using
the online
turnin server.
Note that file
uploading will be disabled automatically after 11:59PM
of the due date, so please turn in your work on time.
[Reading]
- Textbook Chapter 2 and 3.1-3.5.
[Bank Database]
Design a database for a bank which keeps information about customers
and accounts, and of course, which customer owns which account(s). The
information about a customer includes their name, address, phone, and
Social Security Number, and an account has a number, type, and balance.
Note that
- Each customer may have
multiple addresses.
- Each customer address consists of street number, city, state, and
zip code.
- Each customer may have multiple phones at an address., and each phone could be one of
three types: Home, Office, or Mobile.
- Each customer may own multiple accounts.
- Each account may be jointly owned by multiple customers, and
exact one address from each owner is associated with the account, to
which the monthly statement(s) of the account can be delivered.
1. (25pt) Draw the ER diagram for the database design.
2. (15pt) Compare your design with the one shown in Figure 2.14.
Discuss in detail the differences between the two designs, and why
yours
is better.
3. (15pt) Convert the ER diagram to relations.
[Exercises]
4. (15pt) Exercise 3.3.2
5. (15pt) Given relation R(A, B, C,
D, E) with FD's AB -> D,
AC -> E, BC -> D, D -> A, and E -> B.
(a) Compute {A,E}+
(b) Find all keys of R
(c) List a mininal basis for
Find the FD's
which hold for R'(A,
B, D)