--- Maja sql schema teise praktikumitöö jaoks:
--- mis väljad on ja mida sinna panna.
---
--- NB! Selles töös SQL-i otse ei kasutata, siiantav
--- schema on kasutatav lihtsalt dokumentatsioonina
--- (sisemiselt rakendus kasutab muidugi nimelt seda
--- schemat)
---
--- Schema sisaldab kolme tabelit: ühte põhi- ja kahte
--- abitabelit.
\echo create table party
create table party(
id INTEGER NOT NULL
,student varchar(20) NOT NULL
,autent varchar(50)
,name varchar(100) NOT NULL
,description varchar(1000)
,organizer varchar(100)
,date date
,time date
,duration INTEGER
,location varchar(1000)
,xcoordinate varchar(20)
,ycoordinate varchar(20)
,type varchar(100)
,poster varchar(100)
,postingdatetime date
,status char(1) default 'A'
);
alter table party add constraint
party_pk primary key(id);
\echo create table note
create table note(
id INTEGER NOT NULL
,partyid INTEGER NOT NULL
,student varchar(20) NOT NULL
,notetext varchar(1000)
,notecategory varchar(100)
,poster varchar(100)
,postingdatetime date
,picturefile varchar(100)
);
alter table note add constraint
note_pk primary key(id);
alter table note add constraint
note_party_id_fk foreign key(partyid)
references party(id);
create sequence party_sequence
start 100
increment 1;
\echo create table house
--- house is the main table
create table house(
id INTEGER NOT NULL --- system id nr
,student varchar(20) NOT NULL --- student (user) code
,category varchar(20) NOT NULL --- what kind of a house
,city varchar(20) NOT NULL --- city where house is located
,address varchar(20) NOT NULL --- address (excluding city)
,price INTEGER --- price in EEK (if known)
,owner varchar(20) --- name of the owner (if known)
,stories INTEGER --- stories 1,2,...etc: if known
);
alter table house add constraint
house_pk primary key(id); --- id is the primary key of house
\echo create table description
--- one house may have many descriptions
create table description(
id INTEGER NOT NULL --- system id nr
,student varchar(20) NOT NULL --- student (user) code
,houseid INTEGER NOT NULL --- house being described
,enterdate date --- when was the description entered
,description varchar(1000) --- actual description text
,points INTEGER --- how good? Higher points: better!
);
alter table description add constraint
description_id primary key(id); --- id is the primary key of description
alter table description add constraint
description_houseid_fk foreign key(houseid)
references house(id); --- described house must exist!
\echo create table visit
--- one house may have many visits
create table visit(
id INTEGER NOT NULL --- system id nr
,student varchar(20) NOT NULL --- student (user) code
,houseid INTEGER NOT NULL --- house being visited
,category varchar(20) NOT NULL --- what kind of visit?
,visitdate date --- when was house visited?
,description varchar(1000) --- actual description of the visit
,points INTEGER --- how good visit? Higher points: better!
);
alter table visit add constraint
house_id primary key(id); --- id is the primary key of a visit
alter table visit add constraint
visit_houseid_fk foreign key(houseid)
references house(id); --- visited house must exist!
\echo create sequence house_sequence
--- house_sequence is used for automatic creation of id numbers
create sequence house_sequence
start 10
increment 1;