DDL ASSIGNMENT 2 - Facebook Application

Objective:

You are required to address a set of Data Definition Language (DDL) tasks for a Facebook Application System. Each query focuses on distinct DDL tasks, including the creation of tables, application of constraints, and establishment of relationships between tables.

Requirements:

You will need to complete at least 20 different DDL tasks, covering table creation, primary keys, foreign keys, constraints, and indexes. Each task represents a key part of designing the structure of the Facebook Application System.

Question 1: Create the USER Table

Write a DDL SQL statement to create a USER table with the following requirements:

• user_id as a primary key with auto-increment functionality.
• first_name as a string of maximum length 255 characters, not null.
• last_name as a string of maximum length 255 characters, not null.
• email as a unique string, not null.
• password as a string of maximum length 100 characters, not null.
• date_of_birth as a date field, not null.
• current_subscription_type_id as a foreign key referencing the SUBSCRIPTION_TYPE table.
• created_at as a datetime field, default it to system timestamp, not null.

Question 2: Create the POST Table with Constraints

Create a POST table with the following specifications:

• post_id as a primary key with auto-increment functionality.
• user_id as a foreign key referencing the USER table, not null.
• content as a string with a maximum length of 500 characters, not null.
• created_at as a datetime field, default it to system timestamp, not null.

Question 3: Create the FRIEND Table

Define a FRIEND table with the following details:

• user_id as a foreign key referencing the USER table.
• friend_user_id as a foreign key referencing the USER table (indicating a friendship between two users).
• status as a string (e.g., "Accepted", "Pending", "Blocked"), not null.
• created_at as a datetime field, default it to system timestamp, not null.

Question 4: Create the COMMENT Table with a Foreign Key

Define a COMMENT table:

• comment_id as a primary key with auto-increment functionality.
• content as a string of maximum length 255 characters, not null.
• created_at as a datetime field, default it to system timestamp, not null.
• post_id as a foreign key referencing the POST table, not null.
• user_id as a foreign key referencing the USER table, not null.

Question 5: Define the REACTION Table

Write the SQL statement to create a REACTION table, which tracks user reactions on posts or comments. This will include:

• reaction_id as a primary key with auto-increment functionality.
• reaction_type as a string (e.g., "Like", "Love", "Haha"), not null.
• post_id as a foreign key referencing the POST table (can be null), not null.
• comment_id as a foreign key referencing the COMMENT table (can be null), not null.
• user_id as a foreign key referencing the USER table, not null.
• created_at as a datetime field, default it to system timestamp, not null.

Question 6: Create the PAGE Table

Create a PAGE table:

• page_id as a primary key with auto-increment functionality.
• page_name as a string with a maximum length of 255 characters, not null.
• created_at as a datetime field, default it to system timestamp, not null.
• owner_id as a foreign key referencing the USER table (page owner), not null.

Question 7: Define the MESSAGE Table with Relationships

Write the SQL statement to create a MESSAGE table that includes:

• message_id as a primary key with auto-increment functionality.
• message_content as a string with a maximum length of 500 characters, not null.
• created_at as a datetime field, default it to system timestamp, not null.
• sender_id as a foreign key referencing the USER table, not null.
• receiver_id as a foreign key referencing the USER table, not null.

Question 8: Create the SUBSCRIPTION_TYPE Table with Constraints

Write a DDL SQL statement to create a SUBSCRIPTION_TYPE table:

