Tammet – 3. aprill 2006, kell 09:52

2006-04-03T09:52:46Z

←Vanem redaktsioon		Redaktsioon: 3. aprill 2006, kell 11:52
16. rida:		16. rida:

	create table party(		create table party(
	id INTEGER NOT NULL		id INTEGER NOT NULL --- system id nr
	,student varchar(20) NOT NULL		,student varchar(20) NOT NULL --- student (you!) code
	,autent varchar(50)		,autent varchar(50) --- you may just avoid using it
	,name varchar(100) NOT NULL		,name varchar(100) NOT NULL --- this field is obligatory
	,description varchar(1000)		,description varchar(1000)
	,organizer varchar(100)		,organizer varchar(100)
41. rida:		41. rida:

	create table note(		create table note(
	id INTEGER NOT NULL		id INTEGER NOT NULL --- system id nr
	,partyid INTEGER NOT NULL		,partyid INTEGER NOT NULL --- party to attach the note to
	,student varchar(20) NOT NULL		,student varchar(20) NOT NULL --- student (you!) code
	,notetext varchar(1000)		,notetext varchar(1000)
	,notecategory varchar(100)		,notecategory varchar(100)

Tammet – 3. aprill 2006, kell 09:50

2006-04-03T09:50:46Z

Uus lehekülg

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

Kasutajaliidesed lab 2 sql schema - Redigeerimiste ajalugu

Tammet – 3. aprill 2006, kell 09:52

Tammet – 3. aprill 2006, kell 09:50