project dbms

8/9/2019 Project Dbms

1/17

Employee Record System

HITEC UNIVERSITY TAXILA

DEPARTMENT OF MANAGEMENT SCIENCES

COURSE: Data Base Management System

Project On


SUBMITTED TO:MAM KOUSAR

SUBMITTED BY:

SIDRA KOUSAR

JAMIL HASSAN KHATTAK

ABBRAR HUSSAIN

1


2/17


2


3/17


CONTENTS

1. Introduction

2. Significance

3. Users

4. ERD

5. Flow Chart6. Queries

3


4/17


1. Introduction to DBMS

A collection of related pieces of data:

Representing / capturing the information about a real world enterprise or part of an

enterprise.

Collected and maintained to serve specific data management needs of theenterprise.

Activities of the enterprise are supported by the data base and continually updatethe data base.

Introduction to employees record system

For organizational and legal purposes, you should maintain an up-to-date employee

record on every employee. But be careful to compile only job-related information in

the personnel file. Further it is strongly suggested that you keep more than one file on

each employee .How should I design an Employee Records system? You should keep

all employment action forms in the employee file including the employment

application, payroll changes, performance reviews, disciplinary notices, and other

employment action forms. Generally it is recommended that whether you have hardcopy files or a computerized system to keep three separate files on each employee.

General Employee Information this includes forms like the employment

application, performance review, disciplinary actions, promotions, or other standard

operational issues.

The other reason to keep this information in independent files is because of varying

time limits on how long the information must be kept.

4


5/17


2. Significance

Record the required details for each employee employed in their organization.

Establish procedures to enable employees to access their records and to obtain

a copy of the records.

Ensure all records are maintained in a secure and confidential area.

Establish systems and procedures regarding the authority for access to and the

availability of employee records. Access to employee records should only be on a

'need to know' basis or for official purposes.

The following details regarding employees are to be kept.

designation and any previous designations assigned to the employee remuneration

full name and any previous names

residential and postal address

next of kin and/or emergency contact

entitlements and use of those entitlements

qualifications

details of citizenship

date of birth

5


6/17


3. USERS

There are four types of users these are the following:

1. Employee

2. Administrator

3. Head of department

4. Finance manager

5. Top management

Statements

Employee:

Employee can view his attendance

Employee can view his leaves.

Employee can view his salary.

Employee can view terms and condition.

Employee can view his project.

Employee can view update qualification.

Top management

Top management can view performance of organization.

Top management can hire and fire the employees.

6


7/17


Top management announces bonuses.

Top management introduces the new strategies.

Top management can control the organization.

Head of department

Hod can view his attendance.

Hod can view his leaves.

Hod can enter the attendance.

Hod can assign the project.

Hod can check the project.

Hod can view the salary of employees.

Hod define terms and condition.

Finance department

Finance manager can view salary.

Finance manger can enter the attendance.

Finance manager can enter the salary.

Finance manger can enter the bonuses.

Administrator

7


8/17


Administrator can control the employee.

Administrator can enter the attendance.

Administrator can view the attendance.

Administrator controls the top management.

Administrator can control the HOD.

Administrator can control the finance manager.

Administrator can control the strategies.

8


9/17


ERDIAGRAM

9


10/17


10


11/17


Requirement1. Company has several DEPARTMENT

a. Each DEPARTMENT has a name, number, location and Employee whomanage the DEPARTMENT.

b. One DEPARTMENT can locate in several places.c. Starting work date from manager need to be saved.

2. Each DEPARTMENT control PROJECTEach PROJECT has name, number, and located in one place.

3. Personal data of EMPLOYEE include SSN, Address, Salary, Sex, and Birthdate.

a. Each EMPLOYEE works on one DEPARTMENT, but able to involved inseveral PROJECT.

b. Amount of Employee works hour each project must recorded.c. Supervisor direct from each EMPLOYEE must recorded too.

4. Employee have DEPENDENTData of dependent include Name, Sex, Birth date, Relationship (connection dependentwith Employee).

11


12/17


-

12

Employe

e

Attendan

ce

vi

e

w

Leaves

vi

e

w

salary

Finance

Departm

ent

strategyAdministrator

project

qualificat

ion

condition

vi

ew

vi

ew

vi

e

w

vi

e

w

HOD

vi

e

w

vi

e

wvi

e

w

en

te

r

che

ck

defi

ne

assi

gne

d

performa

nce

Bonuse

s

vi

e

w

en

te

r

en

te

ren

te

r

Top

Manageme

nt

Hi

re

fir

eannou

nce

introd

uce

control

cont

rol

cont

rol

cont

rol

vi

e

w


13/17


FLOWCHART

13


14/17


6. QUEERIESCreate table employee

(

employeeid int auto_increment,

Name varchar(20),

Fname varchar(20),

Homeaddress varchar(20),

Dob varchar(20),

Designation varchar(20),

Sex varchar(20),

Emailaddress varchar(30),

Contactno int(15),

Department varchar(20),

City varchar(20),

Constraint pk_employee primary key(employeeid)

);

Create table department

(

Departmentid int auto_increment,

Name varchar(20),

Location varchar(20),

Type varchar(20),

Constraint pk_department primary key(departmentid)

);

14


15/17


Create table project

(

Projectid int(20),

Name varchar(20),

Location varchar(20),

Nature varchar(20),

Type varchar(20),

Year int(20),

Constraint pk_project primary key(projectid)

);

create table salary

(

employeeid int auto_increment,

name varchar (20),

salary int (20),

year int (20),

constraint pk_salary primary key(employeeid)

);

____________________________________________________

15


16/17


insert into project (name, location, year) values ('drug addiction', hitec university, 2009);

insert into project (name, location, year) values ('kohinoor mill anaylsis', rwp, 2010);

insert into project (name, location, year) values ('unicef', islamabad, 2010);

insert into employee (name) values ('ahsan');

insert into employee (name, salary, year) values ('jamil', 10000, 2010);

insert into employee (name, salary, year) values ('sidra', 12000, 2010);

insert into employee (name, salary, year) values ('asad', 7000, 2010);

insert into employee (name, salary, year) values ('abrar', 8000, 2010);

insert into employee (name, salary, year) values ('ali', 50000, 2010);

insert into employee (name, salary, year) values ('irum', 89000, 2010);

insert into employee (name, salary, year) values ('kiran', 36000, 2010);

insert into employee (name, salary, year) values ('hina', 45000, 2010);

insert into employee (name, salary, year) values ('jack', 69000, 2010);

insert into employee (name, salary, year) values ('anu', 23000, 2010);

insert into employee (name, salary, year) values ('sidd', 45000, 2010);

__________________________________________________________

alter table salary

add constraint fk_employee foreign key(employeeid) references employee(employeeid)on update cascade on delete restrict;

____________________________________________________

select*from salary;

select *from employee where salary=20000;

select*from employee where salary>35000;

select*from employee where salary


17/17


select name, salary, salary*0.1bonus,salary*+(salary*0.1)added bonus fromemployee;

____________________________________________________

Order By

Select*from employee order by employeeid;

Select*from employee where salary=50000 order by employeeid;

Select*from employee where salary=50000 order by employeeid desc;

17

project dbms

Documents