Reading: Chapters 9 and 10

Master's Students, for Monday November 11: Hand in title, abstract, and outline for your protocol paper.

Download employees_db-full-1.0.6.tar.bz2 from Launchpad.

On any Unix system (including Mac OS X):

1. Make sure the MySQL server is running.

2. cd to the directory containing the downloaded file.

3. On the command line, expand the downloaded file:

tar -xjf employees_db-full-1.0.6.tar.bz2 

4. cd to the directory employees_db:

cd employees_db

5. Import data into your mysql instance:

mysql -u root -p -t < employees.sql

If you have set a root password, use the options -u root -p too.

There are also instructions here.

Before we do any more with creating and administering databases, we're going to learn how to access them. This will make it easier to learn administration because, in fact, databases store all their administrative information in tables!

See the database diagram.

You can get the same diagram from MySQL Workbench:

1. Open MySQL Workbench.

2. Select Database>Reverse Engineer from the Application Menu Bar.

3. Select the employees schema on the Select Schemas screen.

4. Read each screen to get an idea of what is happening, and continue through to the last screen. Now you should have an EER Diagram tab in the Workbench and it should show a database diagram with a departments table, an employees table, a dept_emp table, a dept_manager table, a salaries table, and a titles table.

Look at each table and its attributes. What kind of entity or relationship do you think it represents? Why?

Do you think there are organizations that might need a different data model for departments, employees, managers, salaries, and titles?

Look at the different notations for entities and relationships.

Looking at an entire table:

1. Find the employees database under SCHEMAS. Click the arrow to display the categories of objects in the database (Tables, Views, Stored Procedures, Functions). Note that only Tables contains any objects.
2. Click the arrow next to Tables to display the tables. Then click the arrow next to employees to display the kinds of objects related to the employees table (there are columns, indexes, foreign keys, and triggers). All tables have columns, most will have an index at least on the primary key, and some will have foreign keys and triggers.
3. Select the employees table and right-click on it. Choose Select Rows - Limit 1000 to see 1000 rows of the table.
4. Alternatively, select File>New Query Tab in the MySQL Workbench menu bar and in the resulting MySQL frame, type

   SELECT * FROM employees;
   

   Note that you MUST terminate the statement with a semi-colon. The Workbench will treat statements not terminated with semi-colons as errors; the command line monitor will just wait to respond until you have typed a semi-colon. In either case, the result shows up in a Result Set frame (below the SQL Query Tab). You can also filter the results using the Result Set Filter, or using a WHERE clause:

   SELECT * FROM employees WHERE first_name="Georgi";
   

   Note that you get different results using the filter and the WHERE clause. Why do you think that is?
5. A simple SELECT statement has the following format:

   SELECT <fields>
   FROM <relations>
   WHERE <condition>;
   

   Try some more simple selection queries:

   1. List all male employees.
   2. List employees born on 9/2/1953.
   3. List employees born after 9/2/1953.
6. Look at the dept_emp to see some foreign keys. What are the names of the foreign key columns? Can you make any sense of them? Select the tab Object Info below the SCHEMAS section of the navigation bar. Note that the Target tells you the table and column that the foreign key refers to.
7. Note that we can combine information from multiple tables.

   Suppose, for example, we want to know the salary for the employee Margareta Markovitch.

   Select * from employees, salaries 
   where employees.emp_no=salaries.emp_no and 
         employees.first_name="Margareta" and 
         employees.last_name="Markovitch";
   

   Note that you have to prefix the column names with the table names, at least where there would be ambiguity (you don't need "employees" in front of first_name or last_name). Alternatively, we can give aliases to the table names to shorten the query:

   Select * from employees e, salaries s
   where e.emp_no=s.emp_no and 
         e.first_name="Margareta" and 
         e.last_name="Markovitch";
   

   In some cases, the same relation appears twice in a query, in different roles (e.g., employee as manager and employee as employee). Then we must use aliases. For example, list employees that are working or have worked for Margareta Markovitch:

   Select e.emp_no, m.emp_no, e.first_name, e.last_name 
   from employees e, employees m, dept_manager dm, dept_emp de 
   where m.emp_no=dm.emp_no and 
         e.emp_no=de.emp_no and 
         de.dept_no=dm.dept_no and 
         m.first_name="Margareta" and 
         m.last_name="Markovitch"; 
   

   Try some more complex queries:

   1. List employees making more than 100,000.
   2. Find the manager of Margareta Markovitch.
   3. List the employees that are currently working for manager Margareta Markovitch.
   4. List employees making more than Margareta Markovitch.

   Review Questions

   Review queries in general -- from homework, above examples, other lectures.

   How would you create a database from a model? A model from a database?