final SQL 4th course

1. Programming in PL/SQL

PROGRAMMING IN PL/SQL
Database Project
Topic 20: Social Network for Professional
Communities
DONE BY:
YUN VERONIKA
KENZHEYEV NURISLAM
KARASHBEKOV MIRAS
ENSEZHAR ARMAN
IITU, Almaty, 2026​
GROUP IT2-2211

2. Subject Area Analysis​

SUBJECT AREA ANALYSIS
• The subject area of this project is a professional social networking platform. The database is designed to store and manage
information about users, posts, comments, projects, skills, and recommendations within an online community.
• Each user has a unique identification number, email, full name, headline, and location. Users can create posts and interact
with other users by leaving comments on posts. All posts and comments are stored with their content and creation date.
• The system also stores information about projects, including project name, description, start and end dates, and the owner
of the project. Since multiple users can participate in the same project, the system records project membership, including
the user’s role in the project.
• Each user can have multiple skills, and each skill can belong to multiple users. This many-to-many relationship is
implemented through a junction table that also stores the skill level of each user.
• Additionally, users can provide recommendations to other users, creating a network of professional endorsements. Each
recommendation includes the sender, receiver, text, and creation date.
• The main entities in the database are: Users, Posts, Comments, Projects, Project_Members, Skills, User_Skills, and
Recommendations

3. ER DIAGRAM

4.

• The ER diagram models a professional social networking platform where
users can create posts, comment, participate in projects, and manage
their skills.
• The database includes both one-to-many relationships, such as users to
posts and posts to comments, and many-to-many relationships, such as
users to skills and users to projects, which are resolved using associative
tables like user_skills and project_members.
• The schema is normalized up to Third Normal Form. This means that all
attributes depend only on the primary key, there are no partial or
transitive dependencies, and data redundancy is minimized, ensuring data
consistency and scalability.

5. DDL

6.

CREATE TABLE users (
user_id NUMBER PRIMARY KEY,
email
VARCHAR2(100) UNIQUE NOT NULL,
full_name VARCHAR2(100) NOT NULL,
headline VARCHAR2(150),
location VARCHAR2(100),
created_at DATE NOT NULL);
CREATE TABLE posts (
post_id NUMBER PRIMARY KEY,
user_id NUMBER NOT NULL,
content VARCHAR2(500) NOT NULL,
created_at DATE NOT NULL,
CONSTRAINT fk_posts_user FOREIGN KEY
(user_id) REFERENCES users(user_id));
CREATE TABLE skills (
skill_id NUMBER PRIMARY KEY,
skill_name VARCHAR2(50)
UNIQUE NOT NULL)
CREATE TABLE user_skills (
user_skill_id NUMBER PRIMARY KEY,
user_id NUMBER NOT NULL,
skill_id NUMBER NOT NULL,
skill_level VARCHAR2(20) NOT NULL,
CONSTRAINT fk_us_user FOREIGN KEY
(user_id) REFERENCES users(user_id),
CONSTRAINT fk_us_skill FOREIGN KEY
(skill_id) REFERENCES skills(skill_id));
CREATE TABLE comments (
CREATE TABLE projects (
CREATE TABLE project_members(
CREATE TABLE recommendations (
comment_id NUMBER PRIMARY KEY,
project_id NUMBER PRIMARY KEY,
project_member_id NUMBER PRIMARY KEY,
recommendation_id NUMBER PRIMARY KEY,
post_id
NUMBER NOT NULL,
user_id
NUMBER NOT NULL,
project_id
NUMBER NOT NULL,
from_user_id
NUMBER NOT NULL,
NUMBER NOT NULL,
project_name VARCHAR2(150) NOT NULL,
user_id
NUMBER NOT NULL,
to_user_id
NUMBER NOT NULL,
comment_text VARCHAR2(300) NOT NULL,
description VARCHAR2(500),
role VARCHAR2(50) NOT NULL,
created_at DATE NOT NULL,
start_date DATE,
CONSTRAINT fk_pm_project FOREIGN KEY
(project_id)
CONSTRAINT fk_comments_post FOREIGN KEY
(post_id)
REFERENCES posts(post_id),
CONSTRAINT fk_comments_user FOREIGN KEY
(user_id)
owner_id
end_date
DATE,
text
VARCHAR2(400) NOT NULL,
created_at
DATE NOT NULL,
REFERENCES projects(project_id),
CONSTRAINT fk_rec_from FOREIGN KEY
(from_user_id)
CONSTRAINT fk_projects_owner FOREIGN KEY
(owner_id)
CONSTRAINT fk_pm_user FOREIGN KEY (user_id)
REFERENCES users(user_id),
REFERENCES users(user_id));
REFERENCES users(user_id));
CONSTRAINT fk_rec_to FOREIGN KEY (to_user_id)
REFERENCES users(user_id));
REFERENCES users(user_id));

7. DML: Insert into users table

INSERT INTO users VALUES (1, 'alice.dev@mail.com', 'Alice Brown', 'Java Backend Developer', 'Berlin', DATE '2023-01-10');
INSERT INTO users VALUES (2, 'bob.data@mail.com', 'Bob Smith', 'Data Analyst', 'London', DATE '2023-01-12');
INSERT INTO users VALUES (3, 'carol.ui@mail.com', 'Carol White', 'UI/UX Designer', 'Paris', DATE '2023-01-15');
INSERT INTO users VALUES (4, 'david.ml@mail.com', 'David Green', 'Machine Learning Engineer', 'Amsterdam', DATE '2023-01-18');
INSERT INTO users VALUES (5, 'eva.pm@mail.com', 'Eva Black', 'Project Manager', 'New York', DATE '2023-01-20');
INSERT INTO users VALUES (6, 'frank.dev@mail.com', 'Frank Wilson', 'Python Developer', 'Toronto', DATE '2023-01-22');
INSERT INTO users VALUES (7, 'grace.sec@mail.com', 'Grace Lee', 'Cybersecurity Specialist', 'Seoul', DATE '2023-01-25');
INSERT INTO users VALUES (8, 'henry.cloud@mail.com', 'Henry Adams', 'Cloud Engineer', 'Dublin', DATE '2023-01-28');
INSERT INTO users VALUES (9, 'irene.qa@mail.com', 'Irene Novak', 'QA Engineer', 'Prague', DATE '2023-02-01');
DML: INSERT
INTO USERS
TABLE
INSERT INTO users VALUES (10, 'jack.mobile@mail.com', 'Jack Miller', 'Mobile Developer', 'San Francisco', DATE '2023-02-03');
INSERT INTO users VALUES (11, 'kate.hr@mail.com', 'Kate Johnson', 'HR Specialist', 'Chicago', DATE '2023-02-05');
INSERT INTO users VALUES (12, 'leo.ai@mail.com', 'Leo Martinez', 'AI Researcher', 'Madrid', DATE '2023-02-07');
INSERT INTO users VALUES (13, 'maria.front@mail.com', 'Maria Lopez', 'Frontend Developer', 'Barcelona', DATE '2023-02-10');
INSERT INTO users VALUES (14, 'nick.ops@mail.com', 'Nick Turner', 'DevOps Engineer', 'Austin', DATE '2023-02-12');
INSERT INTO users VALUES (15, 'olivia.biz@mail.com', 'Olivia King', 'Business Analyst', 'Boston', DATE '2023-02-14');
INSERT INTO users VALUES (16, 'peter.game@mail.com', 'Peter Wong', 'Game Developer', 'Tokyo', DATE '2023-02-16');
INSERT INTO users VALUES (17, 'quinn.iot@mail.com', 'Quinn Carter', 'IoT Engineer', 'Oslo', DATE '2023-02-18');
INSERT INTO users VALUES (18, 'rachel.doc@mail.com', 'Rachel Moore', 'Technical Writer', 'Seattle', DATE '2023-02-20');
INSERT INTO users VALUES (19, 'sam.sys@mail.com', 'Sam Patel', 'System Architect', 'Dubai', DATE '2023-02-22');
INSERT INTO users VALUES (20, 'tina.test@mail.com', 'Tina Novak', 'Software Tester', 'Vienna', DATE '2023-02-25');

8. Results of Simple Inner Join And Select from projects table

RESULTS OF SIMPLE INNER JOIN
AND
SELECT FROM PROJECTS TABLE

9. Anonymous Block 1: Create new project and ADD user

DECLARE
v_project_id NUMBER := 200;
v_owner_id NUMBER := 1;
v_user_id NUMBER := 2;
BEGIN
ANONYMOUS BLOCK 1:
CREATE NEW PROJECT
AND ADD USER
-- create project
INSERT INTO projects (project_id, owner_id, project_name, description,
start_date, end_date)
VALUES (v_project_id, v_owner_id, 'AI Platform', 'AI collaboration project',
SYSDATE, NULL);
-- add new user
INSERT INTO project_members (project_member_id, project_id, user_id, role)
VALUES (300, v_project_id, v_user_id, 'Developer');
DBMS_OUTPUT.PUT_LINE('Project and member added successfully');
END;

10. Anonymous Block 2: Show users and their Skills

DECLARE
CURSOR c_skills IS
SELECT s.skill_name, us.skill_level
FROM user_skills us
JOIN skills s ON us.skill_id = s.skill_id
ANONYMOUS BLOCK 2:
SHOW USERS AND
THEIR SKILLS
WHERE us.user_id = 1;
v_skill_name skills.skill_name%TYPE;
v_level user_skills.skill_level%TYPE;
BEGIN
OPEN c_skills;
LOOP
FETCH c_skills INTO v_skill_name, v_level;
EXIT WHEN c_skills%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_skill_name || ' - ' || v_level);
END LOOP;
CLOSE c_skills;
END;

11. Function: Show whether user is active or not

CREATE OR REPLACE FUNCTION user_status (
p_user_id NUMBER
)
RETURN VARCHAR2
IS
v_posts NUMBER;
FUNCTION: SHOWS TOTAL USERS
BEGIN
SELECT COUNT(*)
CREATE OR REPLACE FUNCTION total_users
RETURN NUMBER
IS
INTO v_posts
FROM posts
WHERE user_id = p_user_id;
v_total NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_total
FROM users;
RETURN v_total;
END;
IF v_posts > 0 THEN
RETURN 'ACTIVE';
ELSE
RETURN 'NO POSTS';
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN 'ERROR';
END;
FUNCTION:
SHOW
WHETHER USER
IS ACTIVE OR
NOT

12.

FUNCTION TO CALCULATE USER ACTIVITY LEVELS
CREATE OR REPLACE FUNCTION
user_activity_level (
p_user_id NUMBER
)
-- comments
-- total score
SELECT COUNT(*) INTO v_comments
v_score := v_posts * 2
WHEN OTHERS THEN
FROM comments
+ v_comments
RETURN 'ERROR';
WHERE user_id = p_user_id;
+ v_projects * 3
RETURN VARCHAR2
IS
+ v_recommendations * 2;
-- projects
v_posts NUMBER := 0;
v_comments NUMBER := 0;
v_projects NUMBER := 0;
SELECT COUNT(*) INTO v_projects
-- leveling logic
FROM project_members
IF v_score >= 20 THEN
WHERE user_id = p_user_id;
v_recommendations NUMBER := 0;
v_score NUMBER;
BEGIN
-- posts
ELSIF v_score >= 10 THEN
-- recommendations(recieved)
SELECT COUNT(*) INTO
v_recommendations
SELECT COUNT(*) INTO v_posts
FROM recommendations
FROM posts
WHERE to_user_id = p_user_id;
WHERE user_id = p_user_id;
RETURN 'HIGH';
RETURN 'MEDIUM';
ELSE
RETURN 'LOW';
END IF;
EXCEPTION
END;

13. Result

RESULT

14. Procedure: Add New post to user

CREATE OR REPLACE PROCEDURE add_post (
p_user_id IN posts.user_id%TYPE,
p_content IN posts.content%TYPE
)
IS
PROCEDURE:
ADD NEW
POST TO USER
v_new_id posts.post_id%TYPE;
BEGIN
-- next ID
SELECT NVL(MAX(post_id), 0) + 1
INTO v_new_id
FROM posts;
-- Insert new record
INSERT INTO posts (post_id, user_id, content, created_at)
VALUES (v_new_id, p_user_id, p_content, SYSDATE);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Post added successfully. ID: ' || v_new_id);
END;

