Topics
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.
Assignments
- 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?
well, I’ve understood some of this lesson and got some data into the four tables I’ve created, but I am confused on a couple of important points.
1. How to actually get the tables to reference one another (also: I must be missing data as I don’t see how it would work with what I’ve input).
2. MySQL Workbench is confusing me as to where I’m actually working and the location of my tables – I seem to have two sets of some.
Bob – Tables reference each other through foreign key relationships. These relationships are defined in the data modeler using the four relationship tools. The relationships are generated into foreign keys when the tables are created in the forward engineering tool. Defining the relationships between tables is a key part of data modeling.
MySQL Workbench is pretty complex with how all the screens inter-relate sometimes. I will post a set of resources that may help us use MySQL Workbench more effectively in the next day or so.
I found that the MySQL Workbench Data Modeling tool generates bad script that gets errors and cannot create tables on student. It was putting parentheses in the wrong places and generating indexes that didn’t make sense. Maybe the default schema isn’t the one we should use. What is a schema?
I ended up rewriting the generated script to get past the errors so my MWB file won’t match my end database.
Anna – I had similar experiences with the forward engineering from MySQL Workbench Data Modeling. It isn’t a perfect tool. Did you see the video I posted about generating the tables without the schema?
What is a database schema – In MySQL, the terms database and schema are basically identical. You can create and drop schemas or databases and ‘use’ them to create or access data objects. You can refer to data objects in specific schemas using dot notation – ie select * from winebroker.customer;
The accounts we are using on student.santarosa.edu do not have create schema or create database privileges, so we are using the default database – with the name of our FTP login username.
Jay – I can see the relationships and the foreign keys, but still don’t understand how to get the data into their respective tables so that the links work. I have tried to use unique IDs for each of the four tables and build links based upon those unique keys. I think I did this correctly, but then when trying to apply data to the model, I realize something is amiss.
My schedule means I don’t get to SQL homework until late in the week. When I read the dates above, I thought I was getting a break. The line “Quiz 2 closes 4/18″ is not really true. When I tried to take the quiz today (4/18) the system told me it was only open 4/11-4/17. Maybe in the future you could say closes BEFORE 4/18.
Quiz 2 is reopened until midnight 4/19.
Hi Mike,
I had the same assumption as you about quiz 2. I try to take it at 12am in 4/18/11 and it said the quiz was closed.
Jay,
thanks for letting us have until 4/19 midnight to take quiz 2.
Anyone know where the link to the video that Jay mentioned the the previous posting? I am having issue as Anna and it looks like Jay said something about “Did you see the video I posted about generating the tables without the schema?”
The post is here: http://www.jayperetz.com/myblog/?p=726
I think I messed up my winebroker tables. Somehow with all the relationship going on, I have somehow created two primary keys per table. Now I have ran the script in my DB on the JC server and cannot delete the tables. The “alter table branch drop primary key;” script does not work because I have two primary keys. Any idea how to clean this mess?
You can’t have more than one primary key per table, but you can have more than one column of the table make up your primary key. I logged in and dropped your sales table – you should be able to drop the others and try again. Right click on the table icon and choose drop table from the context menu.
Well, I definitely learned quite a bit more about inner joins ^_^