TDDD37 Database Technology

Project: BrianAir

In the project you will develop a database for an application and go through the whole database development process. The task of the project is found in lab3 - project.

Handing in the project

See under Examination.

Some hints

• 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.
  
• A tutorial for using transactions with InnoDB in mysql is available here. The document for lock and unlock tables is also useful.
  According to what the 5.0 manual said- 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);