Oracle

Objective:

In this assignment, students will design an advanced Facebook Application database using Oracle SQL. Students will implement SQL objects such as stored procedures, views, triggers, and functions aligned with UI mockup displayed at the bottom of this webpage. Students are required to utilize the tables created during the Chapter 5 DDL assignment.

Requirements:

• Complete prior DDL, DML, and DQL assignments to use the provided table structures and data.
• Submit each SQL object (procedure, view, function, trigger) as a separate .sql file, named after the object.
• A report explaining each SQL object’s design and relationship with the proposed UI is required.

Assignment Tasks:

Stored Procedure Tasks:

1. Stored Procedure for Post Data Grid

   • Write a stored procedure to retrieve details from post table.

2. Stored Procedure for Page data entry form

   • Write a stored procedure to insert data into page table.

3. Stored Procedure for Message data entry form

   • Write a stored procedure to update message content in message table.

4. Stored Procedure for Friends Analysis

   • Write a stored procedure to populate the friends' data grid, retrieve the user's name, the count of their own friends, and the number of other users who have added them as a friend.

5. Stored Procedure for Reeaction Analysis

   • Create a stored procedure to gather data for reaction analysis, retrieve the following: User, Post Date, Reaction Name, Total Reaction Count, Weekday Reaction Count, and Weekend Reaction Count. You are required to implement the temporary table technique within this stored procedure.

6. Stored Procedure for Message Management

   • Write a stored procedure to insert data into message table. When inserting data into the message table, ensure that a user cannot send and receive a message to themselves.

7. Stored Procedure for Payment Management

   • Create a stored procedure to manage successfull payments. After receiving a payment, insert the data into the payments and user subscription tables, and update the user table with the current subscription details.

8. Stored Procedure for Post Analysis

   • Create a stored procedure to analyze user posts. Use the date dimension table to generate a list of all dates within the current month. For each date, calculate the total counts of posts, comments, reactions, and messages, ensuring that any date without activity is assigned a value of zero.

View Tasks:

1. View for Post Details

   • Create a view to display user details from user table.

2. View for User Message Activity

   • Create a view to display user name, send message count, received message count and recent message send date.

3. View for User Activity

   • Write a view to display User, Current Subscription, Post Count, Comment Count, Reaction Count and Friends Count.

4. View for Comment Analysis

   • Create a view to display top ten posts by comments count, to include post date, total comment count and comments count made on holidays.

Function Tasks:

1. Function to Calculate Total Reactions per Post

   • Write a function that calculates the total number of reactions a post has received.

2. Function to Get Active Friends

   • Write a function that returns all friends of a user with the status "Accepted".

Trigger Task:

1. Trigger on Post Content Changes
   • Create a trigger on the POST table that logs any changes to the content field into the AUDIT_LOG table. The log should include the post_id, old_content, new_content, log_date, and user_id.

Submission Guidelines:

• Each SQL object (procedure, view, function, trigger) should be submitted as a separate .sql file named after the object.
• Provide a report explaining the SQL object’s functionality and how it aligns with the proposed UI.

This assignment aims to help students develop a Facebook-like Application database using Oracle SQL. The tasks involve managing data for users, posts, friendships, comments, and more, with a focus on using SQL objects to manage and track system data.

UX Screens for Facebook Application Mockup