INFO20003 Week 4 Lab
ER Modelling with MySQL Workbench Objectives:
In this lab you will:
• Design an ER model
• Forward engineer the ER design to SQL
• Prepare the schema for next week’s lab
Section 1: Develop an ER model
Choose either Task 1.1 or Task 1.2. Students who are more confident with ER modelling are suggested to complete Task 1.2.
◆ Task 1.1 Build a physical model using MySQL Workbench for the bus company case study that was covered in Tutorial 4. Be sure to choose suitable attribute names and data types, paying attention to the length and precision of data types.
OR:
◆ Task 1.2 Consider the following ‘MusicMaker’ case study. Build a physical ER model using MySQL Workbench. Be sure to choose suitable attribute names and data types, paying attention to the length and precision of data types.
MusicMaker is an app that provides a way to simplify your digital music catalogue. Music tracks are categorised according to genre (for example, classical, pop, or alternative). Users can add their own genres, but each music track can only belong to one genre, and all tracks on an album must be categorised as the one genre. The date a genre was added to the system needs to be stored. For every track, MusicMaker records the title, the track length, the year it was originally released, the artist, and the track number of the track on the album(s) it belongs to. Further, for each track we record the date it was added to the library, the last time the track was played and a rating from 0 to 10. Each track is released by a record label, and for each record label company we record their name, physical address, website, email and phone number. Lastly, for each album we record the album title and the year of release. About each artist we record their name, their website, their Twitter handle and Instagram account. Not all tracks will belong to an album, but each music track must have a genre, record label and artist.
INFO20003 L-Wk4 1 © The University of Melbourne
Section 2: Forward engineering your ER model
The advantage of most ER modelling tools like MySQL Workbench is that they allow database designers to convert between ER models and actual databases. These processes are called “forward engineering” (converting a model to a database) or “reverse engineering” (generating a model based on an existing database).
In this section, you will forward engineer your ER model to a SQL script which will create the tables and relationships in the model.
◆ Task 2.1 From the menu choose File > Export > Forward Engineer SQL CREATE Script:
Figure 1: The Forward Engineer SQL CREATE Script menu option
The Forward Engineer SQL Script window appears:
Figure 2: The SQL Export Options window in the Forward Engineer process
INFO20003 L-Wk4 2 © The University of Melbourne
◆ Task 2.2 Ensure that “Omit schema qualifier in object names” (circled in red in Figure 2) is checked, then click “Next”.
The Filter Objects window appears:
◆ Task 2.3 Click “Next” again:
Figure 3: The Filter Objects window of the Forward Engineer process
Figure 4: The Review SQL Script window of the Forward Engineer process
INFO20003 L-Wk4 3 © The University of Melbourne
◆ Task 2.4 Take some time to read through the SQL script displayed.
Look carefully at the syntax of the CREATE TABLE command. Pay particular attention to the syntax used to set up foreign keys. You may be asked to write CREATE TABLE statements in tests and exams.
◆ Task 2.5 Save the SQL script to a file by clicking the “Save to Other File” button. ◆ Task 2.6 Click Finish to close the wizard.
Section 3: Install this semester’s lab schema
Throughout the rest of the semester, we will be using a “department store” schema to learn SQL and practice writing queries. We will now run the script to create the department store schema in your database.
◆ Task 3.1 Download the info20003labs-depstore.sql script. This file is on the LMS.
◆ Task 3.2 Connect to your preferred MySQL server.
You may connect to either the info20003db.eng.unimelb.edu.au server, or your own MySQL
Server if you installed this on your own computer in Week 1.
Figure 5: Click your saved MySQL Connection to info20003db.eng.unimelb.edu.au or your local server.
This should bring you to the Query window:
INFO20003 L-Wk4 4 © The University of Melbourne
Figure 6: The New Query window
◆Task3.3 Fromthemenu,chooseFile>OpenSQLScriptandselectthesqlscript you downloaded in Task 3.1.
Figure 7: The Open SQL Script menu item
The script will be displayed in a new Query tab.
◆ Task 3.4 If you are connected to your own MySQL Server, remove the double hyphens (“–”) from the lines shown below in figure 8. This uncomments the DROP SCHEMA, CREATE SCHEMA and USE lines in the script.
If you are connected to info20003db.eng.unimelb.edu.au, do not modify these lines. They need to remain commented out.
INFO20003 L-Wk4 5 © The University of Melbourne
◆ Task 3.5 Click the lightning button to run the entire SQL script. Figure 9: The Lightning button runs all SQL in the Query window.
Figure 8: The lab script, showing lines to uncomment for local MySQL Server installs
◆Task3.6 SwitchtotheSchemaspanel(circledinblueinFigure10)andrefreshthe list of databases and tables by clicking the Refresh button (circled in red).
Figure 10: Refreshing the Schemas panel of the MySQL Workbench window
Under labs2019 (on your own MySQL Server) or your username (on the info20003db server), you should see the following eight tables:
INFO20003 L-Wk4 6 © The University of Melbourne
Figure 11: Tables in the lab schema
You can view the contents of a table by clicking the rightmost icon that appears when hovering the mouse over a table name:
◆ Task 3.7 View the contents of some of the tables in the schema to familiarise yourself with its structure.
You have successfully installed the lab schema. Next week, we will begin to use it to explore SQL.
Note: If you do not see the tables in your query output window, ask your tutor for help. End of Lab 4
Figure 12: Viewing the contents of a table
INFO20003 L-Wk4 7 © The University of Melbourne