Логички и физички дизајн - Креирање база податоци (со SQL DDL): ddlscript12.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,
        BookId INTEGER NOT NULL REFERENCES Book(BookId)
                ON DELETE CASCADE, 
        ISBN VARCHAR(30) NOT NULL
);


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

CREATE TABLE Library(
        InventoryId SERIAL PRIMARY KEY, 
        UserId INTEGER NOT NULL REFERENCES AppUser(UserId)
                ON DELETE CASCADE,      
        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 ContainsLibraryBook(
    InventoryId INTEGER NOT NULL REFERENCES Library(InventoryId) ON DELETE CASCADE,
    BookId INTEGER NOT NULL REFERENCES Book(BookId) ON DELETE CASCADE,
    PRIMARY KEY (InventoryId, BookId)
);

CREATE TABLE Wishlist(
        WishId SERIAL PRIMARY KEY, 
        UserId INTEGER NOT NULL REFERENCES AppUser(UserId)
                ON DELETE CASCADE, 
        Priority VARCHAR(10) CHECK (Priority IN ('High', 'Low', 'Medium'))
);


CREATE TABLE ContainsWishlistBook(
    WishId INTEGER NOT NULL REFERENCES Wishlist(WishId) ON DELETE CASCADE,
    BookId INTEGER NOT NULL REFERENCES Book(BookId) ON DELETE CASCADE,
    PRIMARY KEY (WishId, BookId)
);


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, 
        BookId  INTEGER NOT NULL REFERENCES Book(BookId)
                ON DELETE CASCADE,  
        InventoryId INTEGER NOT NULL REFERENCES Library(InventoryId)
                ON DELETE CASCADE, 
        RequestStatus VARCHAR(20) NOT NULL CHECK (RequestStatus IN ('Approved', 'Pending', 'Declined')), 
        RequestDate DATE NOT NULL
);


CREATE TABLE Transaction(
        TransactionId SERIAL PRIMARY KEY, 
        RequestId INTEGER NOT NULL REFERENCES BookRequest(RequestId), 
        BorrowerId INTEGER NOT NULL REFERENCES AppUser(UserId), 
        LenderId INTEGER NOT NULL REFERENCES AppUser(UserId), 
        InventoryId INTEGER NOT NULL REFERENCES Library(InventoryId), 
        BorrowDate DATE NOT NULL, 
        ReturnDate DATE NOT NULL CHECK (ReturnDate > BorrowDate), 
        BorrowDuration INT 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 ExchangingBook(
    TransactionId INTEGER NOT NULL REFERENCES Transaction(TransactionId) ON DELETE CASCADE, 
    BookId INTEGER NOT NULL REFERENCES Book(BookId) ON DELETE CASCADE,
    PRIMARY KEY (TransactionId, BookId)
);


CREATE TABLE Review(
        ReviewId SERIAL PRIMARY KEY, 
        TransactionId INTEGER NOT NULL REFERENCES Transaction(TransactionId), 
        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 Message(
        MessageId SERIAL PRIMARY KEY, 
        SenderId INTEGER NOT NULL REFERENCES AppUser(UserId),
        ReceiverId 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, 
        SenderId INTEGER NOT NULL REFERENCES AppUser(UserId), 
        ReceiverId INTEGER NOT NULL REFERENCES AppUser(UserId), 
        CHECK (SenderId != ReceiverId),
        DateCreated DATE NOT NULL, 
        FriendshipStatus VARCHAR(20) NOT NULL CHECK (FriendshipStatus IN ('Accepted', 'Pending', 'Declined', 'Blocked'))
);

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, 
        TransactionId INTEGER REFERENCES Transaction(TransactionId) 
                ON DELETE CASCADE,
        MessageId INTEGER REFERENCES Message(MessageId) 
                ON DELETE CASCADE,
        FriendRequestId INTEGER REFERENCES FriendRequest(FriendshipId) 
                ON DELETE CASCADE, 
        BookRequestId INTEGER REFERENCES BookRequest(RequestId) 
                ON DELETE CASCADE,      
        Type VARCHAR(20) NOT NULL CHECK (Type IN ('System Update', 'Transaction', 'Reminder', '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 ReceivesNotification(
        UserId INTEGER NOT NULL REFERENCES AppUser(UserId) ON DELETE CASCADE,
    NotificationId INTEGER NOT NULL REFERENCES Notification(NotificationId) ON DELETE CASCADE,
    PRIMARY KEY (UserId, NotificationId)
);