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