For Monday, November 4: The database design for your Web site.
Turn in:

• an entity-relationship model.
• a list of tables and their fields and types.
• the list of Web pages and what tables each page will use.

For Monday, November 4: Class handout.
For Wednesday, November 6: The Getting Started section in the MySQL Workbench Reference Manual.

Other references:

• Video
• Entity-relationship model in Wikipedia

There's one more step to complete the process.

The official instructions

For Mac OS X, here is the short version:

• Stop the server, using System Preferences or the command

sudo /usr/local/mysql/support-files/mysql.server stop

• Start the server, using the command

sudo /usr/local/mysql/support-files/mysql.server start --skip-grant-tables

• Start a command-line client with the command

mysql

and then in the client, use the database commands

mysql> UPDATE mysql.user SET Password=PASSWORD('') WHERE User='root';
mysql> FLUSH PRIVILEGES;

Root now has no password. If you want to set a different password, put the password between the single quotes in PASSWORD('').

I just tested the above instructions, and there was a problem with stopping my server -- not just from the command line but also from System Preferences. What I finally did was uncheck the box in System Preferences for MySQL that says "Automatically Start MySQL on Startup" and I restarted my computer. Yuk.

Anyway, it will usually work as written. Good luck!

Here's a summary of what I have figured out about the Workbench window:

1. There's an unlabeled (on my computer) Navigation Pane on the left. This includes a Management section, an Instance section, and a Schemas section.
   1. The Management section has several subsections; the only one I've really used so far is "Users and Privileges." You manage the accounts from this one. Be careful that you remember the root password from localhost! That's the one that you should use to manage everything.
   2. The Instance section: I haven't used it.
   3. The Schemas section: This contains the schemas for databases stored in this server. This is what we'll be working with the most, because this is where you go to create, edit, and search tables.

   Configuring users

   Click on the "Users and Privileges" label. You will see a table of "Users" versus "from Host". The hosts will range from localhost to 127.0.0.1 to hostname.local to %. The last is a regular expression that matches anything of any length (% here instead of *).

   For each user, there's a tab that lets you configure the login; another for account limits; another for administrative roles; and the last for schema privileges.

   There are also buttons at the bottom to add, drop and modify accounts.

   Configuring a local user

   Let's add a new user named "nancy" (or whatever name you like). This will be a user that is allowed to look at and modify the test database, and nothing else.

   To start, click on "Add Account." Enter the user name, use Standard authentication, and "Limit connectivity" to localhost (that means that "nancy" can only connect from this host, not remotely). Enter a password that you'll find easy to remember, and then click on the "Apply" button at the bottom right.

   Looking at Account Limits (the next tab after "Login"), we see that all are set to 0. This doesn't mean that the user is allowed no queries, or updates, etc. It means that there are no limits. A non-zero value creates a limit.

   Under administrative roles, there's quite a list. Root needs these (and has them all -- click on the root account to verify this). Your new account doesn't have any, but you could provide some if you like.

   You will want to add some schema privileges so your new user can do something. Click on "Schema Privileges" and then "Add Entry...". You are given several choices; pick whichever you like, and select operations from the bottom part of this screen for the user to be allowed.

   Configuring a remote user

   Before you configure a remote user, let's see what remote access is like.

   Let's try accessing a server on a different host. So that a user can connect to your server from another host, you need to configure a user that can connect from another server. I have created a user called "remote" on "lucy.local". You should be able to create a new connection to this server with the following parameters:

   Connection name: Whatever you like
   Connection method: Standard (TCP/IP)
   Hostname: lucy.local
   Port: 3306
   Username: remote
   Password: N3tw0rk

   To configure a remote user, you need to provide a login name, a host or hosts that they will be allowed to connect from, and a password. Click on "Add Account" and configure those values; I suggest % for hosts so that anyone in the room can connect to your computer, but you're also welcome to try restricting it. Pick a computer name from the list in your Finder under "Shared." and add ".local" to the end.

   You will also need to grant them some privileges; schema privileges are a good way to go, with administrative privileges someone could accidentally (or otherwise) make your life difficult.

   Accessing a remote server

   Once you have configured your remote user, invite someone to access it. You'll have to give them your server name, their login name, and the password. I think that's all they'll need.

   Review questions

   1. Define:
      1. Entity
      2. Relationship
      3. One-to-many relationship
      4. Mony-to-many relationship
   2. Design an entity-relationship model for a school database that provides for scheduling sections of classes, along with teachers, times, and rooms, and for students registering in classes. Include entities, relationships and their cardinalities, and attributes. Indicate keys for each entity and relationship.
   3. Given the entity-relationship model below, what are the tables you should define? Keys? Foreign keys? 
   4. Given the following home screen:  What would you do to:
      1. Create a new connection?
      2. Create a new model?
      3. Connect to the employees database on GI231Lab_17 as nancy?
      4. Connect to the bookorama database on GI231Lab_17 as _mysql?
      5. Connect to the employees database on localhost as root?
      6. Connect to the bookorama database on Mary as root?
      7. Look at the CompBioTools ER Diagram?
   5. Given the following MySQL Workbench screen: 
      1. What would you do to add a new user?
      2. What would you do to get a new SQL Query window?
      3. How would you create a new table?
   6. Given the following MySQL Workbench screen: 
      1. Who is permitted to login from a remote host?
      2. How do you determine what privileges a user has?
      3. What tabs will you use to create a new user with select, update, insert, and delete access to a given database?