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 the, made italic.
• Multivalued attributes have their own table

Tables

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

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

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

4. ADMIN(user_ID*(USER))

5. REGULAR_USER(user_ID*(USER))

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

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, content_type, user_ID*(USER))

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

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

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

14. 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)

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

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

17. 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