php attendance management system

ATTENDANCE MANAGEMENT SYSTEM SYSTEM

ATTE AT TEN NDANCE MANAGEMENT M ANAGEMENT SY SY STEM The mini ini proje rojec ct report submitt itted in partia rtiall fulfillm lfillment Of the requi requirements for the award ard of B.Tech B.Tech Degr Degree

IN COMPUTER COMPUTE R SCI SCI ENCE AND ENGINEERI NG

By

S.M.N.P S.M .N.PR RA NAT NA T HI SHAI HA I K A BDUL SHAJ HA J A HAN HA N BAS BA SHA P.J A NA KI RA MI RE DDY T. T .SANK AR

690752090 690752093 690752082 690752102

Under the esteemed guidance of

M s.A.K avitha Asst.Professor Department of C.S.E

DEPARTM EPAR TM ENT OF COMPUTER COMPUT ER SCI SCI ENCE AND AND ENGINEERI ENGINEER I NG ANIL NEERUK NEERUK OND ONDA I NSTI NSTI TUTE OF TE CHNOLOGY CHNOLOGY & SCI SCI ENCES SANGI VASAL A, VISAK HAPAT HAP ATNAM NAM – 530003 2010-2011

1|Page


CERTIFICATE

This is to certify that it is a bona-fide record of work entitled “ Attendance ttendance M anagement

