RelationalDesign: kreiranjev2.sql

CREATE TABLE city(
        cityname varchar(50) primary key
);

CREATE TABLE faculty(
        facultyid serial primary key,
        facultyname varchar(255) not null,
        cityname varchar(255) references city(cityname)
);

CREATE TABLE building(
        buildingid serial primary key,
        cityname varchar(255) references city(cityname),
        building_adress varchar(255)
);


CREATE TABLE appuser(
        userid serial unique not null,
        prezime varchar(13) not null,
        ime varchar(200) not null,
        pass varchar(200) not null,
        phone_number varchar(30) not null,
        city_of_residency varchar(50),
        datumragj date
);

CREATE TABLE administrator(
        adminid integer primary key references appuser(userid),
        adminrole varchar(255) not null
);

CREATE TABLE apartment_owner(
        ownerid integer primary key references appuser(userid)
);

CREATE TABLE student(
        studentid integer not null primary key references appuser(userid),
        facultyid integer not null references faculty(facultyid),
        age integer,
        gender varchar(255)
);

CREATE TABLE apartment(
        apartmentid serial primary key,
        buildingid integer references building(buildingid) not null,
        ownerid integer references apartment_owner(ownerid) not null,
        apartment_adress varchar(255) not null,
        apartment_size varchar(255) not null
        
);




CREATE TABLE apartment_agreement(
        apartment_agreementid serial primary key,
        apartmentid integer not null references apartment(apartmentid),
        ownerid integer not null references apartment_owner(ownerid),
        date_signed date not null,
        date_expires date not null,
        apartment_rent integer not null
);




CREATE TABLE post(
        postid serial primary key,
        cityname varchar(255) references city(cityname),
        number_of_roommates integer not null,
        price integer not null,
        date_posted date not null,
        description varchar(255)
);




CREATE TABLE student_post(
        student_postid integer primary key references post(postid),
        studentid integer not null references student(studentid)
);

CREATE TABLE owner_post(
        owner_postid integer not null primary key references post(postid),
        apartmentid integer not null references apartment(apartmentid),
        ownerid integer not null references apartment_owner(ownerid)
);

CREATE TABLE apartment_photos(
        postid integer references owner_post(owner_postid) primary key,
        photosurl varchar(1000)
);

CREATE TABLE moderates_posts_for(
        cityname varchar(255) not null references city(cityname),
        adminid integer not null references administrator(adminid),
        primary key(cityname,adminid)
);

CREATE TABLE student_signs(
        studentid integer not null references student(studentid),
        agreementid integer not null references apartment_agreement(apartment_agreementid),
        primary key(studentid,agreementid)
);