Lehekülje Kasutajaliidesed lab 2 sql schema lähteteksti vaatamine

<pre>

--- 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;


</pre>