[team33] Re: code

  • From: Markus Nilsson <i99marni@xxxxxxxxxxxxx>
  • To: team33@xxxxxxxxxxxxx
  • Date: Mon, 15 Nov 2004 13:28:48 +0100

Ok, some good news!
We have been able to create the database!

Basically we had to change some syntax things. The only thing that
affects other documents, like er diagrams and such, is:

Had to change the attribute name from table to tableseat (in the table
seattype) since table is a predefined thing in sql.


We can use foreign keys in the mysql version which we used for lab 4.

The database we created corresponds to the updated design, ie it
includes Restaurant and Name for Station etc, which were missing prior
to our meeting this morning.


When you want to create a database without typing in everything by
hand in mysql, you do like this:

home/TDDB62/start_mysql < sqltables20041115_1.txt

However this requires that the database doesn't exist already. Delete
an existing table named test with: "drop DATABASE test;"

The last lines in the file are just my experimenting...

See you,

Markus






-- Attached file included as plaintext by Ecartis --
-- File: sqltables20041115_1.txt

CREATE DATABASE test;
USE test; 
create table station (id int auto_increment not null, name varchar(25) not 
null, restaurant bool not null, primary key (id));
create table arc (id int auto_increment not null, distance int not null, 
departurestation int not null, arrivalstation int not null, primary key(id), 
foreign key (departurestation) references station (id), foreign key 
(arrivalstation) references station (id)) type = innodb;
create table taxi (id int auto_increment not null, distance int not null, price 
float not null, departurestation int not null, arrivalstation int not null, 
primary key(id), foreign key (departurestation) references station (id), 
foreign key (arrivalstation) references station (id)) type = innodb;
create table customer ( id int auto_increment not null, ccnumber varchar(16) 
not null, cctype varchar(16) not null, ccexpdate varchar(4) not null, ccname 
varchar(25) not null, primary key (id));

create table ticket ( id int auto_increment not null, ticketid int not n
ull, customerid int not null, primary key (id), foreign key (customerid) 
references customer (id)) type = innodb;

create table traveller ( id int auto_increment not null, category int not null, 
bookingid int not null, primary key (id), foreign key (bookingid) references 
booking (id)) type = innodb;

create table train ( id int auto_increment not null, bistro bool not null, 
primary key (id));

create table cartype (id int auto_increment not null, name varchar(32) not 
null, primary key(id));

create table car (number int auto_increment not null, cartypeid int not null, 
trainid int not null, classofcar int not null, primary key (number), foreign 
key (trainid) references train (id), foreign key (cartypeid) references cartype 
(id)) type = innodb;

create table link (id int auto_increment not null, departuretime datetime not 
null, arrivaltime datetime not null, arcid int not null, primary key (id), 
foreign key (arcid) references arc (id)) type = innodb;


create table seattype (id int auto_
increment not null, window bool not null, direction bool not null, tableseat 
bool not null, power bool not null, cartypeid int not null, primary key (id), 
foreign key (cartypeid) references cartype (id)) type = innodb;

create table seatres ( id int auto_increment not null, available bool not null, 
linkid int not null, bookingid int not null, seattypeid int not null, primary 
key (id), foreign key (linkid) references link (id), foreign key (bookingid) 
references booking (id), foreign key (seattypeid) references seattype (id)) 
type = innodb;

create table comment (id int auto_increment not null, code int not null, name 
int not null, trainid int not null, primary key (id), foreign key (trainid) 
references train (id)) type = innodb;

insert into station values (1, 'Stockholm', 0);
insert into station values (2, 'Linkoping', 0);
insert into station values (3, 'Gothenburg', 1);
insert into arc values (1, 300, 1, 2);
insert into arc values (2, 250, 2, 3);
delete from station where name = 'Sto
ckholm';
insert into train values (1, 1);
insert into comment values (1, 'the comment code', 'the comment name', 1);








Other related posts: