TDDE49 Databases and Information Security for Bioinformatics

Setting up the Database for Assignment 1

To set up the Jonson Brothers database for Assignment 1 you need to be in a MySQL client program with which you can interact with the database server. Hence, if you have not done so already, follow the instructions to set up the lab environment.

Step 1: Download the SQL Scripts

You have to download the following two SQL scripts to the computer on which you are running the MySQL client program with which you can interact with the database server.

company_schema.sql -- This script creates the tables of the Jonson Brothers database.
company_data.sql -- This script populates the tables of the Jonson Brothers database with data.

As mentioned before, these script need to be downloaded to the computer on which you are running the MySQL client program (i.e., MySQL Workbench or MySQL Shell). Hence, depending on where you are running this program, you can use one of the following options:

If you are running the MySQL client program on your own computer or on a computer in an SU room at the university, simply download the scripts using the links given above (or open a terminal and use wget as in the last option below).
If you are running the MySQL client program in ThinLinc, open a browser within ThinLinc to download the scripts using the links given above (or open a terminal within ThinLinc and use wget as in the following option).
If you are running the MySQL client program on a university server that you access via SSH, execute the following two commands.
wget https://www.ida.liu.se/~TDDD37/labs/mysqllabs/company_schema.sql
wget https://www.ida.liu.se/~TDDD37/labs/mysqllabs/company_data.sql

Step 2: Execute the SQL Scripts

This step differs depending on which MySQL client program you are using. No matter what, once you have completed this step, the populated Jonson Brothers tables will be available in your database and you do not need to redo this process.

If you use MySQL Workbench

Open the "File" menu in the menubar and select the menu item "Open SQL Script."

Navigate to the file called company_schema.sql that you have downloaded in the previous step, and open this file.

Given that now you are in this file within the main editor window (the big white one), you can execute the script by clicking the icon with the little symbol of a lightning/thunderbolt; as an alternative to clicking this icon, you may use the menu item "Execute (All or Selection)" in the "Query" menu, or you simply use the corresponding keyboard shortcut <Shift>+<Control>+<Return>.

Repeat these three steps for the other file called that you have downloaded in the previous step (company_data.sql).

If you use MySQL Shell

Before you start the MySQL Shell, move the two files that you have downloaded in Step 1 to your working directory from within which you will start the MySQL Shell. After starting the shell (and logging into the database server), input the bold text below at the mysql> prompt:

mysql> source company_schema.sql Query OK, 0 rows affected (0.01 sec) ... Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> source company_data.sql Query OK, 25 rows affected (0.01 sec) Records: 25 Duplicates: 0 Warnings: 0 ... Query OK, 0 rows affected (0.01 sec)

Page responsible: Olaf Hartig
Last updated: 2021-10-27