Question

Please provide a SQL Table diagram with their relationships based on the following prompt:

Consider the design of a database for an online store. Each item is identified by a unique item ID, a title, a description of the item, the date the item is posted, price, and a list of categories (each category is a single word in lower cases). Only registered users can post, buy, and review an item. Each registered user is identified by a user ID, a password, a first name, a last name, an email, the gender, and his/her age. A user can give at most one review for each item, and on a particular day, the user can post at most 5 items and 5 reviews. Meanwhile, an item can have no or many reviews. The review given by a user provides a score of “Excellent, Good, fair, or poor” and then a short remark. A user cannot modify an existing review that she/he gave earlier. One special user called “root” is designated as the administrator user. The root user can ban any other user into a black list. Once banned, a user will not be able to login into the system unless he/she is unbanned from the blacklist subsequently. In addition, each user has two dynamic lists: my favorite sellers and my favorite items, which can be modified by the user when necessary by insert, delete or update. Each user can add items to the shopping cart and a shopping cart the total price of the items is calculated automatically. The shopping cart can be modified by the user when necessary by insert, delete, or update.

Answer 1

Here is diagram..

Item to category is one to many realtionship because one item can have many category.

Item to user is one to many because user can post many items but post date and userid together is unique key as one use can post only one item a day.

User to review is one to many but reviewId and itemId together is unique key.

I have drawn diagram roughly.

That gives you idea you can edit it and make it more precise.

All the important things are covered in the diagram.

Hope this is helpful.

Thank you.