Laborating from home If you would like to work on your own computer, you are free to do so. In this case we recommend you to use a remote access service to connect to the university computers.
A second (more complicated) alternative can be to set up your own database server. For this we recommend MySQL which is free to use although you will need to set up an oracle account. Note, however, that you can not connect directly to our MySQL-server from outside LiU and, hence, it is not enough to just download the client. Notice also that we do not provide support for this option.
Page responsible: Olaf Hartig
Last updated: 2008-03-10
Department of Computer and Information Science
Linköping University
581 83 LINKÖPING
Tel: +46 13 28 10 00
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:
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 7Relational 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;
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.
Page responsible: Olaf Hartig
Last updated: 2008-03-10
Department of Computer and Information Science
Linköping University
581 83 LINKÖPING
Tel: +46 13 28 10 00
Swedish web site