15. Usage example

USAGE EXAMPLE
• BEGIN
• add_post(1, 'My new professional post');
• END;

16. Package Example: Create package with user related functions

BODY
PACKAGE EXAMPLE:
CREATE PACKAGE
WITH USER RELATED
FUNCTIONS
CREATE OR REPLACE PACKAGE BODY
user_package AS
RETURN NUMBER
IS
v_name users.full_name%TYPE;
BEGIN
SELECT full_name
BEGIN
CREATE OR REPLACE PACKAGE user_package AS
FUNCTION count_user_posts(p_user_id NUMBER) RETURN
NUMBER;
FUNCTION get_user_name(p_user_id NUMBER) RETURN
VARCHAR2;
END user_package;
RETURN VARCHAR2
FUNCTION count_user_posts(p_user_idIS
NUMBER)
v_count NUMBER;
SPECIFICATION
FUNCTION get_user_name(p_user_id
NUMBER)
SELECT COUNT(*)
INTO v_count
FROM posts
WHERE user_id = p_user_id;
RETURN v_count;
END;
INTO v_name
FROM users
WHERE user_id = p_user_id;
RETURN v_name;
END;
END user_package;

17. DML trigger 1: BEFORE INSERT trigger: CHECK THAT USER NAME IS NOT EMPTY

DML TRIGGER 1:
BEFORE INSERT TRIGGER: CHECK THAT USER NAME IS NOT
EMPTY
TRIGGER CODE:
CREATE OR REPLACE TRIGGER
trg_users_name_check
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
IF :NEW.full_name IS NULL THEN
RAISE_APPLICATION_ERROR(-20001,
'User name cannot be empty');
END IF;
END;
RESULT:
If we try to insert empty user name by executing the following:
INSERT INTO users (user_id, email, full_name, headline, location, created_at)
VALUES (10, 'test@mail.com', NULL, 'dev', 'Almaty', SYSDATE);

18. DML trigger 2: BEFORE DELETE trigger : You can not delete user if he has posts

DML TRIGGER 2:
BEFORE DELETE TRIGGER : YOU CAN NOT DELETE USER IF
HE HAS POSTS
CREATE OR REPLACE TRIGGER
trg_user_delete_protect
RESULT:
BEFORE DELETE ON users
FOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM posts
WHERE user_id = :OLD.user_id;
IF v_count > 0 THEN
RAISE_APPLICATION_ERROR(-20003,
'Cannot delete user with posts');
END IF;
END;
DELETE FROM users
WHERE user_id = 1;

19. DML trigger 3: AFTER UPDATE trigger:Logging changes of project

DML TRIGGER 3:
AFTER UPDATE TRIGGER:LOGGING CHANGES OF PROJECT
Check the results:
CREATE OR REPLACE TRIGGER trg_projects_update_log
AFTER UPDATE ON projects
FOR EACH ROW
BEGIN
INSERT INTO audit_log
(table_name, action_type, action_date, description)
VALUES
('PROJECTS','UPDATE',SYSDATE,
'Project updated: ' || :NEW.project_name);
END;
UPDATE projects
SET project_name = 'New AI Platform'
WHERE project_id = 1;

20. Conclusion

CONCLUSION
• The developed database system successfully models a professional social networking platform by integrating usergenerated content, professional relationships, and collaborative interactions into a unified relational structure.
• The project demonstrates a solid application of database design principles, including normalization up to the Third
Normal Form (3NF), ensuring data integrity, consistency, and elimination of redundancy. Complex relationships
such as many-to-many associations are effectively resolved through associative entities, providing flexibility and
scalability.
• Advanced PL/SQL features—including functions, procedures, triggers, and anonymous blocks—were implemented
to enforce business rules, automate system behavior, and enhance data processing capabilities at the database
level.
• The system reflects a realistic and scalable architecture that can be extended to support additional functionalities
such as real-time communication, job matching, and analytics, making it suitable for real-world applications.