= 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 ==

1. STORY(__'''story_ID'''__, '''mature_content''', '''short_description''', image, '''content''', __user_ID__*)
   * user_ID references WRITER
   * status (multi-valued attribute, see table STATUS)

2. STATUS(__'''story_ID'''__*, __'''status'''__)
   * story_ID references STORY

3. USER(__'''user_ID'''__, '''username''', '''email''', '''name''', '''surname''', '''password''')

4. ADMIN(__'''user_ID'''__*)
   * user_ID references USER

5. REGULAR_USER(__'''user_ID'''__*)
   * user_ID references USER

6. WRITER(__'''user_ID'''__*, created_story)
   * user_ID references USER

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

8. GENRE(__'''genre_ID'''__, '''name''')

9. READING_LIST(__'''list_ID'''__, '''name''', content, '''is_public''', __user_ID__*)
   * user_ID references USER

10. NOTIFICATION(__'''notification_ID'''__, '''content''', '''content_type''', __user_ID__*)
    * user_ID references USER

11. AI_SUGGESTION(__'''suggestion_ID'''__, '''original_text''', '''suggested_text''', '''suggestion_type''', '''accepted''', __story_ID__*)
    * story_ID references STORY

12. LIKE(__'''user_ID'''__*, __'''story_ID'''__*)
    * user_ID references USER
    * story_ID references STORY

13. COMMENT(__'''comment_ID'''__, '''content''', __user_ID__*, __story_ID__*)
    * user_ID references USER
    * story_ID references STORY

14. COLLABORATION(__'''user_ID'''__*, __'''story_ID'''__*, role, permission_level)
    * user_ID references USER
    * story_ID references STORY
    * role (multi-valued attribute, see table ROLE)
    * permission_level (multi-valued attribute, see table PERMISSION_LEVEL)

15. ROLE(__'''user_ID'''__*, __'''story_ID'''__*, __'''role'''__)
    * user_ID references COLLABORATION
    * story_ID references COLLABORATION

16. PERMISSION_LEVEL(__'''user_ID'''__*, __'''story_ID'''__*, __'''permission_level'''__)
    * user_ID references COLLABORATION
    * story_ID references COLLABORATION

17. HAS_GENRE(__'''story_ID'''__*, __'''genre_ID'''__*)
    * story_ID references STORY
    * genre_ID references 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 =