Scribd
Upload
28 views11 pages

Database System for HR Management

The document outlines an individual assignment by Victoria Calistus Mushi for a Database System module in a Bachelor in Cyber Security program. It identifies entities like Staff, HRM, and Board of Directors, along with their relationships and attributes, and provides SQL code for creating corresponding database tables. Additionally, it discusses referential integrity and the implementation of the design in a relational database management system.

Uploaded by

charlie samson
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
28 views11 pages

Database System for HR Management

The document outlines an individual assignment by Victoria Calistus Mushi for a Database System module in a Bachelor in Cyber Security program. It identifies entities like Staff, HRM, and Board of Directors, along with their relationships and attributes, and provides SQL code for creating corresponding database tables. Additionally, it discusses referential integrity and the implementation of the design in a relational database management system.

Uploaded by

charlie samson
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

INSTITUTE OF ACCOUNTANCY ARUSHA

INDIVIDUAL ASSIGNMENT
STUDENTS NAME: VICTORIA CALISTUS MUSHI

REGISTRATION NO: BCSE-01-0063-2024

MODULE NAME: DATABASE SYSTEM

PROGRAMME: BACHELOR IN CYBER SECURITY

MODULE CODE: CYU 07105

SEMESTER: ONE

FACILITATOR: Dr. KIWANGO


(a) Identify the possible entities and their relationships
Entities:
1. Staff
2. Human Resource Manager (HRM)
3. Salary Scale
4. Training Application
5. Training
6. Promotion
7. Board of Directors
Relationships:
1. Staff is managed by Human Resource Manager (HRM).
2. Staff is assigned a Salary Scale.
3. Staff applies for Training Application.
4. Training Application is approved by Board of Directors.
5. Staff undergoes Training.
6. Staff is considered for Promotion based on Training.
7. Human Resource Manager (HRM) prepares Promotion Plan.

(b) List the attributes for each entity


1. Staff
 StaffID (Primary Key)
 FirstName
 SecondName
 Gender
 Age
 EducationLevel
 SalaryScaleID (Foreign Key)
 Terms (Part-time, Contract, Permanent)
 DateOfAppointment
 Title
 ProbationPeriod (3 months)
 ConfirmationStatus (Confirmed/Not Confirmed)
 TrainingStatus (Trained/Not Trained)
2. Human Resource Manager (HRM)
 HRMID (Primary Key)
 HRMName
 HRMContactInfo
3. Salary Scale
 SalaryScaleID (Primary Key)
 EducationLevel
 Experience
 SalaryAmount
4. Training Application
 TrainingAppID (Primary Key)
 StaffID (Foreign Key)
 ProgrammeName
 CourseName
 TrainingInstitution
 Country
 Duration (months)
 TrainingCost
 ApplicationStatus (Approved/Pending/Rejected)
5. Training
 TrainingID (Primary Key)
 StaffID (Foreign Key)
 TrainingAppID (Foreign Key)
 StartDate
 EndDate
 TrainingStatus (Completed/Ongoing)
6. Promotion
 PromotionID (Primary Key)
 StaffID (Foreign Key)
 NewTitle
 NewSalaryScaleID (Foreign Key)
 PromotionDate
7. Board of Directors
 BoardID (Primary Key)
 BoardMemberName
 BoardMemberContactInfo
(c) Draw the relevant logical ERD for the system
Below is a Picture representation of the ERD. Drawn by hand;
(d) With an aid of well-labelled diagram demonstrate the referential integrity
Referential integrity ensures that relationships between tables remain consistent. For
example, if a StaffID in the Staff table is referenced in the Training Application table, you
cannot delete the Staff record without first deleting the corresponding Training Application
records.

(e) Illustrate the physical design for part (c)


