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