Questions for this assignment in here: Assignment Discussion: From ER to Database
The assignment for this week is to take the bookstore database design and implement it in mysql by using phpmyadmin. See Modeling IV: Implementing Data Models in SQL for details on how to log in.
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.
The design that you should implement is: BooksDatabaseDesign2013.pdf which we did together in class.
Create the database with the name <yourusername>_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.
Remember that when you are off campus access you have to 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.
You don't need to designate foreign key columns as such, the naming conventions that we are using are sufficient. 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, just get the column names right and you've specified the foreign key/primary key relationships.