Kasutajaliidesed lab 2 sql schema

--- 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         --- system id nr  
,student      varchar(20) NOT NULL     --- student (you!) code
,autent       varchar(50)              --- you may just avoid using it
,name         varchar(100) NOT NULL    --- this field is obligatory
,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        --- system id nr
,partyid         INTEGER NOT NULL        --- party to attach the note to
,student         varchar(20) NOT NULL    --- student (you!) code
,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;