Notes

If Not Exists(Select * From sys.databases Where name='EmployeesManagementDb')
Create database EmployeesManagementDb
Go
If Exists(Select * From sys.databases Where name='EmployeesManagementDb')
Use EmployeesManagementDb
Go
If Not Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'Titles')
Create Table Titles
(
TitleId Int Identity Primary Key Not Null,
Title Varchar(50) Not Null
)
Go
If Not Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'Employees')
Create Table Employees
(
EmpId Int Identity Primary Key Not Null,
EmpPhoto Image Null,
EmpName Varchar(50) Not Null,
EmpGender Varchar(10) Not Null,
EmpAdress Varchar(50) Not Null,
EmpTitle Int Not Null,
EmpDOB Datetime Not Null,
EmpPhone Varchar(50) Not Null,
EmpEducation Varchar(50) Not Null,
Constraint FK_Employees_EmpTitle Foreign key (EmpTitle) References Titles (TitleID)
)
Go
If Not Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'Roles')
Create Table Roles
(
RoleId Int Identity Primary Key,
RoleName Varchar(50) Not Null
)
Go
If Not Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'Users')
Create Table Users
(
UserId Int Primary Key,
UserName Varchar(50) Unique Not Null,
Password Varchar(50) Not Null,
IsActive Bit Default 1,
Constraint FK_Users_UserId Foreign Key(UserId) References Employees(EmpId)
)
Go
If Not Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'UsersInRoles')
Create Table UsersInRoles
(
RoleId Int,
UserId Int,
Constraint PK_UsersInRole Primary Key(RoleId,UserId),
Constraint FK_UsersInRoles_RoleId Foreign Key(RoleId) References Roles(RoleId),
Constraint FK_UsersInRoles_UserId Foreign Key(UserId) References Users(UserId)
)
Go
If Not Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'Departments')
Create Table Departments
(
DepartmentID Int Identity Primary Key Not Null,
DepartmentName Varchar(50) Not Null
)
Go
If Not Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'DepartmentEmps')
Create Table DepartmentEmps
(
DepartmentID Int Not Null,
EmpID Int Not Null,
Constraint FK_DepartmentEmps_DepartmentID Foreign key (DepartmentID) References Departments (DepartmentID),
Constraint FK_DepartmentEmps_EmpID Foreign key (EmpID) References Employees (EmpID),
Constraint PK_DepartmentEmps_DepEmp Primary Key(DepartmentID,EmpID)
)
Go
If Not Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'SalaryTerms')
Create Table SalaryTerms
(
TermID Int Identity Primary Key Not Null,
TermDefination Varchar(50) Not Null,
IsActive BIT Not Null
)
Go
If Not Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'Salaries')
Create Table Salaries
(
SalaryId Int Primary Key IDentity,
EmpID Int Not Null,
Salary Money Default 0.0 Not Null,
SalaryTerm Int,
Constraint FK_Salaries_EmpID Foreign key (EmpID) References Employees (EmpID),
Constraint FK_Salaries_SalaryTerm Foreign key (SalaryTerm) References SalaryTerms (TermID)
)
Go
Create Trigger TR_AddUser On Employees
After Insert
As
Declare @empId Int,@empName Varchar(50), @empPhone Varchar(50)
Select @empId = EmpId, @empName = EmpName, @empPhone = EmpPhone From inserted
Insert Into Users(UserId,UserName,Password,IsActive) Values(@empId,LOWER(REPLACE(@empName,' ','')),@empPhone,0)