Database Tutorial

You connect as the student user, which puts you in the /home/student directory. Once connected to the Fedora OS, you open a Terminal session by clicking on Activities in the upper right hand corner, and then you type terminal in the search box. When you’re in the Terminal session, use the following command to verify that the apply_mysql_lab1.sql file is correct:

cat Data/cit225/mysql/lab1/apply_mysql_lab1.sql

It should display the following commands:

\. /home/student/Data/cit225/mysql/lib/cleanup_mysql.sql
\. /home/student/Data/cit225/mysql/lib/create_mysql_store_ri.sql
\. /home/student/Data/cit225/mysql/lib/seed_mysql_store_ri.sql

You can run the apply_mysql_lab1.sql to create the tables in the studentdb database, and seed them with data. Assuming you’re in the same /home/student directory, you connect to the studentdb database with the following syntax:

mysql -ustudent -pstudent studentdb

or, more securely:

mysql -ustudent studentdb -p

Having connected to the studentdb database, you can run the following command:

\. /home/student/Data/cit225/mysql/lab1/apply_mysql_lab1.sql

It creates a Video store image and seeds it with some basic data. You can use the show command to see the tables you’ve created, like:

show tables;

It displays:

+---------------------+
| Tables_in_studentdb |
+---------------------+
| address             |
| common_lookup       |
| contact             |
| contacts            |
| current_rental      |
| item                |
| member              |
| rental              |
| rental_item         |
| street_address      |
| system_user         |
| telephone           |
+---------------------+
12 rows in set (0.00 sec)

After successfully creating and seeding the studentdb database, you can run MySQL Workbench by launching it from the search field (recommended). Alternatively, you can open it from a terminal session with the following command. Unfortunately, a command-line launch links the terminal and the MySQL Workbench processes and closing the terminal will close the MySQL Workbench.

mysql-workbench

Here are the instructions for the lab with MySQL Workbench:

1. The first displayed page of MySQL Workbench is the home page (click on it or any of the others to see the full size image). Click the ⊕ symbol to the right of the MySQL Workbench title.

2. After clicking the the ⊕ symbol, it launches the Startup New Connection dialog. Enter a name for your new connection. I recommend you enter MySQLConnection.

3. Click the Test Connection button to see if it works.

4. When you click the Test Connection button, MySQL Workbench prompts you for a password. After entering a password and verifying the connection, click the OK button to test the connection.

5. Click the OK button to continue.

6. Click the gray highlighted MySQLConnection connection icon below the MySQL Connection title to launch the MySQL Workbench application.

7. When you click the gray highlighted MySQLConnection connection icon, MySQL Workbench prompts you for a password. After entering a password and verifying the connection, click the OK button to connect to the MySQL Workbench application.

8. The MySQL Workbench launches in the default view.

9. Click on the Database menu option and then the Reverse Engineering… option, as shown in the illustration.

10. This displays the Set Parameters for Connecting to a DBMS dialog. Click the Stored Connection list of values. Choose the MySQLConnection (if you used my suggestion) or the one you created from the list of values of the Stored Connection element. Click the Next button to continue.

11. Enter the password and click the OK button to connect to the MySQL database.

12. This is an in-progress display, it runs waiting for the password and until the step of the wizard completes.

13. This dialog displays when the MySQL Workbench application connects to the database, retrieves a schema (database) list from the database management system, and checks the common server configuration issues. Click the Next button to move to the next step of the wizard.

14. This Select Schemas to Reverse Engineer dialog displays any available schemas. You check the schemas that you want. Click the Next button to move to the next step of the wizard.

15. Enter the password and click the OK button to connect to the MySQL database to retrieve objects from the database management system.

16. This dialog displays when the MySQL Workbench application retrieves objects from the database. Click the Next button to move to the next step of the wizard.

17. This dialog displays when the MySQL Workbench application retrieves objects from the schemata and checks the result. Click the Next button to move to the next step of the wizard.

18. This dialog displays the tables, views, and routines to import. Click the Execute button to move to the next step of the wizard.

18. This dialog displays the tables, views, and routines to import. Click the Execute button to move to the next step of the wizard.

19. This dialog displays shows the reverse engineering objects and puts them in the object image. Click the Next button to move to the next step of the wizard.

20. This dialog displays a summary of reverse engineering objects. Click the Close button to move to the next step of the wizard.

21. The MySQL Model dialog displays after you complete the reverse engineering process. Click the EER Diagram icon or EER Diagram tab to see visual depiction of the database objects.

22. The EER Diagram is equivalent to the EER Diagram tab. The Navigator displays the tables as blue rectangles. You can scroll through the Canvas Panel to work with the display of tables and views.

23. The next image shows EER Diagram with the tables moved for display purposes. Sometimes there are too many relationship lines, MySQL Workbench lets you split the lines so they don’t clutter the diagram.

24. The next image shows you the Property tab of EER Diagram. You should see that the drawSplit checkbox is checked, which suppresses the bottom-most relationship line from displaying in the EER from display on the Canvas Panel.

You can now save the MySQL Workbench file in the Lab 3 directory.