Question

Based on reference below.

1. Write a relational algebra expression to return those users who have posted “excellent” reviews but never “poor” reviews.

CREATE TABLE Users (

            userId varchar (30) NOT NULL,

            pass varchar (30),

            fname varchar (50),

            lname varchar (50),

            email varchar (50),

            gender char(1),

            age integer,

            banned boolean,

            PRIMARY KEY (userId),

            UNIQUE(email))

CREATE TABLE FavSellers (

            userId varchar (30),

            sellerId varchar (30),

            PRIMARY KEY (userId, sellerId),

            FOREIGN KEY(userId) references Users,

            FOREIGN KEY(sellerId) references Users(userId))

CREATE TABLE Items (

            itemId integer,

            title varchar (50),

            desc varchar (255),

            posterId varchar (30),

            postTime DATETIME,

            price integer,

            PRIMARY KEY (itemId),

            FOREIGN KEY(posterId) references Users(userId))

CREATE TABLE ItemCategories (

            itemId integer,

            category varchar (50),

            PRIMARY KEY (itemId, category),

            FOREIGN KEY(itemId) references Items)

CREATE TABLE FavItems (

            itemId integer,

            userId varchar (30),

            PRIMARY KEY (itemId, userId),

            FOREIGN KEY(itemId) references Items,

            FOREIGN KEY(userId) references Users)

CREATE TABLE Reviews (

            reviewId integer,

            score varchar (50),

            remark varchar (255),

            submitTime Datetime,

            userId varchar (30) NOT NULL,

            itemId integer NOT NULL,

            PRIMARY KEY (reviewId),

            CHECK score IN ('Excellent, 'Good', ...),

            FOREIGN KEY (userId) references Users

            FOREIGN KEY (itemId) references Items)

Answer 1

_{Users.userId, fname,lname}( _{score = 'Excellent' and score != 'Poor'} Users Reviews)

is projection operation to display userId, fname and lname

is join operation on tables Users and Reviews

Do ask if any doubt. Please upvote.