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

CREATE TABLE Genre(
        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 LibraryBook(
        InventoryId INTEGER NOT NULL REFERENCES Library(InventoryId)
                ON DELETE CASCADE,
        BookId INTEGER NOT NULL REFERENCES Book(BookId)
                ON DELETE CASCADE
);

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 WishlistBook(
        WishId INTEGER NOT NULL REFERENCES Wishlist(WishId)
                ON DELETE CASCADE,
        BookId INTEGER NOT NULL REFERENCES Book(BookId)
                ON DELETE CASCADE
);

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, 
        SwapTransactionId INTEGER REFERENCES Transaction(TransactionId)
                ON DELETE SET NULL
);


CREATE TABLE TransactionBook(
        TransactionId INTEGER NOT NULL REFERENCES Transaction(TransactionId)
                ON DELETE CASCADE, 
        BookId INTEGER NOT NULL REFERENCES Book(BookId)
                ON DELETE CASCADE
);

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), 
        Date DATE NOT NULL
);

CREATE TABLE Message(
        MessageId SERIAL PRIMARY KEY, 
        SenderId INTEGER NOT NULL REFERENCES AppUser(UserId),
        ReceiverId INTEGER NOT NULL REFERENCES AppUser(UserId), 
        Time TIME NOT NULL, 
        Date 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, 
        Status VARCHAR(20) NOT NULL CHECK (Status 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')), 
        Time TIME NOT NULL, 
        Date DATE NOT NULL, 
        Description VARCHAR (100) NOT NULL, 
        Status VARCHAR(20) NOT NULL CHECK (Status IN ('Read', 'Unread', 'Dismissed'))
);

CREATE TABLE UserNotification(
        UserId INTEGER NOT NULL REFERENCES AppUser(UserId),
        NotificationId INTEGER NOT NULL REFERENCES Notification(NotificationId)
);