Hide menu

TDDD37 Database Technology

FAQ


1. General
2. Installation
3. Lab 1
4. Lab 2
5. Lab 4

1. General

1.1. 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.

1.2. How do I know what lab group I am in? What is my lab group ID?
You can either:
  • Log in to webreg and check out the name of the webreg group you are in and add the subgroup number to it. For example, if your webreg group is DB1A and your subgroup number is 2, then the lab group ID will be DB1A2. This is what you put on the lab report wrapper.
  • Run the script /home/TDDD12/bin/group in a shell window (terminal), which will print the lab group ID on the screen (you can also give the script another login ID as a parameter, if you want to check your friend's lab group ID, example: /home/TDDD12/bin/group stust001).


1.3. 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 lab assistant for your lab group.

2. Installation

2.1. Nothing happens in mysql!
All commands and SQL queries must be terminated with a semicolon and by pressing RETURN.

2.2. 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.

2.3. Logon denied. What now?
Always use 
  mysql -h db-und.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.

2.4. I am using phpadmin to query the database and have problems with defining procedures that are longer than one line; the following example: 
DELIMITER $$ CREATE PROCEDURE HelloWorld() BEGIN SELECT 'Hello World'; END
$$ DELIMITER ;

This gives the following error: 

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'DELIMITER $$
CREATE PROCEDURE HelloWorld()
BEGIN
SELECT 'Hello World'' at line 1

This is expected since DELIMITER is a mysql command-line function and not part of the language, but there must be a way to execute multi-line procedures via phpadmin?

No, this is not possible, because the database server only accepts connections from "localhost", so you can not use any of the MySQL tools, including phpadmin, from other machines to connect to the database.

3. Lab 1

3.1. 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)


3.2. I want to take a look at the company database creation script.
The scripts are available as Company schema (for creating the tables) and Company data (for filling the tables with data)

3.3. 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.

3.4. I want to spool output from mysql to a file.
Use the tee and notee command as follows: 
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


3.5. 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).

3.6. 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 ...." For 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)


3.7. How do I write a comment in SQL?
You can use the following formats: 
-- This is a comment on one line

# This is a comment on one line

/* This is a comment possibly spanning several lines */

Here you see a file with the three possible types of comments and how to execute them 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. |
+----------------------------+


4. Lab 2

4.1. How do I transform the ER diagram into a good relational schema as 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.

4.2. 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.

4.3. 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, first 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);

Then, the dropping does not work: 

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

To solve this, 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 |
+-------+---------------+

Then, drop the foreign key constraint: 

mysql> alter table c2 drop foreign key c2_ibfk_1;
... and then drop the other table, not the one you modified in the previous statement: 
mysql> drop table c1;

How to drop the second table: 

mysql> drop table c2;


4.4. How do I change the name of a table?
Use the following command: 
alter table <table_name_old> rename to <table_name_new>;


4.5. 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.

4.6. How do I drop a column?
Use the following command: 
alter table <table> drop <col_name>


4.7. Is there any software that we can use to create our ER diagrams?
I recommend using Dia, which can be used to draw several different types of diagrams, including ER diagrams. It has been installed at IDA and you make it available by doing module initadd misc/student-after (log out and then log in again). The start-up command is dia.

4.8. When we do ALTER TABLE we get this strange "error 150 can't create table"; what could be the problem?
Make sure that you have used "ENGINE = InnoDB" when you created the tables that you are trying to alter (see the CREATE command).

5. Lab 4

5.1. We get this strange error but we cannot see what's wrong.
Check that you are not using any reserved words, such as weekday or dayofweek. See also the mysql manual regarding other reserved words (search for "reserved").


Page responsible: Olaf Hartig
Last updated: 2014-10-28