• subscription_type_id as a primary key with auto-increment functionality.
• subscription_type as a string (e.g., "Free", "Premium"), not null.
• active_flag as a boolean, default to true, not null.
• validity_days as a number, not null.
• created_at as a datetime field, default it to system timestamp, not null.
• Add a CHECK constraint to ensure the subscription_type is in ("Free", "Standard:, "Premium").

Question 9: Create the USER_SUBSCRIPTION Table with Constraints

Write a DDL SQL statement to create a USER_SUBSCRIPTION table:

• subscription_id as a primary key with auto-increment functionality.
• user_id as a foreign key referencing the USER table.
• subscription_type_id as a foreign key referencing the SUBSCRIPTION_TYPE table, not null.
• start_date as a date field, not null.
• end_date as a date field, nullable.
• active_flag as a boolean, default to true, not null.
• created_at as a datetime field, default it to system timestamp, not null.
• Add a CHECK constraint to ensure the end_date is after start_date.

Question 10: Create the SUBSCRIPTION_PAYMENT Table with Constraints

Write a DDL SQL statement to create a SUBSCRIPTION_PAYMENT table:

• payment_id as a primary key with auto-increment functionality.
• subscription_id as a foreign key referencing the USER_SUBSCRIPTION table, not null.
• amount_paid as a decimal (10, 2), not null.
• payment_date as a datetime, not null.
• Add a CHECK constraint to ensure the amount_paid is greater than 0.

Question 11: Create the GROUP Table

Define a GROUP table with the following specifications:

• group_id as a primary key with auto-increment functionality.
• group_name as a string of maximum length 255 characters, not null.
• created_at as a timestamp, not null.
• created_user_id as a foreign key referencing the USER table, not null.
• created_at as a datetime field, default it to system timestamp, not null.

Question 12: Create the EVENT Table

Write a DDL SQL statement to create an EVENT table with the following details:

• event_id as the primary key with auto-increment functionality.
• event_name as a string of maximum length 255 characters, not null.
• event_date as a date field, not null.
• event_start_time as a time field, not null.
• event_end_time as a time field, not null.
• created_user_id as a foreign key referencing the USER table, not null.
• created_at as a datetime field, default it to system timestamp, not null.

Question 13: Create the ADVERTISEMENT Table

Write a DDL SQL statement to create an ADVERTISEMENT table:

• ad_id as the primary key with auto-increment functionality.
• ad_content as a string with a maximum length of 255 characters, not null.
• posted_at as a timestamp, defaulting to the current timestamp, not null.
• page_id as a number, not null.
• created_at as a datetime field, default it to system timestamp, not null.

Question 14: Create the AUDIT_LOG Table

Define an AUDIT_LOG table to track changes in the Facebook application system:

• log_id as a primary key with auto-increment functionality.
• log_date as a datetime field, not null.
• action as a string to describe the action performed.
• user_id as a foreign key referencing the USER table.

Question 15: Add a Column to the USER Table

Write a SQL statement to add a phone_number column of type VARCHAR(15) to the USER table.

Question 16: Alter a Column in the PAGE Table

Write a SQL statement to alter the page_name column in the PAGE table, increasing its length to 500 characters.

Question 17: Drop a Column from the COMMENT Table

Write a SQL statement to drop the created_at column from the COMMENT table.

Question 18: Rename the AUDIT_LOG Table

Write a SQL statement to rename the AUDIT_LOG table to AUDIT_LOGS.

Question 19: Drop the GROUP Table

Write a SQL statement to drop the GROUP table.

Question 20: Add a Compostire Primary Key

Set up a composite primary key using user_id and friend_user_id on FRIEND table.

Question 21: Alter the ADVERTISEMENT Table to Add a Foreign Key column

Write a SQL statement to add a event_id foreign key to the ADVERTISEMENT table, referencing the EVENT table.

Question 22: Add a Foreign Key to the ADVERTISEMENT Table

Add a foreign key in the ADVERTISEMENT table to reference the PAGE table.

Question 23: Add a CHECK Constraint on the FRIEND Table

Write a SQL statement to add a CHECK constraint to the FRIEND table ensuring that user_id and friend_id are not the same.

Question 24: Add a CHECK Constraint on the FRIEND Table

Write a SQL statement to add a CHECK constraint to the FRIEND table ensuring that status is ("Accepted" or "Pending" or "Blocked").

Question 25: Add a CHECK Constraint on the REACTION Table

Write a SQL statement to add a CHECK constraint to the REACTION table ensuring that reaction_type is ( "Like" or "Love" or "Haha")

Question 26: Add a UNIQUE Constraint on the PAGE Table

Write a SQL statement to add a UNIQUE constraint to the PAGE table ensuring that the page_name is not repeated.

Question 27: Add Indexes to the USER and POST Tables

Write SQL statements to:

• Add an index on the last_name column in the USER table.
• Add an index on the created_at column in the POST table.

Question 28: Drop an Index from the POST Table

Write a SQL statement to drop the index on the created_at column in the POST table.

Question 29: Enforce UNIQUE constraints on all applicable tables.

Apply UNIQUE constraints to columns across the entire database wherever duplicate data is not permitted.

GOOD LUCK WITH YOUR ASSIGNMENT!!!

Don't forget to contact us if you need any further assistance with your assignments, and most importantly, for a manual review and approval of your work.

Sample ERD Data Model for Facebook Application