Table of Contents

Introduction...................................................................................................................... 3
[Link] normalization.............................................................................................3
2.1 unnormalized form.............................................................................................. 3
2.2 third normal form.................................................................................................3
3 table attributes...............................................................................................................3
3.1 role..........................................................................................................................3
3.2 User........................................................................................................................ 4
3.3 Student profile........................................................................................................ 4
3.4 Recruiter profile...................................................................................................... 5
3.5 Category................................................................................................................. 6
3.6 Job..........................................................................................................................7
3.7 application.............................................................................................................. 8
3.8 complaint................................................................................................................ 9
3.9 content..................................................................................................................10
3.10 audit log.............................................................................................................. 10
4. Data Dictionary...........................................................................................................11
4.2 user.......................................................................................................................12
4.3 student profile.......................................................................................................13
4.4 recruiter profile......................................................................................................14
4.5 category................................................................................................................16
4.6 job.........................................................................................................................16
4.7 Application............................................................................................................18
4.8 complaint.............................................................................................................. 19
4.9 complaint.............................................................................................................. 21
4.10 audit log.............................................................................................................. 21
5. redundancy elimination..............................................................................................22
5.1 Role Name Redundancy (resolved by role table).................................................22
5.2 Category Name Redundancy (Resolved by category table)................................22
 5.3 Company Details Redundancy (Resolved by recruiter_profile)............................22
5.4 Student Details Redundancy (Resolved by student_profile).................................23
5.5 Duplicate Application Prevention (UNIQUE constraint)........................................23
5.6 Duplicate Wishlist Prevention (UNIQUE constraint).............................................23
5.7 CMS Page Redundancy (UNIQUE constraint on page_name)............................23
5.8 Password Storage (No duplication, no plain text).................................................24
5.9 Audit Log Integrity (SET NULL on cascade).........................................................24
5.10 Summary of All Redundancy Resolutions..........................................................24
6. Conclusion................................................................................................................. 25
Introduction
This document outlines the entire database design of the Campus Job Portal System - a
J2EE/JSP web application that links students, recruiters and administrators to
modernize campus recruitment in Nepal, especially in institutions such as Itahari
International College.
The database, called campus job portal, is implemented in MySQL using the InnoDB
storage engine with the utf8mb4 character set to support Unicode text including Nepali
characters. It has eleven tables, the structure of which was obtained after a formal
normalization process (UNF → 1NF → 2NF → 3NF).
This document covers:
 Sequential normalization of unnormalized raw data to the 3NF-compliant tables.
Table attributes: column names, data types and constraints as they are actually defined
in the SQL schema.
 A data dictionary that is detailed with business rules on all attributes.
 How and why redundancy is removed during the design is explained.

[Link] normalization
Normalization is the process of ensuring the relational tables are structured in a way
that data redundancy is minimized and the level of integrity is enhanced. Beginning with
a conceptual flat record that captures all the interactions of a student with the portal,
one normal form is applied at a time.

2.1 unnormalized form

Raw Field Group Example Values

student_id, full_name, email, password, 5, Ram Sharma, ram@[Link],
is_approved hashed_pw, 1
enrollment_number, course_name, BIT-2021-005, BIT, 5, 9800000001
semester, contact_number
resume_file_path, date_of_birth /uploads/cv_ram.pdf, 2001-04-12
role_name (multi-role concept) Student
company_name, company_website, TechCorp Nepal, [Link], Sita
contact_person Thapa
job_id, job_title, category_name, location, 12, Java Developer, Information
deadline Technology, Kathmandu, 2025-08-01
salary_min, salary_max, is_active 30000.00, 60000.00, 1
application_id, status, applied_date, 77, PENDING, 2025-06-10, Strong
recruiter_notes candidate
 complaint_subject, complaint_message, Fake job posting, This job looks
complaint_status suspicious, PENDING
audit_action, ip_address LOGIN, [Link]

2.2 third normal form

A table is in 3NF when it is in 2NF AND there are no transitive dependencies i.e. no
non-key attribute ascertaining another non-key attribute.
Table Dependency Chain Issue Resolution
user (pre-3NF) user_id → role_id Transitive — Extracted to role
→ role_name role_name depends table; user holds
on role_id not role_id FK only
user_id
job (pre-3NF) job_id → Transitive — Extracted to
category_id → category_name category table; job
category_name depends on holds category_id
category_id FK only
application application_id → Transitive — name student name never
student_id → is in user, not stored in
full_name application application;
retrieved by JOIN

