The topic of database design is a chance to exercise a new way of thinking, to think of the world in terms of ‘entity/relationships’. Chapter 4 of the textbook is a very well written overview of relational database design principles. Initially there are few exercises as the basics of modeling databases is explained. Later in the chapter there are examples that you can use in MySQL Workbench. Here are the Model files that you can open in MySQL Workbench.:
Database design is a discipline of its own, but with Chapter 4, a free copy of MySQL Workbench, and access to MySQL, you get a great opportunity to explore the fundamentals of how to design a relational database.
- Review Chapter 4 pages 109-132
- Read this NetTuts+ Tutorial on MySQL Workbench Data Modeling
(skip the create schema part – we do not have schema permission on SRJC.)
- Quiz 2 – closes April 18
- Project 2: due April 20
- Scenario: You are employed by a wine broker in Sonoma County that sells wine grapes to several local wineries. Your company has several salespeople in a few branch offices in the County. He has been keeping records of his sales transactions on a small pad in the back pocket of his overalls. He hands you the pad and says that he is meeting with Jess Jackson tomorrow about selling the company and he needs these transactions organized in a well design MySQL database. Here is the sales information in PDF and XLS.
- Using the MySQL Workbench Data Modeling tool, design a database structure of at least four related tables for storing this information in a normalized way. Each table should have a primary key and relate to one or more other tables.
- Add several appropriate attributes to two or more of the tables.
- Generate the database structures from the data model
- Enter the data from ‘the pad’ into the database tables. This shouldn’t be too much entry if your database is designed correctly, alot of the data is redundant.
- Send me an email with your MWB data model file attached. In the body of the email include the following information:
- Write an SQL statement that lists all the sales by the Petaluma branch of 2006 vintage or more recent. Past the SQL statement and results in the email.
- Write a brief paragraph about your experiences in the project. What did you have trouble with, what did you learn, what questions do you still have?