Employee Management System DBMS Project Create Database, Create Tables Using SQL Server
Employee Management System is a useful tool that securely stores and manages all of an employee's personal information and work related data. You could perform administrative tasks more efficiently and rapidly with this solution. With a computerized recruiting management platform, you nowadays can keep track of your contract information.
We are going to Create a Database and the following Tables which are shown in ER Diagram.
Database Create Query:
Create Database EmployeeManagementSystem.
Tables Create Queries:
(1) Table Name: Employee
Create Table Employee(
EmpId Int Primary Key Not Null identity(1,1),
FullName varchar(50) Not Null,
FirstName varchar(50),
MiddleName varchar(50),
LastName varchar(50) Not Null,
empEmail varchar(50) Not Null,
empContactNo Int Not Null,
empGender varchar(50) Not Null,
isActive Bit,
DateOfBirth DateTime,
ResidentalAddress varchar(50),
PermenantAddress varchar(50),
CityId varchar(50),
CountryId varchar(50),
CreatedDate DateTime Not Null,
UpdatedDate DateTime);
(2) Table Name: Role
Create Table Role(
RoleId Int Primary Key Not Null identity(1,1),
RoleName varchar(50) not null,
Role Varchar(50),
Description varchar(50),
CreatedDate DateTime Not Null );
(3) Table Name: Login
Create Table Login(
LoginId Int Primary Key Not Null identity(1,1),
EmpId Int Not Null,
RoleId Int Not Null,
Username varchar(50) Not Null,
PasswordId Int,
CreatedDate DateTime Not Null,
FOREIGN KEY (EmpId) REFERENCES Employee(EmpId),
FOREIGN KEY (RoleId) REFERENCES Role(RoleId)
);
(4) Table Name: PasswordLog
Create Table PasswordLog(
PasswordId Int Primary Key Not Null identity(1,1),
EmpId Int Not Null,
isResetPassword Bit,
PassLog1 varchar(50),
PassLog2 varchar(50),
UpdatedDate DateTime,
FOREIGN KEY (EmpId) REFERENCES Employee(EmpId));
(5) Table Name: EmployeeRole
Create Table EmployeeRole(
EmployeeRoleId Int Primary Key Not Null identity(1,1),
RoleId Int,
EmpId int,
Description varchar(50),
CreatedDate DateTime Not Null,
FOREIGN KEY (EmpId) REFERENCES Employee(EmpId),
FOREIGN KEY (RoleId) REFERENCES Role(RoleId) );
(6) Table Name: Department
Create Table Department(
DeptId Int Primary Key Not Null identity(1,1),
DeptName varchar(50) not null,
Description varchar(50) not null);
(7) Table Name: EmpDepartment
Create Table EmpDepartment(
EmpDeptId Int Primary Key Not Null identity(1,1),
EmpId int,
DeptId int,
comments varchar(100),
CreatedDate DateTime Not Null,
FOREIGN KEY (EmpId) REFERENCES Employee(EmpId),
FOREIGN KEY (DeptId) REFERENCES Department(DeptId) );
(8) Table Name: Qualification
Create Table Qualification(
QualificationId Int Primary Key Not Null identity(1,1),
EmpId Int,
DegreeName varchar(100),
Position varchar(50),
description varchar(100) not null,
CreatedDate DateTime Not Null,
FOREIGN KEY (EmpId) REFERENCES Employee(EmpId));
(9) Table Name: Salary
Create Table Salary(
SalaryId Int Primary Key Not Null identity(1,1),
EmpId Int,
RoleId Int,
BasicSalary bigint,
MedicalAllowance bigint,
TravelAllowance bigint,
RentAllowance bigint,
TotalAmount bigint,
CreatedDate DateTime Not Null,
FOREIGN KEY (EmpId) REFERENCES Employee(EmpId),
FOREIGN KEY (RoleId) REFERENCES Role(RoleId) );
(10) Table Name: Leave
Create Table Leave(
LeaveId Int Primary Key Not Null identity(1,1),
EmpId Int,
RoleID Int,
LeaveMonth Date,
fromDate DateTime,
toDate DateTime,
Reason varchar(100),
FOREIGN KEY (EmpId) REFERENCES Employee(EmpId),
FOREIGN KEY (RoleId) REFERENCES Role(RoleId) );
(11) Table Name: WeekDays
Create Table WeekDays(
WeekId Int Not Null unique,
DayName varchar(50));
(12) Table Name: Attendence
Create Table Attendence(
AttendenceId Int Primary Key Not Null identity(1,1),
EmpId Int,
WeekId Int,
Attendence DateTime,
duration Int,
CreatedDate DateTime Not Null,
FOREIGN KEY (EmpId) REFERENCES Employee(EmpId),
FOREIGN KEY (WeekId) REFERENCES WeekDays(WeekId));
(13) Table Name: Holiday
Create Table Holiday(
HolidayeId Int Primary Key Not Null identity(1,1),
fromDate DateTime,
toDate DateTime,
HolidayName varchar(50),
Description varchar(50));
(14) Table Name: PayRoll
Create Table PayRoll(
PayRollId Int Primary Key Not Null identity(1,1),
EmpId Int,
RoleID Int,
SalaryID Int,
Leave Int,
Attendence Int,
Holiday Int,
Report varchar(100),
CreatedDate DateTime Not Null,
FOREIGN KEY (EmpId) REFERENCES Employee(EmpId),
FOREIGN KEY (RoleId) REFERENCES Role(RoleId) );
Comments
Post a Comment