3 table attributes
3.1 role
Lookup table for the three user roles in the system. Removed in the 3NF normalization
of the user table to remove the transitive dependency user id = role id = role name.
Column Name Data Type Constraints Description
role_id INT(11) PK, Surrogate primary
AUTO_INCREMENT, key for the role
NOT NULL
role_name VARCHAR(50) UNIQUE, NOT NULL One of: Admin,
Student, Recruiter
3.2 User
Central authentication table. Has a single login account per user irrespective of position.
The role_id foreign key is connected to the role table which was pulled out during 3NF
normalization.
Column Name Data Type Constraints Description
user_id INT(11) PK, Surrogate primary key
AUTO_INCREMENT,
NOT NULL
full_name VARCHAR(100 NOT NULL Full legal name —
) letters and spaces only
(validated in
UserService)
email VARCHAR(100 UNIQUE KEY Login email — globally
) idx_email, NOT NULL unique across all roles
password_has VARCHAR(255 NOT NULL SHA-256 hashed
h ) password — plain text
never stored
role_id INT(11) FK → role(role_id), Determines system role
NOT NULL (Admin/Student/Recruite
r)
is_approved TINYINT(1) NOT NULL, DEFAULT 0 = pending admin
0 approval; 1 = active
account
created_at TIMESTAMP NOT NULL, DEFAULT Account creation
CURRENT_TIMESTAM timestamp — set once,
P never updated
last_login TIMESTAMP NULL, DEFAULT NULL Updated each time user
successfully logs in

3.3 Student profile

Academic and personal information of Student-role users is stored. One row each
student. The UNIQUE constraint on user id makes the 1:1 relationship with user table.
generated in the process of 2NF decomposition.
Column Name Data Type Constraints Description
profile_id INT(11) PK, Surrogate profile
AUTO_INCREMENT, key
NOT NULL
user_id INT(11) UNIQUE KEY, FK → 1:1 link to parent
user(user_id) user account
enrollment_number VARCHAR(20) UNIQUE KEY, NOT College-issued
NULL enrolment ID —
must be globally
unique
course_name VARCHAR(100) NOT NULL Programme name
(e.g., BIT, BBA,
BCA)
semester INT(2) NOT NULL, Current semester
DEFAULT 1 (1–8 for a 4-year
programme)
contact_number VARCHAR(15) DEFAULT NULL Mobile/phone
number —
optional at
registration
resume_file_path VARCHAR(255) DEFAULT NULL Server-relative
path to uploaded
PDF CV
date_of_birth DATE DEFAULT NULL Student date of
birth for age
verification
3.4 Recruiter profile
Contact information and company and contact details of Recruiter-role users. A row to
each recruiter. Formed in the process of 2NF to eliminate company data that was
partially dependent on recruiter_id.
Column Name Data Type Constraints Description
profile_id INT(11) PK, Surrogate profile
AUTO_INCREMENT, key
NOT NULL
user_id INT(11) UNIQUE KEY, FK → 1:1 link to parent
user(user_id) user account
company_name VARCHAR(100) NOT NULL Official registered
company name
company_website VARCHAR(255) DEFAULT NULL Company URL —
optional
contact_person VARCHAR(100) NOT NULL Name of HR/hiring
contact at the
company
contact_designatio VARCHAR(100) DEFAULT NULL Job title of the
n contact person —
optional
is_suspended TINYINT(1) NOT NULL, 0 = active; 1 =
DEFAULT 0 suspended by
admin

