TDDB77 Databaser och bioinformatik

FAQ about the labs

1. What is a good way of doing the labs?
2. Nothing happens in mysql!
3. When I type mysql, it doesn't seem to work at all!
4. Logon denied: What now?
5. How can I take a look at the definition of a table?
6. I want to take a look at the company database creation scripts.
7. How do I reload my company database?
8. I want to spool output from mysql to a file.
9. How do I add an ER entity to my database?
10. How to calculate the difference between two numerical columns?
11. How do I write a comment in an SQL command file?
12. I declared a foreign key from one table to a second table. Now I can't drop the second table!
13. I declared a foreign key from one table to a second table, and another foreign key from the second table to the first table. Now I can't drop the tables!
14. How do I change the name of a table?
15. How do I change the name of a column?
16. How do I drop a column?
17. I have this other problem...

What is a good way of doing the labs?

You should prepare for the worst: you may have to reload your entire database, for example if you delete some or all of the data or the tables by mistake, or if you accidentally delete your lab report. All of these things have been known to happen.

Therefore, you should write all your SQL commands in text files (usually one for each lab), and load them into mysql either with copy'n'paste or with the source command. Then you have all your commands easily available if you have to give them again! Try not to type in commands directly into mysql, except perhaps when experimenting.

Nothing happens in mysql!

All commands and SQL queries must be terminated with a semicolon and by pressing RETURN.

When I type mysql, it doesn't seem to work at all!

Maybe you have not set up your environment yet. Go to the lab settings section and follow the instructions there.

Logon denied. What now?

Always use
  mysql -h db.ida.liu.se -p <your login name>
to logon.

If your logon is still denied, you might not have registered for the course yet. MySQL account creation is done automatically when you register for a course that uses MySQL. The password is not necessarily the same as the password of your account. Check with IDA's helpdesk, not the lab assistant, if you have forgotten your password.

How can I take a look at the definition of a table?

Run the following for e.g. describing your employee table:

mysql> desc jbemployee;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | NO   | PRI | 0       |       |
| name      | varchar(20) | YES  |     | NULL    |       |
| salary    | int(11)     | YES  |     | NULL    |       |
| manager   | int(11)     | YES  | MUL | NULL    |       |
| birthyear | int(11)     | YES  |     | NULL    |       |
| startyear | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

You can even get access to the full table description which is useful for seeing the names of the foreign key constraints:

