RelationalDesign: kireiranjenew.sql

CREATE TABLE city (
    CityID serial primary key,
    CityName varchar(50) not null
);

CREATE TABLE faculty (
    FacultyID serial primary key,
    FacultyName varchar(255) not null,
    CityID integer references city(CityID)
);

CREATE TABLE building (
    BuildingID serial primary key,
    CityID integer references city(CityID),
    BuildingAddress varchar(255)
);

CREATE TABLE appuser(
        userid        serial unique,
    username      varchar(30) not null,
    password      varchar(255),
    email_address varchar(255),
    ime           varchar(255),
    prezime       varchar(255),
    phone_number  varchar(255)
);

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,
    ApartmentFloorAndRoomNumber varchar(255) not null,
    ApartmentSize varchar(255) not null
);

CREATE TABLE apartment_agreement (
    AgreementID serial primary key,
    ApartmentID integer not null references apartment(ApartmentID),
    OwnerID integer not null references apartment_owner(OwnerID),
    DateSigned date not null,
    DateExpires date not null,
    ApartmentRent integer not null
);

CREATE TABLE post (
    PostID serial primary key,
    CityID integer references city(CityID),
    NumberOfRoommates integer not null,
    Price integer not null,
    DatePosted date not null,
    Description varchar(255)
);

CREATE TABLE student_post (
    StudentPostID integer primary key references post(PostID),
    StudentID integer not null references student(StudentID)
);

CREATE TABLE owner_post (
    OwnerPostID 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(OwnerPostID) primary key,
    PhotosURL varchar(1000)
);

CREATE TABLE moderates_posts_for (
    CityID integer not null references city(CityID),
    AdminID integer not null references administrator(AdminID),
    primary key (CityID, AdminID)
);

CREATE TABLE student_signs (
    StudentID integer not null references student(StudentID),
    AgreementID integer not null references apartment_agreement(AgreementID),
    primary key (StudentID, AgreementID)
);