3.5 Category
Table of job categories. Obtained in the 3NF normalization to eliminate the transitive
dependency job id → category id category name.
Column Name Data Type Constraints Description
category_id INT(11) PK, Surrogate
AUTO_INCREMENT category key
, NOT NULL
category_name VARCHAR(50) UNIQUE KEY, NOT e.g., Information
NULL Technology,
Finance,
Internship
description VARCHAR(255) DEFAULT NULL Short explanation
of what roles fall
under this
category
3.6 Job
Archives all job advertisements which recruiters place. The user table (through
recruiter_id) and the category table (through category_id) are referred to. Added in the
process of 2NF decomposition; category_id FK added in the process of 3NF.
Column Name Data Type Constraints Description
job_id INT(11) PK, Surrogate job
AUTO_INCREMENT, key
NOT NULL
recruiter_id INT(11) FK → user(user_id), Recruiter who
NOT NULL owns this
posting
category_id INT(11) FK → Job category —
category(category_id), SET NULL if
NULL category
deleted
title VARCHAR(100) NOT NULL Job title (max
100 chars)
description TEXT NOT NULL Full job
description
including
responsibilities
eligibility TEXT DEFAULT NULL Academic or
skills
requirements
for applicants
salary_min DECIMAL(10,2) DEFAULT NULL Minimum
monthly salary
in NPR (NULL =
not disclosed)
salary_max DECIMAL(10,2) DEFAULT NULL Maximum
monthly salary
 in NPR
location VARCHAR(100) NOT NULL Work location or
'Remote'
application_deadlin DATE NOT NULL Applications
e closed after this
date
is_active TINYINT(1) NOT NULL, DEFAULT 1 1 = visible to
students; 0 =
deactivated
posted_date TIMESTAMP NOT NULL, DEFAULT Exact
CURRENT_TIMESTAM timestamp the
P job was created

3.7 application
Logs all student application events. The composite UNIQUE KEY of (student_id, job_id)
helps to avoid duplicate applications on the database level and it is complemented with
the duplicate check on the service-layer in ApplicationService.
Column Name Data Type Constraints Description
application_id INT(11) PK, Surrogate
AUTO_INCREMENT, application key
NOT NULL
student_id INT(11) FK → user(user_id), Student who
NOT NULL submitted this
application
job_id INT(11) FK → job(job_id), NOT Job being applied
NULL for
status ENUM NOT NULL, DEFAULT PENDING,
'PENDING' REVIEWED,
SHORTLISTED,
REJECTED,
HIRED
applied_date TIMESTAMP NOT NULL, DEFAULT Timestamp of
CURRENT_TIMESTAMP application
submission
recruiter_notes TEXT DEFAULT NULL Private notes
added by the
recruiter
UNIQUE KEY — idx_unique_application Prevents duplicate
(student_id,job_id) applications at DB
level
3.8 complaint
Complaints made by any user (students or recruiters) in respect of fake account or
fraudulent job listing. Status can be updated and responded to by the Admin.
Column Name Data Type Constraints Description
application_id INT(11) PK, Surrogate
AUTO_INCREMENT, application key
NOT NULL
student_id INT(11) FK → user(user_id), Student who
NOT NULL submitted this
application
job_id INT(11) FK → job(job_id), NOT Job being applied
NULL for
status ENUM NOT NULL, DEFAULT PENDING,
'PENDING' REVIEWED,
SHORTLISTED,
REJECTED,
HIRED
applied_date TIMESTAMP NOT NULL, DEFAULT Timestamp of
CURRENT_TIMESTAMP application
submission
recruiter_notes TEXT DEFAULT NULL Private notes
added by the
recruiter
UNIQUE KEY — idx_unique_application Prevents duplicate
(student_id,job_id) applications at DB
 level

3.9 content
CMS table under the control of AdminContentServlet. Editable text is stored in stores
that can be viewed by other people (About, Contact, Privacy, Terms). The page_name
UNIQUE KEY makes sure that each page contains only one content record.
Column Name Data Type Constraints Description
content_id INT(11) PK, Surrogate
AUTO_INCREMENT, content key
NOT NULL
page_name VARCHAR(50) UNIQUE KEY, NOT Slug identifier:
NULL 'about', 'contact',
'privacy', 'terms'
title VARCHAR(200) NOT NULL Page heading
displayed to
visitors
body TEXT NOT NULL HTML body
content of the
page
last_updated TIMESTAMP NOT NULL, ON Auto-updated
UPDATE whenever admin
CURRENT_TIMESTAMP saves the page
updated_by INT(11) FK → user(user_id), Admin user who
NULL (SET NULL) last edited the
page

3.10 audit log

Audit trail table and security. Documents all major user activities (LOGIN, LOGOUT,
JOB_POST, APPLICATION_SUBMIT, etc.) along with the IP address, which can be
used to forensically examine the logs. user-id is SET NULL on user deletion to preserve
logs.
Column Data Type Constraints Description
 Name
