Lab 04 - MySQL Introductions

Preliminaries

• Attendance
• Office Hours and Homework comments
• Turnin expectations

MySQL Browser (part of the GUI Tools download)

1. Open the lab attendance database in the MySQL Browser. You will need to enter the following information:
   • Server: ix.cs.uoregon.edu
   • Port: 3035
   • Username: guest
   • Password: guest
   • Database: cit381lab

   Try some queries, e.g., "select * from student" or "select studentName from student" or "select * from lab"

2. Connect to your own database account in the MySQL Browser. You will need to enter the following information:
   • Server: ix.cs.uoregon.edu
   • Port: 3171
   • Username: [your DuckID]
   • Password: [supplied by your GTF in class]
   • Database: [your DuckID]_db1

   Create a "test" table in your *_db1 database. Add some attributes, keys, etc.

   Drop the "test" table.

Reverse Engineer a MySQL Database into ER Studio

1. Setup the ODBC Connector for ER Studio and point it to the BigHitVideo database under your MySQL account.

2. In ER Studio, do: File > New > Reverse Engineer...

Generating a MySQL Database from ER Studio

1. Model the following entities and their attributes.

   • employee(employee_id, fname, lname, rank, salary, medical package, vacation allowed, sick leave allowed)
   • office(off_num, phone_num, square_feet)
   • car(lic_num, model, year, color)
   • department(dep_num, dep_name)
   • assignment(assignment_id, deadline, deliverable, description)

   The relationships to consider are as follows. If in doubt about whether a non-identifying relationship is mandatory or optional, just make it mandatory to simplify the diagram:

   • An employee always has an office, which may be shared with other employees.
   • An employee may have no car, one car, or several cars.  Cars are not shared with other employees.
   • An employee works in one or more departments. Each department has one or more employees. How do we resolve many-to-many relationships?
   • Each department has a manager, who is an employee. One employee cannot manage more than one department. Use an appropriate role name.
   • Each employee has zero or one mentors. A mentor is another employee. This would be a recursive relationship – Be sure to use an appropriate role name.
   • One employee can work on more than one assignments
2. Verify your solution against this possible solution. (Open the solution in ER Studio and compare it to yours.)
3. Use ER Studio to automatically generate the MySQL database for the above, provided model.

MySQL Administrator (part of the GUI Tools download)

1. Backup your database from the previous exercise and save the file to a secure location.
2. View the file in Notepad, making note of the statements that should be familiar to you from class.
3. Drop the tables from the previous exercise, then restore your database from the backup file.
4. Use the MySQL Query Browser to enter data. Repeat the backup and restore process.
5. LESSON: use the BACKUP feature OFTEN to save your work!