Examples and Tools for Database Design - Resources

To learn more about database fundamentals, including ER modeling, we recommend the following books:

1. Database Management Systems by Raghu Ramakrishnan and Johannes Gehrke (McGraw-Hill).
   
2. Fundamentals of Database Systems by Ramez Elmasri and Shamkant B. Navathe (Addison-Wesley).
   
3. Database Systems: A Practical Approach to Design, Implementation and Management by Thomas M. Connolly and Carolyn E. Begg (Addison-Wesley).

Exercises

1. When would you use a weak entity?
   
2. Is it better to use entities instead of attributes?
   
3. Alter and extend the music database ER model so that it can store compilations, where a compilation is an album that contains tracks by two or more different artists.
   
4. Create an ER diagram for an online media store using the following requirements:
   
   • There are two types of product: music CDs and video DVDs.
     
   • Customers can buy any number of each product.
     
   • For each CD, store the title, the artist’s name, the label (publisher), and the price. Also store the number, title, and length (in seconds) of each track on the CD.
     
   • For each video DVD, store the title the studio name, and the price.

Tables 4-1 and 4-2 contain some sample data to help you better understand the requirements.

Table 4-1. Video DVDs

Title	Studio	Price
Leon—The Professional	Sony Pictures	$21.99
Chicken Run	Dreamworks Video	$19.99

Table 4-2. Music CDs

Title	Artist	Label	Price
Come Away With Me	Norah Jones	Blue Note Records	$11.99
Feels Like Home	Norah Jones	Blue Note Records	$11.99
The Joshua Tree	U2	Island	$10.99
Brothers in Arms	Dire Straits	Vertigo	$9.99

Table 4-3 contains a sample list of music CD track titles and length in seconds for the CD with the title “Come Away With Me” by the artist Norah Jones.

Table 4-3. Tracks

Number	Name	Length
1	Don't Know Why	186
2	Seven Years	145
3	Cold, Cold Heart	218
4	Feelin' the Same Way	177
5	Come Away with Me	198
6	Shoot the Mone	236
7	Turn Me On	154
8	Lonestar	186
9	I've Got to See You Again	253
10	Painter Song	162
11	One Flight Down	185
12	Nightingale	252
13	The Long Day Is Over	164
14	The Nearness of You	187

Comments On: Examples and Tools for Database Design

· This article is an excerpt from the book "Learning MySQL," published by O'Reilly. We...    · I would have liked some snippets to create the DB with the primary and foreign keys,...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 4 of the book Learning MySQL, written by Seyed M.M. "Saied" Tahaghoghi and Hugh E. Williams (O'Reilly, 2006; ISBN: 0596008643). Check it out today at your favorite bookstore. Buy this book now.