log_id INT(11) PK, Surrogate log entry key
AUTO_INCREMENT,
NOT NULL
user_id INT(11) FK → user(user_id), Actor who triggered the
NULL (SET NULL) event — NULL if user
deleted
action VARCHAR(100 NOT NULL Action code e.g. LOGIN,
) JOB_CREATED,
APPLICATION_SUBMITTE
D
entity_typ VARCHAR(50) DEFAULT NULL Object type affected e.g.
e JOB, APPLICATION, USER
entity_id INT(11) DEFAULT NULL Primary key of the affected
record
details TEXT DEFAULT NULL Additional context or
before/after values
ip_addres VARCHAR(45) DEFAULT NULL IPv4 or IPv6 address of the
s request origin
created_at TIMESTAMP NOT NULL, DEFAULT Exact timestamp of the
CURRENT_TIMESTAM logged action
P

4. Data Dictionary
The data dictionary gives an all-inclusive authoritative source of all attributes in the
schema of the campus_job_portal. It captures the domain, constraints, default values
and business rules that operate on each column. This is the sole source of truth of all
developers working on the Campus Job Portal System.

4.1 role
Attribute Type / Size Null? Default Business
Rule / Notes
role_id INT(11) No AUTO_INC System-
 generated.
Never reused.
Referenced by
user.role_id as
a foreign key.
role_name VARCHAR(50) No — Allowed
values:
'Admin',
'Student',
'Recruiter'.
UNIQUE
constraint
prevents
duplication.
Seeded at DB
creation.

4.2 user
Attribute Type / Size Null Default Business Rule / Notes
?
user_id INT(11) No AUTO_IN Surrogate PK. Referenced by
C student_profile, recruiter_profile,
 job, application, wishlist,
complaint, content, audit_log.
full_name VARCHAR(10 No — Must contain only letters and
0) spaces. Numbers rejected by
[Link]
ds(). Max 100 chars.
email VARCHAR(10 No — Standard email format. Globally
0) unique (idx_email). Stored in
lowercase. Used as the login
identifier.
password_ha VARCHAR(25 No — SHA-256 hex digest. Plain-text
sh 5) password never persisted.
Hashed in PasswordUtil before
any INSERT or UPDATE.
role_id INT(11) No — FK → role(role_id). ON DELETE
CASCADE. Determines which
dashboard and permissions the
user sees.
is_approved TINYINT(1) No 0 0 = account pending admin
approval. 1 = active and may log
in. Suspended accounts have
is_approved set back to 0.
created_at TIMESTAMP No NOW() Set at INSERT. Immutable. Used
in admin registration reports.
last_login TIMESTAMP Yes NULL Updated on every successful
login. NULL means user has
never logged in since account
creation.

4.3 student profile

Attribute Type / Size Null Default Business Rule / Notes
?
profile_id INT(11) No AUTO_IN Surrogate PK for this
C profile record.
 user_id INT(11) No — 1:1 FK to user. UNIQUE
ensures one profile per
student. ON DELETE
CASCADE removes
profile if user deleted.
enrollment_numbe VARCHAR(20) No — Issued by the college.
r Globally unique
(idx_enrollment_number)
. Used as secondary
duplicate-account check.
course_name VARCHAR(100 No — Programme full name
) e.g. Bachelor of
Information Technology.
Free text validated for
non-blank.
semester INT(2) No 1 Current semester
number. Valid range: 1–8
for four-year
programmes. Validated
server-side.
contact_number VARCHAR(15) Yes NULL Mobile phone. Optional
at registration. Stored as
string to preserve leading
zeros and support +977
prefix.
resume_file_path VARCHAR(255 Yes NULL Server-side relative path
) to uploaded PDF (e.g.
/uploads/cvs/cv_5.pdf).
NULL until student
uploads CV.
date_of_birth DATE Yes NULL Format: YYYY-MM-DD.
Used for age verification.
Should be a past date —
validated in registration
form.

4.4 recruiter profile

