Ohjelmistotuotanto kesä 2000 - SQLtrainer Helsingin yliopisto - Tietojenkäsittelytieteen laitos © OHTU SQLtr, kesä 2000

drop table pscourse;
drop table psanswer;
drop table psstudent;
drop table pstask;
drop table psstate;
drop table pstopic;

create table psstudent(
   sid varchar(10) not null,
   lname varchar(48) not null,
   fname varchar(48),
   last_visit date not null,
   HETU VARCHAR(11),
   SS VARCHAR(12),
   stnumber integer,
   primary key (sid)
);


create table pscourse(
   year integer,
   term char,
   startdate date,
   enddate date
);

create table psstate (
  sid varchar(10),
  task_id integer,
  lasttry integer,
  state integer,
primary key (sid,task_id));
rem psstate täytyy initialisoida tehtävien lisäämisen jälkeen
rem viemällä laskureille alkuarvot 0,0

create table pstopic (
   tno integer not null,
   ttitle_fi varchar(60),
   ttitle_en varchar(60),
   tasks integer not null,
   credits_upto date not null,
   primary key (tno) 
);

create table pstask (
   task_id integer not null,
   title_fi varchar(60) not null,
   title_en varchar(60),
   task_text_fi varchar(500),
   task_text_en varchar(500),
   ok_rows integer,
   nonunique integer,
   ok_cols integer,
   ok_checkable varchar(32),
  ok_checkvalue integer,
   ordered char,
   neededcols varchar(120),
   eqpairs varchar(120), 
   tno integer,
   included varchar(120),
   col_max integer,
   needed_tabs varchar(120),
   needless_tabs varchar(120),
   primary key (task_id),
   foreign key (tno) references pstopic(tno)
);


create table psanswer (
  sid varchar(10) not null,
  task_id integer not null,
  trycnt integer not null,
  answ varchar(1000),
  trydate date,
  response integer,
  extra varchar(500),
  primary key (sid, task_id, trycnt),
  foreign key (task_id) references pstask(task_id),
  foreign key (sid) references psstudent(sid)
  )
storage (initial 5M
  next 3M
 pctincrease 5);