Логички и физички дизајн - Креирање база податоци (со SQL DDL): ddlscript3.sql

CREATE TABLE User(
        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 User(UserId)
                ON DELETE CASCADE,   
        BookId INTEGER NOT NULL REFERENCES Book(BookId)
                ON DELETE CASCADE,  
        Availability VARCHAR(20) NOT NULL DEFAULT 'Available' CHECK (Availability IN ('Available', 'Not Available', 'Reserved')), 
        Condition VARCHAR(20) NOT NULL (Condition IN ('New', 'Like New', 'Good', 'Poor'))
);

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


CREATE TABLE LibraryBook(
        BookId INTEGER NOT NULL REFERENCES Book(BookId),
        InventoryId INTEGER NOT NULL REFERENCES Library(InventoryId),
);


CREATE TABLE WishlistBook(
        BookId INTEGER NOT NULL REFERENCES Book(BookId),
        WishId INTEGER NOT NULL REFERENCES Wishlist(WishId),
);

CREATE TABLE BookRequest(
        RequestId SERIAL PRIMARY KEY, 
        RequesterId INTEGER NOT NULL REFERENCES User(UserId)
                ON DELETE CASCADE, 
        OwnerId INTEGER NOT NULL REFERENCES User(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 User(UserId), 
        LenderId INTEGER NOT NULL REFERENCES User(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 User(UserId), 
        GiverId INTEGER NOT NULL REFERENCES User(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 User(UserId),
        ReceiverId INTEGER NOT NULL REFERENCES User(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 User(UserId), 
        ReceiverId INTEGER NOT NULL REFERENCES User(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 User(UserId), 
        ReportingUserId INTEGER NOT NULL REFERENCES User(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 User(UserId),
        NotificationId INTEGER NOT NULL REFERENCES Notification(NotificationId),
);