Systems” done by S.A.SHAJ S.A.SHA J A HA N BASHA BA SHA(6 (69 90752093) in the partial fulfillment of the requirements for the award of degree of MI NI PROJ ECT I N COMPUTER SCIE SCIE NCE AND AND TECHNOLOGY in Anil Neerukonda Institute Institute of Technology & Sciences , Visakhapatnam Visakhapatnam for the year 2010 - 2011.

PROJ PROJ ECT GUIDE GUIDE

(Ms.A.Kavitha)

2|Page

HEAD OF DEPARTME EPART MENT NT

(Dr. S. C. Satapathy)


CERTIFICATE

This is to certify that it is a bona-fide record of work entitled “ Attendance ttendance M anagement

Systems” done by S.A.SHAJ S.A.SHA J A HA N BASHA BA SHA(6 (69 90752093) in the partial fulfillment of the requirements for the award of degree of MI NI PROJ ECT I N COMPUTER SCIE SCIE NCE AND AND TECHNOLOGY in Anil Neerukonda Institute Institute of Technology & Sciences , Visakhapatnam Visakhapatnam for the year 2010 - 2011.

PROJ PROJ ECT GUIDE GUIDE

(Ms.A.Kavitha)

2|Page

HEAD OF DEPARTME EPART MENT NT

(Dr. S. C. Satapathy)


ACKNOWLEDGEMENT Apart from the efforts made by us, the success of our project depends largely on the encouragement and guidelines of many others. I take this opportunity to express my gratitude to the people who have been instrumental in the successful completion of this project. We thank Ms.A.Kavitha, Asst. Professor, Department of CSE, ANITS who is our internal guide and whose constant support and supervision made our project successful. We would also like to thank Mr.Y.V.Srinivasa Mr.Y.V.Srinivasa Murthy, Murthy, Asst Professor, Department Department of CSE,ANITS for extending his support. We also take the privilege to thank the Head of our Department, Mr. Suresh Chandra Satapathy, for permitting us in laying the first stone for success. We would also like to thank the other staff in our department and lab assistants who directly or indirectly helped us in successful completion of the project. We also thank our friends and college staff who extended their part of support in the successful completion of the project.

S.M.N.P S.M .N.PR RA NAT NA T HI SHAI HA I K A BDUL SHAJ HA J A HAN HA N BAS BA SHA P.J A NA KI RA MI RE DDY T. T .SANK AR

3|Page

690752090 690752093 690752082 690752102


INDEX 1. ABSTRACT

5

2. SYSTEM REQUIREMENT SPECIFICATION

6

2.1 2.2 2.3 2.4 2 .5

INTR INTROD ODUC UCTI TION ON FUNCTI FUNCTIONA ONAL L REQUIR REQUIREME EMENTS NTS NON FUNCTI FUNCTIONA ONAL L REQUIR REQUIREME EMENTS NTS HARDWARE HARDWARE REQUIREMENT REQUIREMENT SPECIFICATI SPECIFICATION ON SOFTWARE REQUIREMENT SPECIFICATION

3. CONC CONCEP EPTU TUAL AL SCHE SCHEMA MA DESI DESIGN GN 3.1 3 .2 3.3 3.4

ENTITY ENTITY RELATIO RELATIONSH NSHIP IP MODEL MODEL IDENTIFICATION OF ENTITIES IDENTIFICA IDENTIFICATION TION OF RELATIONS RELATIONS COMPLE COMPLETE TE E-R DIAGRA DIAGRAM M

4. LOGIC LOGICAL AL SCHEM SCHEMA A GENERAT GENERATION ION 4.1 REPRESENTATI REPRESENTATION ON OF ENTITIES ENTITIES INTO TABLES

5. RELATI RELATIONA ONAL L SCHEM SCHEMA A DESIGN DESIGN 5.1 5.1 ORAC ORACLE LE 5.2 CODE GENERATED GENERATED FOR THE PROPOSED PROPOSED PROJECT PROJECT USING SQL 5.3 TABL TABLES ES CREA CREATE TED D

6. SCHEM SCHEMA A REFIN REFINEM EMENT ENT 6.1 NORM NORMAL ALIZ IZAT ATIO ION N

7 7 8 8 8 9 10 14 17 20 21 22 24 25 26 29 30 35

7. SNAPSHOTS OF TABLES WITH VALID DATA ENTRIES

45

8. QUERIES

49

9. CONCLUSION

54

10. BIBILOGRAPHY

55

4|Page


ABSTRACT Attendance Management Management System is is software developed for daily student attendance attendance in schools, colleges and institutes. If facilitates to access access the attendance information of a particular student student in a particular class. class. The informati information on is sorted by the operators, operators, which will be provided by the teacher for a particular particular class. This system will also help in evaluating attendance attendance eligibility eligibility criteria of a student.

In the present system all work is done on paper. The whole session attendance is stored in register and at the end of the session the reports are generated generated and the students who don’t have have 75% attendance attendance get get a notice.

The existing system system is not user friendly because the retrieval of data is is very slow and data is not maintained efficiently. efficiently. We require more calculations calculations to generate the the report so it is generated at the end end of the session. session. And the stude students nts not get a single single chance chance to improve improve their their attendan attendance. ce. All calcu calculati lations ons to generate report are done manually so there is greater chance chance of errors. Existing Existing system system requires lot of paper work. Loss of even a single register/record led to difficult situation because all the papers are needed to generate the reports. reports. Every work is done manually so we cannot generate report in the middle of the session or as per the requirement because because it is very time consuming. consuming.

The proposed system system is user friendly because the retrieval and storing storing of data is fast fast and data is maintained efficiently. efficiently. Moreover Moreover the graphical user interface is provided in the proposed system, which which provides user to deal with the system very very easily. Reports can be easily easily generated in in the proposed system so user can generate there port as per the requirement requirement (monthly) or in the middle of the session. User User can give the notice to the students students so he/she become regular. regular.

5|Page

ATTENDANCE MANAGEMENT SYSTEM

SYSTEM REQUIREMENT SPECIFICATION

6|Page


2.1 INTRODUCTION:

2.1.1 PURPOSE: Attendance Management System is softwaredeveloped for daily student attendance in schools, collegesand institutes . If facilitates to access the attendanceinformation of a particular student in a particular class. Theinformation is sorted by the operators, which will be providedby the teacher for a particular class. This system will alsohelp in evaluating attendance eligibility criteria of a student.The purpose of developing attendancemanagement system is to computerized the tradition way oftaking attendance. Another purpose for developing thissoftware is to generate the report automatically at the end ofthe session or in the between of the session.

2.1.2 SCOPE: The scope of the project is the system on whichthe software is installed, i.e. the project is developed as a database design from which we can retrieve information about attendance, and it will work for a particular institute.But later on the project can be modified to operate it online.

2.1.3 LIFE TIME: This product works until the software used is in existence.

2.1.4 SUCCESS CRITERI A: As we are using current trends of software, the product runs successfully.

2.2 FUNCTI ONAL REQUIREMENTS: Attendance management system requires the following information to maintain attendance of each student.

1. First the respective students from each class of particular department are identified. 2. Each faculty member is assigned a single subject for particular batch. 3. Each student is enrolled for a particular course and the subjects of that course are identified. 4. Whenever student attends class of particular subject, the corresponding faculty member should allot him attendance and update his status in attendance table. 7|Page


5. The attendance of each student is calculated as ratio of number of classes attended to total number of classes.

2.3 NON-FUNCTI ONAL REQUIREMENTS: 2.3.1 USABI L I TY : The product could be used by two categories of people: faculty members and students. Apart from them administrator also could use it.

2.3.2 RELIABILITY: Users can perform the operations without any constraints regarding the outcome of operation. The product as a whole is highly reliable.

2.3.3 PERFORMANCE: It provides users with access to information based on the type of users i.e. , student, faculty and to which department they belong. It provides fast access to all the data and transactions requested thereby providing a high degree of performance and throughput.

2.3.4 SUPPORTABILITY: All kinds of information which can be supported in the database are supported by the system and the application supports the utilities of the system over which it is deployed.

2.4 HARDWARE REQUIREMENT SPECIFI CATI ON: Processor: Intel Pentium III or above Hard Disk: 30GB or above RAM: 256MB or above

2.5 SOFTWARE REQUIREMENT SPECIFI CATI ON: Database support (for back end): oracle 8i or 9i or 10g

8|Page


CONCEPTUAL SCHEMA DESIGN

9|Page


3.1 ENTI TY REL ATIONSHIP MODEL : An entity-relationship model (ERM) is an abstract and conceptual representation of data. Entityrelationship modeling is a database modeling method, used to produce a type of conceptual schema or semantic data model of a system, often a relational database, and its requirements in a down fashion. Diagrams created by this process are called entity-relationship diagrams, ER diagrams, or ERDs.

COMPONENTS: ENTITY: An entity may be defined as a thing which is recognized as being capable of an independent existence and which can be uniquely identified. An entity is an abstraction from the complexities of some domain. When we speak of an entity we normally speak of some aspect of the real world which can be distinguished from other aspects of the real world. An entity may be a physical object such as a house or a car, an event such as a house sale or a car service, or a concept such as a customer transaction or order. Although the term entity is the one most commonly used, following Chen we should really distinguish between an entity and an entity-type. An entity-type is a category. An entity, strictly speaking, is an instance of a given entity-type. There are usually many instances of an entity-type. Because the term entity-type is somewhat cumbersome, most people tend to use the term entity as a synonym for this term. Entities can be thought of as nouns. Examples: a computer, an employee, a song, a mathematical theorem.

REPRESENTATION: Entities are drawn as rectangles

EXAMPLE: COMPANY

Or

JOB

ATTRIBUTES: An entity is described using a set of attributes. All entities in a given entity set have the same attributes; this is known as similar type. Our choice of attributes reflects the level of detail at which we wish to represent information about entities. For example, company entity set could use company_id, company_name for each company.

10 | P a g e


For each attribute associated with an entity set, we must identify a domain of possible values. For example domain associated with attribute company_name of company might be a set of 20-character strings similarly company_id might be integer. Further, for each entity set , we choose a key. A key is a minimal set of attributes whose values uniquely identify an entity in the set, generally called as candidate key, there could be more than one candidate key, if so we designate one of them as primary key. A primary key is key with which we can identify a tuple uniquely.

TY PES: Simple Attribute: A normal attribute defining a entity

Representation: Name

Multivalued attribute: Attribute consisting of multiple values.

Example: Address

Derived attribute: An attribute which is derived from other attribute.

RELATIONS: A relationship captures how two or more entities are related to one another. Relationships can be thought of as verbs, linking two or more nouns. Examples: an owns relationship between a company and a computer, a supervises relationship between an employee and a department, a performs relationship between an artist and a song, a proved relationship between a mathematician and a theorem. Entity-relationship diagrams don't show single entities or single instances of relations. Rather, they show entity sets and relationship sets. Example: a particular song is an entity. The collection of all songs in a database is an entity set. The eaten relationship between a child and her lunch is a single relationship. The set of all such child-lunch relationships in a database is a relationship set. In other words, a relationship set corresponds to a relation in mathematics, while a relationship corresponds to a member of the relation.

11 | P a g e

ATTENDANCE MANAGEMENT SYST M

EXAMPLE:

COMPANY

OFFERS

JOB

CARDINALITY: In the relational model, tables c n be related as any of: many-to-many, many-t -one (rev. one-tomany), or one-to-one. This is said o be the cardinality of a given table in relation to a other. For example, considering a database designed to keep track of hospital records. Suc have many tables like: 

a Doctor table full of doctor inf ormation



a Patient table with patient info mation



And a Department table with a entry for each department of the hospital.

a database could

In that model: 



There is a many-to-many rela ionship between the records in the doctor table nd records in the patient table (Doctors have many patients, and a patient could have several doctors); A one-to-many relation between the department table and the doctor table (each doctor works for one department, but one department could have many doctors).

One-to-one relationship is mostly used to split a table in two in order to optimize ccess or limit the visibility of some information. In t he hospital example, such a relationship could be sed to keep apart doctor's personal or administrative information.

EXAMPLE:

12 | P a g e


DIAGRAM ATI C REPRESENTATION OF VARI OUS COMPONENTS AND THEI R TY PES I N ER_M ODEL :

13 | P a g e


3.2 IDENTI FI CATI ON OF ENTITI ES: STUDENT ENTITY:

secid

semid

sname

sid

Student

  

Student entity has total 4 attributes sid,semid,secid,sname sid represents student id,semid represents to which semester the student is currently belonging to,secid represents the student’s section in a particular semester,sname represents student name. sid is the primary key.

SUBJ ECT ENTI TY :

subid

subnm

semid

Subjects   

subjects entity consists of attributes subid,subnm,semid subid represents subject id,subnm represents subject name,semid represents semester id(subjects for a particular semester). Subid is the primary key.

14 | P a g e


TEACHER ENTI TY : subid Tname

secid

Tid

Teacher

  

Teacher is an entity which consists of attributes tid,tname,subnm,secid,semid. Where tid represents teachers id,tname represents teachers name,subid represents subject id,sec id represents section id. Tid is used as a primary key.

SEMESTER ENTITY : semnm

semid

Semester   

Semester is an entity which consists of attributes semid,semnm. Semid represents semester id and semnm represents semester name. Semid is used as a primary key.

SECTI ON ENTITY : secid

secnm

Section

15 | P a g e


  

Section entity consists of attributes secid and secnm. Sec id represents section id,secnm represents section name. Secid is the primary key.

MONTHS ENTITY :

No_of_days

sid

Month Tot_days Subid

Months

 

Month is an entity which consists of attributes subid,month,sid,no_of_days,tot_days. Where subid represents subject identity,month represents particular month,sid represents student id.

ATTENDANCE ENTITY: secid

semid from1

to1

Sid

Attendance

 

subid

Tot no. of classes No. of classes

Attendance is an entity consisting of sid,secid,subid,semid,from1,to1,tot no. of classes and no. of classes attended as attributes. (Sid,subid) is the primary key.

16 | P a g e


3.3 IDENTI FI CATI ON OF REL ATIONSHIPS:

SEMESTER-SUBJECT RELATIONSHIP:

Semester

consists

Subject

STUDENT-SEMESTER RELATIONSHIP:

Student

belongs to

Semester STUDENT-SECTION RELATIONSHIP:

Student

belongs to

Section

17 | P a g e


STUDENT-ATTENDANCE RELATIONSHIP:

Student

has

Attendance TEACHER-SUBJECT RELATIONSHIP:

Teacher

teaches

Subject TEACHER-ATTENDANCE RELATIONSHIP:

Teacher

gives

Attendance TEACHER-SECTION RELATIONSHIP:

Teacher

handles

Section

18 | P a g e


SUBJECT-ATTENDANCE RELATIONSHIP:

Subject

has

Attendance

SUBJECT-MONTH RELATIONSHIP:

Subject

has

Month MONTH-ATTENDANCE RELATIONSHIP:

Month

based on

Attendance

19 | P a g e


3.4 COMPL ETE ER DIAGRAM :

semid

semnm

Semester

consists Belongs to

Belongs to

Subid

From1

semid

Si semid

secid secnm

to1

secid

Sname

has

secid Section

Tot no. of classes Subid

Teacher

subnm

gives teaches

tid tname

secid

handles

month

sid

has

Month

Tot_days

subid No_of_day

20 | P a g e

has

No. of classes

Subject

secid

sid

Attendance

Student

Based on

semid


LOGICAL SCHEMA DESIGN

21 | P a g e


4.1 REPRESENTATION OF ENTITI ES INTO TABLES: The different entities identified in our project are as follows: 

STUDENT



SUBJECT



TEACHER



SEMESTER



SECTION



ATTENDANCE



MONTHS

Above listed entities can be converted into tables as shown below with their corresponding attributes as fields.

STUDENT TABLE: S.NO

FIEL D NAME

FIEL D TY PE

FIEL D RANGE

1 2 3 4

SID SNAME SEMID SECID

NUMBER VARCHAR NUMBER NUMBER

10 20 10 10

S.NO

FIEL D NAME

FIEL D TY PE

FIEL D RANGE

1 2 3

SUBID SUBNM SEMID

NUMBER VARCHAR NUMBER

10 20 10

SUBJ ECT TABLE:

TEACHER TABL E: S.NO

FIEL D NAME

FIEL D TY PE

FIEL D RANGE

1 2 3 4

TID TNAME SUBID SECID

NUMBER VARCHAR NUMBER NUMBER

10 20 10 10

22 | P a g e


SEMESTER TABLE:

S.NO

FIEL D NAME

FIEL D TY PE

FIEL D RANGE

1 2

SEMID SEMNM

NUMBER VARCHAR

10 10

S.NO

FIEL D NAME

FIEL D TY PE

FIEL D RANGE

1 2

SECID SECNM

NUMBER VARCHAR

10 10

SECTI ON TABLE:

ATTENDANCE TABLE: S.NO

FI EL D NAME

FI EL D TY PE

FIEL D RANGE

1 2 3 4 5 6 7

SID SECID SEMID FROM1 TO1 SUBID TOT NO. OF CLASSES NO. OF CLASSES ATTENDED

NUMBER NUMBER NUMBER DATE DATE NUMBER NUMBER

10 10 10 10 10

NUMBER

10

S.NO

FI EL D NAME

FI EL D TY PE

FIEL D RANGE

1 2 3 4 5

SID MONTH SUBID NO_OF_DAYS TOTAL_DAYS

NUMBER VARCHAR NUMBER NUMBER NUMBER

10 20 10 10 10

8

MONTHS TABLE:

23 | P a g e


RELATIONAL SCHEMA DESIGN

24 | P a g e


5.1 ORACL E :

The Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle) is a relational database management system(RDBMS) produced and marketed by Oracle Corporation. As of 2009, Oracle remains a major presence in database computing.Larry Ellison and his friends and former coworkers Bob Miner and Ed Oates started the consultancy Software Development Laboratories (SDL) in 1977. SDL developed the original version of the Oracle software. The name Oracle comes from the codename of a CIA-funded project Ellison had worked on while previously employed by Ampex. DATABASE SCHEMA: Oracle database conventions refer to defined groups of object ownership (generally associated with a "username") as schemas. After the Oracle database installation process has set up the sample tables, the user can log into the database with the username scott and the password tiger. The name of the SCOTT schema originated with Bruce Scott, one of the first employees at Oracle (then Software Development Laboratories), who had a cat named Tiger. Oracle Corporation has de-emphasized the use of the SCOTT schema, as it uses few of the features of the more recent releases of Oracle. Most recent examples supplied by Oracle Corporation reference the default HR or OE schemas.

25 | P a g e


5.2 CODE GENERATED FOR THE PROPOSED PROJ ECT USING SQL: CREATE TABLE Student ( sid NUMBER(10) NOT NULL , sname VARCHAR2(20) NULL , semid NUMBER(10) NOT NULL , secid NUMBER(10) NOT NULL , primary key(sid), foreign key(semid) references semester(semid), foreign key(secid) references section(secid) ); CREATE TABLE Subject ( subid NUMBER(10)

NOT NULL ,

subnm VARCHAR2(20) NULL , semid NUMBER(10) NOT NULL , primary key(subid), foreign key(semid) references semester(semid); );

CREATE TABLE Teacher ( tid NUMBER(10) NOT NULL , tname VARCHAR2(20) NULL , subid NUMBER(10) NOT NULL, secid NUMBER(10) NOT NULL, primary key(tid), foreign key(subid) references subject(subid), foreign key(secid) references section(secid) ); CREATE TABLE Semester ( 26 | P a g e


semid NUMBER(10) NOT NULL , semnm VARCHAR2(20) NOT NULL , primary key(semid) );

CREATE TABLE Section ( secid NUMBER(10) NOT NULL , secnm VARCHAR2(20) NOT NULL , primary key(secid) );

CREATE TABLE Attendance ( sid NUMBER(10) NOT NULL , secid NUMBER(10) NOT NULL , subid NUMBER(10) NOT NULL , semid NUMBER(10) NOT NULL , from1 DATE NULL , to1 DATE NULL , tot no. of classes NUMBER(10) NOT NULL, No. of classes attended NUMBER(10) NULL, primary key(sid,subid), foreign key(sid) references student(sid), foreign key(subid) references subject(subid), foreign key(semid) references semester(semid), foreign key(secid) references section(secid) ); CREATE TABLE Months ( MONTH VARCHAR2(20) NULL, no_of_days NUMBER(10) NULL , total_days NUMBER(10) NULL , sid NUMBER(10) NOT NULL , 27 | P a g e


subid NUMBER(10) NOT NULL , foreign key(subid) references subject(subid), foreign key(sid) references student(sid) );

INSERT INTO Student values(71,’sowmya’,5,1); INSERT INTO Student values(72,’pranathi’,5,1); INSERT INTO Student values(73,’madhu’,7,2); INSERT INTO Student values(74,’sruthi’,3,2); INSERT INTO Student values(75,’sowmya’,7,3) INSERT INTO Student values(75,’ramya’,3,3);

INSERT INTO Subject values(1,’ca’,5); INSERT INTO Subject values(2,’daa’,5); INSERT INTO Subject values(3,’dc’,5); INSERT INTO Subject values(4,’dbms’,5); INSERT INTO Subject values(5,’edc’,2); INSERT INTO Subject values(6,’et’,2); INSERT INTO Subject values(7,’dms-1’,2); INSERT INTO Subject values(8,’ds’,4);

INSERT INTO Teacher values(1,’kavitha’,4,1); INSERT INTO Teacher values(2,’gayathri’,1,2); INSERT INTO Teacher values(3,’jaya’,4,3); INSERT INTO Teacher values(4,’ujwala’,8,2); INSERT INTO Teacher values(5,’kavitha’,7,3);

INSERT INTO Semester values(1,’1’); INSERT INTO Semester values(2,’2-1’); INSERT INTO Semester values(3,’2-2’); INSERT INTO Semester values(4,’3-1’); INSERT INTO Semester values(5,’3-2’); INSERT INTO Semester values(6,’4-1’); INSERT INTO Semester values(7,’4-2’); 28 | P a g e


INSERT INTO Section values(1,’A’); INSERT INTO Section values(1,’B’); INSERT INTO Section values(1,’C’);

INSERT INTO Attendance values(71,1,4,5,’25-JAN-11’,’25-FEB-11’,80,60); INSERT INTO Attendance values(72,1,3,5,’25-JAN-11’,’25-FEB-11’,65,60); INSERT INTO Attendance values(73,2,6,7,’25-SEP-11’,’26-NOV-11’,78,78); INSERT INTO Attendance values(74,2,7,3,’20-APR-10’,’25-MAY-10’,75,72); INSERT INTO Attendance values(75,3,4,7,’25-SEP-11’,’26-OCT-11’,60,55); INSERT INTO Attendance values(76,3,8,3,’20-APR-10’,’24-MAY-10’,80,70);

INSERT INTO Months values(‘APR’,27,30,71,1); INSERT INTO Months values(‘JAN’,25,31,76,1); INSERT INTO Months values(‘SEP’,28,30,72,2); INSERT INTO Months values(‘JUL’,26,31,73,4); INSERT INTO Months values(‘NOV’,27,30,75,5); INSERT INTO Months values(‘APR’,29,30,72,8);

5.3 TABL ES CREATED:

TNAME

TABTYPE CLUSTERID

------------------------------ ------- --------STUDENT

TABLE

SUBJECTS

TABLE

TEACHER

TABLE

SEMESTER

TABLE

SECTION

TABLE

ATTENDANCE

TABLE

MONTH

TABLE

6 rows selected.

29 | P a g e


SQL> desc section; Name

Null?

Type

----------------------------------------------------- -------- -----------------------------------SECID

NOT NULL NUMBER(10)

SECNM

VARCHAR2(20)

SQL> desc semester; Name

Null?

Type

----------------------------------------------------- -------- -----------------------------------SEMID

NOT NULL NUMBER(10)

SEMNM

VARCHAR2(20)

SQL> desc month; Name

Null?

Type

----------------------------------------------------- -------- -----------------------------------MONTH

NOT NULL VARCHAR2(20)

NO_OF_DAYS

NUMBER(10)

TOTAL_DAYS

NUMBER(10)

SID SUBID

NOT NULL NUMBER(10) NOT NULL NUMBER(10)

SQL> desc subjects; Name

Null?

Type

----------------------------------------------------- -------- -----------------------------------SUBID

NOT NULL NUMBER(10)

SUBNM SEMID

VARCHAR2(20) NOT NULL NUMBER(10)

SQL> desc student; Name

Null?

Type

----------------------------------------------------- -------- -----------------------------------SID

NOT NULL NUMBER(10)

SNAME

VARCHAR2(20)

SEMID

NOT NULL NUMBER(10)

30 | P a g e


SECID

NOT NULL NUMBER(10)

SQL> desc teacher; Name

Null?

Type

----------------------------------------------------- -------- -----------------------------------TID

NOT NULL NUMBER(10)

TNAME

VARCHAR2(20)

SUBID

NOT NULL NUMBER(10)

SECID

NOT NULL NUMBER(10)

SQL> desc attendance; Name

Null?

Type

----------------------------------------------------- -------- -----------------------------------SID

NOT NULL NUMBER(10)

SECID

NOT NULL NUMBER(10)

SEMID

NOT NULL NUMBER(10)

SUBID

NOT NULL NUMBER(10)

FROM1

DATE

TO1

DATE

TOT NO. OF CLASSES

NOT NULL NUMBER(10)

NO. OF CLASSES ATTENDED

NUMBER(10)

SQL>select * from student; SID

SNAME

SEMID

SECID

---------------- ------------------- ------------- -------------------71

soumya

5

1

72

pranathi

5

1

73

madhu

7

2

74

sruthi

3

2

75

sowmya

7

3

76

ramya

3

3

SQL>select * from subject; SUBID 31 | P a g e

SUBNM

SEMID


------------------- ------------------------ ------------------1

ca

5

2

daa

5

3

dc

5

4

dbms

5

5

edc

2

6

et

2

7

dms-1

2

8

ds

4

SQL>select * from teacher; TID

TNAME

SUBID

SECID

-------------- ---------------- -------------------- ------------------1 2 3 4 5

kavitha gayathri jaya ujwala kavitha

4 1 4 8 7

SQL>select * from semster; SEMID

SEMNM

---------------- --------------------1

1

2

2-1

3

2-2

4

3-1

5

3-2

6

4-1

7

4-2

SQL>select * from section; SECID

SECNM

---------------- --------------------1

A

2

B

32 | P a g e

1 2 3 2 3


3

C

SQL>select * from attendance; SID

SECID

SUBID

SEMID

FROM1

TO1

TOT NO. OF CLASSES

-------------- ---------------- ----------------- ----------------- --------------- --------------------------------- NO. OF CLASSES ATTENDED -------------------------------------------71

1

4

5

25-JAN-11 25-FEB-11

80

1

3

5

25-JAN-11 25-FEB-11

65

2

6

7

25-SEP-11 26-NOV-11

78

2

7

3

20-APR-10 25-MAY-10

75

3

4

7

25-SEP-11 26-OCT-11

60

3

8

3

20-APR-10 24-MAY-10

80

60 72 60 73 78 74 72 75 55 76 70

SQL>select * from month; MONTH

NO_OF_DAYS

TOT_DAYS

SID

SUBID

-------------- ------------------------- ---------------------- ------------ ------------------APR

27

30

71

1

JAN

25

31

76

1

SEP

28

30

72

2

JUL

26

31

73

4

NOV

27

30

75

5

APR

29

30

72

8

33 | P a g e


SCHEMA REFINEMENT

34 | P a g e


6.1 NORMAL IZATION: Normalization of data can be looked upon as a process of analyzing the given relation schemas based on their functional dependencies and primary keys to achieve the desirable properties of 1. Minimizing redundancy and 2. Minimizing the insertion, deletion and update anomalies. The normalization procedure provides database designers with following: 1. A formal framework for analyzing relation schemas based on their keys and on the functional dependencies among their attributes. 2. A series of normal form tests that can be carried out on individual relation schemas so that the relational database can be normalized to any desired degree. The process of normalization through decomposition must also confirm the existence of additional properties that the relation schemas, taken together, should process. This would include two properties: 1. The lossless join or non additive join property, which guarantees that the spurious tuple generation problem does not occur with respect to the relation schemas created after decomposition. 2. The dependency preservation property, which ensures that each functional dependency is represented in some individual relation resulting after decomposition.

NORMAL FORMS: FI RST NORMAL FORM: First normal form (1NF) is considered to be part of the formal definition of a relation in the basic relational model; historically, it was defined to disallow multi valued attributes, composite attributes and their combinations. It states that the domain of an attribute must include only atomic values and that the value of any attribute in a tuple must be a single value from the domain of that attribute .

SECOND NORMAL FORM: Second normal form (2NF) is based on the concept of fully functional dependency. A functional dependency X→Y is a full functional dependency if removal of any attribute A from X means that the dependency does not hold any more; that is, for any attribute AєX, (X-{A}) does not functionally determine Y. Functional dependency X→Y is a partial dependency if some attribute AєX can be removed from X and the dependency still holds; that is, for some AєX, (X -{A})→Y.

THIRD NORMAL FORM: Third normal form (3NF) is based on the concept of transitive dependency. A functional dependency X→Y in a relation schema R is a transitive dependency if there is a set of attributes Z that is a candidate key nor a subset of any key of R, and both X→Z and Z→Y hold. Let us consider tables from our scenario:

35 | P a g e


STUDENT: Sid

Sname

Semid

Secid

1

sowmya

5

1

2

pranathi

5

1

3

madhu

7

2

4

sruthi

3

2

5

sowmya

7

3

6

ramya

3

3

FI RST NORMAL FORM(1NF): The student table is in first normal form because 1. Each row is identified uniquely. 2. Each column is not having more than a single value.

SECOND NORMAL FORM(2NF): The student table is in second normal form because 1. It is in first normal form. 2. Every non key attribute is fully functionally dependent on key attributes.

THIRD NORMAL FORM(3NF): The student table is in third normal form because 1. It is in second normal form. 2. No non key attribute is is identified by another non key attribute.

TEACHER: Tid

Tname

Subid

Secid

1

kavitha

4

1

2

gayathri

1

2

3

jaya

4

3

4

ujwala

8

2

5

kavitha

7

3

FI RST NORMAL FORM(1NF): The teacher table is in first normal form because 3. Each row is identified uniquely. 4. Each column is not having more than a single value.

36 | P a g e


SECOND NORMAL FORM(2NF): The teacher table is in second normal form because 3. It is in first normal form. 4. Every non key attribute is fully functionally dependent on key attributes.

THIRD NORMAL FORM(3NF): The teacher table is in third normal form because 3. It is in second normal form. 4. No non key attribute is is identified by another non key attribute.

SEMESTER: Semid

Semnm

1

1

2

2-1

3

2-2

4

3-1

5

3-2

6

4-1

7

4-2

FI RST NORMAL FORM(1NF): The semester table is in first normal form because 5. Each row is identified uniquely. 6. Each column is not having more than a single value.

SECOND NORMAL FORM(2NF): The semester table is in second normal form because 5. It is in first normal form. 6. Every non key attribute is fully functionally dependent on key attributes.

THIRD NORMAL FORM(3NF): The semester table is in third normal form because 5. It is in second normal form. 6. No non key attribute is is identified by another non key attribute.

37 | P a g e


SECTION: Secid

Secnm

1

A

2

B

3

C

FI RST NORMAL FORM(1NF): The section table is in first normal form because 7. Each row is identified uniquely. 8. Each column is not having more than a single value.

SECOND NORMAL FORM(2NF): The section table is in second normal form because 7. It is in first normal form. 8. Every non key attribute is fully functionally dependent on key attributes.

THIRD NORMAL FORM(3NF): The section table is in third normal form because 7. It is in second normal form. 8. No non key attribute is is identified by another non key attribute.

SUBJ ECT: In the subject table semid(non key attribute) can be identified by subnm(non key attribute).It is violating 3NF.So we divide it into two tables ‘SUB1’ and ‘SUB2’.

SUB1:

38 | P a g e

Subid

Subnm

1

ca

2

daa

3

dc

4

dbms

5

edc

6

et

7

dms-1

8

ds


SUB2: Subid

Semid

1

5

2

5

3

5

4

5

5

2

6

2

7

2

8

4

FI RST NORMAL FORM(1NF): The sub1 and sub2 tables are in first normal form because 9. Each row is identified uniquely. 10. Each column is not having more than a single value.

SECOND NORMAL FORM(2NF): The sub1 and sub2 tables are in second normal form because 9. It is in first normal form. 10. Every non key attribute is fully functionally dependent on key attributes.

THIRD NORMAL FORM(3NF): The sub1 and sub2 tables are in third normal form because 9. It is in second normal form. 10. No non key attribute is is identified by another non key attribute.

ATTENDANCE: In this table the primary key is (sid,subid).Here ‘secid’ can be identified from ‘sid’ alone which is a part of primary key. Also ‘semid’, ‘tot no. of classes’ and ‘no. of classes attended’ can be identified from ‘subid’(part of primary key).These are violating 2NF.Hence we divide this into two tables ‘ATT1’ and ‘ATT2’.

39 | P a g e


ATT1: Sid

Secid

Semid

71

Tot no. of No. of classes classes attended 80 60

1

5

72

65

60

1

5

73

78

78

2

7

74

75

72

2

3

75

60

55

3

7

76

80

70

3

3

ATT2: Sid

Subid

From1

To1

71

4

25-JAN-11

25-FEB-11

72

3

25-JAN-11

25-FEB-11

73

6

25-SEP-11

26-NOV-11

74

7

20-APR-10

25-MAY-10

75

4

25-SEP-11

26-OCT-11

76

8

20-APR-10

24-MAY-10

FI RST NORMAL FORM(1NF): The att1 and att2 tables are in first normal form because 11. Each row is identified uniquely. 12. Each column is not having more than a single value.

SECOND NORMAL FORM(2NF): The att1 and att2 tables are in second normal form because 11. It is in first normal form. 12. Every non key attribute is fully functionally dependent on key attributes.

THIRD NORMAL FORM(3NF): The att1 and att2 tables are in third normal form because 11. It is in second normal form. 12. No non key attribute is is identified by another non key attribute.

40 | P a g e


MONTHS: In this table,the non key attributes ‘month’ and ‘ tot_days’ are dependent on each other violating 3NF.so we divide this table into ‘months1’ and ‘months2’.

MONTHS1:

Month

No_of_days

Sid

Subid

APR

27

71

1

JAN

25

76

1

SEP

28

72

2

JUL

26

73

4

NOV

27

75

5

APR

29

72

8

MONTHS2: Tot_days

No_of_days

Sid

Subid

30

27

71

1

31

25

76

1

30

28

72

2

31

26

73

4

30

27

75

5

30

29

72

8

FI RST NORMAL FORM(1NF): The months1 and months2 tables are in first normal form because 13. Each row is identified uniquely. 41 | P a g e


14. Each column is not having more than a single value.

SECOND NORMAL FORM(2NF): The months1 and months2 tables are in second normal form because 13. It is in first normal form. 14. Every non key attribute is fully functionally dependent on key attributes.

THIRD NORMAL FORM(3NF): The months1 and months2 tables are in third normal form because 13. It is in second normal form. 14. No non key attribute is is identified by another non key attribute.

AFTER NORMAL IZATION: We got six new tables ‘sub1’,’sub2’,’att1’,‘att2’ and ‘months1’,’months2’ in the place of

‘subject’,’attendance’ and ‘months’ table respectively.So we delete these old tables and create the six new tables.

Following is the code for deletion of old tables: Alter table subject drop column subid; Alter table subject drop column subnm; Alter table subject drop column semid; Alter table attendance drop column sid; Alter table attendance drop column secid; Alter table attendance drop column subid; Alter table attendance drop column from1; Alter table attendance drop column to1; Alter table attendance drop column tot no. of classes; Alter table attendance drop column no. of classes attended; Alter table months drop column month; Alter table months drop column tot_days; Alter table months drop column no_of_days; Alter table months drop column sid; Alter table months drop column subid;

creation of new tables: CREATE TABLE Sub1 42 | P a g e


( subid NUMBER(10)

NOT NULL ,

subnm VARCHAR2(20) NULL , primary key(subid) ); CREATE TABLE Sub2 ( subid NUMBER(10)

NOT NULL ,

semid NUMBER(10) NOT NULL , primary key(subid), foreign key(semid) references semester(semid) );

CREATE TABLE Att1 ( sid NUMBER(10) NOT NULL , tot no.ofclasses NUMBER(10) NOT NULL, No.of classes attended NUMBER(10) NULL, secid NUMBER(10) NOT NULL , semid NUMBER(10) NOT NULL , primary key(sid), foreign key(sid) references student(sid), foreign key(secid) references section(secid), foreign key(semid) references semester(semid) );

CREATE TABLE Att2 ( sid NUMBER(10) NOT NULL , subid NUMBER(10) NOT NULL , from1 DATE NULL , to1 DATE NULL , primary key(sid,subid), 43 | P a g e


foreign key(sid) references student(sid), foreign key(subid) references subject(subid) );

CREATE TABLE Months1 ( MONTH VARCHAR2(20) NULL, no_of_days NUMBER(10) NULL , sid NUMBER(10) NOT NULL , subid NUMBER(10) NOT NULL , foreign key(subid) references subject(subid), foreign key(sid) references student(sid) ); CREATE TABLE Months2 ( no_of_days NUMBER(10) NULL , total_days NUMBER(10) NULL , sid NUMBER(10) NOT NULL , subid NUMBER(10) NOT NULL , foreign key(subid) references subject(subid), foreign key(sid) references student(sid) );

44 | P a g e


SNAPSHOTS OF TABL ES

45 | P a g e


SEMESTER TABLE: Semid

Semnm

1

1

2

2-1

3

2-2

4

3-1

5

3-2

6

4-1

7

4-2

SECTI ON TABLE: Secid

Secnm

1 2 3

A B C

STUDENT TABLE: Sid 71 72 73 74 75 76

Sname Sowmya Pranathi Madhu Sruthi Sowmya Ramya

Semid

Secid

5 5 7 3 7 3

1 1 2 2 3 3

TEACHER TABL E: Tid

Tname

Subid

Secid

1 2 3 4 5

Kavitha Gayathri Jaya Ujwala kavitha

4 1 4 8 7

1 2 3 2 3

46 | P a g e


MONTHS1 TABLE: Month

No_of_days

APR JAN SEP JUL NOV APR

27 25 28 26 27 29

Sid

Subid

71 76 72 73 75 72

1 1 2 4 5 8

MONTHS2 TABLE:

Tot_days

No_of_days

30 31 30 31 30 30

Sid

Subid

71 76 72 73 75 72

1 1 2 4 5 8

27 25 28 26 27 29

SUB1: Subid

Subnm

1 2 3 4 5 6 7 8

ca daa dc dbms edc et dms-1 ds

Subid

Semid

1 2 3 4 5

5 5 5 5 2

SUB2:

47 | P a g e


6 7 8

2 2 4

ATT1: Sid

Tot no.of classes

No. of classes attended

Secid

Semid

71 72 73

80 65 78

60 60 78

1 1 2

5 5 7

74

75

72

2

3

75

60

3

7

76

80

3

3

55 70

ATT2: Sid

Subnm

71 72 73

4 3 6

25-JAN-11 25-JAN-11 25-SEP-11

25-FEB-11 25-FEB-11 26-NOV-11

74

7

20-APR-10

25-MAY-10

75

4

25-SEP-11

26-OCT-11

76

8

20-APR-10

24-MAY-10

48 | P a g e

From1

To1


QUERIES

49 | P a g e


1. Write a query to obtain the month which has (a.) maximum student attendance (b.) minimum student attendance Solution: 

Select month from months1 group by month having max(avg(no_of_days));

Output: 

SEP

Select month from months1 group by month having min(avg(no_of_days));

Output:

APR

2. Write a query to obtain the maximum attendance semester wise. Solution: 

Select semid,max((nca/tnc)*100) as percentage from att1 group by semid;

Output: SEMID PERCENTAGE ---------- -------------------5

92.307962

7

100

3

96

3. Write a query to obtain student details whose attendance is less than 80%. Solution: 

Select sid from att1 where (nca/tnc)*100<80;

Output: SID -----------71 4. Find name of the student who has the maximum attendance in the month of april.

50 | P a g e


Solution: Select m1.sid from months1 m1,months1 m2 where m1.month=’apr’ and m2.month=’apr’ and m1.no_of_days>m2.no_of_days; Output: SID ---------72 5. Procedure to update attendance of a student. Solution: create or replace procedure att(p_sid in att1.sid%type,ncat in att1.nca%type) is begin update att1 set nca=ncat where sid=p_sid; end att; SQL>procedure created SQL>execute att(75,78); SQL>procedure successfully completed. 6. Procedure to insert details of teacher into the teacher table. create or replace procedure pr_teacher(p_tid in teacher.tid%type,p_tname in teacher.tname%type,p_subid in teacher.subid%type,p_sec in teacher.secid%type) is begin insert into teacher(tid,tname,subid,secid) values(p_tid,p_tname,p_subid,p_sec); 51 | P a g e


commit; end pr_teacher; SQL>procedure created. SQL>execute pr_teacher(6,’kavitha’,5,1); SQL>procedure successfully completed. 7. Function to calculate the attendance of a student. create or replace function cal_att1(tnca in att1.tnc%type,ncat in att1.nca%type) return number is begin return((ncat/tnca)*100); end cal_att1; SQL>function created. SQL> select sid,cal_att1(tnc,nca)"percentage" from att1 where secid=2; SID percentage ---- ---------73

100

74

96

8. Trigger to restrict insertion or updation on student attendance. create or replace trigger restrict_upd before insert or update of no_of_days on months for each row begin if not(:new.sid in(71,72,73,74,75,76)) then raise_application_error(-20202,'student does not exist'); end if; end; SQL>trigger created. SQL>Update months set no_of_days=25 where sid=77; Error:student does not exist.

52 | P a g e


9. Trigger to restrict insertion or updation on student attendance. create or replace trigger restrict_upd before insert or update of no_of_days on months for each row begin if (:new.no_of_days>31) then raise_application_error(-20202,'attendance cannot be updated as number of days in a is>31’); end if; end; SQL>trigger created. SQL>Update months set no_of_days=32 where sid=75; Error: attendance cannot be updated as number of days in a month is>31.

53 | P a g e

month


CONCLUSION

The Attendance Management System is developed using ORACLE DATABASE and fully meets the objectives of the system which it has been developed. The system has reached a steady state where all bugs have been eliminated. The system is operated at a high level of efficiency and all the teachers and user associated with the system understands its advantage. The system solves the problem. It was intended to solve as requirement specification.

54 | P a g e

php attendance management system

Recommend Documents