From ER to Database

The assignment for this week is to take the bookstore database design and implement it in mysql by using phpmyadmin.  This means you'll log in, create the database, create the tables with relevant fields and enter a few rows of illustrative data, showing correct use of the relevant foreign keys. For your own use you should complete the database design on paper by adding the table sketch.

The design that you should implement is: BooksDatabaseDesign2013.pdf

Create the database with the name _bookstore_assignment

Once it is created and you are satisfied, write any notes, questions or feedback into the text entry field for the assignment and submit this assignment.  I will look on the database server to see your actual database.

Off campus access via http://vpn.utexas.edu  then the phpmyadmin interface will be available at 

http://holden.ischool.utexas.edu/pma.

Notes: Please ensure that you enter sample data for at least one each of: supplier, author, editor and a purchase by a person.  

In this database design:

- people who have only purchased books have addresses but no BookRole,

- suppliers have both an address and a BookRole,

- and authors/editors/translators etc have BookRoles but no addresses.  

This setup is handled by the optionality between Person to BookRole and Person to AddressRecord.  The relationship between a customer and the books they have bought is handled (and can be queried) through purchases.

Of foreign keys:

I've gotten a few questions about handling foreign keys for the assignment.  Yes, there is a way (in MySQL and PhpMyAdmin) to designate a column as a foreign key and to indicate which primary key it refers to.  But, you don't have to do this.

In fact we won't do this during the semester, it's actually a feature that is rarely used anymore and gets handled by the programming framework.

Having said that hopefully you can see that the relationships can be seen through our conventions in naming.  If you have a column named "color_id" you can look over and see that there's a column named "id" in the colors table; then you know that "color_id" is the foreign key and that the numbers in it refer to the rows in the color table identified by their id.

So:  colors.id object.color_id,  people.id address_record.person_id

So, just get the column names right and you've specified the foreign key/primary key relationships.