Changes between Initial Version and Version 1 of P6

Timestamp:

04/25/26 20:56:56 (7 days ago)

Author:

211099

Comment:

--

P6

@@ +1 @@
+= Complex DB Reports (SQL, Stored Procedures, Relational Algebra)
+
+
+=== 1. Story Statistics 
+
+==== SQL
+{{{
+
+WITH likeCount AS (
+    SELECT
+        story_id,
+        COUNT(user_id) AS total_likes
+    FROM likes
+    GROUP BY story_id
+),
+commentCount AS (
+    SELECT
+        story_id,
+        COUNT(comment_id) AS total_comments
+    FROM comment
+    GROUP BY story_id
+),
+averageRating AS (
+    SELECT
+        story_id,
+        ROUND(AVG(rating), 2) AS avg_rating
+    FROM chapter
+    WHERE rating IS NOT NULL
+    GROUP BY story_id
+)
+SELECT
+    u.username AS writer,
+    s.story_id,
+    s.short_description,
+    st.status,
+    COALESCE(lk.total_likes, 0) AS total_likes,
+    COALESCE(cm.total_comments, 0)         AS total_comments,
+    COALESCE(CAST(ar.avg_rating AS VARCHAR), 'no ratings')    AS avg_rating
+FROM story              s
+JOIN status             st ON s.story_id = st.story_id
+JOIN writer             w  ON s.user_id  = w.user_id
+JOIN users              u  ON w.user_id  = u.user_id
+LEFT JOIN likeCount     lk ON s.story_id = lk.story_id
+LEFT JOIN commentCount  cm ON s.story_id = cm.story_id
+LEFT JOIN averageRating ar ON s.story_id = ar.story_id
+ORDER BY total_likes DESC, total_comments DESC;
+}}}
+
+==== Relational Algebra
+{{{
+likeCount <-
+γ story_id;
+  total_likes := COUNT(user_id)
+(
+  likes
+)
+
+commentCount <-
+γ story_id;
+  total_comments := COUNT(comment_id)
+(
+  comment
+)
+
+averageRating <-
+γ story_id;
+  avg_rating := ROUND(AVG(rating), 2)
+(
+  σ rating ≠ NULL (chapter)
+)
+
+Base <-
+story s
+⨝ (s.story_id = st.story_id) status st
+⨝ (s.user_id = w.user_id)   writer w
+⨝ (w.user_id = u.user_id)   users u
+
+WithLikes <-
+Base
+⟕ (s.story_id = lk.story_id) likeCount lk
+
+WithComments <-
+WithLikes
+⟕ (s.story_id = cm.story_id) commentCount cm
+
+WithRatings <-
+WithComments
+⟕ (s.story_id = ar.story_id) averageRating ar
+
+Result <-
+π
+  u.username → writer,
+  s.story_id,
+  s.short_description,
+  st.status,
+  COALESCE(lk.total_likes, 0)             → total_likes,
+  COALESCE(cm.total_comments, 0)          → total_comments,
+  COALESCE(ar.avg_rating, 'no ratings')   → avg_rating
+(
+  WithRatings
+)
+}}}