Objectives:

• Understand how to create a database
• Understand how to create database tables
• Understand how to add data to a table

1. Locate your partner and introduce yourself:

Abby Alderman	Matt Vibert
Richard Curtin	Alyssa Cockrell	Andrea Titone
Cory DiBenedetto	Robert Perryman
Briggs Elwell	Preston Maddock
Elise Galbo	Ben McDaniel
Brooke Haynes	Whitney Merrill
Nikki Lustig	Ryan Piacentini
Brenna Fox	Shanice Smith

Swap drivers and repeat each step below for each partner. By the end of the lab, both partners should have a _police database with two tables containging data in their accounts.

Step 1: Creating the _police Database

1. The first step in creating your database is to open the MySQL Query Browser tool.
2. Move the mouse into the upper right subwindow labeled Schemata and right click.
3. Select Create Schema. This will pop up a prompt asking for your database name. Remember that your database name must begin with your user name (e.g., hellis2) followed by an underscore, followed by the database name. Enter your name and hit OK.
   
   
   Your schema name should now appear among the list of databases in the window in the lower left of the screen.

Step 2: Creating the _incident Table

1. Click on your database name in the catalogs listed in the upper right list on your screen.
2. Right click on your database name and select Create Table. This should bring up the Table Editor interface that looks like:
   
   
   
   
3. Fill in the table name on the top of the form as incident and provide a comment describing what the table will hold (motor vehicle incidents).You are now ready to add columns.
4. Click on the blue diamond under Column Name. This will allow you to edit the first column. In the Name area, enter incident_id. This will be the first column in our table and will serve as our primary key.
5. In the Data Type section, enter a type of VARCHAR(10). Note that you may also select a type from the pull-down menu.
6. In the Column Options area, make sure that both the Primary Key and the Not Null options are checked. Your screen should now look similar to:
   
   
   
   
7. You are now ready to add the remaining columns. Each column is added by right clicking in the white space under the existing column(s) that you have created. Add the following columns with their corresponding data types (note that you've already added the first column):
   
   

   Column Name	Data Type	Other Characteristics
   incident_id	VARCHAR(10)	NOT NULL
   name	VARCHAR(40)	NOT NULL
   age	TINYINT	UNSIGNED
   address	VARCHAR(40)	NOT NULL
   license_number	VARCHAR(11)	NOT NULL
   infraction_id	VARCHAR(5)	NOT NULL
   incident_date	DATETIME	NOT NULL
   fine	FLOAT(6,2)	NOT NULL

   
   
   
8. When you're done, your Table Editor interface should look something like:
   
   
   
   
9. Before we apply the changes, we need to indicate the underlying type of storage used by the table:
   1. Select the Table Options tab across the top of the table editor.
   2. Select the InnoDB option from the Storage Engine area. You should see something like:
      
      
      
      
   3. Click on the Columns and Indices tab to return to the column area.
10. Click the Apply Changes button on the lower part of the table creation screen to permanently add the columns to the incident table. This will pop up a window showing the SQL code that will be executed in order to create the table. Note that the SQL command to create a table is create table:
    
    
    
    Click the Execute button and you should receive a message indicating that you have successfully created a table.
11. Click OK. Note that you will be returned to a blank Table Editor interface.

Step 3: Adding Data to the incident Table

1. Select your database in the MySQL Query Browser by double clicking on it. You should automatically have access to your database since you are the creator of the database. You should see the incident table appear.
2. To enter data, we will use an insert statement which has the following syntax:

   INSERT INTO table_name
   VALUES (value1, value2,....)
   

   Specifically, for your first row of data type:

   insert into incident
   values('1', 'J. Doe', 30, 'Main St', '123456789', '3', '2006-08-01 10:15:00', 102.00);
   

   This insert statement goes in the query window at the top, center for the query browser interface. Note that the insert statement contains a piece of data for each column in the table. This data must be in the same order as the columns in the table from left to right. If you copy and paste the text above from this web page into the query browser, the copy process may insert hidden characters that will cause the insert to fail. Better to type this command directly into the query browser interface. Once you have entered the insert statement, when you next do a select * from incident you should see a single row containing the data above.
3. Use the insert command above as a template to add at least 10 more rows of data to your incident table.

Step 4: Creating and Populating the vehicle Table