Home‎ > ‎CIS 54‎ > ‎

Lab 2 - Your First Schema

In this lab you will create a schema and some tables two different ways. 

Introduction

MySQL Workbench allows you to do many things with little or no knowledge of SQL. That's very helpful for beginners but, as this class progresses, I hope that, like me, you'll learn to crave the power of SQL and not depend on the GUI. In this lab we'll create a schema with two tables and add some data to it.  

Chapter Questions 

Answer the following questions:
  1. What is a modification problem. Name two types of modification problem. 
  2. What does SQL stand for? 
  3. Define the term metadata and give some examples of metadata. 

Using the GUI

In this part you will create a new schema. Your new schema name must begin with your login ID. For example: 

    matmic154_lab2 

Use the Create Schema button shown below: 


That will bring up the create schema tab. Name your schema and apply as shown:


MySQL Workbench will show you what SQL is about to execute and ask you to confirm:


Click Apply. Now you should see your schema on the left hand side of your window: 


Double click the name of your schema to select it as the active one. Then select the "New Table" button. This will bring up the new table tab. Your new table should have the following properties: 
  • Name: actor
  • Columns:
    • id (type INT, PK, NN)
    • name (type VARCHAR(45), NN)
    • birthday (type DATE, NN)
    • hometown (type VARCHAR(45), NN)
You table should look like this: 


Select apply to create your table and you will see the SQL command that creates your table:


Click "Apply". Next you will add values to the table. Pull open the lab2 schema. When you hover over the actor table you will see a set of icons. The right-most icon looks like a table. Select it and it will bring up the table editor as shown below:


Add the following people to the actor table as shown in the picture above: 

 ID Name  Birthday  Hometown 
 1 Robert De Niro  1943-08-17 New York, NY
 2 Meryl Streep 1949-06-22 Summit, NJ

When you're done click Apply and the SQL statements will be shown to you. Select Apply again. You have now populated the actors table. 

Creating Tables Using SQL

Now you will create tables using SQL. Select the "Query 1" tab in MySQL Workbench. This tab allows you to enter SQL statements. You can load and save SQL files as well as run them step-by-step. In the tab enter the following SQL statements:

CREATE TABLE oscar (year INT NOT NULL, category VARCHAR(45) NOT NULL, movie VARCHAR(45), actor INT NOT NULL); 

INSERT INTO oscar VALUES (1981, 'Best Actor', 'Raging Bull', 1);
INSERT INTO oscar VALUES (1975, 'Best Suporting Actor', 'The Godfather: Part II', 1);
INSERT INTO oscar VALUES (2012, 'Best Actress', 'The Iron Lady', 2);
INSERT INTO oscar VALUES (1983, 'Best Actress', 'Sophie\'s Choice', 2);
INSERT INTO oscar VALUES (1980, 'Best Supporting Actress', 'Kramer vs. Kramer', 2);

With the above SQL statements in the editor press the button with the lightning icon to execute them all. It's much faster to create and edit tables when you know SQL. Verify that your table contains the data. It must be turned in with your assignment. 

Export your Schema 

MySQL workbench can export your schema so you can load it into another DBMS. To export a schema select the server menu item at the top of the window:

    Server -> Data Export

That will bring up the export dialog as shown:


Save your schema in a file called Lab2Export.sql and submit your file for credit.

Turn In
  1. The answers to the chapter questions.
  2. Your Lab2Export.sql file 
Submit your homework on canvas.

Grading

  • 10 points for correct answers to the chapter questions
  • 10 points for submitting your exported database
Comments