Attribute Type / Size Null? Default Business
Rule / Notes
profile_id INT(11) No AUTO_INC Surrogate PK.
user_id INT(11) No — 1:1 FK to
user.
UNIQUE. ON
DELETE
CASCADE.
One company
per recruiter
account.
company_name VARCHAR(100) No — Official
registered
company
name. Must
be non-blank.
Displayed on
job listings.
company_website VARCHAR(255) Yes NULL Optional.
Validated for
http/https
format in the
recruiter
registration
form.
contact_person VARCHAR(100) No — Full name of
the HR or
hiring
manager.
Displayed to
admin for
verification.
contact_designatio VARCHAR(100) Yes NULL Job title of the
n contact (e.g.
HR Manager).
Optional —
displayed on
company
profile page.
is_suspended TINYINT(1) No 0 0 = active
recruiter. 1 =
suspended by
admin.
 Suspended
recruiters
cannot post or
manage jobs.

4.5 category
Attribute Type / Size Null? Default Business Rule /
Notes
category_id INT(11) No AUTO_INC Surrogate PK.
Referenced by
job.category_id.
category_nam VARCHAR(50) No — Must be
e unique. 8
values seeded
at DB creation.
Admin can add
more via CMS.
description VARCHAR(255) Yes NULL Optional plain-
text description
shown in the
category
management
panel.

4.6 job
Attribute Type / Size Null? Default Business Rule / Notes
job_id INT(11) No AUTO_INC Surrogate PK.
Referenced by
 application.job_id and
wishlist.job_id.
recruiter_id INT(11) No — FK → user(user_id).
ON DELETE
CASCADE — jobs
removed if recruiter
account deleted.
category_id INT(11) Yes NULL FK →
category(category_id)
. ON DELETE SET
NULL — job remains
if category deleted,
category_id becomes
NULL.
title VARCHAR(100) No — Job title displayed in
search results. Max
100 chars. Indexed
for keyword search.
description TEXT No — Full role description.
Must be non-blank.
Rendered as HTML in
the job detail page.
eligibility TEXT Yes NULL Optional
qualifications/skills
requirements. If
NULL, no eligibility
criteria are shown.
salary_min DECIMAL(10,2) Yes NULL Minimum monthly
salary (NPR). NULL
when salary is not
disclosed. Must be ≤
salary_max.
salary_max DECIMAL(10,2) Yes NULL Maximum monthly
salary (NPR).
Validated: salary_max
≥ salary_min when
both provided.
location VARCHAR(100) No — City, district, or the
string 'Remote'. Used
in location filter on the
 student search page.
application_deadlin DATE No — Must be a future date
e at creation time.
Applications blocked
after this date by
ApplicationService.
is_active TINYINT(1) No 1 1 = visible and
accepting
applications. 0 =
deactivated by
recruiter or admin.
Checked before
apply.
posted_date TIMESTAMP No NOW() Set at INSERT.
Immutable. Displayed
as 'Posted on' in job
listings.

4.7 Application

Attribute Type / Size Null? Default Business Rule /

Notes
application_id INT(11) No AUTO_INC Surrogate PK.
student_id INT(11) No — FK →
user(user_id). ON
DELETE
CASCADE. Must
reference a user
whose role is
Student.
job_id INT(11) No — FK → job(job_id).
ON DELETE
CASCADE. Must
reference a job
where is_active=1
and deadline not
passed.
status ENUM No PENDING Valid values:
 PENDING,
REVIEWED,
SHORTLISTED,
REJECTED,
HIRED.
Transitions
enforced in
ApplicationService.
applied_date TIMESTAMP No NOW() Set by system at
INSERT. Student
cannot modify.
Displayed in
recruiter's
applicant list.
recruiter_notes TEXT Yes NULL Private notes
visible only to the
recruiter. Never
shown to the
student.

4.8 complaint
Attribute Type / Size Null? Default Business
Rule / Notes
complaint_id INT(11) No AUTO_INC Surrogate PK.
user_id INT(11) No — FK →
 user(user_id).
ON DELETE
CASCADE.
Any role may
file a
complaint.
subject VARCHAR(200) No — Brief title of the
complaint. Max
200 chars.
Displayed in
admin
complaint list.
message TEXT No — Full complaint
text. Must be
non-blank.
Shown in
admin
complaint
detail view.
status ENUM No PENDING Allowed
values:
PENDING,
RESOLVED,
REJECTED.
Updated only
by admin.
admin_respons TEXT Yes NULL Admin's
e resolution
message. Set
when status
changes to
RESOLVED or
REJECTED.
created_at TIMESTAMP No NOW() Complaint
submission
timestamp.
Immutable.
resolved_at TIMESTAMP Yes NULL Set by system
when admin
saves a
 RESOLVED or
