UN

Task 1 — Designing the Database 20 marks

Entities, attributes, domains, candidate keys and primary keys (10 marks) plus the detailed ER diagram — completeness 3, accuracy 3, viva 4.

1(a) — Entities, attributes, domains & keys

EntityAttributesDomain of each attributeCandidate key(s)Primary key
DEPARTMENT
dept_id
dept_name
office_location
official_email
official_phone
INT (auto)
VARCHAR(100)
VARCHAR(120)
VARCHAR(120)
VARCHAR(20)
dept_id
dept_name
official_email
dept_id
DESIGNATION
designation_id
designation_title
job_category
grade_level
basic_salary_scale
INT (auto)
VARCHAR(80)
VARCHAR(50)
INT (1–20)
DECIMAL(12,2) ≥ 0
designation_id
designation_title
designation_id
EMPLOYEE
employee_id
full_name
email
phone
date_of_birth
gender
national_id
present_address
permanent_address
joining_date
employment_type
employment_status
dept_id (FK)
designation_id (FK)
INT (auto)
VARCHAR(80)
VARCHAR(120)
VARCHAR(20)
DATE
ENUM('Male','Female','Other')
VARCHAR(30)
VARCHAR(200)
VARCHAR(200)
DATE
ENUM (6 types)
ENUM (5 states)
INT → DEPARTMENT
INT → DESIGNATION
employee_id
email
national_id
employee_id
ATTENDANCE
attendance_id
employee_id (FK)
attendance_date
check_in_time
check_out_time
attendance_status
late_status
remarks
INT (auto)
INT → EMPLOYEE
DATE
TIME (null)
TIME (null)
ENUM (5 states)
ENUM('Yes','No')
VARCHAR(150)
attendance_id
{employee_id, attendance_date}
attendance_id
LEAVE_APPLICATION
leave_app_id
employee_id (FK)
leave_type
start_date
end_date
total_days
reason
application_date
approval_status
approved_by (FK)
remarks
INT (auto)
INT → EMPLOYEE
ENUM (6 types)
DATE
DATE
INT ≥ 1
VARCHAR(200)
DATE
ENUM (3 states)
INT → EMPLOYEE (null)
VARCHAR(150)
leave_app_id
leave_app_id
SALARY
salary_id
employee_id (FK)
basic_salary
house_rent_allowance
medical_allowance
transport_allowance
other_allowance
total_deduction
gross_salary
net_salary
effective_date
salary_status
INT (auto)
INT → EMPLOYEE
DECIMAL(12,2)
DECIMAL(12,2)
DECIMAL(12,2)
DECIMAL(12,2)
DECIMAL(12,2)
DECIMAL(12,2)
DECIMAL(12,2)
DECIMAL(12,2)
DATE
ENUM('Active','Inactive')
salary_id
{employee_id, effective_date}
salary_id
How the candidate keys were found. A candidate key is a minimal set of attributes that uniquely identifies a tuple.
  • dept_name and official_email are unique per department, so each is a candidate key alongside the surrogate dept_id.
  • For an employee, email and national_id are naturally unique — both are candidate keys; the surrogate employee_id is chosen as PK because it is short, numeric and never changes.
  • {employee_id, attendance_date} is a composite candidate key of ATTENDANCE — this is exactly the “no two attendance records for the same employee on the same date” rule.
  • {employee_id, effective_date} is a composite candidate key of SALARY — one salary revision per employee per effective date.
  • LEAVE_APPLICATION has only the surrogate leave_app_id: an employee may legitimately apply twice on the same date, so no natural combination is guaranteed unique.

1(b) — Detailed ER diagram

Chen notation: rectangle = entity set, diamond = relationship set, underlined attribute = primary key, 1 / N pills = mapping cardinality, double line = total participation. approves is a second relationship between EMPLOYEE and LEAVE_APPLICATION with the roles approver and applicant.

DEPARTMENTdept_iddept_nameoffice_locationofficial_emailofficial_phoneDESIGNATIONdesignation_iddesignation_titlejob_categorygrade_levelbasic_salary_scaleEMPLOYEEemployee_idfull_nameemailphonedate_of_birthgendernational_idpresent_addresspermanent_addressjoining_dateemployment_typeemployment_statusATTENDANCEattendance_idattendance_datecheck_in_timecheck_out_timeattendance_statuslate_statusremarksLEAVE_APPLICATIONleave_app_idleave_typestart_dateend_datetotal_daysreasonapplication_dateapproval_statusremarksSALARYsalary_idbasic_salaryhouse_rent_allowancemedical_allowancetransport_allowanceother_allowancetotal_deductiongross_salarynet_salaryeffective_datesalary_statusworks inholdshasapplies fordrawsapprovesapprover / applicant1N1N1N1N1N1Nunderline = primary key · 1/N = mapping cardinality · thick line = total participation
University HR Management ER diagram — 6 entity sets, 6 relationship sets, all 1 : N.

Reading the diagram

RelationshipBetweenCardinalityParticipation
works inDEPARTMENT — EMPLOYEE1 : NTotal on EMPLOYEE (every employee must have a department)
holdsDESIGNATION — EMPLOYEE1 : NTotal on EMPLOYEE (exactly one current designation)
hasEMPLOYEE — ATTENDANCE1 : NTotal on ATTENDANCE
applies forEMPLOYEE — LEAVE_APPLICATION1 : NTotal on LEAVE_APPLICATION
approvesEMPLOYEE — LEAVE_APPLICATION1 : NPartial — a Pending application has no approver yet
drawsEMPLOYEE — SALARY1 : NTotal on SALARY (history of revisions)
Two points the examiner will probe. (1) Why is there no weak entity here? ATTENDANCE and SALARY look weak (they depend on an employee), but each was given its own surrogate key, so they are strong entities with a total-participation FK. If you instead identified them by {employee_id, attendance_date}, they would be weak entity sets with a double rectangle, a double diamond and a dashed-underlined discriminator — say this out loud, it is worth marks. (2) Why two relationships to LEAVE_APPLICATION? Because employee_id and approved_by are different roles of the same entity set.

Notation refresher: Lecture 3 — ER Diagram & EER →