The physical design involves creating the actual tables in a database. Below is the SQL code to
create the tables:
CREATE TABLE Staff (
StaffID INT PRIMARY KEY,
FirstName VARCHAR(50),
SecondName VARCHAR(50),
Gender CHAR(1),
Age INT,
EducationLevel VARCHAR(50),
SalaryScaleID INT,
Terms VARCHAR(20),
DateOfAppointment DATE,
Title VARCHAR(50),
ProbationPeriod INT,
ConfirmationStatus VARCHAR(20),
TrainingStatus VARCHAR(20),
FOREIGN KEY (SalaryScaleID) REFERENCES SalaryScale(SalaryScaleID)
);
CREATE TABLE HRM (
HRMID INT PRIMARY KEY,
HRMName VARCHAR(50),
HRMContactInfo VARCHAR(100)
);
CREATE TABLE SalaryScale (
SalaryScaleID INT PRIMARY KEY,
EducationLevel VARCHAR(50),
Experience INT,
SalaryAmount DECIMAL(10, 2)
);
CREATE TABLE TrainingApplication (
TrainingAppID INT PRIMARY KEY,
StaffID INT,
ProgrammeName VARCHAR(100),
CourseName VARCHAR(100),
TrainingInstitution VARCHAR(100),
Country VARCHAR(50),
Duration INT,
TrainingCost DECIMAL(10, 2),
ApplicationStatus VARCHAR(20),
FOREIGN KEY (StaffID) REFERENCES Staff(StaffID)
);
CREATE TABLE Training (
TrainingID INT PRIMARY KEY,
StaffID INT,
TrainingAppID INT,
StartDate DATE,
EndDate DATE,
TrainingStatus VARCHAR(20),
FOREIGN KEY (StaffID) REFERENCES Staff(StaffID),
FOREIGN KEY (TrainingAppID) REFERENCES TrainingApplication(TrainingAppID)
);
CREATE TABLE Promotion (
PromotionID INT PRIMARY KEY,
StaffID INT,
NewTitle VARCHAR(50),
NewSalaryScaleID INT,
PromotionDate DATE,
FOREIGN KEY (StaffID) REFERENCES Staff(StaffID),
FOREIGN KEY (NewSalaryScaleID) REFERENCES SalaryScale(SalaryScaleID)
);
CREATE TABLE BoardOfDirectors (
BoardID INT PRIMARY KEY,
BoardMemberName VARCHAR(50),
BoardMemberContactInfo VARCHAR(100)
);
(f) Implement part (c), (d), and (e) in a relational database management system
The RDBMS used is RDBMS like MySQL Server to implement the above schema. Below is an
illustration;
(g) Input at least 10 records for each table created in part (f)
Below is an example of how you can insert records into the Staff table:

Common questions

Powered by AI

Implementing a physical database design using SQL provides various advantages such as standardized query capabilities, ease of data manipulation with insert, update, and delete commands, and support for complex queries and data retrieval. It supports data management functions by allowing explicit definition of data structures and relationships through commands like CREATE TABLE, ensuring data integrity with foreign key constraints, and optimizing data storage and retrieval through indexes .

An RDBMS supports efficient data management by providing structured data storage, easy data retrieval through SQL queries, and data integrity through enforcement of primary and foreign keys. It ensures that data can be consistently accessed, updated, and retrieved across various departments. Features such as transactions and concurrency control in an RDBMS ensure high levels of data accuracy and availability, which are crucial for business operations .

Structuring a database with entities such as Staff, HRM, and Salary Scale allows for comprehensive data analysis and informed decision-making. By organizing data into specific entities, it facilitates targeted queries and reports—such as analyzing salary distributions against performance or training efficacy—which inform strategic decisions like budgeting for salary adjustments or tailoring training programs. This systematic approach enhances the accuracy of business insights and supports strategic planning and alignment across the organization .

The Human Resource Manager (HRM) plays a pivotal role by managing Staff-related processes, like assigning Salary Scales and overseeing Training and Promotions. The HRM prepares Promotion Plans based on completed Trainings and ensures alignment with organizational policies. This involves evaluating training needs, interacting with the Board for application approvals, and coordinating schedules, evidencing the HRM’s central role in driving employee growth and aligning it with organizational needs .

The outlined process in the database system shows a systematic approach to identifying and developing employee skills through training, managed by applications and approvals, and linked to career advancement via promotions. Training applications approved by the board ensure alignment with company strategy, while successful training completion leads to promotion considerations, reflecting a strategy to nurture talent internally, increase employee satisfaction, and strategically support organizational goals .

Unapproved training applications can lead to misalignment of employee skills with organizational needs, waste of resources on non-strategic trainings, and reduced employee morale due to perceived lack of support for professional growth. These risks can be mitigated by establishing clear, transparent criteria for approvals that align with company goals, ensuring all stakeholders understand the strategic importance, and communicating decisions effectively to all parties involved .

Referential integrity helps maintain consistent relationships by ensuring that foreign keys correctly point to existing records in related tables. If a StaffID in the Staff table is referenced in the Training Application table, deleting the Staff record without first deleting the corresponding Training Application records is prevented. Violating referential integrity can result in orphaned records, which leads to data inconsistencies and potential errors in data retrieval and processing .

Not accurately defining attributes and their data types can lead to data inaccuracies, increased risk of data integrity issues, and inefficiencies in data storage and retrieval. Attributes must have the correct data types to ensure proper data validation and safeguard against incorrect data entry. For instance, setting a numeric data type for age ensures age entries are valid numbers, while incorrect definitions can cause application errors or necessitate costly and time-consuming schema changes later .

The key relationships in an organizational database system include: Staff being managed by the HR Manager, Staff having an assigned Salary Scale, Staff applying for Training Applications, Training Applications being approved by the Board of Directors, Staff undergoing Training, and Staff being considered for Promotion based on Training. These relationships facilitate human resource management by establishing structured workflows and data consistency. For instance, the Training Application process managed by the Board ensures that only authorized and necessary trainings are undertaken, which align with organizational goals and staff development paths .

The ERD aids in conceptual understanding by visually representing the entities and their relationships within the database system, helping stakeholders quickly grasp how data flows and is interconnected. It serves as a communication tool by providing a common framework that technical and non-technical stakeholders can use to discuss and design the system effectively, ensuring alignment in business and technical requirements .

You might also like