Relational Design for ChapterX

Notation

• Primary keys are marked with bold and underlined
• Required attributes are marked with bold
• Foreign keys are marked with * and underlined

Tables

Relational Design for ChapterX

Notation

• Primary keys are bolded and underlined.
• Foreign keys are marked with * at the end of their name and the referenced entity is written in parentheses.
• Complex attributes are bolded, and their containing attributes are following them, made italic.
• Multivalued attributes have their own table

Tables

1. USER (user_ID, username, email, name, surname, password)

2. ADMIN (user_ID* (USER))

3. REGULAR_USER (user_ID* (USER))

4. WRITER (user_ID* (USER), created_story)

5. STORY (story_ID, mature_content, short_description, image, content, user_ID* (WRITER))
   • status (multi-valued attribute, see table STATUS)

6. STATUS (story_ID* (STORY), status)

7. CHAPTER (chapter_ID, chapter_name, title, content, word_count, rating, published_at, view_count, story_ID* (STORY))

8. GENRE (genre_ID, name)

9. READING_LIST (list_ID, name, content, is_public, user_ID* (USER))

10. NOTIFICATION (notification_ID, content, user_ID* (USER))
    • content_type (multi-valued attribute, see table CONTENT_TYPE)

11. CONTENT_TYPE (notification_ID* (NOTIFICATION), content_type)

12. AI_SUGGESTION (suggestion_ID, original_text, suggested_text, suggestion_type, accepted, story_ID* (STORY))

13. LIKE (user_ID* (USER), story_ID* (STORY))

14. COMMENT (comment_ID, content, user_ID* (USER), story_ID* (STORY))

15. COLLABORATION (user_ID* (USER), story_ID* (STORY), role, permission_level)
    • role (multi-valued attribute, see table ROLE)
    • permission_level (multi-valued attribute, see table PERMISSION_LEVEL)

16. ROLE (user_ID* (COLLABORATION), story_ID* (COLLABORATION), role)

17. PERMISSION_LEVEL (user_ID* (COLLABORATION), story_ID* (COLLABORATION), permission_level)

18. HAS_GENRE (story_ID* (STORY), genre_ID* (GENRE))

DDL script for creation and deletion of tables

DML script for inserting data in the tables

Relational diagram made in DBeaver

AI Usage for Relational Design