Hide menu

TDDD37 Database Technology

Computer Labs


This part of the course contains computer labs that are implemented in MySQL on SUN machines.

Signing up for labs

To use a database you require a special database account. You receive this by registering for the course and signing up for the labs via the link in the menu. Sign up latest Nov 11.

Setting up your MYSQL environment for the labs

Please follow the steps to set up your MySQL environment.

Labs

The lab exercises are available from lab 1-2, lab3 - project, and also lab appendix.
  • Lab 1: SQL (scheduled 2 lab sessions)
  • Lab 2: Database design and ER modeling (scheduled 2 lab sessions)
  • Lab 3: Normalization (scheduled 0 lab sessions - do at home)
Other material for the labs

Handing in the labs

See under Examination.

Other Information

  • Tips for the labs.
  • FAQ about the labs.
  • MySQL documentation.
  • In the labs you work with the Jonson Brothers database.
    The relational schema for the database (ie. table definitions) is given here.
    The data stored in the database (ie. rows in tables) is given here.
  • The company database from the book Elmasri, Navathe. "Fundamentals of Database Systems", 5th Edition, figure 5.6 is available here. Note that all table names are prefixed with EN and that some table names have been abbreviated: EMPLOYEE => ENEMP; DEPARTMENT => ENDEPT; DEPT_LOCATIONS => ENDEPT_LOC; PROJECT => ENPROJ

Online material for each lab

  • Lab 1: SQL—queries and views.
    • For all labs, it is not allowed to use graphical interfaces to generate tables and queries. This is an SQL course and you must write the code in plain SQL. Graphical interfaces usually only provide a subset of SQL, so you will end up in trouble later on if you start using them.
    • Differences between SQL in the book and MySQL
  • Lab 2: Database design and ER modelling.
    • Ignore the following in section 2.5 in the lab compendium:
      * No customer account must have a credit above $10,000. The reason is that check conditions are not supported by MySQL and triggers are not yet introduced.
    • Always create InnoDB tables. To create a InnoDB table, you just have to add "engine=InnoDB" after the usual create table statement. That is,
      CREATE TABLE customers (a INT, b CHAR (20)) ENGINE=InnoDB;
      If you do not explicitly create your tables as InnoDB, they will be created as MyISAM by default and you may not be able to create foreign keys. To read more about the different storage engines in mysql, visit this page.
    • If you have problem when you create table
      If errors are not syntax errors , e.g. ERROR 1005: Can't create table './test/fkey_exmpl.frm' (errno: 150), there is a high possibility that the error is because your statement breaks a key constraint, entity integrity constraint or referential integrity constraint .
      For other problems, this page could be useful.
    • If you get error messages that are not about syntax, in most cases it is deserved to check whether your actions break a key constraint, entity integrity constraint or referential integrity constraint .
  • Lab 4: BrianAir database.
    • A simple solution would be to model flights as an entity with attributes such as cities of departure and arrival, day of the year and time of departure, etc. However, this is not acceptable because this table may contain a lot of duplicated information. Use two additional entities instead:
      • Route, which contains all the routes the company flies. A route is characterized by the cities of departure and arrival.
      • Weekly flights, which contains the schedule of flights for any week since BrianAir flies the same flights every week of the year. A weekly flight is characterized by a route, plus a day of the week, plus the time of departure.

    With the above information, you can produce the flights for any day of the year: You have to iterate through the days of the year and use weekly flights for the relevant information. Please, involve the three entities (routes, weekly flights, and flights)  in your ER diagram to faithfully represent the airline’s way of proceeding.

    • Mind the difference between booking or session number and ticket number.
    • If the session A commits its update, the session B still get the old data. Try to commit in session B as well, or use select ... for update;
    • A tutorial for using transactions with InnoDB in mysql is available here. The document for lock and unlock tables is also useful.
      According to the MySQL manual, when you use LOCK TABLES, you must lock all tables that you are going to use in your queries. Because LOCK TABLES will not lock views, if the operation that you are performing uses any views, you must also lock all of the base tables on which those views depend. While the locks obtained with a LOCK TABLES statement are in effect, you cannot access any tables that were not locked by the statement. Also, you cannot use a locked table multiple times in a single query. Use aliases instead, in which case you must obtain a lock for each alias separately. An example follows:
      > mysql> lock table jbemployee write;
      > Query OK, 0 rows affected (0.00 sec)
      > mysql> select * from jbemployee where id in (select id from jbemployee);
      > ERROR 1100 (HY000): Table 'jbemployee' was not locked with LOCK TABLES
      > mysql> lock table jbemployee write, jbemployee as e write;
      > Query OK, 0 rows affected (0.00 sec)
      > mysql> select * from jbemployee where id in (select id from jbemployee as e);
    • Triggers do not work!
      In the current version if MySQL installed on the server you do not have the privilege to create triggers. This means you cannot create and test your triggers (unless you run your own server). But you should still hand in your trigger code.

Page responsible: Fang Wei-Kleiner
Last updated: 2013-10-30