Hide menu

732A54 Big Data Analytics

Relational Databases Lab


Assignment

Purpose (Overview)

SQL is both DDL (Data Definition Language) and DML (Data Manipulation Language). DDL means the language can be used to create, modify, and delete databases, tables, and views. DML means you can insert, modify, and delete data in tables as well as perform more or less complex searches over data. Some preparation is required (see below).

Preparations

Use the SQL tutorial offered by W3Schools.

NOTE: The instructions below apply to doing the labs on LiU's Linux computers (e.g. a computer in a SU room or via Thinlinc). Instructions for working on your own database server are available here.

Account on the database server:
LiU provides a database server called MariaDB (which is the same type as MySQL) where students in the course get a database account and a database. Both have the same name as your LiU-ID, but the database account has its own password.

  1. Check whether you have received an email with account information (the password) for the database manager. The email has the subject "MySQL database created" and is sent out a few days after you register for the course. If you have taken an IDA course where you previously used a database, you will not receive a new email and should use the same credentials.
  2. If you cannot find any email but know you are registered for the course, or have forgotten the password to an old database account, you can request a new password here. The new password will be sent to your LiU email.

Starting the database client:
To complete the tasks in this lab, you will use the text-based database client "mysql" to manage the database on the server.

  1. First open a terminal window (Terminal).
  2. Enter the following command in the terminal window:
    mysql -h mariadb.edu.liu.se -u <LiU-ID> -p
    Replace <LiU-ID> with your own LiU-ID.
    This command starts the mysql client, connects it to IDA's database server for educational use (mariadb.edu.liu.se), and initiates login with your account name. You will then be prompted to enter your database password:
    Enter password
    Note that nothing at all is shown in the terminal when you enter the password (not even dots), and the cursor does not move. If login succeeds, you will see the following output:
    Welcome to the MySQL monitor. Commands end with ; or \g.
    ...
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    mysql>
    This means the mysql client is ready to accept your commands.
  3. You need to start by specifying which database you want to work with. Remember that you only have one, and it has the same name as your LiU-ID:
    mysql> use <LiU-ID>
After that, you can enter your SQL commands. A tip is to keep a text file (preferably the one to be submitted) open in a text editor where you edit commands that are then copied into the mysql client. Note that Ctrl-C exits the mysql client in some types of terminal windows. Therefore copy using the mouse when you want to copy from the client to the text file (in some terminal windows Ctrl-Shift-C seems to work for copying). Also note that all commands must end with a semicolon. If you end up seeing:
->
it means it is waiting for a semicolon. If you want to use autocomplete, i.e. pressing <tab> completes a started command or name (if it is unique), in the same way as when typing Linux/Unix commands, you can try giving the mysql command REHASH;.

Installing a database to work with:
The command SOURCE filename makes the database client read (and execute) SQL commands stored in a file called filename. If filename does not contain a path, the file is assumed to be in the folder from which the mysql client was started. The commands in the file are executed in the order they appear, and any output from them is printed in the terminal window.

The compressed file Johnson_Brothers.zip contains two files, company_schema.sql and company_data.sql, which contain definitions and data for The Johnson Brothers Database.

  1. Download the zip file and unpack it using the following command in a terminal window (remember which folder you store the files in):
    unzip Johnson_Brothers.zip
    This will extract the two files company_schema.sql and company_data.sql into the current folder. You can also specify a destination folder:
    unzip Johnson_Brothers.zip -d /path/to/destination/
    Inspect the extracted files with a text editor. What do they contain?
  2. Install the Johnson Brothers database using the SOURCE command as described above. The order in which the files are loaded matters, you must load the schema first, then the data:
    mysql> SOURCE company_schema.sql;
    mysql> SOURCE company_data.sql;
    If everything goes well, a lot of output of the following type will be printed:
    Query OK, 0 rows affected (0.01 sec)
    Records: 0, Duplicates: 0 Warnings: 0
     If something goes wrong, you will get error messages, so carefully check the output to make sure there were no errors. If you get multiple error messages, the files may have been corrupted during download. So try downloading the zip file again.
  3. Explore the database using the commands SHOW TABLES and SHOW CREATE TABLE tablename. Draw a relational schema with all relations (tables) and their attributes. Mark foreign keys (reference attributes) as arrows between the relations. You do not need to submit the drawings. The purpose is for you to understand how the data is modeled in this database.

Tasks

For each task below, you should in the submission file "SQL_tasks.txt" state the SQL command used to perform the task and paste in the table that was the result. Clearly mark the boundary between tasks. Some tasks also contain a question that must be answered, write the answer after the task's table.

All tasks except number 13 must be solved with a single SQL command (nested commands, such as a SELECT inside a SELECT, count as one).

  1. List the names of all departments (the column "name") for all rows in the table "jbdept".
  2. List all information about all employees, i.e. all columns for all rows in the table "jbemployee".
  3. Which parts are not in stock in the table "jbparts"? I.e. for which parts is "qoh" (Quantity On Hand) zero?
  4. Which employees ("jbemployee") have a salary between 9000 and 10000?
  5. Calculate how old each employee was when he/she started working at the company.
    Tip: Simple mathematical calculations can be written directly in the list of columns to be displayed (after SELECT) and are then performed for each row.
  6. Which employees have a "son-name", i.e. a last name ending in "-son"?
    Tip: You can assume that "name" has the form "lastname, firstname".
  7. Construct a nested query (sub-query) to find out which items (the table "jbitem") have been delivered by the supplier named "Fisher-Price". The ID number of the supplier must not appear as a constant in the query. The result list should contain the ID and name of the items.
  8. Now construct a non-nested query for the same result list as above. The ID number must still not appear as a constant.
  9. Find the name and color of all parts ("jbparts") that are heavier than a "Card reader". Use a nested query. The weight of the card reader must not appear as a constant in the query.
  10. Ask the same question as above without nesting. The weight must still not appear as a constant.
  11. What is the average weight of black parts (in the table "jbparts")?
    Tip: What you need is called an "aggregate function".
  12. For each supplier in Massachusetts ("Mass"), find the total weight of everything the supplier has delivered. The answer should be a list with the names of the suppliers and the total delivered weight for each supplier.
    Tip: The number of delivered parts is found in "jbsupply". This query becomes very complicated if you try to nest it.
  13. Create a completely new table with the same structure as "jbitem" and fill it with all items (from "jbitem") that cost less than the average price. You may use more than one command here if you want, but there is one command that suffices. If you use it, what difference is there in the schema between the old "jbitem" and the new one you create?
  14. Create a view of all items in "jbitem" that cost less than the average price. What is the difference between this view and the table in the previous task?


Page responsible: Huanyu Li
Last updated: 2026-03-31