Scribd
Upload
4 views11 pages

Jambo Stores MySQL 2

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
4 views11 pages

Jambo Stores MySQL 2

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
You are on page 1/ 11

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:

You might also like