REJECTED
response.

4.9 complaint
Attribute Type / Size Null? Default Business Rule / Notes
content_id INT(11) No AUTO_INC Surrogate PK.
page_name VARCHAR(50) No — Unique slug: 'about',
'contact', 'privacy', 'terms'.
Used as URL identifier by
ContentServlet.
title VARCHAR(200) No — Page heading rendered in
the <h1> of the public
page.
body TEXT No — HTML body content.
Admin can include basic
formatting tags.
Rendered unescaped.
last_updated TIMESTAMP No NOW() Auto-updated ON
UPDATE
CURRENT_TIMESTAMP.
Displayed as 'Last
updated' on the public
page.
updated_by INT(11) Yes NULL FK → user(user_id). ON
DELETE SET NULL —
record kept even if admin
account removed.

4.10 audit log

Attribute Type / Size Null? Default Business Rule / Notes
log_id INT(11) No AUTO_INC Surrogate PK. Append-only
— rows are never updated or
deleted.
 user_id INT(11) Yes NULL FK → user(user_id). ON
DELETE SET NULL — logs
preserved even after user
deletion for forensics.
action VARCHAR(100 No — Action code e.g. LOGIN,
) LOGOUT, JOB_CREATED,
JOB_DELETED,
APPLICATION_SUBMITTED,
STATUS_CHANGED.
entity_type VARCHAR(50) Yes NULL Type of object affected: JOB,
APPLICATION, USER,
COMPLAINT, CONTENT.
NULL for session events.
entity_id INT(11) Yes NULL Primary key of the affected
row. Allows filtering logs by
e.g. all events for job_id=12.
details TEXT Yes NULL Human-readable context e.g.
'Status changed from
PENDING to SHORTLISTED
for application_id=77'.
ip_address VARCHAR(45) Yes NULL Supports both IPv4 (15
chars) and IPv6 (39 chars)
addresses. Captured from
HttpServletRequest.
created_at TIMESTAMP No NOW() Exact server timestamp of the
action. Indexed for time-
range queries in the admin
audit panel.

5. redundancy elimination
Data redundancy is a situation whereby an identical information has been saved in an
excess of locations. This results in three forms of anomalies: update anomalies (data is
changed in one copy and not in another), insertion anomalies (cannot store a fact
without storing another fact), and deletion anomalies (loss of useful data when a row is
deleted). The subsections that follow detail all redundancy in the raw data and how it is
resolved by the schema campus-job-portal.

5.1 Role Name Redundancy (resolved by role table)

Issue: The role name of Student or Recruiter would be stored as raw string in each user
row without a role table. Having 500 students, 500 times, Student is stored.
Anomaly: In case the name of the role is changed (e.g. the name Student is changed to
the name Learning), then all 500 rows should be changed. Lacking one will result in
inconsistency.
Resolution: In the role table every role name is stored only once. Role id of the user
table is a foreign key. The new name is set everywhere immediately with just a single
UPDATE to role. This was the major 3NF change - role name was transitively
dependent on role id and not on user id.

5.2 Category Name Redundancy (Resolved by category table)

Problem: The default category name of Information Technology would be duplicated in
all job rows of the same category without the category table. The string has 200 jobs in
the field of IT, and the string is stored 200 times.
Anomaly: To rename a category, one has to update all 200 rows. An error in the typing
of a row forms a ghost category.
Solution: The category table contains all the names once. Jobs reference category_id.
The query time JOIN retrieves the name - never the job row itself. It was the second
3NF modification (job id category id category name is a transitive dependency).

5.3 Company Details Redundancy (Resolved by recruiter_profile)

Problem: Assuming that company name, company website, and contact person are
columns within the job table, a recruiter advertising 15 jobs would record 'TechCorp
Nepal' and Sita Thapa HR Manager 15 times.
Anomaly: The update of the contact person will involve the need to find and update all
15 job rows. Deletion of a single job row may result in the loss of contact data in case
company name was not stored anywhere.
Resolution: In recruiter profile, company information is stored once per recruiter. The job
table is based on recruiter id. Information about the company is displayed when visiting
user to recruiter profile and it is never repeated in job rows.
5.4 Student Details Redundancy (Resolved by student_profile)
Problem: Assuming full name, enrollment number and course name were columns of
the application table, then a student who had submitted 10 applications would have his
or her personal information stored 10 times.
Anomaly: To change the contact number of the student, all the rows of the application of
the student need to be located. Uninstalling all the applications erases the profile
information of the student.
Resolution: student-profile contains academic/personal information once. Only student
id is FK in the application table. JOIN will always get student name and details, but not
stored in application.

