-- filename: cugs.sql -- author: Adrian Pop adrpo@ida.liu.se -- last modified date: 2003-03-17 -- this is the postgresql specific implementation -- all tables begins with cugs_ -- esti fan linkin' park sau limbi in parc bai -- hei, niciodata nu e prea tirziu -- toate tre' sa le incerci incearca-le pe viu -- (cind) totzi prefacutzii se prefac ca stiu -- (tu) ridica doar degetul mijlociu -- am limuzine'n clip merg cu metrou'n schimb -- da si mie lantzul tau frate sa-l port in clip -- am inlocuit Havanele cu Maro, aha -- am inlocuit tricoul cu un sacou, aha -- mi-am pus 3 kile de lantz la git din care'un gram de aur -- mor femeile dupa mine -- sint Centaur --------------------------------------------------------------------------------------------- ----------------------------------------- TABLES -------------------------------------------- --------------------------------------------------------------------------------------------- -- sequence for table person drop sequence cugs_sqperson; create sequence cugs_sqperson; -- person table is the root table for cugs database drop table cugs_person cascade; create table cugs_person ( -- primary key for this table automatically generated from sequence id bigint not null primary key default nextval('cugs_sqperson'), -- name firstname text not null, lastname text not null, -- personal no 4(year)+2(month)+2(day)+1(dash)+4(digits) personalno varchar(100) null, -- cugs admittance year adate varchar(100) null, -- freetext txt text null ); -- indexes defined on person table create index cugs_ndxperson0 on cugs_person ( firstname ); create index cugs_ndxperson1 on cugs_person ( lastname ); create index cugs_ndxperson2 on cugs_person ( personalno ); -- email table (table that tell us the emails) drop table cugs_email cascade; create table cugs_email ( -- (person_id) id bigint not null, -- the email string "name@host.net" email text not null, -- foreign key, the code type of this email references cugs_emailtype(code) description text null, constraint cugs_c_email_pk primary key(id, email), constraint cugs_c_email_fk_person_id foreign key(id) references cugs_person(id) match full on update cascade on delete cascade ); -- phone table (table that tell us the phones for a person) drop table cugs_phone cascade; create table cugs_phone ( -- primary key for this table (person_id) id bigint not null, -- the phone no phone text not null, -- foreign key, the type of the phone -> cugs_phonetype(code) description text null, constraint cugs_c_phone_pk primary key(id, phone), constraint cugs_c_phone_fk_person_id foreign key(id) references cugs_person(id) match full on update cascade on delete cascade ); drop sequence cugs_sqaddress; create sequence cugs_sqaddress; -- address table (table that tell us the addresses for a person) drop table cugs_address cascade; create table cugs_address ( -- person_id id bigint not null , -- address_id address_id bigint not null default nextval('cugs_sqaddress'), -- the components of an address country text null, city text null, street text null, postalcode varchar(500) null, -- description for this address description text null, constraint cugs_c_address_pk primary key(id, address_id), constraint cugs_c_address_fk_person_id foreign key(id) references cugs_person(id) match full on update cascade on delete cascade ); -- links table drop table cugs_link cascade; create table cugs_link ( -- primary key for this table (person_id) id bigint not null, -- the url that points to the webpage link text not null, -- the description of this page constraint cugs_c_link_pk primary key(id, link), description text null, constraint cugs_c_phone_fk_person_id foreign key(id) references cugs_person(id) match full on update cascade on delete cascade ); drop sequence cugs_squniversity; create sequence cugs_squniversity; -- (table for university) drop table cugs_university cascade; create table cugs_university ( -- university id id bigint not null primary key default nextval('cugs_squniversity'), -- university name name text null, -- link to univestity link text null, -- descr description text null ); -- init with default values insert into cugs_university values(0, 'None', 'None', 'None'); insert into cugs_university(name, link, description) values('Linköping Institute of Technology', 'http://www.lith.liu.se/', 'None'); insert into cugs_university(name, link, description) values('University of Skövde', 'http://www.his.se/', 'None'); insert into cugs_university(name, link, description) values('Mälardalen University', 'http://www.mdh.se/', 'None'); insert into cugs_university(name, link, description) values('Örebro University', 'http://www.oru.se/', 'None'); insert into cugs_university(name, link, description) values('Lund University', 'http://www.lth.se/', 'None'); insert into cugs_university(name, link, description) values('Växjö University', 'http://www.vxu.se/', 'None'); drop sequence cugs_sqdepartment; create sequence cugs_sqdepartment; -- (table for departments) drop table cugs_department cascade; create table cugs_department ( -- dep id id bigint not null primary key default nextval('cugs_sqdepartment'), -- dep name name text null, -- link to dep link text null, -- descr description text null ); -- init with default values insert into cugs_department values(0, 'None', 'None', 'None'); insert into cugs_department(name, link, description) values('Department of Computer and Information Science (IDA)', 'http://www.ida.liu.se/', 'None'); drop sequence cugs_sqlab; create sequence cugs_sqlab; -- (table for labs/groups) drop table cugs_lab cascade; create table cugs_lab ( -- lab id id bigint not null primary key default nextval('cugs_sqlab'), -- lab/group name name text null, -- link to lab link text null, -- descr description text null ); -- init with default values insert into cugs_lab values(0, 'None', 'None', 'None'); insert into cugs_lab(name, link, description) values('Programming Environments (PELAB)', 'http://www.ida.liu.se/~pelab', 'None'); insert into cugs_lab(name, link, description) values('Real-Time Systems (RTSLAB)', 'http://www.ida.liu.se/~rtslab', 'None'); insert into cugs_lab(name, link, description) values('Teorethical Computer Science (TCSLAB)', 'http://www.ida.liu.se/~tcslab', 'None'); insert into cugs_lab(name, link, description) values('Intelligent Information Systems (IISLAB)', 'http://www.ida.liu.se/~iislab', 'None'); insert into cugs_lab(name, link, description) values('Embedded Systems (ESLAB)', 'http://www.ida.liu.se/~eslab', 'None'); --