mysql> show create table jbemployee;
+------------+-----------------------+
| Table      | Create Table
+------------+-----------------------+
| jbemployee | CREATE TABLE `jbemployee` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(20) default NULL,
  `salary` int(11) default NULL,
  `manager` int(11) default NULL,
  `birthyear` int(11) default NULL,
  `startyear` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_emp_mgr` (`manager`),
  CONSTRAINT `fk_emp_mgr` FOREIGN KEY (`manager`)
    REFERENCES `jbemployee` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+------------------------+
1 row in set (0.00 sec)

I want to take a look at the company database creation script.

Company schema (for creating the tables)
Company data (for filling the tables with data)

How do I reload my company database?

Before reloading, drop all tables that YOU might have created that refer to the original tables, specifically the MANAGER and ACCOUNT table.  You will probably have circular dependencies and will have to drop constraints before you can drop certain tables. Look at the issue of resolving circular dependencies in the FAQ below.
Once you have removed your own tables that refer to the Jonson Brothers database, you can follow the description for setting up your account. The original tables of the Jonson Brothers database are dropped at the beginning of the company schema script.

I want to spool output from mysql to a file.

Use the tee and notee command.

mysql> tee out.txt
Logging to file 'out.txt'
mysql> select * from jbemployee;
+------+--------------------+--------+---------+-----------+-----------+
| id   | name               | salary | manager | birthyear | startyear |
+------+--------------------+--------+---------+-----------+-----------+
|   10 | Ross, Stanley      |  15908 |     199 |      1927 |      1945 |
...
| 4901 | Bailey, Chas M.    |   8377 |      32 |      1956 |      1975 |
| 5119 | Bono, Sonny        |  13621 |      55 |      1939 |      1963 |
| 5219 | Schwarz, Jason B.  |  13374 |      33 |      1944 |      1959 |
+------+--------------------+--------+---------+-----------+-----------+
25 rows in set (0.00 sec)

mysql> notee

How do I add an ER entity to my database?

You don't. At least not until you have translated it into a relational schema, i. e. tables and attributes.
An ER entity is an abstraction that should sometimes be represented by a table, but sometimes just as one or several attributes in a table.
An ER relationsship (which is not the same thing as a relation in the relational data model, which is a table) is sometimes represented using reference attributes between tables, but sometimes you need to represent it with an extra table (for N:M-relations and sometimes if the relation has attributes).

How to transform the ER diagram into a good relational schema is covered in the course book, and in the lectures.
Take a look at ELMASRI, NAVATHE Fundamentals of Database Systems, chapter 7 Relational Database Design Using ER-to-Relational Mapping.

How do you calculate the difference between two attributes and then display it?

You can directly write arithmetic expressions in your select statements, e.g. "select x, y - w ...." or "select x, y - w as yw_diff ...."

Example with literals, you could use table column names as well.

mysql> select 35-20;
+-------+
| 35-20 |
+-------+
|    15 |
+-------+
1 row in set (0.00 sec)

mysql> select 35-20 as Result;
+--------+
| Result |
+--------+
|     15 |
+--------+
1 row in set (0.00 sec)

How do I write a comment in SQL?

--, # or /* ... */

Here you see a file with the three possible types of comments and its execution in sqplus:

mina8 <336> cat >comments.sql
-- This is a comment for the rest of the line.
# This is another way of marking one line as a comment.
/* This is for several lines
   of comments. Just as in your favourite
   programming language. */
select 'That was not so difficult.' STUDENT_RESPONSE;

mina8 <337> mysql -h db.ida.liu.se -p <your login name> -e "source comments.sql"
Enter password:
+----------------------------+
| STUDENT_RESPONSE           |
+----------------------------+
| That was not so difficult. |
+----------------------------+

I declared a foreign key from one table to a second table. Now I can't drop the second table!

You can't remove a table when a foreign key from another table refers to it. Therefore, you must remove the tables in the right order. In this case, remove first table first.

I declared a foreign key from one table to a second table, and another foreign key from the second table to the first table. Now I can't drop the tables!

You have created a circular dependency with the foreign keys and must remove at least one of the foreign keys you have declared. Here is a scenario:

Creating the circular tables:
mysql> create table c1 (id int primary key) ENGINE=InnoDB;

mysql> create table c2 (id int primary key,
       foreign key (id) references c1(id)) ENGINE=InnoDB;

mysql> insert into c1 values (100);

mysql> insert into c2 values (100);

mysql> alter table c1 add constraint
       fk_c2_id foreign key (id) references c2(id);

The dropping does not work:
mysql> drop table c2;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Get the name of one of the foreign key constraints:
mysql> show create table c2;
+-------+---------------
| Table | Create Table |
+-------+--------------+
| c2    | CREATE TABLE `c2` (
  `id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  CONSTRAINT `c2_ibfk_1`<-- A name that was assigned to the constraint automatically
      FOREIGN KEY (`id`) REFERENCES `c1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------+
Drop the foreign key constraint:
mysql> alter table c2 drop foreign key c2_ibfk_1;

Drop the other table, not the one you modified in the previous statement:
mysql> drop table c1;

Drop the second table:
mysql> drop table c2;

How do I change the name of a table?

alter table <table_name_old> rename to <table_name_new>;

How do I change the name of a column in a table?

If the column you want to rename is not referred in any foreign key constraints you can use

alter table <table> change <old_col_name> <new_col_name> <datatype>

However, if the column is referred in forein key constraints you first have to disable all those constraint, rename the column and add the constraints again.

How do I drop a column?

alter table <table> drop <col_name>

I have this other problem...

If you can't find a solution to your problem here, in the course material, or in the documentation, ask the assistant for your lab group.

Sidansvarig: Jose M. Peña
Senast uppdaterad: 2007-12-06