Нормализација и подобрувања на дизајнот: ddl_2025-Sept.sql

CREATE TABLE AppUser(
        UserId SERIAL PRIMARY KEY,
        FirstName VARCHAR(30) NOT NULL,
        LastName VARCHAR(30)  NOT NULL,
        Email VARCHAR(100)  UNIQUE NOT NULL,
        Username VARCHAR(30) UNIQUE NOT NULL,
        Password  VARCHAR(50) NOT NULL, 
        City  VARCHAR(30) NOT NULL, 
        Neighborhood  VARCHAR(30) NOT NULL,
        Bio VARCHAR(150),
        Quote VARCHAR(250)
);

CREATE TABLE Book(
        BookId SERIAL PRIMARY KEY, 
        Title VARCHAR(150) NOT NULL, 
        Author VARCHAR(100) NOT NULL, 
        Language VARCHAR(30) NOT NULL, 
        ImageURL VARCHAR(300)
);

CREATE TABLE BookISBN(
        BookISBNId SERIAL PRIMARY KEY,
        ISBN VARCHAR(30) NOT NULL
);

CREATE TABLE Genre(
        GenreId SERIAL PRIMARY KEY,
        Genre VARCHAR(50) NOT NULL
);

CREATE TABLE Review(
        ReviewId SERIAL PRIMARY KEY, 
        ReceiverId INTEGER NOT NULL REFERENCES AppUser(UserId), 
        GiverId INTEGER NOT NULL REFERENCES AppUser(UserId), 
        Rating INTEGER NOT NULL CHECK (Rating BETWEEN 1 AND 5), 
        ReviewerComment VARCHAR (100), 
        ReviewDate DATE NOT NULL
);

CREATE TABLE Report(
        ReportId SERIAL PRIMARY KEY, 
        ReportedUserId INTEGER NOT NULL REFERENCES AppUser(UserId), 
        ReportingUserId INTEGER NOT NULL REFERENCES AppUser(UserId), 
        CHECK (ReportedUserId != ReportingUserId),
        ReportType VARCHAR(20) NOT NULL CHECK (ReportType IN ('Harassment', 'Bullying', 'Hate speech', 'Spamming')), 
        ReportDate DATE NOT NULL, 
        Details VARCHAR (1000), 
        ReportStatus VARCHAR(20) NOT NULL CHECK (ReportStatus IN ('Pending', 'Accepted', 'Rejected', 'Resolved')), 
        ReportedEntity VARCHAR(20) NOT NULL CHECK (ReportedEntity IN ('Message', 'Rating', 'Profile', 'Library'))
);


CREATE TABLE Notification(
        NotificationId SERIAL PRIMARY KEY, 
        Type VARCHAR(20) NOT NULL CHECK (Type IN ('Transaction', 'Message', 'Friend Request', 'Book Request')), 
        NotifTime TIME NOT NULL, 
        NotifDate DATE NOT NULL, 
        Description VARCHAR (100) NOT NULL, 
        NotificationStatus VARCHAR(20) NOT NULL CHECK (NotificationStatus IN ('Read', 'Unread', 'Dismissed'))
);

CREATE TABLE Transaction(
        TransactionId SERIAL PRIMARY KEY, 
        BorrowerId INTEGER NOT NULL REFERENCES AppUser(UserId), 
        LenderId INTEGER NOT NULL REFERENCES AppUser(UserId), 
        BorrowDate DATE NOT NULL, 
        ReturnDate DATE NOT NULL CHECK (ReturnDate > BorrowDate), 
        BorrowDuration INT NOT NULL
);

CREATE TABLE LibraryBook(
        InventoryId SERIAL PRIMARY KEY,         
        Availability VARCHAR(20) NOT NULL DEFAULT 'Available' CHECK (Availability IN ('Available', 'Not Available', 'Reserved')), 
        Condition VARCHAR(20) NOT NULL CHECK (Condition IN ('New', 'Like New', 'Good', 'Poor'))
);

CREATE TABLE WishlistBook(
        WishId SERIAL PRIMARY KEY, 
        Priority VARCHAR(10) CHECK (Priority IN ('High', 'Low', 'Medium'))
);

CREATE TABLE Message(
        MessageId SERIAL PRIMARY KEY, 
        MsgSenderId INTEGER NOT NULL REFERENCES AppUser(UserId),
        MsgReceiverId INTEGER NOT NULL REFERENCES AppUser(UserId), 
        MsgTime TIME NOT NULL, 
        MsgDate DATE NOT NULL, 
        MessageContent VARCHAR(1000) NOT NULL
);

CREATE TABLE FriendRequest(
        FriendshipId SERIAL PRIMARY KEY, 
        FriendshipSenderId INTEGER NOT NULL REFERENCES AppUser(UserId), 
        FriendshipReceiverId INTEGER NOT NULL REFERENCES AppUser(UserId), 
        CHECK (FriendshipSenderId  != FriendshipReceiverId),
        DateCreated DATE NOT NULL, 
        FriendshipStatus VARCHAR(20) NOT NULL CHECK (FriendshipStatus IN ('Accepted', 'Pending', 'Declined', 'Blocked'))
);