5.5 Duplicate Application Prevention (UNIQUE constraint)

Issue: A student might submit twice (i.e. by clicking the back button and submitting the
same application form again) which would fill recruiter dashboards with the identical
account.
Solution: This is avoided at the database level by having a unique key (idx) on the
application table: student_id, job_id. ApplicationService goes further and checks
existing records before INSERT, such that a user is presented with an understandable
error message, as opposed to a raw SQL exception.

5.6 Duplicate Wishlist Prevention (UNIQUE constraint)

Issue: A student might add the same job to their wishlist more than once by clicking the
same job quickly or encountering a problem with their session, and that job appears
more than once.
Resolution: Unique key idx unique wishlist (student id, job id) on the wishlist table can
prevent such duplicates at the DB level. The number of counts of the dashboard stat as
a result is always correct.

5.7 CMS Page Redundancy (UNIQUE constraint on page_name)

Issue: Since there is no special constraint, AdminContentServlet may accidentally
INSERT a second row with the same value in the about column, causing the public
About page to display whichever row is returned first.
Resolution: UNIQUE KEY idx-page-name (page-name) to ensure that there is only one
content row per page slug. This is observed in the seed data using the INSERT pattern
with the ON DUPLICate KEY UPDATE option: it does not duplicate but on a duplicate
key, it upserts.
5.8 Password Storage (No duplication, no plain text)
Issue: Storing plain-text passwords, or storing the same hash in two or more tables (e.g.
user and student_profile), would be both a security and consistency risk.
Solution: the password hash is not present in any of the tables except the user table. It
caches the hex digest of the SHA-256 hash of the password generated by
[Link](). Plaintext is never stored at any location. On login, the
hash is compared by PasswordUtil and the hash is discarded - never logged in
audit_log.

5.9 Audit Log Integrity (SET NULL on cascade)

Issue: Assuming that audit log has an ON DELETE CASCADE user-id FK, deleting a
user would also destroy all the audit history of the user, eliminating the forensic
evidence of that user.
Solution: in audit log ON DELETE SET NULL is used. Deleting a user sets user_id to
NULL in their rows in the log but keeps the action, entity type, entity id, ip address and
created at. The audit trail has been left in its original form so that security checks can be
done.

5.10 Summary of All Redundancy Resolutions

Redundancy Normal Form DB Mechanism Result
Identified
Role name 3NF role table + FK Role name
repeated in user stored once;
rows changed in one
place
Category name 3NF category table + FK Category name
repeated in job stored once;
rows JOIN at query
time
Company details 2NF recruiter_profile + FK Company data
repeated in job stored once per
rows recruiter
Student details 2NF student_profile + FK Student data
repeated in stored once;
applications retrieved by
JOIN
Duplicate Constraint UNIQUE(student_id,job_id) DB blocks
applications second
application for
 same job
Duplicate wishlist Constraint UNIQUE(student_id,job_id) DB blocks
entries second
bookmark for
same job
Duplicate CMS Constraint UNIQUE(page_name) Exactly one
page rows content row per
page
Plain-text Security SHA-256 in PasswordUtil Plain text never
password storage persisted
anywhere
Audit log Integrity ON DELETE SET NULL Logs
destroyed on
user delete

6. Conclusion
A rigorous, step-by-step normalization process, starting with an unnormalized flat
record, was used to design the campus_job_portal database. The outcome is an eleven
table schema deployed in MySQL InnoDB using utf8mb4 encoding and with all of the
capabilities of the Campus Job Portal System.
Notable design decisions: role and category lookup tables are in 3NF; the
student_profile and recruiter_profile tables are in 2NF; composite UNIQUE constraints
on application and wishlist ensure that no plain-text credential is ever stored.
The design guarantees consistency of data, enables the effective querying of data with
indexed foreign keys and provides a reliable base to all service-layer operations
implemented in JobService, ApplicationService, UserService, DashboardService and
SearchService.