ACKNOWLEDGEMENT
I express my sincere regards and thanks to
my Faculty Mr. Deepak Singh for his
indispensable contribution, inexhaustible
patience to deal with my endless queries and
dedication throughout the work without which it
would not have been possible for me to
complete in the stipulated time.
I also express my thanks towards our
Director Mrs. Jyotsna Sinha who helps us in all
related matter.
PRATYUSH KUMAR
MCA- 2nd Sem
PROBLEM DEFINITION
Requirement Definition and Analysis:-
There are lots of projects going on every year. Company hires lots of student employees to
help them do the projects. How to manage all the projects efficiently on line is a problem for
the project leaders and their employees. Also an employee might be involved in several
different projects; he/she might have different roles in different projects. For example, an
employee might be a team leader for one project; while at the same time, he/she might be a
team member for another project. Right now they do not have software to manage their
projects yet. By using an on- line project management system, without having a meeting,
the employee could know the tasks assigned to them by their project leaders; the project
leader could know the progress of the current project; and the supervisor will know how
many projects are going on concurrently and who is responsible for each of the project.
Also, sometimes people need to modify their projects. For example, the goal of a project can
be changed; the duration and extension of a project can be changed too. The supervisor can
even change the project leader, although it does not happen quite often. All of these could be
done by using project management software. The design and implementation of such a
project management system is the goal of this project. All the information of current
employees will be stored in a database. Without managing the employee, we cannot manage
projects. Old employee leave and new employee come. Different people have different
roles.
Conceptual diagram:-
ENTITY RELATIONSHIP DIAGRAM
LIST OF ENTITIES
STRONG ENTITIES:-
EMPLOYEE
PROJECT
TASK
SCHEMA REFINEMENT
AND
NORMALIZATION
SCHEMA
Relationship:-
EMPLOYEE
Ename pword fname lname type current
PROJECT
Pid Pname pgoal Bdate Pleader Preport Edate Pduration pextention
TASK
Tid Tname Tcontent adate Pdate pid
WORKS_ON
tid Ename tprogress
NORMALISATION
The Normalization process, as first proposed by Codd (1972), takes a relation schema through a series of tests
to certify a certain normal form. The process, which proceeds in top-down fashion by evaluating each relation
against the criteria for normal forms and decomposing relations as necessary, can thus be considered as
relational design by analysis.
Normalization of data can be considered a process of analyzing the given relation schemas based on
their FD’s and primary keys to achieve the desirable properties of:
Minimizing redundancy
Minimizing the insertion, deletion and update anomalies that could lead to loss of data
integrity.
Types of Anomalies:
The update anomaly occurs when the same data is stored in several records and a change has to be made. If
only some of the records are updated, an update erors occurs - eg we could end up recording two teachers as
giving the same class. If you are dealing with hundreds of records, this is quite likely to occur.
A deletion anomaly occurs not when you delete something by accident, but when deleting a record also
removes the only instance of some other data. So if we recorded a class's details along with a student's, have
only one student enrolled and then that student withdrew, we would be deleting the class info as well.
Normal Forms
Employee
Ename Powrd Fname Lname Type curent
Project
Pid Pname pgoal Bdate Pleader Preport Edate Pduration pextention
Task
Tid Tname Tcontent adate Pdate pid
Works_on
tid Ename tprogress
1ST NORMAL FORM:
A relation (hospital is in 1st normal form) because it holds following properties:
Attributes are having atomic values
Domain of an attributes are simple.
Attributes are having indivisible values.
2nd NORMAL FORM:
Due to its following properties a relation is in 2nd form of normalization:
A relation is in 1st normal form.
A relation is identified through primary key i.e. Branch_no).
Branch_no. is identifying its non prime attributes (hospital name and phone no.)
3rd NORMAL FORM:
A relation hospital is in 3rd normal form as:
A relation is in 2nd normal form.
Relation is having no transitive dependencies.
There is no need for BCNF as there is no duplicacy found in relation.
Tables:-
Logical Database Design: Table Definition
SQL>CREATE TABLE EMPLOYEE (Ename VARCHAR (20),
Pword
VARCHAR (8),
Fname
VARCHAR (10),
Lname
VARCHAR (10),
Type
VARCHAR (10),
Current
VARCHAR (10),
PRIMARY KEY (Ename));
SQL>CREATE TABLE PROJECT (Pid NUMBER (8),
Pname
VARCHAR (20),
Pgoal
VARCHAR (20),
Bdate DATE,
Pleader
VARCHAR (20),
Preport
VARCHAR (20),
Edate DATE,
Pextention
VARCHAR (10),
PRIMARY KEY (Pid));
SQL>CREATE TABLE TASK (Tid NUMBER (8),
Tname
VARCHAR (10),
Tcontent
VARCHAR (20),
Adate DATE,
Ddate DATE,
Pid
NUMBER (8),
PRIMARY KEY (Tid));
SQL>CREATE TABLE WORKS_ON (Tid NUMBER (8)
REFFERECES TASK (Tid),
Ename VARCHAR (20)
REFFERENCES EMPLOYEE (Ename),
Tprogress
VARCHAR (10),
PRIMARY KEY (Tid,Emp));
DATA ENTERED IN SQL
SQL>INSERT INTO EMPLOYEE VALUES (‘Swati’,’manish’,’kapoor’,’swati’,’delhi’,m);
1 row created
SQL>INSERT INTO EMPLOYEE VALUES(‘shilpi’,’vikash’,’tuteja’,’shilpi’,’delhi’,m);
1 row created
SQL>;
SQL>INSERT INTO PROJECT VALUES (234,’SRMS’,’EF’,29-1-2011,’swati’,No,29-11-
2011,1yr,PF);
1 row created
SQL>INSERT INTO PROJECT VALUES (278,’SMS’,EF,29-11-2011,shilpi,yes,31-12-
2011,8M,SX);
1 row created
SQL>INSERT INTO TASK VALUES (456,Testing,Complete,11-1-2011,11-11-
2011,234);
1 row created
DATABASE STATE
SQL> select * from employee;
Ename Pword Lname Fname Type Current
------- -------- -------- --------- ------ ----------
Swati manish kapoor Swati delhi m
Shilpi vikash tuteja Shilpi delhi m
SQL> select * from project;
Pid Pname Pgoal Bdate Pleader Preport
Edate Pduration Pextention
---- -------- ------- ------- --------- ---------
-------- ------------ --------------
234 SRMS EF 29-1-2011 Swati NO 29-11-
2011 1yr PF
278 SMS RF 3-3-2011 Shilpi Yes 31-12-
2011 8M SX
SQL> select * from task;
Tid Tname Tcontent adate ddate
Pid
--- ------ ------- ----- -----
---
456 Testing Complete 11-1-2011 11-11-
2011 234
SQL> select * from works_on;
Tid Ename Tprogress
----- -------- ------------
456 Swati Yes
SQL QUERIES
SQL> DESC employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME NOT NULL NUMBER (5)
PWORD CHAR(10)
FNAME CHAR(10)
LNAME CHAR(10)
TYPE CHAR(20)
CURRENT CHAR(6)
SQL> DESC PROJECT;
Name Null? Type
----------------------------------------- -------- ----------------------------
PID CHAR(10)
PNAME NOT NULL NUMBER(5)
PGOAL CHAR(15)
BDATE DATE
PLEADER CHAR(10)
PREPORT CHAR(10)
EDATE DATE
PDURETION CHAR(10)
PEXTENTION CHAR(10)
SQL> DESC TASK;
Name Null? Type
----------------------------------------- -------- ----------------------------
TID CHAR(10)
TNAME CHAR(6)
TCONTANT DATE
ADATE CHAR(15)
DDATE DATE
PID NUMBER(8)
SQL> desc manages;
Name Null? Type
----------------------------------------- -------- ----------------------------
start_date CHAR(10)
SQL> DESC WORKS_ON;
Name Null? Type
----------------------------------------- -------- ----------------------------
TID NUMBER(8)
ENAME CHAR(20)
TPROGRES CHAR(10)
SQL> rename table employee to employee1;
SQL> rename table employee1 to employee;
SQL> select *fname from employee;
F_NAME
--------
Swati
shilpi
SQL> select *l_ name from employee;
L_NAME
--------
kapoor
tuteja
SQL> select ename,type from employee;
ENAME TYPE
------- --------
swati delhi
shilpi delhi
SQL> update employee set type=’amritsar’ where ename=’shilpi’;
ENAME TYPE
------- --------
swati delhi
shilpi amritsar
SQL> update project set pname=’SMS1’ where pid=278;
PNAME PID
---------- ----------
SRMS 234
SMS1 278
SQL> delete from project where pname=’shilpi’;
1 row deleted