CREATE TABLE BookRequest(
        RequestId SERIAL PRIMARY KEY, 
        RequesterId INTEGER NOT NULL REFERENCES AppUser(UserId)
                ON DELETE CASCADE, 
        OwnerId INTEGER NOT NULL REFERENCES AppUser(UserId)
                ON DELETE CASCADE, 
        RequestStatus VARCHAR(20) NOT NULL CHECK (RequestStatus IN ('Approved', 'Pending', 'Declined')), 
        RequestDate DATE NOT NULL
);

CREATE TABLE Swap(
        SwapId SERIAL PRIMARY KEY,
        TransactionId1 INTEGER NOT NULL REFERENCES Transaction(TransactionId)
                ON DELETE CASCADE,   
        TransactionId2 INTEGER NOT NULL REFERENCES Transaction(TransactionId) 
                ON DELETE CASCADE
);

CREATE TABLE GenreLink(
        GenreId INTEGER NOT NULL REFERENCES Genre(GenreId) ON DELETE CASCADE,
        BookId INTEGER NOT NULL REFERENCES Book(BookId) ON DELETE CASCADE,
        PRIMARY KEY (GenreId, BookId)
);

CREATE TABLE ISBNLink(
        BookISBNId INTEGER NOT NULL REFERENCES BookISBN(BookISBNId) ON DELETE CASCADE,
        BookId INTEGER NOT NULL REFERENCES Book(BookId) ON DELETE CASCADE,
        PRIMARY KEY (BookISBNId, BookId)
);

CREATE TABLE WishlistBookLink(
        WishId INTEGER NOT NULL REFERENCES WishlistBook(WishId) ON DELETE CASCADE,
        BookId INTEGER NOT NULL REFERENCES Book(BookId) ON DELETE CASCADE,
        UserId INTEGER NOT NULL REFERENCES AppUser(UserId) ON DELETE CASCADE,
        PRIMARY KEY (WishId, BookId)
);

CREATE TABLE LibraryBookLink(
        InventoryId INTEGER NOT NULL REFERENCES LibraryBook(InventoryId) ON DELETE CASCADE,
        BookId INTEGER NOT NULL REFERENCES Book(BookId) ON DELETE CASCADE,
        UserId INTEGER NOT NULL REFERENCES AppUser(UserId) ON DELETE CASCADE,   
        PRIMARY KEY (InventoryId, BookId)
);

CREATE TABLE BookRequestLink(
        RequestId INTEGER NOT NULL REFERENCES BookRequest(RequestId) ON DELETE CASCADE,
        InventoryId INTEGER NOT NULL REFERENCES LibraryBook(InventoryId) ON DELETE CASCADE,
        BookId INTEGER NOT NULL REFERENCES Book(BookId) ON DELETE CASCADE,
        PRIMARY KEY (RequestId)
);

CREATE TABLE ReviewLink(
        ReviewId INTEGER NOT NULL REFERENCES Review(ReviewId) ON DELETE CASCADE,
        TransactionId INTEGER NOT NULL REFERENCES Transaction(TransactionId) ON DELETE CASCADE,
        PRIMARY KEY (ReviewId)
);


CREATE TABLE TransactionLink(
        TransactionId INTEGER NOT NULL REFERENCES Transaction(TransactionId) ON DELETE CASCADE,
        RequestId INTEGER NOT NULL REFERENCES BookRequest(RequestId) ON DELETE CASCADE,
        PRIMARY KEY (TransactionId)
);

CREATE TABLE BookRequestLink(
        RequestId INTEGER NOT NULL REFERENCES BookRequest(RequestId) ON DELETE CASCADE,
        InventoryId INTEGER NOT NULL REFERENCES LibraryBook(InventoryId) ON DELETE CASCADE,
        PRIMARY KEY (RequestId)
);


CREATE TABLE MessageNotifLink(
        NotificationId INTEGER NOT NULL REFERENCES Notification(NotificationId) ON DELETE CASCADE,
        MessageId INTEGER NOT NULL REFERENCES Message(MessageId) ON DELETE CASCADE,
        PRIMARY KEY (NotificationId)
);

CREATE TABLE FriendNotifLink(
        NotificationId INTEGER NOT NULL REFERENCES Notification(NotificationId) ON DELETE CASCADE,
        FriendshipId INTEGER NOT NULL REFERENCES FriendRequest(FriendshipId) ON DELETE CASCADE,
        PRIMARY KEY (NotificationId)
);


CREATE TABLE BookRequestNotifLink(
        NotificationId INTEGER NOT NULL REFERENCES Notification(NotificationId) ON DELETE CASCADE,
        RequestId INTEGER NOT NULL REFERENCES BookRequest(RequestId) ON DELETE CASCADE,
        PRIMARY KEY (NotificationId)
);

CREATE TABLE TransactionNotifLink(
        NotificationId INTEGER NOT NULL REFERENCES Notification(NotificationId) ON DELETE CASCADE,
        TransactionId INTEGER NOT NULL REFERENCES Transaction(TransactionId) ON DELETE CASCADE,
        PRIMARY KEY (NotificationId)
);