Lab 8. Database Design Using ER Diagram
CS122, Summer 2008

Please write down your solutions on paper and turn it in at the end of the lab.

1. Analyze the the sample email below and design a database for storing emails.

From: John Smith <jm@spammer.net>
Reply-to: nobody <nobody@spammer.net>
To: John Doe <jd@hotmail.com>
Doe, Jane <janedoe@hotmail.com>
CC: jm@yahoo.com
Date: 09:01PM 7/24/2005
Subject: CONGRATULATIONS

Dear Winner:
You have won the International Lottery. Please send us your name, birthday,
credit card number, and other personal information you want to share.

(a) (10pt) Draw an ER diagram for this database.

(b) (5pt) Convert your ER diagram to tables.

Please show me your solution for this exercise before you proceed to the next one.

2. Design a database to store the information about files and folders as shown in the figure bellow:

[file.jpg]

(a) (10pt) Draw an ER diagram for your design and convert it to table(s).

HINT: check out the SalePeople table in the Lyric Music database and the employees table in the Human Resource database and see how hierachical information is handled.

(b) (5pt) Using the table(s) you created, write SQL statements to perform the following operations: